Oracle UCP with Java DB (Derby)

Creating some JSF 2.0 demos and was told if I could use Sun Java DB (derby) rather then Oracle for the back end database. Thought it would be a chance to use some other DB and was surprised with how easy it was to install, setup and create a database with. Here is how I ended up settting up a UCP Connection Pool against a Java Db (Derby) database.

1. Create the database itself

> java -jar %DERBY_HOME%\lib\derbyrun.jar ij
> connect 'jdbc:derby:firstdb;create=true';

2. Create an SQL file to use the classic DEPT/EMP tables.

SQL File to create classic DEPT/EMP Tables

 
drop table emp;
drop table dept;

AUTOCOMMIT OFF;

CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13));

ALTER TABLE DEPT
  ADD CONSTRAINT DEPT_PK Primary Key (DEPTNO);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
DEPTNO INTEGER);

ALTER TABLE EMP
  ADD CONSTRAINT EMP_PK Primary Key (EMPNO);

INSERT INTO EMP VALUES
       (7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
       (7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-21', 1600,  300, 30);
INSERT INTO EMP VALUES
       (7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30);
INSERT INTO EMP VALUES
       (7566, 'JONES',  'MANAGER',   7839, '1981-04-02',  2975, NULL, 20);
INSERT INTO EMP VALUES
       (7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
       (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01',  2850, NULL, 30);
INSERT INTO EMP VALUES
       (7782, 'CLARK',  'MANAGER',   7839, '1981-06-09',  2450, NULL, 10);
INSERT INTO EMP VALUES
       (7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
       (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
       (7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08',  1500,    0, 30);
INSERT INTO EMP VALUES
       (7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
       (7900, 'JAMES',  'CLERK',     7698, '1981-12-03',   950, NULL, 30);
INSERT INTO EMP VALUES
       (7902, 'FORD',   'ANALYST',   7566, '1981-12-03',  3000, NULL, 20);
INSERT INTO EMP VALUES
       (7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

COMMIT;

ALTER TABLE EMP
  ADD CONSTRAINT EMP_FK Foreign Key (DEPTNO)
  REFERENCES DEPT (DEPTNO);

COMMIT;

Ant Build File Used to Load the Data

<?xml version="1.0"?>

<project default="buildschema" name="deptemp" basedir=".">

<!-- Set Properties -->
<property file="build.properties"/>

<!-- Targets -->

<target name="init">
  <tstamp/>
  <delete file="deptemp.out"/>
</target>

<target name="buildschema" depends="init">
  <java classname="org.apache.derby.tools.ij"
        output="deptemp.out"
        failonerror="true"
        dir="."
        fork="true">
   <classpath>
       <pathelement path="${derby.home}/lib/derby.jar"/>
       <pathelement path="${derby.home}/lib/derbytools.jar"/>
       <pathelement path="${derby.home}/lib/derbyclient.jar"/>
   </classpath>
   <sysproperty key="ij.driver" value="org.apache.derby.jdbc.ClientDriver"/>
   <sysproperty key="ij.database" value="${jdbcurl}"/>
   <arg value="deptemp.sql"/>
  </java>
</target>

</project>

Ant Properties File

# derby.home
#
# This property is only required if using ANT to run this demo. It will
# use the property to build a classpath required to run the utilities

derby.home=C:\\pas\\software\\derby\\10530\\JavaDB

# jdbcurl , assumes server network connection

jdbcurl=jdbc:derby://localhost:1527/firstdb;create=true

3. Start a network server which my clients will connect to to access the database

D:\jdev\derby\pas\databases>java -jar D:\jdev\derby\10530\JavaDB\lib\derbyrun.jar server start
2010-07-04 22:14:37.187 GMT : Security manager installed using the Basic server security policy.
2010-07-04 22:14:38.109 GMT : Apache Derby Network Server - 10.5.3.0 - (802917) started and ready to accept connections on port 15
27

4. Create a quick client to verify UCP using Java Db (Derby).

Note: Need to add ucp.jar and derbyclient.jar to the classpath

 
package pas.au.ucp.standalone;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

public class DerbyUCPTest
{
 private PoolDataSource pds = null;

 public DerbyUCPTest() throws SQLException
 {
   pds = PoolDataSourceFactory.getPoolDataSource();
   pds.setURL("jdbc:derby://localhost:1527/firstdb");
   pds.setConnectionFactoryClassName("org.apache.derby.jdbc.ClientDriver");
 }

 public void run () throws SQLException
 {
  
   Connection  conn = null;
   Statement   stmt = null;
   ResultSet   rset = null;
  
   try
   {
     conn = pds.getConnection();
  
     System.out.println("Got Connection to DERBY database server from UCP Pool");

     stmt = conn.createStatement();
     rset = stmt.executeQuery("SELECT empno, ename from EMP");
    
     while (rset.next())
     {
       System.out.println
         (String.format("Empno#: %s, Ename: %s",
                        rset.getInt(1),
                        rset.getString(2)));
     }
   }
   catch (SQLException se)
   {
     se.printStackTrace();
   }
   finally
   {
     if (rset != null)
     {
       rset.close();
     }
    
     if (stmt != null)
     {
       stmt.close();
     }
    
     if (conn != null)
     {
       conn.close();
     }
   }
  
 }

 public static void main(String[] args) throws SQLException
 {
   DerbyUCPTest derbyUCPTest = new DerbyUCPTest();
   derbyUCPTest.run();
  
 }
}

Output
-------

Got Connection to DERBY database server from UCP Pool
Empno#: 7369, Ename: SMITH
Empno#: 7499, Ename: ALLEN
Empno#: 7521, Ename: WARD
Empno#: 7566, Ename: JONES
Empno#: 7654, Ename: MARTIN
Empno#: 7698, Ename: BLAKE
Empno#: 7782, Ename: CLARK
Empno#: 7788, Ename: SCOTT
Empno#: 7839, Ename: KING
Empno#: 7844, Ename: TURNER
Empno#: 7876, Ename: ADAMS
Empno#: 7900, Ename: JAMES
Empno#: 7902, Ename: FORD
Empno#: 7934, Ename: MILLER