Using the API

Table of Contents

SQL queries
CRUD statements
Stored procedures
Compound parameters
DDL loader
Simple batches
Database row processing

SQL queries

The primary SQL Processor class for the META SQL query execution is SqlQueryEngine. The instance of this class holds one META SQL query, one optional explicit mapping rule and possibly a set of optional features. This class can be created using an instance of SqlEngineFactory (or more low level SqlProcessorLoader). The class SqlQueryEngine has a couple of overridden methods for

• a query execution to obtain a list of result classes based on input values

• • query()

• a query execution to obtain a number of database rows based on input values

• • queryCount()

• a META SQL query processing to obtain the final ANSI SQL query based on input values

• • getSql()

The primary and the most complex SQL Processor engine execution method is the next one

public <E> List<E> query(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final Object staticInputValues, 
    final SqlOrder order, 
    final int maxTimeout, 
    final int maxResults,
    final int firstResult,
    final Map<String, Class<?>> moreResultClasses
) throws SqlProcessorException, SqlRuntimeException;

Here are the next parameters

1. session – the SQL Engine session. It can work as a first level cache and the SQL query execution context. The implementation depends on the stack, on top of which the SQL Processor works. For example it can be an Hibernate session.
2. resultClass - the class used for the return values, the SQL query execution output. This class is also named as the output class or the transport class, In fact it's a standard POJO class, which must include all the attributes described in the mapping rule statement. This class itself and all its subclasses must have public constructors without any parameters. All the attributes used in the mapping rule statement must be accessible using public getters and setters. The instances of this class are created on the fly in the process of query execution using the reflection API.
3. dynamicInputValues - the object used for the SQL statement dynamic input values. The class of this object is also named as the input class or the dynamic parameters class. The exact class type isn't important, all the parameters settled into the SQL prepared statement are picked up using the reflection API.
4. staticInputValues - the object used for the SQL statement static input values. The class of this object is also named as the input class or the static parameters class. The exact class type isn't important, all the parameters injected into the SQL query command are picked up using the reflection API. Compared to dynamicInputValues input parameters, parameters in this class shouldn't be produced by an end user to prevent SQL injection threat!
5. order - the ordering directive list. Using the class SqlOrder the ordering rules can be chained. Every ordering rule in this chain should correspond to one META SQL ordering statement.
6. maxTimeout - the max SQL execution time. This parameter can help to protect production system against ineffective SQL query commands. The value is in milliseconds.
7. maxResults - the max number of SQL execution output rows, which can be returned in the result list. The primary usage is to support the pagination.
8. firstResult - the first SQL execution output row to be returned in the case we need to skip some rows in the result set. The primary usage is to support the pagination.
9. moreResultClasses - more result classes used for the return values, like the collections classes or the collections items. They are used mainly for the one-to-one, one-to-many and many-to-many associations.

This method returns the list of the resultClass instances. In the case of any problem the exception SqlProcessorException or SqlRuntimeException can be thrown. The method execution is thread safe. The execution context is inside an instance of SqlContext and in an SQL Engine session.

Similarly to count the rows the primary and the most complex SQL Processor execution method is shown here

public int queryCount(
    final SqlSession session, 
    final Object dynamicInputValues, 
    final Object staticInputValues,
    final SqlOrder order, 
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

The last principal method to obtain the final ANSI SQL query (for example to log it) is shown here

public String getSql(
    final Object dynamicInputValues, 
    final Object staticInputValues, 
    final SqlOrder order
) throws SqlProcessorException, SqlRuntimeException;

There are also the ordering directives as convenient constants

public static final SqlOrder NO_ORDER = SqlOrder.getOrder();
public static final SqlOrder ASC_ORDER = SqlOrder.getAscOrder(1);
public static final SqlOrder DESC_ORDER = SqlOrder.getDescOrder(1);

CRUD statements

The primary SQL Processor class for the META SQL CRUD statement execution is SqlCrudEngine. The instance of this class holds one META SQL statement and possibly a set of optional features. This class can be created using an instance of SqlEngineFactory (or more low level SqlProcessorLoader). The class SqlCrudEngine has a couple of overridden methods for

• an insert statement

• • query()

• a query execution to obtain an unique result

• • get()

• an update statement

• • update()

• a delete statement

• • delete()

• a META SQL statement processing to obtain the final ANSI SQL statement based on input values

• • getInsertSql()
• • getGetSql()
• • getUpdateSql()
• • getDeleteSql()

The primary and the most complex SQL Processor engine execution methods are shown here

public int insert(
    final SqlSession session, 
    final Object dynamicInputValues, 
    final Object staticInputValues,
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

public <E> E get(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final Object staticInputValues, 
    final int maxTimeout,
    final Map<String, Class<?>> moreResultClasses
) throws SqlProcessorException, SqlRuntimeException;

public int update(
    final SqlSession session, 
    final Object dynamicInputValues, 
    final Object staticInputValues,
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

public int delete(
    final SqlSession session, 
    final Object dynamicInputValues, 
    final Object staticInputValues,
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

The insert/update/delete methods return the number of affected rows in the target database. In the case of any problem the exception SqlProcessorException or SqlRuntimeException can be thrown. The method execution is thread safe. The execution context is inside an instance of SqlContext and in an SQL Engine session.

Stored procedures

The primary SQL Processor class for the stored procedures execution is SqlProcedureEngine. The instance of this class holds one META SQL statement, one optional explicit mapping rule and possibly a set of optional features. This class can be created using an instance of SqlEngineFactory (or more low level SqlProcessorLoader). The class SqlProcedureEngine has a couple of overridden methods for

• to call the function, which returns a simple value

• • callFunction()

• to call the procedure, which doesn't return a result set in any form

• • callUpdate()

• to call the procedure, which returns a result set in some form

• • callQuery()

• a META SQL statement processing to obtain the final ANSI SQL statement based on input values

• • getCallSql()

The primary and the most complex SQL Processor engine execution methods are shown here

public Object callFunction(
    final SqlSession session,
    final Object dynamicInputValues,
    final Object staticInputValues, 
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

public int callUpdate(
    final SqlSession session, 
    final Object dynamicInputValues, 
    final Object staticInputValues,
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeException;

public <E> List<E> callQuery(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final Object staticInputValues, 
    final int maxTimeout
) throws SqlProcessorException, SqlRuntimeExceptionn;

In the case of any problem the exception SqlProcessorException or SqlRuntimeException can be thrown. The method execution is thread safe. The execution context is inside an instance of SqlContext and in an SQL Engine session.

Compound parameters

All principal methods have an alternative with compound parameters in SqlControl. This interface includes the following getters

• staticInputValues
• maxTimeout
• maxResults
• firstResult
• order
• moreResultClasses
• features
• processingId
• fetchSize

This is used primarily in generated DAO classes (SQLEP 1.2.x DAO modelling). The implementation class for the interface SqlControl is SqlStandardControl. Compared to the most complex parameters in the principal methods

• dynamicInputValues has to be passed to the methods separately
• features (runtime optional features) has no alternative in the most complex parameters

For example the primary SQL Processor engine query execution method is the next one

public <E> List<E> query(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final SqlControl sqlControl) 
throws SqlProcessorException, SqlRuntimeException

How to load DDL statements

There's a new helper class DDLLoader, which can help to load DDL statements (the supported grammar for the DDL file is simplified). The usage is simple

List<String> ddls = DDLLoader.getDDLs(this.getClass(), "hsqldb.ddl");

To execute the loaded DDL statements, a batch API can be utilized.

Simple batches

The usage is straightforward. Let's say we have a DDL HSQLDB script hsqldb.ddl. To load these DDL statements and execute them, we can use the next code (for the JDBC stack)

List<String> ddls = DDLLoader.getDDLs(this.getClass(), "hsqldb.ddl");
Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
SqlSessionFactory sessionFactory = new JdbcSessionFactory(connection);

SqlSession sqlSession = sessionFactory.getSqlSession();
sqlSession.executeBatch(ddls.toArray(new String[0]));

Database row processing

Unlike the query method

public <E> List<E> query(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final SqlControl sqlControl) 
throws SqlProcessorException, SqlRuntimeException

which returns the complete list of database rows, there's another possibility to process database rows in sequence

public <E> Integer query(
    final SqlSession session, 
    final Class<E> resultClass, 
    final Object dynamicInputValues,
    final SqlControl sqlControl, 
    final SqlRowProcessor<E> sqlRowProcessor) 
throws SqlProcessorException, SqlRuntimeException

There's a new parameter sqlRowProcessor, which is in fact the instance of callback SqlRowProcessor to process the actual database row. The primary business execution method is

boolean processRow(
    E result, 
    int rownum)
throws SqlRuntimeException;

with the next parameters

1. result - the result class instance. The object representation of the database row.
2. rownum - the database row number starting from 1

The return value - true to continue processing, false to stop. The query() return value - the total number of processed database rows.

The tutorial is updated for SQLP 2.6