In-Clause Tutorial

Author: Renato Brunella

This tutorial demonstrates how SQL in-clauses work in QueryObjectFactory. The parameters for in-clauses get passed in as arrays. The example uses an employee/department relationship.

Tutorial Files

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

EmployeeRepositoryExample.java	Contains the main method.
EmployeeDao.java	An immutable currency object.
Employee.java	An employee bean.
DepartmentCache.java	A cache for departements.
Department.java	An immutable department object.
DepartmentAdapter.java	A custom mapping adapter for departments.
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.inclause.EmployeeRepositoryExample

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.inclause.EmployeeRepositoryExample

Source Code Walkthrough

The DatabaseSetup class creates two tables DEPARTMENT and EMPLOYEE and inserts six department records and create 100 random employees. Each employee belongs to one department.

DepartmentCache class uses the Singleton pattern. During initialization it reads all rows from the DEPARTMENT table and caches the Department objects in a map. It uses the DepartmentDao query definition interface:

  private interface DepartmentDao extends BaseQuery {
    @Query(sql = "select code {string%%1,%%*}, name {string%%2} from department")
    Map<String, Department> retrieveDepartments() throws SQLException;
  }

DepartmentCache class provides the findDepartment(String code) and getDepartments() methods to find and list Department objects.

The main method in EmployeeRepositoryExample prints a list of all departments and asks the user to input a comma-separated list of department code. It then creates a string array of department codes and uses it as a parameter for the getEmployeesByDepartment defined in EmployeeDao query definition interface:

package sf.qof.tutorial.inclause; import java.sql.SQLException; import java.util.List; import sf.qof.*; public interface EmployeeDao extends BaseQuery { @Query(sql = "select id {%%.id}, first_name {%%.firstName}, " + "last_name {%%.lastName}, department_code {department%%.department} " + "from employee where department_code in ({%1}) " + "order by last_name, first_name") List<Employee> getEmployeesByDepartment(String[] departmentCodes) throws SQLException; @Query(sql = "select id {%%.id}, first_name {%%.firstName}, " + "last_name {%%.lastName}, department_code {department%%.department}" + "from employee where department_code in ({department%1}) " + "order by last_name, first_name") List<Employee> getEmployeesByDepartment(Department[] departments) throws SQLException; }

QueryObjectFactory supports parameters in SQL in-clauses like "where department_code in ({%1})" through Java arrays. It creates a prepared statement with as many SQL parameters as there are elements in the array.

For example if there are three strings in the departmentCodes array it creates a SQL statement with three SQL parameters ("where department_code in (?,?,?)") and bind the values in the array to the parameters.

EmployeeDao also uses a mapping adapter to map between department_code column and Department objects. The DepartmentAdapter class is responsible for this.

DepartmentAdapter implements the DynamicMappingAdapter interface:

... public Object get(ResultSet rs, String[] columns) throws SQLException { String code = rs.getString(columns[0]); if (code == null) { return null; } else { return DepartmentCache.getInstance().findDepartment(code); } } public void set(PreparedStatement ps, Object value, int[] indexes) throws SQLException { if (value == null) { ps.setNull(indexes[0], java.sql.Types.VARCHAR); } else { ps.setString(indexes[0], ((Department)value).getCode()); } } ...

The get method is responsible to map a SQL column in a result set to an Department object. The adapter uses the DepartmentCache singleton to map a department code to a Department object.

The set method is responsible to map a Department object to a prepared statment.

A special tutorial will give more infomation on how to write custom adapters.