Tutorial with Hibernate - Employment - DbFixture

Example

The schema can be found on Hibernate help: http://www.hibernate.org/hib_docs/reference/en/html/example-mappings.html#example-mappings-emp
The final project can be found in DbFixture release directory: <dbfixture>/ormappers/hibernate

Create First Test

Like any test-driven development, we start by creating a test case EmploymentMappingTest. Add the first test for running th query. There are the steps that we are going to do and we are going to do them with test-driven.

Open Hibernate session and transaction
Configure database
Set up mapping for employers table
Insert and assert data

Open Hibernate session and transaction

We are going to do it step by step. When you get fluent at it, you can actually jump steps ahead.

Configuration configuration = new Configuration();
Session session = configuration.buildSessionFactory().openSession();
Transaction transaction = null;
try {
      transaction = session.beginTransaction();
      transaction = null;
} finally {
      if (transaction != null) {
        transaction.rollback();
}
      session.close();
}

This will fail because the configuration has not been configured. In order to fix this test, configure it by adding database properties:

...
Configuration configuration = new Configuration();
configuration.setProperty("hibernate.connection.driver_class", org.hsqldb.jdbcDriver.class.getName());
configuration.setProperty("hibernate.connection.url", "jdbc:hsqldb:mem:employment");
configuration.setProperty("hibernate.connection.username", "sa");
configuration.setProperty("hibernate.connection.password", "");
configuration.setProperty("hibernate.connection.pool_size", "1");
configuration.setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLDialect");
Session session = configuration.buildSessionFactory().openSession();
...

Here you see the convience of HsqlDb. When you try to connect a database through JDBC URL in the form of "jdbc:hsqldb:mem:<database name>", the database will be created upon the first connection and will stay available until you issue "SHUTDOWN" command. Because it is in memory, the database will disappear once the process is terminated, without a trace!

Configure Database

However, an empty database is of no use for us. We need to set up the database base on our schema. DbFixture provides a proxy database driver, org.thoughtworks.dbfixture.proxy.ProxyDriver, which, upon the first connection, will set up the database by retriving it from the DatabaseRegistry.

To make the test fail, change the two lines that sets the hibernate driver class and url to point to the proxy driver.

configuration.setProperty("hibernate.connection.driver_class", org.thoughtworks.dbfixture.proxy.ProxyDriver.class.getName());
configuration.setProperty("hibernate.connection.url", org.thoughtworks.dbfixture.proxy.ProxyDriver.urlForDb("employment"));

The test will fail. because schema "employment" is not registered. Not fixing it by creating a static initializer (you will pull it up to a super class later) that registers this schema.

static {
    DatabaseRegistry.instance().registerSchema(new Schema() {

      public String getDbName() {
        return "employment";
      }

      public void setupSchema(HsqlDb database) {
      }
    });
}

Sep up mapping for employers table

Since the session does not have any mapping set up yet, we make the test fail by running the query. Insert the line that does the query

...
transaction = session.beginTransaction();
Employer employer = (Employer) session.load(Employer.class, new Long(0));
transaction = null;
...

This class Employer does not exist yet so the test will not compile. Fix it by creating this class in package org.thoughtworks.dbfixture.example.hibernate2. Since this will be the production class, you should create it in the source coder rather than test folder.

Afer you fix the compiling error, you should run the test again and you will see that now the test fails because

JUnit Test fail

net.sf.hibernate.MappingException: Unknown entity class: org.thoughtworks.dbfixture.example.hibernate2.Employer
at net.sf.hibernate.impl.SessionFactoryImpl.getPersister(SessionFactoryImpl.java:347)
at net.sf.hibernate.impl.SessionImpl.getClassPersister(SessionImpl.java:2718)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1998)
...

Now fix the build by adding more on the configuration:

Adding the mapping entry for Employer class

...
configuration.setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLDialect");
configuration.addClass(Employer.class);
Session session = configuration.buildSessionFactory().openSession();
...

Create the mapping file for table employers: Employer.hbm.xml under the source folder at org/thoughtworks/dbfixture/example/hibernate2

Employer.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
  <class name="Employer" table="employers">
      <id name="id">
          <generator class="identity" />
      </id>
      <property name="name"/>
  </class>
</hibernate-mapping>

The test will still fail because certain properties are not found. You can fix it by adding the property id and name to the class Employer. The class now should match the UML diagram in the hibernate document.

The failure is now "Table not found".

Table not found failure

net.sf.hibernate.exception.SQLGrammarException: could not load: [org.thoughtworks.dbfixture.example.hibernate2.Employer#3]
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:69)
...
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Caused by: java.sql.SQLException: Table not found: EMPLOYERS in statement [select employer0_.id as id0_, employer0_.name as name0_ from employers employer0_ where employer0_.id=?]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
...

Now we need to change the schema registration part a little bit so that employers table can be created during the setup.

public void setupSchema(HsqlDb database) {
        String sql =
            "create table employers (" +
                "id integer identity," +
                "name varchar(255)," +
                "constraint employer_primary_key primary key (id)" +
                ")";
        database.execute(sql);
}

The last failure now is ObjectNotFound because there are no data in the database.

ObjectNotFound

net.sf.hibernate.ObjectNotFoundException: No row with the given identifier exists: 3, of class: org.thoughtworks.dbfixture.example.hibernate2.Employer
at net.sf.hibernate.ObjectNotFoundException.throwIfNull(ObjectNotFoundException.java:24)
at net.sf.hibernate.impl.SessionImpl.load(SessionImpl.java:1931)
...

Instert and assert data

Insert data by adding the following line at the beginning of the test method:

HsqlDb database = DatabaseRegistry.instance().getDatabase("employment");
database.insert(new EmployersRow("Thoughtworks, Inc."));
long id = database.getLastIdentity();

and replace the query to use the id value, followed by the name field assertion.

Employer employer = (Employer) session.load(Employer.class, new Long(id));
assertEquals("Thoughtworks, Inc.", employer.getName());

The class EmployersRow is a helper class that you use to insert raw data in an easy way.

EmployersRow.java

public class EmployersRow {
  public String name;
  
  public Employers_Row(String name) {
    this.name = name;
  }
}

Add a Second Test for Create, Update, Delete

Now lets add another test that test the create/update/delete with hibernate

Move the fixture code

We need to move the fixture code to the setUp and tearDown so that they can be reused by other tests. If we are going to create more test cases, we can introduce a super class and pull them up.

The setUp and tearDown area should look like the following:

private Session session;
private Transaction transaction;

protected void setUp() throws Exception {
    super.setUp();
    Configuration configuration = new Configuration();
    configuration.setProperty("hibernate.connection.driver_class", org.thoughtworks.dbfixture.proxy.ProxyDriver.class.getName());
    configuration.setProperty("hibernate.connection.url", org.thoughtworks.dbfixture.proxy.ProxyDriver.urlForDb("employment"));
    configuration.setProperty("hibernate.connection.username", "sa");
    configuration.setProperty("hibernate.connection.password", "");
    configuration.setProperty("hibernate.connection.pool_size", "1");
    configuration.setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLDialect");
    configuration.addClass(Employer.class);
    session = configuration.buildSessionFactory().openSession();
}

protected void tearDown() throws Exception {
    if (transaction != null) {
      transaction.rollback();
    }
    session.close();
    DatabaseRegistry.instance().cleanResources();  //This cleans up the tables after the test
    super.tearDown();
}

and the test method is now simplified as following:

public void testQuery() throws HibernateException {
    HsqlDb database = DatabaseRegistry.instance().getDatabase("employment");
    database.insert(new EmployersRow("Thoughtworks, Inc."));
    long id = database.getLastIdentity();
    transaction = session.beginTransaction();
    Employer employer = (Employer) session.load(Employer.class, new Long(id));
    assertEquals("Thoughtworks, Inc.", employer.getName());
    transaction = null;
}

Don't forget to run your test after you made the change to make sure that they still pass.

Add additional test

public void testCreatUpdateDeleteActions()

public void testCreatUpdateDeleteActions() throws HibernateException {
    HsqlDb database = DatabaseRegistry.instance().getDatabase("employment");
    DatabaseTable table = database.table("employers");

    //Test the insert
    Employer employerToInsert = new Employer();
    employerToInsert.setName("IBM");
    transaction = session.beginTransaction();
    Long id = (Long) session.save(employerToInsert);
    transaction.commit();
    transaction = null;
    ResultRow rowForInsert = table.row("id = " + id);
    assertEquals("IBM", rowForInsert.column("name"));

    //Test the update
    transaction = session.beginTransaction();
    Employer employerToUpdate = (Employer) session.load(Employer.class, id);
    employerToUpdate.setName("IBM updated");
    session.save(employerToUpdate);
    transaction.commit();
    transaction = null;
    ResultRow rowForUpdate = table.row("id = " + id);
    assertEquals("IBM updated", rowForUpdate.column("name"));

    //Test the delete
    transaction = session.beginTransaction();
    Employer employerToDelete = (Employer) session.load(Employer.class, id);
    session.delete(employerToDelete);
    transaction.commit();
    transaction = null;
    int count = table.count("id = " + id);
    assertEquals(0, count);
}

This test will actually pass, because I just want to show you that once you have the fixture code in place, writing additionl code is really easy.

What Next?

By now you should be able to use test-driven to finish creating the mapping files for the rest of the tables. You will find out that embedding the SQL string that creats the tables in Java is a hassel. You can us the SchemaByFile class by creating a class EmployemntSchema, and move the SQL statements to a file called employment_tables_sql.txt that is in the same directory of the schema class. Because you don't have any views, so you just need to override the method getViewSql and make it return an empty string.