“This is the third day of my participation in the First Challenge 2022. For details: First Challenge 2022”
Introduce a,
1.1 the SQLite
For duplicate or structured data, such as contact information, saving the data to a database is ideal. Let’s start using SQLite database on Android. The apis required to use databases on Android are provided in the Android.database.sqlite package.
Let’s play with the SQLite database and then move from SQLite to Room.
Note: While these apis are powerful, they are low level and take a lot of time and effort to use.
- There is no compile-time validation for the raw SQL query. As the data graph changes over time, you need to manually update affected SQL queries. This process is time-consuming and error-prone.
- You need to use a lot of boilerplate code to convert between SQL queries and data objects.
For these reasons, I strongly recommend using the Room persistence library as an abstraction layer to access information in your application’s SQLite database.
Room Persistence library: Provides an abstraction layer on TOP of SQLite, allowing smooth database access while taking full advantage of SQLite’s power.
1.2 Basic SQL Syntax
SQL is essentially a programming Language, its scientific name is “StructuredQuery Language” (short for SQL). However, SQL language is not a general programming language, it is dedicated to database access and processing, more like an operation command, so often said SQL statements rather than SQL code. Standard SQL statements fall into three categories: data definition, data manipulation, and data control, but different databases often have their own implementations.
SQLite is a compact embedded database that is easy to use and easy to develop. Just like MySQL and Oracle, SQLite also uses SQL statements to manage data. As a lightweight database, it does not involve complex data control operations, so App development only uses two types of SQL, data definition and data manipulation. In addition, SQLite’s SQL syntax is slightly different from general-purpose SQL syntax.
1.3 Database Helper SQLiteOpenHelper
Because of the limitations of SQLiteDatabase, it is not easy to open the database repeatedly if you are not careful. So Android provides a database helper called SQLiteOpenHelper to help developers use SQLite properly.
SQLiteOpenHelper can be used as follows:
-
Step 1: Create a new database action class that inherits from SQLiteOpenHelper and override the onCreate and onUpgrade methods as prompted. The onCreate method is executed only when the database is first opened, where the table structure can be created; The onUpgrade method, on the other hand, executes as the database version increases, where you can change the table structure based on the old and new version numbers.
-
Step 2: To secure the database, encapsulate several necessary methods, including obtaining singleton objects. The following describes how to open or close a database connection:
- Get a singleton: Ensure that the database is opened only once during App execution to avoid errors caused by repeated opening.
- Open database connection: SQLite has lock mechanism, namely read lock and write lock processing; Therefore, there are two types of database connections: read connections can be obtained by calling getReadableDatabase and write connections can be obtained by calling getWritableDatabase.
- Close database connection: After the database operation is complete, call the close method of the database instance to close the connection.
-
Step 3: Add, delete, modify, and query table records.
Use SQLite
2.1 Inherit SQLiteOpenHelper class and rewrite onCreate, onUpgrade and onOpen methods
Create the default database path: / data/data/com. SCC. Datastorage (your package name)/databases/user. The db
public class UserDBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "user.db";// Database name
private static UserDBHelper mHelper = null;// Database helper instance
private SQLiteDatabase mDB = null;// Database instance
public static final String TABLE_NAME = "user_info";
public static final String _ID = "_id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_AGE = "age";
public static final String COLUMN_UPDATE_TIME = "update_time";
/ * * *@paramContext Context (for example, an Activity) * DB_NAME database name * Factory NULL An optional cursor factory (usually null) *@paramVersion The integer * of the database model version calls the constructor */ of the parent class SQLiteOpenHelper
public UserDBHelper(Context context, int version) {
super(context, DB_NAME, null, version);
}
// Get a unique instance of SQLiteOpenHelper using the singleton mode
public static UserDBHelper getInstance(Context context, int version) {
if (version > 0 && mHelper == null) {
// Create database
mHelper = new UserDBHelper(context, version);
}
return mHelper;
}
// Create a database table, execute the table construction clause
@Override
public void onCreate(SQLiteDatabase db) {}/** * Android automatically calls this method when the database needs to be modified (the two database versions are different). */ create a new database table by deleting the database table
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}@Override
public void onOpen(SQLiteDatabase db) {
// is executed first after each successful database opening
super.onOpen(db); }}Copy the code userDBHelper = UserDBHelper.getInstance(SQLiteActivity.this.1);
Copy the code
2.2 create a table
The create command creates a TABLE in the format of create TABLE IFNOT EXISTS TABLE name (defined by fields separated by commas). . Take the user information table as an example, its construction sentence is as follows:
public class UserDBHelper extends SQLiteOpenHelper {
// Create the database, execute the table construction clause
@Override
public void onCreate(SQLiteDatabase db) {
Log.e(getClass().getName(), "onCreate");
// Create a table sentence
String SQL_CREATE_ENTRIES = "CREATE TABLE IF NOT EXISTS "
+ TABLE_NAME + "("
+ _ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
+ COLUMN_NAME+" VARCHAR NOT NULL,"
+ COLUMN_AGE+" INTEGER NOT NULL,"
+ COLUMN_UPDATE_TIME+" VARCHAR NOT NULL" + "); ";
Execute the full sQL statementdb.execSQL(SQL_CREATE_ENTRIES); }}Copy the codeThe preceding SQL syntax is different from the SQL syntax of other databases. Note the following:
-
1.SQL statements are case-insensitive. Keywords such as CREATE and table, table names, and field names are case-insensitive. The only case sensitive values are string values enclosed in single quotes.
-
2. To avoid duplicate TABLE creation, add the IFNOT EXISTS keyword, for example, CREATE TABLE IFNOT EXISTS TABLE name……
-
3.SQLite supports INTEGER, LONG, string VARCHAR, FLOAT, but does not support Boolean. Boolean data is stored as an integer. If you save Boolean data directly, sQLite automatically converts it to 0 or 1 when you enter it, where 0 means false and 1 means true.
-
4. Field _ID must be uniquely identified during table creation. For example, _ID lNTEGER PRIMARY KEY AUTOINCREMENT NOT NULL.
2.3 delete table
The drop command is used to delete a TABLE. The format is drop TABLE IFEXISTS TABLE name. . The following is an example SQL statement to drop the user information table:
/ / delete table
public void deleteTable(SQLiteDatabase db) {
String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(SQL_DELETE_ENTRIES);
}
Copy the code2.4 Opening database tables
The following code returns an instance of the SQLiteDatabase class, with which you can query or modify the database.
// Open a read connection to the database
public SQLiteDatabase openReadLink(a) {
if (mDB == null| |! mDB.isOpen()) { mDB = mHelper.getReadableDatabase(); }return mDB;
}
// Open a write connection to the database (writable database)
public SQLiteDatabase openwriteLink(a) {
if (mDB == null| |! mDB.isOpen()) { mDB = mHelper.getWritableDatabase(); }return mDB;
}
Copy the code2.5 Shutting down the Database
Because of the high cost of calling getWritableDatabase() and getReadableDatabase() when the database is down, keep the database connection open as long as you may need to access the database. In general, it is best to close the database in onDestroy() of the calling Activity.
@Override
protected void onDestroy(a) {
userDBHelper.closeLink();
super.onDestroy();
}
Copy the code2.6 Data addition, deletion, modification and check
2.6.1 Adding Data
Use insert() of the SQLiteDatabase object.
// Add multiple records to the table
public long insert(List<UserInfoBean> infoList) {
long result = -1;
for (int i = 0; i < infoList.size(); i++) {
UserInfoBean info = infoList.get(i);
// If there is no unique duplicate record, insert new record
ContentValues cv = new ContentValues();
cv.put(_ID, info.get_id());
cv.put(COLUMN_NAME, info.getName());
cv.put(COLUMN_AGE, info.getAge());
cv.put(COLUMN_UPDATE_TIME, info.getUpdate_time());
// Perform the insert record action, which returns the line number of the insert record
result = mDB.insert(TABLE_NAME, "", cv);
if (result == -1) {// The line number is returned on success, and -1 is returned on failure
returnresult; }}return result;
}
Copy the codeCall method to add data:
List<UserInfoBean> list = new ArrayList<>();
list.add(new UserInfoBean("10"."Handsome every time.".20."GMT"));
list.add(new UserInfoBean("12"."Zhu Yuanzhang".30."11"));
list.add(new UserInfoBean("15"."Zhao Kuangyin".40."13:00"));
list.add(new UserInfoBean("18"."Li Shimin".50."15:00"));
long l = userDBHelper.insert(list);
sqliteBinding.tvSql.setText("Insert successful:" + l);
Copy the code
2.6.2 Modifying Data
Use the update() method of the SQLiteDatabase object.
// Update the specified table records according to the criteria
public int update(UserInfoBean info, String condition) {
ContentValues cv = new ContentValues();
cv.put(COLUMN_NAME, info.getName());
cv.put(COLUMN_AGE, info.getAge());
cv.put(COLUMN_UPDATE_TIME, info.getUpdate_time());
// Executes the update record action, which returns the number of updated records
return mDB.update(TABLE_NAME, cv, condition, null);
}
Copy the codeCalling methods to modify data:
UserInfoBean bean = new UserInfoBean("The Empress of China".32."Twelve");
userDBHelper.update(bean, "_id=18");
Copy the code
2.6.3 Searching for Data
Use the query() method of the SQLiteDatabase object.
// Query records based on the specified criteria and return a list of results
public List<UserInfoBean> query(a) {
List<UserInfoBean> infoList = new ArrayList<UserInfoBean>();
// Executes the record query action, which returns a cursor for the result set
//SELECT the contents of the statement as arguments to the query() method, such as the name of the table to be queried, the name of the field to be retrieved, the WHERE condition,
// Contains optional positional arguments to replace positional values in the WHERE condition, GROUP BY condition, and HAVING condition.
// All arguments except the table name can be null
Cursor cursor = mDB.query(TABLE_NAME, null.null.null.null.null.null);
// Loop over each record the cursor points to
while (cursor.moveToNext()) {
UserInfoBean info = new UserInfoBean();
info._id = cursor.getString(0);// Fetch the string
info.name = cursor.getString(1);// Fetch the string
info.age = cursor.getInt(2);// Retrieve the integer number
info.update_time = cursor.getString(3);// Fetch the string
infoList.add(info);
}
cursor.close();
// Close the database cursor
return infoList;
}
Copy the codeCall method to find data:
StringBuilder sql = new StringBuilder();
List<UserInfoBean> list = userDBHelper.query();
sql.append(list.size());
Copy the code
2.6.4 Deleting Data
Use the delete() method of the SQLiteDatabase object.
// Delete table records based on specified criteria
public int delete(String condition) {
Log.e(getClass().getName(), "delete：" + condition);
// Perform the delete record action, which returns the number of deleted records
return mDB.delete(TABLE_NAME, condition, null);
}
Copy the codeCall method to delete data:
userDBHelper.delete("_id=12");
Copy the code
2.6.6 delete table
/ / delete table
public void deleteTable(SQLiteDatabase db) {
String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TABLE_NAME;
db.execSQL(SQL_DELETE_ENTRIES);
}
Copy the codeCall method to drop table:
userDBHelper.deleteTable(db));
Copy the codeNote: the table is deleted here, not the database, so you are in
2.6.6 Operation table is incorrectly reported
Do not operate on a table before it is created or deleted; otherwise, an error will be reported
1. Failed to add data (Error) The program reported an Error
E/SQLiteLog: (1) no such table: user_info in "INSERT INTO user_info(update_time,_id,age,name) VALUES (? ,? ,? ,?) "
E/SQLiteDatabase: Error inserting update_time=09:00 _id=10 age=20Name = handsome every time android. Database. Sqlite. SQLiteException: no to the table: user_info (code1 SQLITE_ERROR): , while compiling: INSERT INTO user_info(update_time,_id,age,name) VALUES (? ,? ,? ,?)
Copy the codeAndroidRuntime crashes
E/SQLiteLog: (1) no such table: user_info in "SELECT * FROM user_info"
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.scc.datastorage, PID: 15093
android.database.sqlite.SQLiteException: no such table: user_info (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM user_info
Copy the code3. Description of parameters
-
Table: indicates the table name
-
Colums: Array of column names
-
Selection: conditional clause, equivalent to where
-
SelectionArgs: An array of parameters for conditional statements
-
GroupBy: grouping
-
Having: Group condition
-
OrderBy: Sorting class
-
Limit: indicates the limit of paging queries
-
Cursor: Returns a value equivalent to a ResultSet
Using the cursor
No matter how you perform the query, a Cursor is returned. This is the Android SQLite database Cursor. Using the Cursor, you can:
-
Get how many records are in the result set with getCount();
-
Traverse all records with moveToFirst(), moveToNext(), and isAfterLast();
-
GetColumnNames () : retrieves the field name;
-
With the getColumnIndex() method: convert to a field number;
-
GetString (), getInt() and other methods: to get the current record value of a given field;
-
With requery() : re-execute the query to get the cursor;
-
Close () : release cursor resources;
4. Related links
Android data processing scheme
Android Data Store (1)- File storage
Android Data Storage (2)-Preferences or MMKV