|| Jai Sri Gurudev |

Sri Adichunchanagiri Shikshana Trust (R)

SJB INSTITUTE OF TECHNOLOGY

Study Material

Subject Name: Advanced Java

Subject Code: BCS613D

By
Faculty Name:
Mrs. Rajani,Vijayalakshmi B
Designation: Assistant Professor
Semester: VI

Department of Computer Science & Engineering

Aca. Year: Even Sem /2025

Advanced Java BCS613D

Advanced Java: Module -5

The Concept of JDBC:
 Java was not considered industrial strength programming language since java was unable
to access the DBMS.
 Each dbms has its own way to access the data storage. low level code required to access
oracle data storage need to be rewritten to access db2.
 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
 JDBC drivers has to do the following
o Open connection between DBMS and J2EE environment.
o Translate low level equivalents of sql statements sent by J2EE component into
messages that can be processed by the DBMS.
o Return the data that conforms to JDBC specifications to the JDBC driver
o Return the error messages that conforms to JDBC specifications to the JDBC
driver
o Provides transaction management routines that conforms to JDBC specifications
to the JDBC driver
o Close connection between the DBMS and the J2EE component.

JDBC Architecture

JDBC Driver Types

 Type 1 driver JDBC to ODBC Driver

1. It is also called JDBC/ODBC Bridge , developed by MicroSoft.

2. It receives messages from a J2EE component that conforms to the
JDBC specifications.
3. Then it translates into the messages understood by the DBMS.
4. This is DBMS independent database program that is ODBC open
database connectivity.

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

 Advanced Java BCS613D

 Type 2 JAVA / Native Code Driver

1. Generates platform specific code that is code understood by platform
specific code only understood by specific databases.
2. Manufacturer of DBMS provides both java/ Native code driver.
3. Using this provides lost of portability of code.
4. It won’t work for another DBMS manufacturer

 Type 3 JDBC Driver

1. Most commonly used JDBC driver.
2. Coverts SQL queries into JDBC Formatted statements.
3. Then JDBC Formatted statements are translated into the format
required by the DBMS.
4. Referred as Java protocol

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

79
Advanced Java BCS613D

 Type 4 JDBC Driver

1. Referred as Type 4 database protocol
2. SQL statements are transferred into the format required by the DBMS.
3. This is the fastest communication protocol.

JDBC Packages
JDBC API contains two packages. First package is called java.sql, second package is
called javax.sql which extends java.sql for advanced JDBC features.

The various steps of the JDBC process with code snippets.

1. Loading the JDBC driver

• The jdbc driver must be loaded before the J2EE component can be connected
to the database.
• Driver is loaded by calling the method and passing the name of driver
Class.forName(“sun:jdbc.odbc.JdbcOdbcDriver”);

2. Connecting to the DBMS.

• Once the driver is loaded , J2EE component must connect to the DBMS using
DriverManager.getConnection() method.
• It is highest class in hierarchy and is responsible for managing driver
information.
• It takes three arguments URL, User, Password
• It returns connection interface that is used throughout the process to reference
a database
String url=”jdbc:odbc:JdbcOdbcDriver”;
String userId=”jim”
String password=”Keogh”;
Statement DataRequest; Private
Connection db;
try{
Class.forName(“sun:jdbc.odbc.JdbcOdbcDriver”);
Db=DriverManager.getConnection(url,userId,password);
}

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
3. Creating and Executing a statement.
• The next step after the JDBC is loaded and connection is successfully
made with a particular database managed by the dbms, is to end a
particular query to the DBMS for processing.
• SQL query consists series of SQL command that direct DBMS to do
something example Return rows.
• Connect.createStatement() method is used to create a statement Object.
• The statement object is then used to execute a query and return result
object that contain response from the DBMS
Statement DataRequest;
ResultSet Results;
try {
String query=“select * from Customers”;
DataRequest=Database.createStatement();
Results= DataRequests.executeQuery(query);
}
4. Processing data returned by the DBMS
• java.sql.ResultSet object is assigned the result received from the DBMS
after the query is processed.
• java.sql.ResultSet contain method to interact with data that is returned
by the DBMS to the J2EE Component.
Results= DataRequests.executeQuery(query);
do
{
Fname=Results.getString(Fname)
}
While(Results.next())
In the above code it return result from the query and executes the query. And getString
is used to process the String retrieved from the database.
5. Terminating the connection with the DBMS.
To terminate the connection Database.close() method is used.
getConnection() method.
o After the JDBC driver is successfully loaded and registered, the J2EE component
must connect to the database. The database must be associated with the JDBC
driver.
o The datasource that JDBC component will connect to is identified using the
URL format. The URL consists of three formats.
o These are jdbc which indicate jdbc protocol is used to read the URL.
<subprotocol> which is JDBC driver name.
<subname> which is the name of database.
Connection to the database is achieved by using one of three getConnection()
methods. It returns connection object otherwise returns SQLException.
Three getConnection() method are
getConnection(String url)
getConnection(String url, String pass, String user)
getConnection(String url, Properties prop)
1 getConnection(String url)
• Sometimes the DBMS grant access to a database to anyone that time
J2EE component uses getConnection(url) method is used.
String url=” jdbc:odbc:JdbcOdbcDriver ”;
try{
Class.forName(“sun:jdbc.odbc.JdbcOdbcDriver”);

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
Db=DriverManager.getConnection(url);}

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
2 getConnection(String url, String pass, String user)

• Database has limited access to the database to authorized user and requires
J2EE to supply userid and password with request access to the database.
try{
Class.forName(“sun:jdbc.odbc.JdbcOdbcDriver”);
Db=DriverManager.getConnection(url,userId,password);
}
3 getConnection(String url, Properties prop)
• There might be occasions when a DBMS require information besides
userid and password before DBMS grant access to the database.
• This additional information is called properties and that must be
associated with Properties object.
• The property is stored in text file. And then loaded by load method of
Properties class.
Connection db;
Properties props=new Properties();
try {
FileInputStream inputfile=new FileInputStream(“text.txt”);
Prop.load(inputfile);
}
Timeout:
 Competition to use the same database is a common occurrence in the J2EE
environment and can lead to performance degradation of J2EE application
 Database may not connect immediately delayed response because database may not
available.
 Rather than delayed waiting time J2EE component can stop connection after some
time. This time can bet set with the following method:
DriverManager.setLoginTimeout(int sec).
 DriverManager.getLoginTimeout(int sec) return the current timeout in seconds.

Connection Pool
 Client needs frequent that needs to frequently interact with database must either open
connection and leave open connection during processing or open or close and
reconnect each time.
 Leaving the connection may open might prevent another client from accessing the
database when DBS have limited no of connections. Connecting and reconnecting is
time consuming.
 The release of JDBC 2.1 Standard extension API introduced concept on connection
pooling
 A connection pool is a collection of database connection that are opened and loaded
into memory so these connections can be reused without reconnecting to the
database.
 DataSource interface is used to connect to the connection pool. Connection pool is
implemented in application server.
 There are two types of connection to the database 1.) Logical 2.) Physical
 The following code is used to connect to the connection pool.

Context ctext= new IntialContext()

DataSource pool =(DataSource)
ctext.lookup(“java:comp/env/jdbc/pool”); Connection
db=pool.getConnection();

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

Statement object.
 Statement object executes query immediately without precompiling.
 The statement object contains the excuteQuery() method , which accept query as
argument then query is transmitted for processing. It returns ResultSet as object.

o Example Program
String url=”jdbc:odbc:JdbcOdbcDriver”;
String userId=”jim”
String password=”Keogh”;
Statement DatRequest;
Private Connection db;
ResultSet rs;

// code to load driver

//code to connect to the database
try{
String query=”SELECT * FROM Customers;
DatRequest=Db.createStaement();
rs=DatRequest.executeQuery(query);// return result set object
}catch(SQLException err)
{
System.err.println(“Error”);
System.exit(1);
}
 Another method is used when DML and DDL operations are used for processing
query is executeUpdate(). This returns no of rows as integer.
try{
String query=”UPDATE Customer set PAID=’Y’ where BALANCE =’0’;
DatRequest=Db.createStaement();
int n=DatRequest.executeUpdate(query);// returns no of rows updated
}catch(SQLException err)
{
System.err.println(“Error”);
System.exit(1);
}

Prepared statement object.

 A SQL query must be compiled before DBMS processes the query. Query is
precompiled and executed using prepared statements.
String query=”SELECT * FROM Customers where cno=?”;
 Question mark is placed as the value of the customer number. The value will be
inserted into the precompiled query later in the code.
 Setxxx() is used to replace the question mark with the value passed to the setxxx()
method . xxx represents data type of the field.
Example if it is string then setString() is used.It takes two arguments one is position of
question mark and other is value to the filed.
 This is referred as late binding.
String url=”jdbc:odbc:JdbcOdbcDriver”;
String userId=”jim”
String password=”Keogh”;

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

ResultSet rs;

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
// code to load driver
//code to connect to the database
try{
String query=”SELECT * FROM Customers where cno=?”;
PreparedStatement pstatement=db.preparedStatement(query);
pstatement.setString( 1,”123”); // 1 represents first place holder, 123 is value
rs= pstatement.executeQuery();
}catch(SQLException err)
{
System.err.println(“Error”);
System.exit(1);
}

Callable statement object.

 The callableStatement object is used to call a stored procedure from within
J2EE object. A stored procedure is block of code and is identified by unique
name. The code can be written in Transact-C ,PL/SQL.
 Stored procedure is executed by invoking by the name of procedure.
 The callableStatement uses three types of parameter when calling stored
procedure. The parameters are IN ,OUT,INOUT.
 IN parameter contains data that needs to be passed to the stored procedure
whose value is assigned using setxxx() method.
 OUT parameter contains value returned by stored procedure. The OUT
parameter should be registers by using registerOutParameter() method and then
later retrieved by the J2EE component using getxxx() method.
 INOUT parameter is used to both pass information to the stored procedure
and retrieve the information from the procedure.
 Suppose, you need to execute the following Oracle stored procedure:

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;

 The following code snippets is used

CallableStatement cstmt = null;

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

 Using CallableStatement objects is much like using PreparedStatement objects. You must
bind values to all 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; use the setXXX() method that corresponds to the Java data type
you are binding.
 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 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. This method casts the retrieved value of SQL type to a
Java data type.

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

ResultSet
 ResultSet object contain the methods that are used to copy data from ResultSet into java
collection object or variable for further processing.
 Data in the ResultSet is logically organized into the virtual table for further processing.
Result set along with row and column it also contains metadata.
 ResultSet uses virtual cursor to point to a row of the table.
 J2EE component should use the virtual cursor to each row and the use other methods of
the ResultSet to object to interact with the data stored in column of the row.
 The virtual cursor is positioned above the first row of data when the ResultSet is returned
by executeQuery() method.
 The virtual cursor is moved to the first row with help of next() method of ResultSet
 Once virtual cursor is positioned getxxx() is used to return the data. Data type of data is
represents by xxx. It should match with column data type.
 getString(fname) …..fname is column name.
 setString(1) ............... in this 1 indicates first column selected by query.

stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");/ / rs.getInt(1);
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
Scrollable Result Set
 Until the release of JDBC 2.1 API , the virtual cursor can move only in forward
directions. But today the virtual cursor can be positioned at a specific row.
 There are six methods to position the cursor at specific location in addition to next() in
scrollable result set. firs() ,last(), absolute(), relative(), previous(), and getRow().
 first() ................. position at first row.
 last() ............... position at last row.
 previous() ............ position at previous row.
 absolute().............. To the row specified in the absolute function
 relative() .................move relative to current row. Positive and negative no can be given.
Ex. relative(-4) … 4 position backward direction.
 getRow() ............. returns the no of current row.
 There are three constants can be passed to the createStatement()
 Default is TYPE_FORWARD_ONLY. Otherwise three constant can be passed to the
create statement
1) TYPE_SCROLL_INSENSITIVE: makes changes made by J2EE component will
not reflect.
2) TYPE_SCROLL_SENSITIVE means changes by J2EE will reflect in the result set.

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
3) TYPE_SCROLL makes cursor to move both direction. Example code.

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D


String sql=” select * from emp”;

DR=Db.createStatement(TYPE_SCROLL_INSENSITI
VE); RS= DR.executeQuery(sql);
 Now we can use all the methods of ResultSet.

updatable Result Set.

 Rows contained in the result set is updatable similar to how rows in the table can
be updated. This is possible by sending CONCUR_UPDATABLE.
 There are three ways in which result set can be changed. These are updating row ,
deleting a row, inserting a new row.
1. Update ResultSet
• Once the executeQuery() method of the statement object returns a
result set. updatexxx() method is used to change the value of
column in the current row of result set.
• It requires two parameters, position of the column in query. Second
parameter is value
• updateRow() method is called after all the updatexxx() methods
are called.
Example:
try{
String query= “select Fname, Lname from Customers
where Fname= ‘Mary’ and Lanme=’Smith’;
DataRequest= Db.
createStatement(ResultSet.CONCUR_UPDATABLE);
Rs= DataRequest.executeQuery(query);
Rs.updateString(“LastName”,”Smith”);
Rs.updateRow();
}
2. Delete row in result set
 By using absolute method positioning the virtual cursor and calling
deleteRow(int n) n is the number of rows to be deleted.
 Rs.deleteRow(0) current row is deleted.
3. Insert Row in result set
 Once the executeQuery() method of the statement object returns
a result set. updatexxx() method is used to insert the new row of
result set.
 It requires two parameters, position of the column in query. Second
parameter is value
 insertRow() method is called after all the updatexxx()
methods are called.
try{
String query= “select Fname, Lname from Customers
where Fname= ‘Mary’ and Lanme=’Smith’;
DataRequest= Db.
createStatement(ResultSet.CONCUR_UPDATABLE);
Rs= DataRequest.executeQuery(query);
Rs.updateString(1 ,”Jon”);
Rs.updateString(2 ,”Smith”);
Rs.insertRow();
}
 Whatever the changes making will affect only in the result set not in the table. To
update in the table have to execute the DML( update, insert, delete) statements.

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

 A transaction may consist of a set of SQL statements, each of which must be

successfully completed for the transaction to be completed. If one fails SQL statements
successfully completed must be rolled back.
 Transaction is not completed until the J2EE component calls the commit() method of the
connection object. All SQL statements executed prior to the call to commit() method can
be rolled back.
 Commit() method was automatically called in the program. DBMS has set AutoCommit
feature.
 If the J2EE component is processing a transaction then it has to deactivate the
autocommit() option false.
try {
DataBase.setAutoCommit(false)
String query=”UPDATE Customer set Street =’5 main Street’
“+ “WHERE FirstName =’Bob’ ”;
DR= DataBase.createStatement();
DataRequest=DataBase.createStatement();
DataRequest.executeUpdate(query1);
DataBase.commit();
}
 Transaction can also be rolled back using Db.rollback() method.
 A transaction may consist of many tasks, some of which no need to roll back in such
situation we can create a savepoints, in between transactions. It was introduced in JDBC
3.0. Savepoints are created and then passed as parameters to rollback() methods.
 releseSavepint() is used to remove the savepoint from the transaction.
 Savepoint s1=DataBase.setSavePoint(“sp1”);to create the savepoint.
 Database. rollback (sp1); to rollback the transaction.

Batch Execution of transaction

 Another way to combine sql statements into a single transaction and then execute the
entire transaction.
 The addBatch() method receives a SQL statement as a parameter and places the SQL
statement in the batch.
 executeBatch() method is called to execute the entire batch at the same time. It returns an
array that contains SQL statements that execute successfully.
String query1=”UPDATE Customers SET street =’ 5th Main’” +
“Where Fname=’BoB’ “;
String query2=”UPDATE Customers SET street =’ 10th Main’” +
“Where Fname=’Tom’ “;
Statement DR=DB.createStatement ();

DR.addBatch(query1);
DR.addBatch(query2);
int [] updated= DR.executeBatch();

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

 Metadata is data about data. Metadata is accessed by using the

DatabaseMetaData interface.
 This interface is used to return the metadata information about database.
 Meta data is retrieved by using getMetaData() method of connection object.
Database metadata
The method used to retrieve metadata information’s are
 getDatabaseProductNAme()…returns the product name of database.
 getUserNAme() returns the username()
 getURL() returns the URL of the database.
 getSchemas() returns all the schema name
 getPrimaryKey() returns primary key
 getTables() returns names of tables in the database
ResultSet Metadata
 ResultSetMetaData rm=Result.getMetaData()
The method used to retrieve metadata information about resultset.
 getColumnCount() returns the number of columns contained in resultset

Data types of Sql used in setXXX() and getXXX() methods.

SQL JDBC/Java

VARCHAR java.lang.String

CHAR java.lang.String

LONGVARCHAR java.lang.String

BIT boolean

NUMERIC java.math.BigDecimal

TINYINT byte

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D

SMALLINT short

INTEGER int

BIGINT long

REAL float

FLOAT float

DOUBLE double

VARBINARY byte[ ]

BINARY byte[ ]

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Timestamp

CLOB java.sql.Clob

BLOB java.sql.Blob

ARRAY java.sql.Array

REF java.sql.Ref

Exceptions handling with jdbc

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 thrown means that
current program execution stops, and control is redirected to the nearest applicable catch
clause. If no applicable catch clause exists, then the program's execution ends.
 JDBC Exception handling is very similar to Java Exception handling but for JDBC There
are three kind of exception thrown by JDBC methods.
SQLException, SQLWarnings, Data Truncation

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
1. SQLException

The most common exception you'll deal with is java.sql.SQLException which result in
SQL syntax errors.

 getNextException() method returns details about the error.

 getErrorCode() method retrieves vendor specific error codes.

2. SQLWarnings
It throws warnings related to connection from DBMS.
getWarnings() method of connection object retrieves SQLwarnings.
getNextWarnings() returns subsequent warnings.

3. Data Truncation
Whenever data is lost due to truncation of the data value, a truncation exception is
thrown.

Differentiate between a Statement and a PreparedStatement.

• A standard Statement is used for creating a Java representation for a literal SQL statement
and for executing it on the database.
• A PreparedStatement is a precompiled Statement.
• A Statement has to verify its metadata in the database every time.
• But ,the prepared statement has to verify its metadata in the database only once.
• If we execute the SQL statement, it will go to the STATEMENT.
• But, if we want to execute a single SQL statement for the multiple number of times, it’ll go to
the PreparedStatement.

Write a program to connect to database with the following information

Drive: JDBC/ODBC bridge URL: &quot;jdbc.odbc:Ex&quot;
Username: &quot;xyz&quot;
Password: &quot;123&quot;
Retrieve all rows with marks &gt; 60 using prepared statement object.
Assume following table:
Table Name : STUDENT
Fields: USN-Varchar (20)
Marks-int
Name-Varchar (25)
import java.sql.*;
public class RetrieveDataExample {
public static void main(String[] args) {
// JDBC driver name and database URL
String JDBC_DRIVER = &quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;;
String DB_URL = &quot;jdbc:odbc:Ex&quot;;
// Database credentials
String USER = &quot;xyz&quot;;
String PASS = &quot;123&quot;;
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// Register JDBC driver
Class.forName(JDBC_DRIVER);
// Open a connection
System.out.println(&quot;Connecting to database...&quot;);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// Prepare SQL query to retrieve data with marks &gt; 60
Dept of CSE,SJBIT Rajani,Vijayalakshmi B
Advanced Java BCS613D
String sql = &quot;SELECT USN, Name, Marks FROM STUDENT WHERE Marks &gt;
?&quot;;
stmt = conn.prepareStatement(sql);
stmt.setInt(1, 60);
// Execute the query
rs = stmt.executeQuery();
// Process the result set
while (rs.next()) {
// Retrieve by column name
String usn = rs.getString(&quot;USN&quot;);
String name = rs.getString(&quot;Name&quot;);
int marks = rs.getInt(&quot;Marks&quot;);

// Display values
System.out.print(&quot;USN: &quot; + usn);
System.out.print(&quot;, Name: &quot; + name);
System.out.println(&quot;, Marks: &quot; + marks);
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
} finally {
// Close resources
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println(&quot;Finished.&quot;);
}
}
classes, interface , methods available in java.sql.* package.
Java.sql.package include classes and interface to perform almost all JDBC operation
such as creating and executing SQL queries

1. java.sql.BLOB -------- provide support to BLOB SQL data type.

2. java.sql.Connection ------ creates connection with specific data type
Methods in Connection
setSavePoint()
rollback()
commit()
setAutoCommit()
3. java.sql.CallableStatement--------- Executes stored
procedures
Methods in CallableStatement
execute()
registerOutParameter()
4. java.sql.CLOB ------------- support for CLOB data type.
5. java.sql.Date -------------support for Date SQL type.
6. Java.sql.Driver ----- create instance of driver with the DriverManager
7. java.sql.DriverManager ---- manages the data base driver
getConnection()
setLoginTimeout()

Dept of CSE,SJBIT Rajani,Vijayalakshmi B

Advanced Java BCS613D
getLoginTimeout()
8. java.sql.PreparedStatement—create parameterized query
executeQuery()
executeUpdate()
9. java.sql.ResultSet ------------- it is interface to access result row by row
rs.next()
rs.last()
rs.first()
10. java.sql.Savepoint ------- Specify savepoint in transaction.
11. java.sql.SQLException ---------- Encapsulates JDBC related exception.
12. java.sql.Statement ............... interface used to execute SQL statement.
13. java.sql.DataBaseMetaData ....... returns mata data

Dept of CSE,SJBIT Rajani,Vijayalakshmi B