[www.richardsenior.net/projects]
Derby
Derby is a lightweight database written in Java.
More usefully it is a set of java libraries that allow the creation and use of databases within Java applications.
What have I done here?
I'm simply listing out code that covers two ways to create and deploy derby as an embedded database in a web application.
One way, is to create the database at runtime, as a fully writable relational database.
In this instance I do this by using spring to instantiate a singleton which can be used to access or create a derby database.
The other is to create the database at buildtime, and then package it up for use at runtime as a read only database.
In this instance I've listed a bespoke ANT task and the associated ant build script snippets which create and package a read only database at build time
and the associated spring bean that uses that read only database.
It seems to me that if you package the database as a JAR it is always going to be 'read only', but I may be wrong.
Create and use at runtime
Below is listed a spring bean which is injectable into other beans. Pick the bones out of it if it's useful.
Note that when the init method is called (at instantiation) it will attempt to connect to the database. If that fails
it will try to create the database.
The relevant spring config for this bean is listed below :
<![CDATA[
<bean id="derbyDelegate" class="net.richardsenior.derby.DerbyDelegate" init-method="init" destroy-method="destroy" lazy-init="false">
<property name="sqlFile" value="WEB-INF/config/sql/richardsDB.sql"/>
<property name="databaseName" value="richardsDB"/>
</bean>
]]>
The SQL file contains create table statements which create the tables of the database.
<![CDATA[
package net.richardsenior.derby;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.io.Resource;
/**
* A bean which may be injected into other beans to provide access to a DERBY database which
* @author Richard
*/
public class DerbyDelegate {
private static final Log log = LogFactory.getLog(DerbyDelegate.class);
private Connection connection = null;
private String connectionString = null;
//*************************************************************************
//**** SPRING ***
//*************************************************************************
private String databaseName;
private Resource sqlFile;
public Resource getSqlFile() {return sqlFile;}
public void setSqlFile(Resource sqlFile) {this.sqlFile = sqlFile;}
public void setDatabaseName(String databaseName) {this.databaseName = databaseName;}
public String getDatabaseName() {return this.databaseName;}
//*************************************************************************
/**
* Method called by Spring upon instantiation of this bean.
* Attempts to connect to an already existing database, otherwise attempts to create
* and populate a new database
*/
public void init() {
if (this.connection == null) {
this.connectionString = "jdbc:derby:" + System.getProperties().getProperty("user.dir") + System.getProperties().getProperty("file.separator") + getDatabaseName();
if (log.isDebugEnabled()) {log.debug("Attempting to start the DERBY database using connectionString:" + this.connectionString);}
attemptToCreateConnection(this.connectionString);
if (this.connection == null) {
if (log.isDebugEnabled()) {log.debug("Couldn't Start DERBY database, attempting to create it");}
attemptToCreateConnection(this.connectionString + ";create=true;");
if (this.connection != null) {
if (log.isDebugEnabled()) {log.debug("DERBY database created. Attempting to populate it.");}
attemptToCreateSchemaAndTables();
} else {
if (log.isWarnEnabled()) {log.warn("Failed to create DERBY database!");}
}
}
}
}
/**
* Returns this bean's connection object
* @return a connection object which may be instantiated or otherwise
*/
public Connection getConnection() {return this.connection;}
/**
* General method for attempting to create a Connection object
* @param connectionString the connection string that should be used in this attempt
*/
private void attemptToCreateConnection(String connectionString) {
if (this.connection==null) {
try {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
this.connection = DriverManager.getConnection(connectionString);
} catch (Exception e) {
if (log.isDebugEnabled()) {log.debug(e);}
}
}
}
/**
* Derby must be properly closed or the lock files will remain, and when
* a connection attempt is made in future it will be denied.
* This method attempts the proper closure of the open connection
*/
private void attemptToCloseDatabase() {
if (this.connection!=null) {
try {
DriverManager.getConnection(this.connectionString + ";shutdown=true");
this.connection.close();
this.connection=null;
} catch (SQLException e) {
if(e.getErrorCode() != 45000) { // != normal shutdown
if (log.isDebugEnabled()) {log.debug("Abnormal exception whilst attempting to close the DERBY database" + e);}
}
}
}
}
/**
* Utility method that retrieves an SQL file that was injected and
* parses it into a string list
* @return a list of SQL statements
* @throws Exception
*/
private List<String> getSQLStatementsToExecute() throws Exception {
final List<String> sqlStatements = new ArrayList<String>();
final BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(getSqlFile().getInputStream()));
String sqlStatement = "";
while((sqlStatement = bufferedReader.readLine()) != null) {
if(!sqlStatement.equals("") && !sqlStatement.startsWith("--")) {
sqlStatements.add(sqlStatement);
}
}
bufferedReader.close();
return sqlStatements;
}
/**
* Method which loops through a list of SQL statements attempting to execute them.
*/
private void attemptToCreateSchemaAndTables() {
Statement s = null;
try {
s = getConnection().createStatement();
for (final String sqlStatment : getSQLStatementsToExecute()) {
s.execute(sqlStatment);
}
if (s!=null) {
s.close();
s=null;
}
} catch (Exception e) {
if (log.isDebugEnabled()) {log.debug("Failed to read SQL commands from the specified SQL resource " + e);}
}
}
public void destroy() {
attemptToCloseDatabase();
}
}
]]>
Create at buildtime
Below is listed the code for an ant task which creates a database at buildtime, and the relevant snippets of ant build script which use it.
Once you have created this, you simply drop the created jar, and derby.jar into the classpath of your application and use code similar to that above (in the spring bean) to
connect to it. Notice that you'd have to change the connection string from jdbc:derby:databasename form to jdbc:derby:classpath:databasename form.
Also note that this database would be read only.
<![CDATA[
<!-- ant target to create a derby read only database jar using the bespoke task listed below -->
<target name="createDatabase" depends="compile">
<taskdef name="createdb" classname="net.richardsenior.ant.derby.CreateDerbyDatabase">
<classpath>
<pathelement path="${build.buildtime.dir}" />
<path refid="compile.class.path" />
</classpath>
</taskdef>
<createdb databaseName="${derby.database.name}" tempPath="${buildtime.temp.dir}" sqlFilePath="${derby.database.sqlfile}" outputPath="${dist.dir}" createJar="false" />
<jar basedir="${buildtime.temp.dir}" destfile="${dist.dir}/richardsDB.jar" />
</target>
]]>
Notice that I set createJar to false and then build the jar using the ant jar task. This is because the sun jar libraries put whitespace at the end of the manifest file lines, and derby rejects the manifest.
I aren't sure if this is badness on the part of sun, derby, or myself.
<![CDATA[
package net.richardsenior.ant.derby;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.jar.Attributes;
import java.util.jar.JarEntry;
import java.util.jar.JarOutputStream;
import java.util.jar.Manifest;
import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.Task;
/**
* This task creates a derby database using the sent parameters, and if
* the value of createJar is true, it also jars the database up.
* You might want to make this false, and do the jarring in the build script using
* the jar ant task. Partly because there's some bizarreness with the Manifest files.
* @author Richard
*
*/
public class CreateDerbyDatabase extends Task {
private String databaseName;
private String tempPath;
private String sqlFilePath;
private String outputPath;
private boolean createJar = true;
public boolean isCreateJar() {return createJar;}
public void setCreateJar(boolean createJar) {this.createJar = createJar;}
public String getOutputPath() {return outputPath;}
public void setOutputPath(String outputPath) {this.outputPath = outputPath;}
public String getSqlFilePath() {return sqlFilePath;}
public void setSqlFilePath(String sqlFilePath) {this.sqlFilePath = sqlFilePath;}
public String getDatabaseName() {return databaseName;}
public void setDatabaseName(String databaseName) {this.databaseName = databaseName;}
public String getTempPath() {return tempPath;}
public void setTempPath(String tempPath) {this.tempPath = tempPath;}
public void execute() throws BuildException {
Connection c = null;
String sep = null;
String connStr = null;
Statement statement = null;
if (getTempPath()==null || getDatabaseName()==null || getOutputPath()==null || getSqlFilePath()==null) {
System.out.println("Must send the following parameters to this task : /n databaseName (the name of the database you want to create), tempPath (a temporary directory where the database files will be created, this should be empty), sqlFilePath (the path to a file containing SQL commands which will set up the strucutre of the database you are trying to create), outputPath (the directory where you wish the finished database JAR file to be placed");
throw new BuildException("bad parameters");
}
try {
sep = System.getProperties().getProperty("file.separator");
connStr = "jdbc:derby:" + getTempPath() + sep + getDatabaseName();
} catch (Exception e) {
System.out.println("Couldn't retrieve system properties, or concatenate a string from parameters that should have been sent to this ant task.");
throw new BuildException(e);
}
System.out.println("Attempting to start Embedded Derby Database");
try {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
} catch (Exception e) {
System.out.println("Couldn't find the Derby Database driver. Perhaps derby.jar is not in the classpath?");
throw new BuildException(e);
}
System.setProperty("derby.system.home", getTempPath());
try {
c = DriverManager.getConnection(connStr + ";create=true");
} catch (Exception e) {
System.out.println("Couldn't create the database using the connection string : " + connStr);
throw new BuildException(e);
}
System.out.println("Derby has been started");
System.out.println("Attempting to run SQL statements against the Derby database" + getSqlFilePath());
try {
statement = c.createStatement();
for (final String sqlStatment : getSQLStatementsToExecute(getSqlFilePath())) {
System.out.println("Attempting to execute " + sqlStatment);
statement.execute(sqlStatment);
}
} catch (Exception e) {
System.out.println("failed to run the query. Aborting database creation. Delete " + getTempPath() + " to clean up.");
throw new BuildException(e);
}
System.out.println("Attempting to close down the Derby database, and remove the lock");
try {
DriverManager.getConnection(connStr + ";shutdown=true");
} catch (SQLException e) {
if(e.getErrorCode() != 45000) { // != normal shutdown
System.out.println("failed to shut down the newly created database properly, this will result in it being unusable. Delete " + getTempPath() + " to clean up.");
throw new BuildException(e);
}
}
System.out.println("Cleaning up..");
try {
if (statement!=null) {
statement.close();
statement=null;
}
if (c!=null) {
c.close();
c = null;
}
} catch (SQLException e) {
System.out.println("WARN : failed to cleanup properly, this won't affect the performance of the created database but it is unusual. Make sure that this JVM is closed down before attempting to run the database.");
}
try {
if (isCreateJar()) {
System.out.println("Attempting to create the Jar File");
createJar();
System.out.println("Created the database JAR " + getDatabaseName() + " in " + getOutputPath());
} else {
System.out.println("Finished creation of Database. Create a JAR from " + getTempPath() + " to use the database.");
}
} catch (Exception e) {
System.out.println("failed to create a jar file from the newly created database.");
throw new BuildException(e);
}
}
private List<String> getSQLStatementsToExecute(final String fileName) throws Exception {
final List<String> sqlStatements = new ArrayList<String>();
final BufferedReader bufferedReader = new BufferedReader(new FileReader(fileName));
String sqlStatement = "";
while((sqlStatement = bufferedReader.readLine()) != null) {
if(!sqlStatement.equals("") && !sqlStatement.startsWith("--")) {
sqlStatements.add(sqlStatement);
}
}
bufferedReader.close();
return sqlStatements;
}
public void createJar() {
String separator = System.getProperties().getProperty("file.separator");
String outputFileName = getOutputPath() + separator + getDatabaseName() + ".jar";
File jarFile = new File(outputFileName);
File sourceFolder = new File(getTempPath() + separator + getDatabaseName() + separator);
System.out.println("Jarring up the files from " + sourceFolder);
//TODO find out why whitepace characters are added in the manifest file when the ANT JAR task does not add them!
try {
Manifest m = new Manifest();
Attributes global = m.getMainAttributes();
if (global.getValue(Attributes.Name.MANIFEST_VERSION) == null) {
global.put(Attributes.Name.MANIFEST_VERSION,"1.0");
}
if (global.getValue(new Attributes.Name("Created-By")) == null) {
String javaVendor = System.getProperty("java.vendor");
String jdkVersion = System.getProperty("java.runtime.version");
global.put(new Attributes.Name("Created-By"), jdkVersion + " (" + javaVendor + ")");
}
OutputStream fileOut = new FileOutputStream(jarFile);
JarOutputStream jarOut = new JarOutputStream(fileOut,m);
createJar(jarOut, sourceFolder);
jarOut.flush();
jarOut.finish();
}
catch (Exception e) {
e.printStackTrace();
}
}
public void createJar(JarOutputStream jarOut, File directory) {
try {
File[] fileArray = directory.listFiles();
byte buffer[] = new byte[1024];
int bytesRead;
for (int i=0; i<fileArray.length; i++) {
File currFile = fileArray[i];
if (currFile.isDirectory()) {
createJar(jarOut, currFile);
}
else {
String currFilename = currFile.getName();
// split the filename into directory and class name - in order
// to prevent putting the complete filenames into JAR
String pathInJar = directory.getAbsolutePath();
pathInJar = pathInJar.substring(getTempPath().length()+1);
JarEntry entry = new JarEntry(pathInJar + "/" + currFilename);
jarOut.putNextEntry(entry);
entry.setMethod(JarEntry.DEFLATED);
File file = new File(directory, currFilename);
FileInputStream fis = new FileInputStream(file);
while ((bytesRead = fis.read(buffer)) != -1) {
jarOut.write(buffer, 0, bytesRead);
}
fis.close();
jarOut.flush();
jarOut.closeEntry();
}
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
]]>