Enterprise J2ME Developing Mobile Java Applications [Electronic resources] نسخه متنی

11.2 Introducing JDBC

The JDBC specification defines a set of standard interfaces. Each database vendor is responsible for supplying implementation classes that know how to talk with their proprietary databases. Those vendor-specific classes are called JDBC drivers and they are transparent to developers. Using JDBC to access a database involves several steps:

Obtain a Connection object to the specific database.

Build a Statement object from the Connection object.

Execute a SQL statement through the Statement object.

A ResultSet is returned from the execution.

Navigate and retrieve data from the ResultSet object.

Close the Statement and the Connection objects.

In Table 11.1, we list JDBC interfaces commonly supported by mobile databases.

11.2.1 A JDBC Example

Now, let's look at the use of the JDBC API through an example. For the sake of simplicity, let's assume that the database table we are dealing with has the following schema (Listing 11.1).

Listing 11.1. Example database table schema

CREATE TABLE PersonRecords(
USERID INTEGER PRIMARY KEY,
NAME VARCHAR(254),
ENTRYTIME TIMESTAMP,
PICTURE BLOB
};

11.2.2 Obtain a Connection Object

The code for obtaining a Connection object is slightly database-dependent because we have to load the vendor-specific JDBC driver. Using JDBC v3.0 (or the CDC JDBC Optional Package; see Section 11.5), we can use the following code.

VendorDataSource ds = new VendorDataSource();
ds.setServerName("dbserver");
ds.setPortNumber(9980);
Connection conn = ds.getConnection("username", "passwd");

The port number, username, and password are specific to the database setup. Most on-device embedded databases are for single user only. If you use PersonalJava, the driver initialization code is slightly different because it conforms to the JDBC v1.2 specification. The database URL string (DBURI) in the code below is database-specific.

Class.forName("vendor.specific.DriverManager");
Connection conn=DriverManager.getConnection("DBURI",
"username","password");

11.2.3 Execute a SQL Statement

Next, we assemble a SQL string and execute it through a Statement object.

String SQLStr = "SELECT * FROM PersonRecords";
Statement stat= conn.createStatement();
ResultSet rs = stat.executeQuery(SQLStr);

If the SQL command is an Update instead of a Select, we should call the Statement.update() method. Method update() returns an integer number that indicates the number of rows that have been updated.

11.2.4 Extract Search Results

We can extract data columns from the ResultSet object returned from the Select query. We can use either the column index or the column name to retrieve data.

// Loop through all returned rows
while (rs.next()) {
int userID = rs.getInt(1);
// int userID = rs.getInt("USERID");
String name = rs.getString(2);
// String name = rs.getInt("NAME");
Timestamp entryTime = rs.getTimestamp(3);
// Timestamp entryTime =
// rs.getTimestamp("ENTRYTIME");
InputStream is = rs.getBinaryStream(4);
// InputStream is = rs.getBinaryStream("PICTURE");
}