Introduction to SQLite database

1. Lightweight database integrated in Android system

2. Characteristics:

3. The program can be accessed by the name of the database, other applications can not access it.

Path: data/data/application package name/database/**.db

5. Types of storage:

NULL Null value
INTEGER integer(No need int)
VARCHAR Variable Length Character Data
TEXT Text string
BOOLEAN Boer
DATE

Database usage and two core classes

First, SQLiteOpenHelper

Help class for database, "For database creation and version updating"
How to use it:

(1)Define a class, inherit SQLiteOpenHelper
(2)Rewrite Construction Method :Provide basic information about the database : Context object,Database name,Null,Version number of database
(3)Two methods of overwriting parent classes:
onCreate(): onUpgrade()

public class MySqlHelper extends SQLiteOpenHelper {
    /**
     * structure
     * @param context context
     * @param name  Database name
     * @param factory  Create factory objects for data
     * @param version Data Version Number
     */
    public MySqlHelper(Context context,  String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    /**
     * Note: The onCreate method only executes once if you want to modify the fields of the table.
     * Modifying the oncreate method is not feasible. You need to delete the previously generated database.
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table user(_id integer primary key autoincrement,name varchar(20))");
    }

    /**
     * Update data -- if the version number of the database changes, execute this method -- execute multiple times
     * @param db
     * @param oldVersion  Old version number
     * @param newVersion  New version number
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

II. SQLiteDatabase

Operating class for database, "Operating database: executing sql statement / add / delete / change / check"
Getting the SQLiteDatabase object through SQLiteOpenHelper

package com.example.day009.util;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.example.day009.MySqlHelper;

/**
 * Method of adding, deleting and changing encapsulation
 */
public class UserDao {
    private SQLiteDatabase db;
    //Create a db by constructing;
    public UserDao(Context context){
        MySqlHelper mySqlHelper = new MySqlHelper(context,"user.db",null,1);
        //Readable and writable. The disk is full and trying to open. (Recommended)
        db = mySqlHelper.getReadableDatabase();
        //Readable and Writable. Disk full, open error.
//        db = mySqlHelper.getWritableDatabase();
    }
}

The database can be created by executing commands in Activity:

 UserDao userDao = new UserDao(this);

** The values in the operational data are divided into the following two categories. **

Add an insertion method to UserDao.

public void insert(User user){
        //Method 1, the form of placeholder
        String sql = "insert into user(name,age,address) values(?,?,?)";
//        db.execSQL(sql,new Object[]{user.getName(),user.getAge(),user.getAddress()});
        //Method 2. insert method of system
        ContentValues contentValues = new ContentValues();
        contentValues.put("name",user.getName());
        contentValues.put("age",user.getAge());
        contentValues.put("address",user.getAddress());
        db.insert("user",null,contentValues);
    }

Add an update() method to UserDao.

// Note that the parameter is not user, but with new fields and field values
 public void update(String age , int id){
        //Method 1
        String sql = "update user set age = ? where _id=?";
//        db.execSQL(sql,new Object[]{user.getAge(),user.get_id()});
        //Mode 2
        ContentValues contentValues = new ContentValues();
        contentValues.put("age",age);
        db.update("user",contentValues,"_id = ?",new String[]{id+""});
    }

Add a delete() method to UserDao.

    //Delete data
    public int delete(String id) {
        //Mode 1:
        String sql="delete from user where _id = ? ";
//        db.execSQL(sql,new String[]{id});
        //Mode 2:
        int num = db.delete("user", "_id=?", new String[]{id});
        return num;
    }

Add a query() method to UserDao.

  //Query a set of data
    public List<User> query() {
        ArrayList<User> users = new ArrayList<>();
        Cursor user = db.query("user", null, null, null, null, null, null);
        while(user.moveToNext()){
            //1. cursor. getColumnIndex ("Name") gets the number of the column in which it is located by column name
            //2, cursor. getString (column number); get the column data by column number
            //Check whether the ID is used or not, and the fixed format is the best format _id (there is an adapter mandatory requirement)
            String id = user.getString(user.getColumnIndex("_id"));
            String address = user.getString(user.getColumnIndex("address"));
            String name = user.getString(user.getColumnIndex("name"));
            String age = user.getString(user.getColumnIndex("age"));
            User user1 = new User(Integer.parseInt(id), name, Integer.parseInt(age), address);
            users.add(user1);
        }
        return users;
    }

Complete code in Activity:

package com.example.day009;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.example.day009.entity.User;
import com.example.day009.util.UserDao;

import java.util.List;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    private Button insertId;
    private Button selectId;
    private Button updateId;
    private Button delId;
    private UserDao userDao;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        insertId = findViewById(R.id.insert_id);
        selectId = findViewById(R.id.select_id);
        updateId = findViewById(R.id.update_id);
        delId = findViewById(R.id.del_id);


        insertId.setOnClickListener(this);
        selectId.setOnClickListener(this);
        updateId.setOnClickListener(this);
        delId.setOnClickListener(this);

        userDao = new UserDao(this);

    }

    @Override
    public void onClick(View v) {

        int id = v.getId();
        switch (id) {
            case R.id.insert_id:
                User user = new User();
                user.setAge(17);
                user.setName("Feng");
                user.setAddress("Beijing");
                userDao.insert(user);
                break;

            case R.id.update_id:
                userDao.update("20",1);
                break;

            case R.id.del_id:
                int delete = userDao.delete("2");
                Toast.makeText(this, delete+"", Toast.LENGTH_SHORT).show();
                break;
            case R.id.select_id:
                List<User> query = userDao.query();
                Toast.makeText(this, query.get(0).getName(), Toast.LENGTH_SHORT).show();
                break;
            default:
                break;
        }
    }
}

Content of xml file

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:orientation="vertical"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/insert_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Insert a data"/>

    <Button
        android:id="@+id/select_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Query a data"/>

    <Button
        android:id="@+id/update_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="To update"/>

    <Button
        android:id="@+id/del_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="delete"/>
</LinearLayout>

Completion code in userDao

package com.example.day009.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.View;

import com.example.day009.MySqlHelper;
import com.example.day009.entity.User;

import java.util.ArrayList;
import java.util.List;

/**
 * Method of adding, deleting and changing encapsulation
 */
public class UserDao {
    private SQLiteDatabase db;
    //Create a db by constructing;
    public UserDao(Context context){
        MySqlHelper mySqlHelper = new MySqlHelper(context,"user.db",null,3);
        //Readable and writable. The disk is full and trying to open. (Recommended)
        db = mySqlHelper.getReadableDatabase();
        //Readable and Writable. Disk full, open error.
//        SQLiteDatabase writableDatabase = mySqlHelper.getWritableDatabase();
    }

    public void insert(User user){
        //Method 1, the form of placeholder
        String sql = "insert into user(name,age,address) values(?,?,?)";
//        db.execSQL(sql,new Object[]{user.getName(),user.getAge(),user.getAddress()});
        //Method 2. insert method of system
        ContentValues contentValues = new ContentValues();
        contentValues.put("name",user.getName());
        contentValues.put("age",user.getAge());
        contentValues.put("address",user.getAddress());
        db.insert("user",null,contentValues);
    }

    public void update(String age , int id){
        //Method 1
        String sql = "update user set age = ? where _id=?";
//        db.execSQL(sql,new Object[]{user.getAge(),user.get_id()});
        //Mode 2
        ContentValues contentValues = new ContentValues();
        contentValues.put("age",age);
        db.update("user",contentValues,"_id = ?",new String[]{id+""});
    }

    //Delete data
    public int delete(String id) {
        //Mode 1:
        String sql="delete from user where _id = ? ";
//        db.execSQL(sql,new String[]{id});
        //Mode 2:
        int num = db.delete("user", "_id=?", new String[]{id});
        return num;
    }

    //Query a set of data
    public List<User> query() {
        ArrayList<User> users = new ArrayList<>();
          //Mode 1
        String sql = "select * from user where _id = ?";
        Cursor user = db.rawQuery(sql, new String[]{"50"});
        //Mode 2
        Cursor user = db.query("user", null, null, null, null, null, null);
        while(user.moveToNext()){
            //1. cursor. getColumnIndex ("Name") gets the number of the column in which it is located by column name
            //2, cursor. getString (column number); get the column data by column number
            //Check whether the ID is used or not, and the fixed format is the best format _id (there is an adapter mandatory requirement)
            String id = user.getString(user.getColumnIndex("_id"));
            String address = user.getString(user.getColumnIndex("address"));
            String name = user.getString(user.getColumnIndex("name"));
            String age = user.getString(user.getColumnIndex("age"));
            User user1 = new User(Integer.parseInt(id), name, Integer.parseInt(age), address);
            users.add(user1);
        }
        return users;
    }

}

The content of user of entity class

package com.example.day009.entity;


public class User {
    private int _id;
    private String name;
    private int age;
    private String address;

    public User() {
    }

    public User(int _id, String name, int age, String address) {
        this._id = _id;
        this.name = name;
        this.age = age;
        this.address = address;
    }

    public int get_id() {
        return _id;
    }

    public void set_id(int _id) {
        this._id = _id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

Batch processing of data using transactions

1. What is a transaction?
It is to treat many things as one thing. That is to say, everybody is on the same boat, to live together, to over ride together!
In the development of Android applications, database operations are often needed, and database transaction processing is very important to improve the stability and efficiency of Android applications.

2. Benefits of business?
1. First, Android database operations, especially write operations, are very slow. Packing all operations into one transaction can greatly improve processing speed.
2. Ensure data consistency, so that all operations in a transaction are successfully executed, or fail, or all operations are rolled back.
for example, when an application is initialized, it needs to insert a large amount of data in batches, and the application response is slow because the default statement when sqlite inserts data is a transaction and there are as many disk operations as there are data. My application started with 5000 records, which meant 5000 read-write disk operations.
And there is no guarantee that all data can be inserted at the same time. (It is possible that part of the insert will succeed and the other part will fail, and it will have to be deleted later. Too much trouble)
3. Standard code

	SQLiteDatabase db = myHepler.getWritableDatabase();
	db.beginTransaction();//Open a transaction
	try {
		for (int i=0;i<1000;i++) {
		db.execSQL("insert into Table name (Field name) values(Value)");
	}
		db.setTransactionSuccessful();//Success
	}catch (Exception e){
		e.printStackTrace();
	}finally {
		db.endTransaction();//Closing the transaction
		db.close();//Close
	}

Query Extension (A Case of Query)

Meaning of the parameters of query(table,columns, selection, selectionArgs, groupBy, having, orderBy, limit) method: 
Table: table name. It corresponds to the following part of the select statement from keyword. If multiple tables are jointly queried, two table names can be separated by commas. 
columns: The column name to query. Equivalent to the section after the select keyword in the select statement. 
Select: Query the conditional clause, which corresponds to the part after where keyword in the select clause. The placeholder "?" is allowed in the conditional clause. 
Selection Args: Corresponding to the placeholder value in the selection statement, the position of the value in the array must be the same as that of the placeholder in the statement, otherwise there will be an exception. 
groupBy: Equivalent to the section after the group by keyword in the select statement 
Have: Equivalent to the following part of the select ion statement having keyword 
Order By: The equivalent of the following part of the order by keyword in the select statement, such as: personid desc, age asc; 
Limit: Specifies the offset and the number of records retrieved, which corresponds to the section following the limit keyword in the select statement.


For example, query the picture database and reverse it in order of editing time:

String columns[] = new String[] { Media._ID, Media.BUCKET_ID,
Media.PICASA_ID, Media.DATA, Media.DISPLAY_NAME, Media.TITLE,Media.SIZE, Media.BUCKET_DISPLAY_NAME };
Cursor cur = cr.query(Media.EXTERNAL_CONTENT_URI, columns, null, null,
Media.DATE_MODIFIED+desc;//In reverse chronological order