|
|
|
|
| |
JDBC (Java Database Connectivity)
JDBC provides standard interfaces for accessing relational database systems from Java. To use JDBC, you need a JDBC driver for the database system you want to access. BEA provides JDBC drivers for Oracle, Microsoft SQL Server, and Informix Dynamic Server. JDBC drivers for Cloudscape and Sybase SQL Server are bundled with WebLogic Server. And you can use any standard JDBC driver with WebLogic Server.
JDBC can be employed in two-tier and multitier configurations. In a two-tier configuration, a Java client loads the JDBC driver into its own JVM. If the JDBC driver is a type 2 driver-one that employs vendor-specific native client libraries-the client must have the database vendor's client libraries installed.
In a multitier configuration, WebLogic Server loads a JDBC driver into its JVM and establishes the connection to the database. Instead of loading the database-specific JDBC driver, the client application loads a WebLogic JDBC driver, which is a pure-Java, database-independent driver. A multitier configuration is composed of two two-tier connections: the WebLogic JDBC driver between the client and WebLogic Server, and the vendor-specific JDBC driver between WebLogic Server and the database.
The advantages to using multitier JDBC are found in the additional services that WebLogic Server provides.
First, some JDBC drivers require vendor-supplied native libraries. The BEA jDriver for Oracle JDBC driver for Oracle databases is one of these. Using the multitier configuration, the native libraries only have to be installed on the computer running WebLogic Server. Clients can use the pure-Java WebLogic JDBC driver instead.
Second, WebLogic Server allows you to create a JDBC connection pool. WebLogic Server creates a set of database connections in the pool. Clients then get a connection from the pool and return it when they are finished. Since the connections are pre-established, the client does not wait for the database connection to be made, a process that can be very time-consuming, especially for e-commerce applications where many clients execute short-lived queries against the database.
Third, a WebLogic Server cluster can provide load-balancing and failover for JDBC connection pools. You assign a DataSource name to a connection pool to register the connection pool in the WebLogic Server replicated naming tree. When clients request a connection from the WebLogic Server cluster, the cluster uses a load-balancing algorithm to select an available connection from one of the servers in the cluster. Even if you are not using a cluster now, you can write your applications to use a DataSource to that you can deploy them later on a cluster with no modifications.
WebLogic Server connection pools have several configurable administrative features. By configuring the maximum number of connections permitted in the pool, you can control the load on the database server. When the demand for connections is heavy, WebLogic Server can increase the number of connections in the pool. When the demand decreases, the additional connections can be dropped, shrinking the pool back to its minimum configuration. Connections can be automatically tested and refreshed to ensure that a client does not get a connection that has gone bad. If the database server goes down, connections are remade automatically when it recovers.
Multitier WebLogic JDBC Applications
WebLogic JDBC provides multitier JDBC services to its clients. The client application gets a JDBC connection from WebLogic Server, and WebLogic Server gets a JDBC connection to the back-end database. Before using WebLogic JDBC, all that is required is that a JDBC driver for the target database is installed on the computer running WebLogic Server and that its Java classes are in the WebLogic Server classpath.
Testing a Multitier JDBC Connection
BEA provides a simple way to test a multitier database connection. Start up WebLogic Server and use the WebLogic t3dbping utility to form a multitier connection to a database. t3dbping does not use use a connection pool; it just tests whether WebLogic Server can establish a two-tier JDBC connection to a database. It is useful to ensure that the JDBC driver is installed correctly before you set up a connection pool.
If you have a running database server and its JDBC driver installed on the WebLogic Server computer, you can try this with your own database. The example here uses the Cloudscape database. (An evaluation version of the Cloudscape database is included with WebLogic Server so you can try out the WebLogic Server database-dependent examples.)
The following example assumes that you installed WebLogic Server and have not altered the configuration for Cloudscape. If you have problems with Cloudscape, see Using the Cloudscape Database with WebLogic.
Here are the steps to test a multitier database connection to Cloudscape with t3dbping:
java utils.t3dbping t3://localhost:7001 "" "" ""
COM.cloudscape.core.JDBCDriver
jdbc:cloudscape:demo
If you are using your own database, type java utils.t3dbping to see examples for several database vendors.
t3dbping should display the following results:
Listing 6-1 t3dbping output
Success!!!
You can connect to the database in your WebLogic JDBC program using:
import java.sql.*;
import java.util.Properties;
import weblogic.common.*;
T3Client t3 = null;
Connection conn = null;
try {
t3 = new T3Client("t3://localhost:7001");
t3.connect();
Properties dbprops = new Properties();
dbprops.put("user", "");
dbprops.put("password", "");
dbprops.put("server", "");
Properties t3props = new Properties();
t3props.put("weblogic.t3.dbprops", dbprops);
t3props.put("weblogic.t3", t3);
t3props.put("weblogic.t3.driverClassName",
"COM.cloudscape.core.JDBCDriver");
t3props.put("weblogic.t3.driverURL",
"jdbc:cloudscape:demo");
Class.forName("weblogic.jdbc.t3.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:weblogic:t3", t3props);
// Do something with the database connection
// and then close and disconnect in try blocks
} finally {
try {conn.close();} catch (Exception e) {;}
try {t3.disconnect();} catch (Exception e) {;}
}
The output from the t3dbping utility includes code that you can use to develop a multitier JDBC Java program. (Note that this example code does not use a connection pool and therefore does not provide the benefits of connection pools, including clustered load-balancing and failover. The use of connection pools is discussed in the next section.) If you do not see these results, t3dbping displays a message that should help you diagnose the problem. Another useful debugging utility, dbping, lets you test the two-tier JDBC connection on the WebLogic Server computer to diagnose problems in the back-end connection. See Testing Connections for help with dbping.
The t3dbping command has several arguments:
Using a WebLogic JDBC Connection Pool
A WebLogic JDBC connection pool opens database connections when WebLogic Server starts up, if the pool is configured in the weblogic.properties file, or when the connection pool is created. The connections remain open as long as the connection pool is not destroyed. A client can "reserve" a connection from the pool, which gives the client sole access to the connection until it is returned to the pool. Creating a database connection is an expensive operation on most database systems, and using a connection pool can eliminate most of that expense.
There are three ways to create a connection pool:
Pools created with the WebLogic Console or with Java applications are not automatically recreated when WebLogic Server restarts. Dynamic connection pools can be useful for some applications that need a connection pool only while they are active. An application that creates a dynamic connection pool can also be set up as a WebLogic Server startup class, which has the same effect as defining the connection pool in the weblogic.properties file.
Defining a Connection Pool
A sample Cloudscape connection pool is defined (but commented out) in the weblogic.properties file included in the WebLogic Server distribution. Here are the properties that define the pool:
weblogic.jdbc.connectionPool.demoPool=\
url=jdbc:cloudscape:demo,\
driver=COM.cloudscape.core.JDBCDriver,\
initialCapacity=1,\
maxCapacity=2,\
capacityIncrement=1,\
props=user=none;password=none;server=none
#
# Add an ACL for the connection pool:
weblogic.allow.reserve.weblogic.jdbc.connectionPool.demoPool=\
everyone
The first property defines the connection pool, which is named "demoPool." The second property specifies which WebLogic users are allowed to reserve a connection from the pool. In this case, specifying the special user "everyone" means that any user can reserve a connection.
The url and driver arguments are familiar from the t3dbping example. The url, in the format specified by the JDBC driver vendor, allows the JDBC driver to locate the database. The driver is the full class name of the JDBC driver, which can be found in the documentation for the JDBC driver.
The initialCapacity, maxCapacity, and capacityIncrement arguments determine how many database connections the pool contains and when they are created. When the connection pool is first created, WebLogic Server creates initialCapacity connections. When all of the connections are in use and another one is requested, WebLogic Server creates capacityIncrement new connections and adds them to the pool. The pool will never have more than maxCapacity connections.
The props argument supplies properties that are passed to the DriverManager.getConnection() method when a connection is created.
When you create a connection pool, you should also define a DataSource for the pool, which makes it possible to use the WebLogic JDBC/RMI JDBC driver, which allows a Java client to get a pool connection with a JNDI lookup. To do this, add an additional property, such as this one:
weblogic.jdbc.TXDataSource.demoDataSource=demoPool
This property causes WebLogic Server to register the connection pool in the JNDI naming tree. The TXDataSource portion of the property name tells WebLogic Server to use JTS (Java Transaction Services), which provides transaction support on connections retrieved using this DataSource. If you do not need JTS, you can change this part of the property name to DataSource. If your applications use EJB or JMS, however, you should set up a TXDataSource.
The demoPool property does not use all of the available connection pool configuration options. You can find the entire set of connection pool properties in Using WebLogic JDBC.
You can view and change connection pool properties in the WebLogic Console. The Console displays usage statistics for pools, which are useful for tuning connection pool properties.
Using Connection Pools in JDBC Clients
Once a connection pool and DataSource are set up in WebLogic Server, clients get a connection using JNDI.
In this section, we create a simple Java client, booksPool.java. This example gets a JNDI InitialContext and looks up the DataSource that provides access to the connection pool. It then creates a Statement and executes some SQL commands on it, including some DDL (create table and drop table) and DML (insert and select). It shows how to process the results by getting a JDBC ResultSet object from the Statement. This example also uses a JDBC PreparedStatement to perform the SQL insert commands. A PreparedStatement can be more efficient on the database server for repeated commands because it allows the server to parse and optimize the statement once instead of each time it is executed.
Listing 6-2 booksPool.java
package examples.intro;
import java.sql.*;
import java.util.*;
import javax.naming.*;
public class booksPool {
public static void main(String argv[]) throws Exception {
Context ctx = null;
Hashtable ht = new Hashtable();
java.sql.Connection conn = null;
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://localhost:7001");
try {
ctx = new InitialContext(ht);
javax.sql.DataSource ds =
(javax.sql.DataSource) ctx.lookup("demoDataSource");
conn = ds.getConnection();
Statement s = conn.createStatement();
try {
// First, drop the "books" table, in case it
// already exists. If it does not, then this
// statement will get an warning that can be ignored.
System.out.println("Dropping table books.");
s.execute("drop table books");
}
catch ( SQLException se ) {;}
// Now, create the "books" table and insert some data.
System.out.println("Creating table books.");
s.execute("create table books (title varchar(40), " +
"author varchar(40), publisher varchar(40),"+
"isbn varchar(20))");
// Use a PreparedStatement to insert rows in the table.
System.out.println("Loading table.");
PreparedStatement ps = conn.prepareStatement(
"insert into books " +
"(title,author,publisher,isbn) " +
" values (?, ?, ?, ?)");
ps.setString(1, "Java in a Nutshell");
ps.setString(2, "David Flanagan");
ps.setString(3, "O'Reilly");
ps.setString(4, "1-56592-262-X");
ps.execute();
ps.setString(1, "Java Examples in a Nutshell");
// ps.setString(2, "David Flanagan");
// ps.setString(3, "O'Reilly");
ps.setString(4, "1-56592-371-5");
ps.execute();
ps.setString(1, "Design Patterns");
ps.setString(2, "Gamma, Helm, Johnson, Vlissides");
ps.setString(3, "Addison-Wesley");
ps.setString(4, "0-201-63361-2");
ps.execute();
System.out.println("Retrieving rows.");
s.execute("select * from books");
ResultSet rs = s.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("title") + "\n\t" +
rs.getString("author") + "\n\t" +
rs.getString("publisher") + "\n\t" +
rs.getString("isbn"));
}
rs.close();
s.close();
ps.close();
} finally {
try {conn.close();} catch (Exception e) {;}
try {ctx.close();} catch (Exception e) {;}
}
}
To run the booksPool example, follow these steps:
weblogic.jdbc.TXDataSource.demoDataSource=demoPool
Windows NT:
javac -d %CLIENT_CLASSES% booksPool.java
UNIX:
javac -d $CLIENT_CLASSES booksPool.java
java examples.intro.booksPool
More about WebLogic JDBC
There are many JDBC features and developer issues that could not be presented in this introduction. Some other JDBC features are illustrated in other sections of this document. For example, the SqlServlet.java example in the Servlets chapter shows how to use a JDBC connection pool in a server-side class, and how to access database metadata from a ResultSet.
Here are some online documents where you can find out more about JDBC and additional WebLogic JDBC features:
Documentation for WebLogic JDBC drivers:
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|