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

11.4 Access Stored Procedures Using CallableStatement

Due to the device and network constrains, mobile database applications need to minimize the operational overhead and fully leverage the database's native optimization. The database stored procedures and the JDBC CallableStatement interface are very handy tools.

11.4.1 What Is a Stored Procedure?

A stored procedure is a named group of SQL statements and flow control logic that have been previously created and stored in the database. Stored procedures accept input parameters (IN parameters) just like any other types of remote procedure calls. Stored procedures can also return results by updating the values of some parameters that have been passed to it (OUT parameters). Of course, stored procedures containing query statements also return normal row sets. Stored procedures have the following advantages:

More robust: Stored procedures modularize database access functionalities, which promotes code reuse and reduces the chance for human errors.

Bandwidth friendly: Calling the stored procedure by its name certainly uses less bandwidth than passing the entire sequence of SQL statements.

More secure: A database can be configured to prohibit generic SQL operations but allow only certain stored procedure calls from certain users. This allows fine-tuned access control and helps to ensure data integrity.

High performance: Since a group of SQL statements and their controlling logic are stored together, the database can optimize the execution flow globally. However, most mobile databases on devices do not have this level of sophistication.

11.4.2 Use of the CallableStatement Interface

Using JDBC, we can access stored procedures using the CallableStatement interface, which extends the PreparedStatement interface. We have to first define stored procedures in the database through a database-specific step. The following SQL script defines a stored procedure, addPerson, in an Oracle database. The procedure takes in four parameters: pUSERID, pNAME, pENTRYTIME, and pPICTURE. It adds a new row to the PersonRecords table and returns the total number of rows in the OUT parameter pTOTAL.

CREATE PROCEDURE addPerson (
pUSERID IN INTEGER,
pNAME IN VARCHAR(254),
pENTRYTIME IN TIMESTAMP,
pPICTURE IN BLOB,
pTOTAL OUT INTEGER
)
AS
BEGIN
INSERT INTO PersonRecords
(USERID, NAME, ENTRYTIME, PICTURE)
VALUES (pUSERID, pNAME, pENTRYTIME, pPICTURE);
SELECT COUNT(*) FROM PersonRecords;
COMMIT;
END

Then, we use a parameterized procedure call template to instantiate a CallableStatement instance. The ? marks indicate parameters. An IN parameter is set by the setXXXX method inherited from the PreparedStatement interface. An OUT parameter must be registered so that we can retrieve its value after the call. Any parameter can be both IN and OUT. Finally, the procedure call is executed via the CallableStatement.executeUpdate() method.

int userID;
String name;
Timestamp entryTime;
byte [] picture;
// Init the CallableStatement
CallableStatement cstmt =
conn.prepareCall("{call addPerson(?, ?, ?, ?, ?)}");
// Set IN parameters
cstmt.setInt(1, userID);
cstmt.setString(2, name);
cstmt.setTimestamp(3, entryTime);
cstmt.set(4, picture);
// Register the OUT parameter
cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
// Execute the procedure call
cstmt.executeUpdate();
// If the procedure call returns a ResultSet,
// you can process it here
// Get the OUT parameter
int totalcount = cstmt.getInt(5);

Note

Stored procedures are considered to be expensive on the database server side. They are rarely available on mobile databases. But keep in mind that JDBC also allows us to access stored procedures in fully featured backend databases.