JAVA DATABASE CONNECTIVITY
JDBC
JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query withthe
database. It is a specification from Sun Microsystems that provides a standard abstraction (API or Protocol) for
Java applications to communicate with various databases. It provides the language with Java database
connectivity standards. It is used to write programs required to access databases. JDBC, along with the
database driver, can access databases and spreadsheets. The enterprise data stored in a relational
database (RDB) can beaccessed with thehelp of JDBCAPIs.
Definitionof JDBC(JavaDatabaseConnectivity)
JDBC is an API (Application programming interface) used in Java programming
databases. The classes
and interfaces
to interact with
of JDBC allow the application to send requests made by users to the
specified database.
Purposeof JDBC
Enterprise applications created using the JAVA EE technology need to interact with databases to
store application-specific information. So, interacting with a database requires efficient database
connectivity, which can be achieved by using the ODBC
(Open database connectivity) driver. This
driver is used with JDBC to interact or communicate with various kinds of databases such as Oracle, MS
Access, MySQL, andSQL server database.
Componentsof JDBC
There are generally four main components of JDBC through which it can interact with a database. They
are as mentioned below:
1. JDBC API: It provides various methods and interfaces for easy communication with the database. It
provides two packages as follows, which contain the java SE and Java EE platforms to exhibit
WORA (write once run anywhere) capabilities. The java.sql package contains interfaces and classes
of JDBCAPI.
• java.sql: This package provides APIs for data access and data process in a
relational database, included in Java Standard Edition (java SE)
• javax.sql: This package extends the functionality of java package by providing
datasource interface for establishing connection pooling, statement pooling with a
data source, included in Java Enterprise Edition (java EE)
It also provides a standard to connect a database to a client application.
2.JDBC Driver manager: It loads a database-specific driver in an application to establish a connection with
a database. It is used to make a database-specific call to the database to process the user request.
3.JDBC Test suite: It is used to test the operation (such as insertion, deletion, updation) being performed by
JDBC Drivers.
4.JDBC-ODBC Bridge Drivers: It connects database drivers to the database. This bridge translates the
JDBC method call to the ODBC function call. It makes use of the sun.jdbc.odbc package which includes a
native library to access ODBC characteristics.
Architecture of JDBC
Description:
.
1. JavaApplication:It is a java applet or a servlet that communicates with a data source.
2. The JDBC API: The JDBC API allows Java programs to execute SQL statements and retrieve
results. Some of theimportant classes and interfaces defined in JDBC API are as follows:
3. Driver Manager: It plays an important role in the JDBC architecture. It uses some database
specific drivers to effectively connectenterprise applicationsto databases.
4. JDBC drivers: To communicate with a data source through JDBC, you need a JDBC driver
that intelligently communicates with the respective data source.
Types of JDBCArchitecture(2-tierand3-tier)
The JDBC architectureconsists of two-tier and three-tier processing models to access a database.
They are as described below:
1. Two-tier model: A java application communicates directly to the data source. The JDBC
driver enables the communication between the application and the data source. When a user sends a
query to the data source, the answers for those queries are sent back to the user in the form of results.
The datasource can belocatedon a different machine ona networkto which a user is connected.
This is known as a client/server configuration, where the user’s machine acts as a client, and the
machine has the data source running acts as the server.
2. Three-tier model: In this, the user’s queries are sent to middle-tier services, from which the commands
are again sent to the data source. The results are sent back to the middle tier, and from there to the user.
This type of model is found very useful bymanagement information system directors.
WhatisAPI?
Before jumping into JDBC Drivers, let us know more aboutAPI.
API stands for Application Programming Interface. It is essentially a set of rules and protocols
which transfers data between different software applications and allow different software applications
to communicate with each other. Through an API one application can request information or perform a
function from anotherapplicationwithouthaving direct access to its underlyingcode or theapplicationdata.
JDBCAPI usesJDBC Drivers to connectwiththedatabase.
JDBC Drivers
JDBC drivers
are client-side adapters (installed on the client machine, not on the server) that convert
requests from Java programs to a protocol that the DBMS can understand. There are 4 types of JDBC
drivers:
1.Type-1 driver or JDBC-ODBC bridge driver
2.Type-2 driver or Native-API driver (partially java driver)
3.Type-3 driver or Network Protocol driver (fully java driver)
4.Type-4 driver or Thin driver (fully java driver)
1. JDBC-ODBCbridgedriver–Type 1 driver
Type-1 driver or 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. Type-1 driver is
also calledUniversal driver because it can be used toconnectto any of thedatabases.
Advantages
• This driver software is built-in with JDK so no need to install separately.
• It is a database independent driver.
Disadvantages
• As a common driver is used in order to interact with different databases, the data transferred through this
driver is not so secured.
• The ODBC bridge driver is needed to be installed in individual client machines.
• Type-1 driver isn’t written in java, that’s why it isn’t a portable driver.
2. Native-APIdriver– Type 2 driver( PartiallyJavadriver)
The Native API driver uses the client-side libraries of the database. This driver converts JDBC method
calls into native calls of the database API. In order to interact with different database, this driver needs
their local API, that’s why data transfer is much more secure as compared to type-1 driver. This driver is
not fully written in Java that is why it is also called PartiallyJava driver.
Advantage
• Native-API driver gives better performance than JDBC-ODBC bridge driver.
Disadvantages
• Driver needs to be installed separately in individual client machines
• The Vendor client library needs to be installed on client machine.
• Type-2 driver isn’t written in java, that’s why it isn’t a portable driver
• It is a database dependent driver.
3. NetworkProtocoldriver– Type 3 driver(fully Javadriver)
The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or
indirectly into the vendor- specific database protocol. Here all the database connectivity drivers are present
in a single server, hence no need of individual client-side installation.
Advantages
• Type-3 drivers are fully written in Java; hence they are portable drivers.
• No client-side library is required because of application server that can perform many tasks like auditing,
load balancing, logging etc.
• Switch facility to switch over from one database to another database.
Disadvantages
• Network support is required on client machine.
• Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be
done in the middle tier.
4. Thin driver – Type 4 driver (fully Java driver)
Type-4 driver is also called native protocol driver. This driver interacts directly with
database. It does not require any native database library, that is why it is also known as Thin
Driver.
Advantages
• Doesnotrequireanynative library and Middlewareserver, so no client-sideor
server-side installation.
• It is fully written in Java language, hence they are portabledrivers.
Disadvantage
• If the databasevaries, then thedriver will carry because it is database dependent.
Which Driver to use When?
• If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is type-4.
• If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred
driver.
• Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for your database.
• The type 1 driver is not considered a deployment-level driver, and is typically used for development and
testing purposes only.
Interfaces of JDBCAPI
Alist of popularinterfacesof JDBCAPI is
given below:
• Driver interface
• Connectioninterface
• Statementinterface
• PreparedStatement interface
• CallableStatement interface
• ResultSet interface
• ResultSetMetaDatainterface
• DatabaseMetaDatainterface
• RowSetinterface
Classesof JDBCAPI
Alist of popularclassesof JDBCAPI is given
below:
• DriverManager class
Working of JDBC
There are 5 steps toconnectany java applicationwith thedatabase using JDBC. These steps are as follows:
❑ Register theDriver class
❑ Createconnection
❑ Createstatement
❑ Executequeries
❑ Closeconnection
1
. Register the driver class
The forName() method ofClass class is used to register the driver class. This method is used to dynamically load
thedriver class.
Syntax of forName() method
➢ publicstaticvoidforName(String className)throws ClassNotFoundException
➢ Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in the
classpath, and thenJDBC driver manager can detect and load thedriver automatically.
Example toregister the MySqlDriver class
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
➢ Class.forName("com.mysql.cj.jdbc.Driver");
2
. Create the connection object
The getConnection()methodof DriverManager class is used toestablish connectionwith thedatabase.
Syntax of getConnection()method
➢ publicstaticConnectiongetConnection(Stringurl)throwsSQLException
➢ publicstaticConnectiongetConnection(Stringurl,Stringusername,Stringpassword)throwsSQLException
Example to establish connection with the MySql database
➢ Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","ro
ot","root");
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 :
➢ publicStatement createStatement()throwsSQLException
Example to create the statement object
➢ Statement smt=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 :
➢ publicResultSetexecuteQuery(Stringsql)throwsSQLException
Example toexecutequery
➢ ResultSet rs=smt.executeQuery("select * from emp");
while(rs.next())
{
System.out.print("Empid:"+rs.getInt(1));
}
5. Close the connection object
By closing connection object statement and ResultSetwill be closed automatically. The close() method of Connection
interface is used to close the connection.
Syntax of close() method
➢ publicvoidclose()throwsSQLException
Example toclose connection
➢ rs.close();
Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close resources of type
Connection,ResultSet,and Statement.
It avoids explicit connectionclosing step.
Example :-1
package databaseapp;
import java.sql.*;
public class employee {
public static void main(String args[])
{
try
{
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","root","root")
;
Statement smt=con.createStatement();
ResultSet rs=smt.executeQuery("select * from emp");
while(rs.next())
{
System.out.print("empid:"+rs.getInt(1));
System.out.print("empname:"+rs.getString(2));
System.out.println("sal:"+rs.getInt(3));
}
rs.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
DriverManager
class
The DriverManager class is the component of JDBC API and also a member of
the java.sql package. The DriverManager class acts as an interface between users and
drivers. It keeps track of the drivers that are available and handles establishing a
connection between a database and the appropriate driver. It contains all the
appropriate methods to register and deregister the database driver class and to create a
connection between a Java application and the database. The DriverManager class
maintains a list of Driver classes that have registered themselves by calling the method
DriverManager.registerDriver().
Method Description
1)publicstaticsynchronizedvoidregisterDriver(Driver
driver):
isused to register thegivendriver withDriverManager.Noaction is
performedbythemethodwhenthegivendriver isalreadyregistered.
2) public static synchronized void
deregisterDriver(Driverdriver):
isusedtoderegister thegivendriver(dropthedriver fromthelist)with
DriverManager. If thegivendriverhasbeenremovedfromthelist, then
noactionisperformedbythemethod.
3) public staticConnectiongetConnection(Stringurl)
throwsSQLException:
is used to establish the connection with the specified url. The
SQLException is thrownwhen thecorrespondingDriver classof the
givendatabaseisnot registeredwiththeDriverManager.
4) public static Connection getConnection(String
url,String userName,String password) throws
SQLException:
isused toestablish theconnectionwith thespecifiedurl, username,
andpassword.TheSQLException is thrownwhenthecorresponding
Driver class of the given database is not registered with the
DriverManager.
5)publicstaticDrivergetDriver(Stringurl)
Those drivers that understand thementionedURL (present in the
parameterof themethod)arereturnedbythismethodprovidedthose
driversarementioned inthelistofregistereddrivers.
Methods of the DriverManager Class
Connection
interface
A Connection is a session between a Java application and a database. It helps to establish a
connectionwith the database.
The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData, i.e.,
an object of Connection can be used to get the object of Statement and DatabaseMetaData. The
Connection interface provide many methods for transaction management like commit(), rollback(),
setAutoCommit(), setTransactionIsolation(), etc.
Commonly used methods of Connection interface:
1) public Statement createStatement(): creates a statement object that can be used to execute SQL
queries.
2) public voidsetAutoCommit(booleanstatus): is used to set the commit status.By default,it is true.
3) publicvoidcommit(): savesthe changes madesince the previous commit/rollback is permanent.
4) public voidrollback():Drops all changes made since the previous commit/rollback.
5) public voidclose(): closes the connectionand Releases a JDBC resourcesimmediately.
Statement interface
The Statement interface provides methods to execute queries with the database. The
statement interface is a factory of ResultSet i.e. it provides factory method to get the object
of ResultSet.
Commonly used methods of Statement interface:
The important methods of Statement interface are as follows:
1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns
the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query, it may be
create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may return multiple
results.
ResultSetinterface
TheobjectofResultSetmaintainsacursorpointingtoarowof a table. Initially, cursor
pointstobeforethefirstrow.
CommonlyusedmethodsofResultSetinterface
1)publicbooleannext(): isusedtomove thecursor totheone rownext
fromthecurrentposition.
2)publicbooleanprevious(): isusedtomovethecursortotheonerowprevious
fromthecurrentposition.
3)publicbooleanfirst(): isusedtomovethecursortothefirstrowinresult
setobject.
4)publicbooleanlast(): isusedtomovethecursortothelastrowinresult
setobject.
5)publicbooleanabsolute(introw): isusedtomove thecursor tothespecifiedrow
numberintheResultSetobject.
6)publicbooleanrelative(introw): is used tomove the cursor to the relative row
number intheResultSetobject, itmaybepositive
ornegative.
7)publicintgetInt(intcolumnIndex): is used to return the data of specified column
indexofthecurrentrowasint.
8)publicintgetInt(StringcolumnName): is used to return the data of specified column
nameofthecurrentrowasint.
9)publicStringgetString(intcolumnIndex): is used to return the data of specified column
indexofthecurrentrowasString.
10)publicStringgetString(StringcolumnName): is used to return the data of specified column
nameofthecurrentrowasString.
PreparedStatement interface
The PreparedStatement interface is a subinterface of Statement. It is used to execute
parameterized query.
Let's see the example of parameterized query:
String sql="insert into emp values(?,?,?)";
As you can see, we are passing parameter (?) for the values. Its value will be set by calling
the setter methods of PreparedStatement.
Why use PreparedStatement?
Improves performance: The performance of the application will be faster if you use
PreparedStatement interface because query is compiled only once.
Howto getthe instance of PreparedStatement?
The prepareStatement() method of Connection interface is used to return the object of
PreparedStatement.
Syntax:
public PreparedStatement prepareStatement(String query)throws SQLException{}
Method Description
public void setInt(int paramIndex, int
value)
sets the integer value to the given
parameter index.
public void setString(int paramIndex,
String value)
sets the String value to the given
parameter index.
public void setFloat(int paramIndex,
float value)
sets the float value to the given
parameter index.
public void setDouble(int paramIndex,
double value)
sets the double value to the given
parameter index.
public int executeUpdate() executes the query. It is used for
create, drop, insert, update, delete etc.
public ResultSet executeQuery() executes the select query. It returns an
instance of ResultSet.
MethodsofPreparedStatementinterface
TheimportantmethodsofPreparedStatement interfacearegivenbelow:
package database1;
import java.sql.*;
public class emp_prepare {
public static void main(String args[])throws SQLException,Exception
{
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee
","root","root");
PreparedStatement prmt=con.prepareStatement("select * from emp");
//prmt.setInt(1,12);
//prmt.setString(2,"shubham
");
//prmt.setInt(3,4520);
// int
x=prmt.executeUpdate();
//if(x>0)
//System.out.println("One record is inserted......");
ResultSet rs=prmt.executeQuery();
while(rs.next())
{
System.out.println("id"+rs.getInt(1));
System.out.println("name"+rs.getString(2));
}
rs.close();
}
}
JavaResultSetMetaDataInterface
Themetadatameansdataaboutdatai.e.wecangetfurther informationfromthedata.
Ifyouhavetogetmetadataofatableliketotalnumberofcolumn,columnname,column
typeetc. , ResultSetMetaData interface isuseful because it providesmethods toget
metadatafromtheResultSetobject.
CommonlyusedmethodsofResultSetMetaDatainterface
Method Description
public int getColumnCount()throws
SQLException
it returns the total number of columns in the
ResultSetobject.
publicStringgetColumnName(int index)throws
SQLException
it returns the column name of the specified
columnindex.
public String getColumnTypeName(int
index)throwsSQLException
it returns the column type name for the
specifiedindex.
public String getTableName(int index)throws
SQLException
it returns the table name for the specified
columnindex.
How to get the object of ResultSetMetaData:
The getMetaData() method of ResultSet interface returns the object of
ResultSetMetaData.
Syntax:
➢ publicResultSetMetaData getMetaData()throws SQLException
package database1;
import java.sql.*;
public class emp_prepare {
public static void main(String args[])throws SQLException,Exception
{
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","
root","root");
PreparedStatement prmt=con.prepareStatement("select * from emp");
ResultSet rs=prmt.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
System.out.println("Total columns: "+rsmd.getColumnCount());
System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));
System.out.println("Column Type Name of 1st column:
"+rsmd.getColumnTypeName(1));
rs.close();
}
}
Java
DatabaseMetaDatainterface
DatabaseMetaData interface provides methods to get meta data of a database such as
database product name, database product version, driver name, name of total number of
tables, name of total number of views etc.
Commonly used methodsofDatabaseMetaData interface
•public String getDriverName()throws SQLException: it returns the name of the
JDBC driver.
•public String getDriverVersion()throws SQLException: it returns the version
number of the JDBC driver.
•public String getUserName()throws SQLException: it returns the username of
the database.
•public String getDatabaseProductName()throws SQLException: it returns the
product name of the database.
•public String getDatabaseProductVersion()throws SQLException: it returns the
product version of the database.
Howtoget theobject ofDatabaseMetaData:
The getMetaData() method of Connection interface returns the object of
DatabaseMetaData.
Syntax:
public DatabaseMetaData getMetaData()throws SQLException
package database1;
import java.sql.*;
public class employee {
public static void main(String[] args) throws ClassNotFoundException
{
try
{
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","ro
ot","root");
DatabaseMetaData dbmd=con.getMetaData();
System.out.println("Driver Name: "+dbmd.getDriverName());
System.out.println("Driver Version: "+dbmd.getDriverVersion());
System.out.println("UserName: "+dbmd.getUserName());
System.out.println("Database Product Name:
"+dbmd.getDatabaseProductName());
System.out.println("Database Product Version:
"+dbmd.getDatabaseProductVersion());
}
catch(SQLException e)
{
System.out.println("error"+e.getMessage());
}
}
}
Java CallableStatement Interface
CallableStatement interface is used to call the stored procedures and functions.
We can have business logic on the database by the use of stored procedures and functions
that will make the performance better because these are precompiled.
Howto getthe instance of CallableStatement?
The prepareCall() method of Connection interface returns the instance of CallableStatement.
Syntax is given below:
public CallableStatement prepareCall("{ call procedurename(?,?...?)}");
The example to get the instanceof CallableStatement is given below:
CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");
MySql Procedure
DELIMITER $$
USE `employee`$$
CREATE PROCEDURE `new_procedure` (in empid int)
BEGIN
delete from emp where empid=empid;
END
$$DELIMITER ;
package database1;
import java.sql.*;
import javax.sql.*;
public class emp_procedure
{
public static void main(String args[])throws SQLException,Exception
{
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/employee","root","root");
CallableStatement stmt=con.prepareCall("{call new_emp(?,?,?)}");
stmt.setInt(1,117);
stmt.setString(2,"Amit kumar");
stmt.setInt(3,15640);
stmt.execute();
System.out.println("success");
}
}
