Connecting to a database from a java web application

In these days of numerous java frameworks, we often forget or don’t care about some simple things. Though this post might not be very interesting to most of you, it might help some of those to whom this might be the information they are looking for. So bear with me.

Pre-requisites:

- Latest version of Tomcat (currently 6.0.16)

- A database (In my case, it’s mysql 5.0)

- Appropriate jdbc “driver” jar file for your database. (In my case, it’s mysql jdbc driverr)

Step 1:

- Copy the “jdbc driver” jar file to the TOMCAT_HOME/lib directory. (Usually, C:\Program Files\Apache Software Foundation\Tomcat 6.0.x\lib)

Step 2:

- Open the “context.xml” file of your web application. It can be found under META-INF directory in your web app.

- Your context.xml file might look something like this, initially:

<?xml version=”1.0″ encoding=”UTF-8″?>
<Context path=”/CrickBoss-JSF”/>
Step 3:

- Update your context.xml to register your “jdbc driver”, like this:

<?xml version=”1.0″ encoding=”UTF-8″?>
<Context path=”/CrickBoss-JSF”>
<Resource name=”jdbc/CrickBossDB” auth=”Container”
type=”javax.sql.DataSource” username=”root” password=”mypassword”
driverClassName=”com.mysql.jdbc.Driver”
url=”jdbc:mysql://127.0.0.1:3306/CrickBossDB”
maxActive=”8″ maxIdle=”4″/>
</Context>

Things to note here:

- Give appropriate resource name for your datasource. In my case, it’s “jdbc/CrickBossDB” .

- Use appropriate username and password with respect to your database.

- Provide appropriate driver name in the driverClassName attribute. In my case, it’s “com.mysql.jdbc.Driver”

- Enter your database url correctly. In my case, it’s “jdbc:mysql://127.0.0.1:3306/CrickBossDB”

(Note: Refer your database driver’s documentation to find out the jdbc url. Microsoft SQL Server, for example, expects the string “databaseName” in the jdbc url.)

Don’t worry about other settings. You need not change it at this point of time.

Step 4:

- Register the jdbc datasource in “web.xml”. (This file is located under “WEB-INF” directory.), by adding the following code:

<resource-ref>
<res-ref-name>jdbc/CrickBossDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Note:

Give your datasource name in <res-ref-name> as defined in your context.xml. In my case, it’s “jdbc/CrickBossDB”

Step 5:

We are almost there. It’s now time to use all the configurations we have made.

Now lookup the jdbc datasource we have defined from your servlet, like this:

InitialContext context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup(“java:/comp/env/jdbc/CrickBossDB”);

Once you get the datasource, creating a connection is a piece of cake.

Connection connection = dataSource.getConnection();

That’s it. Now it’s up to you to use this connection and execute some queries.