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

3.4. Use the proper JDBC API to submit queries and read results from the database
Prev	Chapter 3. Describe the JDBC API	Next


	[JDBC_TUTORIAL_STATEMENT] Processing SQL Statements with JDBC

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:

Statement: Used to implement simple static SQL statements with NO parameters.
PreparedStatement (extends Statement): Used for precompiling SQL statements that might contain input parameters.
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:

boolean execute(String sql) throws SQLException: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResutSet.
ResultSet executeQuery(String sql) throws SQLException: Executes the given SQL statement, which returns a single ResultSet object.
```
 ResultSet rs = stmt.executeQuery("SELECT * FROM PERSON");
								
```
Note:This method CANNOT be called on a PreparedStatement or CallableStatement.
int executeUpdate(String sql) throws SQLException: Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Note:This method CANNOT be called on a PreparedStatement or CallableStatement.

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();
}

Prev	Up	Next
3.3. Use JDBC transactions	Home	3.5. Use JDBC `PreparedStatement` and `CallableStatement`