Database Programming with JDBC & Java

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Is it certain that to the word communication corresponds a concept that is unique, univocal, rigorously controllable, and transmittable: in a word, communicable? Thus, in accordance with a strange figure of discourse, one must first of all ask oneself whether or not the word or signifier "communication" communicates a determinate content, an identifiable meaning, or a describable value.

—Jacques Derrida, Limited Inc

Two years ago when the first edition of this book was initially published, Java was attracting unprecedented attention from its early success in bringing dynamic content to web pages. The question "Is Java ready for serious development?" was on everyone's mind. Presenting pretty pictures is one thing, but supporting the complex needs of enterprise development is very much another thing. Could Java leverage the infrastructure of existing business environments and take it where existing tools could never imagine going?

Today, Java's power as a server language is taken for granted. Ironically, due to problems with the early versions of the AWT, people tend to question its stability on the client. APIs such as JNDI, the servlet API, the security API, and the suite of APIs collectively known as the Java Enterprise APIs—JDBC, RMI, and Java IDL—together make Java a formidable force on the server. The leap from being a good server development language to being a powerful enterprise development platform, however, is still far.

Unfortunately, the word "enterprise" most certainly does not communicate a determinate content, an identifiable meaning, or a describable value. Just about every technology product aimed at the business customer is sold with the tag—the buzzword—"enterprise." As with any technology industry buzzword, the marketing people have twisted it and made it into a meaningless term. If you pull the marketing fog away, however, you can find an important concept that the word "enterprise" once captured. Within that meaning, the power of Java is fully realized. Before you get into the heart of Java Enterprise's capabilities, you should first understand what the term "enterprise" means in the context of enterprise software.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Buried within the term "enterprise" is the idea of a business taken wholistically. An enterprise solution identifies common problem domains within a business and provides a shared infrastructure for solving those problems. If your business is running a bank, your individual branches may have different business cultures, but those cultures do not alter the fact that they all deal with customers and accounts. Looking at this business from an enterprise perspective means abstracting away from irrelevant differences in the way the individual branches do things, and instead approaching the business from their common ground. It does not mean dismissing meaningful distinctions, such as the need for bilingual support in California branches.

Applying this view to software engineering, an enterprise system provides the proper abstractions for business concepts that are constant across a business so that they may be shared by all the different units within the company. In the Internet age, enterprise systems even go beyond sharing those business concepts within the company to sharing them with vendors, clients, and customers. A detailed look at an example of a manufacturing company can better illustrate how to look at a business from the enterprise perspective.

For this example, the hypothetical manufacturing company, Wombles, Inc., makes all sorts of goods—toasters, blenders, tire irons, light bulbs, etc.—and has three major business units: North America, Europe, and Asia-Pacific. The company started out as an American company. As it grew, it acquired two other companies to gain a worldwide presence. All three business units have their own systems and are mostly ignorant about the issues involved in doing business in the other two regions. Marketing, however, has worked hard and successfully at creating a single, world-recognized brand.

As long as each unit works within its own realm, everything runs smoothly. From the perspective of each unit working within its own realm, however, they might as well be three separate companies. Certainly, moving beyond the distinct realms of each business unit in this environment is a formidable task. What do you do if Asia-Pacific runs out of light bulbs but North America experiences a light-bulb glut? What do you do if your distributors want a single interface into your inventory system? What do you do if your customers, who do not care that you are divided into three separate business units, demand direct and immediate online access via the Web?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Java is really the only language in widespread use that can easily be used to build systems that meet the requirements I just listed for an enterprise system. Java is a standards-based language that is platform-independent. It has support for accessibility and internationalization and localization, including a Unicode basic character type, built into the language. Finally, Java is an object-oriented language with database access and distributed computing at its core.

One important test of whether a component of your enterprise system is proprietary is whether or not another vendor could, in principal, provide a black-box implementation of that component. The Java Virtual Machine (JVM), for example, is an open specification for which others can—and some do—write independent implementations. Java's suitability for this requirement, however, goes beyond the fact that it is a standardized language that is platform-independent. It also provides a host of APIs that you are guaranteed to find on any JVM for accessing hardware and software resources traditionally blocked by expensive, proprietary interfaces. For its original release, the Java specification prescribed what Sun termed the Java Core API—the basic objects required for a minimally viable language. The Java platform specification has since grown to encompass many other APIs. The following is an abridged list of some of the Java APIs:

JavaBeans™: In response to the Microsoft ActiveX threat, JavaSoft developed JavaBeans, a platform-neutral specification for creating software components. Part of the JavaBeans specification actually involves interfacing with ActiveX components.
Java Commerce

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The database is the heart of any enterprise system. The shared business objects that make up an enterprise need some way to make sure they are saved across time. The database provides that storage mechanism. Any language that is going to claim to be an enterprise language therefore needs to have strong, reliable database connectivity.

Several important database concepts form the core of this book's discussion. This book assumes some basic familiarity with Java and databases. You should have a basic understanding of SQL and transaction management. Building on this foundation, we will discuss JDBC and how it can be used to execute SQL against any potential database engine.

Section 1.3.1.1: SQL

The Java database API, JDBC, requires that the database being used support ANSI SQL2 as the query language. The SQL language itself is worthy of a tiny mini-industry within the publishing field, so covering it is well beyond the scope of this book. The SQL in this book, however, stays away from the more complex areas of the language and instead sticks with basic DELETE, INSERT, SELECT, and UPDATE statements. For a short overview of SQL, check out Chapter 2.

The only additional level of complexity I use consists of stored procedures in the later chapters. Stored procedures are precompiled SQL stored on the database server and executed by naming the procedure and passing parameters to it. In other words, a stored procedure is much like a database server function. Stored procedures provide an easy mechanism for separating Java programmers from database issues and improving database performance.

Section 1.3.1.2: JDBC

JDBC is in a SQL-level API that allows you to embed SQL statements as arguments to methods in JDBC interfaces. To enable you to do this in a database-independent fashion, JDBC requires database vendors (such as those mentioned earlier in this chapter) to furnish a runtime implementation of its interfaces. These implementations route your SQL calls to the database in the proprietary fashion it recognizes. As the programmer, though, you do not ever have to worry about how it is routing SQL statements. The façade provided by JDBC gives you complete freedom from any issues related to particular database issues; you can run the same code no matter what database is present.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

While the marriage of Java and database programming is beneficial to Java programmers, Java also helps database programmers. Specifically, Java provides database programmers with the following features they have traditionally lacked:

Easy object to relational mapping
Database independence
Distributed computing

If you are interested in taking a pure object approach to systems development, you may have run into the cold reality that most of the world runs on relational databases into which companies have often placed hefty investments. This leaves you trying to map C++ and Smalltalk objects to relational entities. Java provides an alternative to these two tools that frees you from the proprietary interfaces associated with database programming. With the "write once, compile once, run anywhere" power that JDBC offers you, Java's database connectivity allows you to worry about the translation of relational data into objects instead of worrying about how you are getting that data.

A Java database application does not care what its database engine is. No matter how many times the database engine changes, the application itself need never change. In addition, a company can build a class library that maps its business objects to database entities in such a way that applications do not even know whether or not their objects are being stored in a database. Later in the book I discuss building a class library that allows you to map the data you retrieve through the JDBC API into Java objects.

Java affects the way you distribute and maintain an application. A traditional client/server application requires an administrator responsible for the deployment of the client program on users' desktops. That administrator takes great pains to assure that each desktop provides a similar operating environment so that the application may run as it was intended to run. When a change is made to the application, the administrator makes the rounds and installs the upgrade.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Good sense is the most evenly shared thing in the world, for each of us thinks he is so well endowed with it that even those who are the hardest to please in all other respects are not in the habit of wanting more than they have. It is unlikely that everyone is mistaken in this. It indicates rather that the capacity to judge correctly and to distinguish true from false, which is properly what one calls common sense or reason, is naturally equal in all men, and consequently the diversity in our opinions does not spring from some of us being more able to reason than others, but only from our conducting our thoughts along different lines and not examining the same things.

— René Descartes, Discourse on the Method

Before you dive into the details of database programming in Java, I would like to take a chapter to provide a basic discussion of relational databases for those of you who might have little or no experience in this area. The subject of relational databases, however, is a huge topic that cannot possibly be covered fully in this chapter. It is only designed to provide you with the most basic introduction. Experienced database developers will find nothing new in this chapter; you will probably want to skip ahead to Chapter 3.

Programming is all about data processing; data is central to everything you do with a computer. Databases—like filesystems—are nothing more than specialized tools for data storage. Filesystems are good for storing and retrieving a single volume of information associated with a single virtual location. In other words, when you want to save a WordPerfect document, a filesystem allows you to associate it with a location in a directory tree for easy retrieval later.

Databases provide applications with a more powerful data storage and retrieval system based on mathematical theories about data devised by Dr. E. F. Codd. Conceptually, a relational database can be pictured as a set of spreadsheets in which rows from one spreadsheet can be related to rows from another; in reality, however, the theory behind databases is much more complex. Each spreadsheet in a database is called a table. As with a spreadsheet, a table is made up of rows and columns.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Programming is all about data processing; data is central to everything you do with a computer. Databases—like filesystems—are nothing more than specialized tools for data storage. Filesystems are good for storing and retrieving a single volume of information associated with a single virtual location. In other words, when you want to save a WordPerfect document, a filesystem allows you to associate it with a location in a directory tree for easy retrieval later.

Databases provide applications with a more powerful data storage and retrieval system based on mathematical theories about data devised by Dr. E. F. Codd. Conceptually, a relational database can be pictured as a set of spreadsheets in which rows from one spreadsheet can be related to rows from another; in reality, however, the theory behind databases is much more complex. Each spreadsheet in a database is called a table. As with a spreadsheet, a table is made up of rows and columns.

A simple way to illustrate the structure of a relational database is through a CD catalog. Let's say that you have decided to create a database to keep track of your music collection. Not only do you want to be able to store a list of your albums, but you also want to use this data later to help you select music for parties. Your collection might look something like Table 2.1.

Table 2.1: A List of CDs from a Sample Music Collection
Artist	Title	Category	Year
The Cure	Pornography	Alternative

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

SQL keywords are case-insensitive, meaning that SELECT and select are treated exactly the same. Depending on your database, however, table and column names may or may not be case-insensitive. In addition, the space between words in a SQL statement is unimportant. You can have a newline after each word, several spaces, or just a single space. Throughout this book I use the convention of placing SQL keywords in all capitals and separating single SQL statements across multiple lines for readability.

How do you get the data into the database? And how do you get it out once it is in there? All major databases support a standard query language called Structured Query Language (SQL). SQL is not much like any programming language you might be familiar with. Instead, it is more of a structured English for talking to a database. A SQL query to the album titles from your database would look like this:

SELECT title FROM albums

In fact, much of the simplest database access comes in the form of equally simple SQL statements. Most of what you will do in SQL boils down to four SQL commands: SELECT, INSERT, UPDATE, and DELETE. You can issue SQL statements in several ways. The simplest, quickest way is through a SQL command-line tool. Each database engine comes with its own. Throughout most of this book, however, you will send your SQL as Java strings to JDBC methods.

I should also make a couple of other syntactic notes. First, the single quotation mark (') is used to mark string constants, and double quotation marks (") are used to show significant space, such as in column names that contain spaces in them.

Before you get into the four most common SQL statements, you need to actually create the tables in which your data will be stored. The major database engines provide GUI utilities that allow you to create tables without issuing any SQL. It is nevertheless good to know the SQL

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book deals almost exclusively with the current, widespread version of SQL, SQL2 (also called SQL/92). Part of the JDBC specification is that SQL2 is its supported SQL version. A newer and not universally supported SQL specification now exists, SQL3 (SQL/99). Among its most fundamental changes is support for abstract data types—an extremely useful change for developers programming in object-oriented languages like Java.

Newer versions of some databases—especially object-relational databases—now support some parts of the SQL3 specification. To take advantage of this important power in newer databases, the new JDBC 2.0 specification has added some extra features. I will note instances when you encounter SQL3-specific functionality, but you should be aware that these SQL calls are bleeding-edge SQL and thus unlikely to be supported by your particular database engine.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

These common thoughts are expressed in a shared public language, consisting of shared signs...a sign has a "sense" that fixes the reference and is "grasped by everybody" who knows the language...

— Noam Chomsky, Language and Thought

Database programming has traditionally been a technological Tower of Babel. You are faced with dozens of available database products, and each one talks to your applications in its own private language. If your application needs to talk to a new database engine, you have to teach it (and yourself) a new language. As Java programmers, however, you should not worry about such translation issues. Java is supposed to bring you the ability to "write once, compile once, and run anywhere," so it should bring it to you with database programming, as well.

SQL was a key first step in simplifying database access. Java's JDBC API builds on that foundation and provides you with a shared language through which your applications can talk to database engines. Following in the tradition of its other multi-platform APIs, such as the AWT, JDBC provides you with a set of interfaces that create a common point at which database applications and database engines can meet. This chapter will discuss the basic interfaces that JDBC provides.

Working with leaders in the database field, Sun developed a single API for database access—JDBC. As part of this process, they kept three main goals in mind:

JDBC should be a SQL-level API.
JDBC should capitalize on the experience of existing database APIs.
JDBC should be simple.

A SQL-level API means that JDBC allows you to construct SQL statements and embed them inside Java API calls. In short, you are basically using SQL. But JDBC lets you smoothly translate between the world of the database and the world of the Java application. Your results from the database, for instance, are returned as Java objects, and access problems get thrown as exceptions. Later in the book, you will go a step further and talk about how you can completely hide the existence of the database from a Java application using a database class library.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Working with leaders in the database field, Sun developed a single API for database access—JDBC. As part of this process, they kept three main goals in mind:

JDBC should be a SQL-level API.
JDBC should capitalize on the experience of existing database APIs.
JDBC should be simple.

A SQL-level API means that JDBC allows you to construct SQL statements and embed them inside Java API calls. In short, you are basically using SQL. But JDBC lets you smoothly translate between the world of the database and the world of the Java application. Your results from the database, for instance, are returned as Java objects, and access problems get thrown as exceptions. Later in the book, you will go a step further and talk about how you can completely hide the existence of the database from a Java application using a database class library.

Because of the confusion caused by the proliferation of proprietary database access APIs, the idea of a universal database access API to solve this problem is not new. In fact, Sun drew upon the successful aspects of one such API, Open DataBase Connectivity (ODBC). ODBC was developed to create a single standard for database access in the Windows environment. Although the industry has accepted ODBC as the primary means of talking to databases in Windows, it does not translate well into the Java world. First of all, ODBC is a C API that requires intermediate APIs for other languages. But even for C developers, ODBC has suffered from an overly complex design that has made its transition outside of the controlled Windows environment a failure. ODBC's complexity arises from the fact that complex, uncommon tasks are wrapped up in the API with its simpler and more common functionality. In other words, in order for you to understand a little of ODBC, you have to understand a lot.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now I am going to dive into the details about JDBC calls and how to use them. The examples in this book should run on your system regardless of the database or driver you use. The one phase when it is hard to achieve portability is the first step of connecting, because you have to specify a driver. I'll discuss that first to get it out of the way.

Figure 3.4 shows how an application uses JDBC to talk to one or more databases without knowing the details concerning the driver implementation for that database. An application uses JDBC as an interface through which it passes all its database requests.

Figure 3.4: JDBC shields an application from the specifics of individual database implementations

When you write a Java database applet or application, the only driver-specific information JDBC requires from you is the database URL. You can even have your application derive the URL at runtime—based on user input or applet parameters.

The JDBC Connection process is the most difficult part of JDBC to get right. The API itself is fairly straightforward, but many "gotchas" hide right beneath the surface. The new JDBC Standard Extension discussed in Chapter 5, will cover a simplified way of making database connections that avoids many of these problems. Unfortunately, few drivers support the JDBC Optional Package at this time. If you run into problems just making a connection, check if they match any of the following:

Connection fails with the message "Class not found": This message usually results from not having the JDBC driver in your CLASSPATH. You should remember to enter .zip and .jar files explicitly into a CLASSPATH. If you put all your

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that you are connected to the database, you can begin making updates and queries. The most basic kind of database access involves writing JDBC code when you know ahead of time whether the statements you are sending are updates (INSERT, UPDATE, or DELETE) or queries (SELECT). In the next chapter, you will discuss more complex database access that allows you to execute statements of unknown types.

Basic database access starts with the Connection object you created in the previous section. When this object first gets created, it is simply a direct link to the database. You use a Connection object to generate implementations of java.sql.Statement tied to the same database transaction. After you have used one or more Statement objects generated by your Connection, you can use it to commit or rollback the Statement objects associated with that Connection.

A Statement is very much what its name implies—a SQL statement. Once you get a Statement object from a Connection, you have what amounts to a blank check that you can write against the transaction represented by that Connection. You do not actually assign SQL to the Statement until you are ready to send the SQL to the database.

This is when it becomes important to know what type of SQL you are sending to the database, because JDBC uses a different method for sending queries than for sending updates. The key difference is the fact that the method for queries returns an instance of java.sql.ResultSet, while the method for nonqueries returns an integer. A ResultSet provides you with access to the data retrieved by a query.

JDBC's most fundamental classes are the Connection, the Statement, and the ResultSet. You will use them everytime you write JDBC code. This book has already discussed the details of the Connection class.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Support for different datatypes in SQL2 is poor. Since Java is an object-oriented language, however, datatype support is extremely rich. Therefore a huge disconnect exists between what sits in the database and the way you want it represented in your Java application. The SQL concept of a variable width, single-byte character array, for example, is the VARCHAR datatype. Java actually has no concept of a variable width, single-byte character array; Java doesn't even have a single-byte character type. The closest thing is the String class.

To make matters worse, many database engines internally support their own datatypes and loosely translate them to a SQL2 type. All Oracle numeric types, for example, map to the SQL NUMERIC type. JDBC, fortunately, lets you retrieve data in their Java forms defined by a JDBC-specified datatype mapping. You do not need to worry that a SQL LONG has a different representation in Sybase than it does in Oracle. You just call the ResultSet getLong() method to retrieve numbers you wish to treat as Java longs.

You do need to be somewhat concerned when designing the database, however. If you pull a 64-bit number into a Java application via getInt(), you risk getting bad data. Similarly, if you save a Java float into a numeric field with a scale of 0, you will lose data. The important rule of thumb for Java programming, however, is think and work in Java and use the database to support the Java application. Do not let the database drive Java. Table 3.2 shows the JDBC prescribed SQL to Java datatype mappings. Table 3.3 shows the reverse mappings. A full discussion of the SQL3 mappings will occur in Chapter 4.

Table 3.2: JDBC Specification SQL to Java Datatype Mappings (SQL3 Types in Italic
SQL Type (from java.sql.Types)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The single most visible addition to the JDBC API in its 2.0 specification is support for scrollable result sets. When the JDBC specification was first finalized, the specification contributors engaged in serious debate as to whether or not result sets should be scrollable. Those against scrollable result sets—and I was one of them—argued that they were antithetical to object-oriented programming and that they violated the rule that complex functionality should not encumber the most commonly used classes. In addition, requiring all driver vendors to implement scrollable result sets could adversely impact the performance of more mundane result set operations for some database engines. Scrollable result sets, on the other hand, are common in database vendor APIs, and the database vendors thus believed they should be present in JDBC.

Using scrollable result sets starts with the way in which you create statements. Earlier in the chapter, you learned to create a statement using the

createStatement(
)

method. The Connection class actually has two versions of createStatement()—the zero parameter version you have used so far and a two parameter version that supports the creation of Statement instances that generate scrollable ResultSet objects. The default call translates to the following call:

conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                     ResultSet.CONCUR_READ_ONLY);

The first argument is the result set type. The value ResultSet.TYPE_FORWARD_ONLY indicates that any ResultSet generated by the Statement returned from createStatement() only moves forward (the JDBC 1.x behavior). The second argument is the result set concurrency. The value ResultSet.CONCUR_READ_ONLY specifies that each row from a ResultSet is read-only. As you will see in the next chapter, rows from a ResultSet can be modified in place if the concurrency specified in the createStatement() call allows it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

JDBC provides a handful of other classes and interfaces that support JDBC's core functionality. Many of them are more SQL-friendly extensions of java.util classes like java.sql.Date and java.sql.Numeric. Others are exception classes that get thrown by JDBC calls.

The Types class provides constants that identify SQL datatypes. Each constant representing a SQL datatype that is mapped to an integer is defined by the XOPEN SQL specification. You will see this class used extensively in the next chapter.

The SQLException class extends the general java.lang.Exception class that provides extra information about a database error. The information provided by SQLException includes:

The SQLState string describing the error according to the XOPEN SQLState conventions. The different values of this string are defined in the XOPEN SQL specification.
The database-specific vendor error code. This code is usually a number you have to look up in the obscure reference section of your database's documentation. Fortunately, the error should be sufficiently described through the Java Exception class's getMessage() method.
A chain of exceptions leading up to this one. This is one of the niftier features of this class. Specifically, if you get several errors during the execution of a transaction, they can be piggybacked in this class. This is frequently useful when you have exceptions that you want to inform the user of, but you do not want to stop processing:

try {
    Connection connection = DriverManager.getConnection(url, uid,
                            pass);
}
catch( SQLException e ) {
    while( e != null ) {
        System.err.println("SQLState: " + e.getSQLState( ));
        System.err.println("    Code: " + e.getErrorCode( ));
        System.err.println("  Message:");
        System.err.println(e.getMessage( ));
        e = e.getNextException( );
    }
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter has covered a lot of ground. Now it is time to put all the information together in a single, concrete example: a Java servlet that serves up dynamic HTML content based on data in a database. This servlet will serve as a simple guest book. Visitors to the web page can enter their name, email address, and a few comments, as well as view a random list of other visitors to the site. This example assumes some level of familiarity with Java servlets, but you do not really need to have servlet knowledge to pick out the bits relevant to database access. For an excellent discussion of the Java Servlets API, see Java Servlet Programming by Jason Hunter with William Crawford (O'Reilly).

Before you can connect to a database, you need to have the information to make the connection. As the examples in this chapter have shown, and Example 3.2 in particular, you need a JDBC URL, the proper connection properties, and a way to register one or more JDBC drivers. For a servlet, the place to get this information is in the

init(
)

method. Like init() in applets, it is where a servlet does its initialization. It accepts the ServletConfig instance for this servlet from which you can grab initialization parameters. For this example, I have prefixed all initialization parameters with "gb.":

public void init(ServletConfig cfg) throws ServletException {
  super.init(cfg);
  driverName = cfg.getInitParameter("gb.driver");
  jdbcURL = cfg.getInitParameter("gb.jdbcURL");
   connectionProperties.put("user", cfg.getInitParameter("gb.user"));
   connectionProperties.put("password", cfg.getInitParameter("gb.pw"));
  try {
    driver = (Driver)Class.forName(driverName).newInstance( );
  }
  catch( Exception e ) {
    throw new ServletException("Unable to load driver: " +
                               e.getMessage( ));
  }
}

Under the servlet API, the ServletConfig object holds runtime configuration information. Use this information to capture all JDBC runtime configuration and save it in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The only thing that makes the device a quarter-detector rather than a slug detector or a quarter-or-slug detector is the shared intention of the device's designers, builders, owners, users. It is only in the environment or context of those users and their intentions that we can single out some of the occasions of state Q as "veridical" and others as "mistaken."

—Daniel C. Dennett, The Intentional Stance

Chapter 3, provides all the JDBC you absolutely need to know to build database applications. If you understand all of it and then put this book away, you will probably never feel like anything is missing. That is exactly how JDBC's creators intended the API to feel. They wanted to provide a few simple interfaces to support the majority of what database programmers want to do. Extended and complex functionality appears in extra interfaces designed specifically to support that functionality.

Advanced JDBC programming supports advanced needs. These advanced needs break down into two categories: optimizations and extended functionality. This chapter dives into all of the extended functionality included in the JDBC Core API.

Each SQL statement you send to the database needs to be parsed by the database engine before it can actually be executed. When the database parses a SQL statement, it reads the SQL to determine what you want the database to do, and then it formulates a plan for carrying out your instructions. This processing is called building a query plan.

In Chapter 3, each SQL statement you sent to the database required the database to treat the statement as a brand-new query and thus build a new query plan for it. This processing is necessary only if each statement requires a distinct query plan. If you are executing statements over and over again that have the same query plan, you are wasting processing power. If, for example, your banking application uses the SQL

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Each SQL statement you send to the database needs to be parsed by the database engine before it can actually be executed. When the database parses a SQL statement, it reads the SQL to determine what you want the database to do, and then it formulates a plan for carrying out your instructions. This processing is called building a query plan.

In Chapter 3, each SQL statement you sent to the database required the database to treat the statement as a brand-new query and thus build a new query plan for it. This processing is necessary only if each statement requires a distinct query plan. If you are executing statements over and over again that have the same query plan, you are wasting processing power. If, for example, your banking application uses the SQL

UPDATE ACCOUNT SET BALANCE
=

XXX

WHERE ACCOUNT_ID
=

YYY, you would force the database to rebuild the same query plan each time you changed the balance for the account. Databases enable you to optimize repeated calls through prepared SQL.

Databases provide two kinds of prepared SQL: prepared statements and stored procedures. Prepared SQL provides an advantage over the simple SQL statements you have covered so far; a database can get the SQL ahead of time and create a query plan while you are doing other application logic. This means that your SQL should execute faster and that you can have a generic reference to a statement for later reuse rather than repeatedly create new SQL statements for each new access to the database.

The optimization factor comes from the database knowing what you are about to do. When you create a Java instance of a prepared statement or stored procedure, you notify the database of what kind of SQL call that object represents. The database can then create a query plan for that SQL call before you ever actually execute it. When it comes time for you to execute the SQL, the database is ready for you. If you execute the same prepared SQL more than once, the database remains ready for your SQL without having to rebuild the query plan.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Complex systems often require both online and batch processing. Each kind of processing has very different requirements. Because online processing involves a user waiting on application processing order, the timing and performance of each statement execution in a process is important. Batch processing, on the other hand, occurs when a bunch of distinct transactions need to occur independently of user interaction. A bank's ATM machine is an example of a system of online processes. The monthly process that calculates and adds interest to your savings account is an example of a batch process.

JDBC 2.0 introduced new functionality to address the specific issues of batch processing. Using the JDBC 2.0 batch facilities, you can assign a series of SQL statements to a JDBC Statement (or one of its subclasses) to be submitted together for execution by the database. Using the techniques you have learned so far in this book, account interest-calculation processing occurs roughly in the following fashion:

Prepare statement.
Bind parameters.
Execute.
Repeat steps 2 and 3 for each account.

This style of processing requires a lot of "back and forth" between the Java application and the database. JDBC 2.0 batch processing provides a simpler, more efficient approach to this kind of processing:

Prepare statement.
Bind parameters.
Add to batch.
Repeat steps 2 and 3 until interest has been assigned for each account.
Execute.

Under batch processing, there is no "back and forth" between the database for each account. Instead, all Java-level processing—the binding of parameters—occurs before you send the statements to the database. Communication with the database occurs in one huge burst; the huge bottleneck of stop and go communication with the database is gone.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you remember scrollable result sets from Chapter 3, you may recall that one of the parameters you used to create a scrollable result set was something called the result set concurrency . So far, the statements in this book have used the default concurrency, ResultSet.CONCUR_READ_ONLY. In other words, you cannot make changes to data in the result sets you have seen without creating a new update statement based on the data from your result set. Along with scrollable result sets, JDBC 2.0 also introduces the concept of updatable result sets—result sets you can change.

An updatable result set enables you to perform in-place changes to a result set and have them take effect using the current transaction. I place this discussion after batch processing because the only place it really makes sense in an enterprise environment is in large-scale batch processing. An overnight interest-assignment process for a bank is an example of such a potential batch process. It would read in an accounts balance and interest rate and, while positioned at that row in the database, update the interest. You naturally gain efficiency in processing since you do everything at once. The downside is that you perform database access and business logic together.

JDBC 2.0 result sets have two types of concurrency: ResultSet.CONCUR_READ_ONLY and ResultSet.CONCUR_UPDATABLE . You already know how to create an updatable result set from the discussion of scrollable result sets in Chapter 3. You pass the concurrency type ResultSet.CONCUR_UPDATABLE as the second argument to createStatement(), or the third argument to prepareStatement() or prepareCall():

PreparedStatement stmt = conn.prepareStatement(
          "SELECT acct_id, balance FROM account",
          ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);

The most important thing to remember about updatable result sets is that you must always select from a single table and include the primary key columns. If you don't, the concept of the result set being updatable is nonsensical. After all, updatable result set only constructs a hidden

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

JDBC 1.x supported the SQL2 datatypes. JDBC 2.0 introduces support for more advanced datatypes, including the SQL3 "object" types and direct persistence of Java objects. Except for the BLOB and CLOB datatypes, few of these advanced datatype features are likely to be relevant to most programmers for a few years. While they are important features for bridging the gap between the object and relational paradigms, they are light years ahead of where database vendors are with relational technology and how people use relational technology today.

Stars of a bad horror film? No. These are the two most important datatypes introduced by JDBC 2.0. A blob is a B inary Large Object, and a clob is a C haracter Large Object. In other words, they are two datatypes designed to hold really large amounts of data. Blobs, represented by the BLOB datatype, hold large amounts of binary data. Similarly, clobs, represented by the CLOB datatype, hold large amounts of text data.

You may wonder why these two datatypes are so important when SQL2 already provides VARCHAR and VARBINARY datatypes. These two old datatypes have two important implementation problems that make them impractical for large amounts of data. First, they tend to have rather small maximum data sizes. Second, you retrieve them from the database all at once. While the first problem is more of a tactical issue (those maximum sizes are arbitrary), the second problem is more serious. Fields with sizes of 100 KB or more are better served through streaming than an all-at-once approach. In other words, instead of having your query wait to fetch the full data for each row in a result set containing a column of 1-MB data, it makes more sense to not send that data across the network until the instant you ask for it. The query runs faster using streaming, and your network will not be overburdened trying to shove 10 rows of 1 MB each at a client machine all at once. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Much of what you have done with JDBC so far requires you to know a lot about the database you are using, including the capabilities of the database engine and the data model against which you are operating. Requiring this level of knowledge may not bother you much, but JDBC does provide the tools to free you from these limitations. These tools come in the form of meta-data.

The term "meta" here means information about your data that does not interest the end users at all, but which you need to know in order to handle the data. JDBC provides two meta-data classes: java.sql.ResultSetMetaData and java.sql.DatabaseMetaData. The meta-data described by these classes was included in the original JDBC ResultSet and Connection classes. The team that developed the JDBC specification decided instead that it was better to keep the ResultSet and Connection classes small and simple to serve the most common database requirements. The extra functionality could be served by creating meta-data classes to provide the often esoteric information required by a minority of developers.

As its name implies, the ResultSetMetaData class provides extra information about ResultSet objects returned from a database query. In the embedded queries you made earlier in the book, you hardcoded into your queries much of the information a ResultSetMetaData object gives you. This class provides you with answers to the following questions:

How many columns are in the result set?
Are column names case-sensitive?
Can you search on a given column?
Is NULL a valid value for a given column?
How many characters is the maximum display size for a given column?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Narrow souls I cannot abide;there's almost no good or evil inside.

—Friedrich Nietzsche, The Gay Science

The JDBC API you have covered in this book is called the JDBC 2.0 Core API. The JDBC 2.0 Core API is a narrowly focused specification that supports the functionality required by applications to successfully access databases. With the JDBC 2.0 release, however, Sun added an API called the JDBC 2.0 Optional Package (formerly called the JDBC 2.0 Standard Extension) to support extended database access functionality. The JDBC 2.0 version of the Optional Package encompasses the following elements:

Data source-oriented database access via the new JNDI API
JDBC driver-based connection pooling
Rowsets
Distributed transactions

As I write this chapter, the JDBC 2.0 Optional Package has just been finalized. Very few drivers support any of this functionality. I will therefore cover as much of the JDBC 2.0 Optional Package in this chapter as possible, but I will not be able to do full justice to some topics due to the scarcity of available information at the time of writing.

In Chapter 3, we covered how to register a JDBC driver and make a connection using a JDBC URL. Perhaps you, like me and many others, found this to be a bit of an annoyance, especially if you are trying to write database-independent code. I am now about to tell you that all of that is completely unnecessary. You don't have to register drivers. You don't have to know anything about JDBC URLs. JDBC has discovered the marvels of naming and directory services.

Naming and directory services are basic to computing. Naming services are the tool through which programmatic things—files, printers, file servers, etc.—are matched to names. You do not print to your local printer by referencing its I/O port. You reference the printer by its name. A naming service inside your OS maps that printer name to an I/O port.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|

Table of Contents

Section 1.3.1.1: SQL

Section 1.3.1.2: JDBC