Unit-2

JDBC

The Concept of JDBC

 JDBC stands for Java Database Connectivity.

 JDBC is a Java API to connect and execute the query with the
database.
 It is a part of JavaSE (Java Standard Edition).
 JDBC API uses JDBC drivers to connect with the database.
 There are four types of JDBC drivers:

o JDBC-ODBC Bridge Driver,

o Native Driver,
o Network Protocol Driver, and
o Thin Driver

 We can use JDBC API to access tabular data stored in any

relational database.
 By the help of JDBC API, we can save, update, delete and fetch
data from the database.
 It is like Open Database Connectivity (ODBC) provided by
Microsoft.
 The current version of JDBC is 4.3.
 It is based on the X/Open SQL Call Level Interface.
 The java.sql package contains classes and interfaces for JDBC
API.
 A list of popular interfaces of JDBC API are given below:

o Driver interface
o Connection interface
o Statement interface
o PreparedStatement interface
o CallableStatement interface
o ResultSet interface
o ResultSetMetaData interface
o DatabaseMetaData interface
o RowSet interface
 A list of popular classes of JDBC API are given below:

o DriverManager class
o Blob class
o Clob class
o Types class

 Before JDBC, ODBC API was the database API to connect and
execute the query with the database.
 But, ODBC API uses ODBC driver which is written in C
language (i.e. platform dependent and unsecured).
 That is why Java has defined its own API (JDBC API) that uses
JDBC drivers (written in Java language).
 We can use JDBC API to handle database using Java program
and can perform the following activities:

 Connect to the database

 Execute queries and update statements to the database
 Retrieve the result received from the database
JDBC Drivers

 JDBC Driver is a software component that enables java application

to interact with the database.
 There are 4 types of JDBC drivers:

1. JDBC-ODBC bridge driver

2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

 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.
 This is now discouraged because of thin driver.
  Oracle does not support the JDBC-ODBC Bridge from Java 8.

 Oracle recommends that you use JDBC drivers provided by the

vendor of your database instead of the JDBC-ODBC Bridge.

Advantages:

o Easy to use.
o Can be easily connected to any database.

Disadvantages:

o Performance degraded because JDBC method call is converted

into the ODBC function calls.
o The ODBC driver needs to be installed on the client machine.
2) Native-API driver

 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:

o Performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

o The Native driver needs to be installed on the each client machine.

o The Vendor client library needs to be installed on client machine.

3) Network Protocol driver

  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:

o No client side library is required because of application server that

can perform many tasks like auditing, load balancing, logging etc.

Disadvantages:

o Network support is required on client machine.

o Requires database-specific coding to be done in the middle tier.
 o Maintenance of Network Protocol driver becomes costly because it
requires database-specific coding to be done in the middle tier.

4) Thin driver

 The thin driver converts JDBC calls directly into the vendor-specific
database protocol.
 That is why it is known as thin driver.
 It is fully written in Java language.

Advantage:

o Better performance than all other drivers.

o No software is required at client side or server side.

Disadvantage:
 o Drivers depend on the Database.

JDBC Packages

 The java.sql and javax.sql are the primary packages for JDBC
4.0.
 It offers the main classes for interacting with your data sources.
 The new features in these packages include changes in the
following areas −

 Automatic database driver loading.

 Exception handling improvements.
 Enhanced BLOB/CLOB functionality.
 Connection and statement interface enhancements.
 National character set support.
 SQL ROWID access.
 SQL 2003 XML data type support.
 Annotations.

a. java.sql:

o java.sql is a package to access and process

the data stored in a database, typically a
relational database using Java.
o Different drivers can be installed
dynamically for the access of various
databases, using a framework which is in-
built in this JDBC API.
b. javax.sql:

o javax.sql is a JDBC package for the server

side accessing and processing the data from
the databases typically a relational database
using Java.
o It is an essential part for J2EE.
o This API provides the facilities such as
connection pooling, distributed transactions
and row sets for the enterprise applications.
o An interface by name DataSource is
provided in this API as an alternative to
DriverManager to establish the connection.

o DataSource and RowSet usage is direct, for

the applications, whereas connection
pooling, distributed transactions are
implemented by an infrastructure called
middle-tier.

c. javax.naming
o Contains classes and interfaces for Java
Naming and Directory Interface (JNDI),
which is often used for implementing
a DataSource.
 d. com.ibm.db2.jcc
o Contains the implementation of JDBC for
the IBM® Data Server Driver for JDBC and
SQLJ.

Overview of JDBC Process

 JDBC API provides a standard interface for interacting with any

relational database management systems (RDBMS).
 JDBC API consists of the following main components:

1. JDBC Driver
2. Connection
3. Statement
4. ResultSet

1. JDBC Driver
 A JDBC driver is set of Java classes that implement JDBC
interfaces for interacting with a specific database.
 Almost all database vendors such as MySQL, Oracle, Microsoft
SQL Server, provide JDBC drivers.
 For example, MySQL provides a JDBC driver called MySQL
Connection/J that allows you to work with MySQL database
through a standard JDBC API.
 There are three types of JDBC drivers including JDBC-native
API Driver, JDBC-net Driver, and JDBC thin Driver.
 JDBC Driver is written in pure Java.
 It translates JDBC calls into MySQL specific calls and sends the
calls directly to a specific database.
 To use a JDBC driver, you need to include the driver JAR file with
your application.
 MySQL Connector/J is the JDBC driver.

2. Connection
 The most important component of JDBC is the Connection object.
 In a Java application, you first load a JDBC driver and
then establish a connection to the database.
 Through the Connection object, you can interact with the database
e.g., creating a Statement to execute SQL queries against tables.
 You can open more than one connection to a database at a time.

3. Statement
 To execute a SQL queries e.g, SELECT, UPDATE, DELETE etc.,
you use a Statement object.
 You create the Statement object through the Connection object.
 JDBC provides several types of statements for different purposes
such as PreparedStatement , CallableStatement .
 4. ResultSet
 After querying data from the database, you get a ResultSet object.
 The ResultSet object provides a set of API that allows you to
traverse result of the query.

The typical flow of using JDBC is as follows:

1. First, load the JDBC driver and create a connection to the database.
2. Then, create a Statement and execute the query to get a ResultSet.
3. Next, traverse and process the ResultSet .
4. Close the ResultSet , Statement , and Connection .

Database Connection

Java Database Connectivity with 5 Steps

5 Steps to connect to the database in java

1. Register the driver class

2. Create the connection object
3. Create the Statement object
4. Execute the query
 5. Close the connection object

There are 5 steps to connect any java application with the database
using JDBC.
These steps are as follows:

o Register the Driver class

o Create connection
o Create statement
o Execute queries
o Close connection

1) Register the driver class

 The forName() method of class Class is used to register the driver class.
This method is used to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)

throws ClassNotFoundException

Example to register the OracleDriver class

Here, Java program is loading oracle driver to establish database

connection.

Class.forName("oracle.jdbc.driver.OracleDriver");

2. Create the connection object

 The getConnection() method of DriverManager class is used to
establish connection with the database.

Syntax of getConnection() method

public static Connection getConnection(String url)throws

SQLException
 Or

public static Connection getConnection(String url,String name,String p

assword) throws SQLException
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@loc
alhost:1521:xe","system","password");

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

Syntax of createStatement() method

public Statement createStatement()throws SQLException

Example to create the statement object

Statement stmt=con.createStatement();
4) 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.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

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

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

5) Close the connection object

 By closing connection object statement and ResultSet will be

closed automatically.
 The close() method of Connection interface is used to close the connection.
 Syntax of close() method

public void close()throws SQLException

Example to close connection

con.close();

Java Database Connectivity with Oracle

 To connect java application with the oracle database, we need to follow

5 following steps.
 In this example, we are using Oracle 10g as the database.
 So we need to know following information for the oracle database:
1. Driver class:

 The driver class for the oracle database is oracle.jdbc.driver.

OracleDriver.

2. Connection URL:

 The connection URL for the oracle10G database is

jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API,

 oracle is the database, thin is the driver, localhost is the server

name on which oracle is running, we may also use IP address,

1521 is the port number and XE is the Oracle service name.

3. Username:

 The default username for the oracle database is system.

4. Password:

 It is the password given by the user at the time of installing the oracle
database.

Create a Table
 Before establishing connection, let's first create a table in oracle database.
 Following is the SQL query to create a table.

create table emp(id number(10),name varchar2(40),age number(3));

Example to Connect Java Application with Oracle database

 In this example, we are connecting to an Oracle database and

getting data from emp table.
 Here, system and oracle are the username and password of the
Oracle database.

import java.sql.*;
class OracleCon{
public static void main(String args[]){
try{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

//step2 create the connection object

Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

//step3 create the statement object

Statement stmt=con.createStatement();

//step4 execute query

ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3));

//step5 close the connection object

con.close();
}catch(Exception e){ System.out.println(e);}
}
}

 To connect java application with the Oracle database ojdbc14.jar

file is required to be loaded.
 Two ways to load the jar file:
o paste the ojdbc14.jar file in jre/lib/ext folderset
o classpath

1) paste the ojdbc14.jar file in JRE/lib/ext folder:

 Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and

 paste the jar file here.

2) set classpath:

There are two ways to set the classpath:

o temporary
o permanent

How to set the temporary classpath:

 Firstly, search the ojdbc14.jar file then open command prompt and
write:
C:>set classpath=c:\folder\ojdbc14.jar;

How to set the permanent classpath:

 Go to environment variable then click on new tab.

 In variable name write classpath and in variable value paste the
path to ojdbc14.jar by appending ojdbc14.jar;.; as C:\oraclexe\
app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;

Java Database Connectivity with MySQL

 To connect Java application with the MySQL database, we need to

follow 5 following steps.

 In this example we are using MySql as the database.

 So we need to know following information for the mysql database:

1. Driver class:

 The driver class for the mysql database is

com.mysql.jdbc.Driver.

2. Connection URL:

 The connection URL for the mysql database is

jdbc:mysql://localhost:3306/sonoo where jdbc
is the API, mysql is the database, localhost is the
server name on which mysql is running, we may also use
IP address, 3306 is the port number and sonoo is the database
name.
 We may use any database, in such case, we need to replace
the sonoo with our database name.

3. Username:

 The default username for the mysql database is root.

4. Password:

 It is the password given by the user at the time of installing

 the mysql database.
 In this example, we are going to use root as the password.
 Let's first create a table in the mysql database, but before
creating table, we need to create database first.

create database sonoo;

use sonoo;
create table emp(id int(10),name varchar(40),age int(3));

Example to Connect Java Application with mysql database

 In this example, sonoo is the database name, root is the username

and password both.

import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sonoo","root","root");
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

 To connect java application with the mysql database,

mysqlconnector.jar file is required to be loaded.

 Two ways to load the jar file:

1. Paste the mysqlconnector.jar file in jre/lib/ext folder

2. Set classpath

1) Paste the mysqlconnector.jar file in JRE/lib/ext folder:

-> Download the mysqlconnector.jar file.

-> Go to jre/lib/ext folder and paste the jar file here.

2) Set classpath:

 There are two ways to set the classpath:

o temporary
o permanent

How to set the temporary classpath

 open command prompt and write:

 C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;

How to set the permanent classpath

 Go to environment variable then click on new tab.

 In variable name write classpath and in variable value

paste the path to the mysqlconnector.jar file by appending

mysqlconnector.jar;.; as C:\folder\mysql-connector-java-5.0.8-bin.jar;

JDBC - Statements, PreparedStatement and CallableStatement

 Once a connection is obtained we can interact with the database.

 The JDBC Statement, CallableStatement, and PreparedStatement

interfaces define the methods and properties that enable you to

send SQL or PL/SQL commands and receive data from your database

 The following table provides a summary of each interface's purpose

to decide on the interface to use.

Interfaces Recommended Use

Statement  Use this for general-purpose access to your database.

 Useful when you are using static SQL statements at
runtime.
 The Statement interface cannot accept parameters.

PreparedStatemen  Use this when you plan to use the SQL statements many
t
 times.
 The PreparedStatement interface accepts input
parameters at runtime.

CallableStatement  Use this when you want to access the database stored
procedures.
 The CallableStatement interface can also accept
runtime input parameters.

The Statement Objects

Creating Statement Object

 Before you can use a Statement object to execute a SQL statement,

you need to create one using the Connection object's createStatement( )

method, as in the following example –

Statement stmt = null;

try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
finally {
 ...
}

 Once you've created a Statement object, you can then use it to execute an

SQL statement with one of its three execute methods.

 boolean execute (String SQL):

 Returns a boolean value of true if a ResultSet

object can be retrieved; otherwise, it returns false.

 Use this method to execute SQL DDL statements.

 int executeUpdate (String SQL)

 Returns the number of rows affected by the

execution of the SQL statement.

 Use this method to execute SQL statements for

which you expect to get a number of rows

affected - for example, an INSERT, UPDATE,

or DELETE statement.

 ResultSet executeQuery (String SQL)

 Returns a ResultSet object.

 Use this method when you expect to get a result set,

as you would with a SELECT statement.

Closing Statement Object

 Just as you close a Connection object to save database resources,

for the same reason you should also close the Statement object.

 If you close the Connection object first, it will close the Statement object

as well.

 However, you should always explicitly close the Statement object to

ensure proper clean-up.

Statement stmt = null;

try {
stmt = conn.createStatement( );
...
}
catch (SQLException e) {
...
}
finally {
stmt.close();
}

The PreparedStatement Objects

 The PreparedStatement interface extends the Statement interface,

 which gives you added functionality with a couple of advantages

over a generic Statement object.

 This statement gives you the flexibility of supplying arguments

dynamically.

Creating PreparedStatement Object

PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}
catch (SQLException e) {
...
}
finally {
...
}

 All parameters in JDBC are represented by the ? symbol, which is

known as the parameter marker.

 You must supply values for every parameter before executing the SQL

statement.

 If you forget to supply the values, you will receive an SQLException.

 Each parameter marker is referred by its ordinal position.
  The first marker represents position 1, the next position 2, and so forth.
 This method differs from that of Java array indices, which starts at 0.
 All of the Statement object's methods for interacting with the database

(a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with

the PreparedStatement object.

 However, the methods are modified to use SQL statements that can input

the parameters.

Closing PreparedStatement Object

 Just as you close a Statement object, for the same reason you should also

close the PreparedStatement object.

 A simple call to the close() method will do the job.

 If you close the Connection object first, it will close the

PreparedStatement object as well.

 However, you should always explicitly close the PreparedStatement

object to ensure proper clean-up.

PreparedStatement pstmt = null;

try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}
catch (SQLException e) {
...
}
finally {
pstmt.close();
}

The CallableStatement Objects

 Just as a Connection object creates the Statement and PreparedStatement

objects, it also creates the CallableStatement object, which would be used

to execute a call to a database stored procedure.

Creating CallableStatement Object (Oracle)

CREATE OR REPLACE PROCEDURE getEmpName

(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;

For MySQL
DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$

CREATE PROCEDURE `EMP`.`getEmpName`
(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
 SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END $$
DELIMITER ;

 Three types of parameters exist: IN, OUT, and INOUT.

 The PreparedStatement object only uses the IN parameter.
 The CallableStatement object can use all the three.

Here are the definitions of each −

Parameter Description

IN  A parameter whose value is unknown when the SQL

statement is created.

OUT  A parameter whose value is supplied by the SQL statement

it returns.

INOUT  A parameter that provides both input and output values.

 The following code snippet shows how to employ the

Connection.prepareCall() method to instantiate a CallableStatement

object based on the preceding stored procedure –

CallableStatement cstmt = null;

try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
...
}
catch (SQLException e) {
...
}
finally {
...
}

 The String variable SQL, represents the stored procedure, with parameter

placeholders.

 Using the CallableStatement objects is much like using the

PreparedStatement objects.

 You must bind values to all the parameters before executing the statement,

or you will receive an SQLException.

 If you have IN parameters, just follow the same rules and techniques

that apply to a PreparedStatement object.

 When you use OUT and INOUT parameters you must employ an

additional CallableStatement method, registerOutParameter().

 The registerOutParameter() method binds the JDBC data type, to the

data type that the stored procedure is expected to return.

Closing CallableStatement Object

 Just as you close other Statement object, for the same reason you should

also close the CallableStatement object.

 A simple call to the close() method will do the job.

 If you close the Connection object first, it will close the

CallableStatement object as well.

 However, you should always explicitly close the CallableStatement

object to ensure proper cleanup.

CallableStatement cstmt = null;

try {
String SQL = "{call getEmpName (?, ?)}";
cstmt = conn.prepareCall (SQL);
...
}
catch (SQLException e) {
...
}
finally {
cstmt.close();
}

JDBC - Result Set

 The SQL statements that read data from a database query, return the data in a
 result set.

 The SELECT statement is the standard way to select rows from a database and

view them in a result set.

 The java.sql.ResultSet interface represents the result set of a database query.

 A ResultSet object maintains a cursor that points to the current row in the result set.
 The term "result set" refers to the row and column data contained in a ResultSet

object.

 The methods of the ResultSet interface can be broken down into three categories –

 Navigational methods − Used to move the cursor around.

 Get methods − Used to view the data in the columns of the current

row being pointed by the cursor.

 Update methods − Used to update the data in the columns of the

current row. The updates can then be updated in the underlying

database as well.

 The cursor is movable based on the properties of the ResultSet.

 JDBC provides the following connection methods to create statements with
desired ResultSet –

 createStatement(int RSType, int RSConcurrency);

 prepareStatement(String SQL, int RSType, int RSConcurrency);
 prepareCall(String sql, int RSType, int RSConcurrency);

 The first argument indicates the type of a ResultSet object and the second argument

is one of two ResultSet constants for specifying whether a result set is

read-only or updatable.
Type of ResultSet

 The possible RSType are given below.

 If you do not specify any ResultSet type, you will automatically get one that

is TYPE_FORWARD_ONLY.

Type Description

ResultSet.TYPE_FORWARD_ONLY  The cursor can only move

forward in the result set.

ResultSet.TYPE_SCROLL_INSENSITIV  The cursor can scroll forward

E and backward, and the result
set is not sensitive to changes
made by others to the database
that occur after the result set
was created.

ResultSet.TYPE_SCROLL_SENSITIVE.  The cursor can scroll forward

and backward, and the result
set is sensitive to changes
made by others to the database
that occur after the result set
 was created.

Concurrency of ResultSet

 The possible RSConcurrency are given below.

 If you do not specify any Concurrency type, you will automatically get one
that is CONCUR_READ_ONLY.

Concurrency Description

ResultSet.CONCUR_READ_ONLY  Creates a read-only result set.

 This is the default

ResultSet.CONCUR_UPDATABLE  Creates an updateable result set.

Example
try {
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
}
catch(Exception ex) {
....
}
finally {
....
}
Navigating a Result Set

 There are several methods in the ResultSet interface that involve moving the

cursor, including –

S.N Methods & Description

.

1 public void beforeFirst() throws SQLException

Moves the cursor just before the first row.

2 public void afterLast() throws SQLException

Moves the cursor just after the last row.

3 public boolean first() throws SQLException

Moves the cursor to the first row.

4 public void last() throws SQLException

Moves the cursor to the last row.

5 public boolean absolute(int row) throws SQLException

Moves the cursor to the specified row.

6 public boolean relative(int row) throws SQLException

 Moves the cursor to the given number of rows forward or backward, from
where it is currently pointing.

7 public boolean previous() throws SQLException

Moves the cursor to the previous row.

This method returns false if the previous row is off the result set.

8 public boolean next() throws SQLException

Moves the cursor to the next row.

This method returns false if there are no more rows in the result set.

9 public int getRow() throws SQLException

Returns the row number that the cursor is pointing to.

10 public void moveToInsertRow() throws SQLException

Moves the cursor to a special row in the result set that can be used to insert a
new row into the database.

The current cursor location is remembered.

11 public void moveToCurrentRow() throws SQLException

Moves the cursor back to the current row if the cursor is currently at the insert
row; otherwise, this method does nothing
Viewing a Result Set

 The ResultSet interface contains dozens of methods for getting the data of the

current row.

 There is a get method for each of the possible data types, and each get method

has two versions –

 One that takes in a column name.

 One that takes in a column index.

S.N Methods & Description

.

1 public int getInt(String columnName) throws SQLException

Returns the int in the current row in the column named columnName.

2 public int getInt(int columnIndex) throws SQLException

Returns the int in the current row in the specified column index.

The column index starts at 1, meaning the first column of a row is 1, the
second column of a row is 2, and so on.
Updating a Result Set

 The ResultSet interface contains a collection of update methods for updating

the data of a result set.

 As with the get methods, there are two update methods for each data type –

 One that takes in a column name.

 One that takes in a column index.

S.N Methods & Description

.

1 public void updateString(int columnIndex, String s) throws SQLException

Changes the String in the specified column to the value of s.

2 public void updateString(String columnName, String s) throws

SQLException

Similar to the previous method, except that the column is specified by its name
instead of its index.

 Updating a row in the result set changes the columns of the current row in the

ResultSet object, but not in the underlying database.

  To update your changes to the row in the database, you need to invoke one

of the following methods.

S.N Methods & Description

.

1 public void updateRow()

Updates the current row by updating the corresponding row in the database.

2 public void deleteRow()

Deletes the current row from the database

3 public void refreshRow()

Refreshes the data in the result set to reflect any recent changes in the
database.

4 public void cancelRowUpdates()

Cancels any updates made on the current row.

5 public void insertRow()

Inserts a row into the database.

This method can only be invoked when the cursor is pointing to the insert row.
Transaction Processing

 If your JDBC Connection is in auto-commit mode, which it is by

default, then every SQL statement is committed to the database
upon its completion.
 Three reasons to turn off the auto-commit and manage your own
transactions –

 To increase performance.
 To maintain the integrity of business processes.
 To use distributed transactions.

 Transactions enable you to control if, and when, changes are

applied to the database.
 It treats a single SQL statement or a group of SQL statements as
one logical unit, and if any statement fails, the whole transaction
fails.
 To enable manual- transaction support instead of the auto-
commit mode, JDBC driver uses by default the Connection
object's setAutoCommit() method.
 If you pass a boolean false to setAutoCommit( ), you turn off
auto-commit.
 You can pass a boolean true to turn it back on again.
 conn.setAutoCommit(false);

Commit & Rollback

 Once you are done with your changes and you want to commit the
changes then call commit() method on connection object as
follows −

conn.commit( );

 Otherwise, to roll back updates to the database made using the

Connection named conn, use the following code −

conn.rollback( );
try
{
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

String SQL = "INSERT INTO Employees" +"VALUES (106, 20,

'Rita', 'Tez')";
stmt.executeUpdate(SQL);
String SQL = "INSERT INTO Employees " + "VALUES (107,
22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
conn.commit();
}
catch(SQLException se){
// If there is any error.
conn.rollback();
}
 In this case, none of the above INSERT statement would success
and everything would be rolled back.
Using Savepoints

 The new JDBC 3.0 Savepoint interface gives you the additional
transactional control.
 Most modern DBMS, support savepoints within their
environments such as Oracle's PL/SQL.
 When you set a savepoint you define a logical rollback point
within a transaction.
 If an error occurs past a savepoint, you can use the rollback
method to undo either all the changes or only the changes made
after the savepoint.
 The Connection object has two new methods that help you
manage savepoints –

 setSavepoint(String savepointName)

o Defines a new savepoint.

o It also returns a Savepoint object.

 releaseSavepoint(Savepoint savepointName)
o Deletes a savepoint.
o Notice that it requires a Savepoint object as
a parameter.
o This object is usually a savepoint generated
by the setSavepoint() method.

 There is one rollback (String savepointName) method, which

rolls back work to the specified savepoint.
Example:
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();

//set a Savepoint
Savepoint savepoint1=conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees " + "VALUES (106, 20,
'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees " + "VALUES (107, 22,
'Sita', 'Tez')";
stmt.executeUpdate(SQL);
// If there is no error, commit the changes.
conn.commit();

}catch(SQLException se){
// If there is any error.
conn.rollback(savepoint1);
}
conn.releaseSavePoint(savepoint1);

 In this case, none of the above INSERT statement would success

and everything would be rolled back
DatabaseMetaData in JDBC

 Data about data is known as metadata.

 The DatabaseMetaData interface provides methods to get
information about the database you have connected with like,
database name, database driver version, maximum column length
etc...
 DatabaseMetaData programming is useful while developing GUI
tools for Database software like sqlyog, mysql front, etc.
 Following are some methods of DatabaseMetaData interface.

Method Description

getDriverName() Retrieves the name of the current JDBC

driver

getDriverVersion() Retrieves the version of the current JDBC

driver

getUserName() Retrieves the user name.

getDatabaseProductName() Retrieves the name of the current

database.

getDatabaseProductVersion( Retrieves the version of the current

) database.

getNumericFunctions() Retrieves the list of the numeric functions

 Method Description

available with this database.

getStringFunctions() Retrieves the list of the string functions

available with this database.

getSystemFunctions() Retrieves the list of the system functions

available with this database.

getTimeDateFunctions() Retrieves the list of the time and date

functions available with this database.

getURL() Retrieves the URL for the current

database.

supportsSavepoints() Verifies whether the current database

supports save points

supportsStoredProcedures() Verifies whether the current database

supports stored procedures.

supportsTransactions() Verifies whether the current database

supports transactions.

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;
public class DatabaseMetadataExample {

public static void main(String args[])throws Exception {

//Getting the connection

String mysqlUrl = "jdbc:mysql://localhost/sampleDB";

Connection con = DriverManager.getConnection(mysqlUrl,

"root", "password");

System.out.println("Connection established......");

//Creating the DatabaseMetaData object

DatabaseMetaData dbMetadata = con.getMetaData();

//invoke the supportsBatchUpdates() method.

boolean bool = dbMetadata.supportsBatchUpdates();

if(bool) {

System.out.println("Underlying database supports batch

updates");

} else {

System.out.println("Underlying database doesnt supports

batch updates");

//Retrieving the driver name

System.out.println(dbMetadata.getDriverName());

//Retrieving the driver version

System.out.println(dbMetadata.getDriverVersion());

//Retrieving the user name

System.out.println(dbMetadata.getUserName());

//Retrieving the URL

System.out.println(dbMetadata.getURL());

//Retrieving the list of numeric functions

 System.out.println("Numeric functions:
"+dbMetadata.getNumericFunctions());

System.out.println("");

//Retrieving the list of String functions

System.out.println("String functions:
"+dbMetadata.getStringFunctions());

System.out.println("");

//Retrieving the list of system functions

System.out.println("System functions:
"+dbMetadata.getSystemFunctions());

System.out.println("");

//Retrieving the list of time and date functions

System.out.println("Time and Date funtions:

"+dbMetadata.getTimeDateFunctions());

JDBC Datatypes

 The JDBC driver converts the Java data type to the appropriate
JDBC type, before sending it to the database.
 It uses a default mapping for most data types.
 For example, a Java int is converted to an SQL INTEGER.
 Default mappings were created to provide consistency between
drivers.
 The following table summarizes the default JDBC data type that
the Java data type is converted to, when you call the setXXX()
method of the PreparedStatement or CallableStatement object
or the ResultSet.updateXXX() method.
SQL JDBC/Java setXXX updateXXX

VARCHAR java.lang.String setString updateString

CHAR java.lang.String setString updateString

LONGVARCHA java.lang.String setString updateString

R

BIT Boolean setBoolean updateBoolean

NUMERIC java.math.BigDecimal setBigDecima updateBigDecimal

l

TINYINT Byte setByte updateByte

SMALLINT Short setShort updateShort

INTEGER Int setInt updateInt

BIGINT Long setLong updateLong

REAL Float setFloat updateFloat

FLOAT Float setFloat updateFloat

DOUBLE Double setDouble updateDouble

VARBINARY byte[ ] setBytes updateBytes

BINARY byte[ ] setBytes updateBytes

DATE java.sql.Date setDate updateDate

TIME java.sql.Time setTime updateTime

TIMESTAMP java.sql.Timestamp setTimestamp updateTimestamp

CLOB java.sql.Clob setClob updateClob

BLOB java.sql.Blob setBlob updateBlob

ARRAY java.sql.Array setARRAY updateARRAY

REF java.sql.Ref SetRef updateRef

STRUCT java.sql.Struct SetStruct updateStruct

  JDBC 3.0 has enhanced support for BLOB, CLOB, ARRAY, and
REF data types.
 The ResultSet object now has updateBLOB(), updateCLOB(),
updateArray(), and updateRef() methods that enable you to
directly manipulate the respective data on the server.
 The setXXX() and updateXXX() methods enable you to convert
specific Java types to specific JDBC data types.
 The methods, setObject() and updateObject(), enable you to
map almost any Java type to a JDBC data type.
 ResultSet object provides corresponding getXXX() method for
each data type to retrieve column value.
 Each method can be used with column name or by its ordinal
position.

SQL JDBC/Java setXXX getXXX

VARCHAR java.lang.String setString getString

CHAR java.lang.String setString getString

LONGVARCHA java.lang.String setString getString

R

BIT Boolean setBoolean getBoolean

NUMERIC java.math.BigDecima setBigDecima getBigDecima

 l l l

TINYINT Byte setByte getByte

SMALLINT Short setShort getShort

INTEGER Int setInt getInt

BIGINT Long setLong getLong

REAL Float setFloat getFloat

FLOAT Float setFloat getFloat

DOUBLE Double setDouble getDouble

VARBINARY byte[ ] setBytes getBytes

BINARY byte[ ] setBytes getBytes

DATE java.sql.Date setDate getDate

TIME java.sql.Time setTime getTime

TIMESTAMP java.sql.Timestamp setTimestamp getTimestamp

CLOB java.sql.Clob setClob getClob

BLOB java.sql.Blob setBlob getBlob

ARRAY java.sql.Array setARRAY getARRAY

REF java.sql.Ref SetRef getRef

STRUCT java.sql.Struct SetStruct getStruct

Date & Time Data Types

 The java.sql.Date class maps to the SQL DATE type, and the
java.sql.Time and java.sql.Timestamp classes map to the SQL
TIME and SQL TIMESTAMP data types, respectively.
 Following example shows how the Date and Time classes format
the standard Java date and time values to match the SQL data type
requirements.

import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.*;

public class SqlDateTime {

public static void main(String[] args) {
//Get standard date and time
java.util.Date javaDate = new java.util.Date();
long javaTime = javaDate.getTime();
System.out.println("The Java Date is:" +
javaDate.toString());

//Get and display SQL DATE

java.sql.Date sqlDate = new java.sql.Date(javaTime);
System.out.println("The SQL DATE is: " +
sqlDate.toString());

//Get and display SQL TIME

java.sql.Time sqlTime = new java.sql.Time(javaTime);
System.out.println("The SQL TIME is: " +
sqlTime.toString());
//Get and display SQL TIMESTAMP
java.sql.Timestamp sqlTimestamp =
new java.sql.Timestamp(javaTime);
System.out.println("The SQL TIMESTAMP is: " +
sqlTimestamp.toString());
 }//end main
}//end SqlDateTime

Now let us compile the above example as follows −

C:\>javac SqlDateTime.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java SqlDateTime
The Java Date is:Tue Aug 18 13:46:02 GMT+04:00 2009
The SQL DATE is: 2009-08-18
The SQL TIME is: 13:46:02
The SQL TIMESTAMP is: 2009-08-18 13:46:02.828
C:\>

Exceptions

 Exception handling allows you to handle exceptional conditions

such as program-defined errors in a controlled fashion.
 When an exception condition occurs, an exception is thrown.
 The term throws means that current program execution stops, and
the control is redirected to the nearest applicable catch clause.
 If no applicable catch clause exists, then the program's execution
ends.
  For JDBC, the most common exception
is java.sql.SQLException.

SQLException Methods

 An SQLException can occur both in the driver and the database.

 When such an exception occurs, an object of type SQLException
will be passed to the catch clause.
 The passed SQLException object has the following methods
available for retrieving additional information about the exception

Method Description

getErrorCode( ) Gets the error number associated with the

exception.

getMessage( ) Gets the JDBC driver's error message for an

error, handled by the driver or gets the
Oracle error number and message for a
database error.

getSQLState( ) Gets the XOPEN SQLstate string. For a

JDBC driver error, no useful information is
returned from this method. For a database
error, the five-digit XOPEN SQLstate code
is returned. This method can return null.
getNextException( ) Gets the next Exception object in the
exception chain.

printStackTrace( ) Prints the current exception, or throwable,

and it's backtrace to a standard error stream.

printStackTrace(PrintStream Prints this throwable and its backtrace to the

s) print stream you specify.

printStackTrace(PrintWriter Prints this throwable and it's backtrace to

w) the print writer you specify.

 By utilizing the information available from the Exception object,

you can catch an exception and continue your program
appropriately.
 Here is the general form of a try block −

try {
// Your risky code goes between these curly braces!!!
}
catch(Exception ex) {
// Your exception handling code goes between these
// curly braces, similar to the exception clause
// in a PL/SQL block.
}
finally {
 // Your must-always-be-executed code goes between these
// curly braces. Like closing database connection.
}

Example
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCExample {

static final String DB_URL = "jdbc:mysql://localhost/Employee";
static final String USER = "guest";
static final String PASS = "guest123";
static final String QUERY = "{call getEmpName (?, ?)}";

public static void main(String[] args) {

// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL,
USER, PASS);
CallableStatement stmt = conn.prepareCall(QUERY);
){
// Bind values into the parameters.
stmt.setInt(1, 1); // This would set ID
// Because second parameter is OUT so register it
 stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//Use execute method to run stored procedure.
System.out.println("Executing stored procedure..." );
stmt.execute();
//Retrieve employee name with getXXX method
String empName = stmt.getString(2);
System.out.println("Emp Name with ID: 1 is " + empName);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Associating JDBC-ODBC bridge driver with the database

The JDBC-ODBC bridge provides JDBC access via most ODBC drivers.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class Main {

public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement();
// st.executeUpdate("drop table survey;");
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
 st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");

ResultSetMetaData rsMetaData = rs.getMetaData();

int numberOfColumns = rsMetaData.getColumnCount();

System.out.println("resultSet MetaData column Count=" + numberOfColumns);

st.close();
conn.close();
}

private static Connection getConnection() throws Exception {

String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:northwind";
String username = "";
String password = "";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}
}