Working with SQLite databases
After you create an SQLite® database, you can use SQL statements to do things such as add data, retrieve data, and modify the database.
The list of supported SQL statements and their syntax is available at www.SQLite.org. The Database API does not support FTS2 and RTREE.
There are two ways to execute statements: create a Statement object, or use Database.executeStatement.
Executing statements with a Statement object
The following steps outline how to execute statements with a Statement object:
- Create a Statement object using the Database.createStatement method.
- Prepare the statement to run by invoking the Statement.prepare method.
- Bind parameters in the statement. This is optional, but improves performance when you are running the statement multiple times.
- Execute the statement.
If the statement might return results (such as a SELECT statement), execute it by calling Statement.getCursor, which returns a Cursor object.
- If the statement does not return a result set, execute it by calling one of the following methods:
- Statement.executeUpdate: Use when updating data (with an UPDATE statement) and running the Statement multiple times with bind parameters.
- Statement.executeInsert: Use when inserting data (with an INSERT statement) and running the Statement multiple times with bind parameters.
- Statement.execute: Use when you want to use bind parameters.
- If the statement returns a result set, retrieve the result set by iterating over the returned cursor row by row. Do this using the Cursor interface, which works in all circumstances but is forward-only. For bidirectional cursor movement, but only for small result sets, use the BufferedCursor class.
The following example updates rows:
Statement st = d.createStatement("UPDATE Account set Balance = ? WHERE AcctNo > ?");
try
{
st.prepare();
Object[] bindParams = {new Integer (2000), new Integer (100)};
st.executeUpdate(bindParams);
}
finally
{
st.close();
}
Executing statements without a Statement object
When you are executing a query that does not return a result set and you are not using bind parameters, you do not need to create a Statement object. Instead, you can use Database.executeStatement, which performs a statement prepare, execute, and close in a single call.
The following example creates a table:
Database d = null;
try {
d = DatabaseFactory.create("hello.db");
d.executeStatement( "CREATE TABLE t (a INTEGER PRIMARY KEY, b BLOB);" );
} catch (Exception e) {
System.out.println( e.getMessage() );
} finally {
try {
d.close();
} catch (DatabaseException e) {
}
}