Unit 2 - JDBC

Java Database Connectivity

JDBC
• JDBC is a technology which can be used to communicate with
the database from Java Application.

Java
JDBC
Application DATABASE

Oracle,DB2,Postgre,Sybase,MySQL,
SQLite, Apache Cassandra, MS Access
• JDBC Is a part of Java Standard Edition.
• JAVA editions are
▪ Java Standard Edition
▪ Java Enterprise Edition
▪ Java Micro Edition
• JDBC is a Specification defined by Java Vendor and implemented
by Database vendor.
• Database vendor provided implementation is Driver Software.
Introduction
 Database
◦ Collection of data

 DBMS
◦ Database management system
◦ Storing and organizing data

 SQL
◦ Relational database
◦ Structured Query Language

 JDBC
◦ Java Database Connectivity
◦ JDBC driver
• JDBC is a Sun Microsystems trademark
• It is often taken to stand for Java Database Connectivity

Java Provides Platform Independent API.

• Java is very standardized, but there are many versions of SQL.
• JDBC is a means of accessing SQL databases from Java
• JDBC is a standardized API for use by Java programs. Its Database
Independent API.
• JDBC is also a specification for how third-party vendors should write
database drivers to access specific SQL versions.
• JDBC provides CRUD OPERATIONS.
• C - Create R- Retrieve U- Update D- Delete
Java Database Connectivity (JDBC)

• JDBC – provides an interface to Relational Data Sources

• JDBC library provides the means for executing SQL statements to

access and operate on a relational database

• JDBC library is implemented in the java.sql package

• Set of classes and interfaces that provide a uniform API for access to broad
range of databases
Contd…

• Programs developed with Java/JDBC are platform and vendor

independent.
• “write once, compile once, run anywhere”
• Write apps in java to access any DB, using standard SQL statements –
while still following Java conventions.
• JDBC driver manager and JDBC drivers provide the bridge between
the database and java .
JDBC & ODBC
Sun
Micro Microsys 1997
1992
soft tems

OD JD
C,C++ , Java
BC Java BC
win Completel
Any
Native Platfor
Languages dow y Java
m
s
ODBC
 Open Data Base Connectivity
 Developed by Microsoft for the Windows platform as the way for
Windows applications to access Microsoft databases (SQL Server, FoxPro,
Ms.Access)
 Most data base vendors supply native, odbc, and jdbc drivers for their
data base products
 ODBC provides a C interface for database access on Windows
environment.
 ODBC has a few commands with lots of complex options. Java prefers
simple methods.
Two-Tier Database Access Model
 Java Application talks directly Application Space
to the database
 Accomplished through the Java Application
JDBC driver which sends
commands directly to the
database JDBC Driver

 Results sent back directly to

the application SQL Result
Command Set

Database
Three-Tier Database Access Model

 JDBC driver sends commands Application Space

to a middle tier, which in
Java Application
turn sends commands to
database.
JDBC Driver
 Results are sent back to the
middle tier, which SQL
Result
communicates them back to Command
Set
Application Server
the application
(middle-tier)
Proprietary
Protocol
Database
JDBC Components
• Driver Manager
• Loads database drivers, and manages the connection between the
application and the driver

• Driver
• Translates API calls into operations for a specific data source

• Connection
• A session between an application and a database
JDBC Component Interaction
1. getConnection() 2. createStatement()
Driver
Creates Connection Creates Statement Creates ResultSet
Manager

SQL
SQL
3a. select -> executeQuery() Driver
3b. Insert/Update/Delete Establish
executeUpdate() Link to DB Result
(tuples)

Database
STEPS Using JDBC
The following steps are executed when running a JDBC application
• Import the necessary classes (JDBC Packages – java.sql)
• Load the JDBC driver
• Identify the database source
• Obtain a “Connection” object (create) to the database (Create a Connection)
• Obtain a “Statement” object (create)
• Execute a query using the “Statement” object
• Updates, inserts and deletes return Boolean. Selects return a ResultSet
• Retrieve data from the returned “ResultSet” object
• Close the “ResultSet” object
• Close the “Statement” object
• Close the “Connection” object
Do NOT close the connection
The same connection object can be used to create further
statements

A Connection may only have one active Statement at a time. Do not

forget to close the statement when it is no longer needed.

Close the connection when you no longer need to access the

database
Driver names and Database URLs for popular RDBMS

RDBMS JDBC Driver Name Database URL Format

MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/databasename

ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port:databasename

DB2 com.ibm.db2.net.DB2Driver jdbc:db2:hostname:portno/databasename

Sybase com.sybase.jdbc.SybDriver jdbc:Sybase:Tds:hostname/databasename

MSACCESS sun.jdbc.odbc.JdbcOdbcDriver jdbc:odbc:databasename

JDBC Classes for DB Connection
 java.sql.Driver
◦ Unless creating custom JDBC implementation, never have to deal with
it. It gives JDBC a launching point for DB connectivity by responding to
DriverManager connection requests

 java.sql.DriverManager
◦ Maintains a list of Driver implementations and presents an application
with one that matches a requested URL.
◦ getConnection(url, uid, password)
◦ getDrivers(), registerDriver()

 java.sql.Connection
◦ Represents a single logical DB connection; used for sending SQL
statements
JDBC – Making the Connection

 Register the Driver implementation of the DB. JDBC requires a Driver

class to register itself with DriverManager when it is instantiated.
◦ Explicitly call new to load the driver (needs to be hardcoded)
◦ Or, use Class.forName(“DriverClass”)

 Establish a connection with the DB

Connection c = DriverManager.getConnection(url,
uid, password);
◦ DM searches the registered Drivers until it finds the match.
◦ The Driver class then establishes the connection, returns a
connection object to DM, which in turn returns it back.
JDBC – Database Access Classes

 java.sql.Statement
◦ Most basic class. It performs all the SQL statements
◦ executeQuery( String )
◦ executeUpdate( String )
◦ execute( String )

 java.sql.ResultSet
◦ One or more rows of data returned by a query
Statement st = c.createStatement();
ResultSet rs = st.executeQuery(“…”);
JDBC – Database Access Classes

◦ Methods: next(),getString(column),getInt(..),
last(), getRow()

◦ Be careful about SQL NULL and Java NULL

◦ Always use wasNull()

◦ Always call close() on all ResultSet, Statement, and

Connection objects. Some drivers (e.g., IBM’s native DB2) will
not close the rs and st objects even when you close the
connection.
JDBC – DB Access Classes (Cont.)
 java.sql.ResultSetMetaData
◦ Provides extra information about (data about data) the ResultSet object
◦ getColumnCount(), getColumnName(column)
ResultSetMetaData md = rs.getMetaData();

 java.sql.DatabaseMetaData
◦ Provides extra information about the database for a given connection object
◦ What tables exist, what username is being used, is the DB read-only, what are
the primary keys for a table, etc.
DatabaseMetaData dmd = c.getMetaData();
◦ Lets developers write apps that are DB-independent
Steps Involved in Basic JDBC Operations
Driver Manager
1. Load the JDBC driver class:
Class.forName(“driverName”); Driver
2. Open a database connection:
DriverManager.getConnection
(“jdbc:xxx:datasource”); Connection Database

3. Issue SQL statements:

stmt = con.createStatement();
stmt.executeQuery (“Select * from myTable”); Statement

4. Process result set:

while (rs.next()) {
name = rs.getString(“name”);
amount = rs.getInt(“amt”); }
Result Set
Type 1 3rd Party API

Database
Type 3 Type 2 Native C/C++ API

Local API
Network API
Type 4

• Type 1: Uses a bridging technology to access a database. JDBC-ODBC bridge is an example. It provides a gateway
to the ODBC.
• Type 2: Native API drivers. Driver contains Java code that calls native C/C++ methods provided by the database
vendors.
• Type 3: Generic network API that is then translated into database-specific access at the server level. The JDBC
driver on the client uses sockets to call a middleware application on the server that translates the client requests into
an API specific to the desired driver. Extremely flexible.
• Type 4: Using network protocols built into the database engine talk directly to the database using Java sockets.
Almost always comes only from database vendors.
JDBC STATEMENTS
⮚Once a connection is obtained we can interact with the
database.
⮚Following Interfaces define the methods and properties that
enable you to send SQL or PL/SQL commands and receive data
from your database.
1. The JDBC Statement

2. PreparedStatement

3. CallableStatement
1. JDBC STATEMENT(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,
Statement stmt = null;
try {
stmt = conn.createStatement( );
}
Catch(Exception e){
}
⮚Once you've created a Statement object, you can then use it to execute an SQL statement with
one of its three execute methods.
1. boolean execute (String SQL)
2. int executeUpdate (String SQL)
3. ResultSet executeQuery (String SQL)

⮚ Call stmt.close() method to close the statement

THE PREPAREDSTATEMENT OBJECTS

⮚Example to Create Prepare Statement object

PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1,30) ;
pstmt.setString(2,”M1016175”) ;
. . Pstmt.executeUpdate();
}
catch (SQLException e) {
}
CREATING PREPAREDSTATEMENT OBJECT

⮚Preparedstatement gives you the flexibility of supplying arguments dynamically.

⮚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
⮚The setYYY() methods bind values to the parameters ,where YYYrepresents the Java data type of the
value you wish to bind to the input parameter .
⮚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.
⮚Call pstmt.close() method to close the statement
DIFFERENCE BETWEEN STATEMENT AND PREPAREDSTATEMENT
DIFFERENCE BETWEEN STATEMENT AND PREPAREDSTATEMENT

Statement PreparedStatement Callabale Statement

Every time Sql query will be Sql query will be pre Stored procedures are pre
compiled compiled compiled and are saved as
database objects
It is Slower It is Faster It is Very Fast
Statements are used if we It is used if we are executing It is used if we are writing
are executing queries less queries many times complex queries which
often enable multiple tables.
CRUD OPERATIONS ON PRODUCT TABLE

CREATE PRODUCT TABLE AND PERFORM CRUD OPERATIONS ON PRODUCT TABLE

ProdId ProdName Quantity Price

10345 Patanjali Atta 10 85
1879 Kissan fruit Jam 20 65
1679 Maggi 100 12
int pid=s.nextInt();
String pname=s.next();
int qty=s.nextInt();
float price=s.nextFloat();

String sql=“Insert into product values(“+pid+”,’” +pname +”’,”+qty+”,”+price +”)”; //using

Ststement
St.execute(sql);

String sql=“Insert into product values(?,?,?,?)”; //Using PreparedStatement

Pstmt=con.prepareStatement(sql);
Pstmt.setInt(1,pid); pstmt.setString(2,pname); pstmt.setInt(3,qty);
pstmt.setFloat(4,price);
Pstmt.executeUpdate();
CRUD OPERATIONS

************************************************************
PRODUCT INFORMATION
************************************************************
1… Insert New Product
2… Update Existing Product
3… Search Product
4… Delete Product
5… Display All Products
STORED PROCEDURE

⮚A stored procedure is a subroutine available to applications that access

a relational database management system (RDBMS).
⮚A stored procedure is a prepared SQL code that you can save, so the code
can be reused over and over again.
⮚A Procedure is a subprogram unit that consists of a group of PL/SQL
statements. Each procedure in Oracle has its own unique name by which it
can be referred. This subprogram unit is stored as a database object.
The values can be passed into the procedure or fetched from the
procedure through parameters.
These parameters should be included in the calling statement.
PARAMETERS IN STORED PROCEDURE
IN Parameter:
This parameter is used for giving input to the subprograms.
It is a read-only variable inside the subprograms. Their values cannot be
changed inside the subprogram.
In the calling statement, these parameters can be a variable or a literal value
or an expression, for example, it could be the arithmetic expression like '5*8'
or 'a/b' where 'a' and 'b' are variables.
By default, the parameters are of IN type.
OUT Parameter:
•This parameter is used for getting output from the subprograms.
•It is a read-write variable inside the subprograms. Their values can be
changed inside the subprograms.
•In the calling statement, these parameters should always be a variable to
hold the value from the current subprograms.

IN OUT Parameter:
•This parameter is used for both giving input and for getting output from the
subprograms.
•It is a read-write variable inside the subprograms. Their values can be
changed inside the subprograms.
•In the calling statement, these parameters should always be a variable to
hold the value from the subprograms
PARAMETERS IN STORED PROCEDURE

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

⮚IN: You bind values to IN parameters with the setYYY() methods.

⮚OUT: You retrieve values from the OUT parameters with the getYYY()
methods
⮚INOUT: You bind variables with the setYYY() methods and retrieve
values with the getYYY() methods.
⮚Example
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;
END;
CREATE OR REPLACE PROCEDURE getName
(NO IN NUMBER, FIRST OUT VARCHAR) AS
BEGIN
SELECT Name INTO FIRST
FROM student
WHERE REGNO = NO;
END;
STORED PROCEDURE TO GET EMPLOYEE
DETAILS
create or replace PROCEDURE getEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
out_name OUT EMPLOYEE.NAME%TYPE,
out_role OUT EMPLOYEE.ROLE%TYPE,
out_city OUT EMPLOYEE.CITY%TYPE,
out_country OUT EMPLOYEE.COUNTRY%TYPE )
AS
BEGIN
SELECT NAME, ROLE, CITY, COUNTRY INTO out_name, out_role,
out_city, out_country FROM EMPLOYEE WHERE EMPID = in_id;
END;
STORED PROCEDURE TO INSERT EMPLOYEE
RECORD
CREATE OR REPLACE PROCEDURE BEGIN
insertEmployee
INSERT INTO EMPLOYEE (EMPID,
(in_id IN EMPLOYEE.EMPID%TYPE, NAME, ROLE, CITY, COUNTRY)
in_name IN values
EMPLOYEE.NAME%TYPE, (in_id,in_name,in_role,in_city
in_role IN EMPLOYEE.ROLE%TYPE, ,in_country); commit;

in_city IN EMPLOYEE.CITY%TYPE, out_result := 'TRUE';

EXCEPTION
in_country IN
EMPLOYEE.COUNTRY%TYPE, WHEN OTHERS THEN
out_result := 'FALSE';
out_result OUT VARCHAR2)
ROLLBACK;
AS
END;
CALLABLE STATEMENTS
⮚which would be used to execute a call to a database stored procedure
⮚Creating CallableStatement Object
⮚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);
//Bind IN parameter first, then bind OUT parameter
int empID = 102;
cstmt.setInt(1, empID); // This would set ID as 102
// Because second parameter is OUT so register it

cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);

//Use execute method to run stored procedure.

System.out.println("Executing stored procedure..." );

cstmt.execute();

//Retrieve employee name with getYYY method

String empName = cstmt.getString(2);

System.out.println("Emp Name with ID:" +

empID + " is " + empName);

cstmt.close();

conn.close();

} catch (SQLException e) {
⮚Using the CallableStatement objects is much like using the PreparedStatement objects. You
must bind values to all the parameters before executing the statement

⮚If you have IN parameters, just follow the same rules and techniques that apply to a
PreparedStatement object; use the setYYY() method

⮚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

⮚Once you call your stored procedure, you retrieve the value from the OUT parameter with
the appropriate getXXX() method
TRANSACTIONS AND JDBC
▪ JDBC allows SQL statements to be grouped together into a single transaction
A transaction is a unit of work that is performed against a database.
Transaction control is performed by the Connection object, default mode is auto-
commit mode, I.e., each sql statement is treated as a transaction
To enable manual- transaction ,We can turn off the auto-commit mode with
con.setAutoCommit(false);
And turn it back on with con.setAutoCommit(true);
Once auto-commit is off, no SQL statement will be committed until an explicit is
invoked con.commit();
At this point all changes done by the SQL statements will be made permanent in the
database.

50
TRANSACTIONS
⮚Transactions enable you to control if, and when, changes are applied to
the database
⮚All data base transactions are by default in auto-commit mode
⮚To enable manual- transaction support instead of the auto-commit
mode that the JDBC driver uses by default, use 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.
⮚Example:
⮚conn.setAutoCommit(false);
Transactions enable you to control if, and when, changes are applied to the
database
The following commands are used to control transactions.

⮚COMMIT − to save the changes. The COMMIT command saves all the transactions to the
database. Once you are done with your changes and you want to commit (Save) the changes
then call commit() method on connection object as follows
con.commit( );

ROLLBACK − to roll back the changes, used to undo transactions that have not already been
saved to the database. This command can only be used to undo transactions since the last
COMMIT or ROLLBACK command was issued.
con.rollback( );

SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK. A

SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point
without rolling back the entire transaction.
Transaction Successfull

commit

Initial State
rollback

Transaction failed
COMMIT, ROLLBACK AND SAVEPOINT
⮚If we enable manual transaction supportin the in SQL statement
execution then Commit, Rollback and Savepoint plays an important role
⮚Once you are done with your changes and you want to commit (Save)
the changes then call commit() method on connection object as follows
conn.commit( );
⮚to roll back updates to the database made using the Connection named
conn
conn.rollback( );
⮚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.

⮚There are 2 methods to use savepoint are

1. setSavepoint(String savepointName):
2. releaseSavepoint(Savepoint savepointName)

⮚ There is one rollback (String savepointName) method, which rolls

back work to the specified savepoint.
EXAMPLE
try{ stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
//Assume a valid connection object conn
String SQL = "INSERTED IN Employees " +
conn.setAutoCommit(false);
"VALUES (107, 22, 'Sita', 'Tez')";
Statement stmt = conn.createStatement(); stmt.executeUpdate(SQL);

//set a Savepoint // If there is no error, commit the changes.

conn.commit();
Savepoint savepoint1 =
conn.setSavepoint("Savepoint1"); }catch(SQLException se){
// If there is any error.
String SQL = "INSERT INTO Employees " +
conn.rollback(savepoint1);
"VALUES (106, 20, 'Rita', 'Tez')";
}
// Insert 3st record
String INSERT_SQL = "INSERT INTO employee (EMP_ID, NAME) VALUES
(?,?)";
insertStmt.setInt(1, 3);
try {
insertStmt.setString(2, “Neha");
Connection conn = DriverManager.getConnection(Url, username,
password); insertStmt.executeUpdate();
// Disable auto commit mode
conn.setAutoCommit(false);
// Insert 4st record
try {
insertStmt.setInt(1, 4);
PreparedStatement insertStmt =
conn.prepareStatement(INSERT_SQL); insertStmt.setString(2, "Manish");
// Insert 1st record insertStmt.executeUpdate();
insertStmt.setInt(1, 1);
insertStmt.setString(2, “Shwetha"); // Insert 5st record
insertStmt.executeUpdate(); insertStmt.setInt(1, 5);
// Insert 2st record insertStmt.setString(2, “Preeti");
insertStmt.setInt(1, 2);
insertStmt.executeUpdate();
insertStmt.setString(2, "Sunil");
insertStmt.executeUpdate();
// Rollback to savepoint
// Create Savepoint
conn.rollback(savepoint);
Savepoint savepoint = conn.setSavepoint();
String INSERT_SQL = "INSERT INTO employee (EMP_ID, NAME) VALUES (?,?)";
try {
Connection conn = DriverManager.getConnection(Url, username, password);
// Disable auto commit mode
conn.setAutoCommit(false);
try {
PreparedStatement insertStmt = conn.prepareStatement(INSERT_SQL);
// Insert 1st record
insertStmt.setInt(1, 1);
insertStmt.setString(2, “Shwetha");
insertStmt.executeUpdate();
// Insert 2st record
insertStmt.setInt(1, 2);
insertStmt.setString(2, "Sunil");
insertStmt.executeUpdate();
// Create Savepoint
Savepoint savepoint = conn.setSavepoint();
// Insert 3st record
insertStmt.setInt(1, 3);
insertStmt.setString(2, “Neha");
insertStmt.executeUpdate();

// Insert 4st record

insertStmt.setInt(1, 4);
insertStmt.setString(2, "Manish");
insertStmt.executeUpdate();

// Insert 5st record

insertStmt.setInt(1, 5);
insertStmt.setString(2, “Preeti");
insertStmt.executeUpdate();

// Rollback to savepoint
conn.rollback(savepoint);
BATCH PROCESSING

⮚Batch Processing allows you to group related SQL statements into a

batch and submit them with one call to the database
⮚ Sequence of steps to use batch processing with statement object
1.Create a Statement object using either createStatement() methods.
2.Set auto-commit to false using setAutoCommit().
3.Add as many as SQL statements you like into batch using addBatch()
method on created statement object.
4.Execute all the SQL statements using executeBatch() method on
created statement object.
5.Finally, commit all the changes using commit() method.
⮚ Example will be shown in next slide
// Create statement object
Statement stmt = conn.createStatement();
// Set auto-commit to false
conn.setAutoCommit(false);
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
"WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();