Introduction

Author: Renato Brunella

This is a quick introduction to QueryObjectFactory's most important features. Read through it to get an idea of what QueryObjectFactory is and isn't.

Basics

QueryObjectFactory is a fast, lightweight runtime JDBC code generator. In this section we will have a look at the basic functionality that QueryObjectFactory provides for CRUD operations i.e. SQL select, insert, update and delete statements.

Let's start with an easy example: We have a table PERSON that has three column:

CREATE TABLE PERSON (
  id         NUMERIC(5),
  first_name VARCHAR(20),
  last_name  VARCHAR(20),
  PRIMARY KEY (id)
)

Next we define a Java class Person that has public getters and setters for three fields that we want map to the columns in table PERSON:

package sf.qof.intro; public class Person { private int id; private String firstName; private String lastName; public void setId(int id) { this.id = id; } public int getId() { return id; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getFirstName() { return firstName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getLastName() { return lastName; } }

So far we didn't do anything special. Now is the time to create our first query object definition interface. This interface provides a specification that allows QueryObjectFactory to implement a query object class for us.

Let's call the interface PersonDao and add methods to retrieve, add, update and delete Person objects to the PERSON table.

The necessary mapping information that binds parameters and results from SQL statements to the Java object must be specified using Java annotations:

package sf.qof.intro; import java.sql.SQLException; import sf.qof.*; public interface PersonDao extends BaseQuery { @Query(sql = "select id {%%.id}, first_name {%%.firstName}, " + "last_name {%%.lastName} from PERSON where id = {%1}") Person getPerson(int id) throws SQLException; @Insert(sql = "insert into PERSON (id, first_name, last_name) " + "values ({%1.id}, {%1.firstName}, {%1.lastName})") void insertPerson(Person person) throws SQLException; @Update(sql = "update PERSON set first_name = {%1.firstName}, " + "last_name = {%1.lastName} where id = {%1.id}") void updatePerson(Person person) throws SQLException; @Delete(sql = "delete from PERSON where id = {%1.id}") void deletePerson(Person person) throws SQLException; }

For CRUD operations there are four annotations available: @Insert, @Query, @Update and @Delete. Each of them requires at least the definition of a SQL statement (sql = "..."). Parameter and result mappings are defined within the SQL statements in curly brackets: {%%.id} etc.

A parameter mapping is defined by a percent character followed by a number that defines the position of the parameter in the query method. For example in the getPerson query method {%1} refers to the id parameter. To map parameters that refer to a field within a Java object you need to add a dot and the name of the field: {%1.firstName}. There must be a public getter method defined for that field String Person.getFirstName() according to the JavaBean specification.

QueryObjectFactory is able to determine the type of the parameter for all primitive Java types and their boxed version (int, Integer, String, Date etc.) It uses reflection to do this. Sometimes it is necessary to explicitly specify the type and in this case you add it before the percent character: {int%1}. More on this and a list of all supported predefined mapping options can be found in the API documentation and the tutorial.

A result mapping is defined similar to a parameter definition by two percent characters: {%%}. If the result of the query method is a JavaBean object we can specify which field in the object we want to map to. In the example above we want to map the last_name column returned by the SQL select statement to the field lastName. We do this by adding a dot followed by the field name to the result mapping: {%%.lastName}. There must be a public setter method defined for that field void Person.setLastName(String lastName) according to the JavaBean specification.

The interface that defines the query methods should normally extend the BaseQuery interface. This interface defines methods to set the database connection, fetch size and batch size. QueryObjectFactory will always implement these methods regardless if the interface extends BaseQuey or not.

All query methods should have a throws SQLException clause to enable proper exception handling.

Now that we understand the basics of how to map parameters and results we want to use QueryObjectFactory to implement the PersonDao interface for us:

package sf.qof.intro; import java.sql.*; import sf.qof.*; public class PersonDaoTest { public static void main(String[] args) throws SQLException { PersonDao personDao = QueryObjectFactory.createQueryObject(PersonDao.class); Connection connection = newConnection(); try { personDao.setConnection(connection); Person person = personDao.getPerson(1); if (person == null) { System.out.println("Not found"); } else { System.out.println(person.getFirstName() + " " + person.getLastName()); } } finally { connection.close(); } } private static Connection newConnection() throws SQLException { // returns a new database connection } }

The call to QueryObjectFactory.createQueryObject(PersonDao.class) will return an instance of a class that implements the PersonDao interface. QueryObjectFactory caches generated class therefore only the first call to implement a certain query interface will use code generation. Subsequent calls simply create a new instance of the class which greatly enhances performance.

Now that we have an object that implements PersonDao we just need to set the database connection using personDao.setConnection() and we are ready to run the first query.

The call to personDao.getPerson(1) will return a new Person object if there is a row in the PERSON table with id = 1 or null if no result was found or throws a SQLException if more than one result can be found.

Java Collections

QueryObjectFactory provides powerful support for Java collections both as parameters and results. Java Collections that are supported are java.util.List, java.util.Set and java.util.Map.

For example to have a method that returns a list of persons with a given last name we simply need to add:

    @Query(sql = "select id {%%.id}, first_name {%%.firstName}, " +
        "last_name {%%.lastName} from PERSON where last_name = {%1}")
    List<Person> findPersonByLastName(String lastName) throws SQLException;

to PersonDao. QueryObjectFactory will implement a method that returns a list of Person objects.

The BaseQuery interface provides a method setFetchSize(int) that allows to set how many records should be fetched from the database at one time to enhance performance in large result sets.

Collections that are passed in as parameters will be implemented using batch processing:

    @Insert(sql = "insert into PERSON (id, first_name, last_name) " +
        "values ({%1.id}, {%1.firstName}, {%1.lastName})")
    void insertPersons(List<Person> personList) throws SQLException;

In the method above you can pass in a list of Person objects that get inserted into the PERSON table using batch processing to enhance performance.

The BaseQuery interface provides a method setBatchSize(int) that allows to set how many records should sent in a batch to the database.

For more details check the tutorial section.

Required libraries

The following library files need to be on the classpath:

• qof-RC3-1.0.0.jar
• cglib-2.1_3.jar
• asm-1.5.3.jar

Alternatively there is a library file without external dependencies:

• qof-nodependency-RC3-1.0.0.jar

QueryObjectFactory uses Code Generation Library (cglib) and ASM for the byte-code generation. Therefore these two components as well as the qof-library need to be on the classpath.

You can find all necessary libraries in the Download Section.