JDBC Classes and Interfaces

JDBC Classes:

1.   DriverManager

2.   SQLException

DriverManager:

       The DriverManager class is the management layer of JDBC, working between the user and the drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. In addition, the DriverManager class attends to things like driver login time limits and the printing of log and tracing messages.

Methods Of DriverManager Class:

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 url):	is used to establish the connection with the specified url.
4) public static Connection getConnection(String url,String userName,String password):	is used to establish the connection with the specified url, username and passw

For simple applications, the only method in this class that a general programmer needs to use directly is DriverManager.getConnection. As its name implies, this method establishes a connection to a database. JDBC allows the user to call the DriverManager methods getDriver, getDrivers, and registerDriver as well as the Driver method connect, but in most cases it is better to let the DriverManager class manage the details of establishing a connection.

SQLException:

With the help of exception handling we can take the appropriate action in case something goes wrong like committing the data or rolling it back etc. JDBC execute statement or connection related exception throws SQLException which is a checked Exception

SQLException

SQLException class is available in java.sql package and extends Exception class which means all methods that we used to use in Exception will be available in SQLException also. There are other relevant methods added in SQLException class which we will discuss in this section.

SQLException Useful methods:

                There are several methods available but below are the most commonly used methods.

1.   int getErrorCode()-  this method can be used to get the vendor-specific exception or error code.

2.   SQLException getNextException()- This method is used to get the chained exception which is being set using  setNextException(SQLException ex) method.

3.   Iterator iterator()-  This method is used to iterate all the chained exceptions in SQL Exception.

4.   void setNextException(SQLException ex)- This method is used to add another SQL exception in chain.

5.   String getSQLState() – This method is used to retrieve the SQLState for SQLException object. This method can return null as well.

JDBC Interface:

1.   Connection

2.   .Statement

3.   PreparedStatement

4.   ResultSet

5.   ResultSetMetaData

6.   DataBaseMetaData

Connection:

      A Connection object represents a connection with a database. A connection session includes the SQL statements that are executed and the results that are returned over that connection. A single application can have one or more connections with a single database, or it can have connections with many different databases. 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: 

A Statement object is used to send SQL statements to a database. There are actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection: Statement, PreparedStatement, which inherits from Statement, and CallableStatement, which inherits from PreparedStatement.

The Statement interface provides basic methods for executing statements and retrieving results.

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.

Creating Statement Objects

Once a connection to a particular database is established, that it can be used to send SQL statements. A Statement object is created with the Connection method createStatement, as in the following code:  

    Connection con = DriverManager.getConnection(url, "sunny", "");

    Statement stmt = con.createStatement();

The SQL statement that will be sent to the database is supplied as the argument to one of the methods for executing a Statement object:

    ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

PreparedStatemen :

The PreparedStatement interface inherits from Statement. PreparedStatement accepts runtime values and these values can be passed as parameters. This interface is useful to execute the same SQL command multiple times, like inserting number of records. This gives a better performance.

Following is the interface signature

public interface PreparedStatement extends Statement

As the signature indicates, the PreparedStatement interface is derived from Statement interface.

Let us see a snippet of code of using PreparedStatement.

PreparedStatement pst = con.prepareStatement(“insert into Employee (empid, empname, empsal) values (?, ?, ?)”);

where con is an object of Connection interface.

The questions marks in the above statement are called as parameters. The values are represented as question marks as their values are not known at compile time but known as runtime only. As values can be inserted dynamically, PreparedStatement is used for dynamic SQL statements executed repeatedly like inserting number of records one-by-one.

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 value)	sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value)	sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value)	sets the double value to the given parameter index.
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.

ResultSet:

           A ResultSet contains all of the rows which satisfied the conditions in an SQL statement, and it provides access to the data in those rows through a set of get methods that allow access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making the next row become the current row.

Note :- By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well as we can make this object as updatable by:

 

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 columnIndex):	is used to return the data of specified column index of the current row as int.
8) public int getInt(String columnName):	is used to return the data of specified column name of the current row as int.
9) public String getString(int columnIndex):	is used to return the data of specified column index of the current row as String.
10) public String getString(String columnName):	is used to return the data of specified column name of the current row as String.

ResultSetMetaData:

What is metadata of a table?
The number of columns, data type of each column, name of each column, primary keys etc. constitutes the metadata of a table. Actual data refers the records in the table. Using JDBC, metadata of a table can be retrieved. The ResultSetMetaData interface can hold the metadata of the table returned by the database.

In the following program, records are printed along with column names (using metadata).

import java.sql.*;

public class MetaData

{

  public static void main(String args[]) throws Exception

  {

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

    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");

    Statement stmt = con.createStatement();

                    

    ResultSet res = stmt.executeQuery("select *from Employee");

                                           // extract meta data from the ResultSet object

    ResultSetMetaData rsmd = res.getMetaData();

                                           // to print column names

    for( int i = 0; i < rsmd.getColumnCount(); i++)

    {

      System.out.print( rsmd.getColumnLabel(i+1) + "\t");

    }

                                           // column numbers start from 1

    System.out.println();                  // to give a new line

                                           // to print the records

    while(res.next())

    {

      System.out.println(res.getInt(1) + "\t" + res.getString(2) + "\t" + res.getDouble(3));    

    }

    res.close();

    stmt.close();

    con.close();

  }

}

1. getMetaData() is a method of ResultSet interface that returns an object of ResultSetMetaData interface.
2. ResultSetMetaData object contians the metadata of the table.
3. getColumnCount() and getColumnLabel() methods of ResultSetMetaData interface returns the number of columns(fields) of a table and label(name) of each column respectively.
4. Column numbers start from 1 where as loop starts from 0. For this reason, i+1 is given as method parameter.

 

DatabaseMetaData:

Using DatabaseMetaData interface, we can obtain the database specific metadata like the functions database supports, the driver used, its info and types permitted etc. This interface is used mostly by tool developers to know database-specific information.

Many methods exist with which the database can be queried. Following program illustrate the usage of few of the above methods.

import java.sql.*;

public class DatabaseMetaDataDemo
{
  public static void main(String args[]) throws Exception
  {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott","tiger");

    DatabaseMetaData dbmd = con.getMetaData();
    System.out.println("Numeric Function:  " + dbmd.getNumericFunctions());
    System.out.println("String Functions: " + dbmd.getStringFunctions());
    System.out.println("System Functions: " + dbmd.getSystemFunctions());
    System.out.println("Search String Escape: " + dbmd.getSearchStringEscape());
    System.out.println("Schema Term: " + dbmd.getSchemaTerm());                          
    System.out.println("Supports Stored Procedures:  " + dbmd.supportsStoredProcedures());  
    System.out.println("Get Max Binary Literal Length: " + dbmd.getMaxBinaryLiteralLength());
  }       
}

 

All the methods are self-explanatory. The next program gets the driver information.

import java.sql.*;

public class DatabaseMetaDataDemo

{

  public static void main(String args[]) throws Exception

  {

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

    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott","tiger");

    DatabaseMetaData dbmd = con.getMetaData();

    System.out.println("Numeric Function:  " + dbmd.getNumericFunctions());

    System.out.println("String Functions: " + dbmd.getStringFunctions());

    System.out.println("System Functions: " + dbmd.getSystemFunctions());

    System.out.println("Search String Escape: " + dbmd.getSearchStringEscape());

    System.out.println("Schema Term: " + dbmd.getSchemaTerm());                          

    System.out.println("Supports Stored Procedures:  " + dbmd.supportsStoredProcedures());  

    System.out.println("Get Max Binary Literal Length: " + dbmd.getMaxBinaryLiteralLength());

  }        

}