Ques : Describe JDBC API

 In this section, you will learn about the JDBC API. The JDBC provides a set of classes and

interfaces that can be used to write database applications. The JDBC API (Application
Programming Interface) is implemented by JDBC drivers. A JDBC driver is a software
component that enables a Java application to interact with a database. The JDBC core API is
found in the java.sql package and additional optional extensions for JDBC are found in
javax.sql package such as connection pooling, rowsets and distributed transactions. The most
important members of JDBC API are as follows:

 DriverManager class
 Connection interface
 Statement interface
 Prepared and Callable Statement Interface
 ResultSet interface

The Driver Manager Class
The task of Driver Manager class is to maintain a list of JDBC drivers. This list contains
information about the object reference of drivers and subprotocols that it supports. When a
program requests a database connection with getConection() method call, the Driver Manager
goes through the list and match a suitable driver. Each JDBC driver must be registered with
the Driver Manager. The JDBC drivers are provided by the database vendor or third party.
You can use different JDBC drivers for different database servers.

The most important method of Driver Manager class is getConnection() that returns a
java.sql.Connection object. The signatures of this method are as follows:

public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties info)
public static Connection getConnection(String url, String user, String password)

You can write connection string like the following way:

Connection con = DriverManager.getConnection(dbURL);

The dbURL is specified in the following manner:

protocol:subprotocol:other parameter

For example:

jdbc:microsoft:sqlserver://127.0.0.1:1433;user=dbusername; password=dbpwdname;
DatabaseName=dbname;

The protocol should be jdbc. The subprotocol represents database type. This may be
Microsoft SQL Server, Oracle, MySql, jdbdc:odbc and so forth. In the above case, it is
Microsoft SQL Server. In other parameter category, IP address of the system, port number
such as 1433 for Microsoft SQL Server and name, password, username of the database. The
above definition is depends on the type of JDBC driver and database vendor.

The Connection Interface
This interface declares the methods that can be used to create a connection for particular
database. An instance of the connection interface is obtained from getConnection() method of the DriverManager class. After creating the connection with database, you can execute SQL statements for that particular connection and retrieve the results.
Some of the methods are listed below:

createStatement() method

The createStatement() method is used to create a statement object for sending SQL
statements to the database. If you are using the same SQL statement in your application many
times, it is more efficient and suitable to use a PreparedStatement object. Its signature of
createStatment() method is as follows:

public Statement createStaement() throws SQLException

prepareStatement() method
This method is used to create a PreparedStatement object. Its signature is as follows:

public PreparedStatement prepareStatement() throws SQLExecption

close() method
The close() method is used to immediately close and release a Connection object. Its
signature is as follows:
public void close() throws SQLException

The Statement Interface

The statement interface provides methods for executing SQL statements and obtains the
results that are produced. The following is the two important method of Statement interface
i.e. executeQuery() and executeUpdate(). Both are used for execution of SQL queries. The
signature for both methods is as follows:
public int executeUpdate(String sql) throws SQLException
public ResultSet executeQuery(String sql) throws SQLException

executeUpdate() method
The executeUpdate() method is used to executes an insert, delete, update SQL statements and
also DDL statements to create, drop and alter tables. It returns the row count for insert,
update, delete statement and returns zero(0) for SQL statement that return nothing.

executeQuery() method
The executeQuery() method is used to executes an SQL select statement that returns a single
ResultSet object which contains data.

execute() method
execute() method is used to execute stored procedure.

Prepared and Callable Statement Interface

Prepared Statement Interface
When you are used same SQL statements over and over again changing only parameter
values, it is best way to use prepared statement i.e. java.sql.PreparedStatement. The
PreparedStatement differ from simple Statement in that it specified fill-in-blanks SQL
template.

For example, “insert into tablename values (?, ?)”

There is a two unknown parameter in the form of question marks which are set at the run time
by your application using setXXX() method such as setString(), setInt(). The
PreparedStatement is a precompiled statement and has a query plan generated for it once. It is
mainly used to speed up the process of insertion, deletion and updation especially when there
is a bulk processing. When the program sends SQL queries to database engine, the query is
parsed, compiled and optimized, the outcome of this process is called query plan.

For example:
PreparedStatement pstmt = con.prepareStatement(“update product_table set productcode = ?
where product_name like ?”);

pstmt.setString(1, P001);
pstmt.setString(2, “Washing Machine”);
ResultSet rs = pstmt.executeUpdate();

Here, ? represents a modifiable value in SQL statement. You can set the value of unknown
parameter i.e. question mark with setXXX() method which specifies a parameter number and
a value.

The Callable Statement Interface
A Callable statement is used for handling stored procedure. A stored procedure is one or
more SQL statement stored as a one group in a compiled form inside a database engine. You
can simply call the store procedure as you call a method call. It is an extension of
PreparedStatement. The CallableStatement’s object is obtained from
Connection.prepareCall() method and set the parameter using setXXX() methods. The
execute() method is used to execute stored procedure.

For example:

CallableStatement cs = con.prepareCall(“{call myProcedure}”);
ResultSet rs = cs.executeQuery();

The ResultSet Interface
The ResultSet interface represents an object that contains data in the form of row and column.
The ResultSet object maintains record pointer that points to its current row of data. Initially,
the cursor is positioned before the first row. By default, the record pointer of ResultSet object
is forward only scrollable. You can traverse through the ResultSet using next() method only.
The next() method returns false when the last record is reached and no more details can than
be retrieved. The java.sql.ResultSet interface provides several methods for retrieving column