Managing & Using MySQL, 2nd Edition

Queries and Result Sets

Queries are a bit more complicated than updates because queries return information from the database in the form of a ResultSet. A ResultSet is an interface that represents zero or more rows matching a database query. A JDBC Statement has an executeQuery( ) method that works like the executeUpdate( ) method, except it returns a ResultSet from the database. Exactly one ResultSet is returned by executeQuery( ). JDBC supports the retrieval of multiple result sets, but MySQL does not. You may notice code for multiple result sets if you look at code written for another database.

Example 13-5 shows a simple query. Figure 13-2 shows the data model behind the test table.

Example 13-5. A simple query

import java.sql.*; import java.util.*; public class Select { public static void main(String argv[]) { Connection con = null; ResourceBundle bundle = ResourceBundle.getBundle("SelectResource"); try { String url = bundle.getString("URL"); Statement stmt; ResultSet rs; Class.forName(bundle.getString("Driver")); // here is where the connection is made con = DriverManager.getConnection(url, "user", "pass"); stmt = con.createStatement( ); rs = stmt.executeQuery("SELECT * from TEST ORDER BY TEST_ID"); System.out.println("Got results:"); while(rs.next( )) { int a= rs.getInt("TEST_ID"); String str = rs.getString("TEST_VAL"); System.out.print(" key= " + a); System.out.print(" str= " + str); System.out.print("\n"); } stmt.close( ); } catch( SQLException e ) { e.printStackTrace( ...