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).
It's hostet at sourceforge where it can be downloaded.
Just add the jar file queryfish-1.0.jar to your classpath. There are no further dependencies apart from the JRE istelf.
A Java Runtime Environment 1.4.2 or newer
A JDBC database driver
There are public forums at sourceforge. If you have problems, please leave a note.
The main advantage over using pure JDBC is that it uses a PL/SQL style parameter syntax instead of the question marks.
Row row = new
QueryStatement("select * from Suppliers where
SUP_ID=:sup_id")
.set("sup_id",
"101")
.fetch(db.getConnection());
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.
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.
Row row = new
QueryStatement("select * from Suppliers where (:name is
null name=:name)")
.set("name",
"Acme, Inc.")
.fetch(db.getConnection());
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.
Variable i = new
Variable(Types.INTEGER, new Integer(1));
new
CallStatement("{call testProcInOut(:i)}")
.set("i",
i)
.execute(db.getConnection());
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 usage of UpdateStatement is quite unspectacular. Apart from the set() method mentioned above, there are three kinds of execute methods:
int execute(Connection conn)
int execute(Connection conn, int count)
int execute(Connection conn, int min, int max)
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!
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:
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.
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).
new QueryStatement("select
* from Suppliers")
.execute(db.getConnection(),
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:
Row[] execute(Connection con) ...this one returns an array of rows.
Row fetch(Connection conn) ...this one returns exactly one row and throws an execption if more than one row is retuned.
Cell compute(Connection conn) ...this one returns exactly one cell. Example 6 shows the most common way, this is used.
Int count = new QueryStatement("select count(*) from Suppliers").compute(db.getConnection()).intValue();
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.