Quick Start Tutorial

Author: Renato Brunella

This tutorial takes the example from the Introduction and creates a running example in the form of a person repository. It creates a command line program that allows you to add, delete, change and search for persons in a database.

Tutorial Files

You can find the source code in the Code Repository in the sf.qof.tutorial.quickstart package.

PersonRepositoryExample.java	This file contains the main command line control loop and uses the PersonDAO implementation to add, delete and query persons from the database.
PersonDAO.java	Query definition interface.
Person.java	A person JavaBean.
DatabaseSetup.java	Database setup.

Running the Tutorial

Make sure you built the tutorial jar using Ant.

Use

java -cp dist/qof-tutorial-1.0.0.jar;lib/qof-1.0.0.jar;lib/cglib-2.1_3.jar;lib/asm-1.5.3.jar;lib/hsqldb.jar sf.qof.tutorial.quickstart.PersonRepositoryExample

to run the example (replace ; with : on Unix based systems).

Alternatively under Windows you can use the run.bat batch file:

run.bat sf.qof.tutorial.quickstart.PersonRepositoryExample

Source Code Walkthrough

The PersonRepositoryExample class has a main method that creates a new instance of PersonRepositoryExample and calls doCommandLoop the command loop waiting for some user input.

Initialization of the database and creation of a PersonDAO instance with connection is done in the constructor:

  private PersonRepositoryExample() throws SQLException {
    DatabaseSetup.setup();
    personDao = QueryObjectFactory.createQueryObject(PersonDAO.class);
    Connection connection = DataSourceFactory.getDataSource().getConnection();
    connection.setAutoCommit(true);
    personDao.setConnection(connection);
  }

In this example we use the database connection in auto-commit mode so we do not need to deal with transactions. SQLExceptions that are thrown by PersonDAO are just re-thrown.

The private methods addPerson(), deletePerson(), etc in PersonRepositoryExample are reading user input from the command line and invoking the corresponding method on the PersonDAO query object to query or update the database.

Let's have a look at the query definition interface PersonDAO:

package sf.qof.tutorial.quickstart; import java.sql.SQLException; import java.util.List; import sf.qof.*; public interface PersonDAO extends BaseQuery { @Query(sql = "select id {%%.id}, first_name {%%.firstName}, last_name {%%.lastName}, " + "date_of_birth {%%.dateOfBirth} from person where id = {%1}") Person getPerson(int id) throws SQLException; @Query(sql = "select id {%%.id}, first_name {%%.firstName}, last_name {%%.lastName}, " + "date_of_birth {%%.dateOfBirth} from person") List<Person> listPersons() throws SQLException; @Query(sql = "select id {%%.id}, first_name {%%.firstName}, last_name {%%.lastName}, " + "date_of_birth {%%.dateOfBirth} from person where last_name like {%1}") List<Person> findPersons(String lastName) throws SQLException; @Insert(sql = "insert into person (id, first_name, last_name, date_of_birth) " + "values ({%1.id}, {%1.firstName}, {%1.lastName}, {%1.dateOfBirth})") void addPerson(Person person) throws SQLException; @Update(sql = "update person set first_name = {%1.firstName}, last_name = {%1.lastName}, " + "date_of_birth = {%1.dateOfBirth} where id = {%1.id}") int updatePerson(Person person) throws SQLException; @Delete(sql = "delete from person where id = {%1.id}") int deletePerson(Person person) throws SQLException; @Query(sql = "select max(id) + 1 as next_id {%%} from person") Integer nextId() throws SQLException; }

After you read the Introduction most of the query definition methods should be easy to understand. There are two interesting details we will discuss below:

The updatePerson(Person person) and deletePerson(Person person) methods both have a return type of int but there is not result definition in the SQL statement. The reason for this is that update and delete query methods can not return result sets but if you specify a return type of int (or int[] if a collection parameter is used) QueryObjectFactroy's implementation will return the update-count; that is the number of rows in the database table that were updated. We use this to find out if a person record for a given id was deleted or not:

  if (personDao.deletePerson(new Person(id)) != 1) {
    System.out.println("Not found");
  }

Another detail is the nextId() method:

  @Query(sql = "select max(id) + 1 as next_id {%%} from person")
  Integer nextId() throws SQLException;

This method uses Integer as the result type rather than int. The reason for this is that if the PERSON database table is empty the query will return an empty result set. Using int as the result type would cause the implementation to throw a SQLException as it cannot return a useful result. But if the result type is Integer the implementation will return a null value if no results can be found.

If the result types is a primitive (int, double, etc) the query method throws an exception if the result set contains no rows whereas if the result type is an Java object (String, Person, etc.) a null value is returned.