Native UltraLite for Java User's Guide
  Tutorial: An Introductory Application

Lesson 3: Select the rows from the table

This lesson retrieves rows from the table, and prints them on the command line. It shows how to loop over the rows of a table.

To list the rows in the table

1. Add the following method to the Customer.java file:

   private void select() throws SQLException
   {
     // Fetch rows
     // Open the Customer table
     Table t = conn.getTable( "customer" );
     t.open();
   
     short id = t.schema.getColumnID( "id" );
     short fname = t.schema.getColumnID( "fname" );
     short lname = t.schema.getColumnID( "lname" );
   
   
     t.moveBeforeFirst();
     while( t.moveNext() ) {
         System.out.println(
           "id= " + t.getInt( id )
           + ", name= " + t.getString( fname )
           + " " + t.getString( lname )
           );
     }
     t.close();
   }

   This code carries out the following tasks:

   • Opens the Table object, as in the previous lesson.

   • Retrieves the column identifiers, as in the previous lesson.

   • Sets the current position before the first row of the table.

     Any operations on a table are carried out at the current position. The position may be before the first row, on one of the rows of the table, or after the last row. By default, as in this case, the rows are ordered by their primary key value (id). To order rows in a different way (alphabetically by name, for example), you can add an index to an UltraLite database and open a table using that index.

   • For each row, the id and name are written out. The loop carries on until moveNext returns false, which happens after the final row.

   • Closes the Table object.

2. Add the following line to the main() method, immediately after the call to the insert method:

   cust.select();

3. Compile and run your application, as in Lesson 1: Connect to the database.

The application prints the following message:

Connected to an existing database.
The table has 2 rows.
id= 1, name= Gene Poole
id= 2, name= Penny Stamp