Tag Archives: JDBC

Database Key Generation in Java Applications

Posted on by
3

All JDBC programmers at one time or another have had to deal with key generation for new records, or more precisely, how to retrieve keys for newly created records. This article discusses some of the techniques and limitations offered to Java JDBC programmers.

Key

  • Unique Keys

Most (good) database systems assign a unique key, usually a large number, to every record in the database. For example, a user “Bob Jones” might have a User Id associated with the record of 808182313713. This key is unique for Bob and is a lot easier to pass around a system then Bob’s full name, since there may be other Bob Jones’s in the system. We could spend time discussing Natural Keys, although I would argue there is no such thing as natural keys. As an example, Bob’s social security number might seem like a natural key since it is unique to Bob but it may be the case Bob is a visiting foreigner (or the application is deployed outside the country) in which a US social security number is not applicable. Also, it’s been reported the government has been known to recycle social security numbers, as well as the fact that people can be issued new social security numbers during their lifetime. Even if unique natural keys did exist – e-mail for example – what if a user wants to change their address down the road?

This leads us to our second requirement of unique keys: Unique keys should not change throughout the life of the system. Changing unique keys is often an extremely difficult, highly risk process. A third, less followed, requirement is that unique keys should be private and only used internally by the application server. This is a huge boon to security and prevents users from attacking the system. In the case the system does need to expose a key, such as an eBay item number found in the URL of the auction, the system may create two keys: an Internal Id that is used for table relations within a database, and an External Id used to perform lookups on behalf of the user. The key here is that the External Id the user sees only exists in one record, whereas the Internal Id is used throughout the system to connect different records.

For the purpose of this article, we’ll assume key generation is the common approach of assigning long integers to each record in a table. In most database systems, this corresponds to the BIGINT type.

Helpful Advice: Always use the largest integer type the database has to offer for unique keys – numeric wrap-around is something to be concerned about, especially on smaller data types.

  • Defining the Problem

Most systems generate keys sequentially starting with a low number such as 1000. Why? Well, for starters it makes it easy to find new unused key value. Just find the max value of the table, and any value above this will be an unused available key. You could randomly generate a key, but then creating a new key would be a time consuming processing of generating a random number that has not been used.

Now, let’s say a user is in the process of creating a new record in your system. For each user record, you also have a set of postal addresses. For example, Bob may be purchasing items on NewEgg and have a home address and a work address. Furthermore, Bob enters his two addresses at the time he creates his account, so the application server receives the information to create all 3 records at once. In such a situation, you would normally have 3 records: 1 user record for Bob and 2 address records. You could add the address info in the user table, although then you have to restrict the number of addresses Bob can have and/or have a user table with a lot of extra columns.

Inserting Bob into the user table is straight forward enough, but there is a problem when you go to insert users into the address table, namely that you need Bob’s newly generated User Id in order to insert any records into the address table. After all, you can’t insert addresses without being connected to a specific user, lest chaos ensue in your data management system.

Most database systems provide a technique for the database to create keys for you in the event you do not specify them. In this situation, the first insert would leave the User Id for Bob as blank, or more precisely null, and the database would fill in a new value on insertion. The problem is the database isn’t really obligated to tell you what this newly created value is. The rest of this article will be devoted to discussing how you can get this generated key in your application.

  • Technique 1: Get the max id

Probably the most common approach is the quick and dirty one; perform a select query immediately after your insert and retrieve the max value:

SELECT max(id) FROM users

The good: In a lot of systems this actually works pretty well. It’s easy to implement and is extremely database independent.
The bad: It’s extremely risky in high concurrency environments. For example, if two users perform inserts at nearly the same time, they will both get the same max value from this query, leading to a huge breach in security (both users may have access to one of the users records if the application server returns the same id). There are ways to set your transaction isolation level to avoid problems like this, but that can be tricky and often requires more skill and experience than someone implementing this solution regularly is likely to have.

Also, you have to be *very* sure the system always creates keys strictly in ascending order. A database system that reused old or skipped key values would be incompatible with this approach.

  • Technique 2: JDBC to the Rescue!

Many JDBC programmers are unaware that there is built-in JDBC method for retrieving the newly generated keys immediately after an insert. It is appropriately named getGeneratedKeys() and can be used as follows:

Connection con = // connect to database
final PreparedStatement ps = con.prepareStatement("INSERT INTO users (...) VALUES (...)");
ps.setInt(1,...)
...
final int insertStatus = ps.executeUpdate();
int newKey = -1;
if(insertStatus == 1) {
     final ResultSet rs = ps.getGeneratedKeys();
     if(rs.next()) {
          newKey = rs.getInt(1);
          if(rs.next()) {
               // Probably should throw some exception if rs returns a second, unexpected record
          }
     } else {
          // Probably should throw some exception if no generated keys created
     }
} else {
     // Definitely should throw an error if insert failed
}
System.out.println("The key value for the newly created record is: "+newKey);

A reminder: JDBC starts with 1 (instead of 0) for accessing columns based on an index number.

The good: Don’t need to write any special or tricky logic to get the key. Works in systems that use ascending and non-ascending key value generation. No risk of getting the wrong key value even if you fail to set transaction or isolation levels properly.
The bad: Not supported by all JDBC drivers. Need to test it out in your real system to verify it works before relying on it.

  • Technique 3: Application Server Controlled Keys

One approach used in most large J2EE systems is to build a key generation service within the application tier. In this manner, all generated keys are created ahead of time by the application server without the use of any database-specific features. In the example above, the system would generate all the keys for the user and addresses before the first insert even occurs. Then it would insert the 3 records, one after another, using the keys it had at the start of the service. Keep in mind, the order of insert still matters, i.e. you can’t insert an address for a user that does not yet exist; but in this case, you don’t need to stop after the first insert to figure out information for the next insert.

Such a key generation module would have to be application-wide (static) and thread safe to prevent two requests from getting the same key at the same time. Also, such a system would have to be fault tolerant; if the system crashes it should not reassign any of the same keys after the next restart.

Often this is implemented with a database table that keeps track of the most recently assigned key for each record. To make the service perform well, keys are often released in blocks. For example, the system might say “The last key used was 4100, and I’m going to request 4101-4200”. In this manner the system would update the database record for that table to 4200 and only change this value when it used up all 100 keys in memory. It’s fault tolerant because even if not all keys from 4101-4200 are used, if the system crashes, it knows it can just request 4201-4300 next time.

The good: Performs better than any of the other solutions since it takes the load off of the database entirely. All inserts can be performed immediately without requesting information from the database in between inserts. Safer than any of the solutions because its is 100% database independent. Supports composite keys and distributed database systems.
The bad: Requires the most overhead of any of the solutions since you need to implement a server wide key generation tool. Requires the application server to know the name of each table (or some reference to each table) to track the key generation values for each table. Also, you could skip a lot of keys (sparse key range) if your system restarts frequently, although servers shouldn’t be restarting frequently! Lastly, it tends to require an experienced developer to implement properly (prevent errors) and perform well (using blocking properly).

  • Technique 4: Database-specific techniques

Lastly, you may want to rely on database-specific techniques. For example, Oracle allows you to write queries such as:

INSERT INTO Users (...) VALUES (...) RETURNING (id) INTO newUserId

In this case you would perform an executeQuery() instead of an executeUpdate(), so that the results of the insert could be read in a Result Set.

There are numerous other database-specific techniques for retrieving newly generated keys, but since I advocate against database-specific code in Java applications, I’ll leave a more detailed discussion for someone else.

The good: It’s safer than technique 1 since it works in systems with non-sequential keys.
The bad: It’s database-specific. Not only that but as shown above, all of your insert statements would be database specific. Would make moving the code to another database system very difficult (often impossible).

  • Final Thoughts

Generally, for smaller systems Technique #2 using getGeneratedKeys() is the best solution. Larger systems with composite key generation or distributed databases often require Technique #3 for an application controlled key generator.

As for the other two approaches, I would strongly advise against ever using Technique #4, lest you want to give future developers of your source code a huge headache in management and portability. Lastly, Technique #1 is sometimes safe to use, especially in single threaded systems, although is often evidence of a beginner JDBC developer.

Question mark ‘?’ characters as text in JDBC

Posted on by
1

Many people wonder how insert strings containing question mark characters into their database queries via JDBC. For those unfamiliar with the problem, ? is a reserved character in JDBC queries for parameterizing inputs. For example, if you have run the same query searching for a user but each time with a different name, JDBC offers you the ability to precompile and save the parameterized form of the query with ?’s, thereby saving the overhead of creating lots of new database statements. First, let’s frame the problem. Consider the following code:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE note LIKE '%long?'");
ResultSet rs = ps.executeQuery();

Description: This code searches for all widgets with note field that ends in the phrase “long?”, such as “This is how long?”.

Your first thought might be why make this a PreparedStatement at all (which supports parameters), you could just as easily do it with a Statement (which does not support parameters). Under most circumstances, it is a good coding practice to use PreparedStatement over Statements, even if you don’t have any input parameters. It allows you add parameters easily such as:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE size > ? AND description LIKE '%long?'");
ps.setInt(1,100);
ResultSet rs = ps.executeQuery();

Question: Will this code compile and run?

The answer is that it will compile, but under most circumstances (depending on the JDBC driver) it will not run. Why? The answer highlights just how dumb JDBC drivers really are. In short, they don’t really understand anything about the data they are parsing other than “I see a question mark, let me replace it with something!”. In this example, the user replaced the first ? with an integer, but did not replace the second question mark. In this regard, the JDBC driver will throw a runtime exception prior to sending the code to the database. Also note this code will have the same problem whether you are inserting the value ‘%long?’ into the database or reading it; as I said the JDBC driver knows very little about the query you’re constructing other than its find and replace mentality.

There’s a number of solutions available although my favorite is the following:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE size > ? AND description LIKE ?");
ps.setInt(1,100);
ps.setString(2,"%long?");
ResultSet rs = ps.executeQuery();

Notice I don’t need the single quotes around the parameter, JDBC will do this for me. This is better not just because it solves our original problem, the code will now run, but we’ve parameterized a messy string query! Solving the problem and enforcing good code practices is a win-win. What might throw you for a loop is you’ve increased the number of question mark ?s in the code by one. Whereas before the second ? in the query was a character representing text to be searched on, the second ? now represents a parameter JDBC should replace. It could be replaced with our target string ‘%long?’ or something without a question mark at all such as ‘horse’. Part of the advantage of parameterizing your inputs in the first place is you don’t have to worry about such situations if a user enters a question mark as a value.

Finally Closing of JDBC Resources

Posted on by
8

I love reading Alex’s The Daily WTF and I noticed the recent Finally WTF is relevant to JDBC in an important way. All *good* JDBC developers already know you should close your result sets, statements, and connections (in that order) in a finally block when you are done with them, but do you all know how they should be closed? In particular, while a finally block will be entered under most circumstances, there’s no guarantee every line of code will be executed. Consider the following code I often come across:

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
     // Do stuff
     ...
} catch (Exception e) {
     // Do exception recovery stuff
     ...
} finally {
     try {
          rs.close();
          stmt.close();
          con.close();
     } catch (Exception e) {  }
}

Now, can you figure out what’s wrong with this code? Imagine if the result set rs is never populated and stays null. The first line of the finally block will throw a NullPointerException, and the stmt.close() and con.close() will never be executed. In other words, your failure to close a result set would lead to a connection never be closed even though it was in a finally block! Sure the code is guaranteed to enter the finally block, but if it fails on the first line, the rest of the code will be skipped. Next, compare this other common but still incorrect solution:

...
} finally {
     try {
          if(rs!=null) {rs.close();}
          if(stmt!=null) {stmt.close();}
          if(con!=null) {con.close();}
     } catch (Exception e) {  }
}

This solution is a little safer in that it avoids NullPointerExceptions, but it’s equally as useless as the first solution in that there are a number of reasons why the first line of code could still fail, for example if the result set is already closed. This solution actually worries me the most because clearly the developer went through the trouble of setting up the finally block and null pointer catches, but failed to fully understand how a finally block works. Now, I present a superior solution:

...
} finally {
     try {rs.close();} catch (Exception e) {}
     try {stmt.close();} catch (Exception e) {}
     try {con.close();} catch (Exception e) {}
}

Now, is this solution safe? See that if rs or stmt fail to close, the call to con.close() will still be executed. Granted you could get fancy by adding logic to handle/log the exceptions or even catch Throwable (although catching Throwable’s never a good practice), but that’s a bit overkill. You could also nest your finally block with more finally blocks (just add finally block to the rs’s try/catch and put the rest inside it… and so on) although I tend to prefer this solution since it’s more readable.

Lastly, you could make helper methods for closing the the objects to make the code easier to work with such as:

...
} finally {
     DBUtil.close(rs);
     DBUtil.close(stmt);
     DBUtil.close(con);
}

Where the try/catch is inside the helper methods. Keep in mind this last solution isn’t really different from the previous solution, just a code management improvement.