Lance Andersen's Blog

Using Apache Derby with the J2EE SDK

Derby is an Open Source project based on the IBM contribution of the Cloudscape RDBMS to Apache. Cloudscape 10 and Derby are currently identical with the exception of what is packaged in the two products for extras.

If you download Cloudscape 10, it includes the DB2 JDBC Universal Driver which is needed for the Cloudscape/Derby Network Server. If you download Derby, you will need to download the DB2 JDBC Universal Driver seperately.

Installing the needed Derby/Cloudscape jar files

To use Derby/Cloudscape, copy the following jar files to $J2EE_HOME/domains/domain1/lib/ext and reboot the J2EE SDK:

For the DB2 Universal Driver:
derby.jar,derbynet.jar,db2jcc.jar,db2jcc_license_c.jar

For the Derby Embedded Driver:
derby.jar

Creating Derby Connection Pools

You can use either the DB2 JDBC Universal Driver or the Derby Embedded Driver when you create your Connection Pools. If you require XA support, currently you must use the Derby Embedded Driver. The work for the Derby Network Server to Support XA is underway, but not yet complete. If you must use the Application Client Container as well as other J2EE containers to access the same database, you need to use the DB2 JDBC Universal Driver and the Derby Network Server.

To create a connection Pool using the DB2 JDBC Universal Driver for Derby, you would specify the following values for your connection pool:

DataSource: com.ibm.db2.jcc.DB2DataSource
Properties:

- databaseName
- serverName
- user
- password
- driverType=4
- portNumber=1527 (default port for the Network Server)

To create a connection pool using the Derby Embedded driver, you would need to specify the following values for your connection pool:

DataSource:org.apache.derby.jdbc.EmbeddedDataSource
Properties:
- databaseName (path to your Derby Database)
- user
- password

Using Derby/Cloudscape with the J2EE SDK CMP container

Currently the J2EE SDK CMP container does not know about Derby/Cloudscape. However Most EJBQL queries work fine and by creating a file called .tpersistence.properties in $J2EE_HOME/domains/domain1/config you will have even
greater success.

The entries in the .tpersistence.properties file for Derby are:

database.apache_derby.QUOTE_CHAR_START=
database.apache_derby.QUOTE_CHAR_END=
database.apache_derby.QUOTE_CHAR_END=
database.apache_derby.SUBSTRING=SUBSTR
database.apache_derby.SUBSTRING_FROM=,
database.apache_derby.SUBSTRING_FOR=,
database.apache_derby.CHAR_LENGTH=LENGTH
database.apache_derby.POSITION=LOCATE
database.apache_derby.POSITION_SEP=,
database.apache_derby.POSITION_SEARCH_SOURCE=true
database.apache_derby.RTRIM=rtrim
database.apache_derby.STRING_CONCAT=||
database.apache_derby.SQRT=SQRT
database.apache_derby.ABS=ABS
database.apache_derby.MOD_FUNCTION_NAME=MOD
database.apache_derby.CONCAT_CAST=VARCHAR

Derby is a powerful Open Source Database which I think you will find works quite well with the J2EE SDK

Comments

• One more important thing.

  When specifying "User", make sure you use the same name as the schema name you created your tables in or else you will find that it returns SQLException: Table does not exist.

  If your schema name is LANCEA, then user username to log into the DB should be LANCEA (ignore the case).

  Posted by: agricfowl on March 04, 2005 at 08:46 AM

• You can also leave off the user and password and the default schema will be used when accessing the database (and assuming you did this when you created your tables).

  otherwise you will need to issue a set schema command

  
  thanks for pointing this out

  Posted by: lancea on June 08, 2005 at 10:29 AM

This work is licensed under a Creative Commons License.