Programming WebLogic JDBC
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
The following sections describe how to set up and use third-party JDBC drivers:
The following sections describe how to get a database connection using a third-party, Type 4 driver, such as the Oracle Thin Driver. BEA recommends you use DataSource objects and a JNDI lookup to establish your connection.
First, you create the data source using the Administration Console, then establish a connection using a JNDI Lookup.
To access the data source using JNDI, obtain a Context from the JNDI tree by providing the URL of your server, and then use that context object to perform a lookup using the DataSource Name.
For example, to access a DataSource called "myDataSource
" that is defined in the Administration Console:
Context ctx = null;
Hashtable ht = new Hashtable();
ht.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL,
"t3://hostname:port");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
ctx = new InitialContext(ht);
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myDataSource");
conn = ds.getConnection();
// You can now use the conn object to create
// Statements and retrieve result sets:
stmt = conn.createStatement();
stmt.execute("select * from someTable");
rs = stmt.getResultSet();
...
//Close JDBC objects as soon as possible
stmt.close();
stmt=null;
conn.close();
conn=null;
}
catch (Exception e) {
// a failure occurred
log message;
}
finally {
try {
ctx.close();
} catch (Exception e) {
log message; }
try {
if (rs != null) rs.close();
} catch (Exception e) {
log message; }
try {
if (stmt != null) stmt.close();
} catch (Exception e) {
log message; }
try {
if (conn != null) conn.close();
} catch (Exception e) {
log message; }
}
(Where hostname
is the name of the machine running your WebLogic Server and port
is the port number where that machine is listening for connection requests.)
In this example a Hashtable object is used to pass the parameters required for the JNDI lookup. There are other ways to perform a JNDI lookup. For more information, see Programming WebLogic JNDI.
Notice that the JNDI lookup is wrapped in a try/catch
block in order to catch a failed look up and also that the context is closed in a finally
block.
Note: BEA strongly discourages directly accessing a physical JDBC connection except for when it is absolutely required. See Getting a Physical Connection from a Data Source.
Standard practice is to cast a connection to the generic JDBC connection (a wrapped physical connection) provided by WebLogic Server. This allows the server instance to manage the connection for the connection pool, enable connection pool features, and maintain the quality of connections provided to applications. Occasionally, a DBMS vendor may provide extra non-standard JDBC-related classes that require direct access of the physical connection (the actual vendor JDBC connection). To directly access a physical connection in a connection pool, you must cast the connection using getVendorConnection .
The following sections provide information on getting a physical connection:
To get a physical database connection, you first get a connection from a connection pool as described in Using a JNDI Lookup to Obtain the Connection, then do one of the following:
getVendorConnection
) within a method that requires the physical connection.WLConnection
and call getVendorConnection
.Always limit direct access of physical database connections to vendor-specific calls. For all other situations, use the generic JDBC connection provided by WebLogic Server. Sample code to open a connection for vendor-specific calls is provided in Listing 5-1.
Listing 5-1 Code Sample to Open a Connection for Vendor-specific Calls
//Import this additional class and any vendor packages
//you may need.
import weblogic.jdbc.extensions.WLConnection
.
.
.
myJdbcMethod()
{
// Connections from a connection pool should always be
// method-level variables, never class or instance methods.
Connection conn = null;
try {
ctx = new InitialContext(ht);
// Look up the data source on the JNDI tree and request
// a connection.
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myDataSource");
// Always get a pooled connection in a try block where it is
// used completely and is closed if necessary in the finally
// block.
conn = ds.getConnection();
// You can now cast the
conn
object to a WLConnection
// interface and then get the underlying physical connection.
java.sql.Connection vendorConn =
((WLConnection)conn).getVendorConnection();
// do not close vendorConn
// You could also cast the vendorConn object to a vendor
// interface, such as:
// oracle.jdbc.OracleConnection vendorConn = (OracleConnection)
// ((WLConnection)conn).getVendorConnection()
// If you have a vendor-specific method that requires the
// physical connection, it is best not to obtain or retain
// the physical connection, but simply pass it implicitly
// where needed, eg: //vendor.special.methodNeedingConnection(((WLConnection)conn)).getVendorConnection());
When you are finished with your JDBC work, you should close the logical connection to get it back into the pool. When you are done with the physical connection:
You determine how a connection closes by setting the value of the Remove Infected Connections Enabled
property in the administration console. See the JDBC Data Source: Configuration: Connection Pool page in the Administration Console Help or see "JDBCConnectionPoolParamsBean" in the WebLogic Server MBean Reference for more details about these options
Note: The Remove Infected Connections Enabled
property applies only to applications that explicitly call getVendorConnection
.
Listing 5-2 Sample Code to Close a Connection for Vendor-specific Calls
// As soon as you are finished with vendor-specific calls,
// nullify the reference to the connection.
// Do not keep it or close it.
// Never use the vendor connection for generic JDBC.
// Use the logical (pooled) connection for standard JDBC.
vendorConn = null;
... do all the JDBC needed for the whole method...
// close the logical (pooled) connection to return it to
// the connection pool, and nullify the reference.
conn.close();
}
conn = null;
catch (Exception e)
{
// Handle the exception.
}
finally
{
// For safety, check whether the logical (pooled) connection
// was closed.
// Always close the logical (pooled) connection as the
// first step in the finally block.
When Remove infected Connections Enabled=false
(default value) and you close the logical connection, the server instance discards the underlying physical connection and creates a new connection to replace it. This action ensures that the pool can guarantee to the next user that they are the sole user of the pool connection. This configuration provides a simple and safe way to close a connection. However, there is a performance loss because:
Note: Use Remove infected Connections Enabled=false
only if you are sure that the exposed physical connection will never be retained or reused after the logical connection is closed.
When Remove infected Connections Enabled=false
and you close the logical connection, the server instance simply returns the physical connection to the connection pool for reuse. Although this configuration minimizes performance losses, the server instance does not guarantee the quality of the connection or to effectively manage the connection after the logical connection is closed. You must make sure that the connection is suitable for reuse by other applications before it is returned to the connection pool.
BEA strongly discourages using a physical connection instead of a logical connection from a connection pool. However, if you must use a physical connection, for example, to create a STRUCT, consider the following costs and limitations:
Some database vendors provide additional proprietary methods for working with data from a database that uses their DBMS. These methods extend the standard JDBC interfaces. In previous releases of WebLogic Server, only specific JDBC extensions for a few vendors were supported. The current release of WebLogic Server supports all extension methods exposed as a public interface in the vendor's JDBC driver.
If the driver vendor does not expose the methods you need in a public interface, you should submit a request to the vendor to expose the methods in a public interface. WebLogic Server does provide support for extension methods in the Oracle Thin Driver for ARRAYs, STRUCTs, and REFs, even though the extension methods are not exposed in a public interface. See Using Oracle Extensions with the Oracle Thin Driver.
In general, WebLogic Server supports using vendor extensions in server-side code. To use vendor extensions in client-side code, the object type or data type must be serializable. Exceptions to this are the following object types:
WebLogic Server handles de-serialization for these object types so they can be used in client-side code.
Note: There are interoperability limitations when using different versions of WebLogic Server clients and servers. See Support for Vendor Extensions Between Versions of WebLogic Server Clients and Servers.
To use the extension methods exposed in the JDBC driver, you must include these steps in your application code:
The following sections provide details in code examples. For information about specific extension methods for a particular JDBC driver, refer to the documentation from the JDBC driver vendor.
The following code examples use extension methods available in the Oracle Thin driver to illustrate how to use vendor extensions to JDBC. You can adapt these examples to fit methods exposed in your JDBC driver.
Import the interfaces from the JDBC driver used to create the connection in the data source. This example uses interfaces from the Oracle Thin Driver.
import java.sql.*;
import javax.sql.DataSource;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import oracle.jdbc.*;
// Import driver interfaces. The driver must be the same driver
// used to create the database connection in the data source.
Establish the database connection using JNDI, DataSource and data source objects. For information, see Using a JNDI Lookup to Obtain the Connection.
// Get a valid DataSource object for a data source.
// Here we assume that getDataSource() takes
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();
Now that you have the connection, you can cast it as a vendor connection. This example uses the OracleConnection interface from the Oracle Thin Driver.
orConn = (oracle.jdbc.OracleConnection)conn;
// This replaces the deprecated process of casting the connection
// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:
// orConn = (weblogic.jdbc.vendor.oracle.OracleConnection)conn;
The following code fragment shows how to use the Oracle Row Prefetch method available from the Oracle Thin driver.
// Cast to OracleConnection and retrieve the
// default row prefetch value for this connection.
int default_prefetch =
((oracle.jdbc.OracleConnection)conn).getDefaultRowPrefetch();
// This replaces the deprecated process of casting the connection
// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:
// ((weblogic.jdbc.vendor.oracle.OracleConnection)conn).
// getDefaultRowPrefetch();
System.out.println("Default row prefetch
is " + default_prefetch);
java.sql.Statement stmt = conn.createStatement();
// Cast to OracleStatement and set the row prefetch
// value for this statement. Note that this
// prefetch value applies to the connection between
// WebLogic Server and the database.
((oracle.jdbc.OracleStatement)stmt).setRowPrefetch(20);
// This replaces the deprecated process of casting the
// statement to a weblogic.jdbc.vendor.oracle.OracleStatement.
// For example:
// ((weblogic.jdbc.vendor.oracle.OracleStatement)stmt).
// setRowPrefetch(20);
// Perform a normal sql query and process the results...
String query = "select empno,ename from emp";
java.sql.ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
java.math.BigDecimal empno = rs.getBigDecimal(1);
String ename = rs.getString(2);
System.out.println(empno + "\t" + ename);
}
rs.close();
stmt.close();
conn.close();
conn = null;
}
For most extensions that Oracle provides, you can use the standard technique as described in Using Vendor Extensions to JDBC Interfaces. However, the Oracle Thin driver does not provide public interfaces for its extension methods in the following classes:
WebLogic Server provides its own interfaces to access the extension methods for those classes:
weblogic.jdbc.vendor.oracle.OracleArray
weblogic.jdbc.vendor.oracle.OracleStruct
weblogic.jdbc.vendor.oracle.OracleRef
weblogic.jdbc.vendor.oracle.OracleThinBlob
weblogic.jdbc.vendor.oracle.OracleThinClob
The following sections provide code samples for using the WebLogic Server interfaces for Oracle extensions. For a list of supported methods, see Tables of Oracle Extension Interfaces and Supported Methods. For more information, please refer to the Oracle documentation.
Note: You can use this process to use any of the WebLogic Server interfaces for Oracle extensions listed in the Tables of Oracle Extension Interfaces and Supported Methods. However, all but the interfaces listed above are deprecated and will be removed in a future release of WebLogic Server.
Please note the following limitations when using Oracle extensions to JDBC interfaces:
The following code examples show how to access the WebLogic Server interfaces for Oracle extensions that are not available as public interfaces, including interfaces for:
If you selected the option to install server examples with WebLogic Server, see the JDBC examples for more code examples, typically at WL_HOME
\samples\server\src\examples\jdbc
, where WL_HOME
is the folder where you installed WebLogic Server.
In your WebLogic Server server-side applications, you can materialize an Oracle Collection (a SQL ARRAY) in a result set or from a callable statement as a Java array.
To use ARRAYs in WebLogic Server applications:
The following sections provide more details for these actions.
Note: You can use ARRAYs in server-side applications only. You cannot use ARRAYs in remote client applications.
Import the Oracle interfaces used in this example. The OracleArray interface is counterpart to oracle.sql.ARRAY
and can be used in the same way as the Oracle interface when using the methods supported by WebLogic Server.
import java.sql.*;
import javax.sql.DataSource;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import weblogic.jdbc.vendor.oracle.*;
Establish the database connection using JNDI and DataSource objects. For information, see Using a JNDI Lookup to Obtain the Connection.
// Get a valid DataSource object.
// Here we assume that getDataSource() takes
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();
You can use the getArray()
methods for a callable statement or a result set to get a Java array. You can then use the array as a java.sql.array
to use standard java.sql.array
methods, or you can cast the array as a weblogic.jdbc.vendor.oracle.OracleArray
to use the Oracle extension methods for an array.
The following example shows how to get a java.sql.array
from a result set that contains an ARRAY. In the example, the query returns a result set that contains an object column—an ARRAY of test scores for a student.
try {
conn = getConnection(url);
stmt = conn.createStatement();
String sql = "select * from students";
//Get the result set
rs = stmt.executeQuery(sql);
while(rs.next()) {
BigDecimal id = rs.getBigDecimal("student_id");
String name = rs.getString("name");
log("ArraysDAO.getStudents() -- Id = "+id.toString()+", Student = "+name);
//Get the array from the result set
Array scoreArray = rs.getArray("test_scores");
String[] scores = (String[])scoreArray.getArray();
for (int i = 0; i < scores.length; i++) {
log(" Test"+(i+1)+" = "+scores[i]);
}
}
To update an ARRAY in a database, you can Follow these steps:
java.sql.Array
or a weblogic.jdbc.vendor.oracle.OracleArray
.setArray()
method for a prepared statement or a callable statement. For example:String sqlUpdate = "UPDATE SCOTT." + tableName + " SET col1 = ?";
conn = ds.getConnection();
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setArray(1, array);
pstmt.executeUpdate();
To use the Oracle extension methods for an ARRAY, you must first cast the array as a weblogic.jdbc.vendor.oracle.OracleArray. You can then make calls to the Oracle extension methods for ARRAYs. For example:
oracle.sql.Datum[] oracleArray = null;
oracleArray = ((weblogic.jdbc.vendor.oracle.OracleArray)scoreArray).getOracleArray();
String sqltype = null
sqltype = oracleArray.getSQLTypeName()
In your WebLogic Server applications, you can access and manipulate objects from an Oracle database. When you retrieve objects from an Oracle database, you can cast them as either custom Java objects or as STRUCTs (java.sql.struct
or weblogic.jdbc.vendor.oracle.OracleStruct
). A STRUCT is a loosely typed data type for structured data which takes the place of custom classes in your applications. The STRUCT interface in the JDBC API includes several methods for manipulating the attribute values in a STRUCT. Oracle extends the STRUCT interface with several additional methods. WebLogic Server implements all of the standard methods and most of the Oracle extensions.
Note: Please note the following limitations when using STRUCTs:
To use STRUCTs in WebLogic Server applications:
The following sections provide more details for steps 3 through 5.
To get a database object as a STRUCT, you can use a query to create a result set and then use the getObject
method to get the STRUCT from the result set. You then cast the STRUCT as a java.sql.Struct
so you can use the standard Java methods. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");
struct = (java.sql.Struct)(rs.getObject(2));
Object[] attrs = ((java.sql.Struct)struct).getAttributes();
WebLogic Server supports all of the JDBC API methods for STRUCTs:
Oracle supports the standard methods as well as the Oracle extensions. Therefore, when you cast a STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct
, you can use both the standard and extension methods.
To use the Oracle extension methods for a STRUCT, you must cast the java.sql.Struct
(or the original getObject
result) as a weblogic.jdbc.vendor.oracle.OracleStruct
. For example:
java.sql.Struct struct =
(weblogic.jdbc.vendor.oracle.OracleStruct)(rs.getObject(2));
WebLogic Server supports the following Oracle extensions:
To get the value for an individual attribute in a STRUCT, you can use the standard JDBC API methods getAttributes()
and getAttributes(java.util.Dictionary map)
, or you can use the Oracle extension method getOracleAttributes()
.
To use the standard method, you can create a result set, get a STRUCT from the result set, and then use the getAttributes()
method. The method returns an array of ordered attributes. You can assign the attributes from the STRUCT (object in the database) to an object in the application, including Java language types. You can then manipulate the attributes individually. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");
//The third column uses an object data type.
//Use getObject() to assign the object to an array of values.
struct = (java.sql.Struct)(rs.getObject(2));
Object[] attrs = ((java.sql.Struct)struct).getAttributes();
String address = attrs[1];
In the preceding example, the third column in the people
table uses an object data type. The example shows how to assign the results from the getObject
method to a Java object that contains an array of values, and then use individual values in the array as necessary.
You can also use the getAttributes(java.util.Dictionary map)
method to get the attributes from a STRUCT. When you use this method, you must provide a hash table to map the data types in the Oracle object to Java language data types. For example:
java.util.Hashtable map = new java.util.Hashtable();
map.put("NUMBER", Class.forName("java.lang.Integer"));
map.put("VARCHAR", Class.forName("java.lang.String"));
Object[] attrs = ((java.sql.Struct)struct).getAttributes(map);
String address = attrs[1];
You can also use the Oracle extension method getOracleAttributes()
to get the attributes for a STRUCT. You must first cast the STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct
. This method returns a datum array of oracle.sql.Datum
objects. For example:
oracle.sql.Datum[] attrs =
((weblogic.jdbc.vendor.oracle.OracleStruct)struct).getOracleAttributes();
oracle.sql.STRUCT address = (oracle.sql.STRUCT) attrs[1];
Object address_attrs[] = address.getAttributes();
The preceding example includes a nested STRUCT. That is, the second attribute in the datum array returned is another STRUCT.
To update an object in the database using a STRUCT, you can use the setObject
method in a prepared statement. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
ps = conn.prepareStatement ("UPDATE SCHEMA.people SET EMPLNAME = ?,
EMPID = ? where EMPID = 101");
ps.setString (1, "Smith");
ps.setObject (2, struct);
ps.executeUpdate();
WebLogic Server supports all three versions of the setObject
method.
STRUCTs are typically used to materialize database objects in your Java application in place of custom Java classes that map to the database objects. In WebLogic Server applications, you cannot create STRUCTs that transfer to the database. However, you can use statements to create objects in the database that you can then retrieve and manipulate in your application. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);
Note: You cannot create STRUCTs in your applications. You can only retrieve existing objects from a database and cast them as STRUCTs. To create STRUCT objects in your applications, you must use a non-standard Oracle STRUCT descriptor object, which is not supported in WebLogic Server.
To enhance the performance of your WebLogic Server applications that use STRUCTs, you can toggle automatic buffering with the setAutoBuffering(boolean)
method. When automatic buffering is set to true
, the weblogic.jdbc.vendor.oracle.OracleStruct
object keeps a local copy of all the attributes in the STRUCT in their converted form (materialized from SQL to Java language objects). When your application accesses the STRUCT again, the system does not have to convert the data again.
Note: Buffering the converted attributes my cause your application to use an excessive amount of memory. Consider potential memory usage when deciding to enable or disable automatic buffering.
The following example shows how to activate automatic buffering:
((weblogic.jdbc.vendor.oracle.OracleStruct)struct).setAutoBuffering(true);
You can also use the getAutoBuffering()
method to determine the automatic buffering mode.
A REF is a logical pointer to a row object. When you retrieve a REF, you are actually getting a pointer to a value in another table. The REF target must be a row in an object table. You can use a REF to examine or update the object it refers to. You can also change a REF so that it points to a different object of the same object type or assign it a null value.
Note: Please note the following limitations when using REFs:
To use REFs in WebLogic Server applications, follow these steps:
You can also create and update a REF in the database.
The following sections describe these steps 3 and 4 in greater detail.
To get a REF in an application, you can use a query to create a result set and then use the getRef
method to get the REF from the result set. You then cast the REF as a java.sql.Ref
so you can use the built-in Java method. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
//Cast as a java.sql.Ref and get REF
ref = (java.sql.Ref) rs.getRef(1);
Note that the WHERE clause in the preceding example uses dot notation to specify the attribute in the referenced object.
After you cast the REF as a java.sql.Ref
, you can use the Java API method getBaseTypeName
, the only JDBC 2.0 standard method for REFs.
When you get a REF, you actually get a pointer to a value in an object table. To get or manipulate REF values, you must use the Oracle extensions, which are only available when you cast the sql.java.Ref
as a weblogic.jdbc.vendor.oracle.OracleRef.
In order to use the Oracle extension methods for REFs, you must cast the REF as an Oracle REF. For example:
oracle.sql.StructDescriptor desc = ((weblogic.jdbc.vendor.oracle.OracleRef)ref).getDescriptor();
WebLogic Server supports the following Oracle extensions:
Oracle provides two versions of the getValue()
method—one that takes no parameters and one that requires a hash table for mapping return types. When you use either version of the getValue()
method to get the value of an attribute in a REF, the method returns a either a STRUCT or a Java object.
The example below shows how to use the getValue()
method without parameters. In this example, the REF is cast as an oracle.sql.STRUCT. You can then use the STRUCT methods to manipulate the value, as illustrated with the getAttributes()
method.
oracle.sql.STRUCT student1 =
(oracle.sql.STRUCT)((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue ();
Object attributes[] = student1.getAttributes();
You can also use the getValue(dictionary)
method to get the value for a REF. You must provide a hash table to map data types in each attribute of the REF to Java language data types. For example:
java.util.Hashtable map = new java.util.Hashtable();
map.put("VARCHAR", Class.forName("java.lang.String"));
map.put("NUMBER", Class.forName("java.lang.Integer"));
oracle.sql.STRUCT result = (oracle.sql.STRUCT)
((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue (map);
When you update a REF, you can do any of the following:
setValue(object)
method.To use the setValue(object)
method to update a REF value, you create an object with the new values for the REF, and then pass the object as a parameter of the setValue
method. For example:
STUDENT s1 = new STUDENT();
s1.setName("Terry Green");
s1.setAge(20);
((weblogic.jdbc.vendor.oracle.OracleRef)ref).setValue(s1);
When you update the value for a REF with the setValue(object)
method, you actually update the value in the table to which the REF points.
To update the location to which a REF points using a prepared statement, you can follow these basic steps:
try {
conn = ds.getConnection();
stmt = conn.createStatement();
//Get the REF.
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
ref = (java.sql.Ref) rs.getRef(1); //cast the REF as a java.sql.Ref
}
//Create and execute the prepared statement.
String sqlUpdate = "update t3 s2 set col = ? where s2.col.ob1=20";
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setRef(1, ref);
pstmt.executeUpdate();
To use a callable statement to update the location to which a REF points, you prepare the stored procedure, set any IN parameters and register any OUT parameters, and then execute the statement. The stored procedure updates the REF value, which is actually a location. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
ref1 = (java.sql.Ref) rs.getRef(1);
// Prepare the stored procedure
sql = "{call SP1 (?, ?)}";
cstmt = conn.prepareCall(sql);
// Set IN and register OUT params
cstmt.setRef(1, ref1);
cstmt.registerOutParameter(2, getRefType(), "USER.OB");
// Execute
cstmt.execute();
You cannot create REF objects in your JDBC application—you can only retrieve existing REF objects from the database. However, you can create a REF in the database using statements or prepared statements. For example:
conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);
cmd = "create table t2 (col ref ob)";
stmt.execute(cmd);
cmd = "insert into t2 select ref(p) from t1 where p.ob1=5";
stmt.execute(cmd);
The preceding example creates an object type (ob
), a table (t1
) of that object type, a table (t2
) with a REF column that can point to instances of ob
objects, and inserts a REF into the REF column. The REF points to a row in t1
where the value in the first column is 5
.
This section contains sample code that demonstrates how to access the OracleBlob interface. You can use the syntax of this example for the OracleBlob interface, when using methods supported by WebLogic Server. See Tables of Oracle Extension Interfaces and Supported Methods.
Note: When working with BLOBs and CLOBs (referred to as "LOBs"), you must take transaction boundaries into account; for example, direct all read/writes to a particular LOB within a transaction. For additional information, refer to Oracle documentation about "LOB Locators and Transaction Boundaries" at the Oracle Web site.
The BLOB Locator, or handle, is a reference to an Oracle Thin Driver BLOB:
String selectBlob = "select blobCol from myTable where blobKey = 666"
The following code presumes the Connection is already established:
ResultSet rs = null;
Statement myStatement = null;
java.sql.Blob myRegularBlob = null;
java.io.OutputStream os = null;
In this try catch block, you get the BLOB locator and access the Oracle BLOB extension.
try {
// get our BLOB locator..
myStatement = myConnect.createStatement();
rs = myStatement.executeQuery(selectBlob);
while (rs.next()) {
myRegularBlob = rs.getBlob("blobCol");
}
// Access the underlying Oracle extension functionality for
// writing. Cast to the OracleThinBlob interface to access
// the Oracle method.
os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream();
...
} catch (SQLException sqe) {
System.out.println("ERROR(general SQE): " +
sqe.getMessage());
}
Once you cast to the Oracle.ThinBlob
interface, you can access the BEA supported methods.
If you use a prepared statement to update a CLOB and the new value is shorter than the previous value, the CLOB will retain the characters that were not specifically replaced during the update. For example, if the current value of a CLOB is abcdefghij
and you update the CLOB using a prepared statement with zxyw
, the value in the CLOB is updated to zxywefghij
. To correct values updated with a prepared statement, you should use the dbms_lob.trim
procedure to remove the excess characters left after the update. See the Oracle documentation for more information about the dbms_lob.trim
procedure.
An Oracle Virtual Private Database (VPD) is an aggregation of server-enforced, application-defined fine-grained access control, combined with a secure application context in the Oracle 9i database server. To use VPDs in your WebLogic Server application, you would typically do the following:
Note: If you are using an XA-enabled version of the JDBC driver, you must set KeepXAConnTillTxComplete=true
. See "JDBC Data Source: Configuration: Connection Pool" in the Administration Console Online Help.
import weblogic.jdbc.extensions.WLConnection
// get a connection from a WLS JDBC data source
Connection conn = ds.getConnection();
// Get the underlying vendor connection object
oracle.jdbc.OracleConnection orConn = (oracle.jdbc.OracleConnection)
(((WLConnection)conn).getVendorConnection());
// Set CLIENT_IDENTIFIER (which will be accessible from
// USERENV naming context on the database server side)
orConn.setClientIdentifier(clientId);
/* perform application specific work, preferably using conn instead of orConn */
// clean up connection before returning to WLS JDBC data source
orConn.clearClientIdentifier(clientId);
// As soon as you are finished with vendor-specific calls,
// nullify the reference to the physical connection.
orConn = null;
// close the pooled connection
conn.close();
Note: This code uses an underlying physical connection from a pooled (logical) connection. See Getting a Physical Connection from a Data Source for usage guidelines.
WebLogic Server provides support for the oracle.jdbc.OracleConnection
.setClientIdentitfier
and oracle.jdbc.OracleConnection
.clearClientIndentifier
methods without using the underlying physical connection from a pooled connection. To use VPDs in your WebLogic Server application, you would typically do the following:
import weblogic.jdbc.vendor.oracle.OracleConnection;
// get a connection from a WLS JDBC data source
Connection conn = ds.getConnection();
// cast to the Oracle extension and set CLIENT_IDENTIFIER
// (which will be accessible from USERENV naming context on
// the database server side)
((weblogic.jdbc.vendor.oracle.OracleConnection)conn).setClientIdentifier(clientId);
/* perform application specific work */
// clean up connection before returning to WLS JDBC data source
((OracleConnection)conn).clearClientIdentifier(clientId);
// close the connection
conn.close();
Because the way WebLogic Server supports vendor JDBC extensions was changed in WebLogic Server 8.1, interoperability between versions of client and servers is affected.
When a WebLogic Server 8.1 or later client interacts with a WebLogic Server 7.0 or earlier server, Oracle extensions are not supported. When the client application tries to cast the JDBC objects to the Oracle extension interfaces, it will get a ClassCastException
. However, when a WebLogic Server 7.0 or earlier client interacts with a WebLogic Server 8.1 or later server, Oracle extensions are supported.
This applies to the following Oracle extension interfaces:
weblogic.jdbc.vendor.oracle.OracleConnection
weblogic.jdbc.vendor.oracle.OracleStatement
weblogic.jdbc.vendor.oracle.OraclePreparedStatement
weblogic.jdbc.vendor.oracle.OracleCallableStatement
weblogic.jdbc.vendor.oracle.OracleResultSet
weblogic.jdbc.vendor.oracle.OracleThinBlob
weblogic.jdbc.vendor.oracle.OracleThinClob
weblogic.jdbc.vendor.oracle.OracleArray
weblogic.jdbc.vendor.oracle.OracleRef
weblogic.jdbc.vendor.oracle.OracleStruct
Note: Standard JDBC interfaces are supported regardless of the client or server version.
In previous releases of WebLogic Server, only the JDBC extensions listed in the following tables were supported. The current release of WebLogic Server supports most extension methods exposed as a public interface in the vendor's JDBC driver. See Using Vendor Extensions to JDBC Interfaces for instructions for using vendor extensions. Because the new internal mechanism for supporting vendor extensions does not rely on the previous implementation, several interfaces are no longer needed and are deprecated. These interfaces will be removed in a future release of WebLogic Server. See Table 5-1. BEA encourages you to use the alternative interface listed in the table.
The interfaces listed in Table 5-2 are still valid because Oracle does not provide interfaces to access these extension methods.
The following tables describe the Oracle interfaces and supported methods you use with the Oracle Thin Driver (or another driver that supports these methods) to extend the standard JDBC (java.sql.*)
interfaces.
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
(This interface is deprecated. See |
|
![]() ![]() |
![]() |
![]() |