So you've read my blog about how to connect to MySQL in GWT 2.x or you've seen other tutorials out there that just go over one example on how to get a user from the database. Great. Now what if you wanted to do something useful... where are the tutorials for that? After much research I was able to successfully connect the dots and get MySQL to actually be MY SQL and not some bastard who only knew how to throw exceptions.
I am writing my code in Java using Eclipse Helios with the GWT Plugin for Eclipse and MySQL 5.1. If you do not have these installed go directly to jail, if you pass "GO" do not collect $200.
The Basics
Each command should be broken up into its own method in the server's MySQLConnection.java and don't forget to also add the methods to the DBConnection and DBConnectionAsync interfaces. With each command you will need to define 4 variables within your server method:
java.sql.Connection:
The MySQL connection that will contain where the host is, what port, the MySQL username and password that you will be connecting with. Its a good idea to create a user that has access to only the database and/or tables of interest and to never use the MySQL root account.
java.sql.PreparedStatement:
This will be where you place your SQL command text. I suggest using parameters with the command rather than trying to concatenate a string with the values you are attempting to insert because there are a lot of little gotchas that you might not be aware of that the MySQL Connector already has figured out for you. To use a parameter in your command text just place a question mark where you would like that value to be then use the methods setString, setInt or setWhatever to set the value at the defined position.
java.sql.ResultSet:
Holds the data returned from an executed PreparedStatement. You can use the getString, getInt or getWhatever methods to retrieve your database columns and set them in variables or better yet in the properties of your serialized class.
A serializable class:
Such as the User class in the previous blog. Since you cannot send the database connection or the ResultSet this is how you will be able to transfer data between the client and the server.
The SELECT Statement
This one is pretty straightforward and hopefully you have seen this before in my previous blog or elsewhere. If not then please go back and review because I am assuming your setup is just like the one I previously talked about.
MySQLConnection.java:/** * 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 id, username, password 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; }
DBConnection.java:public User authenticateUser(String username, String password);
DBConnectionAsync.java:public void authenticateUser(String username, String password, AsyncCallback<User> callback);
All we need to do is set the PreparedStatement's parameter values, execute the query, load the results into our serializable User class then return it to the client.
The SELECT Multiple Rows Statement
Great I can get one user, but what if I wanted to get a collection of users? The secret is the java.util.ArrayList type isn't a primitive type but it is serializable... score! So all we have to do is build and return an ArrayList of our serialized class. We can also do this same solution to INSERT, UPDATE and DELETE multiple rows.
MySQLConnection.java:/** * Gets a collection of users * @return ArrayList of User objects * @throws Exception */ public ArrayList<User> selectUsers() throws Exception { ArrayList<User> returnusers = new ArrayList<User>(); Connection conn = null; PreparedStatement pstmt = null; ResultSet result = null; try { conn = getConnection(); pstmt = conn.prepareStatement("SELECT * FROM users"); result = pstmt.executeQuery(); while (result.next()) { returnusers.add(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 returnusers; }
DBConnection:public ArrayList<User> selectUsers();
DBConnectionAsync:public void selectUsers(AsyncCallback<ArrayList<User>> callback);
The INSERT Statement
Crack open a fresh beer because now the real fun begins. The structure of the INSERT method is very similar to the SELECT method except to execute the command we are going to call the PreparedStatement's method executeUpdate and we want to find out what the new id of the row is so we want to return this to the client by using the PreparedStatement's getGeneratedKeys method and getting the returned "GENERATED_KEY" column.
MySQLConnection.java:/** * * @param user * @return * @throws Exception */ public int insertUser(User user) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet newkeys = null; int id = -1; try { // Get database connection conn = getConnection(); // Insert new user pstmt = conn.prepareStatement("INSERT INTO users (username, password) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, user.getUserName()); pstmt.setString(2, user.getPassword()); // Get id of the new user row if (pstmt.executeUpdate() > 0) { // Get new generated id newkeys = pstmt.getGeneratedKeys(); while (newkeys.next()) { id = newkeys.getInt("GENERATED_KEY"); } } else { //Some error message System.out.println("SQL error in insertUser(" + user.getUserName() + "): Bad insert of user."); } } catch (SQLException sqle) { sqle.printStackTrace(); } finally { // Cleanup newkeys.close(); pstmt.close(); conn.close(); } return id; }
DBConnection:public int insertUser(User user, String password);
DBConnectionAsync:public void insertUser(User user, String password, AsyncCallback<Integer> callback);
The UPDATE Statement
Updating a row is similar to inserting a row, however we already know what tthe row's id is so we can use that to update our specific user. For my example I am using a boolean to return success or failure of the insert but you could return how many rows were updated from the executeUpdate method, 0 for none or error and 1 or more for how many rows affected.
MySQLConnection.java:/** * Udate user row * @param user User to update * @throws Exception */ public boolean updateUser(User user) throws Exception { Connection conn = null; PreparedStatement pstmt = null; boolean success = true; try { if (user != null) { // Get database connection conn = getConnection(); // Update th user row pstmt = conn.prepareStatement("UPDATE users SET username = ?, password = ? WHERE id = ?"); pstmt.setString(1, user.getFirstName()); pstmt.setString(2, user.getLastName()); pstmt.setInt(3, user.getId()); if (pstmt.executeUpdate() == 0) { System.out.println("Error in updateUser(" + user.getId() + "): Bad update."); success = false; } } else { System.out.println("Error in updateUser(null): User object is null."); success = false; } } catch (SQLException sqle) {sqle.printStackTrace(); success = false; } finally { // Cleanup pstmt.close(); conn.close(); } return success; }
DBConnection.java:public boolean updateUser(User user);
DBConnectionAsync.java:public void updateUser(User user, AsyncCallback<Boolean> callback);
The DELETE Statement
I would like to show you how to delete a row from a database but as your good friend I have to stop you in your tracks. Any good DBA will tell you, "Never delete your data, simply deactivate it." Take into consideration what happens if someone wanted to delete some user then realizes their mistake and they want that data back? Too bad, so sad, that data is permanently gone with the DELETE command. What I recommend is adding a new column named "active" or "isactive" of type TINYINT(1) or BOOLEAN to your database table where the value is true if the row is active or false when you want it "deleted". This way any undo action is simply flipping a switch.
