UltraLite User's Guide
  Tutorial: Build an Application Using Java

Lesson 3: Write the application code

The following code listing holds a very simple UltraLite application.

You can copy the code into a new file and save it as Sample.java in your c:\JavaTutorial directory, or open a new file and type the content. You can find this source code in Samples\UltraLite\JavaTutorial\Sample.java.

// (1) Import required packages
import java.sql.*;
import ISampleSQL.*;
import ianywhere.ultralite.jdbc.*;
import ianywhere.ultralite.support.*;
// (2) Class implements the interface containing SQL statements
public class Sample implements ISampleSQL
{
  public static void main( String[] args )
  {
    try{
        // (3) Connect to the database
        java.util.Properties p = new
            java.util.Properties();
        p.put( "persist", "file" );
        SampleDB db = new SampleDB( p );
        Connection conn = db.connect();
        // (4) Initialize the database with data
        PreparedStatement pstmt1 =
            conn.prepareStatement( INSERT_PRODUCT );
        pstmt1.setInt(1, 1);
        pstmt1.setInt(2, 400);
        pstmt1.setString(3, "4x8 Drywall x100");
        int rows1=pstmt1.executeUpdate();
        pstmt1.setInt(1, 2);
        pstmt1.setInt(2, 3000);
        pstmt1.setString(3, "8' 2x4 Studs x1000");
        int rows2=pstmt1.executeUpdate();
        // (5) Query the data and write out the results
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery(
             SELECT_PRODUCT );
        while( result.next() ) {
            int id = result.getInt( 1 );
            String name = result.getString( 2 );
            int price = result.getInt( 3 );
            System.out.println ( name +
                " \tId=" + id +
                " \tPrice=" + price );
        }
        // (6) Close the connection to end
        conn.close();
    } catch (SQLException e) {
        Support.printException( e );
    }
  }
}

Explanation of the sample program 

Although too simple to be useful, this example contains elements that must be present in all Java programs used for database access. The following describes the key elements in the sample program. Use these steps as a guide when creating your own Java UltraLite application.

The numbered steps correspond to the numbered comments in the source code.

1. Import required packages.

   The sample program utilizes JDBC interfaces and classes and therefore must import this package. It also requires the UltraLite runtime classes, and the generated interface that contains the SQL statement strings.

2. Define the class.

   The SQL statements used in the application are stored in a separate file, as an interface. The class must declare that it implements the interface to be able to use the SQL statements for the project. The class names are based on the statement names you provided when adding the statements to the database.

3. Connect to the database.

   The connection is established using an instance of the database class. The database name must match the name of the generated Java class (in this case SampleDB). The file value of the persist Properties object states that the database should be persistent.

4. Insert sample data.

   In a production application, you would generally not insert sample data. Instead, you would obtain an initial copy of data by synchronization. In the early stages of development, it can simplify your work to directly insert data.

   • Create a PreparedStatement object using the prepareStatement() method.

   • To execute SQL commands, you must create a Statement or PreparedStatement object. Use a Statement object to execute simple SQL commands without any parameters and a PreparedStatement object to execute SQL commands with parameters. The sample program first creates a PreparedStatement object to execute an insert command:

     PreparedStatement pstmt1 =
     conn.prepareStatement( INSERT_PRODUCT );

     The prepareStatement method takes a SQL string as an argument; this SQL string is included from the generated interface.

5. Execute a select SQL command using a Statement object

   • Create a Statement object using the createStatement() method.

     Unlike the PreparedStatement object, you do not need to supply a SQL statement when you create a Statement object. Therefore, a single Statement object can be used to execute more than one SQL statement.

     Statement stmt = conn.createStatement();

   • Execute your SQL query.

     Use the executeQuery() method to execute a select query. A select statement returns a ResultSet object.

   • Implement a loop to sequentially obtain query results.

     The ResultSet object maintains a cursor that initially points just before the first row. The cursor is incremented by one row each time the next() method is called. The next() method returns a true value when the cursor moves to a row with data and returns a false value when it has moved beyond the last row.

     while(result.next()) {
     ...
     }

   • Retrieve query results using the getxxx() methods.

     Supply the column number as an argument to these methods. The sample program uses the getInt() method to retrieve the product ID and price from the first and second columns respectively, and the getString() method to retrieve the product name from the third.

     int id = result.getInt( 1 );
     int price = result.getInt( 2 );
     String name = result.getString( 3 );      

6. End your Java UltraLite program

   • Close the connection to the database, using the Connection.close() method:

     conn.close();