Configuration

Jing-SQL uses one XML file to store the SQL in. Currently I've been using the "vsql" extension for "Velocity SQL", but what you call the file is sort of up to you. The important part is what's inside. Let's look at an example:

<?xml version="1.0" encoding="ISO-8859-1"?>

<sql-manager>
    <query id="customer">
        SELECT NAME, ADDRESS, PHONE
        FROM CUSTOMER_TABLE
        WHERE ORDER_NUMBER = ${orderNumber}
    </query>
</sql-manager>

     

In this simple example all we have is one query, "customer." The reference ${orderNumber} represents a variable that will be passed in from the calling code. You can include any valid Velocity templating in this space, but in the end, it must be a valid SQL statement. Perhaps we'll add some processing possiblities, but for now, we'll keep it simple.

The Code

Let's assume that we call the file above "sql-manager.vsql" and we place it in a directory named "WEB-INF." Now let's look at the code that uses this SQL.

There are only two interfaces you need to concern youself with:

• SQLManager
• SQLEvaluator

The SQLManager is fairly simple:

     public interface SQLManager {

        public SQLEvaluator getSQL(String key);

     }
     

If you're using this in a standalone environment, then you'll need to directly access an implementation such as the AvalonSQLManager . You'll need the Avalon Framework 4.1.4 library in your classpath to use this. If you're not using an Avalon container, you would initialize it by doing something like this:

     import org.jadetower.sql.impl.AvalonSQLManager;
     ...

     AvalonSQLManager sqlManager = new AvalonSQLManager();

     // make sure we configure it! 
     sqlManager.configure("WEB-INF/sql-manager.vsql");

     

Now you'll want to use an SQLEvaluator to run the query we looked at before. The SQLEvaluator does not handle your database connections for you. For that you'll need to look into a connection pooling library. But assuming you can get your hands on a database connection, getting the JDBC result set of the query above is this simple:

      
       SQLEvaluator sql = sqlManager.getSQL("customer");
       sql.set("customerNumber",new Integer(100001));
       ResultSet rs = sql.executeQuery(databaseConnection);

     

But wait! It gets better!

Suppose we create a java bean that represents our query results. Wouldn't it be nice if it were automatically populated? Say no more...

       SQLEvaluator sql = sqlManager.getSQL("customer");
       sql.set("customerNumber",new Integer(100001));
       List customers = (List) sql.executeQuery(
                                                databaseConnection,
                                                org.proj.CustomerBean.class,
                                                null);
       for(int i=0; i < customers.size(); i++){
         CustomerBean customer = (CustomerBean) customers.get(i);
         ...
       }

     

This example assumes that org.proj.CustomerBean is a java bean which minimally has the getters and setters for the column names of our returned query. You can use the Map parameter of executeQuery to map columns to properties which don't quite match. Important: Column names are ALWAYS converted to lower case and the mapping is case sensitive. Keep this in mind when creating your beans and maps.

But what about bind variables?

For those that prefer the bind variable approach, you can write your SQL as follows:

<?xml version="1.0" encoding="ISO-8859-1"?>

<sql-manager>
    <query id="customer">
        SELECT NAME, ADDRESS, PHONE
        FROM CUSTOMER_TABLE
        WHERE ORDER_NUMBER = ?
    </query>
</sql-manager>
     

You must then set an attribute named bindParameters in the context. bindParameters should be a List of the bind parameters you want to use in the query. This approach allows you to use both Velocity and bind variables. In fact...

You can use SQLEvaluator.setSQLMutable(false); to stop parsing by Velocity. The SQL template will be parsed via Velocity only once and then cached (until you set the parameter back to the default of 'true'). In this case, you can use Velocity to build the SQL, then use bind parameters is fill in the details, saving you the constant parsing time.

One final note, when you get a ResultSet you need to make sure you close it. The best way to do that is to call SQLEvaluator.close(); which will close all open Result and PreparedStatements. JDBC Connections passed to the evaluator must also be close by the client code.

For more info, see the JavaDocs.