JDBC API and JDBC Drivers

JDBC API

JDBC is java API that is used to connect java application to a database. The JDBC API defines a set of interfaces that encapsulate major database functionality, which are explained one by one. This includes running queries, processing results, and determining configuration information.

A JDBC driver is written by a database vendor or third-party developer, which is a set of classes that implements these interfaces for a particular database system.

Every database has its own API that you must learn to write the program that interacts with the database. Cross-database APIs exist, most notably Microsoft’s ODBC API, but these tend to find themselves, at best, limited to a particular platform.

“To create a platform-neutral interface between database and java is JDBC Sun’s attempt.”

There are four types of JDBC drivers in use today, which are following:

Type 1: JDBC-ODBC bridge plus an ODBC driver

Sun implement jdbc API which is designed to allow access to ODBC standard, implementation consists of the sun.jdbc.odbc package of 22 classes that implement the java.sql interface. Not only this, it also consists of the JdbcOdbc.dll, which is affectionately called the jdbc-odbc bridge.

Each jdbc call in an application internally invokes an odbc function so convert java type to odbc type and invoke odbc function.

Therefore this technology is not suitable for a high transaction environment and complete java command set is not supported by the Type 1 drivers and is limited by the functionality of odbc driver.

Type 2: Native API, part-java driver

A type 2 JDBC driver is a native API, part-java driver. To convert JDBC calls into native calls of the major database vendor APIs, Type 2 drivers are used.

Type 2 drivers force developers to write platform-specific code, which is something that no java developer really wants to do. Each database vendor’s provide different driver for different OS.

Type 3: Pure java driver for database middleware

JDBC calls are translated into a middleware vendor’s protocol, and the middleware converts those calls into a database’s API. To connect to multiple databases, developers can also use a single JDBC driver.

Type 4: Pure java driver for direct-to-database

Type 4 driver take JDBC calls and translates them into the network protocol (proprietary protocol) which is used directly by the DBMS.

Direct call make to the DBMS server by the client machines or Application server. The requirement to each DBMS is to have own Type 4 driver.

JDBC API

A java.sql and javax.sql package provides classes and interfaces that form JDBC API.

Commonly used classes and interfaces:

1. DriverManager
2. Connection
3. ResultSet
4. Statement
5. PreparedStatement
6. CallableStatement
7. SQLException

Only DriverManger is a class rest all are interfaces.

DriverManager: It is a utility class that abstracts the functionality of connecting a java application to a database. This class is responsible for identifying, locating and using required driver to create a Connection with the database.

Methods:

public static Connection getConnection(String url) throws SQLException

p. static Connection getConnection(String url, String user, String password) throws SQLException

Connection: It provides the abstraction of a database connection as well as functionality of creating statements.

Methods:

public Statement createStatement() throws SQLException

public PreparedStatement prepareStatement(String sql) throws SQLException

public CallableStatement prepareCall(String sql) throws SQLException

Following overlaoaded form of createStatement and prepareStatement are used to create scrollable and updatable resultSet.

public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException

public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException

public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException

resultSetType - a result set type; one of

ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
resultSetConcurrency - a concurrency type; one of ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE

Statement: A statement provides the facility of sql queries over a sql connection.

Methods:

public ResultSet executeQuery(String sql) throws SQLException
public int executeUpdate(String sql) throws SQLException
public boolean execute(String sql) throws SQLException
public int executeUpdate(String sql, String[] columnNames) throws SQLException
public boolean execute(String sql, String[] columnNames) throws SQLException

PreparedStatement: It provides the facility of executing parameterized query.

Methods:

public boolean execute() throws SQLException
public ResultSet executeQuery() throws SQLException
ublic int executeUpdate() throws SQLException
Setter methods:
public void setDate(int parameterIndex, Date x) throws SQLException
public void setFloat(int parameterIndex, float x) throws SQLException
public void setString(int parameterIndex, float x) throws SQLException
public void setDouble(int parameterIndex, float x) throws SQLException

CallableStatement: It provides the facility to executed stored procedure and function.

No query executer methods.

ResultSet: It is used to store the result of select query and provides method to fetch individual records.

Methods:

public boolean first() throws SQLException
public boolean last() throws SQLException
public boolean next() throws SQLException
public boolean previous() throws SQLException
public boolean isFirst() throws SQLException
public boolean isLast() throws SQLException

Getter methods:

public Date getDate(String columnLabel) throws SQLException
public float getFloat(String columnLabel) throws SQLException
public int getInt(int columnIndex) throws SQLException
public Object getObject(int columnIndex) throws SQLException
public Time getTime(int columnIndex) throws SQLException
public String getString(String columnLabel) throws SQLException
All getType() method cany be access by columnLable and columnIndex.

SQLException: super class of all the database related exception.

Steps:

1. Load a driver class

forName() method of class Class is used for that purpose

2. Create a connection object

getConnection() method of DriverManager class is used to create a connection object.

3. Create a statement object

createStatement() method of Connection object is used to create an object.

4. Execute query

executeQuery() method of Statement object is used for executing query.

5. Close the Connection, ResultSet, and Statement

Close() of Connection implicitly close Statement and ResultSet.

Example:

Connection con;

try {

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

            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb", "root", "root");

            Statement stmt = con.createStatement();

            ResultSet rset = stmt.executeQuery("select * from login");

            while (rset.next()) {

            System.out.println(rset.getString(1) + " : " + rset.getString(2));

            }

        } catch (Exception ex) {           

        } finally {

            try {

                con.close();

            } catch (SQLException ex) { }

        }