0 foreword
SQLite, a lightweight database, is an ACID-compliant relational database management system contained in a relatively small C library. It is a public domain project established by D. Richardhipp. It is designed to be embedded, and it has been used in many embedded products, its resource consumption is very low, in embedded devices, may only need a few hundred K of memory is enough. It can support Windows/Linux/Unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interface. Compared with Mysql and PostgreSQL, two open source database management systems in the world, It can process it faster than any of them. (From Baidu Baike)
Given SQLite’s business-processing capabilities, the Android platform naturally uses it as a built-in database storage module. Stored in SD card, just a file form, can be convenient backup, transplant. Interested partners, you can go to SQLite official website to download C source code research.
In addition, the Android platform provides a full set of SQLite operation apis. You can easily and quickly create tables, update tables, add, delete, change and check table data.
1 Creating a Database
Create database methods, divided into four classes.
- Create /createInMemory Creates a database in memory and destroys it when the database is shut down.
- OpenDatabase To open a database, you can specify the opening method.
- OpenOrCreateDatabase creates and opens the database.
- GetWritableDatabase/getReadableDatabase open, speaking, reading and writing, or read-only database.
You can specify CursorFactory, Flags, DatabaseErrorHandler (API 11), and OpenParams (API 27). All end up in the openDatabase method.
SQLite adds four types of access to the database file.
- OPEN_READWRITE: open database files in read-write mode. You can add, delete, modify, and query database files.
- OPEN_READONLY: Opens database files in read-only mode. Only query operations can be performed. If data is inserted, an exception will be thrown
android.database.sqlite.SQLiteReadOnlyDatabaseException. - CREATE_IF_NECESSARY: When a database is opened, if no database file is available, the database will be created automatically.
- NO_LOCALIZED_COLLATORS: disables the setLocale method.
// This object is used to generate cursor when the query method is executed
SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() {
@Override
public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, String editTable, SQLiteQuery query) {
if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {
return new SQLiteCursor(masterQuery, editTable, query);
}else
return newSQLiteCursor(db,masterQuery, editTable, query); }};// Execute this object when a database exception occurs.
DatabaseErrorHandler errorHandler = new DatabaseErrorHandler() {
@Override
public void onCorruption(SQLiteDatabase dbObj) {
// Close the databasedbObj.close(); }};// Open database parameters, only supported by API 27.
SQLiteDatabase.OpenParams openParams = new SQLiteDatabase.OpenParams.Builder()
.setCursorFactory(factory) / / specified CursorFactory
.setErrorHandler(errorHandler)/ / specified DatabaseErrorHandler
.addOpenFlags(SQLiteDatabase.CREATE_IF_NECESSARY) // Specify open permission.
.build();
Copy the code1.1 the create
Create the database directly in memory, using the create/createInMemory methods. The latter is only supported in API 27.
/** * Create an SQLite database in memory and destroy it when the database is closed. * Suitable for temporary storage of data. * *@paramFactory This parameter is optional. You are advised to pass NULL or build an instance of factory. *@returnReturns the database object *@throwsSQLiteException */ is thrown when the database creation fails
@NonNull
public static SQLiteDatabase create(@Nullable CursorFactory factory) {
// This is a magic string with special meaning for SQLite.
return openDatabase(SQLiteDatabaseConfiguration.MEMORY_DB_PATH,
factory, CREATE_IF_NECESSARY);
}
/** * Create an SQLite database in memory and destroy it when the database is closed. * Suitable for temporary storage of data. * *@paramOpenParams configures the parameters * to open the database@returnReturns the database object *@throwsSQLiteException */ is thrown when the database creation fails
@NonNull
public static SQLiteDatabase createInMemory(@NonNull OpenParams openParams) {
return openDatabase(SQLiteDatabaseConfiguration.MEMORY_DB_PATH,
openParams.toBuilder().addOpenFlags(CREATE_IF_NECESSARY).build());
}
Copy the codeFlags = CREATE_IF_NECESSARY.
1.2 the openDatabase
/** * Open database ** according to flags@paramPath File path for creating or opening the database. You can use relative or absolute addresses. * Relative addresses can be stored in the application cache database directory, and absolute addresses can be stored in the SD card directory. *@paramFactory This parameter is optional. You are advised to pass NULL or build an instance of factory. *@paramFlags Specifies the mode in which data is controlled@returnReturns to open the newly opened database *@throwsSQLiteException */ is thrown when the database creation fails
public static SQLiteDatabase openDatabase(@NonNull String path, @Nullable CursorFactory factory,
@DatabaseOpenFlags int flags) {
return openDatabase(path, factory, flags, null);
}
/** * Open database ** according to flags@paramPath Opens or creates the database file *@paramFactory This parameter is optional. You are advised to pass NULL or build an instance of factory. *@paramFlags controls how database files are accessed *@paramErrorHandler When SQLite reports a database error, use DatabaseErrorHandler to handle the error. For example, close the database. *@returnReturns to open the newly opened database *@throwsSQLiteException */ is thrown when the database creation fails
public static SQLiteDatabase openDatabase(@NonNull String path, @Nullable CursorFactory factory,
@DatabaseOpenFlags int flags, @Nullable DatabaseErrorHandler errorHandler) {
SQLiteDatabase db = new SQLiteDatabase(path, flags, factory, errorHandler, -1, -1, -1.null.null);
db.open();
return db;
}
/** * Open database ** according to the specified parameters@paramPath File path for creating or opening the database. * Use the absolute path, or context#getDatabasePath(String) *@paramOpenParams configures the parameters * to open the database@returnReturns to open the newly opened database *@throwsSQLiteException */ is thrown when the database creation fails
public static SQLiteDatabase openDatabase(@NonNull File path, @NonNull OpenParams openParams) {
return openDatabase(path.getPath(), openParams);
}
private static SQLiteDatabase openDatabase(@NonNull String path, @NonNull OpenParams openParams) { Preconditions.checkArgument(openParams ! =null."OpenParams cannot be null");
SQLiteDatabase db = new SQLiteDatabase(path, openParams.mOpenFlags,
openParams.mCursorFactory, openParams.mErrorHandler,
openParams.mLookasideSlotSize, openParams.mLookasideSlotCount,
openParams.mIdleConnectionTimeout, openParams.mJournalMode, openParams.mSyncMode);
db.open();
return db;
}
Copy the codeThe four methods can be divided into two categories.
- The first two specify Flags, CursorFactory, and DatabaseErrorHandler (API 11) support, respectively.
- The last two are uniformly specified by OpenParams to open the database. Contains but is not limited to the above three attributes.
1.3 openOrCreateDatabase
OpenDatabase (file.getPath(), factory, CREATE_IF_NECESSARY). */
public static SQLiteDatabase openOrCreateDatabase(@NonNull File file, @Nullable CursorFactory factory) {
return openOrCreateDatabase(file.getPath(), factory);
}
OpenDatabase (path, factory, CREATE_IF_NECESSARY). */
public static SQLiteDatabase openOrCreateDatabase(@NonNull String path, @Nullable CursorFactory factory) {
return openDatabase(path, factory, CREATE_IF_NECESSARY, null);
}
OpenDatabase (path, factory, CREATE_IF_NECESSARY, errorHandler). */
public static SQLiteDatabase openOrCreateDatabase(@NonNull String path, @Nullable CursorFactory factory, @Nullable DatabaseErrorHandler errorHandler) {
return openDatabase(path, factory, CREATE_IF_NECESSARY, errorHandler);
}
Copy the codeCreate and open the database, and three methods finally will be executed to SQLiteDatabase openDatabase (String, CursorFactory, int, DatabaseErrorHandler) method.
1.4 SQLiteOpenHelper
SQLiteOpenHelper is the most user-friendly tool Android has packaged. Convenient for developers to manage their own database table directory and structure.
/** * Manage database creation and versioning */
public class DBOpenHelper extends SQLiteOpenHelper {
SQLiteDatabase openDatabase(String, CursorFactory)
public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
OpenDatabase (String, CursorFactory, DatabaseErrorHandler)
@TargetApi(Build.VERSION_CODES.HONEYCOMB)
public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
super(context, name, factory, version, errorHandler);
}
// SQLiteDatabase openDatabase(String, OpenParams);
@TargetApi(Build.VERSION_CODES.P)
public DBOpenHelper(Context context, String name, int version, SQLiteDatabase.OpenParams openParams) {
super(context, name, version, openParams);
}
// Called when creating a data file, which is suitable for creating a new table
@Override
public void onCreate(SQLiteDatabase db) {}// Called when updating the database version, suitable for updating the table structure or creating a new table
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}// Generate a helper object that can open the database file. The file name can be a relative or absolute path
DBOpenHelper dbHelper = new DBOpenHelper(this."test.db".null.1);
// Open the database file in read-write mode
SQLiteDatabase database = dbHelper.getWritableDatabase();
Copy the code1.5 Application Scenarios
OpenDatabase is suitable for opening existing database files. And the table directory and structure are fixed, generally only the routine operation of the database table. For example, there is a national city information database file that needs to be opened in the project.
SQLiteOpenHelper needs to recreate the database file itself, and may need to modify the table directory and structure of the database based on later requirements. Upgrade the data version, which is handled in the onUpgrade method. OpenDatabase can also fulfill this requirement, but with a little more difficulty.
2 Inserting Data
The insert operation methods are summarized into two classes, insert and replace, and finally the insertWithOnConflict method is executed.
/** * Insert a row into the database **@paramTable specifies the name of the table and inserts a row *@paramNullColumnHack This parameter is optional. Null is recommended. * If null is set, null data cannot be inserted into the table. * If null is not set, then you need to set the name of the property column in the table that can be null. * When values = null, null data can be inserted into the table. * Insert a row of data, and only nullColumnHack is null. *@paramValues Map collection that contains a row of data to be inserted. The key and value must contain at least one attribute. * Key is the attribute column name and value is the attribute value. *@returnReturns the sequence number of the newly inserted row, or -1 */ in case of an error
public long insert(String table, String nullColumnHack, ContentValues values) {
try {
return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values, e);
return -1; }}/** * Insert a row into the database. You just need to catch the exception manually when you insert an exception. * All parameters are the same as above *@param table
* @param nullColumnHack
* @param values
* @throws SQLException
* @return* /
public long insertOrThrow(String table, String nullColumnHack, ContentValues values)
throws SQLException {
return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
}
Copy the code/** * Replace a row in the table with a primary key, or insert a row if the primary key does not exist in the table. * All parameters are the same as above *@param table
* @param nullColumnHack
* @param initialValues
* @returnReturns the sequence number of the newly inserted row, or -1 */ in case of an error
public long replace(String table, String nullColumnHack, ContentValues initialValues) {
try {
return insertWithOnConflict(table, nullColumnHack, initialValues,
CONFLICT_REPLACE);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + initialValues, e);
return -1; }}/** * Replace a row in the table with a primary key, or insert a row if the primary key does not exist in the table. * All parameters are the same as above *@param table
* @param nullColumnHack
* @param initialValues
* @throws SQLException
* @return* /
public long replaceOrThrow(String table, String nullColumnHack, ContentValues initialValues) throws SQLException {
return insertWithOnConflict(table, nullColumnHack, initialValues,
CONFLICT_REPLACE);
}
Copy the code/** * A generic way to insert a row into a database. * Same as above. *@param table
* @param nullColumnHack
* @param initialValues
* @paramConflictAlgorithm Conflict resolution algorithm *@returnReturns the serial number of the row to which the data was inserted. -1 is returned when a conflictAlgorithm error or an insert exception occurs. * /
public long insertWithOnConflict(String table, String nullColumnHack,
ContentValues initialValues, int conflictAlgorithm) {
acquireReference();
try {
StringBuilder sql = new StringBuilder();
sql.append("INSERT");
// Only the first and last values are inserted.
// {"", "OR ROLLBACK", "OR ABORT", "OR FAIL", "OR IGNORE", "OR REPLACE"};
sql.append(CONFLICT_VALUES[conflictAlgorithm]);
sql.append(" INTO ");
sql.append(table);
sql.append('(');
Object[] bindArgs = null;
intsize = (initialValues ! =null && !initialValues.isEmpty())
? initialValues.size() : 0;
// Concatenate SQL statements
if (size > 0) {
bindArgs = new Object[size];
int i = 0;
for (String colName : initialValues.keySet()) {
sql.append((i > 0)?"," : "");
// Splice column names
sql.append(colName);
// Save the attribute value parameter
bindArgs[i++] = initialValues.get(colName);
}
sql.append(') ');
sql.append(" VALUES (");
// Take all values with? Do a placeholder
for (i = 0; i < size; i++) {
sql.append((i > 0)?"And?" : "?"); }}else {
// If the data set to be inserted is NULL, there must be at least one nullColumnHack column
sql.append(nullColumnHack + ") VALUES (NULL");
}
sql.append(') ');
// Perform the insert operation
SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
try {
return statement.executeInsert();
} finally{ statement.close(); }}finally{ releaseReference(); }}Copy the codeTo demonstrate the code, do not disguise the method of throwing an exception:
ContentValues values = new ContentValues();
values.put("id".1);
values.put("name"."flueky");
values.put("age".27);
// Insert full data, id name age
database.insert("user".null, values);
// Insert null data with name set to null
database.insert("user"."name".null);
// Replace the data with id = 1
values.put("id".1);
values.put("name"."xiaofei");
values.put("age".27);
database.replace("user".null, values);
Copy the codeThe replace method is similar to the update operation.
3 Deleting Data
There is only one method to delete database table data. You can specify deletion conditions
/** * Delete a row from the database@paramTable Name of the table to be deleted *@paramWhereClause, when passed null, deletes all data in the table. * Or if you specify a deletion condition, only rows that meet the condition will be deleted. *@paramWhereArgs specifies the values of the delete criteria, in order to replace the values in the delete criteria. . *@returnReturns the number of deleted rows when a condition is met. If no condition can be found for deletion, 0 is returned. * /
public int delete(String table, String whereClause, String[] whereArgs) {
acquireReference();
try {
SQLiteStatement statement = new SQLiteStatement(this."DELETE FROM "+ table + (! TextUtils.isEmpty(whereClause) ?" WHERE " + whereClause : ""), whereArgs);
try {
return statement.executeUpdateDelete();
} finally{ statement.close(); }}finally{ releaseReference(); }}Copy the code4 Updating Data
There are two methods to update table data. It is recommended to use the first one. The conflictAlgorithm parameter defaults to CONFLICT_NONE.
/** * Update a row in a database table **@paramTable Name of the table to be updated *@paramValues contains a map collection of attribute names and new attribute values. *@paramThe optional WHERE condition of whereClause determines which rows need to be updated. * If empty, all rows are updated. *@paramWhereArgs replaces what is included in the WHERE condition? . *@returnReturns the number of updated rows */
public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
}
/** * Update a row of data in a database table@param table
* @param values
* @param whereClause
* @param whereArgs
* @paramConflictAlgorithm Algorithm that determines the update of a conflict. *@return* /
public int updateWithOnConflict(String table, ContentValues values,
String whereClause, String[] whereArgs, int conflictAlgorithm) {
// Null is valid
if (values == null || values.isEmpty()) {
throw new IllegalArgumentException("Empty values");
}
acquireReference();
try {
// Concatenate updated SQL statements
StringBuilder sql = new StringBuilder(120);
sql.append("UPDATE ");
sql.append(CONFLICT_VALUES[conflictAlgorithm]);
sql.append(table);
sql.append(" SET ");
// concatenate values and whereArgs values
int setValuesSize = values.size();
int bindArgsSize = (whereArgs == null)? setValuesSize : (setValuesSize + whereArgs.length); Object[] bindArgs =new Object[bindArgsSize];
int i = 0;
// Concatenate the column attributes to be updated
for (String colName : values.keySet()) {
sql.append((i > 0)?"," : "");
sql.append(colName);
// Save the attribute value
bindArgs[i++] = values.get(colName);
sql.append("=?");
}
// Add the value of the WHERE condition
if(whereArgs ! =null) {
for(i = setValuesSize; i < bindArgsSize; i++) { bindArgs[i] = whereArgs[i - setValuesSize]; }}if(! TextUtils.isEmpty(whereClause)) { sql.append(" WHERE ");
sql.append(whereClause);
}
SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);
try {
// Execute the SQL statement
return statement.executeUpdateDelete();
} finally{ statement.close(); }}finally{ releaseReference(); }}Copy the code5 Querying Data
Android provides a rich API for database table query operations, a total of 10 methods, remove overloaded methods, a total of four. The following table lists the four methods and the parameters used.
- ● Specifies a mandatory parameter
- ○ Indicates a transferable parameter
- –Indicates unused parameters
| parameter | query | queryWithFactory | rawQuery | rawQueryWithFactory |
|---|---|---|---|---|
| String table | low | low | – | – |
| String[] columns | low | low | – | – |
| String selection | low | low | – | – |
| String[] selectionArgs | low | low | low | low |
| String groupBy | low | low | – | – |
| String having | low | low | – | – |
| String orderBy | low | low | – | – |
| String limit | a. | low | – | – |
| boolean distinct | a. | low | – | – |
| CancellationSignal cancellationSignal | a. | a. | a. | a. |
| CursorFactory cursorFactory | – | low | – | low |
| String sql | – | – | low | low |
| String editTable | – | – | – | low |
Parameter Description:
- Table Indicates the name of the table to be queried.
- columnsLists the columns that you want to query. pass
nullAll columns will be returned. This is not recommended to prevent reading data from unused storage. - selectionDeclare the filter that needs to return table data, same as
whereThe conditional clause has the same format (does not containwhereItself). The incomingnullAll data in the table is returned. - selectionArgsReplace the
selectionUsed in the?或sqlUsed in the?. - groupByDeclare the grouping rules for returned table data, as in
GROUP BYClause (does not containGROUP BY) the incomingnullWill not group. - havingDecide which subgroups will be included in the
cursorIn the. If grouping conditions are used, followHAVINGClause (does not containHAVING) to format groups. The incomingnullWill include all the groups in thecursorIn the. This parameter is mandatory when no grouping is usednull. - orderBySort the table data. with
ORDER BYThe conditional statement does not containORDER BY). The incomingnullWill not sort. - limitLimits the number of queries. with
LIMITClause. The incomingnullThe number of queries is not limited. - distinctIf you want each row to be different, use
true, or usefalse. - cancellationSignalCancel the operation signal in the query process. If you do not need to manually cancel the command, run the
null. Thrown if the operation is cancelledOperationCanceledException。 - cursorFactorygenerate
CursorObject, if yesnullIs used when the database is openedcursorFactory. Also used when opening a databasenull, then automatically generatedCursor。 - sqlAll of the
queryMethods, they all mergesqlperformrawqueryMethods. - EditTable Specifies the name of the table to edit.
/** * The simplest and most commonly used query method. Finally, the SQL statement is concatenated and the rawQuery method is executed. * /
public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
return query(false, table, columns, selection, selectionArgs, groupBy,
having, orderBy, null /* limit */);
}
/** * The most complete query method. All arguments can be specified manually or null. In addition to the table. * /
public Cursor queryWithFactory(CursorFactory cursorFactory,
boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit, CancellationSignal cancellationSignal) {
acquireReference();
try {
String sql = SQLiteQueryBuilder.buildQueryString(
distinct, table, columns, selection, groupBy, having, orderBy, limit);
return rawQueryWithFactory(cursorFactory, sql, selectionArgs,
findEditTable(table), cancellationSignal);
} finally{ releaseReference(); }}/** * The simplest and most commonly used rawQuery method. You can customize the SQL statement for query and query independently. This method applies to associated query of complex relational tables. * /
public Cursor rawQuery(String sql, String[] selectionArgs) {
return rawQueryWithFactory(null, sql, selectionArgs, null.null);
}
/** * The rawQuery method with the most complete parameters. * /
public Cursor rawQueryWithFactory( CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable, CancellationSignal cancellationSignal) {
acquireReference();
try {
SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable,
cancellationSignal);
returndriver.query(cursorFactory ! =null ? cursorFactory : mCursorFactory,
selectionArgs);
} finally{ releaseReference(); }}Copy the code6 the transaction
A Transaction is a unit of work performed against a database. A Transaction is a unit or sequence of work that is done in a logical order, either manually by a user or automatically by some database program.
In Android, SQLite transactions provide three main apis. Before closing a transaction, if you want to commit the transaction to the database, you need to set the transaction completion flag. Otherwise, database operations done when a transaction is started will not be retained.
database.beginTransaction();
/* Perform the database operation */
database.setTransactionSuccessful();
database.endTransaction();
Copy the code7 the conclusion
At this point in this article, you have covered common SQLite operations. To customize SQL statements for richer database operations, use the database.execSQL method. Use the database.rawQuery method to query a customized SQL database.
The sample code
In order not to be too long, it is briefly introduced here. For more on SQLite action statements and usage, see a subsequent article.
Think it works? I’ll give you a bonus. ({{site.url}}/donate/)
Here’s the AD: Flueky’s tech site