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