7 Getting a Physical Connection from a Data Source

This chapter describes how to directly access a physical connection from a data source in WebLogic Server 10.3.6. Oracle strongly discourages directly accessing a physical JDBC connection except for when it is absolutely required.

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 provides 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.

Note:

Oracle also provides another mechanism to access a physical connection getVendorConnectionSafe. This mechanism also returns the underlying physical connection (the vendor connection) from a pooled database connection (a logical connection). However, when the connection is closed, it is returned to the pool, independent of the setting of Remove Infected Connections Enabled. For more information, see getVendorConnectionSafe.

This chapter includes the following sections:

Note:

Oracle strongly discourages directly accessing a physical JDBC connection except for when it is absolutely required.

Opening a Connection

To get a physical database connection, you first get a connection from a connection pool, then do one of the following:

  • Implicitly pass the physical connection (using getVendorConnection) within a method that requires the physical connection.

  • Cast the connection as a 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 below.

Example 7-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()); 

Closing a Connection

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:

  • Close any objects you have obtained from the connection.

  • Do not close the physical connection. Set the physical connection to null.

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 Oracle WebLogic Server Administration Console Help or see "JDBCConnectionPoolParamsBean" in the Oracle 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.

Example 7-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. 
   if (conn != null) try {conn.close();} catch (Exception ignore){} 
 } 
} 

Remove Infected Connections Enabled is True

When Remove infected Connections Enabled=true (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:

  • The physical connection is replaced with a new database connection in the connection pool, which uses resources on both the application server and the database server.

  • The statement cache for the original connection is closed and a new cache is opened for the new connection. Therefore, the performance gains from using the statement cache are lost.

Remove Infected Connections Enabled is False

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.

Limitations for Using a Physical Connection

Oracle 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:

  • The physical connection can only be used in server-side code.

  • When you use a physical connection, you lose all of the connection management benefits that WebLogic Server offer, such as error handling and statement caching.

  • You should use the physical connection only for the vendor-specific methods or classes that require it. Do not use the physical connection for generic JDBC, such as creating statements or transactional calls.