James Selvakumar’s Blog

March 12, 2008

Connecting to a database from a java web application

Filed under: java,jsp/servlets — James @ 5:14 pm
Tags: , , ,

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.

About these ads

10 Comments »

  1. Dude! You Rawk!

    Comment by liambean — March 20, 2008 @ 2:18 pm | Reply

  2. Thanks ,, it was helpful for me

    Comment by Jaya — May 13, 2008 @ 8:51 pm | Reply

  3. dear i want to connect java to mysql
    when i run programm i found folloing error during running

    Exception in thread “main” java.lang.NoClassDefFoundError: org/aspectj/lang/Sign
    ature
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at TestMysql.main(TestMysql.java:9)

    please solv this.

    Comment by hitesh — July 2, 2008 @ 3:58 am | Reply

  4. You hammered right to the marrow !!!. have been looking for this exmaple for a long time !!!

    To help others with a JSP script, use the one below and make necessary slight modification on line DataSource dataSource = (DataSource) context.lookup(”java:/comp/env/jdbc/test”);

    Test.jsp

    Read from mySQL Database

    Following records are selected from the ‘jakartaproject’ table. 

    Sr.
    No.

    Project
    Url
    Address

    Description
    of the project

    .

    <a href=””> 

    .

    <a href=””> 

    Comment by Ngoni Munyikwa — July 20, 2008 @ 9:09 pm | Reply

  5. Oh, the server was jealousy there !!!!, it ran the script!!!, wish i could help

    Comment by Ngoni Munyikwa — July 20, 2008 @ 9:10 pm | Reply

  6. This is exact the kind of help I have been looking for. It took me weeks to discover it right here!! Long live the author!

    Comment by Samuel Oyet — October 22, 2008 @ 4:52 pm | Reply

  7. It worked for me, thou
    Is there another way ,Where I can put the database connection part inside web.xml?

    Comment by Rajul Konkar — March 10, 2009 @ 7:23 am | Reply

  8. Hi it good, can u let know how to connect an desktop application to a web application,
    I mean i have a desktop application which used by a school, i need to connect that to a web portal , which technology we shuold go ahead with..

    Comment by Mahesh — March 21, 2009 @ 8:29 am | Reply

  9. Hi Dear

    Your comment really helped me a lot.
    but I am in big confusion ?
    do we have to create diff-diff connection or we can share a single connection object for the whole web application
    I am using NetBeans6.8 and tomcat 6.0 and as a backend mysql 5.0
    reply me as soon as possible
    Thanks in advance

    Comment by Bittu — April 21, 2010 @ 1:39 pm | Reply

  10. need help (step-by-step) on what to do to connect a java web application to a database. please help. badly needed.

    Comment by javaneo — December 3, 2010 @ 12:12 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: