3.4.  Use the proper JDBC API to submit queries and read results from the database

[Note]

Creating Statements

A java.sql.Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a java.sql.Connection object to create a Statement object.

For example, you can create Statement object with the following code:

Statement stmt = con.createStatement();
					

There are three different kinds of statements:

  1. Statement: Used to implement simple static SQL statements with NO parameters.

  2. PreparedStatement (extends Statement): Used for precompiling SQL statements that might contain input parameters.

  3. CallableStatement (extends PreparedStatement): Used to execute stored procedures that may contain both input and output parameters.

Executing Queries

To execute a query, call an execute(...) method from Statement such as the following:

Processing ResultSet Objects

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

For example, you can repeatedly call the method ResultSet.next() to move the cursor forward by one row. Every time you call next(), the method outputs the data in the row where the cursor is currently positioned.

The ResultSet interface provides getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column. NOTE: column names used as input to getter methods are case insensitive.

In general, using the column index will be more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

while (resultSet.next()) {
    int id = resultSet.getInt("ID");
    String name = resultSet.getString("NAME");
    System.out.println("ID: " + id);
    System.out.println("NAME: " + name);
    System.out.println();
}
					

You can also get data from ResultSet by column index (NOTE: first column has index 1, not 0):

while (resultSet.next()) {
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    System.out.println("ID: " + id);
    System.out.println("NAME: " + name);
    System.out.println();
}
					

Professional hosting         Free 'Oracle Certified Expert Web Services Developer 6' Guide     Free SCDJWS 5.0 Guide