Java JDBC Tutorial

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the
query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC
drivers to connect with the database. There are four types of JDBC drivers:

o JDBC-ODBC Bridge Driver,

o Native Driver,
o Network Protocol Driver, and
o Thin Driver

We can use JDBC API to access tabular data stored in any relational database. By the help of
JDBC API, we can save, update, delete and fetch data from the database. It is like Open
Database Connectivity (ODBC) provided by Microsoft.

The java.sql package contains classes and interfaces for JDBC API.

A list of popular interfaces of JDBC API

o Driver interface
o Connection interface
o Statement interface
o PreparedStatement interface
o CallableStatement interface
o ResultSet interface
o ResultSetMetaData interface
o DatabaseMetaData interface
o RowSet interface
o
A list of popular classes of JDBC API are given below:

o DriverManager class
o Blob class
o Clob class
o Types class

Why Should We Use JDBC

Before JDBC, ODBC API was the database API to connect and execute the query with the
database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform
dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses
JDBC drivers (written in Java language).

We can use JDBC API to handle database using Java program and can perform the following
activities:

1. Connect to the database

2. Execute queries and update statements to the database
3. Retrieve the result received from the database.

What is API
API (Application programming interface) is a document that contains a description of all the
features of a product or software. It represents classes and interfaces that software programs can
follow to communicate with each other. An API can be created for applications, libraries,
operating systems, etc

JDBC Driver
Type 1: JDBC-ODBC bridge driver

Type 2: Native-API driver (partially java driver)

Type 3 : Network Protocol driver (fully java driver)

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

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

There are 5 steps to connect any java application with the database using JDBC. These steps are
as follows:

Register the Driver class

Create connection

Create statement

Execute queries

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

public static void forName(String className)throws ClassNotFoundException

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

2) public static Connection getConnection(String url,String name,String password)

throws SQLException

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

public Statement createStatement()throws SQLException

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

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

public void close()throws SQLException

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

Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost :
3306/sms 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 sms is the database
name.

Username: The default username for the mysql database is root.

Password: It is the password given by the user at the time of installing the mysql database.

With MS-Access:-

import java.sql.*;

class Test{

public static void main(String ar[]){

try{

String url="jdbc:odbc:mydsn";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection c=DriverManager.getConnection(url);

Statement st=c.createStatement();

ResultSet rs=st.executeQuery("select * from login");

while(rs.next()){

System.out.println(rs.getString(1));

}catch(Exception ee){System.out.println(ee);}

}}
 DriverManager class
The DriverManager class acts as an interface between user and drivers. It keeps track
of the drivers that are available and handles establishing a connection between a
database and the appropriate driver. The DriverManager class maintains a list of
Driver classes that have registered themselves by calling the method
DriverManager.registerDriver()

Method Description

1) public static void registerDriver(Driver driver): is used to register the given driver with
DriverManager.

2) public static void deregisterDriver(Driver driver): is used to deregister the given driver (drop the
driver from the list) with DriverManager.

3) public static Connection getConnection(String is used to establish the connection with the
url): specified url.

4) public static Connection getConnection(String is used to establish the connection with the
url,String userName,String password): specified url, username and password.

Connection interface
A Connection is the session between java application and database. The Connection
interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e.
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() 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 Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a

Statement object that will generate ResultSet objects with the given type and concurrency.

3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is
true.

4) public void commit(): saves the changes made since the previous commit/rollback permanent.

5) public void rollback(): Drops all changes made since the previous commit/rollback.
 6) public void close(): closes the connection and Releases a JDBC resources immediately.

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.

4) public int[] executeBatch(): is used to execute batch of commands.

ResultSet interface
The object of ResultSet maintains a cursor pointing to a row of a table. Initially,
cursor points to before the first row.

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 is used to return the data of specified column index of the current row
columnIndex): as int.

8) public int getInt(String is used to return the data of specified column name of the current row
columnName): as int.

9) public String getString(int is used to return the data of specified column index of the current row
columnIndex): as String.

10) public String getString(String is used to return the data of specified column name of the current row
columnName): as String.

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:

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

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:
Method Description

public void setInt(int paramIndex, int value) sets the integer value to the given parameter index.

public void setString(int paramIndex, String sets the String value to the given parameter index.
value)

public void setFloat(int paramIndex, float sets the float value to the given parameter index.
value)

public void setDouble(int paramIndex, double sets the double value to the given parameter index.
value)

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.

Java ResultSetMetaData Interface

The metadata means data about data i.e. we can get further information from the
data.

If you have to get metadata of a table like total number of column, column name,
column type etc. , ResultSetMetaData interface is useful because it provides methods
to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

Method Description

public int getColumnCount()throws SQLException it returns the total number of columns in the
ResultSet object.

public String getColumnName(int index)throws it returns the column name of the specified column
SQLException index.

public String getColumnTypeName(int index)throws it returns the column type name for the specified
SQLException index.

public String getTableName(int index)throws it returns the table name for the specified column
SQLException index.

How to get the object of ResultSetMetaData:

The getMetaData() method of ResultSet interface returns the object of ResultSetMetaData. Syntax:

1. public ResultSetMetaData getMetaData()throws SQLException

Java DatabaseMetaData interface

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 methods of DatabaseMetaData

interface
o public String getDriverName()throws SQLException: it returns the name of the
JDBC driver.
o public String getDriverVersion()throws SQLException: it returns the version
number of the JDBC driver.
o public String getUserName()throws SQLException: it returns the username of the
database.
 o public String getDatabaseProductName()throws SQLException: it returns the
product name of the database.
o public String getDatabaseProductVersion()throws SQLException: it returns the
product version of the database.
o public ResultSet getTables(String catalog, String schemaPattern, String
tableNamePattern, String[] types)throws SQLException: it returns the description
of the tables of the specified catalog. The table type can be TABLE, VIEW, ALIAS,
SYSTEM TABLE, SYNONYM etc.

How to get the object of DatabaseMetaData:

The getMetaData() method of Connection interface returns the object of
DatabaseMetaData. Syntax:

1. public DatabaseMetaData getMetaData()throws SQLException