【英文】Android数据存储SQLite

Posted on Edited on In Views:

Preface

Android Data Storage SQLite Learning Notes

Notes

  • SQLite supports storing NULL, INTEGER, REAL, TEXT, and BLOB data, but they will all be converted to the TEXT string type, so you can directly store any data you want as TEXT.
  • SQLite requires the primary key to be _id (although it can also be id, but it is not standardized), and the primary key can only use the INTEGER type.

Location of Database File

  • /data/data/com.xxx.app/database/xxDB.db

Customize a Class that Inherits the SQLiteOpenHelper Class

  • Using the singleton pattern

<name>DB.db: Name of the database file
1: Version number, starting from 1. When upgrading the database in the future, increase the version number by 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private static SQLiteOpenHelper sqLiteOpenHelper;

    public static synchronized SQLiteOpenHelper getSqLiteOpenHelper(Context context) {
        if (sqLiteOpenHelper==null) {
            sqLiteOpenHelper = new MySQLiteOpenHelper(context, "<name>DB.db", null, 1);
        }
        return sqLiteOpenHelper;
    }

    private MySQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    // Database initialization, this method will only be executed once, usually used to execute the create table statement
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // Create the table using SQL statement
        String sql = "CREATE TABLE users(_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)";
        sqLiteDatabase.execSQL(sql);
    }

    // Database upgrade
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

Create Database File

  • When creating the object for the first time, the database file will be created, and the data table will be created through the onCreate() method.
1
2
3
4
5
SQLiteOpenHelper sqLiteOpenHelper = MySQLiteOpenHelper.getSqLiteOpenHelper(this);

// The following statement is required to create the database file
//sqLiteOpenHelper.getReadableDatabase();
sqLiteOpenHelper.getWritableDatabase();

Query

<select>: SQL statement for querying

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQLiteOpenHelper sqLiteOpenHelper = MySQLiteOpenHelper.getSqLiteOpenHelper(this);
SQLiteDatabase db = sqLiteOpenHelper.getReadableDatabase();

if (db.isOpen()) {
    // Return the cursor
    Cursor cursor = db.rawQuery("<select>", null);

    // Iterate through the cursor
    while (cursor.moveToNext()) {
        // Get the value of the first column
        cursor.getInt(0);

        // Get the value of the specified column name
        cursor.getInt(cursor.getColumnIndex("_id"));
    }
    
    // Close the cursor
    cursor.close();
    // Close the database
    db.close();
}

Insert

<insert>: SQL statement for insertion

1
2
3
4
5
6
7
8
9
SQLiteOpenHelper sqLiteOpenHelper = MySQLiteOpenHelper.getSqLiteOpenHelper(this);
SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();

if (db.isOpen()) {
    db.execSQL("<insert>");
    
    // Close the database
    db.close();
}

Update

<update>: SQL statement for modification

1
2
3
4
5
6
7
8
9
SQLiteOpenHelper sqLiteOpenHelper = MySQLiteOpenHelper.getSqLiteOpenHelper(this);
SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();

if (db.isOpen()) {
    db.execSQL("<update>");
    
    // Close the database
    db.close();
}

Delete

<delete>: SQL statement for deletion

1
2
3
4
5
6
7
8
9
SQLiteOpenHelper sqLiteOpenHelper = MySQLiteOpenHelper.getSqLiteOpenHelper(this);
SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();

if (db.isOpen()) {
    db.execSQL("<delete>");
    
    // Close the database
    db.close();
}

Use placeholders to add parameters dynamically

1
db.execSQL("UPDATE table_name SET column_name=? WHERE _id=?", new Object[]{"field_value", 0});

Done

References

Bilibili—Android Architecture Analysis