Link SFS to a MySQL database using JDBC

Most admins in the SmartFox forums seem to think that connecting to a database is not that complicated, but I came across several hours worth of problems while trying to make it work. Most of these are from coding subtleties that only a person truly versed in JDBC knows. It's my goal here to help you connect to your database with ease. I know that everyone has their own formula for database management (usually the first thing you learn is what you want to use), and it shouldn't be too difficult to follow my example in mind of exchangeable parts. Here is a quick bio of why I'm using JDBC (skip to next paragraph if you don't care). I used it once in my graduate Database Management class at USC. In that class, my teacher taught us the theory, but we had to learn all of the implementation on our own to finish his rigorous assignments. I developed a formula to test all of the MySQL, PHP, HTML, XML Schema, CSS etc! No joke, I had to pull an all nighter every time one of our a coding assignment was due. So, I became extremely good friends with the amazing w3 school tutorials, occasionally reading an entire web language tutorial multiple times. http://www.w3schools.com/ should be your first reference for any web development tutorials. Even with my prior exposure to database management (I'm in no way a guru), I still had trouble making things work with SmartFoxServer.

I am currently managing the server on my own computer using WAMP. I believe Mac users can use MAMP.  This program allows you to manage a local database on your computer and even expose it to the outside world if you so desire. It comes built in with MySQL which is the component that you need for this tutorial. Instead of using PHP or ASP like you would do when developing a web site, we're going to connect to the database with JDBC: get the connector at http://www.mysql.com/products/connector/ and place the .jar in your {SFS2X directory}/Extension/__lib__.  I have created a MySQL database on my local computer using the phpMyAdmin tool and Oracle's MySQL Workbench. You can create a .sql file pretty easily with the Workbench. Design your database visually and use the forward engineer function to make a .sql creation script. Then, create that database on your WAMP server by typing localhost in your browser and clicking phpMyAdmin. You might need to edit your hosts file so that localhost will redirect to 127.0.0.1, and if you already have other programs in control of host, try associating it with a name like localhostwamp. This will show you all the databases you have on your server. Create a new database with the file that you built with the workbench.

Ok, so now you have installed a server that is running MySQL, and you have created a database that at least holds one user entry with a user name and password. You'll want to combine the official instructions http://docs2x.smartfoxserver.com/GettingStarted/howtos#item2
with this tutorial from the forums http://www.smartfoxserver.com/forums/viewtopic.php?f=4&t=11793&p=48400&hilit=jdbc+databasemanager#p48400  Well there are a couple of things that they don't explain. Firstly, the Database driver class doesn't refer to the name of the .jar connector, it refers to the name of the class located inside of the file. So for JDBC it's com.mysql.jdbc.Driver  If you're using WAMP then your MySql server should be running on port 3306. You can check this by clicking WAMP -> MySQL -> my.ini which will show you your password and port. The default user name is root. So for connection string it is jdbc:mysql://127.0.0.1:3306/yourDbName
Change the TestSQL value to "select count(*) from yourUserTable". When you boot up your server, I suggest doing it manually by clicking the SFS2X.bat file in your SFS2X directory.  This will show you a cmd window and alert you of any problems it is having connecting to the database. If it doesn't show any errors, then that means that your TestSQL script worked! Be happy! If not, then perhaps your sql statement isn't following standard syntax or has a typing error. Try getting some help from the post in the forums.

Now that your server is configured to connect with the database, you can start coding your server side extension. Previously, I've showed you how to create a room extension, but only a zone extension can be used to register login events. You'll need to open Net Beans and create a new project. Refer to my other posts about how to create an extension. In the main class of this extension,  you'll want to add this line of code addEventHandler(SFSEventType.USER_LOGIN, LoginEventHandler.class); 
inside of your init function. Make sure to create a Login Event Handler class for that function to call. There is a great snippet of code where someone has made a handler that checks the user name and password against the values in the database. http://www.smartfoxserver.com/forums/viewtopic.php?t=13292 Go with the second portion of code that says
by Zageron» 30 Jan 2012, 16:40  You can reference most of the functions in this documentation by SmartFoxServer and searching for the IDBManager http://docs2x.smartfoxserver.com/api-docs/javadoc/server/

It is a pretty complex thing, but this will work really well and follows pretty good Java coding practices. There are a couple things that might seem strange. One is that he calls the password a hash. This is true. SmartFoxServer will always send a password from the client as a hash. So that is why you have to use the API function to check the password from the database against the one the client sent. The other has to to do with stored procedures. You can't just grab a value from result. You must specify Result.first() or Result.next() to get increment the result iterator for the user's table entry for which you were looking. Prepared statements are good because you can iterate through several rows that are returned each time with Result.next(). Still having problems? Check this article on prepared statements to see why he is including a ? in the code. http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html  
 and then look here http://stackoverflow.com/questions/2120255/java-resultset-exception-before-start-of-result-set

*make sure to enable use custom login for the zone

Here is a little update so that you will know how to proceed when you want to connect to the database from the Room instead of the Zone extension. I do this to load a specific character according to what my user has stored in the database. Since you're following the example of the first person shooter, you should have a world class and a spawn me handler. This is quite a nice gift, so enjoy and make your program connect to your database like a champion!

//This is in your SpawnMeHandler.java class
public void handleClientRequest(User user, ISFSObject data)
{
 World world = RoomHelper.getWorld(this);
 String modelName = null;
 String request = "SELECT * FROM users WHERE username = ?";
 try
 {
  modelName = venue.sqlRequest(request, "characterModel", user.getName());
 }
 catch(Exception ex){}
 
 boolean newPlayer = venue.addPlayer(user, modelName);
 if(newPlayer)
 {
  sendOtherPlayersInfo(user);
 }
}

//Connect to a database in your Room Extension
public String sqlRequest(String sqlRequest, String field, String userName) throws SFSException
{
 IDBManager dbManager = extension.getDbManager();
 try
 {
  Connection connection = dbManager.getConnection();
  PreparedStatement stmt = connection.prepareStatement(sqlRequest);
  stmt.setString(1, userName);
  ResultSet result = stmt.executeQuery();
  if(result.first())
  {
   sqlRequest = result.getString(field);
  }
  
  stmt.close();
  connection.close();
 }
 catch(Exception ex)
 {
  SFSErrorData errData = new SFSErrorData(SFSErrorCode.GENERIC_ERROR);
  errData.addParameter("SQL Error: " + ex.getMessage());
  throw new SFSLoginException("A SQL Error occurred: " + ex.getMessage(), errData);
 }
 
 return sqlRequest;
}

Thank you for reading, and feel free to comment below.