Introduction to JDBC

What is JDBC?
• JDBC: Java Database Connectivity
• JDBC is JAVA’s Database connection driver interface which
performs the following task for the application
• Establish a connection with a Database.
• Send SQL request (Query) to a Database Server.
• Returns Result obtained against Query.
 Classes used for Database Connectivity
• The Core element of JDBC is JDBC API
o It consists of a set of Java classes equipped with predefined methods
▪ Selecting appropriate database driver,
▪ establishing connection,
▪ submitting SQL query and
▪ processing results.
• There are four main interfaces in the JDBC.
• Driver Manager Class : The JDBC Driver Manager Class Loads the JDBC driver needed to
access a particular data source, locates & logs on to the database & returns a Connection
Objects.
• Connection Class : The JDBC Connection class manages the communications between a java
client application & a specific database (e.g. MYSQL database), including passing SQL
statement to the DBMS & managing transactions.
• Statement Class : The JDBC Statement class contains SQL string that are submitted to the
DBMS .An SQL Select statement returns a result Set object that contains the data retrieved as
the result of SQL statement.
• Result set class: The JDBC Result Set class provides predefined methods to access, analyze,&
convert data values returned by an executed SQL select statement
Steps To Execute An SQL Statement And
Get Data From Database
• Load the driver (Only performed once)
• Obtain a Connection to the database (Save for later use)
• Obtain a Statement object from the Connection
• Use the Statement object to execute SQL.
• Selects return a ResultSet
• Updates, inserts and deletes return Boolean.
• Navigate ResultSet, using data as required
• Close ResultSet
• Close Statement
Load The Driver
• To open a communication channel
• Initialize driver by registering the JDBC driver with JDBC driver Manager
• Java offers a Class.forName() method in java.lang package.
Class.forName(“java.sql.driver”);
Class.forName(“com.mysql.jdbc.Driver”);
Obtain A Connection To The Database
• DriverManager.getConnection() method is used to create a connection
object
• It creates a physical connection with database
• DriverManager.getConnection() requires the complete address of the
database (Database URL), user name and password as a parameter
• A database URL can be formed as:
jdbc:mysql://localhost/school
• Example:
String DB_URL = “jdbc:mysql://localhost/schol
Connection con=DriverManager.getConnection(DB_URL,”root”,”abc”);
 Obtain A Statement Object From The
Connection
• Use createStatement() method to create statement object for executing
SQL query.
• Example: Statement stmt = con.createStatement();
• Use executeQuery() method along with a valid SQL Statement to execute
a query on the database
 Selects Return A Resultset
• executeQuery() method returns a ResultSet Object that contains the
records from the database.
ResultSet rs = stmt.executeQuery(“<SQL Query>”);
• Example:
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT roll, name, class FROM student”);
 Updates, Inserts And Deletes Return
Boolean.
• An executeUpdate() method is used in place of executeQuery() to perform
• Insert
• Delete
• Or Update command.
• Example:
String query = “INSERT INTO student VALUES (1,’Ram’,4)”;
boolean result = stmt.executeUpdate(query);
Navigate Resultset, Using Data As
Required
• To retrieve the data from the ResultSet object, which contains
records there are special method of the following format
<ResultSet Object>.get<type>(<column name/number>);
• Where <type> may be Int, Long, String and float etc. depending on
the column type of the table.
• Example:
int r = rs.getInt(“roll”); or int r = rs.getInt(1);
String n = rs.getString(“name”); or String n = rs.getString(2);
int c = rs.getInt(“class”); or int c = rs.getInt(3);
Closing Connection
• The final step is to close the environment by closing the connection
by close() method
• rs.close()
• stmt.close()
• con.close()
//STEP 1. Import required packages //STEP 6: Clean-up environment
import java.sql.*; rs.close();
public class FirstExample { stmt.close();
public static void main(String[] args) { conn.close();
Connection conn = null; }catch(SQLException se){
Statement stmt = null; //Handle errors for JDBCs
try{ e.printStackTrace();
//STEP 2: Register JDBC driver }catch(Exception e){
Class.forName("com.mysql.jdbc.Driver"); //Handle errors forClass.forName
//STEP 3: Open a connection e.printStackTrace();
System.out.println("Connecting to database..."); }finally{
conn = DriverManager.getConnection(DB_URL,USER,PASS); //finally block used to close resources
//STEP 4: Execute a query try{
System.out.println("Creating statement..."); if(stmt!=null)
stmt = conn.createStatement(); stmt.close();
String sql; }catch(SQLException se2){}// nothing we can do
sql = "SELECT id, first, last, age FROM Employees"; try{
ResultSet rs = stmt.executeQuery(sql); if(conn!=null)
//STEP 5: Extract data from result set conn.close();
while(rs.next()){ }catch(SQLException se){
//Retrieve by column name se.printStackTrace();
int id = rs.getInt("id"); }//end finally try
int age = rs.getInt("age"); }//end try
String first = rs.getString("first"); System.out.println("Goodbye!");
String last = rs.getString("last"); }//end main
//Display values }//end FirstExample
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
Steps To Add Mysql Jar File To The Project
• Right click on the project
• Select Properties
• Open Libraries
• Click on Add jar/folder
• Navigate to Program files to the following folder
C:\Program Files\NetBeans 8.2\ide\modules\ext
• Select mysql-connector-java-5.1.23-bin and click ok.
JDBC Driver
• JDBC Driver is required to process SQL requests and generate result.
The following are the different types of driver available in JDBC.
• Type-1 Driver or JDBC-ODBC bridge
• Type-2 Driver or Native API Partly Java Driver
• Type-3 Driver or Network Protocol Driver
• Type-4 Driver or Thin Driver
Type-1 Driver: JDBC-ODBC bridge
• Act as a bridge between JDBC and other database connectivity
mechanism(ODBC).
• This driver converts JDBC calls into ODBC calls and redirects the
request to the ODBC driver.
• This driver help the java programmers to use JDBC and develop Java
application to communicate with existing data sources.
• This driver is included in Java2 SDK within the sun.jdbc.odbc package.
Steps involved in establishing the connection
between a Java application and data source
• Java application makes the JDBC call to
the JDBC-ODBC bridge driver to access a
data source.
• The JDBC-ODBC bridge driver resolves the
JDBC call and makes an equivalent ODBC
call to the ODBC driver.
• The ODBC drier completes the request
and sends responses to the JDBC-ODBC
bridge driver.
• The JDBC-ODBC bridge driver converts
the response into the JDBC standards and
displays the result to the requesting Java
Application.
Advantages
• Easy to use
• Represents single driver implementation to interact with different
data sources.
• Allow easy connectivity to all database supported by the ODBC
Driver.
• Represents a vendor independent driver.
Disadvantage
• Slow execution time due to the large number of translations.
• Dependent on ODBC Driver.
• Uses Java Native Interface(JNI) to make ODBC call.
• Thus unsuitable for production environment
• Should be used only if no other driver type available.
• Also not recommended when Java application required with
auto-installation such as applets.
Type-2 Driver :Native API Driver
• The JDBC calls can be converted into the database vendor specific native
call.
• This type of driver make use of Java Native Interface(JNI) call on database
specific native client API.
• These native client API are usually written in C and C++.
• These type of driver is implemented for a specific database
• Usually delivered by a DBMS vendor
• but not mandatory – ex: Weblogic driver is implemented by BEA Weblogic.
• It can be used with server side applications.
• Not recommended to use with client-side applications.
• As the database specific native libraries should be installed on the client machine.
Steps involved in the communication
• Java application that needs to communicates
with the database is programmed using JDBC
API.
• These JDBC calls are converted into database
specific native calls in the client machine and
the request is then dispatched to the
database specific native libraries.
• These native libraries present in the client
are intelligent enough to send the request to
the database server by using native protocol.
Advantage
• faster as compared to other types of drivers.
• Contains additional features provided by the specific data vendor,
which are also supported by the JDBC specification.
Disadvantage
• Requires native library to be installed on client machines, since the
conversion from JDBC class to database specific native calls is done
on client machines.
• Executes the database specific native functions on the client JVM,
implying that any bug in the type-2 driver might crash the JVM.
• Increases the cost of Application in case it is run on different
platforms.
Examples of type-2 drivers
• OCI (Oracle Call Interface) Driver
• Communicates with the Oracle database server.
• This driver converts JDBC calls into oracle native library calls.
• Weblogic OCI Driver for Oracle – Makes JNI calls to Weblogic library
functions. The Weblogic OCI driver for Oracle is similar to the Oracle
OCI driver.
• Type-2 Driver for Sybase – Converts JDBC calls into Sybase dblib or
ctlib calls, which are native libraries to connect to Sybase.
Type-3 Driver: Java to Network Protocol /
All Java Driver
• This driver translate the JDBC calls into a database server
independent and Middleware server-specific calls.
• Middleware server further translate JDBC calls into database server
specific calls.
• It follows 3-tire architecture model.
• Also called:
• net-protocol fully java technology-enabled or
• Net-protocol drivers.
Steps involved in communication
• Type-3 driver listens for JDBC
calls from the Java application
and translates them into
middleware server specific calls.
• Communication happens on a
socket.
• The middleware server converts
these calls into database specific
calls.
Advantage
• Serves as all Java driver and is auto downloadable
• Does not require any native library to be installed on the client machine.
• Ensures database independency because a single driver provides
accessibility to different types of databases.
• Does not provide the database details, such as username, password and
database server location to the client.
• Details are automatically configured in the middleware server.
• Provides the facilities to switch over from one database to another without
changing the client-side driver classes.
• Can be done with changing the configuration on the middleware server.
Disadvantage
• Slow due to increase number of network call as compared to type-2
drivers.
• It is also costlier as compared to other drivers.
Examples
• IDS Driver –
• Listens for JDBC calls and converts them into IDS server specific network calls.
• Type-3 driver communicates over a socket to IDS Server, which acts as a
middleware server.
• Weblogic RMI Driver –
• Listens for JDBC calls and sends the requests from the client to the
middleware server by using the RMI protocol.
• The middleware server uses a suitable JDBC driver to communicate with a
database.
Type-4 Driver: Thin Driver (Java to Database
protocol)
• This is Driver called Pure Java Driver
• Implements the database protocol to interact directly with a database.
• It does not require any native database library, that is why it is also known
as Thin Driver.
• Type-4 Driver translates JDBC calls into database specific network calls.
• Use this driver type when auto-downloadable option for the client-side
application is needed.
• It can be used for server-side applications also.
• Generally this type of driver is implemented by DBMs Vendors.
• It is a 2-tire architecture
Steps involved in communication
• Driver prepares a DBMS-specific network
message and then communicates with
database server over a socket.
• It uses the database specific proprietary
protocol for communication.
Advantages
• Serves as a pure Java driver and is auto downloadable.
• Does not require any native library to be installed on the client
machine.
• Uses database server specific protocol.
• Does not require a middleware server.
Disadvantages
• It uses database specific proprietary protocol and is DBMS vendor
dependent.
The java.sql package contains API for the following:
• Java.sql
• DriverManger Class
• Connection Interface
• Statement Interface
• PreparedStatement Interface
• CallableStatement Interface
• ResultSet Interface
Connection Management
Database Access
Data Types
Refer the Java programs
The Execution Control of the Statement
• When a Statement object is used to execute a query (that is, calling any
one of the execute methods), the
• query is processed as follows:
• The executeXXX() method is invoked on the Statement object by passing the SQL
statement as parameter.
• The Statement object submits the SQL statement to the database.
• The database compiles the given SQL statement.
• An execution plan is prepared by the database to execute the SQL statements.
• The execution plan for the compiled SQL statement is then executed. Now, if the
SQL statement is a data retrieval statement, such as the SELECT statement, the
database caches the results of the SQL statement in the buffer.
• The results are sent to the Statement object.
• Finally, the response is sent to the Java application in the form of ResultSet.
Execution flow of the Statement object:
The PreparedStatement Interface
• Is used for representing a precompiled query that can be run
multiple times.
• The execution of pre-compiled PreparedStatement is much faster in
comparison to the Statement objects included in an application.
• The PreparedStatement interface inherits all the properties of the
Statement interface.
• The execute methods do not take any parameter while using the
PreparedStatement object.
Points to Remember
• A PreparedStatement object must be associated with one connection
• A PreparedStatement object specifies the running plan of a query,
which is then passed as argument during the creation of the
PreparedStatement object
The Execution Control of the
PreparedStatement
• When a PreparedStatement object is used to execute a query (that is, calling any one of the execute
methods), the query is processed as follows:
• The prepareStatement() method of the connection object is used to get the object of the PreparedStatement
interface.
• The connection object submits the given SQL statement to the database.
• The database compiles the given SQL statement.
• An execution plan is prepared by the database to execute the SQL statements.
• The database stores the execution plan with a unique ID and returns the identity to the Connection object.
• The Connection object prepares a PreparedStatement object, initializes it with the execution plan identity, and
returns the reference of the PreparedStatement object to the Java application.
• The setXXX() methods of the PreparedStatement object are used to set the parameters of the SQL statement it
is representing .
• The executeXXX() method of the PreparedStatement object is invoked to execute the SQL statement with the
parameters set to the PreparedStatement object
• The PreparedStatement object delegates the request sent by a client to the database.
• The database locates and executes the execution plan with the given parameters.
• Finally, the result of the SQL statements is sent to the Java application in the form of ResultSet.
The flow of execution when PreparedStatement is
used to execute SQL statements:
The setXXX()
Methods of the
PreparedState
ment Interface
Advantages and Disadvantages of Using a
PreparedStatement Object
• The advantages of using a PreparedStatement object are as follows:
• Improves the performance of an application compared to the Statement
object that executes the same query multiple times. The PreparedStatement
object performs the execution of queries faster by avoiding the compilation
of queries multiple times.
• Provides a programmatic approach to set the values. In other words, the
value of each parameter provided in an SQL query is passed separately by
using the PreparedStatement object, unlike the Statement object.
• The disadvantage of using the PreparedStatement object is that it
represents only one SQL statement at a time, i.e., you cannot execute
more than one statement by a single PreparedStatement
Using the PreparedStatement Interface
• The following are some of the situations when you should use
PreparedStatement in a JDBC application:
• When a single query is being executed multiple times
• When a query consists of various parameters and complex types (SQL 99 types)
• PreparedStatement is used to increase the efficiency and reduce the
execution time of a query. An instance of
• PreparedStatement must be created to execute a precompiled SQL
statement. Follow these broad-level steps to use the PreparedStatement
interface:
• Create a PreparedStatement object
• Provide the values of the PreparedStatement parameters
• Execute the SQL statements
Create a PreparedStatement Object
• Class.forName("oracle.jdbc.driver.OracleDriver");
• Connection con= DriverManager.getConnection (url, “user”,
“password”);
• String query="insert into mytable values (?,?,?)";
• //Step1: Get PreparedStatement object
• PreparedStatement ps=con.prepareStatement (query);
Provide the Values of the PreparedStatement
Parameters
• //Step2: setting values for the parameters
• ps.setString(1,"abc1");
• ps.setInt(2,38);
• ps.setDouble(3,158.75);
Execute the SQL Statements
• //Step3: Executing the SQL statements
• int n = ps.executeUpdate(); // n is the number of rows or tables that
are being updated
Refer the Examples
The ResultSet Interface
• A ResultSet interface is an interface provided in the java.sql package, and is used to represent data
retrieved from a database in a tabular format.
• It implies that a ResultSet object is a table of data returned by executing an SQL query.
• A ResultSet object encapsulates the resultant tabular data obtained when a query is executed.
• It holds zero or more objects, where each of the objects represents one row that may span over
one or more table columns.
• Obtain a ResultSet object by using the executeQuery or getResultSet() method of a Statement
object.
• Some of the important points related to a ResultSet are as follows:
• ResultSet follows the iterate pattern.
• A ResultSet object is associated with a statement within a connection.
• Obtain any number of resultsets using one statement; however, only one ResultSet can be opened at a time.
• When a ResultSet is tried to open by using a statement that is already linked that an opened ResultSet, the
existing ResultSet gets closed implicitly.
• ResultSet is automatically closed when its associated statement is closed.
The Methods of ResultSet
The Methods of ResultSet cont..
The Methods of ResultSet cont..
The Methods of ResultSet cont..
Using ResultSet