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:—
- DriverManager class
- Driver interface
- Connection interface
- Statement interface
- ResultSet interface
- PreparedStatement interface
- 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.