Android

android.database.sqlite.SQLiteDatabase

java.lang.Object
android.database.sqlite.SQLiteClosable
android.database.sqlite.SQLiteDatabase

Exposes methods to manage a SQLite database.

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

See the Notepad sample application in the SDK for an example of creating and managing a database.

Database names must be unique within an application, not across all applications.

Localized Collation - ORDER BY

In addition to SQLite's default BINARY collator, Android supplies two more, LOCALIZED, which changes with the system's current locale if you wire it up correctly (XXX a link needed!), and UNICODE, which is the Unicode Collation Algorithm and not tailored to the current locale.

Nested Classes
SQLiteDatabase.CursorFactory Used to allow returning sub-classes of Cursor when calling query. 

Summary

Constants

      Value  
int  CREATE_IF_NECESSARY  Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to create the database file if it does not already exist.  268435456  0x10000000 
int  NO_LOCALIZED_COLLATORS  Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database without support for localized collators.   16  0x00000010 
int  OPEN_READONLY  Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database for reading only.  0x00000001 
int  OPEN_READWRITE  Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database for reading and writing. If the disk is full, this may fail even before you actually write anything.   0x00000000 
int  SQLITE_MAX_LIKE_PATTERN_LENGTH  Maximum Length Of A LIKE Or GLOB Pattern The pattern matching algorithm used in the default LIKE and GLOB implementation of SQLite can exhibit O(N^2) performance (where N is the number of characters in the pattern) for certain pathological cases.  50000  0x0000c350 

Public Methods

          void  beginTransaction()
Begins a transaction.
          void  close()
Close the database.
          SQLiteStatement  compileStatement(String sql)
Compiles an SQL statement into a reusable pre-compiled statement object.
      static    SQLiteDatabase  create(SQLiteDatabase.CursorFactory factory)
Create a memory backed SQLite database.
          int  delete(String table, String whereClause, String[] whereArgs)
Convenience method for deleting rows in the database.
          void  endTransaction()
End a transaction.
          void  execSQL(String sql, Object[] bindArgs)
Execute a single SQL statement that is not a query.
          void  execSQL(String sql)
Execute a single SQL statement that is not a query.
      static    String  findEditTable(String tables)
Finds the name of the first table, which is editable.
          long  getMaximumSize()
Returns the maximum size the database may grow to.
          long  getPageSize()
Returns the maximum size the database may grow to.
    final      String  getPath()
Getter for the path to the database file.
          Map<StringString getSyncedTables()
          int  getVersion()
Gets the database version.
          boolean  inTransaction()
return true if there is a transaction pending
          long  insert(String table, String nullColumnHack, ContentValues values)
Convenience method for inserting a row into the database.
          long  insertOrThrow(String table, String nullColumnHack, ContentValues values)
Convenience method for inserting a row into the database.
          boolean  isDbLockedByCurrentThread()
Checks if the database lock is held by this thread.
          boolean  isDbLockedByOtherThreads()
Checks if the database is locked by another thread.
          boolean  isOpen()
          boolean  isReadOnly()
return whether the DB is opened as read only.
          void  markTableSyncable(String table, String deletedTable)
Mark this table as syncable.
          void  markTableSyncable(String table, String foreignKey, String updateTable)
Mark this table as syncable, with the _sync_dirty residing in another table.
          boolean  needUpgrade(int newVersion)
      static    SQLiteDatabase  openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags)
Open the database according to the flags OPEN_READWRITE OPEN_READONLY CREATE_IF_NECESSARY and/or NO_LOCALIZED_COLLATORS.
      static    SQLiteDatabase  openOrCreateDatabase(String path, SQLiteDatabase.CursorFactory factory)
Equivalent to openDatabase(path, factory, CREATE_IF_NECESSARY).
      static    SQLiteDatabase  openOrCreateDatabase(File file, SQLiteDatabase.CursorFactory factory)
Equivalent to openDatabase(file.getPath(), factory, CREATE_IF_NECESSARY).
          Cursor  query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Query the given table, returning a Cursor over the result set.
          Cursor  query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Query the given URL, returning a Cursor over the result set.
          Cursor  query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
Query the given table, returning a Cursor over the result set.
          Cursor  queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Query the given URL, returning a Cursor over the result set.
          Cursor  rawQuery(String sql, String[] selectionArgs)
Runs the provided SQL and returns a Cursor over the result set.
          Cursor  rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable)
Runs the provided SQL and returns a cursor over the result set.
      static    int  releaseMemory()
Attempts to release memory that SQLite holds but does not require to operate properly.
          long  replace(String table, String nullColumnHack, ContentValues initialValues)
Convenience method for replacing a row in the database.
          long  replaceOrThrow(String table, String nullColumnHack, ContentValues initialValues)
Convenience method for replacing a row in the database.
          void  setLocale(Locale locale)
Sets the locale for this database.
          void  setLockingEnabled(boolean lockingEnabled)
Control whether or not the SQLiteDatabase is made threadsafe by using locks around critical sections.
          long  setMaximumSize(long numBytes)
Sets the maximum size the database will grow to.
          void  setPageSize(long numBytes)
Sets the database page size.
          void  setTransactionSuccessful()
Marks the current transaction as successful.
          void  setVersion(int version)
Sets the database version.
          int  update(String table, ContentValues values, String whereClause, String[] whereArgs)
Convenience method for updating rows in the database.
          boolean  yieldIfContended()
Temporarily end the transaction to let other threads run.

Protected Methods

          void  finalize()
Called by the virtual machine when there are no longer any (non-weak) references to the receiver.
          void  onAllReferencesReleased()
Methods inherited from class android.database.sqlite.SQLiteClosable
Methods inherited from class java.lang.Object

Details

Constants

public static final int CREATE_IF_NECESSARY

Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to create the database file if it does not already exist.
Constant Value: 268435456 (0x10000000)

public static final int NO_LOCALIZED_COLLATORS

Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database without support for localized collators.

This causes the collator LOCALIZED not to be created. You must be consistent when using this flag to use the setting the database was created with. If this is set, setLocale(Locale) will do nothing.

Constant Value: 16 (0x00000010)

public static final int OPEN_READONLY

Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database for reading only. This is the only reliable way to open a database if the disk may be full.
Constant Value: 1 (0x00000001)

public static final int OPEN_READWRITE

Flag for openDatabase(String, SQLiteDatabase.CursorFactory, int) to open the database for reading and writing. If the disk is full, this may fail even before you actually write anything.

Note that the value of this flag is 0, so it is the default.

Constant Value: 0 (0x00000000)

public static final int SQLITE_MAX_LIKE_PATTERN_LENGTH

Maximum Length Of A LIKE Or GLOB Pattern The pattern matching algorithm used in the default LIKE and GLOB implementation of SQLite can exhibit O(N^2) performance (where N is the number of characters in the pattern) for certain pathological cases. To avoid denial-of-service attacks the length of the LIKE or GLOB pattern is limited to SQLITE_MAX_LIKE_PATTERN_LENGTH bytes. The default value of this limit is 50000. A modern workstation can evaluate even a pathological LIKE or GLOB pattern of 50000 bytes relatively quickly. The denial of service problem only comes into play when the pattern length gets into millions of bytes. Nevertheless, since most useful LIKE or GLOB patterns are at most a few dozen bytes in length, paranoid application developers may want to reduce this parameter to something in the range of a few hundred if they know that external users are able to generate arbitrary patterns.
Constant Value: 50000 (0x0000c350)

Public Methods

public void beginTransaction()

Begins a transaction. Transactions can be nested. When the outer transaction is ended all of the work done in that transaction and all of the nested transactions will be commited or rolled back. The changes will be rolled back if any transaction is ended without being marked as clean (by calling setTransactionSuccessful). Otherwise they will be committed.

Here is the standard idiom for transactions:

   db.beginTransaction();
   try {
     ...
     db.setTransactionSuccessful();
   } finally {
     db.endTransaction();
   }
 

public void close()

Close the database.

public SQLiteStatement compileStatement(String sql)

Compiles an SQL statement into a reusable pre-compiled statement object. The parameters are identical to execSQL(String). You may put ?s in the statement and fill in those values with bindString(int, String) and bindLong(int, long) each time you want to run the statement. Statements may not return result sets larger than 1x1.

Parameters

sql The raw SQL statement, may contain ? for unknown values to be bound later.

Returns

  • a pre-compiled statement object.

Throws

SQLException

public static SQLiteDatabase create(SQLiteDatabase.CursorFactory factory)

Create a memory backed SQLite database. Its contents will be destroyed when the database is closed.

Sets the locale of the database to the the system's current locale. Call setLocale(Locale) if you would like something else.

Parameters

factory an optional factory class that is called to instantiate a cursor when query is called

Returns

  • a SQLiteDatabase object, or null if the database can't be created

public int delete(String table, String whereClause, String[] whereArgs)

Convenience method for deleting rows in the database.

Parameters

table the table to delete from
whereClause the optional WHERE clause to apply when deleting. Passing null will delete all rows.

Returns

  • the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

public void endTransaction()

End a transaction. See beginTransaction for notes about how to use this and when tranactions are commited and rolled back.

public void execSQL(String sql, Object[] bindArgs)

Execute a single SQL statement that is not a query. For example, CREATE TABLE, DELETE, INSERT, etc. Multiple statements separated by ;s are not supported. it takes a write lock,

Parameters

bindArgs only byte[], String, Long and Double are supported in bindArgs.

Throws

SQLException If the SQL string is invalid for some reason

public void execSQL(String sql)

Execute a single SQL statement that is not a query. For example, CREATE TABLE, DELETE, INSERT, etc. Multiple statements separated by ;s are not supported. it takes a write lock

Throws

SQLException If the SQL string is invalid for some reason

public static String findEditTable(String tables)

Finds the name of the first table, which is editable.

Parameters

tables a list of tables

Returns

  • the first table listed

public long getMaximumSize()

Returns the maximum size the database may grow to.

Returns

  • the new maximum database size

public long getPageSize()

Returns the maximum size the database may grow to.

Returns

  • the new maximum database size

public final String getPath()

Getter for the path to the database file.

Returns

  • the path to our database file.

public Map<StringString> getSyncedTables()

public int getVersion()

Gets the database version.

Returns

  • the database version

public boolean inTransaction()

return true if there is a transaction pending

public long insert(String table, String nullColumnHack, ContentValues values)

Convenience method for inserting a row into the database.

Parameters

table the table to insert the row into
nullColumnHack SQL doesn't allow inserting a completely empty row, so if initialValues is empty this column will explicitly be assigned a NULL value
values this map contains the initial column values for the row. The keys should be the column names and the values the column values

Returns

  • the row ID of the newly inserted row, or -1 if an error occurred

public long insertOrThrow(String table, String nullColumnHack, ContentValues values)

Convenience method for inserting a row into the database.

Parameters

table the table to insert the row into
nullColumnHack SQL doesn't allow inserting a completely empty row, so if initialValues is empty this column will explicitly be assigned a NULL value
values this map contains the initial column values for the row. The keys should be the column names and the values the column values

Returns

  • the row ID of the newly inserted row, or -1 if an error occurred

Throws

SQLException
SQLException

public boolean isDbLockedByCurrentThread()

Checks if the database lock is held by this thread.

Returns

  • true, if this thread is holding the database lock.

public boolean isDbLockedByOtherThreads()

Checks if the database is locked by another thread. This is just an estimate, since this status can change at any time, including after the call is made but before the result has been acted upon.

Returns

  • true, if the database is locked by another thread

public boolean isOpen()

Returns

  • true if the DB is currently open (has not been closed)

public boolean isReadOnly()

return whether the DB is opened as read only.

Returns

  • true if DB is opened as read only

public void markTableSyncable(String table, String deletedTable)

Mark this table as syncable. When an update occurs in this table the _sync_dirty field will be set to ensure proper syncing operation.

Parameters

table the table to mark as syncable
deletedTable The deleted table that corresponds to the syncable table

public void markTableSyncable(String table, String foreignKey, String updateTable)

Mark this table as syncable, with the _sync_dirty residing in another table. When an update occurs in this table the _sync_dirty field of the row in updateTable with the _id in foreignKey will be set to ensure proper syncing operation.

Parameters

table an update on this table will trigger a sync time removal
foreignKey this is the column in table whose value is an _id in updateTable
updateTable this is the table that will have its _sync_dirty

public boolean needUpgrade(int newVersion)

public static SQLiteDatabase openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags)

Open the database according to the flags OPEN_READWRITE OPEN_READONLY CREATE_IF_NECESSARY and/or NO_LOCALIZED_COLLATORS.

Sets the locale of the database to the the system's current locale. Call setLocale(Locale) if you would like something else.

Parameters

path to database file to open and/or create
factory an optional factory class that is called to instantiate a cursor when query is called, or null for default
flags to control database access mode

Returns

  • the newly opened database

Throws

SQLiteException if the database cannot be opened

public static SQLiteDatabase openOrCreateDatabase(String path, SQLiteDatabase.CursorFactory factory)

Equivalent to openDatabase(path, factory, CREATE_IF_NECESSARY).

public static SQLiteDatabase openOrCreateDatabase(File file, SQLiteDatabase.CursorFactory factory)

Equivalent to openDatabase(file.getPath(), factory, CREATE_IF_NECESSARY).

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Query the given table, returning a Cursor over the result set.

Parameters

table The table name to compile the query against.
columns A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

Returns

  • A Cursor object, which is positioned before the first entry

See Also

public Cursor query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Query the given URL, returning a Cursor over the result set.

Parameters

distinct true if you want each row to be unique, false otherwise.
table The table name to compile the query against.
columns A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

Returns

  • A Cursor object, which is positioned before the first entry

See Also

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

Query the given table, returning a Cursor over the result set.

Parameters

table The table name to compile the query against.
columns A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

Returns

  • A Cursor object, which is positioned before the first entry

See Also

public Cursor queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Query the given URL, returning a Cursor over the result set.

Parameters

cursorFactory the cursor factory to use, or null for the default factory
distinct true if you want each row to be unique, false otherwise.
table The table name to compile the query against.
columns A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

Returns

  • A Cursor object, which is positioned before the first entry

See Also

public Cursor rawQuery(String sql, String[] selectionArgs)

Runs the provided SQL and returns a Cursor over the result set.

Parameters

sql the SQL query. The SQL string must not be ; terminated
selectionArgs You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Returns

  • A Cursor object, which is positioned before the first entry

public Cursor rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable)

Runs the provided SQL and returns a cursor over the result set.

Parameters

cursorFactory the cursor factory to use, or null for the default factory
sql the SQL query. The SQL string must not be ; terminated
selectionArgs You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.
editTable the name of the first table, which is editable

Returns

  • A Cursor object, which is positioned before the first entry

public static int releaseMemory()

Attempts to release memory that SQLite holds but does not require to operate properly. Typically this memory will come from the page cache.

Returns

  • the number of bytes actually released

public long replace(String table, String nullColumnHack, ContentValues initialValues)

Convenience method for replacing a row in the database.

Parameters

table the table in which to replace the row
nullColumnHack SQL doesn't allow inserting a completely empty row, so if initialValues is empty this row will explicitly be assigned a NULL value
initialValues this map contains the initial column values for the row. The key

Returns

  • the row ID of the newly inserted row, or -1 if an error occurred

public long replaceOrThrow(String table, String nullColumnHack, ContentValues initialValues)

Convenience method for replacing a row in the database.

Parameters

table the table in which to replace the row
nullColumnHack SQL doesn't allow inserting a completely empty row, so if initialValues is empty this row will explicitly be assigned a NULL value
initialValues this map contains the initial column values for the row. The key

Returns

  • the row ID of the newly inserted row, or -1 if an error occurred

Throws

SQLException
SQLException

public void setLocale(Locale locale)

Sets the locale for this database. Does nothing if this database has the NO_LOCALIZED_COLLATORS flag set or was opened read only.

Throws

SQLException if the locale could not be set. The most common reason for this is that there is no collator available for the locale you requested. In this case the database remains unchanged.

public void setLockingEnabled(boolean lockingEnabled)

Control whether or not the SQLiteDatabase is made threadsafe by using locks around critical sections. This is pretty expensive, so if you know that your DB will only be used by a single thread then you should set this to false. The default is true.

Parameters

lockingEnabled set to true to enable locks, false otherwise

public long setMaximumSize(long numBytes)

Sets the maximum size the database will grow to. The maximum size cannot be set below the current size.

Parameters

numBytes the maximum database size, in bytes

Returns

  • the new maximum database size

public void setPageSize(long numBytes)

Sets the database page size. The page size must be a power of two. This method does not work if any data has been written to the database file, and must be called right after the database has been created.

Parameters

numBytes the database page size, in bytes

public void setTransactionSuccessful()

Marks the current transaction as successful. Do not do any more database work between calling this and calling endTransaction. Do as little non-database work as possible in that situation too. If any errors are encounted between this and endTransaction the transaction will still be committed.

Throws

IllegalStateException if the current thread is not in a transaction or the transaction is already marked as cucessful.

public void setVersion(int version)

Sets the database version.

Parameters

version the new database version

public int update(String table, ContentValues values, String whereClause, String[] whereArgs)

Convenience method for updating rows in the database.

Parameters

table the table to update in
values a map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause the optional WHERE clause to apply when updating. Passing null will update all rows.

Returns

  • the number of rows affected

public boolean yieldIfContended()

Temporarily end the transaction to let other threads run. The transaction is assumed to be successful so far. Do not call setTransactionSuccessful before calling this. When this returns a new transaction will have been created but not marked as successful.

Returns

  • true if the transaction was yielded

Protected Methods

protected void finalize()

Called by the virtual machine when there are no longer any (non-weak) references to the receiver. Subclasses can use this facility to guarantee that any associated resources are cleaned up before the receiver is garbage collected. Uncaught exceptions which are thrown during the running of the method cause it to terminate immediately, but are otherwise ignored.

Note: The virtual machine assumes that the implementation in class Object is empty.

protected void onAllReferencesReleased()

Copyright 2007 Google Inc. Build 0.9_r1-98467 - 14 Aug 2008 18:48