2 Using WebLogic JDBC in an Application

This chapter describes how you use the WebLogic Server 10.3.6 Administration Console to enable, configure, and monitor features of WebLogic Server, including JDBC data sources and multi data sources. You can do the same tasks programmatically using the JMX API and the WebLogic Scripting Tool (WLST). After configuring JDBC connectivity components, you can use them in your applications.

This chapter includes the following sections:

For more information about configuring JDBC data sources and multi data sources, see Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

Getting a Database Connection from a DataSource Object

The following sections provide details about requesting a database connection from a DataSource object - either a data source or a multi data source:

Importing Packages to Access DataSource Objects

To use the DataSource objects in your applications, import the following classes in your client code:

import java.sql.*;
import java.util.*;
import javax.naming.*;

Obtaining a Client Connection Using a DataSource

To obtain a connection for a JDBC client, use a Java Naming and Directory Interface (JNDI) lookup to locate the DataSource object, as shown in this code fragment.

Note:

When using a JDBC connection in a client-side application, the exact same JDBC driver classes must be in the CLASSPATH on both the server and the client. If the driver classes do not match, you may see java.rmi.UnmarshalException exceptions.
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; }
}

(Substitute the correct hostname and port number for your WebLogic Server.)

Note:

The code above uses one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming JNDI for Oracle WebLogic Server.

Possible Exceptions When a Connection Request Fails

The weblogic.jdbc.extensions package includes the following exceptions that can be thrown when an application request fails. Each exception extends java.sql.SQLException.

  • ConnectionDeadSQLException—generated when an application request to get a connection fails because the connection test on the reserved connection failed. This typically happens when the database server is unavailable.

  • ConnectionUnavailableSQLException—generated when an application request to get a connection fails because there are currently no connections available in the pool to be allocated. This is a transient failure, and is generated if all connections in the pool are currently in use. It can also be thrown when connections are unavailable because they are being tested.

  • PoolDisabledSQLException—generated when an application request to get a connection fails because the JDBC Data Source has been administratively disabled.

  • PoolLimitSQLException—generated when an application request to get a connection fails due to a configured threshold of the data source, such as HighestNumWaiters, ConnectionReserveTimeoutSeconds, and so forth.

  • PoolPermissionsSQLException—generated when an application request to get a connection fails a (security) authentication or authorization check.

Pooled Connection Limitation

Note:

Certain Oracle JDBC extensions, and possibly other non-standard methods available from other drivers may durably alter a connection's behavior in a way that future users of the pooled connection will inherit. WebLogic Server attempts to protect connections against some types of these calls when possible.

When using pooled connections in a data source, it is possible to execute DBMS-specific SQL code that will alter the database connection properties and that WebLogic Server and the JDBC driver will be unaware of. When the connection is returned to the data source, the characteristics of the connection may not be set back to a valid state. For example, with a Sybase DBMS, if you use a statement such as "set rowcount 3 select * from y", the connection will only ever return a maximum of 3 rows from any subsequent query on this connection. When the connection is returned to the data source and then reused, the next user of the connection will still only get 3 rows returned, even if the table being selected from has 500 rows.

In most cases, there is standard JDBC code that can accomplish the same result. In this example, you could use setMaxRows() instead of set rowcount. Oracle recommends that you use the standard JDBC code instead of the DBMS-specific SQL code. When you use standard JDBC calls to alter the connection, WebLogic Server returns the connection to a standard state when the connection is returned to the data source.

If you use DBMS-specific SQL code that alters the connection, you must set the connection back to an acceptable state before returning the connection to the data source.

Getting a Connection from an Application-Scoped Data Source

To get a connection from an application-scoped data source, see "Getting a Database Connection from a Packaged JDBC Module" in Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.