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

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

2.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 code

2.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 code

2.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 code

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

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

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

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

Call method to drop table:

    userDBHelper.deleteTable(db));
Copy the code

Note: 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 code

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

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

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Android Data Store (3)-SQLite database instance

Introduce a,

1.1 the SQLite

1.2 Basic SQL Syntax

1.3 Database Helper SQLiteOpenHelper

Use SQLite

2.1 Inherit SQLiteOpenHelper class and rewrite onCreate, onUpgrade and onOpen methods

2.2 create a table

2.3 delete table

2.4 Opening database tables

2.5 Shutting down the Database

2.6 Data addition, deletion, modification and check

2.6.1 Adding Data

2.6.2 Modifying Data

2.6.3 Searching for Data

2.6.4 Deleting Data

2.6.6 delete table

2.6.6 Operation table is incorrectly reported

3. Description of parameters

4. Related links

Android Data Store (3)-SQLite database instance

Introduce a,

1.1 the SQLite

1.2 Basic SQL Syntax

1.3 Database Helper SQLiteOpenHelper

Use SQLite

2.1 Inherit SQLiteOpenHelper class and rewrite onCreate, onUpgrade and onOpen methods

2.2 create a table

2.3 delete table

2.4 Opening database tables

2.5 Shutting down the Database

2.6 Data addition, deletion, modification and check

2.6.1 Adding Data

2.6.2 Modifying Data

2.6.3 Searching for Data

2.6.4 Deleting Data

2.6.6 delete table

2.6.6 Operation table is incorrectly reported

3. Description of parameters

4. Related links

Related Posts

RecyclerView how to achieve the scrolling? (2) | Fling

Four ways for Android to view the project dependency tree

Android Camera-CameraView and CameraX