Android - An Open Handset Alliance Project

android.database.sqlite
public class

android.database.sqlite.SQLiteDatabase

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

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	NO_LOCALIZED_COLLATORS	Flag for create(SQLiteDatabase.CursorFactory) methods. Causes the database to be created without support for localized collators.	16	0x00000010
int	OPEN_READONLY	Flag for create(SQLiteDatabase.CursorFactory) and open(File, SQLiteDatabase.CursorFactory) methods.	1	0x00000001
int	OPEN_READWRITE	Flag for create(SQLiteDatabase.CursorFactory) and open(File, SQLiteDatabase.CursorFactory) methods. Causes the database to be opened for reading and writing.	0	0x00000000

Public Methods

		void	close()
			Close the database.
		SQLiteStatement	compileStatement(String sql)
			Compiles an SQL statement into a reusable pre-compiled statement object.
	static	SQLiteDatabase	create(CursorFactory factory)
			Create a memory backed SQLite database.
	static	SQLiteDatabase	create(File file, int version, CursorFactory factory)
			Create a new SQLite database and open it.
	static	SQLiteDatabase	create(String path, int version, CursorFactory factory)
			Create a new SQLite database and open it.
		int	delete(String table, String whereClause, String[] whereArgs)
			Convenience method for deleting rows in the database.
		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.
		Locale	getLocale()
			Returns the default collation as set by setLocale(Locale).
		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	getSyncedTables()
		int	getVersion()
			Gets the database version.
		long	insert(String table, String nullColumnHack, ContentValues values)
			Convenience method for inserting a row into the database.
		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.
	static	SQLiteDatabase	open(File file, CursorFactory factory)
			Open a SQLite database from a file.
	static	SQLiteDatabase	open(String path, CursorFactory factory)
			Open a SQLite database from a file.
		Cursor	query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
			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(CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
			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(CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable)
			Runs the provided SQL and returns a cursor over the result set.
		long	replace(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.
		long	setMaximumSize(long numBytes)
			Sets the maximum size the database will grow to.
		void	setPageSize(long numBytes)
			Sets the database page size.
		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.

Protected Methods

				void	finalize()
					Called by the virtual machine when there are no longer any (non-weak) references to the receiver.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

	Object	clone()
		Answers a new instance of the same class as the receiver, whose slots have been filled in with the values in the slots of the receiver.
	boolean	equals(Object o)
		Compares the argument to the receiver, and answers true if they represent the same object using a class specific comparison.
	void	finalize()
		Called by the virtual machine when there are no longer any (non-weak) references to the receiver.
final	Class	getClass()
		Answers the unique instance of java.lang.Class which represents the class of the receiver.
	int	hashCode()
		Answers an integer hash code for the receiver.
final	void	notify()
		Causes one thread which is `wait` ing on the receiver to be made ready to run.
final	void	notifyAll()
		Causes all threads which are `wait` ing on the receiver to be made ready to run.
	String	toString()
		Answers a string containing a concise, human-readable description of the receiver.
final	void	wait(long time, int frac)
		Causes the thread which sent this message to be made not ready to run either pending some change in the receiver (as indicated by `notify` or `notifyAll`) or the expiration of the timeout.
final	void	wait(long time)
		Causes the thread which sent this message to be made not ready to run either pending some change in the receiver (as indicated by `notify` or `notifyAll`) or the expiration of the timeout.
final	void	wait()
		Causes the thread which sent this message to be made not ready to run pending some change in the receiver (as indicated by `notify` or `notifyAll`).

Methods inherited from interface android.database.sqlite.SQLiteClosable

Details

Constants

public static final int NO_LOCALIZED_COLLATORS

Flag for create(SQLiteDatabase.CursorFactory) methods. Causes the database to be created without support for localized collators.

This causes the collator LOCALIZED not to be created.

This flag does nothing when passed to open(). SQLiteDatabase will always use the setting that the database was created with.

If this is set, setLocale will do nothing and getLocale will return null.

Constant Value: 16 (0x00000010)

public static final int OPEN_READONLY

Flag for create(SQLiteDatabase.CursorFactory) and open(File, SQLiteDatabase.CursorFactory) methods. Causes the database to be opened for reading only.

Constant Value: 1 (0x00000001)

public static final int OPEN_READWRITE

Flag for create(SQLiteDatabase.CursorFactory) and open(File, SQLiteDatabase.CursorFactory) methods. Causes the database to be opened for reading and writing.

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

Constant Value: 0 (0x00000000)

Public Methods

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(CursorFactory factory)

Create a memory backed SQLite database. It's 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 static SQLiteDatabase create(File file, int version, CursorFactory factory)

Create a new SQLite database and open it.

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

Parameters

file	the file where the database should be created
version	the version number to tag the database with
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 couldn't be created

public static SQLiteDatabase create(String path, int version, CursorFactory factory)

Create a new SQLite database and open it.

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

Parameters

path	the path where the database should be created
version	the version number to tag the database with
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 couldn'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 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.

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 Locale getLocale()

Returns the default collation as set by setLocale(Locale).

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 getSyncedTables()

public int getVersion()

Gets the database version.

Returns

the database version, or -1 in the event of an error

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 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 static SQLiteDatabase open(File file, CursorFactory factory)

Open a SQLite database from a file.

Parameters

file	the file containing the database
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 doesn't exist

public static SQLiteDatabase open(String path, CursorFactory factory)

Open a SQLite database from a file.

Parameters

path	the path to the file containing the database
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 doesn't exist

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

Query the given URL, returning a Cursor over the result set. If table contains a list of tables to join only the columns of the first table listed may be updated using commitUpdates().

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.

Returns

A Cursor object, which is positioned before the first entry

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. If table contains a list of tables to join only the columns of the first table listed may be updated using commitUpdates().

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

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

Query the given URL, returning a Cursor over the result set. If table contains a list of tables to join only the columns of the first table listed may be updated using commitUpdates().

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.

Returns

A Cursor object, which is positioned before the first entry

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

Runs the provided SQL and returns a Cursor over the result set. Warning: The returned cursor will not be able to write to the database via commitUpdates()!

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(CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable)

Runs the provided SQL and returns a cursor over the result set. Warning: The returned cursor will not be able to write to the database via commitUpdates()!

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 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 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

IllegalArgumentException	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 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 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

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.


e.g. "intent" or "activity"

Android - An Open Handset Alliance Project

Documentation

Goodies

android.database.sqlite.SQLiteDatabase

Localized Collation - ORDER BY

Nested Classes

Summary

Constants

Public Methods

Protected Methods

Methods inherited from class java.lang.Object

Methods inherited from interface android.database.sqlite.SQLiteClosable

Details

Constants

public static final int NO_LOCALIZED_COLLATORS

public static final int OPEN_READONLY

public static final int OPEN_READWRITE

Public Methods

public void close()

public SQLiteStatement compileStatement(String sql)

Parameters

Returns

Throws

public static SQLiteDatabase create(CursorFactory factory)

Parameters

Returns

public static SQLiteDatabase create(File file, int version, CursorFactory factory)

Parameters

Returns

public static SQLiteDatabase create(String path, int version, CursorFactory factory)

Parameters

Returns

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

Parameters

Returns

public void execSQL(String sql)

Throws

public static String findEditTable(String tables)

Parameters

Returns

public Locale getLocale()

public long getMaximumSize()

Returns

public long getPageSize()

Returns

public final String getPath()

Returns

public Map getSyncedTables()

public int getVersion()

Returns

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

Parameters

Returns

public void markTableSyncable(String table, String deletedTable)

Parameters

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

Parameters

public static SQLiteDatabase open(File file, CursorFactory factory)

Parameters

Returns

public static SQLiteDatabase open(String path, CursorFactory factory)

Parameters

Returns

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

Parameters

Returns

See Also

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

Parameters

Returns

See Also

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

Parameters

Returns

See Also

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

Parameters

Returns

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

Parameters