Preface

In Android development process, for data storage, we will more or less use database-related operations, so in this little summary, Android using SQLite database skills and methods, is their own review of database knowledge. Project source code
This blog is published synchronously XueLong's blog

crud

adb shell
su  //Running commands as administrators
sqlite DATABASE_NAME.db //Enter the database to be operated on
.table  //See which tables are in the current database
.table t% //The ".tables" command can also be followed by a parameter, which is a pattern, so that the command lists only the table name that matches the parameter.
.schema //Display SQL statements originally used to create databases, CREATE TABLE and CRATE INDEX
.schema t% //The ".schema" command can contain a parameter, which is a pattern used to filter tables, which will only show qualified tables and all index SQL statements.
pragma table_info(TABLE_NAME);  //View the data structure of the table
.mode line  //Switch display mode, available parameters are line list column
.separator |   //Use | spacer between fields
.output filename.txt  //Export data from database to file
.width 12 6 //Adjusting column width 12 means the first column width is 12, and 6 means the second column width is 6.
.databases //Displays a list of all currently connected open databases, main: originally opened databases, temp: temporary tables databases
.exit //Sign out

• stay SQLite EXpert Personal Annotate SQL statements in -, single-line annotations in -, multi-line annotations in /**/
• Each SQLite database has a hidden sqlite_master table, which records all the table-building statements in the current database.
• Before checking the database for additions, deletions and modifications, we need to call the getReadableDatabase() (call in time) or getWritableDatabase() (call in time for additions and deletions) method of SQLiteOpenHelper first.

Regular SQL Statement Operation

SQLiteDatabase db = null;
//Use when executing add-delete statements
db = helper.getWritableDatabase();
//Use when executing query statements
//db = helper.getReadableDatabase();
db.execSQL("Fill in your SQL Sentence");

//TABLE statement
create table if not exists TABLE_NAME(Id integer orimary key,Name text,Age integer);
//Add a data
insert into TABLE_NAME(Id,Name,Age) values (1,'lixuelong',23);
//Delete a piece of data
delete from TABLE_NAME where Name = 'lixuelong';
//Modify a piece of data
update TABLE_NAME set Name = 'xuelong' where Id =1;
//Query statement
select * from TABLE_NAME where Name = 'lixuelong' order by Age desc;
//Statistical Query Statement
select count(Id) from TABLE_NAME where Age = 18;
//Compare Query Statements
select Id,Name,Max(Age) as Age from TABLE_NAME;

Android's own Transaction Processing Method

• Table: table name
• nullColumnHack: Used to specify the name of a null field
• values: The ContentValues object to store can be null
• Return value: Returns the line number of the newly added record, independent of the primary key id, and returns - 1 with an error

SQLiteDatabase db = null;
db = helper.getWritableDatabase();
db.beginTransaction();
// Insert into TABLE_NAME(_id, Name, Age) values (6,'Zhang San', 18);
ContentValues contentValues = new ContentValues();
contentValues.put("_id","6");
contentValues.put("Name", "Zhang San");
contentValues.put("Age", 18);
db.insertOrThrow(TABLE_NAME, null, contentValues);
db.setTransactionSuccessful();

• Table: table name
• whereClause: The deletion condition, if null, deletes the entire line
• WhereArgs: There are two ways to use string arrays in conjunction with whereClause. 1. WhereArgs can be set to null if the condition of whereClause has been given directly, such as "Name='Zhang San'". 2. If the conditions of where Clause are not given directly, such as "Name=?", then? It will be replaced by the values in the whereArgs string array.
• Return value: 0: deleted is by, 1: deleted successfully

SQLiteDatabase db = null;
db = helper.getWritableDatabase();
db.beginTransaction();
db.delete(TABLE_NAME, "Name=?", new String[]{"Zhang San"});
//db.execSQL("delete from TABLE_NAME where Name = Zhangsan';");
db.setTransactionSuccessful();

• Table: table name
• values: The ContentValues object to store can be null
• whereClause: Modified condition, if null, then whole line modification
• WhereArgs: There are two ways to use string arrays in conjunction with whereClause. 1. WhereArgs can be set to null if the condition of whereClause has been given directly, such as "Name='Zhang San'". 2. If the conditions of where Clause are not given directly, such as "Name=?", then? It will be replaced by the values in the whereArgs string array.
• Return value: the number of rows affected

SQLiteDatabase db = null;
db = helper.getWritableDatabase();
db.beginTransaction();
// Update TABLE_NAME set Name ='zhangsan'where Name ='Zhangsan'
ContentValues contentValues = new ContentValues();
contentValues.put("Name", "zhangsan");
db.update(TABLE_NAME, contentValues, "Name = ?", new String[]{"Zhang San"});
db.setTransactionSuccessful();

• Table: table name
• Columns: List of columns to be returned, if null, all columns to be returned
• Select: Query conditions, what qualified rows will be returned. If null, all rows in this table will be returned. The two uses are the same as in update.
• Selection Args: The same as in update
• groupBy: Used to control grouping
• Have: Used for filtering groupings
• orderBy: Used to sort records
• Return value: Cursor object

SQLiteDatabase db = null;
Cursor cursor = null;
db = helper.getWritableDatabase();
// select * from TABLE_NAME where Age = 18
cursor = db.query(TABLE_NAME, TABLE_COLUMNS, "Age = ?", new String[]{String.valueOf(18)},null, null, null);
if (cursor.getCount() > 0) {
   List<DBSQLBean> beanList = new ArrayList<DBSQLBean>();
   while (cursor.moveToNext()) {
        DBSQLBean bean = parseBean(cursor);
        beanList.add(bean);
    }
    return beanList;
}

int count = 0;
SQLiteDatabase db = null;
Cursor cursor = null;
db = helper.getWritableDatabase();
// select count(_id) from TABLE_NAME where Age = 18
cursor = db.query(DBSQLHelper.TABLE_NAME, new String[]{"COUNT(_id)"},"Age = ?", new String[]{String.valueOf(18)},null, null, null);
if (cursor.moveToFirst()) {
    count = cursor.getInt(0);
}

SQLiteDatabase db = null;
Cursor cursor = null;
db = helper.getWritableDatabase();
// select _id,Name,Max(Age) as Age from TABLE_NAME
cursor = db.query(DBSQLHelper.TABLE_NAME, new String[]{"_id", "Name","Max(Age) as Age"},null, null, null, null, null);
if (cursor.getCount() > 0) {
    if (cursor.moveToFirst()) {
        return parseBean(cursor);
    }
}

Written in the end

This is a brief summary of database operations in Android.

If you encounter problems in the reference process, you can ask me questions in my contact form.

Later, I will continue to introduce Android related knowledge, welcome to continue to pay attention to the update of my blog.

Project source code

Reference resources

• Android SQLite Explanation
• Parameter List of Several Important Methods for android Notebook SQLiteDatabase

Reprinted please indicate: XueLong's blog » Summary of the use of SQLite in Android database