Tom Daly's Weblog Tom Daly's Weblog

Connecting to MySQL from the “free” Sun Application Server 8.1 Platform Edition (PE) J2EE server

Sun has been offering the "platform edition" of our J2EE 1.4 application server free for download and use for sometime now and I am writing this blog from the Sun JavaOne conference in San Francisco where Sun has just announced the open sourcing of the next version of our J2EE application server i.e. the Java EE 5.0 version. See project http://glassfish.dev.java.net/ for details and to download early versions of the code and also check out Jim Driscoll's blog.

Given that the application server is free and open source and the operating system (Solaris 10) is free and open source it seems to follow that many J2EE developers and users will be or are already interested in using open source and free databases with this free combination of application server and operating system. We have been running the SPECjAppServer2004 J2EE benchmark with the Sun application server 8.1 and MySQL 5.0 and we have learnt much about running the app server with MySQL and with Connector/J, so I thought we would share some of the config details which come from this testing to help you get your application running on this same combination. If you are interested in running either Postgres or Derby in conjunction with Sun application server 8.1 then check out Rajesh's blog entry for Postgres and Lance's blog for Derby.

Versions

I suggest using MySQL 5.0.4 (or later) of the MySQL database and version 3.1.8 (or later) of the Connector/J JDBC driver. It is important to note that the Sun application server 8.1 passes the compatibility test suite when using the above versions and this is a big deal because it protects your investment by keeping the platform consistent and your code portable. There are also a number of performance enhancements in the above releases and we have especially noticed significant improvement in the internal SPECjAppServer2004 results due to improvements by MySQL in the Connector/J driver. Also w.r.t versions don't forget if you already have app server 8.1 with JDK 1.4 then a move to J2SE 5.0 will likely boost you performance significantly.

Configuring the connection for MySQL 5 from the application server using the admin console

1. Under Resources, select JDBC and on right hand side frame, select Connection Pools

2. Click on New to add create a new connection pool

3. Provide some name such as mysql-pool and select Datasource type as javax.sql.ConnectionPoolDataSource

4. 4. Click Next and enter the Datasource Classname as com.mysql.jdbc.jdbc2.optional.MysqlDataSource

5. 5. Add the following properties to the connection pool:

6. DatabaseName <your-db-name>

7. port <your-selected-port-number> default port is 3306

8. user <DB-User-Name>

9. Password <DB-User-Password>

10. ServerName <DB-Server-Name>

11. Save the settings and click on Ping. If DB server is running and connection can be made, Ping will succeed. Please make sure you have copied the mysql-connector-java-3.1.8-bin.jar jdbc driver in $J2EE_HOME/domains/<domain-name>/lib/ext directory.

Example domain.xml

This is entire connection pool entry for the current testing we are doing with SPECjAppServer2004 (see www.spec.org/SPECjAppServer2004 for more details on the workload and check out the Connector/J documentation for an explanation of the properties.

<jdbc-connection-pool

connection-validation-method="table" datasource- classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false"
is-isolation-level-guaranteed="false" max-pool-size="80" max-wait-time-in-millis="90000"
name="SpecJPool" pool-resize-quantity="4" res-type="javax.sql.DataSource" steady-pool-size="80">

<property name="serverName" value="pdb"/>

<property name="portNumber" value="3306"/>

<property name="User" value="spec"/>

<property name="Password" value="spec"/>

<property name="DatabaseName" value="specdb"/>

<!-- Note : the following are tuning parameters not essential for connectivity

<property name="cachePrepStmts" value="true"/>

<property name="prepStmtCacheSize" value="512"/>

<property name="useServerPrepStmts" value="false"/>

<property name="alwaysSendSetIsolation" value="false"/>

<property name="useLocalSessionState" value="true"/>

<property name="elideSetAutoCommit" value="true"/>

<property name="useUsageAdvisor" value="false"/>

<property name="useReadAheadInput" value="false"/>

<property name="useUnbufferedInput" value="false"/>

-->

</jdbc-connection-pool>