Application data storage database of Android system programming introduction series

The previous article has introduced how to use SharedPreferences to store lightweight data in the form of key value pairs. For those groups of data with the same structure, it is similar to storing multiple object attribute values of classes defined in Java. If you read and write one by one in the form of key value pairs, you need to define the key values corresponding to each data respectively, which is quite cumbersome. If you can use the database to save, it will be much more convenient.

Therefore, the Android system provides support for SQLite database. The database created in the application is also saved in the internal storage space of the application by default, so that only the current application can access the data in the database.

In the Android system, you can use the android.database.sqlite.sqlitedatabase database class to directly operate the SQLite database. At the same time, with the help of android.database.sqlite.sqliteopenhelper database help class, you can get the database classes here.

Generally, the class structure to be saved is consistent with the database structure. Taking student information as an example, the student class created below can be directly used as the database structure class. Just define the field names in the database according to the attribute names in the student class.

public final class Student {
    private String name;
    private String birthday;
    private int level;
    private Student() {}

    public static final String TABLE_NAME = "student";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_BIRTHDAY = "birthday";
    public static final String COLUMN_LEVEL = "level";
}

Corresponding to the structure containing data tables in the database, a subclass inherited from sqliteopenhelper is defined here to handle the relationship between data tables, and oncreate (sqlitedatabase dB) and onupgrade (sqlitedatabase dB, int OldVersion, int newversion) methods are implemented in the self-defined subclass. Generally, the construction method of this class inherits from its parent class sqliteopenhelper (context context, string name, sqlitedatabase.cursorfactory factory factory, int version). The parameter context is the context in which the database is used; Parameter name is the name of the database file; The parameter factory is the cursor factory used to access the database. Usually, null can be passed in; The parameter version is the version number of the data table. The oncreate (sqlitedatabase dB) method implemented by this class will be called when the database file is created for the first time after the object is created. Therefore, the data table creation operation can be performed in this method. The parameter DB is the current database object, and the relevant methods of this object can be called to operate the database. The onupdate (sqlitedatabase dB, int newversion) method will be called when the database file exists but the database version is upgraded after the class object is created. Therefore, the data table can be updated in this method. The parameter DB is the current database object, and the database can be operated by calling the relevant methods of the object; The parameter OldVersion is the old version number before the database version upgrade; The parameter newversion is the new version number after the database version upgrade.

Take the database corresponding to the student class created above as an example, and the example code is as follows. When the studentdbhelper object is created for the first time, its corresponding database version number is 10, and the database file needs to be created for the first time. Therefore, the oncreate() method of the object will be executed to execute SQL in the database_ CREATE_ The SQL statement defined by student creates a data table that does not contain the birthday field. If you want to add the birthday field when you need to update the data table in the future, you only need to change the corresponding database version number to 20 when creating the studentdbhelper object, and judge the version number in the onupdate () method. Once it is judged that the conditions are met, you can execute SQL_ ADD_ SQL statement defined by birthday.

public class StudentDbHelper extends sqliteOpenHelper {
    public static final int DATABASE_VERSION_FIRST = 10;
    public static final int DATABASE_VERSION_SECONDDATABASE_VERSION_SECOND = 20;

    public static final String DATABASE_NAME = "students.db";

    private static final String sql_CREATE_STUDENT =
        "CREATE TABLE " + Student.TABLE_NAME + " (" +
        Student.COLUMN_ID + " INTEGER PRIMARY KEY," +
        Student.COLUMN_NAME + " TEXT," +
        Student.COLUMN_LEVEL + " TEXT)";

    private static final String sql_ADD_BIRTHDAY =
        "ALTER TABLE " + Student.TABLE_NAME +
        " ADD COLUMN " + Student.COLUMN_BIRTHDAY + " TEXT";

    public StudentDbHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION_FIRST);
        //super(context,DATABASE_VERSION_SECOND);
    }

    public void onCreate(sqliteDatabase db) {
        db.execsql(sql_CREATE_STUDENT);
    }

    public void onUpgrade(sqliteDatabase db,int newVersion) {
        if(newVersion==DATABASE_VERSION_SECOND){
            db.execsql(sql_DELETE_BIRTHDAY);
        }
    }

}

The sqlitedatabase class has been understood in the sqlitedbhelper subclass above. In addition, you can also obtain the read-write database and getreadabledatabase () methods of the sqlitedbhelper object where you need to obtain the database object.

After obtaining the sqlitedatabase database object, you can add, delete, modify and query the database through its related methods.

Call the insert (string table, string nullcolumnhack, contentvalues) series methods of the object to insert a piece of data into the database. The result of long type returned represents the ID sequence of the inserted data in the data table. If the insertion fails, it returns - 1. Where the parameter table is the name of the data table to be inserted; The parameter nullcolumnhack can specify the field name to be inserted. It is usually ignored when it is null, and the field data contained in the subsequent parameters is used; The parameter values specifies the data to be inserted. Similarly, the data is accessed in the form of key value pairs.

Call the delete (string table, string whereclause, string [] whereargs) method of the object to delete the specified data in the database. The result of type int returned indicates the number of data pieces deleted. The parameter table is also the name of the data table to be deleted; The parameter whereclause specifies the deletion condition, which conforms to the SQL statement, but the variable parameter is available? Instead, specify the specific parameter value in the subsequent parameter; The parameter whereargs is the parameter value array, and its length is the same as that in the parameter whereclause? Consistent with the quantity. If you delete all contents in a data table, just set parameter 2 and parameter 3 to null.

Call the update (string table, contentvalues, string [] whereargs) method of the object to update the specified data in the database. The result of type int returned represents the number of data pieces updated. The parameter table is also the name of the data table to be updated; The parameter values specifies the field data to be updated; The parameter whereclause can specify the update condition; The parameter whereargs corresponds to the parameter value in the specified update condition. Here, if both parameter 3 and parameter 4 are null, all data entries in the data table will be updated.

Call the query (string table, string [] columns, string selection, string [] selectionargs, string groupby, string having, string orderby, string limit) series methods of the object to query the specified data in the database. Returns a cursor object of android.database.cursor type, which can temporarily store multiple results. The parameter table is the name of the data table to be queried; The parameter columns is the fields contained in the returned result. If it is null, all fields will be returned; The parameter selection is the query condition, which also conforms to the SQL statement, but the variable parameter uses? Replace; The parameter selectionargs corresponds to an array composed of the values of variable parameters in the query criteria; The parameter groupby is consistent with the group by in the SQL statement. You can specify a field as the grouping basis in the returned data. If it is null, it will not be grouped; The parameter having is also the same as having in the SQL statement. It specifies whether the returned data contains a field. If it is null, it contains all data; The parameter orderby is also consistent with the order by in the SQL statement. You can specify to sort according to a field. If it is null, it will not be sorted; The parameter limit is consistent with the limit in the SQL statement and can be searched in pages.

The above four operation methods for adding, deleting, modifying and querying can be implemented using native SQL statements, so you can directly call the execsql (string SQL) method of sqlitedatabase object and pass in a defined SQL statement. This method has been used when creating the database above, and can support most SQL statements.

There is the concept of transaction in the database, that is, the linear execution of multiple addition, deletion, modification and query operations is regarded as a whole. Similarly, in Android, you can start a transaction by calling the begaintransaction() method of the sqlitedatabase object. After all transaction operations are completed, you can call the settransactionsuccessful() method to mark the completion of the current transaction operation. If you do not call this method, the current transaction will not be executed even if it is committed. Finally, call the endtransaction () method to end the current transaction, and judge to commit and execute the current transaction under the condition of calling the settransactionsuccessful () flag method above.

After all operations on the database are completed, generally before the end of the component life cycle where the sqlitedbhelper object is used, call the close() method of the sqlitedbhelper object to release the resource occupation of the application on the database.

In order to focus more on business code logic and simplify the switching process of database, a development level framework such as room came into being. Room framework is an official recommended framework. In addition, there are excellent frameworks provided by other developers or organizations, including but not limited to greendao, litepal, ormlite, etc. Due to the convenient use of the development level framework, although the performance has its own advantages and disadvantages, the use is similar, which will not be repeated here.

In simple development, the use of the database is only focused on the simple operation of adding, deleting, changing and querying. In particular, the query data will often be further filtered after taking out the results. If you can skillfully use the sub statement group by during the query operation, its efficiency will be faster. Here is a point that developers are easy to ignore. In short, the rational use of the database is very efficient for the storage of a large number of data with the same structure. At the same time, if you want to further improve the database performance, it is recommended to learn more about SQL statements.