How do I access MySQL from a web application?
Author: Deron Eriksson
Description: This Java tutorial describes using JNDI to access MySQL in a web application.
Tutorial created using: Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 1.5.1 || Tomcat 5.5.20 || MySQL 5.0.27


Page:    1 2 3 >

Utilizing a JNDI resource is the J2EE standard method of accessing a data source (ie, a pool of JDBCW connections) for JavaSW web applications. The TomcatSW "JNDI Resources HOW-TO" document (the 5.5 version is currently located at http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html ) describes the details of this process. There are various ways to configure things, but in a typical approach, you need to perform the following tasks:

  1. Add a database <Resource> to your web application's <Context> entry in Tomcat's server.xml
  2. Add a resource reference to your web application's web.xml file specifying the server.xml-specified database resource to use
  3. Write your Java code to utilize the data source you referenced in web.xml

1.

An example of Tomcat's server.xml after the required changes is shown below. In another tutorial, I created a tomcat-demo web application project, and we put a Context entry in our server.xml file for 'tomcat-demo'. Within Context, we can add a Resource entry our MySQLW databaseW. In another tutorial, I created an 'employees' table in a test MySQL database called 'testdatabase' that we can see referenced via the Resource url attribute. The 'testdatabase' MySQL database gets mapped to the name 'jdbc/TestDB'.

Context/Resource entry from Tomcat's server.xml for the 'tomcat-demo' web application:
        <Context docBase="C:\projects\workspace\tomcat-demo\web"
        	path="/tomcat-demo" reloadable="true">
        	<Resource name="jdbc/TestDB"
        		auth="Container"
        		type="javax.sql.DataSource"
        		maxActive="100"
        		maxIdle="30"
        		maxWait="10000"
        		username="testuser"
        		password="testpassword"
        		driverClassName="com.mysql.jdbc.Driver"
        		url="jdbc:mysql://localhost:3306/testdatabase" />
        </Context>

In the above Resource entry, notice that the user and password used to connect to the database are specified as attributes of Resource. This is quite useful, since it means that this information doesn't need to be contained within your web application. As a result, you can deploy your web application to different environments using the same war/ear file, since you don't need to customize the database information within your war/ear file for each environment. Additionally, it means that developers can write code for a development environment and then deploy their web application to a production environment, but the developers don't need to know the production user/password used to connect to the database. Personally I'm in favor of allowing trusted developers to have access to production databases so that they can debug particular types of problems. As an example of this, how is a developer supposed to track down a special-character data entry issue in an application if the developer can't access the database to see the raw data that made its way into the database for that row?

The Resource entry in server.xml is highly configurable, as you can see by the number of attributes shown in the example above. For more Resource configuration information, I recommend the Connector/J manual that ships with the Connector/J library download.


(Continued on page 2)

Page:    1 2 3 >