Database (BlackBerry JDE 7.1.0 API Reference)

Overview

Package

Class

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

net.rim.device.api.database
Interface Database

public interface Database

Lets you create, modify, and access SQLite databases.

BlackBerry devices that run BlackBerry Device Software 5.0 or later include the open source SQLite library. The Database API gives you access to this library. Although SQLite databases are optimized for use on a smartphone, the API is similar to other database APIs. The Database API is an interface for the SQLite library so that you can create, delete, and execute SQLite SQL statements, as well as perform other common database management tasks.

For more information about the Database API, see the Data Storage Development Guide for BlackBerry Java SDK, available at www.blackberry.com/go/devguides. For more information about SQLite, see www.sqlite.org.

Use the following approach to work with an existing SQLite database.

Create a Statement by calling Database.createStatement(java.lang.String).
Prepare the Statement to run by calling Statement.prepare(). Performing this step is like compiling the Statement.
If the Statement might return results, run the Statement by calling Statement.getCursor().
If the Statement won't return results, run the Statement by calling one of the following methods:
- Statement.execute() - Use when you want to explicitly prepare and close the Statement.
- Database.executeStatement(java.lang.String) - Use when running the Statement only once, when you want the Statement to be automatically prepared and closed.
- Statement.executeInsert(java.lang.Object[]) - Use with the SQL statement INSERT when running the Statement multiple times using SQL bind parameters.
- Statement.executeUpdate(java.lang.Object[]) - Use with the SQL statement UPDATE when running the Statement multiple times using SQL bind parameters.
If there are results, retrieve them by iterating over the returned Cursor row by row.

By default, a transaction is created for each Statement. If the Statement runs successfully, it is automatically committed. If it fails, it is rolled back. If you do not want each Statement to run in its own transaction (for example, if you want to run multiple Statements in one transaction, which will have performance benefits), then you should use Database.beginTransaction() and Database.commitTransaction() to change the default behavior and manually manage transactions. Nested transactions are not supported.

SQLite databases created by your application are not removed automatically when your application is removed.

Code sample: Creating a SQLite database

This example creates a SQLite database at the root of a media card.

 import net.rim.device.api.system.Application;
 import net.rim.device.api.database.*;
 import net.rim.device.api.io.*;
 
 public class CreateDatabase extends Application
 {
     public static void main(String[] args)
     {
          CreateDatabase app = new CreateDatabase();
          Database db = null;
     try {
         URI strURI = URI.create("file:///SDCard/test.db");             
         db = DatabaseFactory.create(strURI);        
     } catch (Exception e) {
         System.out.println( e.getMessage() );
     } finally {
         try {
             db.close();
         } catch (DatabaseException e) {
         }
     }
 }

Code sample: Adding a table to a SQLite database

 import net.rim.device.api.database.Database;
 import net.rim.device.api.database.DatabaseFactory;
 import net.rim.device.api.database.Statement;
 import net.rim.device.api.io.URI;
 import net.rim.device.api.system.Application;
 
 public class AddDatabaseTable extends Application 
 {
   public static void main(String[] args)
   {
       AddDatabaseTable app = new AddDatabaseTable();
       try {
          Database d = null;
          Statement st = null;            
          try {
              URI myURI = URI.create("/SDCard/test.db"); 
              d = DatabaseFactory.open(myURI);
              st = d.createStatement( "CREATE TABLE People ( " +
                                              "Name TEXT, " +
                                              "Age INTEGER )" );                                                            
              st.prepare();
              st.execute();                
          }
          catch ( Exception e ) 
          {         
              System.out.println( e.getMessage() );
          }
          finally
          {            
              st.close();                
              d.close();                
          }
      } catch (Exception e) {            
          System.out.println( e.getMessage() );
      }              
  }
 }

Code sample: Adding content to a SQLite table

 import net.rim.device.api.database.Database;
 import net.rim.device.api.database.DatabaseFactory;
 import net.rim.device.api.database.Statement;
 import net.rim.device.api.io.URI;
 import net.rim.device.api.system.Application;
 
 public class InsertIntoTable extends Application 
 {
    public static void main(String[] args)
   {
       InsertIntoTable app = new InsertIntoTable();
      try
      {           
          Database d = null;
          Statement st = null;
          try
          {
              URI myURI = URI.create("/SDCard/test.db"); 
              d = DatabaseFactory.open(myURI);
              st = d.createStatement("INSERT INTO People(Name,Age) " +
                                              "VALUES ('John',37)");
              st.prepare();
              st.execute();
          }                          
          catch ( Exception e ) 
          {         
              System.out.println( e.getMessage() );
          }
          finally
          {            
              st.close();                
              d.close();                
          }                        
      }            
      catch ( Exception e ) 
      {         
          System.out.println( e.getMessage() );
      }
   }
 }

Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

Method Summary
	`void`	`attach(URI fileURI, String dbName, DatabaseSecurityOptions securityOptions)` Attaches a database file to this opened connection.
	`void`	`beginTransaction()` Starts a new transaction.
	`void`	`close()` Closes the database and releases resources (such as statements and blobs) that are associated with it.
	`void`	`commitTransaction()` Commits the current transaction.
	`InputStream`	`createBlobInputStream(String database, String table, String column, long row)` Creates an InputStream object that can be used to read data from a blob.
	`InputStream`	`createBlobInputStream(String table, String column, long row)` Creates an InputStream object that can be used to read data from a blob.
	`OutputStream`	`createBlobOutputStream(String database, String table, String column, long row)` Creates an OutputStream object that can be used to write data into a blob.
	`OutputStream`	`createBlobOutputStream(String table, String column, long row)` Creates an OutputStream object that can be used to write data into a blob.
	`Statement`	`createStatement(String sqlStatement)` Creates a Statement in this database.
	`void`	`detach(String dbName)` Detaches a database that is attached to this connection.
	`void`	`executeStatement(String sql)` Prepares, executes, and closes a SQL query.
	`int`	`getCacheUsed()` Returns the amount of memory used for caching this database's pages.
	`DatabaseSecurityOptions`	`getDatabaseSecurityOptions()` Returns a new object with populated database security options.
	`URI`	`getFile()` Returns the URI of the database file.
	`String`	`getLastError()` Returns the last error message from the database.
	`int`	`getNumberOfChanges()` Returns the number of rows affected by the last database modification.
	`int`	`getSchemaUsed()` Returns the amount of memory used for storing this database's schema.
	`int`	`getStatementUsed()` Returns the amount of memory used by the database's prepared statements.
	`boolean`	`isEncrypted()` Returns the encrypted database property.
	`long`	`lastInsertedRowID()` Returns the last automatically-generated RowID (the primary key).
	`void`	`rollbackTransaction()` Rolls back the current transaction.

Method Detail

close

void close()
           throws DatabaseIOException

Closes the database and releases resources (such as statements and blobs) that are associated with it.

Note: You should always explicitly close statements and blobs so they don't exhaust memory before the database is closed.

Throws:: DatabaseIOException - If the database file can't be closed and data can't be flushed from the file. For example, a DatabaseIOException is thrown when close() is called and the microSD card has been removed.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

createStatement

Statement createStatement(String sqlStatement)
                          throws DatabaseException

Creates a Statement in this database.

If the given SQL parameter contains multiple statements separated by semicolons (;) then only the first one is parsed and executed. Any statements after the first semicolon are ignored.

Statements should be closed explicitly to free up resources.

Parameters:: sqlStatement - SQL statement to execute.
Returns:: Statement.
Throws:: DatabaseException - If the database is closed.; NullPointerException - If the SQL statement is null.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

getFile

URI getFile()
            throws DatabasePathException

Returns the URI of the database file. In SQLite, each database is one file.

Returns:: The URI of the database file.
Throws:: DatabasePathException - If the specified path is incorrect. The path can be incorrect when the microSD card is removed.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

getNumberOfChanges

int getNumberOfChanges()
                       throws DatabaseException

Returns the number of rows affected by the last database modification.

Returns:: The number of rows affected.
Throws:: DatabaseException - If the database is closed.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

lastInsertedRowID

long lastInsertedRowID()
                       throws DatabaseException

Returns the last automatically-generated RowID (the primary key).

Each entry in a SQLite table has a unique 64-bit signed integer key called the RowID. The RowID is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the RowID.

Care should be taken when there are concurrent inserts to the same database. To ensure that the correct RowID is returned, the INSERT and the call to lastInsertedRowID() should be done within a synchronized block. The synchronized block should be used in a way that blocks any other INSERT statement from executing (and modifying the last inserted RowID).

Returns:: The ID (primary key) of the last row that was inserted.
Throws:: DatabaseException - If the database is closed.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

getLastError

String getLastError()
                    throws DatabaseException

Returns the last error message from the database.

Returns:: Error message string.
Throws:: DatabaseException - If the database is closed.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

isEncrypted

boolean isEncrypted()
                    throws DatabaseException

Returns the encrypted database property.

Returns:: true if the database is encrypted; otherwise false.
Throws:: DatabaseException - If the database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

getDatabaseSecurityOptions

DatabaseSecurityOptions getDatabaseSecurityOptions()
                                                   throws DatabaseException

Returns a new object with populated database security options.

Returns:: DatabaseSecurityOptions New object with populated database security options.
Throws:: DatabaseException - If the database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

beginTransaction

void beginTransaction()
                      throws DatabaseException

Starts a new transaction. All the following statements are executed in the transaction until it is committed or rolled back. If the transaction is not committed then it is rolled back. Nested transactions are not supported. If this method is called twice, a DatabaseException is thrown.

Throws:: DatabaseException - If the database is not open or if this method is called twice.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

commitTransaction

void commitTransaction()
                       throws DatabaseException

Commits the current transaction.

If the application does not close all cursors that were started within the transaction, the commit fails.

You should explicitly close all statements.

Throws:: DatabaseException - If the database is not open or the transaction was not started.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

rollbackTransaction

void rollbackTransaction()
                         throws DatabaseException

Rolls back the current transaction. All updates are lost.

Throws:: DatabaseException - If the database is not open or the transaction was not started.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 5.0.0

executeStatement

void executeStatement(String sql)
                      throws DatabaseException

Prepares, executes, and closes a SQL query.

This method performs a statement prepare, execute, and close in a single call. It provides an alternative to creating a prepared statement with the Statement class.

This method can be used for queries that do not return a result set, such as INSERT and UPDATE queries. For queries that return a result set, use Statement.getCursor().

If the given SQL parameter contains multiple statements separated by semicolons (;) then only the first one is parsed and executed. The remaining statements after the first semicolon are ignored.

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) {
     }
 }

Parameters:: sql - SQL query to execute.
Throws:: DatabaseException - If the database is closed or there is an SQL error with the query.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

attach

void attach(URI fileURI,
            String dbName,
            DatabaseSecurityOptions securityOptions)
            throws DatabaseException

Attaches a database file to this opened connection.

Attach another database to this connection. In queries, the attached database can be referenced by using the alias dbName. The original database can still be referenced in queries by using "main".

Passing in a DatabaseSecurityOptions parameter will specify if the database file to attach is encrypted. If null is given, the file is assumed to be unencrypted.

Parameters:: fileURI - Database file to attach.; dbName - This will be the name for this attached database for use in queries.; securityOptions - Specify the security options of the database we are attaching.
Throws:: DatabaseException - If the main database is not opened, or if dbName cannot be opened.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

detach

void detach(String dbName)
            throws DatabaseException

Detaches a database that is attached to this connection.

The dbName should match the dbName provided in the original call to attach.

Parameters:: dbName - Database to detach from this connection.
Throws:: DatabaseException - If dbName is not currently attached.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

createBlobOutputStream

OutputStream createBlobOutputStream(String table,
                                    String column,
                                    long row)
                                    throws DatabaseException

Creates an OutputStream object that can be used to write data into a blob. The "main" database is used.

You can reserve space for blob output using the Statement.bindZeroBlob method, which binds a series of null bytes to a parameter in a Statement.

Parameters:: table - Name of the table where the desired blob is located.; column - Name of the column where the desired blob is located.; row - The row ID where the desired blob is located.
Returns:: OutputStream A stream to write BLOB data to.
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

createBlobOutputStream

OutputStream createBlobOutputStream(String database,
                                    String table,
                                    String column,
                                    long row)
                                    throws DatabaseException

Creates an OutputStream object that can be used to write data into a blob.

You can reserve space for blob output using the Statement.bindZeroBlob method, which binds a series of null bytes to a parameter in a Statement.

Parameters:: database - Name of the database where the desired blob is located.; table - Name of the table where the desired blob is located.; column - Name of the column where the desired blob is located.; row - The row ID where the desired blob is located.
Returns:: OutputStream A stream to write BLOB data to.
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

createBlobInputStream

InputStream createBlobInputStream(String table,
                                  String column,
                                  long row)
                                  throws DatabaseException

Creates an InputStream object that can be used to read data from a blob. The "main" database is used.

Parameters:: table - Name of the table where the desired blob is located.; column - Name of the column where the desired blob is located.; row - The row ID where the desired blob is located.
Returns:: InputStream A stream to read blob data from.
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

createBlobInputStream

InputStream createBlobInputStream(String database,
                                  String table,
                                  String column,
                                  long row)
                                  throws DatabaseException

Creates an InputStream object that can be used to read data from a blob.

Parameters:: database - Name of the database where the desired blob is located.; table - Name of the table where the desired blob is located.; column - Name of the column where the desired blob is located.; row - The row ID where the desired blob is located.
Returns:: InputStream A stream to read BLOB data from.
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

getCacheUsed

int getCacheUsed()
                 throws DatabaseException

Returns the amount of memory used for caching this database's pages.

Returns:: Memory used by the cache in bytes
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

getSchemaUsed

int getSchemaUsed()
                  throws DatabaseException

Returns the amount of memory used for storing this database's schema.

Returns:: Memory used for the schema in bytes
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

getStatementUsed

int getStatementUsed()
                     throws DatabaseException

Returns the amount of memory used by the database's prepared statements.

Returns:: Memory used for prepared statements in bytes
Throws:: DatabaseException - If the Database is not open.
Category:: Signed: This element is only accessible by signed applications. If you intend to use this element, please visit http://www.blackberry.com/go/codesigning to obtain a set of code signing keys. Code signing is only required for applications running on BlackBerry smartphones; development on BlackBerry Smartphone Simulators can occur without code signing.
Since:: BlackBerry API 7.0.0

Overview

Package

Class

Tree

Deprecated

Index

Help

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

Copyright 1999-2011 Research In Motion Limited. 295 Phillip Street, Waterloo, Ontario, Canada, N2L 3W8. All Rights Reserved.
Java is a trademark of Oracle America Inc. in the US and other countries.
Legal

net.rim.device.api.database Interface Database

Code sample: Creating a SQLite database

Code sample: Adding a table to a SQLite database

Code sample: Adding content to a SQLite table

close

createStatement

getFile

getNumberOfChanges

lastInsertedRowID

getLastError

isEncrypted

getDatabaseSecurityOptions

beginTransaction

commitTransaction

rollbackTransaction

executeStatement

attach

detach

createBlobOutputStream

createBlobOutputStream

createBlobInputStream

createBlobInputStream

getCacheUsed

getSchemaUsed

getStatementUsed

net.rim.device.api.database
Interface Database