EXPLORING THE RESULTSET INTERFACE in Font

Creator PDF-417 2d barcode in Font EXPLORING THE RESULTSET INTERFACE

The following code shows how to set up the Oracle database: SQL> create table employees ( 2 id varchar(10) not null primary key, 3 name varchar(20) not null, 4 age int 5 ); Table created. SQL> desc employees; Name Null ---------------- -------ID NOT NULL NAME NOT NULL AGE SQL> insert into SQL> insert into SQL> insert into SQL> insert into SQL> insert into SQL> commit; Commit complete.

Type -----------VARCHAR2(10) VARCHAR2(20) NUMBER(38) name, name, name, name, name) age) values('11', 'Alex Smith', 25); age) values('22', 'Don Knuth', 65); age) values('33', 'Mary Kent', 35); age) values('44', 'Monica Seles', 30); values('99', 'Alex Edison');

employees(id, employees(id, employees(id, employees(id, employees(id,

SQL> select id, name, age from employees; ID ---------11 22 33 44 99 NAME AGE -------------------- ---------Alex Smith 25 Don Knuth 65 Mary Kent 35 Monica Seles 30 Alex Edison

The following code shows how to set up the MySQL database: create table employees ( id varchar(10) not null primary key, name varchar(20) not null, age int ); insert insert insert insert insert mysql> into employees(id, name, into employees(id, name, into employees(id, name, into employees(id, name, into employees(id, name) select * from employees; age) values('11', 'Alex Smith', 25); age) values('22', 'Don Knuth', 65); age) values('33', 'Mary Kent', 35); age) values('44', 'Monica Seles', 30); values('99', 'Alex Edison');

+----+--------------+------+ | id | name | age | +----+--------------+------+ | 11 | Alex Smith | 25 | | 22 | Don Knuth | 65 | | 33 | Mary Kent | 35 | | 44 | Monica Seles | 30 | | 99 | Alex Edison | NULL | +----+--------------+------+ Five Rows in a Set (0.00 Sec)

I will now provide a simple Java class to demonstrate how to use ResultSet by querying the employees table and retrieving employee information from a database. Note that the age column can accept null values as well; because of this, after getting the value of age from a ResultSet, you check (by invoking ResultSet.wasNull()) to see if it is a null value. import java.sql.*; import jcb.db.VeryBasicConnectionManager; import jcb.util.DatabaseUtil; public class DemoResultSet { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { System.out.println("--DemoResultSet begin--"); String dbVendor = args[0]; // { "mysql", "oracle" } conn = VeryBasicConnectionManager.getConnection(dbVendor); System.out.println("conn="+conn); System.out.println("---------------"); // prepare query String query = "select id, name, age from employees"; // create a statement stmt = conn.createStatement(); // execute query and return result as a ResultSet rs = stmt.executeQuery(query); // extract data from the ResultSet while (rs.next()) { String id = rs.getString(1); System.out.println("id="+id); String name = rs.getString(2); System.out.println("name="+name); // age might be null (according to schema) int age = rs.getInt(3); if (rs.wasNull()) { System.out.println("age=null"); }

else { System.out.println("age="+age); } System.out.println("---------------"); } System.out.println("--DemoResultSet end--"); } catch(Exception e){ e.printStackTrace(); System.exit(1); } finally { // release database resources DatabaseUtil.close(rs); DatabaseUtil.close(stmt); DatabaseUtil.close(conn); } } } The important methods and concepts of this solution are as follows: The getConnection() method gets a database connection for the sample Oracle database. createStatement() creates a Statement object for sending SQL statements to the database. executeQuery() is used for Statement objects that return a ResultSet, which is basically a SELECT statement. next() moves the cursor down one row from its current position. The first next() sets the cursor on the first row; the next() method enables you to iterate through all the records retrieved. getString(int columnIndex) retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language. You use getString(1) and getString(2) to get the id and name, respectively. (In the query, id is defined in the first column, and name is defined in the second column.) Note that when using column index, the first column is 1, the second is 2, and so on. getInt(int columnIndex) retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language. You use getInt(3) to get the age of an employee (note that age is defined in the third column of the query). wasNull() reports whether the last column read had a value of SQL NULL. The ResultSet object s getXXX() methods (such as getString() and getInt()) retrieve column data. JDBC defines types to match the SQL data types, and there is a getXXX() method for each. You can use the getXXX() method in two ways with the same semantics. (You can retrieve the value of the designated column in the current row of this ResultSet object as an XXX type.) getXXX(int columnIndex) is the preferred way of getting data since there is no need to get the column s metadata information. getXXX(String columnName) might be a little bit slow because of getting the column s metadata information.