Row operation management

Manage row operations by performing the following general tasks:

Construct a SQL statement in a String variable.
Create a PreparedStatement object by passing the String variable to the Connection.prepareStatement method.
Set any host variables, indicated by the ? character, by using the PreparedStatement.set method.

Each host variable can be referenced in accordance to its ordinal position in the statement. For example, the first ? is referenced as 1, and the second as 2. The set method, illustrated in the example below, allows you to reference the ordinal position of the variable and specify a new value.
Call the PreparedStatement.execute method to perform the operation on the database.
Commit the changes to the database by calling the Connection.commit method to make the changes permanent; otherwise, call the Connection.rollback method.

Transactions must be explicitly committed or rolled back by using the methods supported by the Connection interface.
Close the PreparedStatement object to free resources.

Example

The code referenced in this example is part of a complete sample that illustrates how to perform basic schema and data management operations using the UltraLiteJ API. See Example: Managing database operations on an Android smartphone.

Global PreparedStatement objects are defined and instantiated by an InitStatements method call. Data insert, update, and delete operations are illustrated in the custom AddDepartment, UpdateDepartment, and DeleteDepartment methods, respectively. The Commit method illustrates how to make the row operations permanent. The FiniStatements method closes the global PreparedStatement objects and frees resources.

Connection _conn = null;
ResultSet _departments = null;
PreparedStatement _inserter = null;
PreparedStatement _updater = null;
PreparedStatement _deleter = null;
PreparedStatement _preparer = null;

public void InitStatements() {
    String stmt;
    try {
        stmt = "INSERT INTO Department(dept_no, name) VALUES (?,?)";
        _inserter = _conn.prepareStatement(stmt);
        stmt = "UPDATE Department SET dept_no = ? WHERE dept_no = ?";
        _updater = _conn.prepareStatement(stmt);
        stmt = "DELETE FROM Department WHERE dept_no = ?";
        _deleter = _conn.prepareStatement(stmt);
    } catch (ULjException e) {
        HandleError(e);
    }
}

public void FiniStatements() {
    try {
        _departments.close();
        _inserter.close();
        _updater.close();
        _deleter.close();
        _preparer.close();
    } catch (ULjException e) {
        HandleError(e);
    }
}

public void AddDepartment(int deptID, String deptName) {
    try {
        _inserter.set(1, deptID);
        _inserter.set(2, deptName);
        _inserter.execute();
        PrintText("Successfully executed:"
                + " INSERT INTO Department(dept_no, name)" 
                + " VALUES (" + deptID + "," + deptName + ")");
    } catch (ULjException e) {
        HandleError(e);
    }
}

public void UpdateDepartment(int deptIDold, int deptIDnew) {
    try {
        _updater.set(1, deptIDnew);
        _updater.set(2, deptIDold);
        _updater.execute();
        PrintText("Successfully executed:"
                + " UPDATE Department SET dept_no = " + deptIDnew 
                + " WHERE dept_no = " + deptIDold);
    } catch (ULjException e) {
        HandleError(e);
    }
}

public void DeleteDepartment(int deptID) {
    try {
        _deleter.set(1, deptID);
        _deleter.execute();
        PrintText("Successfully executed:"
                + " DELETE FROM Department WHERE dept_no = " + deptID);
    } catch (ULjException e) {
        HandleError(e);
    }
}

public void Commit() {
    try {
        _conn.commit();
    } catch (ULjException e1) {
        HandleError(e1);
    }
}

Note

String concatenation is recommended over host variable usage when constructing SQL statements that only need to be executed once.

For example, the following code, which uses String concatenation to construct SQL statements, can be used to replace the DeleteDepartment method:

public void DeleteDepartment(int deptID) {
    String stmt = "DELETE FROM Department WHERE dept_no = " + deptID;
    PreparedStatement deleter;
    try {
        deleter = _conn.prepareStatement(stmt);
        deleter.execute();
        deleter.close();
        PrintText("Successfully executed: " + stmt);
    } catch (ULjException e) {
        HandleError(e);
    }
}