Using MySQL with Web Server 7

A while ago I worked for a bit on some Java servlet code which needed to talk to a MySQL backend database. Diverging a bit from my usual topics I thought I’d write this down in case it is useful for others (or at least it’ll be useful for me next time I need to do the same).

This configuration assumes Web Server 7. In server.xml I configured a JDBC resource for mysql:

  <jdbc-resource>
    <jndi-name>jdbc/mysql</jndi-name>
    <datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</datasource-class>
    <min-connections>1</min-connections>
    <max-connections>5</max-connections>
    <property>
      <name>password</name>
      <value>password-here</value>
    </property>
    <property>
      <name>user</name>
      <value>jyri</value>
    </property>
    <property>
      <name>url</name>
      <value>jdbc:mysql://boqueron/dbname</value>
    </property>
  </jdbc-resource>

Then I copied mysql-connector-java-3.1.12-bin.jar into $INSTANCE/lib (where $INSTANCE is the top level directory of the specific instance, note that you may need to create the lib directory there if it hasn’t been needed before).

In the web application itself, in web.xml, I configured:

 <resource-ref>
    <description>JDBC Connection Pool</description>
    <res-ref-name>jdbc/mysql</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

And in sun-web.xml:

  <resource-ref>
    <res-ref-name>jdbc/mysql</res-ref-name>
    <jndi-name>jdbc/mysql</jndi-name>
  </resource-ref>

That’s it for the configuration. In my servlet code:

    Context initContext = new InitialContext();
    Context webContext = (Context)initContext.lookup("java:/comp/env");

    DataSource ds = (DataSource) webContext.lookup("jdbc/mysql");
    Connection conn = ds.getConnection();

    Statement stmt = conn.createStatement();
     ....