bernt's Blog

Do you need to learn how to use transactions? Again?

Statement: A lot of programmers do not know how to use transactions.
You may now think that I miss the target completely, but think for a while.

Very often they (and myself, I admit) think of transactions as atomic
changes to the database and write code like

... some SQL statements
commit
catch an SQLException and report a bug

This will work for most databases in most cases. BUT:

Seldom you see code that assumes that the transaction actually may
fail, and that it is normal for transactions to fail (i.e. it is not a
bug).

Occasionally, transactions do fail, and the frequency of failure is
dependent on the database and the applications using the
database. Transactions in distributed HA databases fail more
frequently than in simple databases, and I would assume distributed transactions (XA) also fail more often.
The proper way to write code for this is

while(not done) {
   ... some statements
   commit;
   done = true;
}  catch (SQLException e) {
   if (transient error) {
      .. 
      rollback;
  } else {
      rollback;
      done = true;
      e.g. throw exception
  }
}

How often do you write like this?

Apache Derby 10.1.2.1 Released

Apache Derby, the pure Java
SQL database, has released version href="http://db.apache.org/derby/releases/release-10.1.2.1.cgi">10.1.2.1.

The changes since official release 10.1.1.0 include:

• Globalization of the 10.1 error messages.
• Eclipse 3.1 support
• Ability to run on Mac OS X with no special configuration
• Support for direct execution of derbynet.jar using the -jar option of the VM
• Fixes to allow databases to be loaded from jars in the classpath
• Fixes to improve J2ME/CDC/Foundation Profile support
• Fixes to improve security manager support
• Many important fixes to correct issues with data integrity, correct memory leaks and address other product defects

Release notes

A Read-only database in a jar?

In Norway, all wines and liquors and beers stronger than 4.75 volume percent are sold at Vinmonopolet a state monopoly (Literally "The Wine Monopoly").

What has this to do with a read only database? Well, I once had an application on my Palm which contained Vinmonopolet's complete catalogue. Every now and then I updated the catalogue and was the able to search for information about their products (they carry a very impressive selection of good wines). The querying possibilities was somewhat limited. E.g. I could only search for wines within certain fixed price ranges, search for wines from only one country/district/area etc.

What I really wanted was an application with poweful searching possibilities: E.g. Show all Pinotage wines from South Africa and all Shiraz wines from Australia where the price is between NOK 130 and 150. Since I'm a programmer this power could be given me by the means of SQL. Yes, I admit that it would not be very user-friendly for the average wine-consumer, but one could imagine SQL queries as an "advanced option".

Ok. To the point. This application could be implemented as a small Java application on top of a SQL database. And yes, it's easy to make such an application with Apache Derby.

The follwing list shows roughly the steps needed to implement my favourite wine application:

1. Create an application that populates a database for the intended purpose. Remember to do a proper shutdown of this database. This ensures that the database is checkpointed (see the Derby Reference Manual under the shutdown=true url attribute).
2. Create the user application (of course..... ;-)
3. Pack the database files and the user application into one jar file. The Derby property derby.storage.tempDirectory needs to be set, and the application need to do the above described shutdown when it is exited (Since a read-only database can be used from several Derby instances simultaneously, eavch instance will create a unique directory under the tempDirectory which will be deleted during shutdown). The database is accessed with the url jdbc:derby:classpath:<path to database>
4. Ship your combined application/database jar file together with derby.jar and there you go.

Easy, isn't it? And I can envision a lot of other applications for a read only database. Can you?

2005-10-04 11:20:00 CEST: jwenting's comment is kind of right, so I changed from "read only database" to "read only database in a jar" in title and blurb to make the blog entry more precise.

Easy String Concatenation Considered Harmful?

This is not about the amount of String objects generated when you do
string concatenation in Java. This is about databases and JDBC.

Performance problem?

When encountering performance problems programmers have with Derby and other databases I very often find application code like

Statement s = c.createStatement();
while (<some loop on j>) {
   ....
   ResultSet rs = s.executeQuery("select * from t where i > " + j);
   ....
}

This will result in a prepare (or compilation as some will call it) of the query each time executeQuery is called, and since the query will be different each time, no statement cache mechanism can help you avoid the compilation, either. Since prepare often is much heavier than execution, this will execute pretty slow.

Preformance problem solved!

The problem is easily solved with the following pattern (which most programmers should know):

PreparedStatement ps = c.prepareStatement("select * from t where i > ?");
while (<some loop on j>) {
   ps.setInt(1,j);
   ResultSet rs = ps.executeQuery();
   ...
}

Prepare is done only once, and if the loop iterates one zillion times, this really amounts to something!

Why?

There may be several possible explanations to this and one of them could be:

It's far too easy to concatenate strings in Java!

Programmers are obviously lazy (and need to meet deadlines), and the
last (and most efficient) alternative takes somewhat more energy to
write. I often find myself in situations where I use the first variant
because I'm in a hurry. I'm just writing a code piece for bug-finding,
some sample code or I thinking might optimize it later. I'm beginning
to wonder if it's a bad habit.

If you look at C code and ODBC, it's the other way
round. Programmers use the prepared statements and string
concatenation is a nuisance with strncat, strdup and strncpy, so in
ODBC, the last variant would be the easiest one to write.

Also done by tools

I have also found the same pattern in application code generated by some tools. I have even seen this:

while (<some loop on j>) {
   PreparedStatement ps = c.prepareStatement("select * from t where i > " + j);
   ResultSet rs = ps.executeQuery();
   ...
}

which for some databases will give you an extra round-trip to the database, too! (Derby may run in embedded mode where the extra procedure call does not matter much).

Be prepared!

The lesson from this is pretty simple:

Use prepared statements! (and don't forget to reuse them.)