Featured White Papers

• Java Web Hosting | DailyRazor.com - Over 10yrs Of Java Web Hosting Exp!

Sponsored Links

Sponsored Links

Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs

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

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.