by Sebastian Kübeck
Last updated Jan 02, 2007

What is Queryfish?

Queryfish is a thin layer on top of JDBC. It aims to simplify database development in Java, especially creating prepared and callable statements and thus, assist developers to build secure applications without SQL injection vulnerabilities.

To prevent problems with using it in commercial software, I chose a liberal license (Apache 2.0).

Where do I get it?

It's hostet at sourceforge where it can be downloaded.

How do install it?

Just add the jar file queryfish-1.0.jar to your classpath. There are no further dependencies apart from the JRE istelf.


Where do I get help?

There are public forums at sourceforge. If you have problems, please leave a note.

How do I use it?

The main advantage over using pure JDBC is that it uses a PL/SQL style parameter syntax instead of the question marks.

Example 1:

Row row = new QueryStatement("select * from Suppliers where SUP_ID=:sup_id")
    .set("sup_id", "101")

The example above shows how to issue a database query using queryfish. The statement takes one parameter (sup_id). The parameters are identified by a leading colon in the query. The order in which you place the parameters (using the set() method) is irrelevant. The fetch method called at the end of the statement throws a RuntimeException if there's a parameter not yet bound. It's a convenience method for situations where only one row is expected in the result set. This will be discussed later.

Agreed, the above example does not show much benefit compared to using pure JDBC, hovever, the persented approach pays once you have at least six or so parameters. If you've ever struggled adding a parameter in between several dozen others and spent hours getting the indexes right again, you know what I'm talking about.

Parameter Identity

Another adavantage compared to the plain JDBC approach is illustrated in the example below. Although the parameter appears twice in the query, it's set only once for both occurences. This dramatically simplifies programming queries which are filled from search forms and where empty fields indicate that the query should not be restricted for an empty field.

Example 2:

Row row = new QueryStatement("select * from Suppliers where (:name is null name=:name)")
    .set("name", "Acme, Inc.")

OUT and INOUT Parameters

Although stored procedures have the charme of FORTRAN and COBOL, which is quite a contrast to the Java syntax, they're still quite popular. However, the CallableStatamenet is may be the worst part of the whole JDBC library. To make things a bit easier, I introduced „Variables“, that are containers for parameters that are returned from the database.

Example 3:

Variable i = new Variable(Types.INTEGER, new Integer(1));
    new CallStatement("{call testProcInOut(:i)}")
    .set("i", i)
int result = (Integer)i.get();

The above example illustrates how it works. A Variable is defined (in practice, you'll derive something like IntegerVar and add a method such as intValue() to it to avoid the ugly cast) and passed over to the CallStatement using it's set() method. Since a value is passed in the constructor, CallStatement assumes that it's an INOUT parameter and will register and set it. After the execution is done, the value retrieved from the database will be duly transfered to the Variable (in this case i) where it can be obtained afterwards.

That's pretty much all the magic around queryfish. In the next chapters, I'll describe UpdateStatement, QueryStatement, CallStatement more detailed.

The UpdateStatemenet

The usage of UpdateStatement is quite unspectacular. Apart from the set() method mentioned above, there are three kinds of execute methods:

They all execute the update and return the number of affected rows. The ones with the int parameters set constraints to the number of rows to be affected. The motivation for this is that it helps to check the affected row count when you want to delete only one row but someone of your colleges messed up a query and the deleteion affects the whole table. Note that it doesn not issue a rollback! It only throws a SQLException!

Example 4:

new UpdateStatement("update Suppliers set name = “Acme New, Inc.“ where SUP_ID=:sup_id")
    .set("sup_id", "101")
    .execute(db.getConnection(), 1);

For cases wher due to isolation issues, a row has to be deleted when it exist, but it doesn't hurt if it's already been deleted by a transaction running in parallel, there's the third variant of execute in the UpdateStatement:

Example 5:

new UpdateStatement("delete from Suppliers where SUP_ID=:sup_id")
    .set("sup_id", "101")
    .execute(db.getConnection(), 0, 1);

The above example would tolerate non or one deletion, but not mor than one.

The QueryStatement

Apart from the set() method mentioned above, there are two ways to access the result set from the database. The first is accessing the JDBC result set object via a visitor (called RowVisitor).

Example 6:

new QueryStatement("select * from Suppliers")
        new ResultSetVisitor(){
           public boolean next(ResultSet rs)
                   throws SQLException {
               int supID = rs.getInt("sup_id");
               return true;

That's the preferred way of dealing with large result sets. The JDBC ResultSet will be reliably closed within the execute() method. That counts for all methots creating any closable object within the library.

As the above method is somewhat awkward at times, there are three execute variants left that simplify data retreival. All of them copy the content of the result set into an object structure of Rows containing Cells which refer to Columns.

 ______          ______         ________
| Row  |        | Cell |       | Column |
 ------ 1      * ------ 1     1 --------

The three variants to retreive them in short:

Example 7:

Int count = new QueryStatement("select count(*) from Suppliers").compute(db.getConnection()).intValue();

The CallStatement

The CallStatement is somewhat a merger between the UpdateStatement and the QueryStatement. The „Variable“ container has been explained above. The only thing to note is that the Row[] execute(Connection) method from QueryStatement is called Row executeQuery(Connection) in the CallStatement.