[ Back to contents ]

2. Preprocessor

1. Introduction - first example

Let us consider a simple example - we need to manipulate following database table EMP:

Column name Column type Nullable?
EMP_NO NUMBER(4) NOT NULL
EMP_NAME VARCHAR2(10) NOT NULL
JOB VARCHAR2(9) NULL
MGR NUMBER(4) NULL
HIRE_DATE DATE NULL

Here is simple data access object which allows to perform SELECT and INSERT operations (see example):

1    package example1.dao; 
2     
3    import sqlg2.*; 
4     
5    import java.sql.PreparedStatement; 
6    import java.sql.SQLException; 
7    import java.sql.Timestamp; 
8    import java.util.List; 
9     
10   @SQLG 
11   public final class Example1 extends GBase { 
12    
13       public Example1(LocalWrapperBase lwb) { 
14           super(lwb); 
15       } 
16    
17       @RowType 
18       public abstract static class EmpRow { 
19       } 
20    
21       @Business 
22       public List<EmpRow> selectAll() throws SQLException { 
23           /** 
24            * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE 
25            *   FROM EMP 
26            * ORDER BY EMP_NO 
27            */ 
28           @Prepare PreparedStatement stmt = null; 
29           return multiRowQuery(stmt, EmpRow.class); 
30       } 
31    
32       @Business 
33       public void insert(int empNo, String empName, String job, 
34                          Integer manager, Timestamp hireDate) throws SQLException { 
35           /** 
36            * INSERT INTO EMP 
37            *  (EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE) 
38            *  VALUES 
39            *  (:empNo, :empName, :job, :manager, :hireDate) 
40            */ 
41           @Prepare PreparedStatement stmt = null; 
42           executeUpdate(stmt); 
43       } 
44   }

Class piecewise description follows.

DAO class declaration looks like this: 
10   @SQLG 
11   public final class Example1 extends GBase {
@SQLG annotation indicates that this class should be preprocessed; preprocessed class should extend sqlg2.GBase.

Here is the single constructor: 
13       public Example1(LocalWrapperBase lwb) { 
14           super(lwb);
Usually you shouldn't invoke it manually, it is called by runtime system.

Now comes one of the main features of SQLG - row type class declaration: 
17       @RowType
18       public abstract static class EmpRow {  
19       }
@RowType annotation indicates that the class is a row type class; such class should be public, static and abstract. Currently class is empty, its content is created by preprocessor - it inserts data access methods, corresponding to the SELECT statement in the business method following class declaration.

And here is the business method itself: 
21       @Business 
22       public List<EmpRow> selectAll() throws SQLException {
It expresses the idea of SELECT statement quite naturally.

This piece of code shows how you can write pretty SQL statements in Java using SQLG: 
23           /** 
24            * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE 
25            *   FROM EMP 
26            * ORDER BY EMP_NO 
27            */ 
28           @Prepare PreparedStatement stmt = null;
29           return multiRowQuery(stmt, EmpRow.class);
@Prepare annotation is used to mark variables of java.sql.PreparedStatement class, which are assigned with newly created statement corresponding to SQL query in preceding comments with parameters set to referenced variables (see later). Then the method from GBase class is used to perform query. This method should be used to allow preprocessor to find out the row type of result set.

Here is the result of preprocessor work (lines modified or inserted by preprocessor are marked red):

1    package example1.dao; 
2     
3    import sqlg2.*; 
4     
5    import java.sql.PreparedStatement; 
6    import java.sql.SQLException; 
7    import java.sql.Timestamp; 
8    import java.util.List; 
9     
10   @SQLG 
11   public final class Example1 extends GBase { 
12    
13       public Example1(LocalWrapperBase lwb) { 
14           super(lwb); 
15       } 
16    
17       @RowType 
18       public abstract static class EmpRow { 
19    
20           public abstract int empNo(); 
21           public abstract String empName(); 
22           public abstract String job(); 
23           public abstract Integer mgr(); 
24           public abstract java.util.Date hireDate(); 
25       } 
26    
27       @Business 
28       public List<EmpRow> selectAll() throws SQLException { 
29           /** 
30            * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE 
31            *   FROM EMP 
32            * ORDER BY EMP_NO 
33            */ 
34           @Prepare PreparedStatement stmt = prepareStatement("SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE FROM EMP ORDER BY EMP_NO"); 
35           return multiRowQuery(stmt, EmpRow.class); 
36       } 
37    
38       @Business 
39       public void insert(int empNo, String empName, String job, 
40                          Integer manager, Timestamp hireDate) throws SQLException { 
41           /** 
42            * INSERT INTO EMP 
43            *  (EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE) 
44            *  VALUES 
45            *  (:empNo, :empName, :job, :manager, :hireDate) 
46            */ 
47           @Prepare PreparedStatement stmt = prepareStatement("INSERT INTO EMP (EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE) VALUES (?, ?, ?, ?, ?)", new Object[] {empNo, empName, job, manager, hireDate}, new Class<?>[] {Integer.class, String.class, String.class, Integer.class, java.sql.Timestamp.class}); 
48           executeUpdate(stmt); 
49       } 
50    
51       /* PREPROCESSOR GENERATED CODE - DO NOT REMOVE THIS LINE */ 
52    
53       public static final String _IMPLEMENTATION_PACKAGE = "rmi"; 
54    
55       /* PREPROCESSOR GENERATED CODE - DO NOT REMOVE THIS LINE */ 
56   }

You can introduce errors into SQL statements and check what preprocessor says about them - in most cases it won't let you run invalid SQL.

But that's not all - there are also generated wrappers; see next section and example of their usage

2. How preprocessor works

Preprocess phase 1. Parser scans Java class text searching for SQLG annotations (unfortunately, apt is inapt to do this - it does not provide enough information on token locations and local variable annotations). Class should be compilable (without syntactic errors), else unpredictable results can occur (but it won't spoil the original file - incorrect files only can produce strange errors). When it finds @RowType or @EditableRowType annotation, it marks following class body as generated row type class. When it finds @Business annotation, it finds out method return type, method name and parameter names and stores them for later method execution. Preprocessor parses each method body in search of @Sql, @Prepare or @Call annotation and when founds it, uses the value taken from preceding comments to generate query string or statement preparation or execution code. Then the correspondence between business methods and generated row type classes is established: row type class is generated by the first business method following it in text.

Preprocess phase 2. All processed files after phase 1 (with replaced @Sql, @Prepare and @Call variables) are compiled, and then their business methods are called. At this point we need a database connection. If business method has corresponding row type class then the last (in this method) performed query result set is examined and its columns are converted to row type class access methods. Of course, only SELECT queries are executed at preprocess-time, but there is ability to check SQL statements of other types at preprocess-time too (currently it is implemented only for Oracle). Because of this phase, you should not call some JDBC methods in business methods (more specific - statement execution methods) but use their SQLG replacements, which are sensitive to preprocessor state and do not really perform DB operations during preprocess. If you really need it, you can check if method is called by preprocessor with isTesting() method.

There is also a separate branch of preprocessor - wrapper generation. Wrappers are generated in the second phase and loosely coupled with other preprocessor mechanics (you can even write your own wrapper generators, they are pluggable). Currently following wrappers generated:

Consider we have preprocessed file TestDAO.java. Then we'll have following files after preprocessing:

But to hide some complexity under the carpet, we have possibility to generate some of these files in other packages. There are two options passed to the preprocessor: It is convenient to set them to some equal values (in most examples it is rmi subpackage). In this case you can even not know what goes on in this subpackage (you need not reference this classes manually, SQLG framework does it for you) and it is easy to say SQLG to force re-generate all files - simply deleting this subpackage (it may be useful in case of DB change that unsynchronizes DB schema state with your Java files - SQLG can only take file changes into account).

To let SQLG know in which package to search for generated classes, special static fields are added to processed file. In order SQLG runtime to work properly, do not remove these fields (together with generated comments, which are essential too, but only for preprocessor, not runtime system).

3. ANT task for SQLG

Class name of the task is sqlg2.Preprocess. Task classpath should contain full version of SQLG library.

ANT task attributes:
Attribute Description Required
srcroot source files root directory. Package of class is determined relative to the source root. Yes
url JDBC URL to use for DB connection. Yes
user user for DB connection. Yes
password user's password for DB connection. Yes
classpath classpath to use when running preprocessor. Used in the second phase during compilation. Must include SQLG library itself (full version, not runtime), JDBC driver library, and other libraries that are referenced by processed classes. Yes
tabsize tab size (negative for tab character); defaults to 4. No
implpack subpackage name to put implementation class. Same package as input file by default. No
rmipack subpackage name to put wrapper classes. Wrapper classes are not generated by default. No
force whether to force generation of files; defaults to false, which means that preprocessor checks modification time of processed file against modification time of its generated implementation file, and if main file was not modified, no generation is performed. No
driverclass JDBC driver class name. Defaults to oracle.jdbc.driver.OracleDriver. No
mapperclass name of the class implementing sqlg2.Mapper interface. Defaults to sqlg2.MapperImpl. No
dbclass name of the class implementing sqlg2.db.DBSpecific interface. Defaults to sqlg2.db.OracleDBSpecific. No
wrapperclass name of the class implementing sqlg2.WrapperGeneratorFactory interface. Defaults to sqlg2.DefaultWrapperGeneratorFactory. No

Task allows nested filesets to specify which files to preprocess.

Example: 
<target name="preprocess">
    <taskdef name="sqlg" classname="sqlg2.Preprocess"
             classpath="${sqlg.jar};${jdbc.lib}"/>
    <sqlg classpath="${sqlg.jar};${jdbc.lib}"
          url="${jdbc.url}"
          user="sqlg2" password="sqlg2"
          implpack="rmi" rmipack="rmi"
          srcroot="${src.dir}">
        <fileset dir="${src.dir}">
            <include name="example1/dao/Example1.java"/>
        </fileset>
    </sqlg>
</target>

4. Details of the first preprocess phase

There are two more useful features of @Sql, @Prepare and @Call tag substitution. First, you can embed variables in your string constants, for example: 
String table = "EMP";
/**
 * SELECT *
 *   FROM &table
 * ORDER BY ID
 */
@Sql String sql = "";
last line is replaced with 
@Sql String sql = "SELECT * FROM " + table + " ORDER BY ID";
Be warned though, that you better not use such substitution for parameters (as in SELECT * FROM EMP WHERE EMP_NO=&param) because it can lead to poor DB performance: use @Prepare and @Call annotations together with ":variable" parameters or PreparedStatement.setXXX methods instead.

Second, you can use :<number> instead of usual question marks for parameters: 
/**
 * SELECT EMP_NO, EMP_NAME
 *   FROM EMP
 *  WHERE JOB=:1
 *    AND MGR=:2
 */
@Sql String sql = "";
SQL statement is replaced with "SELECT EMP_NO, EMP_NAME FROM EMP WHERE JOB=? AND MGR=?"; this has the advantage that you can see in your code what parameter numbers are to set them by PreparedStatement.setXXX methods.

Another very useful feature is generation of statement preparation/execution code. If you use named parameters in SQL statement, then their values can be substituted from variables with the same names, for example: 
String job = "ANALYST";
int manager = 1;
/**
 * SELECT EMP_NO, EMP_NAME
 *   FROM EMP
 *  WHERE JOB=:job
 *    AND MGR=:manager
 */
@Sql String sql = "";
@Prepare PreparedStatement stmt = null;
will generate 
@Sql String sql = "SELECT EMP_NO, EMP_NAME FROM EMP WHERE JOB=? AND MGR=?";
@Prepare PreparedStatement stmt = prepareStatement(sql, new Object[] {job, manager}, new Class<?>[] {String.class, Integer.class});
Parameter classes are determined not by its declared type but by its runtime type. For callable statement execution you may need to specify out parameters, it can be done in the following way: 
String empName = "SCOTT";
int[] empNo = new int[1];
/**
 * BEGIN
 *   INSERT INTO EMP (EMP_NO, EMP_NAME)
 *   VALUES (EMP_SEQ.NEXTVAL, :empName)
 *   RETURNING EMP_NO INTO :>empNo;
 * END;
 */
@Call Object[] ret = null;
generated code will be 
@Call Object[] ret = executeCall("BEGIN INSERT INTO EMP (EMP_NO, EMP_NAME) VALUES (EMP_SEQ.NEXTVAL, ?) RETURNING EMP_NO INTO ?; END;", new Object[] {empName, out(empNo)}, new Class<?>[] {String.class, Integer.class});
You should use arrays for out parameters and specify ">" sign in the query.

5. Details of the second preprocess phase

Names of column access methods are generated by the following rule: all underscores are translated to case change. For example, EMP_NO is translated to empNo, HIRE_DATE to hireDate (note that this behavior is implemented by the default mapper implementation sqlg2.MapperImpl which you can change). So if you're only going to design your DB schema it is good to have underscores in column names to get well-readable names.

If you use functions (like in "SELECT COUNT(*) FROM EMP") without column aliases in your queries then generated method names will be incorrect! You will have to delete these invalid methods and re-run preprocessor with correctly given aliases: "SELECT COUNT(*) emp_Count FROM EMP"

If you use @EditableRowType instead of @RowType then setter methods will also be generated. But these setters do not affect database state! They just change in-memory fields, it is sometimes convenient to use row type classes to store some variable data, not only DB data.

6. Details of sqlg2.GBase methods

sqlg2.GBase has methods which can be used in data access objects; they can be divided into following groups:

Let us consider different use cases.
SELECT statement with static SQL
SELECT statement with dynamic SQL with fixed column and parameter list
SELECT statement with dynamic SQL with fixed column list and varying parameter list
INSERT/UPDATE/DELETE statements
Stored procedures and PL/SQL blocks
Free-form statements

SELECT statement with static SQL

Preferred way to select rows is to declare row type class and let preprocessor generate all required code to prepare statement for execution; then you can call statement execution method matching your needs: 
@RowType 
public abstract static class EmpRow {
}

@Business
public List<EmpRow> selectByJob(String job) throws SQLException {
    /**
     * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE
     *   FROM EMP
     *  WHERE JOB=:job
     * ORDER BY EMP_NO
     */
    @Prepare PreparedStatement stmt = null;
    return multiRowQuery(stmt, EmpRow.class);
}
You can call columnOf..., singleRowQuery... or optionalRowQuery... methods if you need. There are longer alternative versions of this code, you can use them if you wish: 
@Business
public List<EmpRow> selectByJob(String job) throws SQLException {
    /**
     * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE
     *   FROM EMP
     *  WHERE JOB=?
     * ORDER BY EMP_NO
     */
    @Sql String sql = null;
    PreparedStatement stmt = prepareStatement(sql);
    stmt.setString(1, job);
    return multiRowQuery(stmt, EmpRow.class);
}
or 
@Business
public List<EmpRow> selectByJob(String job) throws SQLException {
    /**
     * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE
     *   FROM EMP
     *  WHERE JOB=?
     * ORDER BY EMP_NO
     */
    @Sql String sql = null;
    PreparedStatement stmt = prepareStatement(sql);
    setParameters(stmt, job);
    return multiRowQuery(stmt, EmpRow.class);
}
or 
@Business
public List<EmpRow> selectByJob(String job) throws SQLException {
    String sql = "SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE" +
                 "  FROM EMP" +
                 " WHERE JOB=?" +
                 " ORDER BY EMP_NO";
    PreparedStatement stmt = prepareStatement(sql);
    stmt.setString(1, job); // or setParameters(stmt, job);
    return multiRowQuery(stmt, EmpRow.class);
}

SELECT statement with dynamic SQL with fixed column and parameter list

Preferred way to select rows is to declare row type class and let preprocessor generate all required code to prepare statement for execution; SQL query is dynamically generated by preprocessor; then you can call statement execution method matching your needs: 
@RowType 
public abstract static class EmpRow {
}

@Business
public List<EmpRow> selectByJob(String job, String order) throws SQLException {
    /**
     * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE
     *   FROM EMP
     *  WHERE JOB=:job
     * ORDER BY &order
     */
    @Prepare PreparedStatement stmt = null;
    return multiRowQuery(stmt, EmpRow.class);
}
You can call columnOf..., singleRowQuery... or optionalRowQuery... methods if you need. Alternative forms (see first case) can be applied too.

SELECT statement with dynamic SQL with fixed column list and varying parameter list

Preferred way to select rows is to declare row type class and manually prepare statement for execution; then you can call statement execution method matching your needs: 
@RowType 
public abstract static class EmpRow {
}

@Business
public List<EmpRow> selectByManager(Integer manager) throws SQLException {
    /**
     * SELECT EMP_NO, EMP_NAME, JOB, MGR, HIRE_DATE
     *   FROM EMP
     *  WHERE 
     */
    @Sql String sql = null;
    StringBuilder buf = new StringBuilder(sql);
    if (manager == null) {
        buf.append(" MGR IS NULL");
    } else {
        buf.append(" MGR=?");
    }
    buf.append(" ORDER BY EMP_NO");
    PreparedStatement stmt = prepareStatement(buf.toString());
    if (manager != null) {
        stmt.setInt(1, manager.intValue());
    }
    return multiRowQuery(stmt, EmpRow.class);
}
You can call columnOf..., singleRowQuery... or optionalRowQuery... methods if you need.

INSERT/UPDATE/DELETE statements

This case is similar to the first three cases, but you should execute statement with executeUpdate method.

Stored procedures and PL/SQL blocks

Preferred way to execute stored procedure or PL/SQL block is to let preprocessor generate all required code to execute statement: 
@Business
public int createEmp(String empName) throws SQLException {
    int[] empNo = new int[1];
    /**
     * BEGIN
     *   INSERT INTO EMP (EMP_NO, EMP_NAME)
     *   VALUES (EMP_SEQ.NEXTVAL, :empName)
     *   RETURNING EMP_NO INTO :>empNo;
     * END;
     */
    @Call Object[] ret = null;
    return empNo[0];
}
Other possible variants are: 
@Business
public int createEmp(String empName) throws SQLException {
    /**
     * BEGIN
     *   INSERT INTO EMP (EMP_NO, EMP_NAME)
     *   VALUES (EMP_SEQ.NEXTVAL, ?)
     *   RETURNING EMP_NO INTO ?;
     * END;
     */
    @Sql String sql = null;
    Object[] ret = callStoredBlock(sql, empName, new OutParameter(Types.INTEGER));
    return (Integer) ret[0];
}
or 
@Business
public int createEmp(String empName) throws SQLException {
    /**
     * BEGIN
     *   INSERT INTO EMP (EMP_NO, EMP_NAME)
     *   VALUES (EMP_SEQ.NEXTVAL, ?)
     *   RETURNING EMP_NO INTO ?;
     * END;
     */
    @Sql String sql = null;
    CallableStatement cs = prepareCall(sql);
    cs.setString(1, empName);
    cs.registerOutParameter(2, Types.INTEGER);
    executeCall(cs);
    Integer empNo = getInt(cs, 2);
    endCall(cs);
    return empNo == null ? 0 : empNo.intValue();
}

Free-form statements

If you need some capabilities not directly supported by SQLG, you can switch it off in the business method and use pure JDBC. For example: 
@Business
public void doSomething() throws SQLException {
    if (isTesting())
        return; // no need for preprocessor services
    // now we can do what we want - this code will
    // be run only at runtime, not preprocess time
    PreparedStatement ps = prepareStatement(...);
    ...
    CallableStatement cs = prepareCall(...);
    ...
    Statement st = createStatement(...);
    ...
}

You still can use @Sql annotation, since it affects only first preprocess phase, but not other services - row type generation and statement code generation. You can also use @BusinessNoSql annotation on business methods which do not want to be called by preprocessor.

[ Back to contents ] [ To next chapter ]