Issuing Queries
To process SQL statements in a JDBC-based application, create a
Statement object from your
Connection object.
Statement objects support an
executeUpdate() method for issuing queries that modify the database and return no result set, and an
executeQuery() method for queries that do return a result set. The query-processing examples in this article use the following table,
animal, which contains an integer
id column and two string columns,
name and
category:
CREATE TABLE animal
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name CHAR(40),
category CHAR(40)
)id is an
AUTO_INCREMENT column, so MySQL automatically assigns successive values 1, 2, 3, ... as records are added to the table.
Issuing Queries That Return No Result Set
The following example obtains a
Statement object from the
Connection object, then uses it to create and populate the
animal table.
DROP TABLE,
CREATE TABLE, and
INSERT all are statements that modify the database, so
executeUpdate() is the appropriate method for issuing them:
Statement s = conn.createStatement ();
int count;
s.executeUpdate ("DROP TABLE IF EXISTS animal");
s.executeUpdate (
"CREATE TABLE animal ("
+ "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
+ "PRIMARY KEY (id),"
+ "name CHAR(40), category CHAR(40))");
count = s.executeUpdate (
"INSERT INTO animal (name, category)"
+ " VALUES"
+ "('snake', 'reptile'),"
+ "('frog', 'amphibian'),"
+ "('tuna', 'fish'),"
+ "('racoon', 'mammal')");
s.close ();
System.out.println (count + " rows were inserted");The
executeUpdate() method returns the number of rows affected by a query. As shown above, the count is used to report how many rows the
INSERT statement added to the
animal table.
A
Statement object may be used to issue several queries. When you're done with it, invoke its
close() method to dispose of the object and free any resources associated with it.
Issuing Queries That Return a Result Set
For statements such as
SELECT queries that retrieve information from the database, use
executeQuery(). After calling this method, create a
ResultSet object and use it to iterate through the rows returned by your query. The following example shows one way to retrieve the contents of the
animal table:
Statement s = conn.createStatement ();
s.executeQuery ("SELECT id, name, category FROM animal");
ResultSet rs = s.getResultSet ();
int count = 0;
while (rs.next ())
{
int idVal = rs.getInt ("id");
String nameVal = rs.getString ("name");
String catVal = rs.getString ("category");
System.out.println (
"id = " + idVal
+ ", name = " + nameVal
+ ", category = " + catVal);
++count;
}
rs.close ();
s.close ();
System.out.println (count + " rows were retrieved");executeQuery() does not return a row count, so if you want to know how many rows a result set contains, you should count them yourself as you fetch them.
To obtain the column values from each row, invoke
getXXX() methods that match the column data types. The
getInt() and
getString() methods used in the preceding example return integer and string values. As the example shows, these methods may be called using the name of a result set column. You can also fetch values by position. For the result set retrieved by the
SELECT query in the example,
id,
name, and
category are at column positions 1, 2 and 3 and thus could have been obtained like this:
int idVal = rs.getInt (1);
String nameVal = rs.getString (2);
String catVal = rs.getString (3);
ResultSet objects, like
Statement objects, should be closed when you're done with them.
To check whether or not a column value is
NULL, invoke the result set object's
wasNull() method after fetching the value. For example, you could check for a
NULL value in the
name column like this:
String nameVal = rs.getString ("name");
if (rs.wasNull ())
nameVal = "(no name available)";
