Many applications requirement where they want to store some data at runtime and able to query using SQL. We wouldn’t like to install a full fledged database in such scenarios.
Apache has come up with a light weight database named Derby which suits the purpose. I would like to publish usage of Apache derby in java and how to use it. This tutorial is targetted to any one who understands java
This example rotates around a simple table person
ID NAME FIRST_NAME BIRTH_DATE
1 Jim Hammond 12/12/1974
2 Robert Grass 2/6/1968
3 John Kazyncksy 5/3/2000
This data is loaded by program from an excel file to Derby Database
As i’m using maven i’ll first define my pom here
4.0.0
com.ashwin.test.derby
derby
Derby Example
1.0.0
org.apache.maven.plugins
maven-compiler-plugin
1.5
1.5
org.apache.maven.plugins
maven-surefire-plugin
<!--
Be sure that JAVA_HOME points to a JDK6 installation
${env.JAVA_HOME}/db/lib/derby.jar
-->
org.apache.maven.plugins
maven-source-plugin
attach-sources
verify
jar
org.dbunit
dbunit
2.2.2
test
org.apache.derby
derby
10.6.2.1
Now as we defined our POM. Now lets try to load some data into derby. As derby is inmemory database we dont need to start any db server before hand. I created a base class which return DB Connection url and driver class
package com.linkwithweb.test.derby.dao;
import org.dbunit.JdbcBasedDBTestCase;
import org.dbunit.dataset.IDataSet;
/**
* An abstract Jdbc test case that factorizes the common methods.
*
* @author Ashwin
* @since 16th Nov 2010
*/
public abstract class AbstractDaoTest extends JdbcBasedDBTestCase {
/**
* @see org.dbunit.JdbcBasedDBTestCase#getConnectionUrl()
*/
@Override
protected String getConnectionUrl() {
return "jdbc:derby:myDB;create=true";
}
/**
* @see org.dbunit.JdbcBasedDBTestCase#getDriverClass()
*/
@Override
protected String getDriverClass() {
return "org.apache.derby.jdbc.EmbeddedDriver";
}
/**
* @see org.dbunit.DatabaseTestCase#getDataSet()
*/
@Override
protected IDataSet getDataSet() throws Exception {
return getConnection().createDataSet();
}
}
Now let me create a class Table Creator which loads Data into the Database
package com.linkwithweb.test.derby.dao;
import java.sql.Statement;
/**
* Run once in order to create the tables.
*
* @author Ashwin
* @since 16th Nov 2010
*/
public class TableCreator extends AbstractDaoTest {
/**
* Initializes the DB. Must be run once and only once to create structure.
*
* @throws Exception
*/
public void initialize() throws Exception {
Statement lStatement = getConnection().getConnection()
.createStatement();
lStatement
.executeUpdate("CREATE TABLE PERSON (ID INT, NAME VARCHAR(60), FIRST_NAME VARCHAR(60), BIRTH_DATE DATE)");
}
/**
* Calls the {@link #initialize()} method on a new instance.
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
new TableCreator().initialize();
}
}
In the above code we have create a table person in our testDB
Now letme create a GenericDAO which define any generic DAO contract
package com.linkwithweb.test.derby.dao;
import java.io.Serializable;
import java.sql.SQLException;
import java.util.Collection;
/**
* A DAO interface with common methods.
*
* @param
* Object type of the DAO
* @param
* Object type of the unique key
* @author Ashwin
* @since 16th Nov 2010
*/
public interface IDao {
/**
* Returns the object denoted by this unique identifier in the back-end
* system.
*
* @param pId
* Unique identifier
* @return Object
* @throws SQLException
* If an error occurs
*/
T findById(K pId) throws SQLException;
/**
* Removes the object denoted by this unique identifier from the back-end
* system.
*
* @param pId
* Unique identifier
* @throws SQLException
* If an error occurs
*/
void removeById(K pId) throws SQLException;
/**
* Returns all the object in the back-end system.
*
* @return Collection of objects
* @throws SQLException
* If an error occurs
*/
Collection getAll() throws SQLException;
}
Now let me create Person DAO which acts on Person Table
package com.linkwithweb.test.derby.dao;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* An abstract DAO that knows how to get a connection to the db.
*
* @param
* Object type of the DAO
* @param
* Object type of the unique key
* @author Ashwin
* @since 16th Nov 2010
*/
public abstract class AbstractDao implements
IDao {
/** Connection URL . */
private static final String CONNECTION_URL;
/** Connection login to the DB. */
private static final String CONNECTION_LOGIN;
/** Connection password to the DB. */
private static final String CONNECTION_PWD;
static {
CONNECTION_URL = System.getProperty("com.linkwithweb.url");
CONNECTION_LOGIN = System.getProperty("com.linkwithweb.login");
CONNECTION_PWD = System.getProperty("com.linkwithweb.pwd");
}
/**
* Gets a connection to the database.
*
*
* @return Connection
* @throws SQLException
* If the DB can't be connected to
*/
protected Connection createConnection() throws SQLException {
return DriverManager.getConnection(CONNECTION_URL, CONNECTION_LOGIN,
CONNECTION_PWD);
}
}
package com.linkwithweb.test.derby.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import com.linkwithweb.test.derby.model.Person;
/**
* DAO for accessing the datas of a person.
*
* @author Ashwin
* @since 16th Nov 2010
*/
public class PersonDao extends AbstractDao {
/**
* @throws SQLException
* @see com.linkwithweb.test.derby.dao.IDao#findById(java.lang.Object)
*/
public Person findById(Integer pId) throws SQLException {
Connection lConnection = null;
try {
lConnection = createConnection();
PreparedStatement lStatement = lConnection
.prepareStatement("SELECT * FROM PERSON WHERE ID = ?");
lStatement.setInt(1, pId);
ResultSet lRs = lStatement.executeQuery();
if (lRs.next()) {
Person lPerson = new Person();
lPerson.setId(lRs.getLong("ID"));
lPerson.setName(lRs.getString("NAME"));
lPerson.setFirstName(lRs.getString("FIRST_NAME"));
lPerson.setBirthDate(lRs.getDate("BIRTH_DATE"));
return lPerson;
}
} finally {
if (lConnection == null) {
lConnection.close();
}
}
return null;
}
/**
* @see com.linkwithweb.test.derby.dao.IDao#getAll()
*/
public Collection getAll() {
// TODO Auto-generated method stub
return null;
}
/**
* @see com.linkwithweb.test.derby.dao.IDao#removeById(java.lang.Object)
*/
public void removeById(Integer pId) {
// TODO Auto-generated method stub
}
}
Now as we have all the classes defined now. Let us create a PersonDAOTest which loads data into our person table and Does simple unit test
package com.linkwithweb.test.derby.dao;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.operation.DatabaseOperation;
import com.linkwithweb.test.derby.model.Person;
/**
* A test case for {@link PersonDao}.
*
* @author Ashwin
* @since 16th Nov 2010
*/
public class PersonDaoTest extends AbstractDaoTest {
/** The DAO to be tested. */
private PersonDao personDao;
/**
* @see org.dbunit.DatabaseTestCase#setUp()
*/
@Override
protected void setUp() throws Exception {
super.setUp();
// Creates the DAO to be tested
personDao = new PersonDao() {
/**
* @see com.linkwithweb.test.derby.dao.AbstractDao#createConnection()
*/
@Override
protected Connection createConnection() throws SQLException {
try {
return getConnection().getConnection();
} catch (Exception e) {
throw new SQLException(e);
}
}
};
InputStream lStream = PersonDaoTest.class.getClassLoader()
.getResourceAsStream("com/ashwin/test/derby/dao/person.xls");
// Creates the initialization data
IDataSet lDataSet = new XlsDataSet(lStream);
DatabaseOperation.CLEAN_INSERT.execute(getConnection(), lDataSet);
}
/**
* Test method for
* {@link com.linkwithweb.test.derby.dao.PersonDao#findById(java.lang.Integer)}.
*
* @throws SQLException
*/
public void testFindById() throws SQLException {
Person lPerson = personDao.findById(1);
assertNotNull(lPerson);
}
}
Below is the project structure which will give you an overview of how it is
C:\software\derby\src>tree /f
C:.
├───main
│ └───java
│ └───com
│ └───linkwithweb
│ └───test
│ └───derby
│ ├───dao
│ │ AbstractDao.java
│ │ IDao.java
│ │ PersonDao.java
│ │
│ └───model
│ Person.java
│
└───test
├───java
│ └───com
│ └───linkwithweb
│ └───test
│ └───derby
│ └───dao
│ AbstractDaoTest.java
│ PersonDaoTest.java
│ TableCreator.java
│
└───resources
└───com
└───linkwithweb
└───test
└───derby
└───dao
person.xls
DerbyHelloWorld
Rename to .rar
Technology Portal 