SqlJaTor - Sql to Java Translator

SqlJaTor is the name of the new Ant task provided with SPWrapper that lets you quickly generate source code to execute parametric sql statements.

SPWrapper is able to wrap stored procedure calls into java files, but often it is also needed to execute SQL statements. SPWrapper source code contains the 99% of what is needed to accomplish this task, but some changes were needed. SqlJaTor provides these changes.

You can use SqlJaTor with Oracle 9/10, Postgresql 8.2, Mysql 4.1/5 and SqlServer 2005; since the support for the three later databases has been introduced with this release most of the examples concerns Oracle, but changing the connection string is usually all you need to use other db.

The 0.9 version introduces the support for other languages, adding a java class and some configuation files it is possible to create source code for virtually any language. Currently I am developing the Python support.

Table of Contents

1. How to use SqlJaTor
2. Closing Statement, ResultSet and Connections
3. The override nested element
4. Input Parameters
5. The param nested element
6. Working with SqlData objects
7. Working with LOBs
8. Connection pooling
9. Jsp example

How to use SqlJaTor

SqlJaTor can be run from Netbeans, or as Ant task. This code snippet, taken from test/build.xml explains how to embed the task into your ant files:

<taskdef name="sqljator" classname="net.sourceforge.spwrapper.ant.SqlJaTorTask"/>
<sqljator connectionstring="${oracle-conn-string}" 
	password="${oracle-password}" 
	username="${oracle-user}" 
	javapackage="net.sourceforge.spwrapper.test.ant"  
	targetdirectory="${build-test}"
	javaclassname="antsjtquerywrapper">
	<![CDATA[select * from sjt_ant_test]]>
</sqljator>
				

The code found into build.xml is more complex, because it had to create the table to work with and must deal with complex test classpaths. The first line assigns a task name to the class net.sourceforge.spwrapper.ant.SqlJaTorTask; the other lines invoke the task to create a Java wrapper to execute a select.

Here is the attribute list you have to provide to build a wrapper:

The body text of sqljator must contain the sql code you want to wrap. It can contain ${ant-variables} to substitute code sections such as table names. It is a good idea to surround this code into a CDATA block.

Before running the ant build file make sure spwrapper.jar is in you CLASSPATH!. If you are using Netbeans 6 check it opening Tools->Options menu and clickin the Miscellaneous icon. Add to the ant path the jdbc driver and spwrapper.jar.

Running the above snippet into a build file creates the java class net.sourceforge.spwrapper.test.ant.antsjtquerywrapper. To do this SqlJaTor opens a connection to the database and tries to execute the sql statement, looks for the datatypes it returns and then rolls back the statement so no change is done when creating the wrappers. The resulting class provides an iterator that allows you to loop on the fetched rows and retrieve each colum in each record.

Supposing that sjt_ant_test is the following table:

create table sjt_ant_test (
		name varchar2(80) not null,
		id integer not null,
		hire_date date not null
	)
				

The resulting class will have 3 getters, get_NAME, get_ID and get_HIRE_DATE to acces the retrieved values and three functions was_NAME_null, was_ID_null and was_HIRE_DATE_null to check if the values are null. This is the same pattern used in SPWrapper.

You can find an example of select iterating into net.sourceforge.spwrapper.test.oracle.SqlJaTorDetectQueryType.java in the test folder. Here is an excerpt:

SJTSelect_WRAPPER select = new SJTSelect_WRAPPER(connection);

select.run();
Iterator<SJTSelect_WRAPPER> iterator = select.getIterator();

while (iterator.hasNext()) {
	iterator.next();
		select.get_NAME();
		select.get_SURNAME();
		select.get_ID();
}
		

With the first line a wrapper instance is initialized with an open connection, the second line executes the query and the third gets an interaton on the results. You can loop on the result following the usual java iterator pattern. The jdbc connection, statement and the result set will be automatically closed when hasNext returns false. If you stop fetching the rows before the result set is at its end you have to manually close the connection.

Closing Statement, ResultSet and Connections

SqlJaTor is able to wrap selects, insert, updates and deletes. Because of the differences in these operations different close behaviour have been chosen. The wrapper will automatically close the connection for those statements that do not returns result sets, such as insert and deletes. If you whant to change this behaviour you can use the methods setAutoclose and setStatementAutoclose.

The default behaviour is different for selects, in this case it is not possible to close the connection and the statement at the end of the run method, because the data has to be retrieved later with the iterator. When the iterator reaches the end it automatically closes the connection, the statement and the result set. If you decide to end the fetching before the end you should close the statement and the result set explicitly.

If the execution raises a SQLException this exception is trapped and handled accroding to setAutoclose and setStatementAutoclose

The override nested element

As with SPWrapper also SqlJaTor allows you to use the nested override element in the task definition. The ant task does its best to map the returning resultset fields to the correct datatype, but sometimes you want to specify the correct mapping. This example maps the ID resultset column to a BigDecimal instead of an int.

<sqljator connectionstring="${oracle-conn-string}" 
	password="${oracle-password}" 
	username="${oracle-user}" 
	javapackage="net.sourceforge.spwrapper.test.ant"  
	targetdirectory="${build-test}"
	javaclassname="antsjtquerywrapper">
	<![CDATA[select * from sjt_ant_test]]>
	<override paramname='ID' sqlType='NUMERIC' />
</sqljator>		
		

For reference look at Task Parameters , Datatypes and Support New Databases.

Input Parameters

The previous example does not use input parameter, but these are needed to specify where conditions and for inserts. SqlJaTor uses your sql code as example and extract all the constants you specify as input parameter placeholders. It analyzes the constant to guess what is the datatype you are going to use. This is an insert example taken from the build.xml file into the test directory:

<sqljator connectionstring="${oracle-conn-string}" 
		password="${oracle-password}" 
		username="${oracle-user}" 
		javapackage="net.sourceforge.spwrapper.test.ant"  
		targetdirectory="${build-test}"
		javaclassname="antsjtwrapper">
	<![CDATA[insert into mytable (name,id,hire_date) 
	      values ('mouse boy',77, DATE '2007-03-24') ]]>
	<param name='name' position='0' />
	<param name='id' constant='77' />
	<param name='hd' constant="2007-03-24" sqlType='TIMESTAMP'/>
</sqljator>
		

Three parameters are guessed from the statement: 'mouse boy', 77 and '2007-03-24'. The constants are analyzed and mapped to a String, int and a Date. The types SqlJaTor is able to detect are:

To change these default, and to give the parameter a useful name instead of in0, in1 etc, you can use the param element:

The param nested element

This element allows to specify the parameter name an optionally its type. The parameter is identified by its position in the sql or its value.

The rollback nested element

Not all databases supports rollback, therefore if you wrap an insert on these systems the rollback command issued by SqlJaTor is unable to remove the new record. This can happen if you use MySql version 4.1 with tables created by the MyISAM engine.

In this case you can add a new nested element called rollback: this element has no attributes and its content is the SQL statement to be issued to undo the changes. Example:

<target name="insert-boosts">
  <sqljator connectionstring="${connection}" username="${user}" 
  	password="${password}" targetdirectory="${target-dir}" 
  	javaclassname="BoostInsert" javapackage="wrappers">
    <param name="url" position="0" /> 
  	<param name="keyword" position="1" /> 
  	<param name="position" position="2" /> 
  	<param name="boost" position="3" /> 
  <![CDATA[insert into boosts(url,keyword,position,boost) 
            values('http://gino','aprikot',1,1)]]> 
  	<rollback>
  		<![CDATA[ delete from boosts where 
  		   url='http://gino' and keyword='aprikot']]> 
  	</rollback>
  </sqljator>
</target>
		

Working with SqlData objects

This feature has been tested only with Oracle

It is possible to use SqlData objects with SqlJaTor. You can find an example in the StrutTest.xml, StructTest.java and StructTestObject.java. Suppose you have this object in the database:

create or replace type struct_test as object (field1 int, field2 varchar(80))
		

An suppose you have defined this java object that must be mapped to it:

package net.sourceforge.spwrapper.test.oracle;

import java.sql.*;

public class StructTestObject implements SQLData {

    public String getSQLTypeName() throws SQLException {        
        return "STRUCT_TEST";
    }

    public void readSQL(SQLInput arg0, String arg1) throws SQLException {
        this.arg1=arg0.readInt();
        this.arg2=arg0.readString();
    }

    public void writeSQL(SQLOutput arg0) throws SQLException {
        arg0.writeInt(this.arg1);
        arg0.writeString(this.arg2);
    }

    public StructTestObject() {
        super();
    }

    public int arg1=0;
    public String arg2="";
}

You could use this sql statement to let SqlJaTor generate an insert

insert into sjt_object(id,s) values (5,<sjt%struct_test(7,'mamma')%sjt>)

the <sjt% and %sjt> markers are used to mark the statement piece that should be considered a single parameter. Do not forget to map this parameter to the OBJECT sql type.

This can be an example of select, the return types are automatically detected by SqlJaTor

select s from sjt_object where id=1

But one last thing is missing, you have to tell the database connection that STRUCT_TEST must be mapped to StructTestObject. To do this you have to use in the ant task a new element, the typeMapping element. This element is described also into SPWrapper documentation.

Working with LOBs

This feature has been tested only with Oracle

It is possible to work with lob files. An example is provided by the LobTest.java test case.

Suppose you need to execute this statement:

insert into SPW_LOB_TEST(id,my_clob) values (99,'my pretty clob')
	

Replaceing the parameter id to 99 and clob to 'my pretty clob'. To do this you must provide two param element: the first one just to give a name to 99; the second one to provide the name but also to specify that its type is CLOB, since 'my pretty clob' is detected as a string.

This example shows you how to use the wrapper generated:

CLOB inclob = CLOB.createTemporary( c, true, 
	CLOB.DURATION_SESSION ); //c is a jdbc connection
inclob.open(CLOB.MODE_READWRITE);
Writer w = inclob.getCharacterOutputStream();
StringBuilder sb = new StringBuilder();
for (int i=0; i<40*1024; i++) /*writes 40kb of text*/
	sb.append('x');
w.write(sb.toString());
w.flush();
w.close();

sjtLobInsert_WRAPPER sli = new sjtLobInsert_WRAPPER(c);
sli.run(999, inclob);   		
	

Once know how to fill the clob it is quite simple to insert it in the table. Getting back the clob is quite different.

You must read the clob, or the blob, while the row is fetched, in the same order as colums appears in the result set. To help you implement this behaviour SPWrapper and SqlJaTor provides callbacks in the wrapper. For each blob/clob you must override this callback and handle the data coming out form the database.

Suppose you have to execute this statement:

select my_clob from SPW_LOB_TEST where id=999	
	

You should provide a param element for the 999 constant. After generating the wrapper (sjtLobSelect_WRAPPER) you have to extend it and implement the method callback_MY_CLOB. Look at the sjtLobSelectWithCallbacks.java file:

...
protected void callback_MY_CLOB() throws SQLException {
	got_callback=true;
	clob_length = MY_CLOB.length();
	example = MY_CLOB.getSubString(1, 10);
}
...
	

This code is executed each time the iterator fetches one row from the database. Instead of instantiating the wrapper remember to instantiate your overriding class, sjtLobSelectWithCallbacks in my case.

You can also look at callbacks documentation for SPWrapper.

Connection pooling

Like SPWrapper, SqlJaTor creates wrappers with three constructors:

• one with a string and a properties parameter
• one with a connection parameter
• one with only a string as parameter

If you have the connection string and a properties instance with username and password, you can use the first one: this is suited for standalone applications, since it registers the jdbc driver.

If you must control the connection status or must execute many statement and then rollback/commit you should use the second constructor. Be sure that the connection is the database specific connections such as OracleConnection

The third one is meant to be used for JNDI datasources. This one is sometimes problematic: if the application server does not provide a database specific connection but wraps it the class is unable to access the driver specific methods needed to handle special datatypes. For Jboss and Tomcat a special casting code has been added to the wrapper, to acces the underlying connection.

Since adding this narrowing code is easy I suggest you to write me wich application server are using, so that I can add the needed code. Mail to giovanni /dot/ bricconi /at/ matrix /dot/ it.

Look at Resultset Narrowing if get some ClassCastExceptions and some return parameters appears null.

If you are working with Tomcat remember to place one copy of the JDBC driver inside tomcat's $CATALINA_HOME/lib and absolutely avoid placing another copy inside your WEB-INF/lib. If you have two copies of the driver the connection pooling will be started with a different class loader, and you will get some cast exception when the wrapper tries to get the correct class to access the proprietary database functions.

Jsp Example

If you whant to try this example remember to place the database driver and spwrapper.jar into ant classpath.

This example shows you how to select the matching data in a table and displaying it in a web page. Suppose that you have this table, and these rows in your database:

<target name="prepare">
    <sql driver="oracle.jdbc.driver.OracleDriver" 
          url="${jdbconn}" userid="${user}" password="${pass}" delimiter="%" >
              <![CDATA[
              create table authors (
                 name varchar2(50) not null,
                 surname varchar2(50) not null,
                 birth_date date,
                 id integer
              )
              %
              insert into authors(id,name,surname, birth_date)
              values (1,'ichs','ypsilon', DATE '1921-07-03')
              %
              insert into authors(id,name,surname, birth_date)
              values (1,'hau','bau', DATE '1931-04-01')
              %
              insert into authors(id,name,surname, birth_date)
              values (1,'ichs','ypsilon', DATE '1959-01-15')
              %
              insert into authors(id,name,surname, birth_date)
              values (1,'ichs','ypsilon', DATE '1979-12-13')
              ]]>
     </sql>        
 </target>	
	

If you whant show all the authors in a time interval you can use this ant code

<target name="all">
 <taskdef name="sqljator" 
  classname="net.sourceforge.spwrapper.ant.SqlJaTorTask"/>
        
 <sqljator connectionstring="${jdbconn}" 
	password="${pass}" 
	username="${user}" 
	javapackage="testapplication.wrappers"  
	targetdirectory="${targetdir}"
	javaclassname="SelectAuthorsByDateRange">
    <![CDATA[select * from authors where birth_date>(DATE '1900-01-01') 
        and birth_date<(DATE '2007-04-02')]]>
        <param name="startDate" constant="1900-01-01"/>
        <param name="endDate" constant="2007-04-02"/>
 </sqljator>
</target>
	

Once you have the code you can start writing your web application, but before remember to register a data source in your jndi context.

This is a JSP + JSTL example: two parameter are get from a form, the start and the end date. If they are not provided 1900-01-01 and 2007-04-02 will be used. The data will be shown in a table

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page  import="testapplication.wrappers.SelectAuthorsByDateRange" %>
<%@ page import="java.sql.*" %>
<% java.sql.Date start = java.sql.Date.valueOf("1901-01-01");
    java.sql.Date end=java.sql.Date.valueOf("2007-04-02");
    %>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Resultset Example</title>
    </head>
    <body>

    <h1>Form</h1>
    <form method="POST">
        StartDate <input type="text" value="${param.start}" name="start" />
        EndDate <input type="text" value="${param.end}" name="end" />
        <br /><input type="submit" />
    </form>    
    <%
        
        if (request.getParameter("start")!=null)
            start=java.sql.Date.valueOf(request.getParameter("start")); 
            //in real code date should be validated
        if (request.getParameter("end")!=null)
            end=java.sql.Date.valueOf(request.getParameter("end"));    
    SelectAuthorsByDateRange query = 
      new SelectAuthorsByDateRange("java:/comp/env/jdbc/OracleDS"); 
    	//my jndi registerd connection
        request.setAttribute("query",query);
        query.run(start,end);
    %>
    
    <table>
        <c:forEach items="${query.iterator}">
            <tr><td>${query._ID}</td>
                   <td>${query._NAME}</td>
                   <td>${query._SURNAME}</td>
                   <td>${query._BIRTH_DATE}</td></tr>
        </c:forEach>
    </table>
    <%-- query.getConnection().close(); --%>
    </body>
</html>	
	

The core lines are the following:

1. SelectAuthorsByDateRange query = new SelectAuthorsByDateRange("java:/comp/env/jdbc/OracleDS"); instantiates the wrapper, referencing the registered data source.
2. reqest.setAttribute("query",query); makes query accessible to forEach.
3. query.run(start,end); perform the query
4. <c:forEach items="${query.iterator}"> loops on each fetched row, notice that query.iterator is used

The wrappers created by SqlJaTor implements the Iterable interface, unfortunately forEach needs a collection or an iterator to display the table. It is unpractical to implement the Collection interface because complex operations have to be provided and only one row is fetched for each iteration; therefore remember to use ${query.iterator} to avoid error messages.

The resultset fields are accessed using the usual get methods. eg. ${query._NAME}