Derby Tutorial Mavenized with Unit Test and Loading Data from Excel

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

Advertisements

8 thoughts on “Derby Tutorial Mavenized with Unit Test and Loading Data from Excel

  1. hey there and thank you for your info – I have definitely picked up something new from right here.

    I did however expertise a few technical issues using this web site,
    as I experienced to reload the site a lot of times previous to I could get it to load properly.
    I had been wondering if your web hosting is OK?
    Not that I’m complaining, but sluggish loading instances times will very frequently affect your placement in google and can damage your high quality score if advertising and marketing with Adwords. Anyway I am adding this RSS to my email and could look out for much more of your respective intriguing content. Ensure that you update this again very soon.

  2. Wonderful blog! I found it while browsing on Yahoo News. Do you have any suggestions on how to get listed
    in Yahoo News? I’ve been trying for a while but I never seem to get there! Thank you

  3. Have you ever thought about publishing an e-book or guest authoring on other websites?
    I have a blog centered on the same ideas you discuss and would love
    to have you share some stories/information. I know my visitors would value your work.
    If you’re even remotely interested, feel free to shoot me an email.

  4. Thanks for any other informative site. Where else could I am getting that type of info written in such a perfect means?
    I have a project that I’m just now operating on, and I’ve been on the look out for such info.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s