Testing query results with dbUnit

dbunit-logoWhen developing a data-warehouse you need to run tests on int to ensure that the datasets are consistent.

Call it data quality tests or just tests, they are needed if you want to trust your data.

Here is a simple tutorial on how to run test agains your dataset with dbUnit, by testing query results.

The idea is that you take a snapshot of a well tested resultset and the you test that the query which provides such dataset always return the expected result.

First of all you need a query to test, let’s say:

SELECT * FROM vw_test_me;

then we need to take a snapshot of it, to do so

Connection jdbcConnection = DriverManager.getConnection(<your db parameters>);
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
// partial database export
QueryDataSet partialDataSet = new QueryDataSet(connection);
partialDataSet.addTable("test_snapshot", "SELECT * FROM vw_test_me");
FlatXmlDataSet.write(partialDataSet, new FileOutputStream(String.format("snapshot.xml","test_snapshot")));

Now we have an xml file called snapshot.xml which contains the resultset of the query above.

This is the starting point of our tests, everytime the query runs the result have to match with the resultset contained into snapshot.xml otherwise the test will fail.

dbUnit is a JUnit extension so the test should be written accordingly:

import junit.framework.TestCase;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.junit.After;
import org.junit.Test;

public class TestQuery extends TestCase
    protected IDatabaseTester databaseTester;

    public TestQuery()
        databaseTester = new JdbcDatabaseTester(<your db parameters>);
    public void testMyQuery()
        ITable actualTable = databaseTester.getConnection().createQueryTable("test_query", "SELECT * FROM vw_test_me");
        IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("snapshot.xml"));
        ITable expectedTable = expectedDataSet.getTable("test_snapshot");
        Assertion.assertEquals(expectedTable, actualTable);
    public void tearDown() throws Exception

the testMyQuery method first runs the query we are testing and store its result into variable ‘actualTable’, then it retrieves the snapshoted resultset from snapshot.xml, eventually it checks if the two datasets matches.

Common Expenses: Lazy Loading

When developing the POJO classes for mapping the database I set the fetch type to LAZY. At first i didn’t pu much interes on it but then when I was actually fetching the data from DB I had to lern it better.

The target was to display all project’s data on a page, my first idea was to simply retrieve the project from db

(Project)session.get(Project.class, 1)

and then display it:

<c:forEach var="expense" items="${project.expenses}">

But this didn’t worked. When an entity is instanciated all its collelctions are defined but the content is not loaded, infact lazy loading means Hibernate would fill the collenctions when them are requested.

If you try to retrieve all project’s expenses into the EJB there are no problems as an Hibernate session is opened. When we are into the view context (JSP in this case) there are no Hibernate sessions opened that’s why the expense collection could not be retrieved.

If the fetch mode is set to EAGER Hibernate load all entities collection whe the entity itself is istanciated. The view above would display all the data correctly but if we add a new expense it won’t be added to the collection as the main entity has already benn loaded.

The best solution would be create a Filter which open and close an Hibernate session at view context to lazy load the collections whenever are needed but seems like this way-around exists only in Spring Framework.

The fixeed this problem with in a ‘dirty’ way building a emporary container which collect the projects data every time the page is requested. This is not as performant as lazy loading because it fetches all the data at each request. But it finally make the view display all data.

Common Expenses: Stateful EJB, fail

Here we come with bad notes.

I was not able to implement some logic with Stateful Session Bean in my application. First it was difficult to find a place for such a thing inside my application then I wasn’t able to find good instruction on how to couple Stateful Bean with Servlet.

Common Expenses: Design Pattern

This post should have been one of the first actually.

Here is described the design pattern used on Common Expenses, I’ve used MVC as it is very straight forward: separate data management (model), application login (controller) and presentation (view).

As I post earlier the persisten layer is made with Hibernate, then the Businnes Login is implementend via Stateless EJB.

The application controller is a set of Servlet which get requests, work out them and send the result to the presentation layer.

The presentation layer is implemented with JSP.

Here is a schema of the whole application:


Common Expenses: EJB

To communicate with POJO entities I’m going to use EJB version 3.1.

As done before I will use annotation instead of a XML configuration file.

I will come back later with a full post on how to couple EJB with Hibernate.

Essentialy we need an Interface for make the bean accessible from other tools (in this case I’ll use Servelt to access EJB):

public interface RegisterBeanRemote

Remote means that this is he ‘server‘ class, in this instance our client will be the Servlets but if it would have been another java application we would have to distinguish between Remote and Local.

When the interface is ready we need a class to implement it:

public class RegisterBean implements RegisterBeanRemote

Stateless means that the bean doesn’t keep a ‘memory‘.

There’s no need to use JNDI explixity because annotation will do it for us.

Common Expenses: Hibernate

Here we start coding (finally)!

As I wrote before I’m going to use Hibernate as ORM (Object Relational Mapper).

I very few words Hibernate map database tables to POJO classes to achieve persistency. To make the class map correctly the table we need to add some extra information to the code. This could be done with an XML file for each class (let’s call it Entity from now) or with annotations.

I choose annotations (JPA ones implemented by Hibernate) because I think is better to keep everithing on the same file and I do not have to switch from a Java file to an XML file.

My first idea was to generate that classes automatically and actually i did it with Hibernate Tools for Eclipse, but I was not satisfacted with the generated code, so decided to do the other way round and generate the tables from the classes.

It was not so easy to find out how to achive it but after a deep deep deep digging in google I’ve discovered a great tutorial site with many examples for Hibernate. Moreover the examples on that site are done with the last version of Hibernate.

Here is the code of User POJO class:


@Table(name = "user")
public class User implements java.io.Serializable


Here we say that User is an Entity bound to the tabe user

@Column(name = "id", nullable = false)
private int id;

The primary key of user table is a generated auto-increment int, the PK column name is id

@Column(name = "username", unique = true, nullable = false, length = 255)
private String username;

@Column(name = "password", nullable = false, length = 255)
private String password;

@Column(name = "email", unique = true, nullable = false, length = 255)
private String email;

@Column(name="meta_ts", nullable=false)
private Date meta_ts = new Date();

@Column(name = "meta_user", nullable = false)
private int meta_user;


Each other class variable is bound to the appropriate column on the table

@OneToMany(fetch = FetchType.LAZY, mappedBy = "user")
private Set<Expense> expenses = new HashSet<Expense>(0);

@OneToMany(fetch = FetchType.LAZY, mappedBy = "meta_user")
private Set<Expense> created_expenses = new HashSet<Expense>(0);

@OneToMany(fetch = FetchType.LAZY, mappedBy = "user")
private Set<DefaultShare> default_shares = new HashSet<DefaultShare>(0);

@OneToMany(fetch = FetchType.LAZY, mappedBy = "meta_user")
private Set<DefaultShare> created_default_shares = new HashSet<DefaultShare>(0);

@OneToMany(fetch = FetchType.LAZY, mappedBy = "meta_user")
private Set<ExpenseCategory> categories = new HashSet<ExpenseCategory>(0);

@OneToMany(fetch = FetchType.LAZY, mappedBy = "meta_user")
private Set<Project> projects = new HashSet<Project>(0);


One to Many relations, we need to proviede the Entity linked to User and also the Foreign Key which refers to the User entity on the other table

Follows auto-generated contructors and setters/getters.

I really like annotations because they are self-explaining and could almost replace comments.

Common Expenses: ER Schema

This is the ER schema for the database of Common Expenses application:

ER Schema
ER Schema

The schema itself should be quite self-explaining anyway here are the main points:

  • meta_ts and meta_user are logging data they represent date and user of tuple creation.
  • OneToMany relationship between Project and Expense
  • OneToMany relationship between User and Share
  • OneToMany relationship between Expense and Share
  • ManyToMany relationship between User and Project, there are a join table (Partecipate) for this relationship which hold also the leader of a project.

I’m going to make Hibernate generate the database form me so I’m quite sure the final schema will be slighty different from the one above.

Common Expenses: Use Case Diagram

Here is the use case diagram of my application:

Use Case Diagram
Use Case Diagram

Basically there are two kinds of user: the project leader and project’s partecipants. Who create a project becomes its leader so an user could be both leader or partecipant depending on the project instance.

The Leader manage a project by setting/editing the basic information (name, description,…) and by managing other users allowing them to join the project and eventually set them default share for expenses.

The Partecipant first has to join a project and wait for leader’s approval, then he can add expense to the project decide which project’s users take part to it. Partecipant can also consults there periodic report of the project (by month, year).

Clearly each user has to register to the application and then log-in to be authorized to perform operation on the system.

Common Expenses: Working Place

Face a new project imply selecting the tools/technologies you’ll be using during developement.

Here is a brief excursus on what I’ll use.

IDE: Eclipse

Eclipse is a very powerfull IDE, I’m using it every day at work for PHP developing that’s why i feel so comfortable with it. Furthermore Eclipse has born for Java developing so i thing this is the right choice.


Because it’s free and I know it quite well since I’m using it for more than 6 years, I could choose SQL Server (the one we use at work) but since it is not free and I’m developing on a Mac that’s it. Moreover MySQL has MySQL Workbench which is a very usefull tool form designing/managing databases.

ORM: Hibernate

This is a project requirement, but i would have use it the same as it seems the most used out there and i want to learn it.

Common Expenses

This is a new project I’m working on for the Web Architecture master course of University of Trento.

Every student is free to choose the application domain but have to develop it using the technologies examinated during the course, here is a brief summary of what is expected.

My domain:

If you do not live in a big city you problably have to move and live in a flat in order to attend university. Living with other people means, between other things, share the expenses needed to ‘run’ the flat: bills, accounts, fees etc.

How do you divide the amout? Well it is not so hard I had a big Excel file which does the work.

But this has some problems: everybody needs to ask me to insert expenses or to have the monthly report or …

Wouldn’t it be smarter with a web app? Everybody could access the expenses without bother the guy who holds the Excel file.

If we want to generalize the whole thing we can look at the flat rental as a project and the tenants as the project participants sharing the expenses.

That’s what i will try to develop: an application which will help keeping track of common expenses on a generic project.


First I’ll try to make the app running on my laptop using the JAVA technologies we are asked and hopefully pass the exam 🙂 but then my goal is to deploy the app to Google AppSpot, this will require some changing on the code because AppSpot doesn’t provide the same ‘tools’ we are asked to use, fore example, DataStore instead of conventional database …

This is my first ‘serious’ Java project so I really hope to learn a lot!