Java Database Toolbox

Ant <sql> task

<macrodef name="run-sql">
  <attribute name="userid" default="${jdbc.userid}"/>
  <attribute name="password" default="${jdbc.password}"/>
  <attribute name="file"/>
  <sequential>
    <sql driver="${jdbc.driver}"
         url="${jdbc.url}"
         userid="@{userid}"
         password="@{password}"
         delimiter=";"
         classpathref="classpath.local"
         >
      <fileset dir="src/main/sql/ddl" includes="@{file}"/>
    </sql>
  </sequential>
</macrodef>

Ant <sql> task

<macrodef name="run-sql">
  <attribute name="userid" default="${jdbc.userid}"/>
  <attribute name="password" default="${jdbc.password}"/>
  <attribute name="file"/>
  <sequential>
    <sql driver="${jdbc.driver}"
         url="${jdbc.url}"
         userid="@{userid}"
         password="@{password}"
         delimiter=";"
         classpathref="classpath.local"
         >
      <fileset dir="src/main/sql/ddl" includes="@{file}"/>
    </sql>
  </sequential>
</macrodef>

<target name="create-test-schema" description="Create 'birt' user." >
  <run-sql file="create_schema.sql"
    userid="${jdbc.system.userid}"
    password="${jdbc.system.password}"/>
</target>

<target name="run-ddl" description="Create tables etc." >
  <run-sql file="create_classicmodels.sql"/>
</target>

Using custom Ant <sql> tasks

• DROP schema objects to get clean schema
Manipulate constraints
Preprocess source files to generate boilerplate
Preprocess source files for multi-pass

--PASS_1
CREATE TABLE Payments (
  customerNumber INTEGER NOT NULL,
  checkNumber VARCHAR2(50) NOT NULL,
  paymentDate DATE NOT NULL,
  amount NUMBER NOT NULL,
  PRIMARY KEY (customerNumber, checkNumber)
);

--PASS_2
ALTER TABLE Payments ADD CONSTRAINT Payments_Customer_fk
  FOREIGN KEY (CustomerNumber)
  REFERENCES Customers(CustomerNumber);

Writing custom Ant <sql> tasks

Subclass JDBCTask...

public class MyJDBCTask extends JDBCTask {
  // ...
  public void execute() throws BuildException {
    Connection connection = getConnection();
    try {
      // execute SQL on Connection ...
    }
    finally {
      // close connection
    }
  }
}

<?xml version="1.0" encoding="UTF-8"?>
<antlib>
  <taskdef name="mytask" classname="nzoug.ant.MyJDBCTask"/>
  <!-- ... -->
</antlib>

Writing custom Ant <sql> tasks

And use your task in an Ant script...

<project name="..." basedir="." default="help"
  xmlns:nzoug="antlib:org.nzoug.ant">

  <target name="define-tasks">
    <taskdef resource="org/nzoug/ant/antlib.xml"
      uri="antlib:org.nzoug.ant"
      classpathref="classpath.compile"/>
  </target>
  ...
  <target name="mytask" depends="create-ddl, define-tasks">
    <nzoug:mytask driver="${jdbc.driver}"
      url="${jdbc.url}"
      userid="${jdbc.userid}"
      password="${jdbc.password}">
      <classpath refid="classpath.compile"/>
    </nzoug:mytask>
  </target>
</project>

Ant build with SQL DDL

SchemaSpy

Running SchemaSpy from Ant

<target name="schemaspy" description="Run SchemaSpy">
  <echo message="Running SchemaSpy..." />
  <java classname="net.sourceforge.schemaspy.Main" fork="true">
    <classpath refid="classpath.local"/>
    <arg line="-t ${schemaspy.type}"/>
    <arg line="-host ${jdbc.host}"/>
    <arg line="-port ${jdbc.port}"/>
    <arg line="-db ${jdbc.db}"/>
    <arg line="-u ${jdbc.userid}"/>
    <arg line="-p ${jdbc.password}"/>
    <arg line="-s ${jdbc.schema}"/>
    <arg line="-o dist"/>
    <arg line="-hq"/>
  </java>
</target>

SchemaSpy Options

• Database type and connection parameters
Classpath for drivers
Quality: low/high
Display/metadata options
Include/exclude tables
Exclude views
Exclude columns
Exclude implied relationships

Implementing Data Access

public interface CustomerDao {
  Customer get(long id);
  List<Customer> findByName(String name);
  List<Customer> findWithOrdersOrPaymentsInMonth(int year, int month);
  List<Customer> findForFilter(CustomerFilter filter);
}

Use JDBC?
Use Spring JDBC?
Use JPA?
Use something else?

DAOs: The Wrong Way

public Customer get(long id) {
  Connection connection = null;
  try {
    connection = dataSource.getConnection();
    PreparedStatement ps = null;
    try {
      ps = connection.prepareStatement("SELECT * FROM customers WHERE customernumber = ?");
      ps.setLong(1, id);
      ResultSet rs = null;
      try {
        rs = ps.executeQuery();
        if (rs.next()) {
          Customer result = new Customer();
          result.setCustomerName(rs.getString("customername"));
          result.setContactLastName(rs.getString("contactlastname"));
          result.setContactFirstName(rs.getString("contactfirstname"));
          result.setPhone(rs.getString("phone"));
          result.setAddressLine1(rs.getString("addressline1"));
          result.setAddressLine2(rs.getString("addressline2"));
          result.setCity(rs.getString("city"));
          result.setState(rs.getString("state"));
          result.setPostalCode(rs.getString("postalcode"));
          result.setCountry(rs.getString("country"));
          result.setSalesRep(null); // TODO
          result.setCreditLimit(rs.getBigDecimal("creditlimit"));
          return result;
        }
        return null;
      }
      catch (SQLException rsEx) {
        throw new RuntimeException("SQLException running query", rsEx);
      }
      finally {
        if (rs != null) {
          try {
            rs.close();
          }
          catch (SQLException ignore) {}
          }
      }
    }
    catch (SQLException psEx) {
      throw new RuntimeException("SQLException preparing statement", psEx);
    }
    finally {
      if (ps != null) {
        try {
          ps.close();
        }
        catch (SQLException ignore) {}
      }
    }
  }
  catch (SQLException conEx) {
    throw new RuntimeException("SQLException opening connection", conEx);
  }
  finally {
    if (connection != null) {
      try {
        connection.close();
      }
      catch (SQLException ignore) {}
    }
  }
}

Spring JDBC

public Customer get(long id) {
  return getJdbcTemplate().queryForObject(
    "SELECT * FROM customers WHERE customernumber = ?",
    new CustomerRowMapper(),
    id
  );
}

private static class CustomerRowMapper implements RowMapper<Customer> {
  public Customer mapRow(ResultSet rs, int i) throws SQLException {
    Customer result = new Customer();
    result.setCustomerNumber(rs.getLong("customernumber"));
    result.setCustomerName(rs.getString("customername"));
    result.setContactLastName(rs.getString("contactlastname"));
    result.setContactFirstName(rs.getString("contactfirstname"));
    result.setPhone(rs.getString("phone"));
    result.setAddressLine1(rs.getString("addressline1"));
    result.setAddressLine2(rs.getString("addressline2"));
    result.setCity(rs.getString("city"));
    result.setState(rs.getString("state"));
    result.setPostalCode(rs.getString("postalcode"));
    result.setCountry(rs.getString("country"));
    result.setCreditLimit(rs.getBigDecimal("creditlimit"));
    return result;
  }
}

Spring JDBC

• Comprehensive JDBC support library
Resource management
Exception translation
Convenience APIs for all usages

Spring JDBC: Reuse RowMapper

public Customer get(long id) {
  return getJdbcTemplate().queryForObject(
    "SELECT * FROM customers WHERE customernumber = ?",
    new CustomerRowMapper(),
    id
  );
}

public List<Customer> findByName(String name) {
  return getJdbcTemplate().query(
    "SELECT * FROM customers WHERE UPPER(customername) LIKE ? " +
    "ORDER BY customernumber",
    new CustomerRowMapper(),
    "%" + name.toUpperCase() + "%"
  );
}

Spring JDBC: SimpleJdbcTemplate

public interface SimpleJdbcOperations {
  // ...
	<T> T queryForObject(String sql, Class<T> requiredType, Object... args)
	<T> T queryForObject(String sql, Class<T> requiredType, Map<String, ?> args)
	<T> T queryForObject(String sql, Class<T> requiredType, SqlParameterSource args)
	<T> T queryForObject(String sql, RowMapper<T> rm, Object... args)
	<T> T queryForObject(String sql, RowMapper<T> rm, Map<String, ?> args)
	<T> T queryForObject(String sql, RowMapper<T> rm, SqlParameterSource args)
	<T> List<T> query(String sql, RowMapper<T> rm, Object... args)
	<T> List<T> query(String sql, RowMapper<T> rm, Map<String, ?> args)
	<T> List<T> query(String sql, RowMapper<T> rm, SqlParameterSource args)
  // ...
}

public interface SqlParameterSource {
  boolean hasValue(String paramName);
  Object getValue(String paramName) throws IllegalArgumentException;
  int getSqlType(String paramName);
  String getTypeName(String paramName);
}

new MapSqlParameterSource("year", year).addValue("month", month)

Spring JDBC: Named Parameters

public List<Customer> findWithOrdersOrPaymentsInMonth(int year, int month) {
  return new NamedParameterJdbcTemplate(getDataSource()).query(
    "SELECT * " +
    "FROM customers c " +
    "WHERE EXISTS (" +
      "SELECT * " +
      "FROM orders o " +
      "WHERE o.customernumber = c.customernumber " +
        "AND EXTRACT(YEAR FROM orderdate) = :year " +
        "AND EXTRACT(MONTH FROM orderdate) = :month" +
    ") " +
      "OR  EXISTS (" +
      "SELECT * " +
      "FROM payments p " +
      "WHERE p.customernumber = c.customernumber " +
        "AND EXTRACT(YEAR FROM paymentdate) = :year " +
        "AND EXTRACT(MONTH FROM paymentdate) = :month" +
    ")" +
    "ORDER BY customernumber",
    new MapSqlParameterSource("year", year).addValue("month", month),
    new CustomerRowMapper()
  );
}

Spring JDBC: Strengths

• Resource and exception management
Flexible
Good batch support
Good stored procedure support
Good for running DDL
Integration with Spring transaction management
Support for some connection pooling frameworks
Support for some vendor-specific extensions (e.g. LOBs)

Spring JDBC: Weaknesses

• Spring framework dependency
Inline SQL?
Programmatic bean/PreparedStatement/ResultSet mapping
No advanced ORM features: identity map, lazy loading, collections/associations

Java Persistence API

@Entity
@Table(name = "Customers")
public class Customer {
  private long customerNumber;
  private String customerName;

  @Id
  public long getCustomerNumber() {
    return customerNumber;
  }
  // ...
  public String getCustomerName() {
    return customerName;
  }
  // ...
  @ManyToOne
  @JoinColumn(name = "SalesRepEmployeeNumber")
  public Employee getSalesRep() {
    return salesRep;
  }
  // ...
}

Persistence Unit

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
  version="1.0">
  <persistence-unit name="classicmodelsPU">
  <class>classicmodels.domain.Office</class>
  <class>classicmodels.domain.Employee</class>
  <class>classicmodels.domain.Customer</class>
  <!-- ... -->
  <properties>
    <property name="hibernate.show_sql" value="false"/>
    <property name="hibernate.dialect"
      value="org.hibernate.dialect.Oracle10gDialect"/>
  </properties>
  </persistence-unit>
</persistence>

EntityManagerFactory entityManagerFactory =
  Persistence.createEntityManagerFactory("classicmodelsPU");
EntityManager em = entityManagerFactory.createEntityManager();

public Customer get(long id) {
  return em.find(Customer.class, id);
}

JPA Criteria Queries

How do we add arbitrary conditions to a query?

public interface CustomerDao {
  Customer get(long id);
  List<Customer> findByName(String name);
  List<Customer> findWithOrdersOrPaymentsInMonth(int year, int month);
  List<Customer> findForFilter(CustomerFilter filter);
}

public interface CustomerFilter {
  String getName();
  String getPhone();
  String getAddress();
  BigDecimal getMinimumCreditLimit();
  BigDecimal getMaximumCreditLimit();
}

JPA 2 Criteria Query API

public List<Customer> findForFilter(final CustomerFilter filter) {
  CriteriaBuilder builder = em.getCriteriaBuilder();
  CriteriaQuery<Customer> criteriaQuery = builder.createQuery(Customer.class);
  Root<Customer> customer = criteriaQuery.from(Customer.class);
  List<Predicate> conditions = new ArrayList<Predicate>();
  if (filter.getName() != null && !filter.getName().equals("")) {
    String name = "%" + filter.getName().toUpperCase() + "%";
    conditions.add(builder.or(
      builder.like(builder.upper(customer.get("customerName").as(String.class)), name),
      builder.like(builder.upper(customer.get("contactLastName").as(String.class)), name),
      builder.like(builder.upper(customer.get("contactFirstName").as(String.class)), name)
    ));
  }
  // similar for address and phone ...
  if (filter.getMinimumCreditLimit() != null) {
    BigDecimal minimumCreditLimit = filter.getMinimumCreditLimit();
    conditions.add(
      builder.gt(customer.get("creditLimit").as(Number.class), minimumCreditLimit)
    );
  }
  // similar for maximumCreditLimit ...
  Predicate predicate = builder.and(conditions.toArray(new Predicate[conditions.size()]));
  criteriaQuery.where(predicate);
  TypedQuery<Customer> query = em.createQuery(criteriaQuery);
  return query.getResultList();
}

MyBatis SQL Maps

public interface MyBatisCustomerDao extends CustomerDao {
  @Select(
    "SELECT customerNumber, customerName, contactLastName, " +
    "contactFirstName, phone, addressLine1, addressLine2, " +
    "city, state, postalCode, country, creditLimit " +
    "FROM customers " +
    "WHERE customerNumber = #{id}")
  public Customer get(long id);
  // ...
}

TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment =
  new Environment("development", transactionFactory, getDataSource());
Configuration configuration = new Configuration(environment);
configuration.addMapper(MyBatisCustomerDao.class);
sessionFactory = new SqlSessionFactoryBuilder().build(configuration);

SqlSession session = sessionFactory.openSession(false);
CustomerDao dao = session.getMapper(MyBatisCustomerDao.class);

MyBatis SQL Maps: Annotations

public interface MyBatisCustomerDao extends CustomerDao {
  @Select(
    "SELECT customerNumber, customerName, contactLastName, " +
    "contactFirstName, phone, addressLine1, addressLine2, " +
    "city, state, postalCode, country, creditLimit " +
    "FROM customers " +
    "WHERE customerNumber = #{id}")
  public Customer get(long id);

  @Select(
    "SELECT customerNumber, customerName, contactLastName, contactFirstName, phone, " +
    "addressLine1, addressLine2, city, state, postalCode, country, creditLimit " +
    "FROM customers " +
    "WHERE UPPER(customerName) LIKE UPPER('%' || #{name} || '%')" +
    "ORDER BY customerNumber")
  List<Customer> findByName(String name);
  // ...
}

MyBatis SQL Maps: XML Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="classicmodels.dao.mybatis.MyBatisCustomerDao">

  <select id="get" parameterType="long"
    resultType="classicmodels.domain.Customer">
    SELECT * FROM Customers WHERE CustomerNumber = #{id}
  </select>

  <select id="findByName" parameterType="java.lang.String"
    resultType="classicmodels.domain.Customer">
    SELECT * FROM Customers
    WHERE  UPPER(CustomerName) LIKE UPPER('%' || #{name} || '%')
    ORDER BY CustomerNumber
  </select>

  <!-- ... -->
</mapper>

MyBatis SQL Maps: More Complex Queries

<select id="findWithOrdersOrPaymentsInMonth"
  resultType="classicmodels.domain.Customer">
  SELECT *
  FROM Customers c
  WHERE EXISTS (
    SELECT *
    FROM   Orders o
    WHERE  o.CustomerNumber = c.CustomerNumber
      AND  EXTRACT(YEAR FROM OrderDate) = #{0}
      AND  EXTRACT(MONTH FROM OrderDate) = #{1}
  )
  OR    EXISTS (
    SELECT *
    FROM   Payments p
    WHERE  p.CustomerNumber = c.CustomerNumber
      AND  EXTRACT(YEAR FROM PaymentDate) = #{0}
      AND  EXTRACT(MONTH FROM PaymentDate) = #{1}
  )
  ORDER BY CustomerNumber
</select>

(Compare with Spring)

public List<Customer> findWithOrdersOrPaymentsInMonth(int year, int month) {
  return new NamedParameterJdbcTemplate(getDataSource()).query(
    "SELECT * " +
    "FROM customers c " +
    "WHERE EXISTS (" +
      "SELECT * " +
      "FROM orders o " +
      "WHERE o.customernumber = c.customernumber " +
        "AND EXTRACT(YEAR FROM orderdate) = :year " +
        "AND EXTRACT(MONTH FROM orderdate) = :month" +
    ") " +
      "OR  EXISTS (" +
      "SELECT * " +
      "FROM payments p " +
      "WHERE p.customernumber = c.customernumber " +
        "AND EXTRACT(YEAR FROM paymentdate) = :year " +
        "AND EXTRACT(MONTH FROM paymentdate) = :month" +
    ")" +
    "ORDER BY customernumber",
    new MapSqlParameterSource("year", year).addValue("month", month),
    new CustomerRowMapper()
  );
}

MyBatis SQL Maps: Conditional Criteria

<select id="findForFilter" parameterType="classicmodels.dao.CustomerFilter"
  resultType="classicmodels.domain.Customer">
  SELECT *
  FROM   Customers c
  <where>
    <if test="name != null">
      AND (   UPPER(CustomerName) LIKE '%' || UPPER(#{name}) || '%'
           OR UPPER(ContactFirstName) LIKE '%' || UPPER(#{name}) || '%'
           OR UPPER(ContactLastName) LIKE '%' || UPPER(#{name}) || '%' )
    </if>
    <if test="phone != null">
      AND (   phone = #{phone} )
    </if>
    <!-- similar for address -->
    <if test="minimumCreditLimit != null">
      AND creditLimit > #{minimumCreditLimit}
    </if>
    <!-- similar for maximumCreditLimit -->
  </where>
  ORDER BY CustomerNumber
</select>

(Compare with JPA)

public List<Customer> findForFilter(final CustomerFilter filter) {
  CriteriaBuilder builder = em.getCriteriaBuilder();
  CriteriaQuery<Customer> criteriaQuery = builder.createQuery(Customer.class);
  Root<Customer> customer = criteriaQuery.from(Customer.class);
  List<Predicate> conditions = new ArrayList<Predicate>();
  if (filter.getName() != null && !filter.getName().equals("")) {
    String name = "%" + filter.getName().toUpperCase() + "%";
    conditions.add(builder.or(
      builder.like(builder.upper(customer.get("customerName").as(String.class)), name),
      builder.like(builder.upper(customer.get("contactLastName").as(String.class)), name),
      builder.like(builder.upper(customer.get("contactFirstName").as(String.class)), name)
    ));
  }
  // similar for address and phone ...
  if (filter.getMinimumCreditLimit() != null) {
    BigDecimal minimumCreditLimit = filter.getMinimumCreditLimit();
    conditions.add(
      builder.gt(customer.get("creditLimit").as(Number.class), minimumCreditLimit)
    );
  }
  // similar for maximumCreditLimit ...
  Predicate predicate = builder.and(conditions.toArray(new Predicate[conditions.size()]));
  criteriaQuery.where(predicate);
  TypedQuery<Customer> query = em.createQuery(criteriaQuery);
  return query.getResultList();
}

A gnarlier problem

@Entity
@Table(name = "Employees")
public class Employee implements Comparable<Employee> {
  // ...

  @OneToMany(mappedBy = "reportsTo")
  @Sort(type = SortType.NATURAL)
  public SortedSet<Employee> getDirectReports() {
    return directReports;
  }

  @Transient
  public SortedSet<Employee> getAllReports() {
    SortedSet<Employee> result = new TreeSet<Employee>();
    for (Employee directReport : directReports) {
      result.add(directReport);
      result.addAll(directReport.getAllReports());
    }
    return result;
  }
}

Using MyBatis SQL Maps

Define a DAO method for the query:

public interface MyBatisEmployeeDao extends EmployeeDao {

  List<Employee> getAllReports(long id);

}

<select id="getAllReports" parameterType="long"
  resultType="classicmodels.domain.Employee">
  WITH r(Manager, EmployeeNumber) AS (
    SELECT ReportsTo, EmployeeNumber FROM Employees WHERE ReportsTo = #{id}
    UNION ALL
    SELECT r.EmployeeNumber, e.EmployeeNumber
    FROM r JOIN Employees e ON e.ReportsTo = r.EmployeeNumber
  )
  SELECT e.* FROM r JOIN Employees e ON e.EmployeeNumber = r.EmployeeNumber
</select>

Other MyBatis SQL Maps Features

• Maps for params and results
<update> and <insert>
<selectKey> for <insert>
Reusable <sql> fragments
Caching
Associations and collections
Lazy loading

DbUnit

• Prime the database:

DbUnit can set up tables with known contents before each test method.

Verify the database

DbUnit makes it easier to verify tables' contents after each test method.

DbUnit: A Simple Test

public class FlatXmlSelectOwnerTest extends DBTestCase {

  protected IDatabaseTester newDatabaseTester() throws Exception {
    return new DataSourceDatabaseTester(DataSourceUtils.getDataSource());
  }

  protected IDataSet getDataSet() throws Exception {
    return new FlatXmlDataSet(getClass().getResourceAsStream("FlatXmlSelectOwnerTest.xml"));
  }

  // ...

  public void testLoadOwner() {
    OwnerDao ownerDao = getOwnerDao();
    Owner joe = ownerDao.loadOwner(2);
    assertOwner(joe, 2, "Joe", "Jeffries", "25 Baywater Lane", "Northbrook", "555-2345678");
  }

  public void testFindOwners() {
    OwnerDao ownerDao = getOwnerDao();
    Iterator<Owner> owners = ownerDao.findOwners("smith").iterator();
    assertOwner(owners.next(), 1, "Mandy", "Smith", "12 Oxford Street", "Southfield", "555-1234567");
    assertOwner(owners.next(), 4, "Dave", "Smith-Jones", "12 Kent Way", "Southfield", "555-4567890");
    assertFalse(owners.hasNext());
  }
}

DbUnit: FlatXmlDataSet

<dataset>
  <owners id="1" first_name="Mandy" last_name="Smith"
    address="21 Oxford Street" city="Southfield" telephone="555-1234567"/>
  <owners id="2" first_name="Joe" last_name="Jeffries"
    address="25 Baywater Lane" city="Northbrook" telephone="555-2345678"/>
  <owners id="3" first_name="Herb" last_name="Dalton"
    address="2 Main St" city="Southfield" telephone="555-3456789"/>
  <owners id="4" first_name="Dave" last_name="Smith-Jones"
    address="12 Kent Way" city="Southfield" telephone="555-4567890"/>
  <pets/>
  <visits/>
</dataset>

DbUnit: Specifying NULLs by Omission

<dataset>
  <owners id="1" first_name="Mandy" last_name="Smith"
    address="12 Oxford Street" city="Southfield" telephone="555-1234567"/>
  <owners id="2" first_name="Joe" last_name="Jeffries"
    address="25 Baywater Lane" telephone="555-2345678"/>
  <owners id="3" first_name="Herb" last_name="Dalton"
    address="2 Main St" city="Southfield"/>
  <owners id="4" first_name="Dave" last_name="Smith-Jones"
    address="12 Kent Way" city="Southfield" telephone="555-4567890"/>
  <pets/>
  <visits/>
</dataset>

DbUnit: Verifying an Update

public void testUpdateOwner() throws Exception {
  OwnerDao ownerDao = getOwnerDao();
  Owner owner = ownerDao.loadOwner(1);
  owner.setFirstName("Mandy-Jane");
  owner.setLastName("Brown");
  owner.setAddress("21 Ocean Parade");
  owner.setCity("Westport");
  owner.setTelephone("555-9876543");
  ownerDao.storeOwner(owner);
  IDataSet expectedDataSet = new FlatXmlDataSet(
    getClass().getResourceAsStream("FlatXmlUpdateOwnerTest.xml")
  );
  ITable expectedTable = expectedDataSet.getTable("owners");
  ITable actualTable = getConnection().createDataSet().getTable("owners");
  Assertion.assertEquals(expectedTable, actualTable);
}

DbUnit Structure: TestCase classes

DbUnit Structure: IDatabaseConnection

DbUnit Structure: IDataSet

DbUnit Structure: ITable

DbUnit Structure: ITableMetaData

DbUnit: XLS DataSet

protected IDataSet getDataSet() throws Exception {
  return new XlsDataSet(getClass().getResourceAsStream("XlsSelectOwnerTest.xls"));
}

DbUnit: CSV DataSet

id,first_name,last_name,address,city,telephone
1,Mandy,Smith,12 Oxford Street,Southfield,555-1234567
2,Joe,Jeffries,25 Baywater Lane,Northbrook,555-2345678
3,Herb,Dalton,2 Main St,Southfield,555-3456789
4,Dave,Smith-Jones,12 Kent Way,Southfield,555-4567890

protected IDataSet getDataSet() throws Exception {
  return new CsvDataSet(
    new File("src/test/nzoug/petclinic/dao/test/csv/")
  );
}

DbUnit: Inline DataSet

public class InlineDataSet {
  public static IDataSet dataSet(ITable... tables) {
    return new DefaultDataSet(tables);
  }

  public static ITable table(String name, String[] cols, String[]... data)
    throws DataSetException {
    Column[] columns = new Column[cols.length];
    for (int i = 0; i < cols.length; i++) {
      columns[i] = new Column(cols[i], DataType.UNKNOWN);
    }
    DefaultTable result = new DefaultTable(name, columns);
    for (String[] row : data) {
      result.addRow(row);
    }
    return result;
  }

  public static String[] col(String... columns) {
    return columns;
  }

  public static String[] row(String... data) {
    return data;
  }
}

DbUnit: Inline DataSet Usage

protected IDataSet getDataSet() throws Exception {
  return dataSet(
    table("owners",
      col("id", "first_name", "last_name", "address", "city", "telephone"),
      row("1", "Mandy", "Smith"      , "12 Oxford Street", "Southfield", "555-1234567"),
      row("2", "Joe"  , "Jeffries"   , "25 Baywater Lane", "Northbrook", "555-2345678"),
      row("3", "Herb" , "Dalton"     , "2 Main St"       , "Southfield", "555-3456789"),
      row("4", "Dave" , "Smith-Jones", "12 Kent Way"     , "Southfield", "555-4567890")
    ),
    table("pets", col("id")),
    table("visits", col("id"))
  );
}

DbUnit: NULL with ReplacementDataSet

protected IDataSet getDataSet() throws Exception {
  IDataSet result = dataSet(
    table("owners",
      col("id", "first_name", "last_name", "address", "city", "telephone"),
      row("1", "Mandy", "Smith"      , "12 Oxford Street", "Southfield", "(NULL)"),
      row("2", "Joe"  , "Jeffries"   , "25 Baywater Lane", "(NULL)"    , "555-2345678"),
      row("3", "Herb" , "Dalton"     , "2 Main St"       , "Southfield", "(NULL)"),
      row("4", "Dave" , "Smith-Jones", "12 Kent Way"     , "(NULL)"    , "555-4567890")
    ),
    table("pets", col("id")),
    table("visits", col("id"))
  );
  Map objectMap = new HashMap(1);
  objectMap.put("(NULL)", null);
  return new ReplacementDataSet(result, objectMap, null);
}

JDBC Logging with jdbcdslog

SLF4J/logback.groovy configuration:

root(INFO, ["FILE"])
logger("org.jdbcdslog.ConnectionLogger", OFF)
logger("org.jdbcdslog.StatementLogger", INFO)
logger("org.jdbcdslog.ResultSetLogger", INFO)
logger("org.jdbcdslog.SlowQueryLogger", INFO)

  OracleDataSource oracleDataSource = new OracleDataSource();
  oracleDataSource.setURL("jdbc:oracle:thin:@oracle11gr2xe:1521:XE");
  oracleDataSource.setUser("birt");
  oracleDataSource.setPassword("birt");
  ConnectionPoolDataSourceProxy loggedDataSource =
    new ConnectionPoolDataSourceProxy();
  loggedDataSource.setTargetDSDirect(oracleDataSource);
  dataSource = loggedDataSource;

jdbcdslog Sample Output

2011-10-29 17:35:30.319 INFO org.jdbcdslog.StatementLogger - SELECT * FROM customers WHERE customernumber = 114;
2011-10-29 17:35:30.320 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {114, 'Australian Collectors, Co.', 'Ferguson', 'Peter', '03 9520 4555', '636 St Kilda Road', 'Level 3', 'Melbourne', 'Victoria', '3004', 'Australia', 1611, 117300}
2011-10-29 17:35:44.429 INFO org.jdbcdslog.StatementLogger - SELECT * FROM customers WHERE UPPER(customername) LIKE '%IMPORTS%' ORDER BY customernumber;
2011-10-29 17:35:44.460 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {121, 'Baane Mini Imports', 'Bergulfsen', 'Jonas ', '07-98 9555', 'Erling Skakkes gate 78', null, 'Stavern', null, '4110', 'Norway', 1504, 81700}
2011-10-29 17:35:44.461 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {145, 'Danish Wholesale Imports', 'Petersen', 'Jytte ', '31 12 3555', 'Vinbæltet 34', null, 'Kobenhavn', null, '1734', 'Denmark', 1401, 83400}
2011-10-29 17:35:44.463 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {169, 'Porto Imports Co.', 'de Castro', 'Isabel ', '(1) 356-5555', 'Estrada da saúde n. 58', null, 'Lisboa', null, '1756', 'Portugal', null, 0}
2011-10-29 17:35:44.463 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {171, 'Daedalus Designs Imports', 'Rancé', 'Martine ', '20.16.1555', '184, chaussée de Tournai', null, 'Lille', null, '59000', 'France', 1370, 82900}
2011-10-29 17:35:44.464 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {303, 'Schuyler Imports', 'Schuyler', 'Bradley', '+31 20 491 9555', 'Kingsfordweg 151', null, 'Amsterdam', null, '1043 GR', 'Netherlands', null, 0}
2011-10-29 17:35:44.465 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {307, 'Der Hund Imports', 'Andersen', 'Mel', '030-0074555', 'Obere Str. 57', null, 'Berlin', null, '12209', 'Germany', null, 0}
2011-10-29 17:35:44.465 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {344, 'CAF Imports', 'Fernandez', 'Jesus', '+34 913 728 555', 'Merchants House', '27-30 Merchant''s Quay', 'Madrid', null, '28023', 'Spain', 1702, 59600}
2011-10-29 17:35:44.466 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {415, 'Bavarian Collectables Imports, Co.', 'Donnermeyer', 'Michael', ' +49 89 61 08 9555', 'Hansastr. 15', null, 'Munich', null, '80686', 'Germany', 1504, 77000}
2011-10-29 17:35:44.467 INFO org.jdbcdslog.ResultSetLogger - java.sql.ResultSet.next {484, 'Iberia Gift Imports, Corp.', 'Roel', 'José Pedro ', '(95) 555 82 82', 'C/ Romero, 33', null, 'Sevilla', null, '41101', 'Spain', 1702, 65700}

Java Database Toolbox

• John Hurst
• john.hurst@skepticalhumorist.co.nz
• http://www.skepticalhumorist.co.nz
• http://skepticalhumorist.blogspot.com/
• @skepticalhumor