Java Database Connectivity

Java DataBase Connectivity, commonly referred to as JDBC, is an API for

the Java programming language that defines how a client may access a
database. It provides methods for querying and updating data in a database.
JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge
enables connections to any ODBC-accessible data source in the JVM host
environment.

JDBC is a collection of classes and interfaces which help a java developer to

establishing connection with any DataBase.

History and implementation

Sun Microsystems released JDBC as part of JDK 1.1 on February 19, 1997.[1] It has since
formed part of the Java Standard Edition.

Functionality
JDBC allows multiple implementations to exist and be used by the same application. The
API provides a mechanism for dynamically loading the correct Java packages and
registering them with the JDBC Driver Manager. The Driver Manager is used as a
connection factory for creating JDBC connections.

JDBC connections support creating and executing statements. These may be update
statements such as SQL's CREATE, INSERT, UPDATE and DELETE, or they may be
query statements such as SELECT. Additionally, stored procedures may be invoked
through a JDBC connection. JDBC represents statements using one of the following
classes:

 Statement – the statement is sent to the database server each and every time.
 PreparedStatement – the statement is cached and then the execution path is pre
determined on the database server allowing it to be executed multiple times in an
efficient manner.
 CallableStatement – used for executing stored procedures on the database.
JDBC Versions
1). The JDBC 1.0 API.

2). The JDBC 1.2 API.

3). The JDBC 2.0 Optional Package API.

4). The JDBC 2.1 core API.

5) The JDBC 3.0 API.

6) The JDBC 4.0 API.

Features of JDBC 1.0 API

The JDBC 1.0 API was the first officially JDBC API launched consists of the following
java classes and interfaces that you can open connections to particular databases.

This version includes a completely redesigned administration console with an enhanced

graphical interface to manage and monitor distributed virtual databases.

Features of JDBC 1.2 API

1). It supports Updatabale ResultSets.

2). The DatabaseMetaData code has been refactored to provide more transparency with
regard to the underlying database engine.

3) New pass through schedulers for increased performance.

Features of The JDBC 2.0 Optional Pacakage API

1). The use of DataSource interface for making a connection.

2). Use of JNDI to specify and obtain database connections.

3). It allows us to use Pooled connections, that is we can reuse the connections.

4). In this version the distrbuted transactions is possible.

5). It provides a way of handling and passing data using Rowset technology.
Features of the JDBC 2.1 core API.

1). Scroll forward and backward in a result set or has the ability to move to a specific
row.

2). Instead of using SQL commands, we can make updates to a database tables using
methods in the Java programming language

3). We can use multiple SQL statements in a a database as a unit, or batch.

4). It uses the SQL3 datatypes as column values. SQL3 types are Blob, Clob, Array,
Structured type, Ref.

5). Increased support for storing persistent objects in the java programming language.

6). Supports for time zones in Date, Time, and Timestamp values.

7). Full precision for java.math.BigDecimal values.

Features of JDBC 3.0 API

1). Reusabilty of prepared statements by connection pools.

2). In this version there is number of properties defined for the

ConnectionPoolDataSource. These properties can be used to describe how the
PooledConnection objects created by DataSource objects should be pooled.

3) A new concept has been added to this API is of savepoints.

4). Retrieval of parameter metadata.

5). It has added a means of retrieving values from columns containing automatically
generated values.

6). Added a new data type i.e. java.sql.BOOLEAN.

7). Passing parameters to CallableStatement.

8). The data in the Blob and Clob can be altered.

9). DatabaseMetaData API has been added.

Features of JDBC 4.0 :

1). Auto- loading of JDBC driver class.

2). Connection management enhancements.

3.) Support for RowId SAL type.

4). SQL exception handling enhancements.

5). DataSet implementation of SQl using Annotations.

6). SQL XML support JDBC driver

JDBC driver

A JDBC driver is a software component enabling a Java application to interact with a

database.[1] JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and
OLE DB providers.

To connect with individual databases, JDBC (the Java Database Connectivity API)
requires drivers for each database. The JDBC driver gives out the connection to the
database and implements the protocol for transferring the query and result between client
and database.

JDBC technology drivers fit into one of four categories

Type 1 Driver - JDBC-ODBC bridge

Schematic of the JDBC-ODBC bridge

The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver
implementation that employs the ODBC driver to connect to the database. The driver
converts JDBC method calls into ODBC function calls.

The driver is platform-dependent as it makes use of ODBC which in turn depends on

native libraries of the underlying operating system the JVM is running upon. Also, use of
this driver leads to other installation dependencies; for example, ODBC must be installed
on the computer having the driver and the database must support an ODBC driver. The
use of this driver is discouraged if the alternative of a pure-Java driver is available. The
other implication is that any application using a type 1 driver is non-portable given the
binding between the driver and platform. This technology isn't suitable for a high-
transaction environment. Type 1 drivers also don't support the complete Java command
set and are limited by the functionality of the ODBC driver.
Functions
 Translates query obtained by JDBC into corresponding ODBC query, which is
then handled by the ODBC driver.
 Sun provides a JDBC-ODBC Bridge driver. sun.jdbc.odbc.JdbcOdbcDriver. This
driver is native code and not Java, and is closed source.
 Client -> JDBC Driver -> ODBC Driver -> Database

Advantages
 Easy to connect.
 Directly connected to the database.

Disadvantages
 Performance overhead since the calls have to go through the jdbc Overhead
bridge to the ODBC driver, then to the native db connectivity interface.
 The ODBC driver needs to be installed on the client machine.
 Considering the client-side software needed, this is not suitable for applets.
 Compared to other driver types it's slow.
 The Sun driver has a known issue with character encodings and Microsoft Access
databases. Microsoft Access may use an encoding that is not correctly translated by
the driver, leading to the replacement in strings of, for example, accented characters
by question marks. One workaround is to avoid reading strings directly, but rather to
read the raw bytes and then translate these into a string, specifying the correct source
encoding:

Type 2 Driver - Native-API Driver specification

Schematic of the Native API driver

The JDBC type 2 driver, also known as the Native-API driver, is a database driver
implementation that uses the client-side libraries of the database. The driver converts
JDBC method calls into native calls of the database API.

The type 2 driver is not written entirely in Java as it interfaces with non-Java code that
makes the final database calls. The driver is compiled for use with the particular
operating system. For platform interoperability, the Type 4 driver, being a full-Java
implementation, is preferred over this driver.

 The vendor client library needs to be installed on the client machine.

 Not all databases have a client side library
 This driver is platform dependent
 This driver supports all java applications except Applets
Type 3 Driver - Network-Protocol Driver

Schematic of the Network Protocol driver

The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware, is
a database driver implementation which makes use of a middle tier between the calling
program and the database. The middle-tier (application server) converts JDBC calls
directly or indirectly into the vendor-specific database protocol.

This differs from the type 4 driver in that the protocol conversion logic resides not at the
client, but in the middle-tier. Like type 4 drivers, the type 3 driver is written entirely in
Java. The same driver can be used for multiple databases. It depends on the number of
databases the middleware has been configured to support. The type 3 driver is platform-
independent as the platform-related differences are taken care by the middleware. Also,
making use of the middleware provides additional advantages of security and firewall
access.
Functions
 Follows a three tier communication approach.
 Can interface to multiple databases - Not vendor specific.
 The JDBC Client driver written in java, communicates with a middleware-net-
server using a database independent protocol, and then this net server translates this
request into database commands for that database.
 Thus the client driver to middleware communication is database independent.
 Client -> JDBC Driver -> Network-protocol driver -> Middleware-Net Server ->
Any Database,...

Advantages
 Since the communication between client and the middleware server is database
independent, there is no need for the vendor db library on the client machine. Also
the client to middleware need not be changed for a new database.
 The Middleware Server (which can be a full fledged J2EE Application server) can
provide typical middleware services like caching (connections, query results, and so
on), load balancing, logging, auditing etc.
 Eg. for the above include jdbc driver features in Weblogic.
o Can be used in internet since there is no client side software needed.
o At client side a single driver can handle any database. (It works provided
the middleware supports that database!)

Disadvantages
 Requires database-specific coding to be done in the middle tier.
 An extra layer added may result in a time-bottleneck. But typically this is
overcome by providing efficient middleware services
Type 4 Driver - Native-Protocol Driver

Schematic of the Native-Protocol driver

The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a
database driver implementation that converts JDBC calls directly into a vendor-specific
database protocol.Therefore it is called a THIN driver.

Written completely in Java, type 4 drivers are thus platform independent. They install
inside the Java Virtual Machine of the client. This provides better performance than the
type 1 and type 2 drivers as it does not have the overhead of conversion of calls into
ODBC or database API calls. Unlike the type 3 drivers, it does not need associated
software to work.

As the database protocol is vendor-specific, the JDBC client requires separate drivers,
usually vendor-supplied, to connect to different types of databases.
Functions
 Type 4 drivers, coded entirely in Java, communicate directly with a vendor's
database, usually through socket connections. No translation or middleware layers are
required, improving performance.
 The driver converts JDBC calls into the vendor-specific database protocol so that
client applications can communicate directly with the database server.
 Completely implemented in Java to achieve platform independence.
 This type includes (for example) the widely-used Oracle thin driver -
oracle.jdbc.driver.OracleDriver which connects using a format configuration of
jdbc:oracle:thin:@URL
 Client -> Native-protocol JDBC Driver -> database server.

Advantages
 These drivers don't translate the requests into an intermediary format (such as
ODBC), nor do they need a middleware layer to service requests. This can enhance
performance considerably.
 The JVM can manage all aspects of the application-to-database connection; this
can facilitate debugging.
 Provides a way to manage copies of the database for each user.

Disadvantages
 Drivers are database dependent.

Seven Basic Steps in Using JDBC

1. Load the driver
2. Define the Connection URL
3. Establish the Connection
4. Create a Statement object
5. Execute a query
6. Process the results
7. Close the connection

Connection

This is an interface in java.sql that specifies connection

with specific database like MYSQL,MS-Access ,Oracle
ETC and java files.

Class.forName(String driver)
This method is static . It is used to load the jdbc driver
class.

Class.forName(“com.somejdbcvendor.TheirJdbcDriver)

DriverManager

Is is a class of java.sql Package that control a set of

JDBC drivers . Each driver has to be register with this
class.

The JDBC classes are contained in the Java package java.sql and javax.sql.

Package java.sql
Provides the API for accessing and processing data stored in a data source (usually a
relational database) using the Java programming language.
TM

Package javax.sql
Provides the API for server side data source access and processing from the Java TM

programming language.

Simple Java Program for Conectivity

import java.sql.*;
class Myapp1
{
public static void main(String s[])
{
try
{
Connection conn;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:kukreti","","");
System.out.println("Connection Successful");
conn.close();
}
catch(ClassNotFoundException e)
{

e.printStackTrace();
}
catch(SQLException w)
{
w.printStackTrace();

}
}
}
Java Program for Retrieving Record from Database

import java.sql.*;
class Myapp2
{
public static void main(String s[])
{
int mcode,msal;
String mname,mdesig;
try
{
Connection conn;
Statement stmt;
ResultSet rs;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:kukreti","","");
stmt=conn.createStatement();
rs=stmt.executeQuery("SELECT * FROM table1");
while(rs.next())
{
mcode=rs.getInt(1);
mname=rs.getString(2);
mdesig=rs.getString(3);
msal=rs.getInt(4);
System.out.println(mcode+" "+mname+" "+mdesig+" "+msal);
}
rs.close();
stmt.close();
conn.close();

} //end of try block

catch(ClassNotFoundException e)
{

e.printStackTrace();
}
catch(SQLException w)
{
w.printStackTrace();
 }

} //End of main
} //End of class

Java Program for Updating Record

import java.sql.*;
public class Myapp3
{
public static void main(String st[])
{
int mcode,msal;
String mname,mdesig;
String buff;
Connection conn;
Statement stmt;
ResultSet rs;
int rt;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:kukreti","","");
stmt=conn.createStatement();
buff="UPDATE table1 SET salary=30000 WHERE desig='manager'";
rt=stmt.executeUpdate(buff);
System.out.println("Number Of Rows Updated====> "+rt);
rs=stmt.executeQuery("SELECT * FROM table1");
while(rs.next())
{
mcode=rs.getInt(1);
mname=rs.getString(2);
mdesig=rs.getString(3);
msal=rs.getInt(4);
System.out.println(mcode+" "+mname+" "+mdesig+" "+msal);
}
rs.close();
stmt.close();
conn.close();

} //end of try block

catch(ClassNotFoundException e)
{

e.printStackTrace();
}
catch(SQLException w)
{
w.printStackTrace();
 }

} //End of main
} //End of class

Java Program for Deleting Record

import java.sql.*;
public class Myapp4
{
public static void main(String st[])
{
int mcode,msal;
String mname,mdesig;
String buff;
Connection conn;
Statement stmt;
ResultSet rs;
int rt;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:kukreti","","");
stmt=conn.createStatement();
buff="Delete from table1 WHERE name='kukreti'";
rt=stmt.executeUpdate(buff);
System.out.println("Number Of Rows Deleted====> "+rt);
rs=stmt.executeQuery("SELECT * FROM table1");
while(rs.next())
{
mcode=rs.getInt(1);
mname=rs.getString(2);
mdesig=rs.getString(3);
msal=rs.getInt(4);
System.out.println(mcode+" "+mname+" "+mdesig+" "+msal);
}
rs.close();
stmt.close();
conn.close();

} //end of try block

catch(ClassNotFoundException e)
{

e.printStackTrace();
}
catch(SQLException w)
{
w.printStackTrace();

}
 } //End of main
} //End of class

Java Program for Inserting Record Into Database

import java.sql.*;
import java.io.*;
public class Myapp5
{
public static void main(String st[])
{

int mcode,msal;
String mname,mdesig;
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter Employee Code");
mcode=Integer.parseInt(br.readLine());
System.out.println("Enter Employee Name");
mname=br.readLine();
System.out.println("Enter Employee Designation");
mdesig=br.readLine();
System.out.println("Enter Employee Salary");
msal=Integer.parseInt(br.readLine());

PreparedStatement inst;
Connection conn;
Statement stmt;
ResultSet rs;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:kukreti","","");
inst=conn.prepareStatement("INSERT INTO table1 VALUES(?,?,?,?)");
inst.setInt(1,mcode);
inst.setString(2,mname);
inst.setString(3,mdesig);
inst.setInt(4,msal);
inst.executeUpdate();
 stmt=conn.createStatement();
rs=stmt.executeQuery("SELECT * FROM table1");
while(rs.next())
{
mcode=rs.getInt(1);
mname=rs.getString(2);
mdesig=rs.getString(3);
msal=rs.getInt(4);
System.out.println(mcode+" "+mname+" "+mdesig+" "+msal);
}
rs.close();
stmt.close();
inst.close();
conn.close();

} //end of try block

catch(ClassNotFoundException e)
{

e.printStackTrace();
}
catch(SQLException w)
{
w.printStackTrace();

}
catch(IOException p)
{

p.printStackTrace();
}
} //End Of main
} //End Of Class
ResultSet
public interface ResultSet extends Wrapper

A table of data representing a database result set, which is usually generated

by executing a statement that queries the database.

A ResultSet object maintains a cursor pointing to its current row of data.

Initially the cursor is positioned before the first row. The next method
moves the cursor to the next row, and because it returns false when there
are no more rows in the ResultSet object, it can be used in a while loop to
iterate through the result set.

Types of Result Sets

Result Sets may have different level of functionality . For

example, they may be scrollable or non scrollable. A scrollable
ResultSet has a cursor that moves both forward and backward
and can be moved to a particular row. Also , ResultSet may be
sensitive or insensitive to changes made, while they are open ;
that is , they may or may not reflect changes to column values
that are modified in the database

Three Types Of Result Sets available

(1) TYPE_FORWARD_ONLY

The result set is non scrollable, its cursor moves forward only
from top to bottom
(2) TYPE_SCROLL_INSENSITIVE

The Result set is scrollable. Its cursor can moves forward or

backward .

The Result Set generally does not show changes to the

underlying database that are mde while it is open.

(3) TYPE_SCROLL_SENSITIVE

The Result set is scrollable. Its cursor can moves forward or

backward .

The Result Set is sensitive to changes made while it is open. If

the Underlying column values are modified , the new values are
visible , thus providing a dynamic view of the underlying data.

Concurrency Type
A result set may have differenet update capabilities .
JDBC API offers two Update Capabilities

(1) CONCUR_READ_ONLY

Indicates a ResultSet that cannot be updates programmatically

(2) CONCUR_UPDATABLE

Indicates a ResultSet that can be updates programmatically