Using a Network Derby Database in Java EE

This doc assumes that a derby database has been created for you and that you have a username and password that grants you full access to that database.

Command-Line Tool

Issue the terminal commands:

  ij
  ij> connect 'jdbc:derby://host:port/dbName;user=yourUserName;password=yourPassword;';

Once connected, you can issue commands to see the tables:

  show tables;

And if there is a table named Contact then you can see its structure using the command:

  describe Content;

At this level, you can issue any valid SQL statement and it will be executed. Here are a few examples:

  Select * from Content;
  
  Create table Student (YorkID INT, NAME VARCHAR(20));

  Insert into Student  (YorkID, NAME) VALUES (2080, 'Adam');

You can find many examples of SQL statements in the Derby documentation and on the Web. Derby supports most of the SQL-92 features.

Eclipse Setup

The jdbc driver for Derby, derbyclient.jar must be on the build path of your dynamic web project. Rather than adding it to each project, it is better to add it to the server's execution environment as this makes it available to all your web projects. To do that, make sure this jar is in your Tomcat's lib folder. If not, you can download it from this link.

Project Setup

Creating a database connection is a slow, resource-intensive operation, and in a typical webapp, a large number of users connect simultaneously and each requires a very short database connection. In order to avoid creating unnecessary connections or prematurely closing them, properly designed web applications use connection pooling to manage these connections. Tomcat 7 has a middle-ware layer capable of doing that. You activate it by creating the file context.xml in the WebContent/META-INF folder and storing the following content in it (assuming the database URL, name, and credentials as in the command-line example):

  <?xml version="1.0" encoding="UTF-8"?>
  <Context privileged="true" reloadable="true">
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <Manager pathname=""/>
    <Resource name="jdbc/CSE" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              type="javax.sql.DataSource"
              driverClassName="org.apache.derby.jdbc.ClientDriver"
              url="jdbc:derby://host:port" 
              username="yourUserName" password="yourPassword"/>    
    <ResourceLink global="jdbc/CSE" name="jdbc/CSE" type="javax.sql.DataSource"/>
  </Context>

In Your Java Code

Once everything is set up, you will need to have something similar to the following in your model's constructor:

  this.dataSource = (DataSource) (new InitialContext()).lookup("java:/comp/env/jdbc/CSE");

where dataSource is an attribute of the model:

  private DataSource dataSource;

After wards, whenever you need to access the database, you would write:

  Connection con = this.dataSource.getConnection();
  Statement s = con.createStatement();
  // do something with s, such as:
  // ResultSet r = s.executeQuery(q);
  con.close();

It is critical that you close the connection after each use. The pool listens for the close event so that it can cancel it and simply return the connection (if not timed out) to the pool.

Roumani's Library - Last updated Oct 2012