JDBC Application Programming Interface (API)

The java.sql package provides the API for accessing and processing data in a data source. The most important members of this package are as follows:—

  1. DriverManager class
  2. Driver interface
  3. Connection interface
  4. Statement interface
  5. ResultSet interface
  6. PreparedStatement interface
  7. ResultSetMetaData interface

Each of the types is briefly discussed in the following sections.


1. DriverManager class

The DriverManager class provides static methods for managing JDBC drivers. Each JDBC driver we want to use must be registered with the DriverManager. The JDBC driver of the database to which we want to connect is supplied either by the database vendor or a third party. We use different JDBC drivers for different database servers. For example, the JDBC driver for MySQL Server is different from the one used to access Oracle databases. To load a JDBC driver from a Java application we use the following code snippet:


In this case, JDBC.driver is the fully-qualified name of the JDBC driver class. This name can be found in the documentation accompanying the JDBC driver. The DriverManager class’s most important method is getConnection() that returns a java.sql.Connection object. This method has three overloads whose signatures 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)


2. Driver interface

The Driver interface is implemented by every JDBC driver class. The driver class itself is loaded and registered with the DriverManager, and the DriverManager can manage multiple drivers for any given connection request. In the case where there are multiple drivers registered, the DriverManager will ask each driver in turn to try to connect to the target URL.


3. Connection interface

The Connection interface represents a connection to the database. An instance of the Connection interface is obtained from the getConnection method of the DriverManager class. The following are some important methods of the Connection interface—


The close() method immediately closes and releases a Connection object instead of waiting for it to be released automatically. Its signature is as follows:

public void close() throws SQLException


We use isClosed() method to test whether the Connection object is closed. The signature of this method is as follows:

public boolean isClosed() throws SQLException


The createStatement() method is used to create a Statement object for sending SQL statements to the database. If the same SQL statement is executed many times, it is more efficient to use a PreparedStatement object.

This method has two overloads with the following signatures:

public Statement createStatement () throws SQLException


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


We use the prepareStatement() method to create a PreparedStatement object. Its signature is as follows:

public PreparedStatement prepareStatement()throws SQLException


The getAutoCommit() method returns a boolean specifying the current auto-commit state. The signature of this method is as follows:

public boolean getAutoCommit() throws SQLException

This method returns true if auto-commit is enabled and false if auto-commit is not enabled. By default, auto-commit is enabled.


The setAutoCommit() method sets the auto-commit state of the Connection object. Its signature is as follows:

public void setAutoCommit(boolean autocommit) throws SQLException


We use the commit() method to commit a transaction. The signature of this method is as follows:

public void commit() throws SQLException


We use the rollback() method to roll back a transaction. Its signature is as follows:

public void rollback() throws SQLException


4. Statement Interface

We use the statement interface method to execute an SQL statement and obtain the results that are produced. The two most important methods of this interface are executeQuery() and executeUpdate().


The executeQuery() method executes an SQL statement that returns a single ResultSet object. Its signature is as follows:

public ResultSet executeQuery(String sql) throws SQLException


The executeUpdate() method executes an insert, update, and delete SQL statement. The method returns the number of records affected by the SQL statement execution, and its signature is as follows:

public int executeUpdate(String sql)


5. ResultSet Interface

The ResultSet interface represents a table-like database result set. A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The following are some important methods of the ResultSet interface:


The isFirst() method indicates whether the cursor points to the first record in the ResultSet. Its signature is as follows:

public boolean isFirst() throws SQLException


The isLast() method indicates whether the cursor points to the last record in the ResultSet. Its signature is as follows:

public boolean isLast() throws SQLException


The next() method moves the cursor to the next record, returning true if the current row is valid and false if there are no more records in the ResultSet object. The method’s signature is as follows:

       public boolean next() throws SQLException


The getMetaData() method returns the ResultSetMetaData object representing the meta data of the ResultSet. The signature of the method is as follows:

public ResultSetMetaData getMetaDate() throws SQLException

In addition to the previous methods, we can use several getXXX() methods to obtain the value of the specified column in the row pointed by the cursor.

In this case, XXX represents the data type returned by the method at the specified index, and each getXXX() method accepts the index position of the column in the ResultSet. The column index 1 indicates the first column. The signature of this method is as follows:

public XXX getXXX(int columnIndex) throws SQLException

For example, the getString() method has the following signature and returns the specified cell as String:

public String getString(int columnIndex) throws SQLException


6. PreparedStatement Interface

The PreparedStatement interface extends the Statement interface and represents a pre-compiled SQL statement. We use an instance of this interface to execute efficiently an SQL statement multiple times.


7. ResultSetMetaData Interface

The ResultSetMetaData interface represents the meta data of a ResultSet object. The most important methods are given below.


The getColumnCount() method returns the number of columns in the ResultSet whose meta data is represented by the ResultSetMetaData object. Its signature is as follows:

public int getColumnCount() throws SQLException


The getColumnName() method returns the column name as the specified column index. Its signature is as follows:

public String getColumnName(int columnIndex) throws SQLException

The first column is indicated by index number 1.

In the next section, we will learn the basic steps of developing JDBC code to access and manipulate tables in MySQL RDBMS.