Chapter 13. JDBC | SQL Performance Tuning

Sun says that JDBC is not an acronym for anything, and www.sun.com says it stands for Java Database Connectivity. Go figure. Anyway, JDBC is the de facto standard API for Java programs and applets. In this chapter, we won't try to compete with the giant reference books on the subject ^[1] remember, we said in Chapter 1, "Facilis Descensus Averni," that we assume you're already familiar with (among other things) programming with an SQL API such as JDBC. Our goal is more specific: to suggest improvements in Java applications written for SQL DBMSs.

^[1] Such as JDBC API Tutorial and Reference, Second Edition: Universal Data Access For the Java 2 Platform , by Seth White, Maydene Fisher, Rick Cattell, and Mark Hapner. Addison-Wesley, 1999.

In simple terms, a JDBC driver makes it possible to do three things:

Establish a connection with a data source
Send queries and data changes to the data source
Process the results

Listing 13-1 shows a short portion of a JDBC program.

Listing 13-1 A short JDBC program example

 Connection con = DriverManager.getConnection(                  "jdbc:odbc:driver",                  "login_name",                  "password"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(                "SELECT column1, column2, column3 FROM Table1");   while (rs.next()) {     String s = rs.getString("column1");     float f = rs.getFloat("column2");     int i = rs.getInt("column3");   } ...

As Listing 13-1 shows, a typical Java program goes like this: You connect, you query, maybe you update or do something else with the result set. As with ODBC, there's some prepping work at the start to discover what the DBMS can do and what the database contains. (This is necessary because a Java program is general and portable, and capable of ad-hoc decisions. If it weren't, we'd be skipping this chapter entirely in favor of Chapter 11, "Stored Procedures.") In our discussion of JDBC, therefore, we'll focus on performance and portability both, under four main headings: "Connections," "Query Prepping," "Result Sets," and "Data Changes." We'll use JDBC 2.0 for all our examples.