Within the third (and final) a part of this collection we’re having a look at utilizing the JDBC API to entry a database by way of SFS2X Extensions. Whereas this method takes a bit extra coding in comparison with the DBManager API (which we’ve got explored in half 2) it additionally offers extra subtle options similar to working with metadata and superior knowledge varieties.

If you’re completely unfamiliar with the JDBC API, we extremely suggest to take an introductory tour earlier than continuing.

Lastly, within the final a part of the article, we’ll briefly contact on native and distant growth utilizing Overcast and the right way to import/export databases out of your dev machine to your distant server(s).

» Accessing the database by way of JDBC straight

Once we use JDBC in an SFS2X Extension we don’t have to setup the driving force and connection particulars by way of code, which is often step one to get began. These steps are already accomplished by way of the DBManager configuration in SFS2X, as we’ve got seen in half one of this text collection.

The DBManager class offers us with a pool of reusable connections that we are able to use to speak with our database, whatever the API in use.

Right here’s the primary SQL question instance utilizing JDBC:

non-public void queryJDBC()
{
	dbMan = getParentZone().getDBManager();
	Connection conn = null;
	
	strive
	{
		conn = dbMan.getConnection();
		PreparedStatement stmt = conn.prepareStatement("SELECT username FROM highscores");
		ResultSet res = stmt.executeQuery();
		
		whereas (res.subsequent())
		{
			System.out.println(res.getString("username"));
		}
	}
	
	catch(SQLException ex)
	{
		strive
		{
			hint("Surprising error whereas querying the DB: " + ex.getMessage());
			if (conn != null)
				conn.shut();
		}
		
		catch(SQLException sqlex)
		{
			hint("Surprising error whereas closing connection: " + sqlex.getMessage());
		}
	}
}

The primary distinction right here is that we’re managing the life-cycle of the connection ourselves, with particular care to closing it once we’ve accomplished our work. Failing to take action (by mistake or as a consequence of an Exception) will trigger a connection leak which in flip will influence each reminiscence and efficiency (i.e. reminiscence leaks and connection hunger).

The explanation for that is that database connections are pooled (i.e. borrowed and returned from a pool of present connections) to enhance the efficiency and so they should be returned on the finish of every exercise. The “returning” half is solely accomplished by invoking the shut() methodology on the connection itself.

The advisable method is to make use of a strive/catch/lastly block to take care of any potential errors and shut the connection within the lastly part, which is assured to run on the exit of our methodology name.

We’re not going into the main points of how the PreparedStament and ResultSet courses work as it’s outdoors of the scope of this text. If it’s essential refresh your reminiscence please make sure that to verify the hyperlinks on the finish of the article.

We are going to nonetheless check out one other instance utilizing exterior parameters and the right way to correctly sanitize them to keep away from SQL injection points.

non-public void findScore(String username)
{
	dbMan = getParentZone().getDBManager();
	Connection conn = null;
	
	strive
	{
		conn = dbMan.getConnection();
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM highscores WHERE username=?");
		stmt.setString(1, username);

		ResultSet res = stmt.executeQuery();
		
		whereas (res.subsequent())
		{
			int rating = res.getInt("rating");
			System.out.println("Rating: " + rating);
		}
	}
	
	catch(SQLException ex)
	{
		strive
		{
			hint("Surprising error whereas querying the DB: " + ex.getMessage());
			if (conn != null)
				conn.shut();
		}
		
		catch(SQLException sqlex)
		{
			hint("Surprising error whereas closing connection: " + sqlex.getMessage());
		}
	}
}

As we’ve got seen in half two we are able to use “?” placeholders within the SQL expression and populate them them individually. Within the case of the PreparedStatement class we use the setString() methodology which makes use of indexes to specify which placeholder we need to change (NOTE: parameter indexes begin at 1 and never zero, because it occurs for arrays and lists).

It’s price to notice that the category helps numerous knowledge varieties and also you’ll discover strategies similar to setInt, setDouble, setBoolean, setBlob, and so forth.

» Utilizing metadata

Metadata in a database doesn’t characterize the content material of the data however reasonably the data themselves, with all their properties, similar to column names, varieties, size and so forth.

That is akin to the reflection API in Java (and comparable programming languages) which permits for self-inspection of the information varieties.

non-public void testMetadata()
{
	dbMan = getParentZone().getDBManager();
	Connection conn = null;
	
	strive
	{
		conn = dbMan.getConnection();
		DatabaseMetaData databaseMetaData = conn.getMetaData();
		
		ResultSet resultSet = databaseMetaData.getColumns(null,null, "highscores", null); 
		
		whereas(resultSet.subsequent()) 
		{ 
			String title = resultSet.getString("COLUMN_NAME");
			String sort = resultSet.getString("DATA_TYPE");
		    String dimension = resultSet.getString("COLUMN_SIZE");
		    String isNullable = resultSet.getString("IS_NULLABLE");
		    
		    hint("Identify: %s, Kind:%s, Dimension: %s, isNullable: %sn", title, sort, dimension, isNullable);
		}
	}
	
	catch(SQLException ex)
	{
		strive
		{
			hint("Surprising error whereas querying the DB: " + ex.getMessage());
			if (conn != null)
				conn.shut();
		}
		
		catch(SQLException sqlex)
		{
			hint("Surprising error whereas closing connection: " + sqlex.getMessage());
		}
	}
}

The snippet above is an instance of the right way to examine a database desk and extract column title, sort, size and nullable properties. This may be helpful when we have to dynamically work with tables with out understanding all the main points of their construction prematurely.

» Testing regionally and remotely

When engaged on an Overcast-based utility with a database it could possibly assist to have the ability to check regionally to be able to construct the DB tables and Extensions wanted.

Within the Overcast cloud we use MySQL so your best option is to reflect the identical setup (SFS2X + MySQL) in your native atmosphere, probably utilizing the identical database model that was deployed within the cloud, or at the least the identical main model (you possibly can verify the Overcast DB model by way of the HQ server particulars web page).

The best way to create an area setup

A fast technique to setup an area MySQL and PHPMyAdmin (the web-based admin device) is to make use of a pre-packaged LAMP stacks similar to XAMPP or MAMP.

These instruments present all the required elements to get began and they are often put in regionally alongside SmartFoxServer 2X.

NOTE: within the case of XAMPP the database put in is MariaDB, reasonably than MySQL, which might be thought-about a drop-in substitute for MySQL. Usually talking you should not have any issues growing with it after which transferring again to MySQL within the cloud. If you wish to be taught extra about this facet please seek the advice of their documentation right here.

The best way to import/export a database

In the event you’re creating and growing the database tables regionally you’ll need a technique to export them to a file format and re-import them within the distant Overcast DB.

That is simply achieved by way of the import/export perform in PHPMyAdmin.

On this case we’ve got chosen a single desk, however we may have additionally accomplished it with the entire database. Subsequent we choose the Export tab, depart the default settings (Fast Export, in SQL format) and proceed.

This can generate a native .sql file containing each the construction and knowledge of the desk we’ve simply chosen.

Now we are able to transfer to the Overcast aspect, choose our database server within the HQ and click on the admin device icon:

This can deliver us to the distant PHPMyAdmin the place we are able to begin by making a database with the identical title because the one used regionally, on this case: overcast_tutorial

As soon as that is accomplished, we transfer to the Import tab:

Right here we merely specify the beforehand created .sql file from our native filesystem and add it. The desk(s) will probably be recreated remotely and we must be good to go.

A fast verify reveals that we’ve got certainly recreated our desk and the relative knowledge within the Overcast MySQL DB.

» Extra JDBC sources

If you wish to learn extra in regards to the JDBC API we’d wish to suggest a number of articles and tutorials that may allow you to be taught the ins and outs of the library.

…and, as ordinary, when you’ve got any remark or questions tell us by way of our SmartFoxServer assist discussion board.