JDBC Code Examples
11 October 2006Â
Accessing Databases with JDBC
JDBC has two parts: the JDBC Core Application Programming Interface (API) and the JDBC Optional Package API.
The java.sql Package
The java.sql package provides the API for accessing and processing data in a data source. The most important members of the java.sql package are as follows:
• The DriverManager class
• The Driver interface
• The Connection interface
• The Statement interface
• The ResultSet interface
• The PreparedStatement interface
• The ResultSetMetaData interface
The DriverManager Class
The DriverManager class provides static methods for managing JDBC drivers. Each JDBC driver you want to use must be registered with the DriverManager. The JDBC driver of the database to which you want to connect is supplied either by the database vendor or a third party.
try {
Class.forName(”JDBC.driver”);
}
catch (ClassNotFoundException e) {
// driver not found
}
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)
The 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.
The 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.
close
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
isClosed
You use this method to test whether the Connection object is closed. The signature of this method is as follows:
public boolean isClosed() throws SQLException
createStatement
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
prepareStatement
You use the prepareStatement method to create a PreparedStatement object. Its signature is as follows:
public PreparedStatement prepareStatement()throws SQLException
getAutoCommit
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.
setAutoCommit
The setAutoCommit method sets the auto-commit state of the Connection object. Its signature is as follows:
public void setAutoCommit(boolean autocommit) throws SQLException
commit
You use the commit method to commit a transaction. The signature of this method is as follows:
public void commit() throws SQLException
rollback
You use the rollback method to roll back a transaction. Its signature is as follows:
public void rollback() throws SQLException
The Statement Interface
You 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.
executeQuery
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
executeUpdate
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)
The 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.
isFirst
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
isLast
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
next
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
getMetaData
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, you 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
The PreparedStatement Interface
The PreparedStatement interface extends the Statement interface and represents a precompiled SQL statement. You use an instance of this interface to execute efficiently an SQL statement multiple times.
The ResultSetMetaData Interface
The ResultSetMetaData interface represents the meta data of a ResultSet object. The following sections introduce the most important methods.
Metadata: contains information about information
getColumnCount
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
getColumnName
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.
Four Steps to Getting to the Database
1. Load the JDBC database driver.
2. Create a connection.
3. Create a statement.
4. Create a resultset, if you expect the database server to send back some data.
Step 1: Load a JDBC Database Driver
Type 1 drivers: are drivers that provide access to ODBC drivers and also are called the JDBC-ODBC Bridge driver
Type 2 drivers: are drivers written in part Java and part native API to convert JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
Type 3 drivers: are drivers written to use the network protocol to translate JDBC API calls into a DBMS-independent net protocol, which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases.
Type 4 drivers: are pure Java. They are written to use the native protocol to convert JDBC technology calls into the network protocol directly used by DBMSs. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access.
Step 2: Creating a Connection
After you register a JDBC driver with the DriverManager, you can use it to get a connection to the database. In JDBC, a database connection is represented by the java.sql.Connection interface. You use the DriverManager class’s getConnection method to obtain a Connection object.
public static Connection getConnection(String url, String user, String password) throws SQLException
The url part is the trickiest element in this method. The url is of this syntax:
jdbc:subprotocol:subname
If you are using a JDBC-ODBC bridge driver, the subprotocol is “odbc” and the subname is the Data Source Name (DSN) for that database. For instance, for a DSN called MarketingData, your URL will be:
jdbc:odbc:MarketingData
If you want to connect to a MySQL database, the subprotocol part is “mysql” and the subname part should be given the name of the machine and the database. For example, for a database named Fred, use the following:
jdbc:mysql///Fred
Step 3: Creating a Statement
Its signature is as follows:
public Statement createStatement() throws SQLException
Therefore, to create a Statement object from an open Connection object, you write the following:
// connection is an open Connection object
Statement statement = connection.createStatement();
Next, use the methods in the Statement class interface to manipulate your data or data structure. You will use two important methods: executeUpdate and executeQuery. The signatures for both methods are as follows:
public int executeUpdate(String sql) throws SQLException
public ResultSet executeQuery(String sql) throws SQLException
The executeUpdate method executes an SQL INSERT, UPDATE, or DELETE statement and also data definition language (DDL) statements to create, drop, and alter tables. This method returns the row count for INSERT, UPDATE, or DELETE statements or returns 0 for SQL statements that return nothing
executeQuery method executes an SQL SELECT statement that returns data. The ResultSet object contains the data produced by the given query. This method never returns a null.
Step 4: Creating a ResultSet
A ResultSet is the representation of a database table that is returned from a Statement object. A ResultSet object maintains a cursor pointing to its current row of data. When the cursor is first returned, it is positioned before the first row. To access the first row of the ResultSet, you need to call the next() method of the ResultSet interface.
on. getShort You use the getInt method, for example, to obtain the value of the designated column in the current row of this ResultSet object as an int in the Java programming language. The getLong gets the cell data as a long, etc. The most commonly used method is getString, which returns the cell data as a String. Using getString is preferable in many cases because you don’t need to worry about the data type of the table field in the database.
The getString method, similar to other getXXX methods, has two overloads that allow you to retrieve a cell’s data by passing either the column index or the column name. The signatures of the two overloads of getString are as follows:
public String getString(int columnIndex) throws SQLException
public String getString(String columnName) throws SQLException
String sql = “SELECT FirstName, LastName FROM Users”;
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + “:” + resultSet.getString(”LastName”) );
}
resultSet.close();
Always call the close method to explicitly close the ResultSet object.
Example
try {
Class.forName(”com.internetcds.jdbc.tds.Driver”);
Connection con = DriverManager.getConnection(
“jdbc:freetds:sqlserver://Lampoon/Registration”,
“sa”, ” s3m1c0nduct0r”);
System.out.println(”got connection”);
Statement s = con.createStatement();
String sql =
“INSERT INTO UserReg VALUES (’a', ‘b’, ‘12/12/2001′, ‘f’)”;
s.executeUpdate(sql);
sql = “SELECT FirstName, LastName FROM Users”;
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1) + ” ” + rs.getString(2));
}
rs.close();
s.close();
con.close();
}
catch (ClassNotFoundException e1) {
System.out.println(e1.toString());
}
catch (SQLException e2) {
System.out.println(e2.toString());
}
catch (Exception e3) {
System.out.println(e3.toString());
}
Connection Pooling
When the application starts, a certain number of Connection objects are created and stored in a pool. When a database client, such as a servlet, needs to use a Connection object, it does not create the object but instead requests one from the pool. When the client is finished with it, the Connection object is returned to the pool.
Transactions
By default, a Connection object’s auto-commit state is true, meaning that the database is updated when an SQL statement is executed.
No comments yet