JDBC stands for Java Database Connectivity. JDBC is a Java interface for Java applications running SQL code against any database. It provides methods for querying and updating data in a database.
The Java Database Connectivity (JDBC) specifications:
- A mechanism for accessing and updating a database.
- Provides transparency to any kind of database vendor. Many vendors are supported (IBM DB2, Oracle, and Informix).
- Requires the use of a driver.
JDBC drivers are supplied by WebLogic Server or by your database vendor.
A simple connection-schematic overview:
In 1997, SUN Microsystems provided this interface with the release of their Java Development Kit (JDK 1.1).
With JDBC, an application can access virtually any database and run on any platform with a Java Virtual Machine (JVM). That is, with JDBC, it is not necessary to write one program to access a Sybase database, another to access an Oracle database, another to access an IBM DB2 database, and so on. You can write a single program using the JDBC API.
JDBC database connections use a driver mechanism that translates the JDBC calls to native database calls. Although most available drivers are fully written in Java (Type 4) and are thus platform-independent, some drivers (Type 2) use native libraries and are targeted to specific platforms. Native libraries are OS specific.
A Datasource object is used to establish connections. Although the Driver Manager can also be used to establish a connection, connecting through a Datasource object is the preferred method.
A Connection object controls the connection to the database. An application can alter the behavior of a connection by invoking the methods associated with this object. An application uses the connection object to create statements.
Statement, PreparedStatement, and CallableStatement objects are used for executing SQL statements. A PreparedStatement object is used when an application plans to reuse a statement multiple times. The application prepares the SQL it plans to use. Once prepared, the application can specify values for parameters in the prepared SQL statement. The statement can be executed multiple times with different parameter values specified for each execution. A CallableStatement is used to call stored procedures that return values. The CallableStatement has methods for retrieving the return values of the stored procedure.
A ResultSet object contains the results of a query. A ResultSet is returned to an application when a SQL query is executed by a statement object. The ResultSet object provides methods for iterating through the results of the query.
The WebLogic implementation of JDBC consists of the following component:
- Data sources:
- Enable database connectivity using a dynamic pool of reusable connections
- Are to be managed by the application server
- Can be obtained by applications from the WebLogic Server's JNDI tree
- Use a dynamic pool of reusable database connections
WebLogic Server can manage your database connectivity through JDBC data sources. Each data source that you configure contains a pool of database connections that are created when the data source instance is created when it is deployed or targeted, or at server startup. The connection pool can grow or shrink dynamically to accommodate the demand.
Applications look up a data source on the Java Naming and Directory Interface (JNDI) tree or in the local application context (java:comp/env), depending on how you configure and deploy the object, and then request a database connection. When finished with the connection, the application uses the close operation on the connection, which simply returns the connection to the connection pool in the data source.
WebLogic Server data sources allow connection information, such as the JDBC driver, the database location (URL), and the username and password to be managed and maintained in a single location, without requiring the application to worry about these details.
Datasource configuration is stored in a XML file at<DOMAIN_HOME>/config/jdbc.
All WebLogic JDBC module files must end with the -jdbc.xml suffix, such as examples-demo-jdbc.xml. They have a reference tag in the config.xml.
The WebLogic Type 4 JDBC drivers are installed with Oracle WebLogic Server in the<WL_HOME>/server/lib folder. Driver class files are included in the manifest classpath in weblogic.jar, so the drivers are automatically added to your classpath on the server.
The WebLogic Type 4 JDBC drivers are installed by default when you perform a complete installation of Oracle WebLogic Server. If you choose a custom installation, ensure that the WebLogic JDBC Drivers checkbox is selected. If this option is deselected, the drivers are not installed.
Oracle WebLogic Server opens JDBC connections to the database during the WebLogic startup process and adds the connections to the pool. This is faster than creating a new connection on demand. The size of the pool is dynamic and can be fine-tuned.
Usually, making a DBMS connection is a very slow process. When Oracle WebLogic Server starts, connections from the connection pools are opened and are available to all clients. When a client closes a connection from a connection pool, the connection is returned to the pool and is available for other clients; the connection itself is not closed. There is little cost in opening and closing pool connections. The alternative is for application code to create its own JDBC connections as needed. A DBMS runs faster when it can use dedicated connections than when it has to handle every individual connection attempt at runtime.
Connection information, such as the JDBC driver class name, the database location (URL), and the username and password can be managed in one location using the Administration Console. Application developers can obtain a connection without having to worry about these details.
Limiting the number of DBMS connections is also important if you have a licensing limitation for DBMS connections or a resource concern.
The first thing an administrator will encounter is the creation of a Data Source. This could be done in the Administration Console.
- Select Data Sources in the Domain Structure:
- Click on New.
- Fill in the details like Name and JNDI Name. JNDI name should be something similar to
jdbc/<datasourcename>.
- Select which driver is needed. For products like the Oracle SOA Suite, you often need an XA driver.
- Provide the name and the TCP port of the database, and the database server name. It will ask your DBA whether it requires an Instance or Service connection.
In more complicated architectures, databases are located in another tier (usually called a Data Tier) than your WebLogic Server (usually the Application Tier). Contact your network administrator if the firewall route and port are set from WebLogic to the database. The user schema which you specify in your JDBC should be created in the database. If username or password is incorrect, or it's not there, you will probably get an ORA-01017 error.
Provide database account username and password.
Now, you can test the configuration. When you're done, complete the configuration by pressing the Finish button.
These settings could be important to set, but always monitor your JDBC first before you make any changes, and look at their behavior. All these settings can have a huge impact, so always be careful.
Here are some of these settings explained:
- Initial Capacity: This is the number of physical connections to create when deploying the connection pool.
- Maximum Capacity: This is the maximum number of physical connections that this connection pool can contain. For optimal performance, set the value of Initial Capacity equal to the value for Maximum Capacity. Be aware that this disables the dynamic resizing.
- Capacity Increment: When there are no more available physical connections to satisfy connection requests, Oracle WebLogic Server creates this number of additional physical connections and adds them to the connection pool up to the maximum capacity.
- Test Frequency: This is the time in seconds that Oracle WebLogic Server tests the unused connections. This requires that you specify a Test Table Name. In an Oracle database, you could use DUAL for this purpose.
After you create a JDBC Data Source and target it to one or more servers, you can monitor it in the Administration Console. Locate and select your new Data Source and click on Monitoring | Statistics tab. Statistics are displayed for each deployed instance of the Data Source. Optionally, click Customize this table to change the columns displayed in the Statistics table. For example, some of the available columns (not displayed by default) include:
- Active Connections Current Count: The number of connections currently in use by applications
- Active Connections Average Count: The average number of active connections from the time that the data source was deployed
- Connections Total Count: The cumulative total number of database connections created in this Data Source from the time the Data Source was deployed
- Current Capacity: The current count of JDBC connections in the connection pool in the Data Source
- Highest Num Available: The highest number of database connections that were available at any time in this instance of the Data Source from the time the Data Source was deployed
- Waiting for Connection High Count: The highest number of application requests concurrently waiting for a connection from this instance of the Data Source
