All for Joomla All for Webmasters

SysAdmin

Apache JDBC Connectivity

84 0
Want create site? Find Free WordPress Themes and plugins.

Java DataBase Connectivity (JDBC) is a programming interface for accessing Relational Databases, its operation is based on the transmission and execution of Structured Query Language (SQL) on the database server. JDBC is based on a Call-Level Interface (CLI) to an engine that processes SQL.


The JDBC engine submits SQL query statements to the remote SQL processing engine and the SQL processing engine returns the result of the query in a set of data called a result set. A result set is zero or more rows of data.

JDBC operations are designed to do the following

  • Take the JDBC API calls and transform them into an SQL query
  • Submit that query to the SQL processing engine on the RDBMS
  • Retrieve the result set that is returned from the query and transform it into a Java-accessible data structure

The following is a typical JDBC connection

  • Obtain a connection to the remote database server
  • Create and prepare an SQL statement for execution
  • Execute the SQL statement
  • Obtain the returned result set and work on it
  • Disconnect from the remote database

The same JDBC APIs can be used to connect to a number of different databases Oracle, MySQL, etc

JDBC Versions

Currently we are at JDBC version 4 which supports all the previous versions and addresses some features that make development easier, improve connection pooling, expose physical database row IDs for developers and introduce a new XML data type to JDBC. Some older databases may require an older version so check your documentation.

Here is a summary of the four different JDBC types

  • TYPE I – These are basic data access adapters, they adapt another data access mechanism (such as OBDC) to JDBC, they completely rely on the other data access mechanism.
  • TYPE II – These drivers are partially written in native data access languages (typical C or C++)
  • TYPE III – These drivers are pure Java drivers on the client side, which gives them the portability benefit of Java, however they rely on a middleware engine running externally to operate.
  • TYPE IV – These are 100% Java and talk directly to the network protocols supported by the RDBMS, this results in giving the highest performance and are the most portable. Most JDBC drivers are now type IV.

When then application makes a connection to a database it is CPU-, Memory-, execution time-intensive operation. It involves multiple layers of software and the transmission and receipt of network data. Many JSP requests may access the database and as you saw above this can be expensive in creating new database connections. To reduce this load on the server we use connection pooling.

Connection pooling reduces expensive session connection times by creating a pool of physical connections when the system starts up. When an application requires a connection, one of these physical connections is provided. When the application is finished the logical connection is disconnected but the physical connection is returned to the pool for reuse ready of the next application to use.


pool manager creates the initial physical connections, manages the distribution of the physical connections to the Web application in the form of a logical connection, returns any closed logical connections to the pool and handles any errors.

JDBC 4 corrected a pooling problem if there were network errors and the physical connects had disconnected requiring a restart of the server, it provides a way for the pool manager to ask a connection if it is still valid for this to work properly, you will require

  • The JDBC driver to implement this JDBC 4 feature, enabling a connection’s validity to be queried
  • The application server pool manager to support this feature – to query connection validity regularly to avoid having bad connections remaining in the pool.

Tomcat and JDBC

Tomcat 6 provides JDBC 3 support and backward compatibility, Tomcat provides the following JDBC features

  • Application server-managed database connection pools – Tomcat 6 uses Jakarta Commons Database Connection Pooling (DBCP) to provide container-managed connection pooling and ResourceParams
  • Using the JNDI-API to look up data source within an application server – Tomcat 6 can emulate JNDI Web applications running under it.
  • Ease of migration to the connector architecture – Tomcat 6 decoupled architecture for access to JDBC data sources (through JNDI lookup) is a first step in the migration toward the JCA Connector-based architecture

JNDI Emulation and Pooling

Tomcat enables running Web applications to do the following

  • Access JDBC data sources using standard JNDI lookup
  • Use connection pooling value-added service

The below diagram shows how Tomcat uses JNDI to lookup data sources

  1. A Web application obtains a JNDI initialContext from Tomcat, it then performs a lookup on the resource (JDBC data source) by name
  2. Tomcat handles the JNDI lookup by consulting the configuration files (context.xmlserver.xml and web.xml) to determine which JDBC driver to use for a data source. Tomcat can also use database connection pooling (DBCP) to pool connections made; the connections obtained are logical connections


Remember that Tomcat is only emulating JNDI-compatible directory services. Using JNDI resources in Tomcat, provides Web applications access to JDBC connections, the following steps are need to setup a JNDI resource

  1. Add a <Resource> tag in the <Context> element (WEB-INF/context.xml) of the Web application or in a <DefaultContext> subelement of the <Host> element (server.xml) to configure the JNDI resource.
  2. Ensure that a <resource-ref> element is defined, corresponding to the <Resource> from step1 (web.xml).
  3. Use JNDI calls in the application code to lookup the JDBC data source
<Resource> Component Attributes
Attribute Description
name Create a JNDI resource that is accessible from the context (logical name). java:comp/env is added on for all Tomcat-managed contexts, use this code inside the application code to lookup the resource.
auth Specifies if the Tomcat container does the authentication on behalf of the the application
type The type of resource that will be returned during this lookup
maxActive Maximum number of active connections in the connection pool ( 0 means unlimited)
maxIdle Number of idle connections in the pool before they are evicted (-1 means unlimited)
maxWait Maximum number of milliseconds that the manager will wait for a database connection to respond, before throwing an exception (-1 means wait indefinitely)
url The JDBC URL to access the database
username The username for the JDBC connection
password The password for the user above for the JDBC connection
driverClassName The driver used to access the Database (Oracle, MySQL, etc)
Example
Oracle <Resource name=”jdbc/oracledb”
auth=”Container”
type=”javax.sql.DataSource”
driverClassName=”oracle.jdbc.OracleDriver”
url=”jdbc:oracle:thin:@localhost:1521:D01″
username=”tomcat”
password=”tomcat”
maxActive=”20″
maxIdle=”30″
maxWait=”-1″
/>
MySQL <Resource name=”jdbc/mysqldb”
auth=”Container”
type=”javax.sql.DataSource”
driverClassName=”com.mysql.jdbc.Driver”
url=”jdbc:mysql://localhost:3306/D01?autoReconnect=true”
username=”tomcat”
password=”tomcat”
maxActive=”20″
maxIdle=”30″
maxWait=”-1″
/>

Tomcat 6 uses a data source factory to create the data source, it uses the Jakarta Commons DBCP to supply a data source factory and implement connection pooling.

Complete Example

Now for a complete example, I will be using Oracle but you can use MySQL or another database

Database setup Download Oracle and the necessary Oracle JAR file (classes12.jar) which I placed in the lib directory so that all web applications can access it.
Note: I have a Oracle web topic that discusses installation, configuring, administrating, etc
Create Oracle table I created a Tomcat schema and Tomcat User for this example
create table employee (
employeeid varchar2(10) NOT NULL primary key,
name varchar2(30) NOT NULL,
phone varchar2(15) NOT NULL,
department varchar2(15) NOT NULL,
password varchar2(15) NOT NULL
);
Load some data into the table insert into employee values (‘0001’, ‘Arthur Askey’, ‘12345’, ‘Comedy’, ‘Hello Playmates’);
insert into employee values (‘0002’, ‘George Formby’, ‘23456’, ‘Comedy’, ‘Turned out Nice’);
insert into employee values (‘0003’, ‘Bob Hope’, ‘34567’, ‘Comedy’, ‘Oh My Lord’);
insert into employee values (‘0004’, ‘Norman Wisdom’, ‘45678’, ‘Comedy’, ‘Mister Grimsdale’);
JNDI Resource I created a new Web application called jdbc in webapps

Add the below webapps/jdbc/META-INF/context.xml file

<Resource name=”jdbc/oracledb
auth=”Container”
type=”javax.sql.DataSource”
driverClassName=”oracle.jdbc.OracleDriver”
url=”jdbc.oracle:thin:@localhost:1521:D01
username=”tomcat”
password=”tomcat”
maxActive=”20″
maxIdle=”30″
maxWait=”-1″
/>

Note: I am presuming that the database is on the localhost and you have a user tomcat with a password of tomcat setup, if not then change the url to reflect your environment

Add the Resource Reference Add the below in the webapps/jdbc/WEB-INF/web.xml file
<resource-ref>
<res-ref-name>jdbc/oracledb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
JSP page Place the below in a webapps/jdbc called jdbcTest.jsp

<%@ taglib uri=”http://java.sun.com/jsp/jstl/sql” prefix=”sql” %>
<%@ taglib uri=”http://java.sun.com/jsp/jstl/core” prefix=”c” %>
<%@ page errorPage=”errorpg.jsp” %>
<html>
<head>
    <sql:query var=”employees” dataSource=”jdbc/oracledb”>
select * from employee
</sql:query>

  </head>
<body>
<h1>JDBC JNDI Resource Test</h1>
<table width=’600′ border=’1′>
<tr>
<th align=’left’>Employee ID</th>
<th align=’left’>Name</th>
<th align=’left’>Department</th>
</tr>
<c:forEach var=”employee” items=”${employees.rows}”>
<tr>
<td> ${employee.employeeid}</td>
<td> ${employee.name} </td>
<td> ${employee.department} </td>
</tr>
</c:forEach>
</table>
</body>
</html>

Test the page Restart Tomcat, then test the JDBC connection
http://localhost/jdbc/jdbcTest.jsp

There is another Connection Pool Manager called “c3p0” which is available but I am not going to discuss it here but leave it to you to find out about this Connection Pool Manager on the internet.

Did you find apk for android? You can find new Free Android Games and apps.

Leave A Reply