HOME | SITE MAP | SEARCH | CONTACT | GLOSSARY | PDF FILES | WHAT'S NEW
DEVELOPING APPLICATIONS | TABLE OF CONTENTS | NEXT TOPIC | INDEX
|
|
|
BEA WebLogic Enterprise 4.2 Developer Center |
|
HOME | SITE MAP | SEARCH | CONTACT | GLOSSARY | PDF FILES | WHAT'S NEW |
||
|
DEVELOPING APPLICATIONS | TABLE OF CONTENTS | NEXT TOPIC | INDEX |
||
This chapter covers general guidelines for using the jdbcKona drivers and some vendor-specific notes on each driver. Included at the end of this chapter is a summary of the steps you take, including sample code, to use a JDBC driver in a WebLogic Enterprise Java application.
Note:
The jdbcKona drivers are based on JDBC 1.22
The jdbcKona drivers include both Type 2 and Type 4 drivers. The Type 2 drivers (for Oracle) employ client libraries supplied by the database vendors. The Type 4 drivers (for the Microsoft SQL Server) are 100% pure Java; they connect to the database server at the wire level without vendor-supplied client libraries.
The following table lists the platforms supported by the jdbcKona drivers.
Be sure to add the jdbcKona JAR file, which applies to both jdbcKona drivers, to your environment. You can do this by appending the following to your On Solaris Systems:
On NT Systems:
The jdbcKona/Oracle (Type 2) driver calls native libraries that are supplied with the driver. The UNIX libraries (shared object files) are in the The following table lists the names of the driver files included with the WLE Java system.
jdbcKona/Oracle
For the jdbcKona/Oracle driver, you also need the vendor-supplied libraries for the database.
You need the following components to connect to a DBMS using a jdbcKona driver:
Platforms Supported by the jdbcKona Drivers
Adding the jdbcKona JAR File to Your CLASSPATH
CLASSPATH system environment variable:
$TUXDIR/udataobj/java/jdbc/jdbcKona.jar
$TUXDIR\udataobj\java\jdbc\jdbcKona.jar
jdbcKona/Oracle Shared Libraries and Dynamic Link Libraries
$TUXDIR/lib directory. The Windows DLL files are included in the WLE Java software kit in the $TUXDIR\bin directory.
JDBC Driver
Windows NT/95
UNIX
weblogicoci33.dll
libweblogicoci33.so
Requirements for Making a Connection to a Database Management System (DBMS)
The Sun Microsystems, Inc. JDBC specification includes SQL Extensions, also called SQL Escape Syntax. All jdbcKona drivers support Extended SQL. Extended SQL provides access to common SQL extensions in a way that is portable between DBMSs.
For example, the function to extract the day name from a date is not defined by the SQL standards. For Oracle, the SQL is:
Using Extended SQL, you can retrieve the day name for both DBMSs, as follows:
The following is an example that demonstrates several features of Extended SQL:
Extended SQL is delimited with curly braces ( The following is Extended SQL used as part of a The following example shows that you can nest extended SQL expressions:
You can retrieve lists of supported Extended SQL functions from a Refer to Chapter 11 of the JDBC 1.2 specification at the Sun Microsystems, Inc. Web site for a description of Extended SQL.
For the complete set of JDBC API documentation, see the following Web site:
The following packages, classes, interfaces, and WebLogic extensions compose the JDBC API:
The jdbcKona drivers provide extensions to JDBC for certain database-specific enhancements. The jdbcKona drivers have the following extended classes:
For more information about these extensions, see Chapter 4, "Extensions to the JDBC API."
This section describes the following steps involved in implementing a simple WLE Java application that uses a jdbcKona driver to connect to a DBMS:
Support for JDBC Extended SQL
select to_char(date_column, 'DAY') from table_with_dates
select {fn dayname(date_column)} from table_with_datesString insert=
"-- This SQL includes comments and JDBC extended SQL syntax. \n" +
"insert into date_table values( {fn now()}, -- current time \n" +
" {d '1997-05-24'}, -- a date \n" +
" {t '10:30:29' }, -- a time \n" +
" {ts '1997-05-24 10:30:29.123'}, -- a timestamp \n" +
" '{string data with { or } will not be altered') \n" +
"-- Also note that you can safely include { and } in comments or \n" +
"-- string data.";
Statement stmt = conn.createStatement();
stmt.executeUpdate(query);{}) to differentiate it from common SQL. Comments are preceded by two hyphens, and are ended by a newline character (\n). The entire Extended SQL sequence, including comments, SQL, and Extended SQL, is placed within double quotes and is passed to the execute method of a Statement object.
CallableStatement object:
CallableStatement cstmt =
conn.prepareCall("{ ? = call func_squareInt(?)}");select {fn dayname({fn now()})}DatabaseMetaData object. The following example shows how to list all the functions a JDBC driver supports:
DatabaseMetaData md = conn.getMetaData();
System.out.println("Numeric functions: " + md.getNumericFunctions());
System.out.println("\nString functions: " + md.getStringFunctions());
System.out.println("\nTime/date functions: " + md.getTimeDateFunctions());
System.out.println("\nSystem functions: " + md.getSystemFunctions());
conn.close();The JDBC API, with WebLogic Extensions
http://www.weblogic.com/docs/classdocs/packages.html#jdbc
Package java.sql
Package java.math
Class java.lang.Object
Interface java.sql.CallableStatement
(extends java.sql.PreparedStatement)
Interface java.sql.Connection
Interface java.sql.DatabaseMetaData
Class java.util.Date
Class java.sql.Date
Class java.sql.Time
Class java.sql.Timestamp
Class java.util.Dictionary
Class java.util.Hashtable
(implements java.lang.Cloneable)
Class java.util.Properties
Interface java.sql.Driver
Class java.sql.DriverManager
Class java.sql.DriverPropertyInfo
Class java.lang.Math
Class java.lang.Number
Class java.math.BigDecimal
Class java.math.BigInteger
Interface java.sql.PreparedStatement
(extends java.sql.Statement)
Interface java.sql.ResultSet
Interface java.sql.ResultSetMetaData
Interface java.sql.Statement
Class java.lang.Throwable
Class java.lang.Exception
Class java.sql.SQLException
Class java.sql.SQLWarning
Class java.sql.DataTruncation
Class java.sql.Types
Class weblogic.jdbc.oci.Connection
(implements java.sql.Connection)
Class weblogic.jdbc.oci.Statement
(implements java.sql.Statement)
Class weblogic.jdbc.oci.PreparedStatement
Class weblogic.jdbc.oci.CallableStatement
(implements java.sql.CallableStatement)Class weblogic.jdbc.oci.CallableStatement
Class weblogic.jdbc.oci.Connection
Class weblogic.jdbc.oci.Statement Implementing a WLE Java Application Using the jdbcKona Drivers
Many of the steps described in this section include code snippets from a comprehensive code example that is provided at the end of this chapter.
For database-specific details on implementing WLE Java applications using the jdbcKona drivers, see Chapter 2, "Using the jdbcKona/Oracle Driver," and Chapter 3, "Using the jdbcKona/ MSSQLServer4 Driver."
The classes that you import into your WLE Java server application that uses a jdbcKona driver should include:
The jdbcKona drivers implement the You do not import the jdbcKona driver class; instead, you load the driver inside the application. This allows you to select an appropriate driver at runtime. You can even decide after the program is compiled what DBMS to connect to.
Included in the WLE Java software is the latest version of the JDBC API class files. Make sure you do not have any earlier versions of the You need to import the In the following example, a The value for the server property may be vendor-specific; in this example, it is the V2 alias of an Oracle database running over TCP. You may also add the server name to the URL (see the next section) instead of setting it with the In general, to a connect to a DBMS, you need to perform the following steps:
Importing Packages
import java.sql.*;
import java.util.Properties;java.sql interface. You write your WLE Java application using the java.sql classes; the java.sql.DriverManager maps the jdbcKona driver to the java.sql classes.
java.sql classes in your CLASSPATH.
java.util.Properties class only if you use a Properties object to set parameters for connecting to the DBMS.
Setting Properties for Connecting to the DBMS
java.util.Properties object sets the parameters for connecting to the DBMS. There are other ways of passing these parameters to the DBMS that do not require a Properties object, as in the following snippet:
Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");java.util.Properties object.
Connecting to the DBMS
The most efficient way to load the jdbcKona driver is to invoke the Class.forName().newInstance method with the name of the driver class. This loads and registers the jdbcKona driver, as in the following example for jdbcKona/Oracle:
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Note that both steps describe the jdbcKona driver, but in different formats. The full pathname for the driver is period-separated, while the URL is colon-separated. The following table lists the class paths and URLs for the jdbcKona drivers:
Additional information required to form a database connection varies by DBMS vendor and by whether the jdbcKona driver is of Type 2 or Type 4. There are also a variety of methods for specifying this information in your program.
For full details about the jdbcKona drivers, refer to Chapter 2, "Using the jdbcKona/Oracle Driver," and Chapter 3, "Using the jdbcKona/ MSSQLServer4 Driver." For a complete code example, see "Implementing a WLE Java Application Using the jdbcKona Drivers" on page 1-6.
The connection to the DBMS is handled by the jdbcKona driver. You use both the class name of the driver (in dot-notation) and the URL of the driver (with colons as separators). Class names are case sensitive.
The Class.forName().newInstance method loads the driver and registers the driver with the DriverManager object.
Note:
The Sun Microsystems, Inc. JDBC API Reference for the java.sql.Driver interface recommends simply invoking Class.forName("driver-class") to load the driver.
The connection is created with the DriverManager.getConnection method, which takes as arguments the URL of the driver and a Properties object, as in the following snippet. The URL is not case sensitive.
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
conn.setAutoCommit(false);The default transaction mode for JDBC assumes autocommit to be true. Setting autocommit to false improves performance.
The Connection object is an important part of the application. The Connection class has constructors for many fundamental database objects that you will use throughout the application. In the examples that follow, you will see the Connection object conn used repeatedly.
Connecting to the database completes the initial portion of a WLE Java application, which will be very much the same for any application.
Invoke the close method on the Connection object as soon as you finish working with the object, usually at the end of a class.
The most fundamental task in database access is to retrieve data. With a jdbcKona driver, retrieving data is a three-step process:
Statement object to send an SQL query to the DBMS.
In the following code snippet, we execute a simple query on the Employee table (alias "emp") and display data from three of the columns. We also access and display metadata about the table from which the data was retrieved. Note that we close the Statement at the end.
Statement stmt = conn.createStatement();
stmt.execute("select * from emp");
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("empid") + " - " +
rs.getString("name") + " - " +
rs.getString("dept"));
}
ResultSetMetaData md = rs.getMetaData();
System.out.println("Number of columns: " + md.getColumnCount());
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.println("Column Name: " + md.getColumnName(i));
System.out.println("Nullable: " + md.isNullable(i));
System.out.println("Precision: " + md.getPrecision(i));
System.out.println("Scale: " + md.getScale(i));
System.out.println("Size: " + md.getColumnDisplaySize(i));
System.out.println("Column Type: " + md.getColumnType(i));
System.out.println("Column Type Name: "+ md.getColumnTypeName(i));
System.out.println("");
}
stmt.close();
The following snippet shows three common database tasks: inserting, updating, and deleting records from a database table. We use a JDBC PreparedStatement object for these operations; we create the PreparedStatement object, then execute the object and close it.
A PreparedStatement object (subclassed from JDBC Statement) allows you to execute the same SQL over and over again with different values. PreparedStatement objects use the JDBC "?" syntax.
String inssql = "insert into emp(empid, name, dept) values (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(inssql);
for (int i = 0; i < 100; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Person " + i);
pstmt.setInt(3, i);
pstmt.execute():
}
pstmt.close();
We also use a PreparedStatement object to update records. In the following code snippet, we add the value of the counter "i" to the current value of the "dept" field.
String updsql = "update emp set dept = dept + ? where empid = ?";
PreparedStatement pstmt2 = conn.prepareStatement(updsql);
for (int i = 0; i < 100; i++) {
pstmt2.setInt(1, i);
pstmt2.setInt(2, i);
pstmt2.execute();
}
pstmt2.close();
Finally, we use a PreparedStatement object to delete the records that we added and then updated, as in the following snippet:
String delsql = "delete from emp where empid = ?";
PreparedStatement pstmt3 = conn.prepareStatement(delsql);
for (int i = 0; i < 100; i++) {
pstmt3.setInt(1, i);
pstmt3.execute();
}
pstmt3.close();
You can use a jdbcKona driver to create, use, and drop stored procedures and functions. First, we execute a series of Statement objects to drop a set of stored procedures and functions from the database, as in the following code snippet:
Statement stmt = conn.createStatement();
try {stmt.execute("drop procedure proc_squareInt");}
catch (SQLException e) {;}
try {stmt.execute("drop procedure func_squareInt");}
catch (SQLException e) {;}
try {stmt.execute("drop procedure proc_getresults");}
catch (SQLException e) {;}
stmt.close();
We use a JDBC Statement object to create a stored procedure or function, and then we use a JDBC CallableStatement object (subclassed from the Statement object) with the JDBC "?" syntax to set IN and OUT parameters. For information about doing this with the jdbcKona/Oracle driver, see Chapter 2, "Using the jdbcKona/Oracle Driver."
The first two code snippets that follow use the jdbcKona/Oracle driver. Note that Oracle does not natively support binding to "?" values in an SQL statement. Instead, it uses ":1", ":2", and so forth. You can use either syntax in your SQL with the jdbcKona/Oracle driver.
Stored procedure input parameters are mapped to JDBC IN parameters, using the CallableStatement.setxxx methods, such as setInt(), and the "?" syntax of the JDBC PreparedStatement object. Stored procedure output parameters are mapped to JDBC OUT parameters, using the CallableStatement.registerOutParameter methods and the "?" syntax of the JDBC PreparedStatement object. A parameter may be both IN and OUT, which requires both a setxxx() and a registerOutParameter() invocation to be made on the same parameter number.
In the following code snippet, we use a JDBC Statement object to create an Oracle stored procedure; then we execute the stored procedure with a CallableStatement object. We use the registerOutParameter method to set an output parameter for the squared value.
Statement stmt1 = conn.createStatement();
stmt1.execute("CREATE OR REPLACE PROCEDURE proc_squareInt " +
"(field1 IN OUT INTEGER, field2 OUT INTEGER) IS " +
"BEGIN field2 := field1 * field1; field1 := " +
"field1 * field1; END proc_squareInt;");
stmt1.close();
// Native Oracle SQL is commented out here
// String sql = "BEGIN proc_squareInt(?, ?); END;";
// This is the correct syntax as specified by JDBC
String sql = "{call proc_squareInt(?, ?)}";
CallableStatement cstmt1 = conn.prepareCall(sql);
// Register out parameters
cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
for (int i = 0; i < 5; i++) {
cstmt1.setInt(1, i);
cstmt1.execute();
System.out.println(i + " " + cstmt1.getInt(1) +
" " + cstmt1.getInt(2));
}
cstmt1.close();
In the following code snippet, we use similar code to create and execute a stored function that squares an integer.
Statement stmt2 = conn.createStatement();
stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt " +
"(field1 IN INTEGER) RETURN INTEGER IS " +
"BEGIN return field1 * field1; " +
"END func_squareInt;");
stmt2.close();
// Native Oracle SQL is commented out here
// sql = "BEGIN ? := func_squareInt(?); END;";
// This is the correct syntax specified by JDBC
sql = "{ ? = call func_squareInt(?)}";
CallableStatement cstmt2 = conn.prepareCall(sql);
cstmt2.registerOutParameter(1, Types.INTEGER);
for (int i = 0; i < 5; i++) {
cstmt2.setInt(2, i);
cstmt2.execute();
System.out.println(i + " " + cstmt2.getInt(1) +
" " + cstmt2.getInt(2));
}
cstmt2.close();
Close Statement, ResultSet, Connection, and other such objects with their close methods after you have finished using them. Closing these objects releases resources on the remote DBMS and within your application. When you use one object to construct another, close the objects in the reverse order in which they were created. For example:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from empno");
(process the ResultSet)
rs.close();
stmt.close();
Always close the java.sql.Connection as well, usually as one of the last steps in your program. Every connection should be closed, even if a login fails. An Oracle connection will cause a system failure (such as a segment violation) when the finalizer thread attempts to close a connection that you have inadvertently left open. If you do not close connections to log out of the database, you may also exceed the maximum number of database logins. Once a connection is closed, all of the objects created in its context become unusable.
There are occasions on which you will want to invoke the commit method to commit changes you have made to the database before you close the connection.
When autocommit is set to true (the default JDBC transaction mode), each SQL statement is its own transaction. After we created the Connection object for these examples, however, we set autocommit to false; in this mode, the Connection object always has an implicit transaction associated with it, and any invocation to the rollback or commit methods will end the current transaction and start a new one. Invoking commit() before close() ensures that all of the transactions are completed before closing the connection.
Just as you close Statement, PreparedStatement, and CallableStatement objects when you have finished working with them, always invoke the close method on the connection as final cleanup in your application, in a try {} block, and catch exceptions and deal with them appropriately. The final two lines of the example include an invocation to commit() and then close() to close the connection, as in the following snippet:
conn.commit();
conn.close();
The following is a sample implementation to give you an overall idea of the structure for a WLE Java application that uses a jdbcKona driver to access a DBMS. The code example shown here includes retrieving data, displaying metadata, inserting, deleting, and updating data, and stored procedures and functions. Note the explicit invocations to close() for each JDBC-related object, and note also that we close the connection itself in a finally {} block, with the invocation to close() wrapped in a try {} block.
import java.sql.*;
import java.util.Properties;
import weblogic.common.*;
public class test {
static int i;
Statement stmt = null;
public static void main(String[] argv) {
try {
Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
}
catch (Exception e)
e.printStackTrace();
}
try {
// This will improve performance in Oracle
// You'll need an explicit commit() call later
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.execute("select * from emp");
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("empid") + " - " +
rs.getString("name") + " - " +
rs.getString("dept"));
}
ResultSetMetaData md = rs.getMetaData();
System.out.println("Number of Columns: " + md.getColumnCount());
for (i = 1; i <= md.getColumnCount(); i++) {
System.out.println("Column Name: " + md.getColumnName(i));
System.out.println("Nullable: " + md.isNullable(i));
System.out.println("Precision: " + md.getPrecision(i));
System.out.println("Scale: " + md.getScale(i));
System.out.println("Size: " + md.getColumnDisplaySize(i));
System.out.println("Column Type: " + md.getColumnType(i));
System.out.println("Column Type Name: "+ md.getColumnTypeName(i));
System.out.println("");
}
rs.close();
stmt.close();
Statement stmtdrop = conn.createStatement();
try {stmtdrop.execute("drop procedure proc_squareInt");}
catch (SQLException e) {;}
try {stmtdrop.execute("drop procedure func_squareInt"); }
catch (SQLException e) {;}
try {stmtdrop.execute("drop procedure proc_getresults"); }
catch (SQLException e) {;}
stmtdrop.close();
// Create a stored procedure
Statement stmt1 = conn.createStatement();
stmt1.execute("CREATE OR REPLACE PROCEDURE proc_squareInt " +
"(field1 IN OUT INTEGER, " +
"field2 OUT INTEGER) IS " +
"BEGIN field2 := field1 * field1; " +
"field1 := field1 * field1; " +
"END proc_squareInt;");
stmt1.close();
CallableStatement cstmt1 =
conn.prepareCall("BEGIN proc_squareInt(?, ?); END;");
cstmt1.registerOutParameter(2, Types.INTEGER);
for (i = 0; i < 100; i++) {
cstmt1.setInt(1, i);
cstmt1.execute();
System.out.println(i + " " + cstmt1.getInt(1) +
" " + cstmt1.getInt(2));
}
cstmt1.close();
// Create a stored function
Statement stmt2 = conn.createStatement();
stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt " +
"(field1 IN INTEGER) RETURN INTEGER IS " +
"BEGIN return field1 * field1; END func_squareInt;");
stmt2.close();
CallableStatement cstmt2 =
conn.prepareCall("BEGIN ? := func_squareInt(?); END;");
cstmt2.registerOutParameter(1, Types.INTEGER);
for (i = 0; i < 100; i++) {
cstmt2.setInt(2, i);
cstmt2.execute();
System.out.println(i + " " + cstmt2.getInt(1) +
" " + cstmt2.getInt(2));
}
cstmt2.close();
// Insert 100 records
System.out.println("Inserting 100 records...");
String inssql = "insert into emp(empid, name, dept) values (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(inssql);
for (i = 0; i < 100; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Person " + i);
pstmt.setInt(3, i);
pstmt.execute();
}
pstmt.close();
// Update 100 records
System.out.println("Updating 100 records...");
String updsql = "update emp set dept = dept + ? where empid = ?";
PreparedStatement pstmt2 = conn.prepareStatement(updsql);
for (i = 0; i < 100; i++) {
pstmt2.setInt(1, i);
pstmt2.setInt(2, i);
pstmt2.execute();
}
pstmt2.close();
// Delete 100 records
System.out.println("Deleting 100 records...");
String delsql = "delete from emp where empid = ?";
PreparedStatement pstmt3 = conn.prepareStatement(delsql);
for (i = 0; i < 100; i++) {
pstmt3.setInt(1, i);
pstmt3.execute();
}
pstmt3.close();
conn.commit();
}
catch (Exception e) {
// Deal with failures appropriately
}
finally {
try {conn.close();}
catch (Exception e) {
// Catch and deal with exception
}
}
}
}
