Newsletter sign-up

View all newsletters

Sign up for our Enterprise Java Newsletter

Enterprise Java

Java Tip 126: Prepare cross-server database access methods with JDBC

Write portable database access methods for generic database tables

By Michael Juntao Yuan, JavaWorld.com, 04/05/02

Java has become increasingly popular as a server-side programming language for database-powered Web applications. Those applications' business logic-handling methods must work with data from backend databases. Therefore, you need a set of well-designed database access methods. Those access methods should meet the following requirements:

1. They should be generic for any table schema. Users can supply the actual table schema information at runtime. Genericness lets you reuse the methods throughout the application to reduce code maintenance overhead and minimize chances for human errors. Genericness also lets you easily add new tables or change existing schema, therefore, making the application more expandable and flexible.
   
   
2. They should be portable across different database servers so the Web application can deploy independent of the backend. Java's built-in, cross-operating system portability no longer suffices for Web applications.
   
   
3. They should be optimized for speed.

In this tip, I discuss how to write generic, portable, and fast database access methods. To illustrate, I provide example code to access an imaginary database table called Article (see Article.sql), which has several fields with different types.

What's wrong with raw SQL statements?

Access methods can generate raw SQL statements at runtime using database table information provided by the access methods' caller method. The caller method knows the table schema and could generate the correct SQL text strings for each Java object it wants to store in the database. The access methods then assemble those strings into raw SQL statements and escape any illegal SQL characters.

This approach is simple but not intelligent. A lot of coding and runtime overhead results from the caller producing the correct SQL text string for each Java object. The solution is also slow. Every time the access method sends a raw SQL statement, it runs the overhead of parsing, compiling, and optimizing the statement. An even bigger problem: the raw SQL-based method cannot be cross-server portable. Different database servers have slightly different SQL syntax. For example, some databases expect YYYY-MM-DD type syntax for the SQL Date field while others might expect DD,MM,YYYY syntax. Databases can also have different SQL text escape requirements. For example, MySQL server uses backslash (\) to escape illegal characters while Microsoft SQL server uses single quote ('). That means any raw SQL-based implementation must target a specific database server.

To overcome the problems of the above raw SQL approach, you can use JDBC's (Java Database Connectivity) PreparedStatement interface.

PreparedStatement for cross-server portability

A PreparedStatement can take a parameterized SQL template and compile it for maximum efficiency. When you need to run a query/update using this template, you only need to reset the parameter values using PreparedStatement.setXXXX() method, where XXXX is the parameter's Java type. For example, setLong(1, articleID) resets the first parameter in the template to long type value articleID. JDBC's PreparedStatement automatically generates the complete SQL statement for execution.

 

XpoLog Center Correlate application logs with server metrics - XpoLog

About Us | Advertise | Contact Us | Terms of Service/Privacy

Copyright, 2006-2008 Network World, Inc. All rights reserved.