Testing ORMLite

While various JPA implementations are the standard for good reasons, I took the time to shop around for some of the lighter ORM tools, especially one with a reasonable SQL query builder DSL.

ORMLite won this round, and I integrated it into my skeleton application, where it replaced Spring JDBC.

The @DatabaseTable and @DatabaseField annotations come with far fewer rules than JPA beans. The _ID and _NAME constants are used with the query builder.

src/main/java/com/gueck/spring3/Example.java:

#!java
package com.gueck.spring3;

import com.google.common.base.Objects;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;

/**
 * Example domain class.
 */
@DatabaseTable(tableName="examples")
public class Example {

    public static final String _ID = "id";
    
    @DatabaseField(columnName=_ID, generatedId=true)
    public Long id;
    
    public static final String _NAME = "name";
    
    /** A name to show in a user interface. */
    @DatabaseField(columnName=_NAME, canBeNull=false)
    public String name;

    public Example() {
    }

    public Example(final String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return Objects.toStringHelper(this)
                .add("id", id)
                .add("name", name)
                .toString();
    }

}

Unfortunately Spring doesn't automagically translate the SQLException to DataAccessException, so I have to use the translator manually.

src/main/java/com/gueck/spring3/ExampleDao.java:

#!java
package com.gueck.spring3;

import com.j256.ormlite.dao.Dao;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.stereotype.Repository;

@Repository
public class ExampleDao {

    @Autowired
    private Dao<Example, Long> delegate;
    
    private static final SQLExceptionTranslator TRANSLATOR =
            new SQLErrorCodeSQLExceptionTranslator();

    public boolean createExample(final Example example) {
        try {
            return 1 == delegate.create(example);
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("update example", null, e);
        }
    }

    public Example readExample(final Long id) {
        try {
            return delegate.queryForId(id);
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("read example", null, e);
        }
    }

    public boolean updateExample(final Example example) {
        try {
            return 1 == delegate.update(example);
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("update example", null, e);
        }
    }

    public boolean deleteExample(final Long id) {
        try {
            return 1 == delegate.deleteById(id);
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("delete example", null, e);
        }
    }

    public List<Example> listExamples() {
        try {
            return delegate.queryForAll();
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("list examples", null, e);
        }
    }

    /**
     * Comedy examples always have a 'y' character in their names.
     */
    public List<Example> listComedies() {
        try {
            return delegate.queryBuilder().where().like(Example._NAME, "%y%").query();
        } catch (final SQLException e) {
            throw TRANSLATOR.translate("list comedies", null, e);
        }
    }
}

Configuring ORMLite with Spring is painless. Had to switch HSQLdb to H2, since HSQLdb threw some permission exceptions when used with ORMLite.

#!xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xmlns:p="http://www.springframework.org/schema/p"
        xmlns:c="http://www.springframework.org/schema/c"
    xmlns:context="http://www.springframework.org/schema/context"
        xmlns:cache="http://www.springframework.org/schema/cache"
    xsi:schemaLocation="
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
        http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd">

    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

    <!-- Scans within the base package of the application for @Components to configure as beans -->
    <!-- @Controller, @Service, @Configuration, etc. -->
    <context:component-scan base-package="com.gueck.spring3" />

    <!-- Enables the Spring MVC @Controller programming model -->
    <mvc:annotation-driven />
    
    <mvc:resources mapping="/resources/**" location="/WEB-INF/resources/" />
        
    <cache:annotation-driven />
    
    <bean id="cacheManager" class="org.springframework.cache.support.SimpleCacheManager">
        <property name="caches">
            <set>
                <bean class="org.springframework.cache.concurrent.ConcurrentMapCacheFactoryBean" p:name="examples"/>
            </set>
        </property>
    </bean>
    
    <bean id="viewNameTranslator" class="org.springframework.web.servlet.view.DefaultRequestToViewNameTranslator"/>
    
    <bean id="scalateViewResolver" class="org.fusesource.scalate.spring.view.ScalateViewResolver"
        p:order="1" p:prefix="/WEB-INF/view/" p:suffix=".scaml" />

    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"
         p:order="2" p:viewClass="org.springframework.web.servlet.view.JstlView" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />

    <context:property-placeholder />

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dataSource" />
    
    <bean id="connectionSource" class="com.j256.ormlite.jdbc.DataSourceConnectionSource" 
        init-method="initialize" c:_0-ref="dataSource" c:_1-ref="databaseType" />
    
    <bean id="exampleLiteDao" class="com.j256.ormlite.spring.DaoFactory" factory-method="createDao"
        c:_0-ref="connectionSource" c:_1="com.gueck.spring3.Example" />
    
    <beans profile="production">
        <bean id="dataSource" class="org.postgresql.ds.PGPoolingDataSource" destroy-method="close">
            <property name="serverName" value="${db.serverName}"/>
            <property name="databaseName" value="${db.databaseName}"/>
            <property name="user" value="${db.username}"/>
            <property name="password" value="${db.password}"/>
        </bean>
        <bean id="databaseType" class="com.j256.ormlite.db.PostgresDatabaseType" />
    </beans>
    
    <beans profile="dev">
        <jdbc:embedded-database id="dataSource" type="H2">
            <jdbc:script location="classpath:com/gueck/spring3/dev-schema.sql"/>
            <jdbc:script location="classpath:com/gueck/spring3/dev-test-data.sql"/>
        </jdbc:embedded-database>
        <bean id="databaseType" class="com.j256.ormlite.db.H2DatabaseType" />
    </beans>

</beans>