Recordset Class

The javaxt.sql.Recordset class is used to query, insert, and update records in a database. It is intended to simplify many of the complexities and nuances associated with the standard Java/JDBC Resultset and is loosely based on the old Microsoft ADODB Resultset class. It is used in conjuction with a live javaxt.sql.Connection.

Simple Query

Here's a simple example of how to execute a query and print the results.

 //Connect to the database
   javaxt.sql.Connection conn = new javaxt.sql.Connection();    
   conn.open(db);

 //Execute query and iterate through the results
   javaxt.sql.Recordset rs = new javaxt.sql.Recordset();
   rs.open("SELECT * FROM EMPLOYEE", conn);
   while (rs.hasNext()){
      for (javaxt.sql.Field field : rs.getFields()){
          System.out.println(field.getName() + ":  " + field.getValue());
      }
      System.out.println();
      rs.moveNext();
   }
   rs.close();

 //Close the database connection
   conn.close();

You can retrieve values for specific fields using the getValue() method.

 //Connect to the database
   javaxt.sql.Connection conn = new javaxt.sql.Connection();    
   conn.open(db);

 //Execute query and iterate through the results
   javaxt.sql.Recordset rs = new javaxt.sql.Recordset();
   rs.open("SELECT * FROM EMPLOYEE", conn); 
   while (rs.hasNext()){
       int id = rs.getValue("ID").toInteger();
       String firstName = rs.getValue("FIRST_NAME").toString();
       String lastName = rs.getValue("LAST_NAME").toString();
       javaxt.utils.Date date = rs.getValue("DATE").toDate();
       System.out.println(id + ":\t " + firstName + " " + lastName);
       rs.moveNext();
   }
   rs.close();

 //Close the database connection
   conn.close();

Insert Record

Here's a simple example of how to insert a record into a table. Note how the setValue() method can accept mix object types.

 //Connect to the database
   javaxt.sql.Connection conn = new javaxt.sql.Connection();    
   conn.open(db);

 //Execute query and iterate through the results
   javaxt.sql.Recordset rs = new javaxt.sql.Recordset();
   rs.open("SELECT * FROM EMPLOYEE", conn, false); //Set "ReadOnly" flag to false
   rs.addNew();
   rs.setValue("LAST_NAME", "Blow");
   rs.setValue("FIRST_NAME", "Joe");
   rs.setValue("AGE", 25);
   rs.setValue("Date", new java.util.Date());
   rs.update();
   rs.close();

 //Close the database connection
   conn.close();

Autogenerated Key

Some tables are designed with an autogenerated key (e.g. identity or sequence). You can retrieve the autogenerated key after inserting a record like this:

int id = rs.getGeneratedKey().toInteger();

Update Record

Here's a simple example of how to update an existing record.

 //Connect to the database
   javaxt.sql.Connection conn = new javaxt.sql.Connection();    
   conn.open(db);

 //Execute query and iterate through the results
   javaxt.sql.Recordset rs = new javaxt.sql.Recordset();
   rs.open("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE ID = " + id, conn, false);
   rs.setValue("FIRST_NAME", "Bob");
   rs.setValue("LAST_NAME", "Smith");
   rs.update();   
   rs.close();

 //Close the database connection
   conn.close();