Thẻ
Today, you’ll learn how to use Java Database Connectivity (JDBC) to connect components of an enterprise application to any data source. JDBC is the standard and unified API to access any data in a tabular form, whether it is a relational database, spreadsheet, or flat file. We’ll give a brief account of the JDBC architecture and the different clients and components across all the J2EE tiers that can use it. Using snippet of codes, the JDBC API will be explained. We’ll wrap up with an example to highlight the main interfaces and classes of the JDBC. We’ll emphasize the fact that JDBC is a vendor-neutral API by running an example in two different environments of application servers, without changes to the sample code.
We’ll also discuss features of JDBC such as connection pooling, data sources, SQL queries and updates, and advanced concepts including prepared statements, local transactions, metadata, and batch updates. Finally, we’ll explore the built-in features of the API to optimize your access to data sources.
-
Learn the rationale behind JDBC
-
Explore the JDBC architecture
-
Learn the JDBC API’s classes and interfaces
-
Learn how to connect to a database using JDBC
-
Learn how to perform local transactions using JDBC API
-
Explore the data manipulation operations of databases using JDBC
-
Study query optimization to databases
-
Learn how to use batch updates for optimal operations
-
Work with metadata for databases and query results
-
Work with a practical example to apply what you learned today
- .tt1 {font-size: 10pt;
-
Why JDBC?
JDBC is a standard API that lets you access virtually any data source in tabular format from your J2EE applications. It provides cross-DBMS connectivity to a wide range of SQL databases, spreadsheets, and flat files. SQL is the lingua franca of the standard database language to separate application data from its logic.
JDBC enables developers to write enterprise applications that run on any J2EE-compliant application server that requires access to enterprise data. It provides separation of application logic from the underlying database operating environment. JDBC encapsulates the connecting method, database vendor, security, and multiuser access. With a JDBC technology–enabled driver, a developer can even connect all corporate data in a heterogeneous database environment.
JDBC is used by many components across all J2EE tiers. First, in the client tier, JDBC can be accessed by Java applets or Java applications. Second, in the Web tier, it can be accessed by JSP, servlet, or Taglib. Finally, all types of EJBs such as session, entity, and message-driven beans can use JDBC from the EJB tier.
Later in this book, on Day 11 “Developing Container-Managed Persistence Entity Beans,” you’ll learn about entity beans in a container-managed persistence (CMP) mode, where application logic is separated from its persistence. JDBC is still being used behind the scenes in the deployment descriptor and in a declarative manner, but is not used directly by the code. However, this is different from the case of bean-managed persistence (BMP) where entity beans use JDBC to manage database access, which will be addressed on Day 10, “Developing Bean-Managed Persistence Entity Beans.”
JDBC Architecture
JDBC is designed using a two-tier approach. It’s a unified and standard API used by all J2EE applications and components to access databases through a provider-specific driver. Such clean separation of application logic and database-specific environment helps an enterprise application to be portable and reusable across multiple databases.
Note
JDBC is similar in concept to ODBC (Open Database Connectivity), a C-based standard to access databases. In fact, both JDBC and ODBC are interoperable; that is, both access each other through the JDBC-ODBC Bridge. JDBC provides enhanced services such as connection pooling, which will be covered later today.
Figure 9.1 illustrates the layered approach of the JDBC architecture. An application client uses a unified JDBC API to access one or many databases, even in a heterogeneous environment. Many of the database products provide JDBC drivers, including Oracle, SQLServer, DB2, Sybase, PointBase, Cloudscape, Hypersonic, Postgres, and mySQL.
Figure 9.1. JDBC architecture.
It’s possible that clients and components across all the J2EE tiers can access the database through the JDBC API (see Figure 9.2). However, it’s recommended in practice that JDBC connections should be near to the data source itself. This implies that components in the EJB-tier are encouraged to use the JDBC to connect to databases. This not only enhances the security aspect of your enterprise applications, but it also increases their portability. Moreover, EJBs provide built-in mechanisms for transaction control, and placing JDBC calls in well-designed EJBs frees you from programming local transactions using JDBC or distributed transactions. Transactions will be covered in detail in Day 16.
Figure 9.2. JDBC clients.
At a minimum, JDBC assumes that all underlying database vendors support the SQL-2 database access language. Since its original inception in 1997, the JDBC specification has focused on these issues:
-
Offers vendor-neutral access to common functionality that most database server vendors must implement in order to be JDBC-compliant.
-
Supports advanced SQL data types (part of SQL3), such as Blobs (Binary Large Object), Clobs (Character Large Object), and arrays and their mappings, to native Java objects.
-
Provides implicit support for database reliability, availability, and scalability. The Standard Extension API describes advanced support features, such as enhancement of database performance and JNDI support.
Choosing the Right JDBC Driver
Different types of JDBC drivers are available for a J2EE application’s use. Two-tier drivers provide direct access to databases from a Java application. Two-tier drivers are vendor specific. Three-tier drivers provide access to databases through the middle tier, which enables you to manage database resources centrally through the application server. Three-tier JDBC drivers are vendor neutral, and make it easier to integrate off-the-shelf components to your DBMS environment and to write more portable code. Moreover, they help you to develop scalable, reliable, and available J2EE applications.
JDBC Driver Types
JDBC drivers are commonly identified by type. The following list provides a brief description of each driver type, which will help you choose a JDBC driver type that fits your application requirements:
-
Type 1: Uses ODBC as the primary interface to the database. The client makes JDBC calls that are converted to ODBC by the JDBC-ODBC Bridge, which is also required on the client tier.
-
Type 2: Uses a native database library as an interface to the database. The client makes JDBC calls that are converted to the native code through a JDBC-native driver. The native library is a proprietary API, such as OCI (Oracle Client Interface). This driver type is normally used for high-performance and large transaction volume requirements.
-
Type 3: This is a multitier, vendor-neutral driver. No driver is required at the client tier. Clients make JDBC calls through the network (RMI calls) to the JDBC driver that resides in the middle tier (application server tier).
-
Type 4: This is an all-Java driver. Clients make JDBC calls directly to the database. Because it’s a native Java driver, it performs according to the underlying Java Virtual Machine (JVM). It’s the most frequently used JDBC driver type.
Figure 9.3 depicts these drivers in a multi-tier approach.
Figure 9.3. Types of JDBC drivers.
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.
Connecting to a Data Source
Applications must first be connected to a database before performing any database operation. As explained earlier, connections are made ready by the container, at startup, through the creation of a connection pool.
Connections are created using the DataSource object. Applications need to locate a DataSource object, before creating a Connection. Applications locate a DataSource through the JNDI service. The following is an example of these steps:
// Connect to the default JNDI service and establish a context
Context ctx = new InitialContext();
// Lookup the DataSource for the configured database
javax.sql.DataSource ds = (javax.sql.DataSource)
ctx.lookup ("java:comp/env/jdbc/styejbDB");
// Make a connection to the database
java.sql.Connection conn = ds.getConnection();
// do some work
// Release the connection after you are done
conn.close();
Note
You must release the connection as soon as you have done work with it by closing the connection. When the client virtually closes the connection with close(), behind the scenes, the container returns the connection to the pool, and makes it available for use by other clients.
As recommended, JDBC DataSource references should always be declared in the java:comp/env/jdbc subcontext. This is established by the system administrator when the DataSource is being created.
Establishing a context to the JNDI service is an expensive operation, especially when using an RMI call. Always connect to the JNDI service to obtain a DataSource object in the initialization code of your application. This can be done once, and connection(s) can be assigned by the DataSource to other parts of the application during its lifetime.
Applications rely on the container to manage the connection pool, but when building scalable applications, releasing connections as soon as possible is the responsibility of the developer.
The traditional way of getting a connection is by using the DriverManager. As mentioned early today, this is a deprecated method, and we recommend using the DataSource method if possible. For the sake of completeness, the following snippet shows how to make a connection using the DriverManager:
String sourceURL = "jdbc:cloudscape:styejbPool"; String driverClass = "COM.cloudscape.core.JDBCDriver" // Loading the JDBC driver Class.forName(driverClass); // Make a connection using the DriverManager java.sql.Connection conn = DriverManager.getConnection(sourceURL);
Data Manipulation
After a connection is made to the data source, the JDBC API is furnished with comprehensive operations. Both DDL (Data Definition Language) and DML (Data Manipulation Language) operations are available. Metadata (information about the database itself) or the result set can also be queried.
Note
We assume that the reader is familiar with SQL fundamentals. For more coverage of SQL, we recommend you to refer to the book, Sams Teach Yourself SQL in 10 Minutes (ISBN: 0672321289).
A Statement object represents a SQL statement, and must be created from the Connection object. A Statement sends simple SQL statements to the database:
// Create a Statement object java.sql.Statement stmt = conn.createStatement();
One of the powerful methods of the Statement object is the execute() method. All DDL and DML database operations can be performed using the execute() method.
Caution
All DDL operations, such as creating and dropping objects, can be performed by the execute() method of the Statement object. However, creating a database instance is DBMS-specific, and is not available to all JDBC drivers.
// Using the execute method for some DDL operations try { stmt.execute(“DROP TABLE Student “); } catch (SQLException e) { System.out.println(“Table Student already exists.”); } stmt.execute(“CREATE TABLE Student (id integer, fname varchar(15), lname varchar(15), ssn varchar(12))”); System.out.println(“Table Student is created…”); // Using the execute method for some DML operations stmt.executeUpdate (“INSERT into Student values (1, ‘Lillian’, ‘Ghaly’ , ‘111-000-1111’)”); stmt.executeUpdate (“INSERT into Student values (2, ‘Raj’, ‘Talla’ , ‘222-000-2222’)”); stmt.executeUpdate (“INSERT into Student values (3, ‘Tony’, ‘Hunter’ , ‘333-000-3333’)”); stmt.executeUpdate (“INSERT into Student values (4, ‘John’, ‘Smith’ , ‘444-000-4444’)”); // close statements when done stmt.close();
Note
When creating a Statement, resources will be allocated to the application in order to execute the SQL. It is vital to release these resources, by closing the Statement when execution is complete, using the close() method.
The execute() method returns a boolean: true if the next result is a ResultSet object, or false if it is an update count or there are no more results. The following code gets a ResultSet object, which holds the result of the last query:
stmt.execute(“SELECT * from DAY09_STUDENTS”); // Obtain the result of the last query ResultSet rs = stmt.getResultSet();
The ResultSet is initially positioned before the first row. Table 9.4 gives a summary of the ResultSet methods.
|
Method |
Purpose |
|---|---|
|
Boolean next() |
Scrolls the cursor to the next available row |
|
String getString(int columnIndex) |
Returns data at the current cursor, under a particular column number or index |
|
String getString(String columnName) |
Returns data at the current cursor, under a particular column name |
|
Boolean isFirst() |
Returns true if the cursor is at the first row |
|
Boolean isLast() |
Returns true if the cursor is at the last row |
|
int getFetchSize() |
Returns the default fetched number of rows |
|
setFetchSize(int rows) |
Set the required number of rows to be fetched |
|
ResultSetMetaData getMetaData() |
Returns data about the ResultSet, such as number of columns and the properties of data elements |
Now we can scroll through the ResultSet to display the retrieved data using getString() method. From the earlier example, the ResultSet contains four columns and four rows. Columns can be identified by column name or column number. This example uses the column name:
// Display the ResultSet data on the screen using column name while (rs.next()) System.out.println(rs.getString(“student_id”) + ” ,” + rs.getString(“first_name”) + ” ,” + rs.getString(“last_name”) + ” ,” + rs.getString(“address”));
When using the column number, you pass an integer value to getString() that starts with 1, which represents the first column:
// Display the ResultSet data using column number while (rs.next()) System.out.println(rs.getString(1) + ” ,” + rs.getString(2) + ” ,” + rs.getString(3) + ” ,” + rs.getString(4)); // close the result set after done. rs.close();
In both cases, the output of the last println should look like this:
1, LILLIAN, GHALY, 15 DIAMOND ST, BOSTON, MA 3, SAM, LE, 12 APPLEBEE RD, LOS ANGELES, CA
Much like the Statement object, the ResultSet object can be tuned to the optimum number of fetched rows. To do this, use the getFetchSize() and setFetchSize() methods of ResultSet. This increases the performance when a large number of rows is retrieved during search operations. Close the ResultSet object when you’re done to release allocated resources.
Optimized Queries to the Database
A shortcut method of querying the database is to use the executeQuery() method of the Statement object. This combines both execute() and getResultSet() in one method. The preceding example can be written as
// Obtain the ResultSet directly
ResultSet rs = stmt.executeQuery("SELECT * DAY09_STUDENTS");
Both simple queries, such as the one specified in the preceding example, and more sophisticated joins can be specified as a parameter String to the executeQuery() method.
Another variant used with the INSERT, UPDATE, and DELETE operations is the executeUpdate() method:
// Using the executeUpdate method instead of execute()
String sql = "INSERT into DAY09_STUDENTS values " +
"('1', 'LILLIAN', 'GHALY', '15 DIAMOND ST, BOSTON, MA')");
stmt.executeUpdate(sql);
The executeUpdate() method specializes in DML operations. DDL operations, such as drop table, create table, and so on, are made available only through the execute() method as explained in the previous section.
Using a PreparedStatement
In situations where the same statement is performed repeatedly, a more optimized way is to use a PreparedStatement. This divides the operation into a creation phase and an execution phase. When creating the statement, the database is instructed to be ready by pre-parsing and compiling the statement, and preparing its buffers to assign variables to the table elements. The execution phase requests the database to execute the operation after the required elements filled up. Let’s illustrate this with the following code:
// Create a PreparedStatement
PreparedStatement pstmt =
conn.preparedStatement("INSERT INTO DAY09_STUDENT values (?,?,?,?)")
The database is now instructed to prepare the buffers for the operation. Each data element is mapped to the wild card in sequence. We use setString() method to fill up the holes:
// Fill up the data elements and execute pstmt.setInt(1, 2); pstmt.setString(2, "DOUG"); pstmt.setString(3, "ZANDER"); pstmt.setString(4, "11 ORANGE AVE, SACRAMENTO, CA"); int rcount = pstmt.executeUpdate();
Other methods to pass these parameters depend on the parameter type, and take the pattern setXXX(). For example, setInt() to pass in an int, setFloat() to pass in a float, setBoolean() to pass in a boolean, and so on.
Using PreparedStatement saves time for a repeated statement, and hence enhances the performance of your application. The return value of the last executeUpdate() indicates the number of rows affected as a result of any INSERT, UPDATE, or DELETE operation. The PreparedStatement inherits all its properties and methods from the Statement object.
Using a CallableStatement for Stored Procedures
The JDBC API provides support for calling a stored procedure. The CallableStatement inherits from PreparedStatement, and is used to call a stored procedure. A stored procedure is a group of SQL statements that can be called by name, and are stored in a file and managed by the underlying Relational Database Management System (RDBMS). Stored procedures, once written, can be compiled and then reused. They are executed on the database server, which relieves the application server of performing the task. The CallableStatement is created from the Connection method prepareCall().
The following snippet demonstrates how the stored procedure getStudentById is created and sent to the RDBMS to compile and store under the name getStudentById:
// Create stored procedure
String storedPoc = "create procedure GetStudentById(Stid integer)" +
"as begin" +
"SELECT * FROM DAY09_STUDENT" +
"WHERE student_id = 'Stid'" +
"end";
Statement stmt = conn.createStatement();
stmt.executeUpdate(storedPoc);
The next code demonstrates the use of a CallableStatement that calls the previous stored procedure getStudentById:
CallableStatement cstmt = conn.prepareCall(
"{call getStudentById(?)}");
cstmt.setInt(1,4);
ResultSet rs = cstmt.executeQuery();
The variable cstmt contains a call to the stored procedure getStudentById, which has one IN parameter, represented by the wildcard ? placeholder. Normally, stored procedure parameter types can be one of type IN, OUT, or INOUT. Passing in any IN parameter values to a CallableStatement object is done using the setXXX() methods inherited from PreparedStatement. The JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. Registering the JDBC type is done with the method registerOutParameter() of the CallableStatement. After the statement has been executed, CallableStatement‘s getXXX() methods can be used to retrieve OUT parameter values. An INOUT parameter requires both treatments of IN and OUT parameter.
Caution
Care should be taken when writing stored procedures. Because it belongs to the data layer, too much logic in a stored procedure violates the purpose of separating data from the application logic. Be aware also that stored procedures do not work the same way across all RDBMSes. In addition, all stored procedures must be compiled again if you have to change just one of them
Using Local Transactions
A transaction is a group of SQL statements that represents one unit of work. A transaction executes all of its statements or none at all. JDBC handles both local and distributed transactions. Today we’re covering only local transactions; distributed transactions are deferred to Day 16, when you’ll study the concepts of the JTA (Java Transaction API).
A local transaction belongs only to the current process, and deals with only a single resource manager that handles a DataSource. An RDBMS is an example of a resource manager., A distributed transaction, on the other hand, manages multiple DataSources across multiple processes. All database operations mentioned earlier today that use a Statement object are implicitly transactional with auto-commit. That means the DBMS commits the transaction as soon as any execute(), executeUpdate(), or executeBatch() is done.
Local transactions are managed by the Connection object, and not by the EJB container. To change this implicit behavior, JDBC provides the method setAutoCommit(false) to set the transaction mode on the Connection object. The commit() and rollback() methods also are used by the Connection object to control the transaction’s behavior. An example of a local transaction is as follows:
// Create a Statement from Connection and its set transaction mode conn.setAutoCommit(false); Statement stmt = conn.createStatement(); try { stmt.executeUpdate(“UPDATE DAY09_STUDENT set first_name=’Laura’ where student_id =’5′”); // assume something wrong happen here….. stmt.executeUpdate(“UPDATE DAY09_STUDENT set last_name=’Smith’ where student_id =’5′”); conn.commit(); } catch (SQLException ex) { conn.rollback(); stmt.close(); }
Disabling auto-commit using setAutoCommit(false) is required when a batch of SQL statements must execute together, or the EJB container is managing the transaction. Controlling transactions can increase performance because you commit a batch of SQL statements instead doing so one at a time. JDBC architects realized this fact and have built the concept of batch updates into the JDBC API. Batch updates will be covered in the next section.
Another concept related to local transactions is handling concurrency by setting the transaction isolation level, which will be covered during your study of transactions on Day 16
Using Batch Updates
Another aspect of enhancing performance of applications is the reduction of network traffic between J2EE tiers, or applications that partitioned to run on different servers. One way to reduce network traffic back and forth between components and enhance the performance of the running application is to use bulk updates, which are coarse-grain updates. This experience is reflected by the JDBC architects in the JDBC API, and is implemented by using the addBatch() method of the Statement object to prepare the batch before using the executeBatch() method. Batch updates use any of the INSERT, UPDATE, and DELETE operations. To use batch updates, you must disable the auto-commit mode.
try{
// Disable auto-commit
conn.setAutoCommit(false);
// Create a Statement from Connection
Statement stmt = conn.createStatement()
stmt.addBatch("INSERT INTO DAY09_STUDENT " +
"values('7', 'ERIC', 'CHRISTIAN', '1 MEMORIAL DRIVE, CAMBRIDGE, MA')";
stmt.addBatch("UPDATE DAY09_STUDENT set first_name='Laura' where id='5'");
int [] rc = stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch(BatchUpdateException sqlb) {
System.err.println("SQL State: " + sqlb.getSQLState());
System.err.println("Message: " + sqlb.getMessage());
System.err.println("Vendor Error: " + sqlb.getErrorCode());
System.err.print("Update counts: ");
int [] updateCounts = sqlb.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
}
The executeBatch() method returns an array of integers that specifies the row count of each operation in the batch.
Batch updates throw BatchUpdateException, which inherits from SQLException, and information can extracted about each update. Performance can be enhanced by tuning the number of rows to be fetched at a runtime from database. By default, you can get the default number of rows provided by the JDBC driver by using getFetchSize() on the Statement object. You can tune the size by using the setFetchSize(int rows) method of the Statement object.
Working with Metadata
Metadata is data about data, and in the context of our study of JDBC, it’s the information provided about the properties of data sources and result sets. Querying metadata is very powerful in creating tools for database manipulation and sophisticated database applications.
To find information about the connected database, we use the method getMetaData() of the Connection object to retrieve a DatabaseMetaData object. You can get information about schemas, tables, and properties of each table. The following sample queries database metadata to display all the table names and properties of each table in the schema "APP":
// Finding MetaData about database
DatabaseMetaData dbmd = conn.getMetaData();
String[] types = {"TABLE"};
System.out.println("Database Table Names for the Schema "APP")
rs = dbmd.getTables(null, "APP", null, types);
while (rs.next()){
String tableName = rset.getString("TABLE_NAME");
System.out.println("Table Name: " + tableName);
ResultSet rsc = dbmd.getColumns(null,null,tableName,"%");
System.out.println("Column Name" + " Data Type" + " Width");
while (rsc.next()){
System.out.println(rsc.getString("COLUMN_NAME") + "..." +
rsc.getString("TYPE_NAME") + "..." +
rsc.getInt("COLUMN_SIZE"));
}
}
When sending a SELECT statement to the database, you receive a ResultSet that holds the records to satisfy your criteria. To find metadata information about a ResultSet object, use the getMetaData() method of the ResultSet interface. This metadata will be captured and retrieved by getMetaData() into a ResultSetMetaData object. Such metadata of a ResultSet is the number of columns and the database properties of each column, such as its name, type, width, and precision. An example of retrieving a ResultSetMetaData object about a ResultSet is the following:
// Finding MetaData about result set
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from student");
ResultSetMetaData rsmd = rs.getMetaData();
int cn = rsmd.getColumnCount();
// Printing column information
for (int i=1; i<= cn ; i++) {
if (i > 1) { System.out.print(", "); }
System.out.print(rsmd.getColumnLabel(i));
}
System.out.print("")
// Printing the data
while (rs.next ()) {
for (int i=1; i<=cn; i++) {
if (i > 1) { System.out.print(", "); }
System.out.print(rs.getString(i));
}
System.out.println("");
}
By combining ResultSetMetaData and DatabaseMetaData, toolmakers can build very powerful database interactive tools to query and schema objects and data.
Working with JDBC Through a Full Example
Now it’s time for todayís example. Listing 9.1 gives a skeleton of code that performs the following:
-
Connects to a database
-
Performs few of both DDL and DML statements
-
Queries ResultSet metadata and database metadata
-
Closes the statements and the connection
-
Handles SQL exceptions
To demonstrate the strong aspect of JDBC as a vendor-neutral API, the same example runs on two different application servers, without changing any line of code. The QueryDB session bean is used to represent the client in query the database. Listings 9.1 to 9.3 are for the home interface, remote interface, and the bean class. Listings 9.4 to 9.7 are for the EJB client, standard deployment descriptor, WebLogic Server deployment descriptor, and the JBoss deployment descriptor.
Listing 9.1 The Home Interface day09/QueryDBHome.java
package day09;
import java.rmi.RemoteException;
import javax.ejb.*;
/*
QueryDBHome is the home interface for the stateless session bean.
*/
public interface QueryDBHome extends EJBHome{
QueryDB create() throws CreateException, RemoteException;
}
Listing 9.2 The Remote Interface day09/QueryDB.java
package day09;
import java.util.*;
import javax.ejb.*;
import java.rmi.RemoteException;
/*
QueryDB is the remote interface for the stateless session bean.
*/
public interface QueryDB extends EJBObject{
public void initDB() throws RemoteException;
public void doDDL() throws RemoteException;
public void getMetaData() throws RemoteException;
}
Listing 9.3 The Bean Class day09/QueryDBBean.java
package day09;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.sql.*;
/**
* QueryDBEJB is stateless session bean to query database properties
*/
public class QueryDBBean implements SessionBean
{
public void initDB(){
try {
System.out.println("\nDay 9: Demonstrate the use of JDBC...\n");
System.out.println("initDB: Get initial context from the JNDI service...");
ctx = new InitialContext();
System.out.println("Lookup the DataSource as configured by administrator...");
ds = (javax.sql.DataSource)ctx.lookup ("java:comp/env/jdbc/styejbDB");
System.out.println("Getting a Connection from the pool...");
conn = ds.getConnection();
System.out.println("Connection is obtained...");
} catch (Exception e) {
System.out.println("Exception was thrown: " + e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException sqle) {
System.out.println("SQLException during close(): " + sqle.getMessage(
));
}
}
}
public void doDDL(){
System.out.println("Run some DDL statements:");
try{
conn = ds.getConnection();
stmt = conn.createStatement();
try {
System.out.println("Trying to drop table DAY09_STUDENTS...");
stmt.execute("DROP TABLE DAY09_STUDENTS");
} catch (SQLException e) {
System.out.println("Table DAY09_STUDENTS already exists.");
}
stmt.execute("CREATE TABLE DAY09_STUDENTS (student_id varchar(12),"+
"first_name varchar(15),"+
"last_name varchar(15),"+
"address varchar(64))");
System.out.println("Table DAY09_STUDENTS is created...");
System.out.println("Run some DML statements:");
stmt.executeUpdate("INSERT into DAY09_STUDENTS values " +
"('1', 'LILLIAN', 'GHALY', '15 DIAMOND ST, BOSTON, MA')");
stmt.executeUpdate("INSERT into DAY09_STUDENTS values " +
"('2', 'DOUG','ZANDER','11 ORANGE AVE, SACRAMENTO, CA' )");
stmt.executeUpdate("INSERT into DAY09_STUDENTS values " +
"('3', 'SAM','LE', '12 APPLEBEE RD, LOS ANGELES, CA' )");
stmt.executeUpdate("DELETE from DAY09_STUDENTS where student_id = '2'");
rs = stmt.executeQuery("SELECT * from DAY09_STUDENTS");
// Get some Metadata about result set
System.out.println("Query ResultSet Metadata:");
rsmd = rs.getMetaData();
cn = rsmd.getColumnCount();
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");
while (rs.next()) {
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rs.getString(i));
}
System.out.println("");
}
} catch (Exception e) {
System.out.println("Exception was thrown: " + e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException sqle) {
System.out.println("SQLException during close(): " + sqle.getMessage());
}
}
}
public void getMetaData() {
// Get some Metadata about database
System.out.println("Query Database Metadata:");
try{
conn = ds.getConnection();
dbmd = conn.getMetaData();
System.out.println(" Product Name: " + dbmd.getDatabaseProductName());
System.out.println(" Driver Name: " + dbmd.getDriverName());
rs = dbmd.getSchemas();
System.out.println("Database Schemas:");
rsmd = rs.getMetaData();
cn = rsmd.getColumnCount();
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");
while (rs.next()) {
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rs.getString(i));
if (schema == null)
schema = new StringBuffer(rs.getString(i));
}
System.out.println("");
}
String[] types = {"TABLE"};
System.out.println("Printing All Data Tables for Schema: " + schema);
rs = dbmd.getTables(null, schema.toString(), null, types);
while (rs.next()){
String tableName = rs.getString("TABLE_NAME");
System.out.println("Table Name: " + tableName);
ResultSet rsc = dbmd.getColumns(null,null,tableName,"%");
rsmd = rsc.getMetaData();
cn = rsmd.getColumnCount();
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");
while (rsc.next()) {
for (i=1; i<= cn ; i++) {
if (i>1) System.out.print(", ");
System.out.print(rsc.getString(i));
}
System.out.println("");
}
}
} catch (Exception e) {
System.out.println("Exception was thrown: " + e.getMessage());
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException sqle) {
System.out.println("SQLException during close(): " + sqle.getMessage());
}
}
}
public void setSessionContext(SessionContext ctx) {this.bctx = ctx;}
public void ejbCreate() throws CreateException {}
public void ejbRemove() {}
public void ejbActivate() {}
public void ejbPassivate() {}
private SessionContext bctx;
private Context ctx = null;
private StringBuffer schema = null;
private javax.sql.DataSource ds = null;
private java.sql.Connection conn = null;
private java.sql.Statement stmt = null;
private java.sql.ResultSet rs = null;
private java.sql.ResultSetMetaData rsmd = null;
private int cn, i;
private java.sql.DatabaseMetaData dbmd = null;
Listing 9.4 The EJB Client Client.java
package day09;
import java.util.*;
import java.rmi.*;
import java.io.*;
import javax.naming.*;
import javax.ejb.*;
public class Client {
public static void main(String argv[]) {
Context initialContext = null;
QueryDBHome qdbHome = null;
QueryDB qdb = null;
System.out.print("\nDay 9: Demonstration the use of JDBC...\n ");
try
{
System.out.print("Looking up the QueryDB home via JNDI.\n");
initialContext = new InitialContext();
Object object = initialContext.lookup("day09/QueryDBHome");
qdbHome = (QueryDBHome)
javax.rmi.PortableRemoteObject.narrow(object, QueryDBHome.class);
System.out.print("Creating an Query DB.\n");
qdb = (QueryDB) qdbHome.create();
qdb.initDB();
qdb.doDDL();
qdb.getMetaData();
}catch ( Exception e){
e.printStackTrace();
}
}
}
Listing 9.5 The Standard Deployment Descriptor ejb-jar.xml
<?xml version="1.0"?>
<!DOCTYPE ejb-jar PUBLIC
'-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN'
'http://java.sun.com/dtd/ejb-jar_2_0.dtd'>
<ejb-jar>
<enterprise-beans>
<session>
<ejb-name>QueryDB</ejb-name>
<home>day09.QueryDBHome</home>
<remote>day09.QueryDB</remote>
<ejb-class>day09.QueryDBBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Container</transaction-type>
<resource-ref>
<res-ref-name>jdbc/styejbDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Application</res-auth>
</resource-ref>
<resource-env-ref>
<resource-env-ref-name>jdbc/styejbDB</resource-env-ref-name>
<resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>
</session>
</enterprise-beans>
</ejb-jar>
Listing 9.6 The WebLogic Deployment Descriptor weblogic-ejb-jar.xml
<?xml version="1.0"?>
<!DOCTYPE weblogic-ejb-jar PUBLIC
'-//BEA Systems, Inc.//DTD WebLogic 6.0.0 EJB//EN'
'http://www.bea.com/servers/wls600/dtd/weblogic-ejb-jar.dtd'>
<weblogic-ejb-jar>
<weblogic-enterprise-bean>
<ejb-name>QueryDB</ejb-name>
<reference-descriptor>
<resource-description>
<res-ref-name>jdbc/styejbDB</res-ref-name>
<jndi-name>jdbc.styejbDB</jndi-name>
</resource-description>
<resource-env-description>
<res-env-ref-name>jdbc/styejbDB</res-env-ref-name>
<jndi-name>jdbc.styejbDB</jndi-name>
</resource-env-description>
</reference-descriptor>
<jndi-name>day09/QueryDBHome</jndi-name>
</weblogic-enterprise-bean>
</weblogic-ejb-jar>
Listing 9.7 The JBoss Deployment Descriptor jboss.xml
<?xml version="1.0" encoding="UTF-8"?>
<jboss>
<enterprise-beans>
<session>
<ejb-name>QueryDB</ejb-name>
<jndi-name>day09/QueryDBHome</jndi-name>
<resource-ref>
<res-ref-name>jdbc/styejbDB</res-ref-name>
<jndi-name>java:/DefaultDS</jndi-name>
</resource-ref>
<resource-env-ref>
<resource-env-ref-name>jdbc/styejbDB</resource-env-ref-name>
<jndi-name>java:/DefaultDS</jndi-name>
</resource-env-ref>
</session>
</enterprise-beans>
</jboss>
Because each application server has its own way of referencing its own resources in the JNDI services, your application needs to be written to avoid such dependency. In order to make your application portable, you need to use the <resource-env-ref> element in the standard deployment descriptor (ejb-jar.xml) to define a logical name, which is used by the application. Then you need to map this logical name to a <reference-descriptor> in your server-specific deployment descriptor, which is the physical reference to the JNDI name. The lines of code (shown in bold) in the preceding example demonstrate the use of such technique.
Configuring the DataSource and the Connection Pool
Before you run the example, you need first to create and configure the connection pool styejbPool. You also need to create the DataSource styejbDB for the connection pool and register it in the JNDI service. The accompanying day09/Readme.txt file explains this process for both WebLogic and JBoss servers.
Compile and Run the Example
This section describes the steps to comoile and run the example for both WebLogic Server and JBoss application servers.
To run the example in WebLogic Server, you must follow these steps:
-
Open a new command window.
Set up the environment for the appropriate application server, and then start the server. Run the accompanying script, found at the root of the sample directory, to set up an environment for either WebLogic Server or JBoss. The following are the scripts for WebLogic Server, (follow Figures 9.6, 9.7, and 9.8 to assist you in setting up the JDBC connection pool in the WebLogic Server environment):
Figure 9.6. Configuring the connection pool styejbPool in WebLogic Server.
Figure 9.7. Configuring a JDBC connection pool.
Figure 9.8. Configuring a JDBC connection pool.
-
Set up and start the PointBase database using the following steps:
c:\>cd c:\styejb\examples c:\styejb\examples>startPointbase.bat c:\styejb\examples>setEnvWebLogic.bat c:\styejb\examples>setupPointbase.bat
The above steps start PointBase Server and create the tables in a PointBase server named styejbPool.
-
Start WebLogic Server using the following steps:
c:\>cd c:\styejb\examples c:\styejb\examples>setEnvWebLogic.bat c:\styejb\examples>startWebLogic.bat
-
Open the WebLogic Administration Console using a Web browser such as Internet Explorer, and point to the URL http://localhost:7001/console. When prompted, enter the user name (system) and password you chose when you installed WebLogic Server (see Appendix A, “WebLogic Application Server 7.0”).
-
Create a connection pool for the Pointbase Database by doing the following:
In the left pane, expand Services > JDBC.
Click Connection Pools.
In the right pane, click Configure a New JDBC Connection Pool.
Enter these values:
Name: styejbPool
URL: jdbc:pointbase:server://localhost:9092/styejbPool
Driver: com.pointbase.jdbc.jdbcUniversalDriver
Properties: user=PBPUBLIC
Password: PBPUBLIC
Leave both the fields ACLName and Open String Password blank.
Click Create.
Figure 9.6 shows the corresponding screen shot.
Click the Connections tab and change the Maximum Capacity to 10 and click the Apply button.
Click the Targets tab.
Move myserver to the Chosen (right) column.
Figure 9.7 shows the corresponding screen shot.
Click Apply.
-
Create the JDBC data source.
Click the Home icon in the upper-right corner of the Administration Console.
In the left pane, expand Services > JDBC.
Click Tx Data Sources.
In the right pane, click Configure a New JDBC Data Source.
For Name, enter styejbDB
For JNDI Name, enter jdbc.styejbDB
For Pool Name, enter styejbPool
Figure 9.8 shows the corresponding screen shot.
Click Create.
Click the Targets tab.
Move myserver to the Chosen (right) column.
Click Apply.
To check the current list of data sources, click Home, and then click JDBC > TXData Sources.
-
Build the example for the appropriate application server. From the directory Day09, run the build script. This creates a subdirectory with the name build, which contains all the compiled code.
c:\>cd c:\styejb\examples c:\styejb\examples>setEnvWebLogic.bat c:\styejb\examples>cd day09 c:\styejb\examples\day09>buildWebLogic.bat
-
To run the example, use the appropriate script for each server. Set up the environment for the client in a new command window, and then use the run script in the Day09 directory:
c:\styejb\examples>setEnvWebLogic.bat c:\styejb\examples>cd day09 c:\styejb\examples\day09> runClientWebLogic.bat
Refer to the README.TXT file in the day09 directory to information to configure your JBoss datasource. The following is the server-side output of the example:
Day 9: Demonstrate the use of JDBC... initDB: Get initial context from the JNDI service... Lookup the DataSource as configured by administrator... Getting a Connection from the pool... Connection is obtained... Run some DDL statements: Trying to drop table STUDENTS... Table STUDENTS is created... Run some DML statements: Query ResultSet Metadata: STUDENT_ID, FIRST_NAME, LAST_NAME, ADDRESS 1, LILLIAN, GHALY, 15 DIAMOND ST, BOSTON, MA 3, SAM, LE, 12 APPLEBEE RD, LOS ANGELES, CA Query Database Metadata: Product Name: PointBase Driver Name: PointBase JDBC Driver Database Schemas: TABLE_SCHEM PBPUBLIC POINTBASE Printing All Data Tables for Schema: PBPUBLIC Table Name: COURSES ...
Note
If your output contains an exception such as Exception was thrown: Unable to resolve jdbc.styejbDB. Resolved: 'jdbc' Unresolved:'styejbDB', your connection pool and data source configuration are incorrect. You must correct the values as specified in the previous section, “Configuring the DataSource and Connection Pool.”
The following steps describe how to start JBoss server, run the sample client, and set up the database tables required for the remaining days:
-
Start JBoss server in a new command window as follows:
C:\>cd styejb\examples C:\styejb\examples>setEnvJBoss.bat C:\styejb\examples>startJBoss.bat
The JBoss server automatically starts the default HyperSonic database.
-
Build and run the example as follows:
c:\>cd c:\styejb\examples c:\styejb\examples>setEnvJBoss.bat c:\styejb\examples>cd day09 c:\styejb\examples\day09>buildJBoss.bat C:\styejb\examples\day09>runClientJBoss.bat
-
Set up the database tables required for the remaining days by doing the following:
In the command window that you used to build and run the example, run the following command:
c:\styejb\examples>setupHypersonic.batThis opens the HSQL Database Manager Connect window. Enter the value jdbc:hsqldb:hsql://localhost:1476 for the URL field. Leave default values for other fields. Figure 9.9 shows the correspon ding screen shot. Click the Ok button.
Figure 9.9. Connecting to the HyperSonic Database Manager.
Click the File\Open Script… menu item. When prompted, enter the filename c:\styejb\examples\styejbhs.sql.
Click the Execute button on the right. Figure 9.10 shows the corresponding screen shot.
Figure 9.10. Setting up the database tables in HyperSonic database.
-
Best Practices
Some of the best practices are already built-in the JDBC API itself, such as connection pooling, PreparedStatement, and batch updates. Still some of the widely known practices help when developing large and enterprise J2EE applications.
Some factors affecting the performance of your application with respect to database are independent of JDBC or any other access method. Examples of such factors are writing optimized queries, and tuning the database using caching of often-used resources. Both database tuning and query optimization are beyond the scope of this book. A simple example such as using the query "SELECT * from Student” rather than "SELECT id from Student" when you need only the student IDs, increases network traffic and the allocated resources to handle such ResultSet. Performing an UPDATE row operation rather than a DELETE on the row followed by an INSERT of new row, is another type of good practice.
With respect to JDBC, performance gain can be established by obtaining a Context to the DataSource in the initialization code. Use PreparedStatement to performing the same query repeatedly. CallableStatement also gives better performance than PreparedStatement and Statement when there is a requirement for single request to process multiple complex statements. It parses and stores the stored procedures in the database and optimizes the work at the database itself to improve its performance. Use stored procedure and triggers for large operations, but use them with care. Stored procedures are handled differently by database products. This has a negative affect on application portability and makes you rely on vendor-specific stored procedures.
Use batch updates to perform batches of SQL statements. Release all resources appropriately; that is, in the reverse order of how they were allocated. If possible, avoid transaction auto-commit, and control your transaction behavior.
Tuning your connection pool is a prime factor in the overall system performance. Both minimum and maximum size should be selected based on the expected user load. Another factor is that the Connection must be returned to the pool as soon as you are done working with it. Delaying the release of the connection has negative impact on an application’s scalability. Disabling auto-commit to control batch operations through a single transaction also helps improve application performance.
-
Summary
Today we covered JDBC as a standard and unified API to access many data sources in tabular forms, such as relational tables, flat files, and spreadsheets. J2EE applications and components use JDBC across all tiers. We examined the methods of connecting to a data source, and showed how to construct and implement SQL statements, and the optimized ways of handling them. We looked briefly at local transactions and stored procedures. Metadata of both the DBMS and the ResultSet was discussed, and we ended by giving a brief account of the best practices in dealing with database objects and queries using JDBC.
Tomorrow, you will learn how to develop a stateless session bean. You will see a full life cycle example by writing, compiling, deploying, and running the bean.
}
-
EJB In 21 Days ? 