Monday, 5 July 2010

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

1 comment:

Pas Apicella said...

To load the deptemp.sql file outside of ANT do it as follows.

java -jar %DERBY_HOME%\lib\derbyrun.jar ij
ij version 10.5
ij> CONNECT 'jdbc:derby://localhost:1527/firstdb';
ij> run 'deptemp.sql';