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 »

    Advertisements

Welcome to the Sustainable Software Workshop!

February 3, 2008

In this blog, I am going to write about my experiences and lessons learned while working as a software engineer.

One of my goals in my daily work is to develop sustainable software: software that can be maintained at a high level of quality in the long term while frequently adapting to changing requirements. All too often, development focuses on getting stuff working for the next deadline, while the long-term vision and consistency of the design is neglected. Over time, the code becomes increasingly complex and hard to maintain, and the quality deteriorates.

Of course these problems are not new, and there are ways to help prevent them, for example using agile methodologies and practices such as unit testing and refactoring. I am planning to blog about my experiences in putting some of these techniques into practice, and there will probably also be insights from everyday problem solving, as well as random geek stuff…

Stay tuned!