JDBC-UNIT 1

JDBC stands for Java DataBase Connectivity, which is a standard java API for database independent
connectivity between the java programming language and a wide range of databases, via the Structured Query
Language (SQL). JDBC is a key enterprise API.
JDBC library includes API for each of the tasks mentioned below that are commonly associated with database
usage.

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of
the following:
Import JDBC packages.
Load and register the JDBC driver.
Create a Connection to the database.
Create a Statement object
Execute the Statement object and return a query ResultSet and Process the ResultSet.
Close the ResultSet and Statement and the Connection objects

1)Import JDBC Packages

This is for making the JDBC API classes immediately available to the application program. The
following import statement should be included in the program irrespective of the JDBC driver being used:
import java.sql.*;
2)Load and register the JDBC driver
The forName() method of Class class is used to register the driver class. This method is used to
dynamically load the driver class.
For ms access driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
For mysql
Class.forName("com.mysql.jdbc.Driver");
3)Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database.
Connection con=DriverManager.getConnection( "url","username","password");
For ms access
Connection con = DriverManager.getConnection("jdbc:odbc:companydb","", "");
For mysql
Connectioncon=DriverManager.getConnection("jdbc:mysql://localhost://companydb","root","password");
4)Create the Statement object
The createStatement() method of Connection interface is used to create statement. The object of
statement is responsible to execute queries with the database.
Statement stmt = con.createStatement();
5)Execute the query
The executeQuery() method of Statement interface is used to execute queries to the database. This method
returns the object of ResultSet that can be used to get all the records of a table.

ResultSet rs=stmt.executeQuery("select fname from emp");

Process the result
while(rs.next())
{
System.out.println(rs.getInt(1));
}
6)Close the connection
. The close() method of Connection interface is used to close the connection.

rs.close() stmt.close() con.close();

import java.sql.*;

public class JDBCSample

{

public static void main(String args[]) {

try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/emp","root","");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES");

while(rs.next())
{
System.out.println(rs.getString(1));
}
rs.close(); stmt.close(); con.close();
}
}

statements In JDBC
Once you have created a Connection, you can begin using it to execute SQL statements. This is usually
done via Statement objects.
There are actually three kinds of statements in JDBC:

Statement
Represents a basic SQL statement
PreparedStatement
Represents a precompiled SQL statement, which can offer improved performance
CallableStatement
Allows JDBC programs complete access to stored procedures within the database itself

1)Statement

To get a Statement object, you call the createStatement() method of a Connection:

Statement stmt = con.createStatement();

Once you have created a Statement, you use it to execute SQL statements. A statement can either be a
query that returns results or an operation that manipulates the database in some way. If you are performing
a query, use the executeQuery() method of the Statement object:
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

Here we've used executeQuery() to run a SELECT statement. This call returns a ResultSet object that
contains the results of the query (we'll take a closer look at ResultSet in the next section). Statement also
provides an executeUpdate() method, for running SQL statements that do not return results, such as the
UPDATE and DELETE statements. executeUpdate() returns an integer that indicates the number of rows
in the database that were altered.

If you don't know whether a SQL statement is going to return results (such as when the user is entering the
statement in a form field), you can use the execute() method of Statement. This method returns true if there
is a result associated with the statement. In this case, the ResultSet can be retrieved using the getResultSet()
method and the number of updated rows can be retrieved using getUpdateCount():
Statement stmt = con.createStatement(); if(stmt.execute(sqlString))//sqlString is any sql query
{
ResultSet rs = stmt.getResultSet(); // display the results
}
else { System.out.println("Rows updated: " + stmt.getUpdateCount());
}
It is important to remember that a Statement object represents a single SQL statement. A call to
executeQuery(), executeUpdate(), or execute() implicitly closes any active ResultSet associated with the
Statement. In other words, you need to be sure you are done with the results from a query before you
execute another query with the same Statement object. If your application needs to execute more than one
simultaneous query, you need to use multiple Statement objects. As a general rule, calling the close()
method of any JDBC object also closes any dependent objects, such as a Statement generated by a
Connection or a ResultSet generated by a Statement, but well−written JDBC code closes everything
explicitly.

2)Prepared Statements

The PreparedStatement object is a close relative of the Statement object. Both accomplish roughly the same
thing: running SQL statements. PreparedStatement, however, allows you to precompile your SQL and run
it repeatedly, adjusting specific parameters as necessary. Since processing SQL strings is a large part of a
database's overhead, getting compilation out of the way at the start can significantly improve performance.
As with Statement, you create a PreparedStatement object from a Connection object. In this case, though,
the SQL is specified at creation instead of execution, using the prepareStatement() method ofConnection:

PreparedStatement pstmt = con.prepareStatement( "INSERT INTO EMPLOYEES (NAME, PHONE)

VALUES (?, ?)");

This SQL statement inserts a new row into the EMPLOYEES table, setting the NAME and PHONE
columns to certain values. Since the whole point of a PreparedStatement is to be able to execute the
statement repeatedly, we don't specify values in the call to prepareStatement(), but instead use question
marks (?) to indicate parameters for the statement. To actually run the statement, we specify values for the
parameters and then execute the statement:

pstmt.clearParameters(); pstmt.setString(1, "Jimmy Adelphi");

pstmt.setString(2, "201 555−7823"); pstmt.executeUpdate();
Then we can set the value for each parameter (indexed from 1 to the number of question marks) using the
setString() method. Finally, we use the executeUpdate() method to run the SQL.
CallableStatement

The CallableStatement interface is the JDBC object that supports stored procedures.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over
again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and
then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored
procedure can act based on the parameter value(s) that is passed.
Stored Procedure syntax

CREATE PROCEDURE procedure_name AS sql_statement

GO;
Connection class has a prepareCall() method that is very similar to the prepareStatement() method we
used to create a PreparedStatement. Because each database has its own syntax for accessing stored
procedures, JDBC defines a standardized escape syntax for accessing stored procedures with
CallableStatement.
The syntax for a stored procedure that does not return a result set is:

{call procedure_name[(?[,?...])]}

The syntax for a stored procedure that returns a result is:

{? = call procedure_name[(?[,?...])]}
Eample:
CallableStatment cstmt = con.prepareCall("{call sp_interest()}");
Here sp_interest() is a stored procedure

JDBC Architecture
Different database systems have something in common: a similar purpose and a mostly compatible query
language. Every database has its own API. JDBC is Sun's attempt to create a platform−neutral interface
between databases and Java. The JDBC API defines a set of interfaces that encapsulate major database
functionality, including running queries, processing results. Following figure shows how an application
uses JDBC to interact with one or more databases without knowing about the underlying driver
implementations.
Common JDBC components

The JDBC API provides the following interfaces and classes:

 DriverManager: this class manages a list of database drivers. Matches connection request from the
java application with the proper database driver using communication sub protocol. The first driver
that recognizes a certain sub protocol under JDBC will be used to establish a database connection.
 Driver: This interface handles the communication with the database server. We rarely interact
directly with Driver, instead we use a DriverManager object which manage objects of this type. It
also abstracts the details associated with working with Driver objects.
 Connection: This interface with all methods for contacting a database. The connection object
represents communication context, i.e., all communication with database is through connection
object only.
 Statement: We use objects created from this interface to submit the SQL statement to the database.
Some interfaces accept parameters in addition to executing stored procedures.
 ResultSet: These objects hold data retrieved from a database after we execute an SQL query using
statement object. It act as an iterator to move through its data.
 SQLException: This clas handles any errors that occur in a database application.

Given below is simple example that incorporates most of the major pieces of JDBC functionality. It loads a
driver, connects to the database, executes some SQL, and retrieves the results.

import java.sql.*;
public class JDBCSample
{
public static void main(String args[]) {
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost/emp","root","");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES");
while(rs.next())
{
System.out.println(rs.getString(1));
}
rs.close(); stmt.close(); con.close();
}
}
JDBC Drivers
JDBC drivers are available for most database platforms, from a number of vendors and in a
number of different flavors.

There are four categories of drivers:

Type 1 JDBC−ODBC Bridge Drivers
Type 2 Native−API Partly Java Drivers
Type 3 Net−protocol All−Java Drivers
Type 4 Native−protocol All−Java Drivers

Type 1 JDBC−ODBC Bridge Drivers

Type 1 drivers use a bridge technology to connect a Java client to an ODBC database system. The
JDBC−ODBC Bridge from Sun and InterSolv is the only extant example of a Type 1 driver. Type 1
drivers require some sort of non−Java software to be installed on the machine running your code,
and they are implemented using native code.

The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC
bridge driver converts JDBC method calls into the ODBC function calls.

Advantages:
easy to use.
can be easily connected to any database.

Disadvantages:
Performance degraded because JDBC method call is converted into the ODBC function calls.
The ODBC driver needs to be installed on the client machine.
 Type 2 Native−API Partly Java Drivers
Type 2 drivers use a native code library to access a database, wrapping a thin layer of Java around the
native library.

The Native API driver uses the client-side libraries of the database. The driver converts JDBC method
calls into native calls of the database API. It is not written entirely in java.
Advantage:
performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:
The Native driver needs to be installed on the each client machine.
The Vendor client library needs to be installed on client machine.

Type 3 Net−protocol All−Java Drivers

Type 3 drivers define a generic network protocol that interfaces with a piece of custom middleware.

The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or
indirectly into the vendor-specific database protocol. It is fully written in java.
Advantage:
No client side library is required because of application server that can perform many tasks like
auditing, load balancing, logging etc.
Disadvantages:
Network support is required on client machine.
Requires database-specific coding to be done in the middle tier.
Maintenance of Network Protocol driver becomes costly because it requires database-specific coding
to be done in the middle tier.

Type 4 Native−protocol All−Java Drivers

Type 4 drivers are written entirely in Java. They understand database−specific networking protocols and
can access the database directly without any additional software. These drivers are also well suited for
applet programming, provided that the Java security manager allows TCP/IP connections to the database
server.

Advantage:

Better performance than all other drivers.

No software is required at client side or server side.

Disadvantage:

Drivers depend on the Database.

Multiple Result Sets

It is possible to return more than one ResultSet or update count. The Statement object supports this
functionality via the getMoreResults() method. Calling this method implicitly closes any existing ResultSet
and moves to the next set of results for the statement. getMoreResults() returns true if there is another
ResultSet available to be retrieved by getResultSet(). The method returns false if another ResultSet is not
available. Eg:
Statement stmt = con.createStatement();
stmt.execute(sqlString);
while (true) {
ResultSet rs = stmt.getResultSet();
if(rs != null)
// display the results
else
/ process the update data
Handling Nulls
Sometimes database columns contain null, or empty, values. Because of the way certain database APIs are
written, it is impossible for JDBC to provide a method to determine whether or not a column is null before
execution. JDBC deals with this problem via the wasNull() method, which indicates whether or not the last
column read was null: Eg:

if(rs.wasNull())
System.out.println("Result was null");
else
System.out.println("In Stock: " + numberInStock);
We can use getObject() and test whether the result is null.
Large Data Types
We can retrieve large chunks of data from a ResultSet as a stream. This can be useful when reading images
from a database or loading large documents from a data store. The relevant ResultSet methods are
getAsciiStream(), getBinaryStream(), and getUnicodeStream(), where each method has column name and
column index variants. Each of these methods returns an InputStream.
Eg: Here's a sample code that retrieves an image from a PICTURES table and writes the image to an
OutputStream of some kind.
ResultSet rs=stmt.executeQuery("select image from pictures where pid="+req.
getparameter("pid"));
The JDBC 2.0 API includes BLOB and CLOB objects to handle large data types. Binary large objects
(BLOBs) and character large objects (CLOBs) store large amounts of binary or character data. JDBC 1.0
makes programs retrieve BLOB and CLOB data using the getBinaryStream() or getAsciiStream() methods.
In JDBC 2.0, the ResultSet interface includes getBlob() and getClob() methods, which return BLOB and
CLOB objects, respectively. The BLOB and CLOB objects themselves allow access to their data via streams
(the getBinaryStream() method of BLOB and the getCharacterStream() method of CLOB). We can set
BLOB and CLOB objects when we are working with a PreparedStatement, using the setBlob() and setClob()
methods. The lifespan of a Blob or Clob object is limited to the transaction that created it.
Handling Errors
Any JDBC object that encounters an error that halts execution throws a SQLException. The SQLException
class extends the normal java.lang.Exception class and defines an additional method called
getNextException(). This allows JDBC classes to chain a series of SQLException objects together.
SQLException also defines the getSQLState(), getMessage() and getErrorCode() methods to provide
additional information about an error. getSQLState(): The value returned by getSQLState() is one of the
SQL state codes. The following are certain SQL State Codes:
SQL State Code Definition
00 Successful completion
01 Warning
02 No data
07 Dynamic SQL error

getMessage(): return the JDBC driver’s error message for an error.

getErrorCode() : returns a specific error code which represent the error number associated
with the exception
Eg:
try {
// Actual database code
}catch (SQLException e) {
while(e != null) {
System.out.println("\nSQL Exception:");
System.out.println(e.getMessage());
System.out.println("ANSI−92 SQL State: " + e.getSQLState());
System.out.println("Vendor Error Code: " + e.getErrorCode());
e = e.getNextException();
}
}
SQL Warnings
JDBC classes have the option of generating a SQLWarning exception when something is not quite right, bet
not sufficiently serious to halt the entire program.
SQLWarning encapsulates the same information as SQLException and is used in a similar fashion.
SQLException objects are caught by try/catch blocks and warnings are retrieved using the getWarnings()
methods of the Connection, Statement, PreparedStatement, ResultSet and CallableStatement interfaces.
SQLWarning implements the getMessage(), getSQLState(), and getErrorCode() methods in the same
manner as SQLException.
If we are debugging an application, and we want to be aware of every little thing that goes wrong within the
database, we might use a printWarnings() method like:
void printWarnings(SQLWarning warn) {
while (warn != null) {
System.out.println(warn.getMessage()); System.out.println("ANSI−92 SQL
State: " + warn.getSQLState()); System.out.println("Vendor Error Code: " +
warn.getErrorCode());
warn = warn.getNextWarning();
}
}
Then we can use the printWarnings() method as:
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

printWarnings(stmt.getWarnings());
printWarnings(rs.getWarnings());
Metadata
Most JDBC programs are designed to work with a specific database and particular tables in that database;
the program knows exactly what kind of data it is dealing with. Some applications, however, need to
dynamically discover information about resultSet structures or underlying database configurations. This
information is called metadata, and JDBC provides two classes for dealing with it: DatabaseMetaData and
ResultSetMetaData.
1. DatabaseMetaData
We can retrieve general information about the structure of a database with the java.sql.DatabaseMetaData
interface. Database metadata is associated with a particular connection, so DatabaseMetaData objects are
created with the getMetaData() method of Connection:
DatabaseMetaData dbmeta = con.getMetaData();
DatabaseMetaData provides a number of methods that can be used to get configuration information about
the database such as database product name, product version, driver name etc. some of the methods in
DatabaseMetaData interface are String getDriverName()- return the name of the JDBC driver
String getDriverVersion()- return the version number of the JDBC driver String
getUserName()- return the user name
String getDatabaseProductName()- return the product name of the database
Eg:
import java.sql.*;
public class DBViewer {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con= DriverManager.getConnection("jdbc:mysql://localhost/emp","root",""");
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("Driver Name: " + dbmd.getDriverName());
System.out.println("Driver Version: " + dbmd.getDriverVersion());
System.out.println("Database Product: " + dbmd.getDatabaseProductName());
con.close();
}
catch (ClassNotFoundException e)
{ System.out.println("Unable to load database driver class");}

catch (SQLException e) {
System.out.println("SQL Exception: " + e.getMessage());
}
} }
2. ResultSetMetaData
The ResultSetMetaData interface provides information about the structure of a particular ResultSet with the
java.sql.ResultSetMetaData interface. ResultSetMetaData is associated with a particular connection, so
ResultSetMetaData objects are created with the getMetaData() method of Connection.
ResultSetMetaData rsmd=con.getMetaData();
Data provided by ResultSetMetaData includes the number of available columns, the names of those
columns, and the type of data available in each column. The methods of ResultSetMetaData interface are:
int getColumnCount(): returns total number of columns in the ResultSet
String getColumnLabel(int index):return column name of the specified column index String

getColumnTypeName(int index):return column type name for specified column index.

 String getTableName(int index): return table name for specified column index.
Eg:
import java.sql.*;
public class TableViewer {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
DriverManager.getConnection("jdbc:mysql://localhost/emp","root",""");
Connection con = DriverManager.getConnection("jdbc:oracle:customerdb");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS); ResultSetMetaData
rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for(int col = 1; col <= columnCount; col++)
{
System.out.print(rsmd.getColumnLabel(col));
System.out.print(" (" + rsmd.getColumnTypeName(col)+")");
}
rs.close();
stmt.close();
con.close();
}
catch (ClassNotFoundException e) { System.out.println("Unable to load database driver
class");
}
catch (SQLException e) {
System.out.println("SQL Exception: " + e.getMessage());
} } }
Advanced Resultset Handling
With JDBC 1.0, the functionality provided by the ResultSet interface is limited. There is no support for
updates of any kind and access to rows is limited to a single, sequential read(ie only in forward direction ).
JDBC 2.0 supports scrollable and updateable result sets, which allows for advanced record navigation and
data manipulation.
With scrolling, we can move forward and backward through the results of a query, rather than using the
next() method to move to the next row. In terms of scrolling, there are three distinct types of ResultSet
objects:
Forward−only (as in JDBC 1.0): allows sequential reading
Scroll−insensitive: a scroll−insensitive result set generally does not reflect changes to the underlying data
Scroll−sensitive: a scroll−sensitive result set reflect changes to the underlying data ResultSets are
also updateable. From this point, there are two different kinds of result sets:
Read−only ResultSets: that does not allow changes to the underlying data
Updateable ResultSets: that allows changes to the underlying data
To create an updateable, scroll−sensitive result set, we pass two extra arguments to the
createStatement() method:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATEABLE);
If we do not pass any arguments to createStatement(), we get a forward−only, read−only result set. If we
specify a scrollable result set (either sensitive or insensitive), we must also specify whether or not the result
set is updateable. The methods used to navigate through ResultSet are given below. They are also called
scrolling functions. When we start working with a ResultSet, the cursor is positioned before the first row of
results.

Method Function
first() Move to the first record.
last() Move to the last record.
next() Move to the next record.
previous() Move to the previous record.
beforeFirst() Move to immediately before the first record.
afterLast() Move to immediately after the last record.
absolute(int) Move to an absolute row number. Takes a positive or negative argument.
relative(int) Move backward or forward a specified number of rows. Takes a positive
or negativeargument.
The JDBC includes a number of methods that tells about the position of cursor in a
ResultSet. They include:
isFirst(): return true if the cursor is located on the first record.
isLast(): return true if the cursor is located on the last record.
isAfterLast(): returns true if the cursor is after the last row in the result set.
isBeforeFirst(): returns true if the cursor is before the first row in the result set .
With an updateable ResultSet, we can change data in an existing row, insert an entirely new
row, or delete an existing row. To change data in an existing row, we use the updateXXX()
methods of ResultSet, where XXX represent a data type in java. Eg:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDAT
EABLE);
ResultSet rs = stmt.executeQuery("select name, customer_id from customers");
rs.first();
rs.updateInt(2, 35243);
rs.updateRow();
In the example the first()navigate to the first row of the result set and then call updateInt() to
change the value of the customer ID column in the result set. After making the change, call
updateRow() to actually make the change in the database.

Transactions
A transaction is a group of several operations that must behave atomically, or as if they are a
single, indivisible operation. With regards to databases, transactions allow to combine one or
more database actions into a single atomic unit.Working with a transaction involves the
following steps:
1. Start the transaction
2. Perform its component operations
3. Either commit the transaction if all the component operations succeed or roll it back
if one of the operations fails.
The ability to roll back a transaction is the key feature. This means that if any one SQL
statement fails, the entire operation fails, and it is as though none of the component
operations took place.
The Connection object in JDBC is responsible for transaction management. By default, a
new connection starts out in transaction auto−commit mode, which means that every SQL
statement is executed as an individual transaction that is immediately committed to the
database.