JDBC –

Java DataBase Connectivity

What is JDBC?
 Java Database Connectivity(JDBC) is an Application Programming
Interface(API) used to connect Java application with Database.

 JDBC is used to interact with various type of Database such as

Oracle, MS Access, My SQL and SQL Server.

 JDBC can also be defined as the platform-independent interface

between a relational database and Java programming.

 It allows java program to execute SQL statement and retrieve result

from database.

2
What is JDBC?
 It provides methods for querying and updating data in a
database.

 JDBC is oriented towards relational databases.

 JDBC (Java Database Connectivity) is a standard API for

accessing relational databases from a Java program.

3
Introduction to Java Database
Connectivity
 JDBC provides API to interact with different
databases.
 With the help of JDBC driver we can connect with
different types of databases.
 Driver is must needed for connection establishment
with any database.
 A driver works as an interface between the client
and a database server.

4
Introduction to Java Database
Connectivity
 JDBC have so many classes and interfaces that allow a
java application to send request made by user to any
specific DBMS(Data Base Management System).

 JDBC supports a wide level of portability.

 JDBC provides interfaces that are compatible with java

application.

5
What Does JDBC Do?
JDBC makes it possible to do three things:
1.establish a connection with a database

2.send SQL statements

3.process the results.

6
JDBC Architecture:

7
JDBC Architecture:

8
JDBC Architecture

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

9
JDBC Architecture
 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.

10
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.
11
Common JDBC Components
 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.

12
Basic steps to use
a database in Java
1.Establish a connection
2.Create JDBC Statements
3.Execute SQL Statements
4.GET ResultSet
5.Close connections

13
1. Establish a connection
 Import the packages:
 Register the JDBC driver:
 Open a connection:

14
1. Establish a connection
 Import the packages:
 This requires that you include the packages
containing the JDBC classes needed for database
programming.
 Most often, using import java.sql.* ; will suffice as
follows:

//STEP 1. Import required packages

import java.sql.*;

15
 1. Establish a connection
 Register the JDBC driver:
 This requires that you initialize a driver so you can open a communications
channel with the database.
 The forName() method of Class class is used to register the driver class.
 This method is used to dynamically load the driver class.

 Following is the code snippet to achieve this:

 //STEP 2: Register JDBC driver

Class.forName("com.mysql.jdbc.Driver");

The driver class for the mysql database is com.mysql.jdbc.Driver.

The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
16
 1. Establish a connection
 Open a connection:
This requires using the DriverManager.getConnection()
method to create a Connection object, which represents a
physical connection with the database as follows:

//STEP 3: Open a connection

// Database credentials
static final String USER = "username";
static final String PASS = "password";
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);

17
 1. Establish a connection
 Open a connection:
 A database URL is an address that points to your database.
 Formulating a database URL is where most of the problems associated with
establishing a connection occurs.Following table lists down the popular JDBC
driver names and database URL.

RDBMS JDBC driver name URL format

MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/

databaseName

ORACLE oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@hostname:port

Number:databaseName

18
 2. Create JDBC statement(s)
 Once a connection is obtained we can interact with the database.
 The JDBC Statement and PreparedStatement interfaces define the
methods and properties that enable you to send SQL commands and
receive data from your database.
 This requires using an object of type Statement or
PreparedStatement for building and submitting an SQL statement
to the database as follows:

Statement stmt = conn.createStatement() ;

 Creates a Statement object for sending SQL statements to the
database

19
 2. Create JDBC statement(s)
//STEP 4: Execute a query

System.out.println("Creating statement...");
stmt = conn.createStatement( );
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);

20
 3. Executing SQL Statements
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.
21
 3. Executing SQL Statements
//STEP 4: Execute a query

System.out.println("Creating statement...");
stmt = conn.createStatement( );
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);

22
 3. Executing SQL Statements
 If there is an SQL UPDATE,INSERT or DELETE statement
required, then following code snippet would be required:

//STEP 4: Execute a query

System.out.println("Creating statement...");
stmt = conn.createStatement( );
String sql;
sql = "DELETE FROM Employees";
ResultSet rs = stmt.executeUpdate(sql);

23
4. Get ResultSet
 This step is required in case you are fetching data from the database.
 You can use the appropriate ResultSet.getXXX() method to retrieve the data from
the result set as follows:
//STEP 5: Extract data from result set
while(rs.next()){ //Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
24
 5. Close connection
 At the end of your JDBC program, it is required explicitly to close all the
connections to the database to end each database session.
 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.
 To close the above opened connection, you should call close() method as
follows −
 stmt.close();
 conn.close();
25