Using a database in Overcast (part 2)

In the previous installment of the this tutorial we have learned all the steps to create a database server in Overcast and connect to it from an existing SFS2X instance.

In this new chapter we are going to explore the database API that can be used to query the database from server side, using SFS2X Extensions.

If you are new to server side coding we highly recommend to get started with this article from our documentation, before proceeding with the rest of the tutorial.

» API Choices

Essentially all database access in SFS2X is done via the standard Java JDBC API, which provides an abstraction for talking to all kinds of relational databases.

In addition to JDBC we provide a simplified layer on top of it, called the DBManager API, that offers a more streamlined approach to querying and manipulating records.

If you’re new to JDBC we highly recommend to start with the latter and eventually move to JDBC, if necessary.

» Database Manager API Examples

Before looking at the code we assume, as a prerequisite, that you’ve already setup your database and configured SFS2X. If not, make sure to go back to part one of this article and follow the steps there.

We’ll start off with a basic query example:

public class OvercastDBExtension extends SFSExtension
{
	private IDBManager dbMan;
	
	@Override
	public void init()
	{
		dbMan = getParentZone().getDBManager();
		String sql = "SELECT username FROM highscores";
		trace("About to execute: " + sql);
		
		try
		{
			ISFSArray result = dbMan.executeQuery(sql);
			trace(result.getDump());
		}
		catch(SQLException ex)
		{
			trace("Error executing query: " + ex.getMessage());
		}
	}
}

In the init() method we get a reference to the DBManager object associated with the current zone. This is the entity that manages our connections and allows us to execute queries, inserts, updates and so on.

Next we execute a basic SQL statement to extract all the user names from the table created in part one of this article. It is important to note that the execution is wrapped in a try/catch block to catch any potential SQLException, which can occur if the database is offline or there’s an error in the SQL statement etc.

The result of the operation is an SFSArray containing as many SFSObject instances are there are results. In other words the returned array is a collection of records, each containing all of the columns that we have selected (in this case only one).

The last line:

trace(result.getDump());

dumps the content of the SFSArray in the standard output, which will look like this:

(sfs_object) 
	(utf_string) username: fozzie
		
(sfs_object) 
	(utf_string) username: gonzo
		
 (sfs_object) 
	(utf_string) username: kermit

Here we find listed every SFSObject contained in the array and its key-value content. Because we only selected the username field in our SQL, we only get one key-value pair in each SFSObject.

» Sanitizing external input

In the code above we have a simple SQL query with no parameters coming from outside but in a more realistic use case we would likely have several parameters coming from the client, which we cannot trust.

To avoid any SQL injection shenanigans we’ll use an overloaded version of the executeQuery method available it the DBManager API: executeQuery(String, Object[]) which takes a second argument with the parameters we want to use.

An example will clarify how this works:


public int findHighscore(String userName)
{
	dbMan = getParentZone().getDBManager();
	String sql = "SELECT * FROM highscores WHERE username=?";
	int highscore = -1;

	try
	{
		ISFSArray result = dbMan.executeQuery(sql, new Object[]{ userName });
		
		if (result.size() > 0)
		{
			highscore = result.getSFSObject(0).getInt("highscore");
		}
	}
	catch(SQLException ex)
	{
		trace("Error executing query: " + ex.getMessage());
	}

	return highscore;
}

Here we have slightly modified version of the previous code: we have created a method that searches for a username and returns his/her high score, if it exists.

Since we now have an external parameter (the user name) we need to sanitize whatever data has been sent by the client: you can notice that our SQL expression now contains a WHERE clause and its value is not specified. Instead it’s replaced by a placeholder (the question mark) and the actual value is passed separately in the 2nd argument of executeQuery().

If we needed to include more parameters in the SQL we could just add more “?” placeholders and provide the relative value in the Object[] array, making sure to keep the correct order.

For instance:

String sql = "SELECT * FROM highscores WHERE username=? AND highscore > ?";

ISFSArray result = dbMan.executeQuery(sql, new Object[]{ userName, minScore });

» Inserting, updating and deleting records

It won’t come as a surprise that inserting and updating records follows a very similar approach, by calling a different method of the DBManager API.

Let’s take a look at how to insert a new record first:

	public void testInsert(String username, int score)
	{
		dbMan = getParentZone().getDBManager();
		String sql = "INSERT INTO highscores (username, score) VALUES (?, ?)";
		
		try
		{
			dbMan.executeInsert(sql, new Object[] { username, score });
			trace("DB Insert success");
		} 
		
		catch (SQLException ex)
		{
			trace("DB Insert failure: " + ex.getMessage());
		}
	}

which can be invoked like this:

testInsert("swedish cook", 1300);

You’ll notice that the example code is almost identical to the previous query snippets with the difference that we are calling executeInsert(), rather than executeQuery().

» Record update

Now let’s see how the update works:

	public void testUpdate(String username, int score)
	{
		dbMan = getParentZone().getDBManager();
		String sql = "UPDATE highscores SET score=? WHERE username=?";
		
		try
		{
			dbMan.executeInsert(sql, new Object[] { score, username });
			trace("DB Update success");
		} 
		
		catch (SQLException ex)
		{
			trace("DB Update failure: " + ex.getMessage());
		}
	}

And we can invoke the method like this:

testUpdate("swedish cook", 1500)

» Record deletion

Finally let’s take a look at deleting a record from an existing table:

	public void testDelete(String username)
	{
		dbMan = getParentZone().getDBManager();
		String sql = "DELETE FROM highscores WHERE username=?";
		
		try
		{
			dbMan.executeInsert(sql, new Object[] { username });
			trace("DB Delete success");
		} 
		
		catch (SQLException ex)
		{
			trace("DB Delete failure: " + ex.getMessage());
		}
	}

And let’s invoke it:

testDelete("swedish cook")

» Next steps

Combining the tutorials from part one and part two of this series you should be able to get cracking with SFS2X and server-side databases coding.

In the next (and last) episode we will take look at more advanced ways of working with databases using the standard Java JDBC API and provide some tips on local and remote deployment and testing.

If you have any comment or questions let us know via our SmartFoxServer support forum.