Introducing the JDBC API
JDBC supports the development of both standard Java and enterprise Java applications. The JDBC API consists of the two main packages: the java.sql package, which is part of standard Java, and the javax.sql package, which is part of enterprise Java. The following two sections will briefly discuss these two main packages.
The java.sql Package
This is a client-side API that allows making connection to a data source, handling database operations and queries, and providing security. The key interface to this package is the Connection interface, which encapsulates all the database operations in the application logic. Table 9.1 lists the JDBC interfaces and gives a brief description of each.
Figure 9.4 summarizes the main classes and interfaces of the JDBC API and the main methods used.
Figure 9.4. JDBC API main classes and interfaces.
The javax.sql Package
The javax.sql package extends the functionality of the JDBC API from a client-side API to a server-side API, and is an essential part of J2EE technology. The key interface to this package is the DataSource interface, which is the factory for creating connections. Other interfaces and classes of this package support distributed transactions, which are commonly used by EJB container providers. As application and bean developers, our main interface in this package is the DataSource interface.
Using Connection Pooling
JDBC supports the concept of connection pooling. A connection pool is a collection of database connections maintained and managed by the application server. A J2EE application reuses database connections from the connection pool. An application server assigns a connection transparently to the application. A connection pool is represented as a DataSource object.
The main reason for using a connection pool is to enhance the performance of running applications. The task of establishing a connection to a database is usually slow because it requires considerable time for the initialization process. With connection pools, connections are established during application server startup and are available to be used by all components. Both database servers and application servers run more efficiently with dedicated connections than if they have to handle incoming connection attempts at runtime. Using connection pools increases the scalability of the system, which can therefore handle more number of users.
Another reason for using connection pools is to separate the application code from database configuration. In setting up a connection pool, we use a declarative approach to describe these configuration settings outside the application. Applications do not need to know of or transmit the database username, password, and location. This separation between application logic and database environment allows you to develop portable and reusable code, which is an important factor in designing enterprise applications. A connection pool, represented by a DataSource object, is created and registered by the system administrators into the JNDI service using a logical name. Hence it becomes available as a resource to be shared by all system components and users. Figure 9.5 illustrates how database connection pooling works.
Figure 9.5. Database connection pool.
Configuring the Connection Pool
Connection pools are set in the application server configuration files. Depending on the application, you can have more than one connection pool, with each being used by different components of the application. The following parameters are required to configure a connection pool:
Connection pool name: Used to identify and register the DataSource with the JNDI service, and later used by the application to recognize the DataSource's name.
Initial number of connections: The number of connections to be created in the pool at startup.
Maximum and minimum pool size.
JDBC URL: Specifies the database location, database name, listening port, and the hostname.
JDBC driver class name.
JDBC driver properties: Any properties required to connect to the data source. They are name/value pairs, such as user ID and password.
Note
Setting up both maximum and minimum pool size is of prime importance. The impact of setting your maximum below the expected peak load of system users will degrade performance at the time you need it most.
A full guide to the JDBC API and its extensions can be found at http://java.sun.com/products/jdbc/.
Understanding DataSource Interface
A DataSource object is a factory for Connection objects. An object that implements the DataSource interface will typically be registered with a JNDI service provider. A DataSource is a representation of a database and its connection pool. An application uses the JNDI service provider to look up the connection pool name and creates a DataSource object factory. An application can be directed to a different data source simply by changing the DataSource object's properties; no change in the application code is needed. Likewise, a DataSource implementation can be changed without changing the application code that uses it, which enhances application portability.
To create a Connection object from a DataSource object, we can use either a declarative or a programmatic approach. In the following sections, we will discuss both these approaches.
Declarative Approach
One of the objectives of the J2EE architecture is to enhance the deployment of enterprise applications. This is accomplished by separating the application code, from its deployment and configuration files. In the declarative approach, you set all configuration parameters of the databases and connection pools in a configuration file or deployment descriptor.
Using the declarative approach, a Connection object is created from a DataSource object by using the following method:
Connection Conn = datasource.getConnection()
This method has no parameters, and the container, behind the scenes, will use all the settings in the deployment and configuration files as the default settings.
If the database location or settings must be changed, you change only the configuration files without any modifications to the application code. This container-managed approach is one of the attractive features for many of the common services provided by the J2EE platform.
Programmatic Approach
The programmatic approach, on the other hand, enables you to control and manage the database setting from inside the application code. The following method is used in making a connection to the database using this approach:
Connection conn =
dataSource.getConnection (String username, String password);
This method creates a Connection to the database by overriding the default user and password. Both the username and password must be hard-coded in the application code, which has a negative affect on application portability.
Caution
Another method of obtaining a database connection is the use of the DriverManager. The DataSource concept was introduced in the JDBC 2.0 Extension package. The JDBC specification recommends the cleaner DataSource method as the preferred method of obtaining a Connection for a J2EE application. The current use of obtaining a Connection through hard-coded parameters to the DriverManager method is deprecated. DriverManager is a shared object with synchronized methods and therefore it's single threaded. This technique establishes a bottleneck for the applications trying to access databases. On the other hand, the DataSource object is a multithreaded object than can handle the access of more than one concurrent user.
Learning the Connection Interface
A connection to a specific database represents a user session. Within the context of a Connection, SQL statements are executed and results are returned to the client for more processing. Connections are created from a DataSource object as described in the previous section. A connection is assigned transparently to the application by the JDBC driver. Table 9.2 summarizes the methods used for the Connection interface.
A Connection object to the database is able to provide connection parameters and schema object information. This includes data tables, supported SQL grammar, stored procedures, and the capabilities of this connection. This information can be obtained with the getMetaData() method.
Note
A Connection object manages transaction behavior. By default, the Connection automatically commits changes after executing each statement. If autoCommit has been disabled, an explicit commit() must be done or database changes will not be saved.
Exception Handling
A number of exceptions can be thrown as a result of connecting to a database or performing any of the operations mentioned earlier. The main exception is SQLException, which is thrown by most of the methods in the java.sql package. Other exceptions are summarized in Table 9.3.
Note
SQLWarning objects are not thrown as other exceptions—you have to query them. SQLWarning objects will build up due to multiple Statement method calls (such as execute() and executeUpdate()) until you ask for each Statement object with getWarning() and getNextWarning(). Statement objects automatically clear warnings on the next execution.
SQLExceptions must be handled in the catch clause. Information about errors can be obtained by the getErrorCode(), which prints a vendor-specific error. The getSQLState() method prints a standard SQL message. In addition, the method getMessage() prints a message that describes the error.