In the third (and last) part of this series we are taking a look at using the JDBC API to access a database via SFS2X Extensions. While this approach takes a bit more coding compared to the DBManager API (which we have explored in part 2) it also provides more sophisticated features such as working with metadata and advanced data types.
If you are totally unfamiliar with the JDBC API, we highly recommend to take an introductory tour before proceeding.
Finally, in the last part of the article, we’ll briefly touch on local and remote development using Overcast and how to import/export databases from your dev machine to your remote server(s).
» Accessing the database via JDBC directly
When we use JDBC in an SFS2X Extension we don’t need to setup the driver and connection details via code, which is typically the first step to get started. These steps are already done through the DBManager configuration in SFS2X, as we have seen in part one of this article series.
The DBManager class provides us with a pool of reusable connections that we can use to communicate with our database, regardless of the API in use.
Here’s the first SQL query example using JDBC:
private void queryJDBC()
{
dbMan = getParentZone().getDBManager();
Connection conn = null;
try
{
conn = dbMan.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT username FROM highscores");
ResultSet res = stmt.executeQuery();
while (res.next())
{
System.out.println(res.getString("username"));
}
}
catch(SQLException ex)
{
try
{
trace("Unexpected error while querying the DB: " + ex.getMessage());
if (conn != null)
conn.close();
}
catch(SQLException sqlex)
{
trace("Unexpected error while closing connection: " + sqlex.getMessage());
}
}
}
The main difference here is that we’re managing the life-cycle of the connection ourselves, with special care to closing it when we’ve completed our work. Failing to do so (by mistake or due to an Exception) will cause a connection leak which in turn will impact both memory and performance (i.e. memory leaks and connection starvation).
The reason for this is that database connections are pooled (i.e. borrowed and returned from a pool of existing connections) to improve the performance and they must be returned at the end of each activity. The “returning” part is simply done by invoking the close() method on the connection itself.
The recommended approach is to use a try/catch/finally block to deal with any potential errors and close the connection in the finally section, which is guaranteed to run at the exit of our method call.
We’re not going into the details of how the PreparedStament and ResultSet classes work as it is outside of the scope of this article. If you need to refresh your memory please make sure to check the links at the end of the article.
We will however take a look at another example using external parameters and how to properly sanitize them to avoid SQL injection issues.
private void findScore(String username)
{
dbMan = getParentZone().getDBManager();
Connection conn = null;
try
{
conn = dbMan.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM highscores WHERE username=?");
stmt.setString(1, username);
ResultSet res = stmt.executeQuery();
while (res.next())
{
int score = res.getInt("score");
System.out.println("Score: " + score);
}
}
catch(SQLException ex)
{
try
{
trace("Unexpected error while querying the DB: " + ex.getMessage());
if (conn != null)
conn.close();
}
catch(SQLException sqlex)
{
trace("Unexpected error while closing connection: " + sqlex.getMessage());
}
}
}
As we have seen in part two we can use “?” placeholders in the SQL expression and populate them them separately. In the case of the PreparedStatement class we use the setString() method which uses indexes to specify which placeholder we want to replace (NOTE: parameter indexes start at 1 and not zero, as it happens for arrays and lists).
It’s worth to note that the class supports a large number of data types and you’ll find methods such as setInt, setDouble, setBoolean, setBlob, etc.
» Using metadata
Metadata in a database doesn’t represent the content of the records but rather the records themselves, with all their properties, such as column names, types, length etc.
This is akin to the reflection API in Java (and similar programming languages) which allows for self-inspection of the data types.
private void testMetadata()
{
dbMan = getParentZone().getDBManager();
Connection conn = null;
try
{
conn = dbMan.getConnection();
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet resultSet = databaseMetaData.getColumns(null,null, "highscores", null);
while(resultSet.next())
{
String name = resultSet.getString("COLUMN_NAME");
String type = resultSet.getString("DATA_TYPE");
String size = resultSet.getString("COLUMN_SIZE");
String isNullable = resultSet.getString("IS_NULLABLE");
trace("Name: %s, Type:%s, Size: %s, isNullable: %s\n", name, type, size, isNullable);
}
}
catch(SQLException ex)
{
try
{
trace("Unexpected error while querying the DB: " + ex.getMessage());
if (conn != null)
conn.close();
}
catch(SQLException sqlex)
{
trace("Unexpected error while closing connection: " + sqlex.getMessage());
}
}
}
The snippet above is an example of how to inspect a database table and extract column name, type, length and nullable properties. This can be useful when we need to dynamically work with tables without knowing all the details of their structure in advance.
» Testing locally and remotely
When working on an Overcast-based application with a database it can help to be able to test locally in order to build the DB tables and Extensions needed.
In the Overcast cloud we use MySQL so the best choice is to mirror the same setup (SFS2X + MySQL) in your local environment, possibly using the same database version that was deployed in the cloud, or at least the same major version (you can check the Overcast DB version via the HQ server details page).
How to create a local setup
A quick way to setup a local MySQL and PHPMyAdmin (the web-based admin tool) is to use a pre-packaged LAMP stacks such as XAMPP or MAMP.
These tools provide all the necessary components to get started and they can be installed locally alongside SmartFoxServer 2X.
NOTE: in the case of XAMPP the database installed is MariaDB, rather than MySQL, which can be considered a drop-in replacement for MySQL. Generally speaking you should have no problems developing with it and then moving back to MySQL in the cloud. If you want to learn more about this aspect please consult their documentation here.
How to import/export a database
If you’re creating and developing the database tables locally you will need a way to export them to a file format and re-import them in the remote Overcast DB.
This is easily achieved via the import/export function in PHPMyAdmin.
In this case we have selected a single table, but we could have also done it with the whole database. Next we select the Export tab, leave the default settings (Quick Export, in SQL format) and proceed.
This will generate a local .sql file containing both the structure and data of the table we’ve just selected.
Now we can move to the Overcast side, select our database server in the HQ and click the admin tool icon:
This will bring us to the remote PHPMyAdmin where we can start by creating a database with the same name as the one used locally, in this case: overcast_tutorial
Once this is done, we move to the Import tab:
Here we simply specify the previously created .sql file from our local filesystem and upload it. The table(s) will be recreated remotely and we should be good to go.
A quick check shows that we have indeed recreated our table and the relative data in the Overcast MySQL DB.
» More JDBC resources
If you want to read more about the JDBC API we’d like to recommend several articles and tutorials that will help you learn the ins and outs of the library.
…and, as usual, if you have any comment or questions let us know via our SmartFoxServer support forum.