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.    Contains the main method. An immutable currency object. An employee bean. A cache for departements. An immutable department object. A custom mapping adapter for departments. Database setup.

Running the Tutorial

Make sure you built the tutorial jar using Ant.


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:

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:

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.