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
Summary
Constants
Public Methods
|
|
|
|
|
void |
beginTransaction() |
|
|
|
|
|
void |
close() |
|
|
|
|
|
SQLiteStatement |
compileStatement(String sql) |
|
|
|
static |
|
SQLiteDatabase |
create(SQLiteDatabase.CursorFactory factory) |
|
|
|
|
|
int |
delete(String table, String whereClause, String[] whereArgs) |
|
|
|
|
|
void |
endTransaction() |
|
|
|
|
|
void |
execSQL(String sql, Object[] bindArgs) |
|
|
|
|
|
void |
execSQL(String sql) |
|
|
|
static |
|
String |
findEditTable(String tables) |
|
|
|
|
|
long |
getMaximumSize() |
|
|
|
|
|
long |
getPageSize() |
|
|
final |
|
|
String |
getPath() |
|
|
|
|
|
Map<String, String> |
getSyncedTables() |
|
|
|
|
|
int |
getVersion() |
|
|
|
|
|
boolean |
inTransaction() |
|
|
|
|
|
long |
insert(String table, String nullColumnHack, ContentValues values) |
|
|
|
|
|
long |
insertOrThrow(String table, String nullColumnHack, ContentValues values) |
|
|
|
|
|
boolean |
isDbLockedByCurrentThread() |
|
|
|
|
|
boolean |
isDbLockedByOtherThreads() |
|
|
|
|
|
boolean |
isOpen() |
|
|
|
|
|
boolean |
isReadOnly() |
|
|
|
|
|
void |
markTableSyncable(String table, String deletedTable) |
|
|
|
|
|
void |
markTableSyncable(String table, String foreignKey, String updateTable) |
|
|
|
|
|
boolean |
needUpgrade(int newVersion) |
|
|
|
static |
|
SQLiteDatabase |
openDatabase(String path, SQLiteDatabase.CursorFactory factory, int flags) |
|
|
|
static |
|
SQLiteDatabase |
openOrCreateDatabase(String path, SQLiteDatabase.CursorFactory factory) |
|
|
|
static |
|
SQLiteDatabase |
openOrCreateDatabase(File file, SQLiteDatabase.CursorFactory factory) |
|
|
|
|
|
Cursor |
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) |
|
|
|
|
|
Cursor |
query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) |
|
|
|
|
|
Cursor |
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) |
|
|
|
|
|
Cursor |
queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) |
|
|
|
|
|
Cursor |
rawQuery(String sql, String[] selectionArgs) |
|
|
|
|
|
Cursor |
rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable) |
|
|
|
static |
|
int |
releaseMemory() |
|
|
|
|
|
long |
replace(String table, String nullColumnHack, ContentValues initialValues) |
|
|
|
|
|
long |
replaceOrThrow(String table, String nullColumnHack, ContentValues initialValues) |
|
|
|
|
|
void |
setLocale(Locale locale) |
|
|
|
|
|
void |
setLockingEnabled(boolean lockingEnabled) |
|
|
|
|
|
long |
setMaximumSize(long numBytes) |
|
|
|
|
|
void |
setPageSize(long numBytes) |
|
|
|
|
|
void |
setTransactionSuccessful() |
|
|
|
|
|
void |
setVersion(int version) |
|
|
|
|
|
int |
update(String table, ContentValues values, String whereClause, String[] whereArgs) |
|
|
|
|
|
boolean |
yieldIfContended() |
Protected Methods
clone,
equals,
finalize,
getClass,
hashCode,
notify,
notifyAll,
toString,
wait,
wait,
wait
Details
Constants
public
static
final
int
CREATE_IF_NECESSARY
public
static
final
int
NO_LOCALIZED_COLLATORS
public
static
final
int
OPEN_READONLY
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.
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.
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. |
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
public
static
String
findEditTable(String tables)
Finds the name of the first table, which is editable.
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
int
getVersion()
Gets 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
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)
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
Equivalent to openDatabase(path, factory, CREATE_IF_NECESSARY).
Equivalent to openDatabase(file.getPath(), factory, CREATE_IF_NECESSARY).
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
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
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
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
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
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
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
|
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()