Writing JDBC Applications with MySQL - Issuing Queries

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)";