Chapter 3: Java Database Connectivity Advanced Java
Chapter 3
Java Database Connectivity
Introduction
JDBC stands for Java Database Connectivity is a Java API (Application
Programming Interface) used to interact with databases. JDBC is a specification from Sun
Microsystems and it is used by Java applications to communicate with relational databases.
We can use JDBC to execute queries on various databases and perform operations like
SELECT, INSERT, UPDATE and DELETE.
JDBC API helps Java applications interact with different databases like MSSQL,
ORACLE, MYSQL, etc. It consists of classes and interfaces of JDBC that allow the
applications to access databases and send requests made by users to the specified database.
Purpose of JDBC
Java programming language is used to develop enterprise applications. These
applications are developed with intention of solving real-life problems and need to interact
with databases to store required data and perform operations on it. Hence, to interact with
databases there is a need for efficient database connectivity, ODBC (Open Database
Connectivity) driver is used for the same.
ODBC is an API introduced by Microsoft and used to interact with databases. It can
be used by only the windows platform and can be used for any language like C, C++, Java,
etc. ODBC is procedural.
JDBC is an API with classes and interfaces used to interact with databases. It is used
only for Java languages and can be used for any platform. JDBC is highly recommended
for Java applications as there are no performance or platform dependency problems.
Architecture of JDBC
Major components of JDBC architecture are as follows:
Application
The JDBC API
DriverManager
JDBC Drivers
Data Sources
Tanaji Kharbad 1
Chapter 3: Java Database Connectivity Advanced Java
Let's take a look at the JDBC architecture in java.
Application
Applications in JDBC architecture are java applications like applets or servlet that
communicates with databases.
JDBC API
The JDBC API is an Application Programming Interface used to create Databases.
JDBC API uses classes and interfaces to connect with databases. Some of the important
classes and interfaces defined in JDBC architecture in java are the DriverManager class,
Connection Interface, etc.
DriverManager
DriverManager class in the JDBC architecture is used to establish a connection
between Java applications and databases. Using the getConnection method of this class a
connection is established between the Java application and data sources.
JDBC Drivers
JDBC drivers are used to connecting with data sources. All databases like Oracle,
MSSQL, MYSQL, etc. have their drivers, to connect with these databases we need to load
their specific drivers. Class is a java class used to load drivers. [Link]() method is
used to load drivers in JDBC architecture.
Data Sources
Data Sources in the JDBC architecture are the databases that we can connect using this
API. These are the sources where data is stored and used by Java applications. JDBC API
helps to connect various databases like Oracle, MYSQL, MSSQL, PostgreSQL, etc.
Tanaji Kharbad 2
Chapter 3: Java Database Connectivity Advanced Java
Types of JDBC Architecture
The JDBC Architecture can be of two types based on the processing models it uses.
1. Two-tier model
Two-tier JDBC architecture model is a basic model. In this model, a java application
communicates directly to the data sources. JDBC driver is used to establish a connection
between the application and the data source. When an application needs to interact with a
database, a query is directly executed on the data source and the output of the queries is
sent back to the user in form of results. In this model, the data source can be located on a
different machine connected to the same network the user is connected to. This model is
also known as a client/server configuration. Here user's machine acts as a client and the
machine on which the database is located acts as a server.
2. Three-tier model
Tanaji Kharbad 3
Chapter 3: Java Database Connectivity Advanced Java
Three-tier model is a complex and more secure model of JDBC architecture in java.
In this model the user queries are sent to the middle tier and then they are executed on the
data source. Here, the java application is considered as one tier connected to the data source
(3rd tier) using middle-tier services (2nd tier). In this model user queries are sent to the data
source using middle-tier services, from where the commands are again sent to databases
for execution. The results obtained on the database are again sent to the middle-tier and
then to the user/application.
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:
easy to use.
can be easily connected to any database.
Disadvantages:
Performance degraded because JDBC method call is converted into the
ODBC function calls.
The ODBC driver needs to be installed on the client machine.
Tanaji Kharbad 4
Chapter 3: Java Database Connectivity Advanced Java
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:
Performance upgraded than JDBC-ODBC bridge driver.
Disadvantage:
The Native driver needs to be installed on the each client machine.
The Vendor client library needs to be installed on client machine.
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:
No client side library is required.
Disadvantages:
Network support is required on client machine.
Requires database-specific coding to be done in the middle tier.
Maintenance of Network Protocol driver becomes costly because it requires
database-specific coding to be done in the middle tier.
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:
Better performance than all other drivers.
No software is required at client side or server side.
Disadvantage:
Drivers depend on the Database.
Tanaji Kharbad 5
Chapter 3: Java Database Connectivity Advanced Java
Working of JDBC (Steps for Database Connection)
Java applications need to be programmed for interacting with data sources. JDBC
Drivers for specific databases are to be loaded in a java application for JDBC support which
can be done dynamically at run time. These JDBC drivers communicate with the respective
data source.
There are 5 steps to connect any java application with the database using JDBC.
These steps are as follows:
1. Register Driver:
Register the JDBC Driver for specific databases using forName() method of class
Class. The forName() method throws ClassNotFoundException, so we have to handle this
exception using try catch block.
Syntax:
public static void forName(String className)throws ClassNotFoundException
Example:
[Link]("[Link]");
2. Create Connection:
Create a connection with a database using DriverManager class. We use
getConnection() method of DriverManager class. The getConnection() method requires
three parameters. These parameters are url, user name and passpword. This
getConnection() method throws SQLException, so we have to handle this exception using
try catch block.
Syntax:
public static Connection getConnection(String url,String name,String password)
throws SQLException
Example:
String url=”jdbc:mysql://localhost:3306/student_db”;
String uname=”root”;
String pwd=”cocsit”;
Connection con=[Link](url,uname,pwd);
Tanaji Kharbad 6
Chapter 3: Java Database Connectivity Advanced Java
3. Create Statement (SQL Query):
To manipulate the database we need to create a query using commands like
INSERT, UPDATE, DELETE, etc. These queries are created and stored in string format.
Example:
String query = "INSERT INTO Stud_tb values(1,'ABC','Latur')";
The query we have created is in the form of a string. To perform the operations in
the string on a database we need to fire that query on the database. To achieve this, we need
to convert a string object into SQL statements. This can be done using Statement or
PreparedStatement or CallableStatement interfaces.
Example:
Statement smt = [Link]();
4. Execute Statement:
To execute SQL statements on the database we can use three methods depending on
which type of query we are executing.
executeUpdate(): This method is used when the query is not going to return any value. To
execute queries like insert, update, delete, etc., we use the executeUpdate() method. Return
type of executeUpdate() method is int.
Example:
int count = [Link](“Insert into stud_tb values(2,’ABC’,’Pune’)”);
executeQuery(): This method is used to execute queries used to display data from the
database, such as select. Return type of executeQuery() method is ResultSet.
Example:
ResultSet rs= [Link](“Select * from stud_tb where rno=2”);
execute(): This method is used to execute stored procedures from the database. Return type
of execute() method is Object.
Tanaji Kharbad 7
Chapter 3: Java Database Connectivity Advanced Java
5. Closing Statement:
After performing operations on the database, it is better to close every interface
object to avoid further conflicts.
Example:
[Link]();
[Link]();
[Link]();
Let's see how to connect a Java program using JDBC API. We will access data from
database. Database is maintained by using MySQL. Name of database is student_db. Inside
database we have created a table named stud_tb. Inside stud_tb, there are three columns
named rno, sname and saddress respectively. The following code will access data based on
given roll no.
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
class DemoAccessData
{
public static void main(String args[])
{
try
{
int rno=[Link](args[0]);
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="cocsit";
[Link]("[Link]");
Connection con=[Link](url,uname,pwd);
Statement smt=[Link]();
ResultSet rs=[Link]("select * from stud_tb where rno=2");
if([Link]())
Tanaji Kharbad 8
Chapter 3: Java Database Connectivity Advanced Java
{
[Link](“Roll No = ”+[Link](1));
[Link](“Name = ”+rs. getString (2));
[Link](“Address = ”+rs. getString (3));
}
else
{
[Link]("Record not found");
}
[Link]();
[Link]();
[Link]();
}
catch(ClassNotFoundException ex)
{
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
}
Prepared Statement
Prepared Statement is a pre-compiled SQL statement. It is a sub-interface of the
statement in java. It has valuable features which are in addition to that of objects in a
statement. The object of the Prepared Statement has the feature of executing parameterized
queries instead of hard coding.
Initially this statement uses place holders “?” instead of parameters, later on you can
pass arguments to these dynamically using the setXXX() methods of the
PreparedStatement interface.
Tanaji Kharbad 9
Chapter 3: Java Database Connectivity Advanced Java
Creating a PreparedStatement
You can create an object of the PreparedStatement (interface) using the
prepareStatement() method of the Connection interface. This method accepts a query
(parameterized) and returns a PreparedStatement object. When you invoke this method the
Connection object sends the given query to the database to compile and save it. If the query
got compiled successfully then only it returns the object. To compile a query, database
doesn’t require any values so you can use (zero or more) placeholders (Question marks ”
?”) in the place of values in the query.
The example of a parameterized query:
String query="insert into stud_tb values(?,?,?)";
PreparedStatement pst=[Link](query);
Setting values to the place holders
The PreparedStatement interface provides several setter methods such as setInt(),
setFloat(), setArray(), setDate(), setDouble(), setString() etc.. to set values to the place
holders(question mark) of the prepared statement. These methods accept two arguments,
one is an integer value representing the placement (question mark) index of the place folder
and, the other is an int or, String or, float etc… representing the value you need to insert at
that particular position. You can set values to the place holders of the above created
statement using the setter methods as shown below:
[Link](1,5);//setting roll no
[Link](2,”Tanaji”);//setting student name
[Link](3,”Latur”);//setting student address
Let's see complete example to insert data inside table named stud_tb by using
PreparedStatement.
import [Link];
import [Link];
import [Link];
import [Link];
class DemoInsertData
{
public static void main(String args[])
Tanaji Kharbad 10
Chapter 3: Java Database Connectivity Advanced Java
{
try
{
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="cocsit";
String query="insert into stud_tb values(?,?,?)";
[Link]("[Link]");
Connection con=[Link](url,uname,pwd);
PreparedStatement pst=[Link](query);
[Link](1,5);//setting roll no
[Link](2,”Tanaji”);//setting student name
[Link](3,”Latur”);//setting student address
int count=[Link]();
if(count>0)
{
[Link]("Student Record Inserted Successfully");
}
else
{
[Link]("Error! Unable to insert data.");
}
[Link]();
[Link]();
}
catch(ClassNotFoundException ex)
{
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
}
Tanaji Kharbad 11
Chapter 3: Java Database Connectivity Advanced Java
Let's see complete example to update data inside table named stud_tb by using
PreparedStatement.
import [Link];
import [Link];
import [Link];
import [Link];
class DemoUpdateData
{
public static void main(String args[])
{
try
{
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="cocsit";
String query="update stud_tb set sname=? where rno=?";
[Link]("[Link]");
Connection con=[Link](url,uname,pwd);
PreparedStatement pst=[Link](query);
[Link](1,”Mahadev”);//setting student name
[Link](2,5);//setting roll no
int count=[Link]();
if(count>0)
{
[Link]("Student Record Updated Successfully");
}
else
{
[Link]("Error! Unable to update data.");
}
[Link]();
[Link]();
}
catch(ClassNotFoundException ex)
{
Tanaji Kharbad 12
Chapter 3: Java Database Connectivity Advanced Java
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
}
Let's see complete example to delete data from the table named stud_tb by using
PreparedStatement. It will delete the record based on given roll no.
import [Link];
import [Link];
import [Link];
import [Link];
class DemoUpdateData
{
public static void main(String args[])
{
try
{
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="cocsit";
String query="delete from stud_tb where rno=?";
[Link]("[Link]");
Connection con=[Link](url,uname,pwd);
PreparedStatement pst=[Link](query);
[Link](1,5);//setting roll no
int count=[Link]();
if(count>0)
{
[Link]("Student Record Deleted Successfully");
}
Tanaji Kharbad 13
Chapter 3: Java Database Connectivity Advanced Java
else
{
[Link]("Error! Unable to delete data.");
}
[Link]();
[Link]();
}
catch(ClassNotFoundException ex)
{
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
}
Callable Statement
The CallableStatement interface provides methods to execute the stored procedures. Since
the JDBC API provides a stored procedure SQL escape syntax, you can call stored
procedures of all RDBMS in single standard way.
Creating a CallableStatement
You can create an object of the CallableStatement (interface) using the prepareCall()
method of the Connection interface. This method accepts a string variable representing a
query to call the stored procedure and returns a CallableStatement object.
A Callable statement can have input parameters, output parameters or both. To pass input
parameters to the procedure call you can use place holder and set values to these using the
setter methods (setInt(), setString(), setFloat()) provided by the CallableStatement
interface.
Suppose you have a procedure named getStudent() in the database you can prepare a
callable statement as:
CallableStatement cst= [Link]("{call getStudent()}");
Tanaji Kharbad 14
Chapter 3: Java Database Connectivity Advanced Java
You can set values to the input parameters of the procedure call using the setter methods.
These accepts two arguments, one is an integer value representing the placement index of
the input parameter and, the other is a int or, String or, float etc… representing the value
you need to pass as input parameter to the procedure.
Note: Instead of index you can also pass the name of the parameter in String format.
Example:
[Link](1, 6);
[Link](2, "Tanaji");
[Link](3, "Latur");
Executing the Callable Statement
Once you have created the CallableStatement object you can execute it using one of the
execute() method.
Example:
[Link]();
Let's see complete example to use CallableStatement. We will call stored procedure from,
MySql database, named getStudent() by using CallableStatement.
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class DemoCallableStatement
{
public static void main(String[] args)
{
try
{
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="Tk#12345";
[Link]("[Link]");
Tanaji Kharbad 15
Chapter 3: Java Database Connectivity Advanced Java
Connection con=[Link](url,uname,pwd);
CallableStatement cst=[Link]("{call countstudent()}");
boolean flag=[Link]();
if(flag)
{
ResultSet rs=[Link]();
[Link]("Student Data");
while([Link]())
{
[Link]([Link](1)+"\t"+[Link](2)
+"\t"+[Link](3));
}
[Link]();
ResultSet rs1=[Link]();
if([Link]())
[Link]("\nTotal Students = "+
[Link](1));
[Link]();
[Link]();
[Link]();
}
}
catch(ClassNotFoundException ex)
{
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
}
Tanaji Kharbad 16
Chapter 3: Java Database Connectivity Advanced Java
Metadata
Metadata in Java, defined as the data about the data, is called “Metadata”. Metadata is also
said to be documentation about the information required by the users. It gives the Java
developers information about the contents like table data, database data, database url,
database software name, user name, column name, column type tec.
To access meta data in java, we use following two interfaces:
1. DatabaseMataData
2. ResultSetMetaData
1. DatabaseMataData
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.
Following are some methods of DatabaseMetaData class.
1. getDriverName() : Retrieves the name of the current JDBC driver
2. getDriverVersion() : Retrieves the version of the current JDBC driver
3. getUserName() : Retrieves the user name.
4. getDatabaseProductName() : Retrieves the name of the current database.
5. getDatabaseProductVersion() : Retrieves the version of the current database.
6. getURL() : Retrieves the URL for the current database.
2. ResultSetMetaData
The ResultSetMetaData provides information about the obtained ResultSet object like,
the number of columns, names of the columns, datatypes of the columns, name of the
table etc.
Following are some methods of ResultSetMetaData class.
1. getColumnCount() : Retrieves the number of columns in the current ResultSet
object.
2. getColumnName() : Retrieves the name of the column.
3. getTableName() : Retrieves the name of the table.
4. getColumnTypeName() : Retrieves the name of the column data type.
Tanaji Kharbad 17
Chapter 3: Java Database Connectivity Advanced Java
Let's see complete example to use DatabaseMetaData and ResultSetMetaData.
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class DemoMetaData {
public static void main(String[] args) {
try
{
String url="jdbc:mysql://localhost:3306/student_db";
String uname="root";
String pwd="cocsit";
[Link]("[Link]");
Connection con=[Link](url,uname,pwd);
DatabaseMetaData dmt=[Link]();
[Link]("Driver Name = "+[Link]());
[Link]("Driver Version = "+[Link]());
[Link]("Software Name = "+
[Link]());
[Link]("Software Version = "+
[Link]());
[Link]("User Name = "+[Link]());
[Link]("URL = "+[Link]());
Statement smt=[Link]();
ResultSet rs=[Link]("select * from stud_tb");
ResultSetMetaData rmt=[Link]();
[Link]("Total Column = "+[Link]());
[Link]("Column Name = "+[Link](2));
[Link]("Coloum Type = "+[Link](2));
[Link]("Coloum Type = "+[Link](1));
Tanaji Kharbad 18
Chapter 3: Java Database Connectivity Advanced Java
[Link]();
[Link]();
[Link]();
}
catch(ClassNotFoundException ex)
{
[Link](ex);
}
catch(SQLException ex)
{
[Link](ex);
}
}
Tanaji Kharbad 19