1 Using WebLogic JDBC in an Application

Learn how to use the WebLogic Server Administration Console to enable, configure, and monitor features of WebLogic Server, including JDBC generic data sources, multi data sources, or Active GridLink 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.

See Configuring JDBC Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server.

Getting a Database Connection from a DataSource Object

Learn how to request a database connection from a DataSource object.

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 WebLogic Server JNDI in Developing JNDI Applications 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

When using pooled connections in a data source, it is possible to execute DBMS-specific SQL code that will alter the database connection properties in a way which WebLogic Server and the JDBC driver will be unaware of. When the connection is returned to the pool, the characteristics of the connection may not be set back to a valid state.

Note:

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

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 pool, 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 pool 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 vendor-specific SQL code that alters the connection, you must set the connection back to an acceptable state before returning the connection to the pool.

Getting a Connection from an Application-Scoped Data Source

To get a connection from JDBC module packaged with an enterprise application, you look up the data source defined in the JDBC module in the local environment or in the JNDI tree and then request a connection from the data source or multi data source.

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