G H Raisoni Institute of

Business Management, Jalgaon

Department of Computer Application

Department of Computer Application

Academic Year: 2022-23
Third Semester

Course Code: BCA CEC-1A

Course Name: Advanced Java

Prepared By: Prof. Kavita K. Ingale

1
CHAPTER 1
JDBC: Java Data Base Connectivity
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.
The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated
with database usage.
 Making a connection to a database.
 Creating SQL or MySQL statements.
 Executing SQL or MySQL queries in the database.
 Viewing & Modifying the resulting records.
Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for
portable access to an underlying database. Java can be used to write different types of executables,
such as −
 Java Applications
 Java Applets
 Java Servlets
 Java ServerPages (JSPs)
 Enterprise JavaBeans (EJBs).
All of these different executables are able to use a JDBC driver to access a database, and take
advantage of the stored data.
JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-
independent code.

JDBC Architecture
The JDBC API supports both two-tier and three-tier processing models for database access but in
general, JDBC Architecture consists of two layers −
 JDBC API − This provides the application-to-JDBC Manager connection.
 JDBC Driver API − This supports the JDBC Manager-to-Driver Connection.
The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity
to heterogeneous databases.
The JDBC driver manager ensures that the correct driver is used to access each data source. The
driver manager is capable of supporting multiple concurrent drivers connected to multiple
heterogeneous databases.
Following is the architectural diagram, which shows the location of the driver manager with respect
to the JDBC drivers and the Java application −

2
Common JDBC Components
The JDBC API provides the following interfaces and classes −
 DriverManager − This class manages a list of database drivers. Matches connection requests
from the java application with the proper database driver using communication sub protocol.
The first driver that recognizes a certain subprotocol under JDBC will be used to establish a
database Connection.
 Driver − This interface handles the communications with the database server. You will interact
directly with Driver objects very rarely. Instead, you use DriverManager objects, which
manages objects of this type. It also abstracts the details associated with working with Driver
objects.
 Connection − This interface with all methods for contacting a database. The connection object
represents communication context, i.e., all communication with database is through
connection object only.
 Statement − You use objects created from this interface to submit the SQL statements to the
database. Some derived interfaces accept parameters in addition to executing stored
procedures.
 ResultSet − These objects hold data retrieved from a database after you execute an SQL query
using Statement objects. It acts as an iterator to allow you to move through its data.
 SQLException − This class handles any errors that occur in a database application.

Types of 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)

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

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

Steps to JDBC Connectivity

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
1. public static void forName(String className)throws ClassNotFoundException
Example to register the OracleDriver class
Here, Java program is loading oracle driver to esteblish database connection.
1. 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
1) public static Connection getConnection(String url)throws SQLException
1. 2) public static Connection getConnection(String url,String name,String password)
2. throws SQLException
Example to establish connection with the Oracle database
1. Connection con=DriverManager.getConnection(
2. "jdbc:oracle:thin:@localhost: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
1. public Statement createStatement()throws SQLException
Example to create the statement object
1. 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
1. ResultSet rs=stmt.executeQuery("select * from emp");
2. while(rs.next()){
3. System.out.println(rs.getInt(1)+" "+rs.getString(2));
4. }

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
1. public void close()throws SQLException
Example to close connection
1. con.close();

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 informations 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/mydb 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.
Example to Connect Java Application with mysql database
In this example, mydb is the database name, root is the username and password both.
import java.sql.*;
public class mysqlconn {
public static void main(String[] args) {
// TODO Auto-generated method stub
try{
//Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb","root","");
System.out.println("Connection succesfull");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from student");
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.
download the jar file mysql-connector.jar
and set classpath in eclipse
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.
They also define methods that help bridge data type differences between Java and SQL data types
used in a 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.
 PreparedStatement Use this when you plan to use the SQL statements many 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( );
...
}
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 or when
you need to use truly dynamic SQL.
 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.
A simple call to the close() method will do the job. 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 cleanup.
Statement stmt = null;
try {
stmt = conn.createStatement( );
...
}
For a better understanding, we suggest you to study the Statement - Example tutorial.
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);
...
}
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 setXXX() methods bind values to the parameters, where XXX represents the Java data type of
the value you wish to bind to the input parameter. 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 cleanup.
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
...
}
For a better understanding, let us study Prepare - Example Code.

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.

Compiled By: Vinod Mahajan

Creating CallableStatement Object
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;
NOTE − Above stored procedure has been written for Oracle, but we are working with MySQL
database so, let us write same stored procedure for MySQL as follows to create it in EMP database −
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. You
bind values to IN parameters with the setXXX() methods.
OUT A parameter whose value is supplied by the SQL statement it returns. You
retrieve values from theOUT parameters with the getXXX() methods.
INOUT A parameter that provides both input and output values. You bind variables with
the setXXX() methods and retrieve values with the getXXX() methods.
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);
...
}
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; 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 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. This method casts the retrieved value of SQL type to a Java data type.

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);
...
}

Result Set
Java ResultSet interface is a part of the java.sql package. It is one of the core components of
the JDBC Framework. ResultSet Object is used to access query results retrieved from the relational
databases.
ResultSet maintains cursor/pointer which points to a single row of the query results. Using
navigational and getter methods provided by ResultSet, we can iterate and access database records
one by one. ResultSet can also be used to update data.
Java ResultSet Hierarchy

Java ResultSet Class Hierarchy

The above diagram shows the place of ResultSet in the JDBC Framework. ResultSet can be obtained
by executing SQL Query using Statement, PreparedStatement or CallableStatement.
AutoCloseable, Wrapper are super interfaces of ResultSet. Now we will see how to work with
ResultSet in our Java programs.
ResultSet Example
We will be using MySQL for our example purpose. Use below DB script to create a database and
table along with some records.
create database empdb;
use empdb;
create table tblemployee (empid integer primary key, firstname varchar(32), lastname
varchar(32), dob date);
insert into tblemployee values (1, 'Mike', 'Davis',' 1998-11-11');
insert into tblemployee values (2, 'Josh', 'Martin', '1988-10-22');
insert into tblemployee values (3, 'Ricky', 'Smith', '1999-05-11');
Let’s have look at the below example program to fetch the records from the table and print them
on the console. Please make sure you have the MySQL JDBC driver in the project classpath.
package com.journaldev.examples;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

public class ResultSetDemo {

public static void main(String[] args) {

String query = "select empid, firstname, lastname, dob from tblemployee";
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn =
DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb", "root", "root");
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
 Integer empId = rs.getInt(1);
String firstName = rs.getString(2);
String lastName = rs.getString(3);
Date dob = rs.getDate(4);
System.out.println("empId:" + empId);
System.out.println("firstName:" + firstName);
System.out.println("lastName:" + lastName);
System.out.println("dob:" + dob);
System.out.println("");
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {}
}
}
}
Copy
Output:
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11

empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22

empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11

Explanation:
 ResultSet is obtained by calling the executeQuery method on Statement instance. Initially,
the cursor of ResultSet points to the position before the first row.
 The method next of ResultSet moves the cursor to the next row. It returns true if there is
further row otherwise it returns false.
 We can obtain data from ResultSet using getter methods provided by it.
e.g. getInt(), getString(), getDate()
 All the getter methods have two variants. 1st variant takes column index as Parameter and
2nd variant accepts column name as Parameter.
  Finally, we need to call close method on ResultSet instance so that all resources are cleaned
up properly.

ResultSet Types & Concurrency

We can specify type and concurrency of ResultSet while creating an instance of
Statement, PreparedStatement or CallableStatement.
statement.createStatement(int resultSetType, int resultSetConcurrency)
ResultSet Types
1) Forward Only (ResultSet.TYPE_FORWARD_ONLY)
This type of ResultSet instance can move only in the forward direction from the first row to the last
row. ResultSet can be moved forward one row by calling the next() method. We can obtain this
type of ResultSet while creating Instance of Statement, PreparedStatement or CallableStatement.
Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

2) Scroll Insensitive (ResultSet.TYPE_SCROLL_INSENSITIVE)

Scroll Insensitive ResultSet can scroll in both forward and backward directions. It can also be
scrolled to an absolute position by calling the absolute() method. But it is not sensitive to data
changes. It will only have data when the query was executed and ResultSet was obtained. It will not
reflect the changes made to data after it was obtained.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

3) Scroll Sensitive (ResultSet.TYPE_SCROLL_SENSITIVE)

Scroll Sensitive ResultSet can scroll in both forward and backward directions. It can also be scrolled
to an absolute position by calling the absolute() method. But it is sensitive to data changes. It will
reflect the changes made to data while it is open.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from tbluser");

ResultSet Concurrency
1) Read Only (ResultSet.CONCUR_READ_ONLY)
It is the default concurrency model. We can only perform Read-Only operations on ResultSet
Instance. No update Operations are allowed.
2) Updatable (ResultSet.CONCUR_UPDATABLE)
In this case, we can perform update operations on ResultSet instance.
Commonly used methods of ResultSet interface
1) public boolean next(): is used to move the cursor to the one row
next from the current position.
2) public boolean previous(): is used to move the cursor to the one row
previous from the current position.
3) public boolean first(): is used to move the cursor to the first row in
result set object.
4) public boolean last(): is used to move the cursor to the last row in
result set object.
5) public boolean absolute(int row): is used to move the cursor to the specified
row number in the ResultSet object.
6) public boolean relative(int row): is used to move the cursor to the relative row
number in the ResultSet object, it may be
positive or negative.
7) public int getInt(int columnIndex): is used to return the data of specified column
index of the current row as int.
8) public int getInt(String columnName): is used to return the data of specified column
name of the current row as int.
9) public String getString(int columnIndex): is used to return the data of specified column
index of the current row as String.
10) public String getString(String columnName): is used to return the data of specified column
name of the current row as String.

******