Table of Contents
Demos
Sample an application using MySql on Apache Tomcat server.
MySql Connection Demo
- Demo - MySql conn application demo using GWT 2.03+ - hosted on tomcat
- Source - See the code in the packages
MySql FeedBack Demo
- Demo - Click on the button in the demo. Its hosted on my tomcat server
- Source - See the code in the packages
The MySql JDBC Connector
- 1. Move the JDBC library to your ./WEB-INF/lib folder
- 2. Add the library to your build path. Right click > Add to build path.
Reference links
Connection Pooling
One of the things that slows down mysql is the authorization process which can take up 400ms of latency. Pooling connections keeps them alive, warm and ready for the next request, especially when you have many coming and going. I recommend using connection pooling, it will speed up your application tremendously. Below I list links to some of my code that I use connection pooling.
Project Example (Called Sand at moment)
My ParseCsv2Sql System Use
If your wondering what the heck this is. Well I got sick and tired of dealing with jdbc connections to multiple database types at one time. So I wrote this library of code, to deal with it using guice dependency injection I can run my sql connections quite effectively. The below snippet shows the generation of a connection, which simplifies it for me to access a database. If you browse the code of the parsecsv2sql, which grew to more than parsing csv automatically. it can run the mysql system, in and out of tomcat. I can use pooling inside tomcat. Or I can use pooling inside my own application.
- context.xml - Another context setup example in my parseCsv2sql library
- My Connection Systems source - I use this library to control all my JDBC connections when I use mysql and GWT. It can pool int and out of tomcat.
Using the ParseCsv2Sql code. My Connection Systems source
// paired down code example.
String serverInfo = getServletContext().getServerInfo();
Context context = DatabaseData.initContext();
DatabaseData dd = DatabaseData(DatabaseData.TYPE_MYSQL, "ark_home", "3306", "test", "test#", "test"); // when using context, the parameters here don't matter.
dd.setServletContext(serverInfo, context, "jdbc/contextNameInContextXml"); //set context, authorization parameters are retrieved from context.xml file.
// dd.setReadOnly(true); // for slaves, no write
Connection conn = dd.getConnection(); // to use the connection
...
Some of My Pooling Challenges
- Its very important to escape your strings before inserting or updating, b/c it can kill a thread.
- You can hit OS limits like open sockets and aborted connection failures. More on that
- I recommend threading queries to mysql, page request, like mapping in GAE. I do this in ParseCsv2Sql to speed processing
- On a windows machine, it may look like a denial of service (DOS) attack and you will have to change the registry. I've wrote about this somewhere too. Beware for aborted connections.
Tomcat Setup Notes
When you use tomcat, the servlet container has to be given access outside of its container.
- gwtTomcat - about tomcat setup, install and more.
- Fix this problem: java.security.AccessControlException access denied (java.net.SocketPermission localhost resolve)
- More information on this here: gwtTomcat
You will need to change Tomcat configuration.
#for debian/ubuntu
# I added to /etc/tomcat5.5/policy.d/04webapps.policy
# Or make your own /etc/tomcat5.5/policy.d/myGWTPolicys.policy
# you can also change file to: file:/apps/directory/yourGWTTomcatApps/-
grant codeBase "file:${catalina.home}/webapps/-" {
permission java.net.SocketPermission "192.168.12.81:3306", "connect";
};
My Redundancy Notes
I recommend at least setting up two mysql servers at least, one master and one slave, one for fail over to in the case of one of the failures. If your really worried about it, I would switch to google app engine. SQL has its limitations with large datasets and huge amount of hits all at once.
MySql Code Snippets Below
MySQL Connection
Connect to db. Don't forget to change the parameters.
/**
* db conn
*
* Make sure you add a reference library (external jar in build path) JDBC Connector -
* You will see I put it in /opt/gwt-linux/mysql-connector-java-5.0.8-bin.jar
*
* @return Connection
*/
private Connection getConn() {
Connection conn = null;
String url = "jdbc:mysql://192.168.12.81:3306/";
String db = "hostdb";
String driver = "com.mysql.jdbc.Driver";
String user = "";
String pass = "";
try {
Class.forName(driver).newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url+db, user, pass);
} catch (SQLException e) {
System.err.println("Mysql Connection Error: ");
e.printStackTrace();
}
return conn;
}
Get Row Count
Use this to size an array for your values
/*
* get row count
*/
protected static int getResultSetSize(ResultSet resultSet) {
int size = -1;
try {
resultSet.last();
size = resultSet.getRow();
resultSet.beforeFirst();
} catch(SQLException e) {
return size;
}
return size;
}
Query
Query statement, using connection, statement, resultset, and get record. Setup a connection first.
String query = "SELECT * FROM table";
try {
Connection conn = this.getConn();
Statement select = conn.createStatement();
ResultSet result = select.executeQuery(query);
while (result.next()) {
String s = result.getString(1);
}
select.close();
result.close();
conn.close();
} catch(SQLException e) {
System.err.println("Mysql Statement Error: " + query);
e.printStackTrace();
}
Update / Insert
Update and Insert into database, MySql or MsSql or whatever database you have. Setup a connection first
//save session data to table
String query = "INSERT INTO `session` (UserID, SessionID, LastAccessed, DateCreated) " +
"VALUES ('" + this.dbUserID + "', '" + this.SessionID + "' , UNIX_TIMESTAMP(), UNIX_TIMESTAMP());";
try {
Connection conn = this.getConn();
Statement update = conn.createStatement();
update.executeUpdate(query);
//get last id
ResultSet result = update.getGeneratedKeys();
if (result != null && result.next()) {
int rsId = result.getInt(1);
}
result.close();
update.close();
conn.close();
} catch(SQLException e) {
System.err.println("Mysql Statement Error: " + query);
e.printStackTrace();
}
Escape String For SQL
Apache has some cool functions for escaping strings. This can only be run on server side, preparing the sql data for insert. You don't have to unescape b/c it comes out as is. It just allows you to save with "'" '"' in the string.
- Right Click Project > Build Path > Configure Build Path > Libraries > Add External Jar
- Link to Apache jar documentation: Classes Documentation
- Download Jar: Goto Download
import org.apache.commons.lang.StringEscapeUtils;
/**
* escape string to db
* @param s
* @return
*/
protected static String escapeForSql(String s) {
String rtn = StringEscapeUtils.escapeSql(s);
return rtn;
}Improved Escape method for fixing data before its stuck into db
/**
* escape string to db
*
* remove harmfull db content
* remove harmfull tags
*
* @param s
* @return
*/
protected static String escapeForSql(String s) {
//remove harmful HTML tags
if (s != null) {
s = s.replaceAll("(?i)</?(HTML|SCRIPT|HEAD|CSS)\\b[^>]*>", "");
}
String rtn = StringEscapeUtils.escapeSql(s);
//escape utils returns null if null
if (rtn == null) {
rtn = "";
}
return rtn;
}
Trasporting Data / Recordsets Around
This is an example of how I transport data from server to client and back. This is my favorite way and by far the most efficient and easiest way to do it.
I use a class like this to store MySQL record set data into an object array. This makes it very easy to pass around data. Now if you use private methods and/or fields in the class that goes from server to client, you will have have to change Tomcat security. See more in gwtTomcat.
/**
* I use this class to store my mysql recordset in an object that is an array.
* This will give an example of how I pass data from the server to client in an object,
* one of my favorites for its simplicity.
*
* @author branflake2267
*
*/
public class BibleData implements IsSerializable {
// fields to store data
public String book;
public int howManyChapters;
public int howManyVerses;
/**
* constructor
*/
public BibleData() {
// nothing to do when transporting
}
}
This is an example of how I spool the data into the Object Array from MySQL.
public BibleData[] getBibleInfo() {
String query = "SELECT bid, en FROM book;";
// prepare for rpc transport
BibleData[] bibleData = null;
try {
Connection connection = getConn();
Statement select = connection.createStatement();
ResultSet result = select.executeQuery(query);
// init object into the size we need, like a recordset
int rsSize = getResultSetSize(result); //size the array
bibleData = new BibleData[rsSize];
int i = 0;
while (result.next()) {
// init each object in the array !!!!
bibleData[i] = new BibleData(); // <-THIS IS CRITICAL TO REMEMBER!!!! init each array with the object type (I forget to do this so often)
int bid = result.getInt(1);
bibleData[i].book = result.getString(2);
bibleData[i].howManyChapters = getHowManyChapters(bid);
bibleData[i].howManyVerses = getHowManyVerses(bid);
i++;
}
// clean up
result.close();
connection.close();
} catch(Exception e) {
System.err.println("Mysql Statement Error: " + query);
e.printStackTrace();
}
// return the array
return bibleData;
}
Feed Back Widget
The feedback widget in the repository gathers up the inputs and sends them to the server and inserts them into MySql.
My Feedback Table
CREATE TABLE `gwt_feedback` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserID` int(11) DEFAULT '0',
`FromEmail` varchar(150) DEFAULT NULL,
`FromName` varchar(150) DEFAULT NULL,
`Subject` varchar(250) DEFAULT NULL,
`Message` text,
`Suggestion` tinyint(1) DEFAULT '0',
`Comment` tinyint(1) DEFAULT '0',
`Problem` tinyint(1) DEFAULT '0',
`Other` tinyint(1) DEFAULT '0',
`Post` tinyint(1) DEFAULT NULL,
`DateCreated` datetime DEFAULT NULL,
`LastUpdated` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM
Use Eclipse to download the files from the SVN repository. I try my best to explain it in the eclipse wiki.
I added an RPC example to the MySQLConn eclipse project. You can download or update via SVN source code. http://gawkat.com/MySQLConn - example of what it does.
Very good example. I have long searched for exactly something! I will try it on my project.
If you prefer to use Netbeans instead of Eclipse you can find a Example on this page: http://www.panticz.de/Netbeans_GWT4NB_MySQLConn_Example
I fetch data by getting a record from mysql (and its fields/columns), and stick that record (and its field/columns) in a object (or class), to tranpsort to the client through rpc. Having said that, I transport multiple records with making the same object an array. So "MyEmployee? employee" or MyEmployee? emploees.
Is this the unique way to extract data from a database in GWT?
No storing data in an object and transporting it to the client in an object, like storing data in xml.
Hi, I found your example invaluable to help me start my first GWT/GWT-Ext project. Eventually I want to do similar task, getting names etc from mysql db. So far I got as far as implementing the RPC, but on the server side I still create dummy resultset to send back to client. I can't get the JDBC to work. I am using Eclipse and added mysql-connector-java-5.1.7-bin.jar to build-path but I can't get project to recognize. Any clues what I can do?
Make a class outside the gwt project to test your jdbc connection to start with. When ever I find problems within my gwt project on the server side, I step outside the project and test.
For example: Goto project > test > create new class with main method Goal: Right click on class > debug as java application > run jdbc connection test
Here is one I built to test my jdbc connection. I'll test building the objects that store my results that I then use the RPC system to send back the object to the client. The goal here is to try out whatever code your having troubles with in a main method that can be debugged as a java application. I always have problems my self with setting up JDBC b/c of all connection variables needed.
Goal: Walk through each step and make sure it works. 1. Make a class with main method so you can run the debugger just on it.
2. Write a jdbc connection statement inside that main method 3. right click on your class > Debug as Java application > It runs it in dubugger Be sure: 1. Jar is added to build path 2. The Connection class imports the correct jar 3. break/pause debugger just after Connection Object during query 4. This will tell you if you actually have a connection to use for seting up a query statementThank you for this grat Example! It works great in "Hosted Mode" on my local machine! But it only works in Hosted Mode when in eclipse project properties the "Use google AppEngine?" checkbox is UNchecked!!!
I removed the method "getServerMysqlOn" in the DB_Conn class (thinking this will not work in the appengine) But i get an error with the mysql jdbc driver: java.security.AccessControlException? also in DB_Conn class in the line you define the following: Class.forName(driver).newInstance();
Has anybody any idea to fix this? Or is there another way to make this running in appengine environment?
Thanks a lot to the author! Lars.
Thanks for your demo!
Sean
the various http://gawkat.com/MySQLConn/ links don't seem to be working any more (chrome on windows vista home premium 64 bit)
Can you fix the link for http://gawkat.com/MySQLConn/? Thanks!
com.google.gwt.user.server.rpc.UnexpectedException?: Service method 'public abstract web.client.AuthorizationResult? web.client.AuthorizationService?.login(java.lang.String,java.lang.String)' threw an unexpected exception: java.lang.ExceptionInInitializerError?
Caused by: java.lang.ExceptionInInitializerError? Caused by: java.lang.ExceptionInInitializerError? Caused by: java.security.AccessControlException?: access denied (java.lang.RuntimePermission? modifyThreadGroup)This example is far to big. It costs to much time to study build an own example. Why not a simple Example where i can send a String query to the server and receive DataTable? as an answer?
It would be dangerous for me to send the query straight from the client to the server due to so many sql injections that could be tried and messed with querying the database table. The new features with GWT looks like it has data binding and may be simpler although I haven't tried them yet. I'll look at making a simpler example and see what I come up with in the near future. Thanks for your feedback. :)
can we run it on google apps engine? sorry for my n00b question...
No this won't run in GAE. You'll have to use the JDO to store your data. The JDO datastore is very fast in GAE, but I have to admit, its taken me a bit to get used to it, but I am very happy with it now.
In development mode, my project runs OK when I start it, but when I try to "reload server" (from the development pane in Eclipse), I get an error: "No suitable driver found for jdbc:mysql://localhost:3306..."
As you explained, I put the MySQL-J-Connector jar in both my war/WEBINF/lib folder and in the build path, and it works OK until I try to reload the server...
Download the mysql jconnector or jdbc driver and stick it in your lib folder.
I did just that, and it really worked, but when I tried to "reload server", it gave me that error again.
not sure where you hit reload server. After you run it once, hit the debug icon at the top carrot that points down, and it should be in the list to run again.
Ah, your in the standalone. Close that, and debug agian.
http://code.google.com/p/gwt-examples/wiki/AskMeAboutGwt - You can ask me questions here on the wiki
I'm doing right that, but is it ok to get GPRS data after each 30 sec...?
i have problem :
onModuleLoad() threw an exception
Exception while loading module org.gonevertical.demo.client.DemoMySqlConn?. See Development Mode for details. java.lang.reflect.InvocationTargetException? at sun.reflect.NativeMethodAccessorImpl?.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl?.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl?.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.google.gwt.dev.shell.ModuleSpace?.onLoad(ModuleSpace?.java:396) at com.google.gwt.dev.shell.OophmSessionHandler?.loadModule(OophmSessionHandler?.java:193) at com.google.gwt.dev.shell.BrowserChannelServer?.processConnection(BrowserChannelServer?.java:510) at com.google.gwt.dev.shell.BrowserChannelServer?.run(BrowserChannelServer?.java:352) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.NullPointerException? at org.gonevertical.demo.client.DemoMySqlConn?.onModuleLoad(DemoMySqlConn?.java:33) ... 9 more
Pleace your solution... thanks
I am having the same exception at onModuleLoad() threw an exception.
I think I have everything setup as described in this example; however, I am unable to connect to MySQL. Following exception is thrown: Mysql Connection Error: java.sql.SQLException: URL is not in the correct format: jdbc:mysql://localhost/Bible
I came across few sites describing that you cannot connect to MySQL since it is not supported by default container. I tested connection in unit test and it is working fine.
I tried process described in : http://whatmovesmike.blogspot.com/2009/04/howto-external-tomcat-in-gwt16-elcipse.html This is actually works but I am unable to get debugger working...
Any alternatives ???
I was having the same problem, but for me, it was pilot error - the sandbox restrictions of GAE. Is it possible you have the GAE libs running in Tomcat somehow?
same problem URL is not in the correct format
I think it really is important saying that in order to connect to any external database, your project can't use AppEngine? (simply disable it from the project properties in Eclipse).