JaQu — Another Lightweight SQL Interface for Java

July 31, 2008

Following up on my earlier post, I would like to mention a new tool by the name of JaQu (Java Query) that Thomas Mueller announced in a comment to my repost on Javalobby. Meanwhile, it has been released as part of the H2 Database Engine and I’ve had a chance to try it.

Even though JaQu comes bundled with H2, it is not specific to that database. I have tested the following code example successfully with an Apache Derby database.

Read the rest of this entry »

Advertisements

Lightweight SQL Interfaces for Java

June 11, 2008

I previously blogged about the shortcomings of JDBC and its way of passing SQL statements as strings without any compile-time checking or type safety. The same also applies to other SQL-based database access libraries such as Microsoft’s ODBC, OLE DB and ADO.NET. None of these APIs provide proper integration of SQL with the host language. Of course you could argue that object-relational mapping (ORM) tools like Hibernate have eliminated the need to work directly with SQL, but I found that there are still situations where you want to control database operations more explicitly.

Microsoft has addressed this issue quite elegantly with the introduction of LINQ to SQL in the .NET Framework 3.5. Although there is nothing equivalent in Java, I recently came across some promising efforts to improve language integration by providing fluent interfaces or other lightweight wrappers around JDBC and SQL.

Read the rest of this entry »


Lightweight Database Abstraction

February 10, 2008

Although there are object-relational mapping tools like Hibernate for persisting Java objects, there are still situations where you need or want to access the database directly using JDBC.

One thing that has always bothered me about JDBC is the disconnect between the SQL command strings and the surrounding Java code. Consider the following example (which prints a list of robots that were “born” before 1980):

public void printClassicRobots() {
Calendar dobThreshold =
new GregorianCalendar(1980, 0, 1);
PreparedStatement statement = null;
try {
Connection connection = getConnection();
statement = connection.prepareStatement(
“SELECT ID, Name” +
” FROM Robots” +
” WHERE DateOfBirth < ?"); statement.setDate(1, new Date( dobThreshold.getTimeInMillis())); ResultSet rs = statement.executeQuery(); while (rs.next()) { System.out.format("%08d: %s\n", rs.getInt(1), rs.getString(2)); } } catch (SQLException e) { e.printStackTrace(); } finally { cleanup(statement); } } [/sourcecode] What I don't like about this kind of code:

  1. Since the SQL command is just another string, the compiler does not know anything about its meaning and cannot check if its syntax is correct or if the number and types of columns in its result set are consistent with the subsequent method calls that extract data from the result set.
  2. Different database systems use different variants of SQL, making it quite challenging to write portable SQL statements in all but the simplest cases.
  3. Refactoring the database schema can be very tedious and error-prone if you have to resort to a text search to find all the places where a certain table or column is used in your code.
  4. Read the rest of this entry »