GWT 2.x and MySQL

While starting my first GWT project I looked around the internet for a tutorial for connecting to a MySQL database without using the AppEngine, and there were a few good ones but they were all written 2+ years ago (eons in computer-speak) and dealt with GWT 1.x which had some minor differences between versions that did not give me a successful outcome when exactly followed.  So after some major trial and error I was able to discover the uncharted territory of connecting to MySQL using GWT 2.x.

I will briefly review the basic setup of how to connect to MySQL and if you would like a more in-depth explanation I would suggest head over to my good friend Bastian Tenbergen's website where he has a great tutorial for GWT 1.x and who worked with me to find a proper solution for GWT 2.x and is what my code is based on. [1]

I am writing my code in Java using Eclipse Kepler with the GWT Plugin for Eclipse and MySQL 5.1.  If you do not have these installed now would be a good time. Once my code is compiled it will be uploaded to a Linux server with Apache and Tomcat, but you can just as easily be using a Windows server.  Most web servers will have a Linux/Apache setup for you already but if you need help setting up Tomcat for the first time there is a really good tutorial here:
http://www.puschitz.com/InstallingTomcat.html


Step 1: Remove the AppEngine from your project

A pox on the AppEngine! It servers no purpose for my needs as I, like many of us, already have a web server.  So I cast you away by right-click the project Eclipse and choose Properties.  Once the new window named "Properties for Your Project" pops up click Google >  App Engine > deselect Use Google App Engine.

Step 2: Add the MySQL Connector/J to the build path
This is the most important step because without this guy we have no way of connecting to our MySQL database.  First you must download the latest version of the MySQL Connector.  In Eclipse right-click your project and choose Build Path > Configure Build Path.  Once the new window named "Properties for Your Project" pops up click the "Add External Jar" button and browse to then select the MySQL Connector/J named something like "mysql-connector-java-x.x.x-bin.jar"
Or if you are using Maven then add the mysql dependency. You can find the latest version to use here.

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>

Step 3: Create a Serializable class
For every bit of data that you would like to send from the server (your server) to the client (their computer) and visa-versa you will have to create a serializable class.  For example lets say our database has a table called `users` which has three columns, `id`, `username` and `password`, in order to communicate this data we would need to create a class called "User" that implements IsSerializable and has three variables, id, username and password.  I know this is a bad example because this is not how you would ever work with authentication but bear with me please.  When your project grows you can create as many serializable classes as you like with as many names as you like to send your data back and forth and to and fro.  In your project's shared package create a new class and name it User.java.

package com.yourproject.shared;

import com.google.gwt.user.client.rpc.IsSerializable;


/**
 * A class for the user database table
 */
public class User implements IsSerializable {
    private int id = "";
    private String username = "";
    private String password = "";
    
    private User() {
        //just here because GWT wants it.
    }
    
    /**
     * A user of the system
     * @param id
     * @param username
     * @param password
     */
    public User(int id, String username, String password) {
        this.setId(id);
        this.setUserName(username);
        this.setPassword(password);
    }
    
    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }

    /**
     * @param username the username to set
     */
    public void setUserName(String username) {
        this.username = username;
    }

    /**
     * @return the username
     */
    public String getUserName() {
        return username;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }    
}

Step 4: Create the Server-Side Class
The server-side class is the code that will directly interact with the database. This is where we will have all of our SQL commands to SELECT, UPDATE, INSERT and anything else we want. To create the class right-click on the com.yourproject.server package and select New > Class and name it "MySQLConnection.java".

package com.yourproject.server;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.yourproject.shared.User;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class MySQLConnection extends RemoteServiceServlet implements DBConnection {

    private static final long serialVersionUID = 1L;    

    private String _status;
    private String _url = "jdbc:mysql://localhost:3306/mydatabase";
    private String _user = "myusername";
    private String _pass = "mysecretpassword";
    
    /**
     * Constructor
     */
    public MySQLConnection() {
    }

    /**
     * Gets the connection for all of our commands
     * 
     * @return
     * @throws Exception
     */
    private Connection getConnection() throws Exception {
        //I like to use this setup where it converts datetimes of '00-00-0000' to null rather than error out.
        Properties props = new Properties();
        props.setProperty("user", _user);
        props.setProperty("password", _pass);
        props.setProperty("zeroDateTimeBehavior", "convertToNull");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection(_url, props);
        return conn;
    }

    
    /**
     * Authenticates a user based on their username and password
     * @throws Exception
     */
    public User authenticateUser(String username, String password) throws Exception {
        User returnuser = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet result = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM users WHERE username = ? AND password = ?");
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            result = pstmt.executeQuery();
            while (result.next()) {
                returnuser = new User(result.getInt("id"), result.getString("username"), result.getString("password"));
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            // Cleanup
            result.close();
            pstmt.close();
            conn.close();
        }

        return returnuser;
    }
}

Step 5: Create the interfaces
Right about now Eclipse is probably freaking out saying, "Hey Jerk there isn't anything called DBConnection!"  Well tell Eclipse to not get its panties in a twist as we need to create 2 classes to be the middle-man between our client and the server called an interface. These interfaces perform the synchronous and asynchronous remove service calls that make AJAX, and by association GWT, just so cool to program in and if you are not as familiar with how this works please research as you will need to know the ins-and-outs of remote procedure calls. In the package com.yourproject.client (or a subfolder of client) create 2 interfaces by right-clicking the package and selecting New > Interface and call them "DBConnection.java" and "DBConnectionAsync.java"

package com.yourproject.client;

import com.yourproject.shared.User;
import com.google.gwt.user.client.rpc.RemoteService;

public interface DBConnection extends RemoteService 
{
    public User authenticateUser(String username, String password);
}

package com.yourproject.client;

import com.yourproject.shared.User;
import com.google.gwt.user.client.rpc.AsyncCallback;

public interface DBConnectionAsync 
{
    public void authenticateUser(String username, String password, AsyncCallback<User> callback);
}

Step 6: Declare the servlet
Open your web.xml file found in your projects war/WEB-INF/ folder and add the following code within the web-app tags. Note that the value yourprojectname in the url-pattern node is the subfolder under the war folder that is your project name.  Confused? You can read more about this in the "Implementing Services" section of the GWT dev guide topic "Communicating with a server". [3]

    <servlet>
        <servlet-name>mySQLConnection</servlet-name>
        <servlet-class>com.yourproject.server.MySQLConnection</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>mySQLConnection</servlet-name>
        <url-pattern>/yourprojectname/MySQLConnection</url-pattern>
    </servlet-mapping>

Step 7: Call the server from the client
Okay this is the last step so now you get to find out if I am a deadbeat liar or if you accidentally skipped a step. In any class in your client package, preferably in a presenter class if you are using the MVP format, you will first need to connect to the server via the DBConnection interface.

    DBConnectionAsync rpcService = (DBConnectionAsync) GWT.create(DBConnection.class);
    ServiceDefTarget target = (ServiceDefTarget) rpcService;
    String moduleRelativeURL = GWT.getModuleBaseURL() + "MySQLConnection";
    target.setServiceEntryPoint(moduleRelativeURL);

Now you can use the rpcService variable to call the method defined in the server-side code.

    rpcService.authenticateUser("president_skroob", "12345", new AsyncCallback<User>(){
      public void onFailure(Throwable caught) {
        Window.alert("You got to help me. I don't know what to do. I can't make decisions. I'm a president!");        
      }

      public void onSuccess(User result) {
        Window.alert("Hey I'm a user with id " + result.getId());        
      }
    });

Conclusion:
I hope this was able to help get you started. In the future I wrote another blog to actually do something useful with this code because every tutorial out there does the same "authenticate user" crap which isn't actually how you want to authenticate your users anyway nor does it help when you want to insert or update some rows. And now that I've complained it makes this an official blog!

The next blog can be found here: Doing More With GWT 2x and MySQL

References:
[1] http://altair.cs.oswego.edu/~tenberge/tenbergen.org/misc/DB-Access-in-GWT-The-Missing-Tutorial.pdf
[2] http://dev.mysql.com/downloads/connector/j/
[3] http://code.google.com/webtoolkit/doc/latest/DevGuideServerCommunication.html