Using JDBC from servlets

Written by Hani Suleiman

This is an example of how to use a JDBC data-source from within Servlets/JSPs without using EJBs.

Orion allows you to configure your JDBC database connections in a central place which you can use by performing a look up with JNDI. The obvious advantage is that connection details are stored in a central place. Orion also provides some servers to aid the data-sources such as pooling.

It is assumed that you are familiar with JDBC and Servlets. Understanding JNDI is helpful, but not required.

Install JDBC driver

Download the appropriate JDBC driver for your database and place the driver .jar file in [ORION-HOME]/lib. This shall make the contained classes available from any application running in Orion.

Add data-source

Open the file [ORION-HOME]/config/data-sources.xml. This contains the data-source available to your entire application. Each data-source is enclosed in a <data-source> tag.

With recent versions of Orion you will notice there is a data-source already installed for HyperSonic SQL, the lightweight database bundled with Orion.

Have your JDBC connection details to hand, including class name of JDBC driver, url, username and password. The example here uses Sybase as the database, with the following JDBC settings:

As well as JDBC settings, a data-source needs to know a JNDI location to bind to. This is the name that the Servlet accessing the data-source should use. There are actually 3 locations specified which all access the same data-source but Orion provides them with different services.

If all that seems meaningless then don't worry, you don't need to do anything special to use them, other than assign JNDI names to them. These can be anything but it is recommend you follow the naming conventions used in the example below.

To add a data-source with these JDBC settings, add the following to data-sources.xml:

<data-source

  name="Test data-source"

  location="jdbc/TestCoreDS"
  xa-location="jdbc/xa/TestXADS"
  ejb-location="jdbc/TestDS"

  connection-driver="com.sybase.jdbc2.jdbc.SybDriver"
  url="jdbc:sybase:Tds:localhost:4100"
  username="me"
  password="abcdef"

  inactivity-timeout="30"
  class="com.evermind.sql.DriverManagerDataSource"

/>

Notice the final attribute: class="...". This is not the class name of your JDBC driver but that used by Orion to manage connections. Leave it as is.

Save file and restart Orion. Your data-source is now installed.

To access the data-source a JNDI lookup is performed using the name "jdbc/TestDS". Notice how this is in fact the ejb-location - this is so Orion provides all services it possibly can such as transaction management and pooling.

Use data-source from Servlet

Now the data-source has been installed it can be used from anywhere within your Servlets or application-clients. To access it you use the JNDI lookup you specified when installing the data-source.

Three simple steps to obtaining the connection:

1. Obtain the JNDI initial context (javax.naming.InitialContext). This is used to lookup any objects bound to a JNDI location.

   InitialContext ctx = new InitialContext();

2. Lookup the data-source from the context (javax.sql.DataSource).

   DataSource ds = (DataSource) ctx.lookup("jdbc/TestDS");

3. From the data-source, get a connection to the database. (java.sql.Connection).

   Connection conn = ds.getConnection();

At this point you have a standard database connection from which you can perform your usual queries on.

Note: It is important that when you have finished using your connection, you close it so it returns to the pool.

Example JSP

Simple JSP to list all names of staff in a table:

<%@ page import="java.sql.*, javax.sql.DataSource, javax.naming.InitialContext" %>

<h3>Staff</h3>

<%

// Obtain connection
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/TestDS");
Connection conn = ds.getConnection();

// Create query and get results
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT name FROM staff");

// List results
while ( rs.next() ) {
  out.println( rs.getString("name") + "<br>");
}

// Close connection - important!
conn.close();

%>

If you want to test this JSP, execute this SQL before hand:

Copyright © 2007 IronFlare AB