It's been a long time, and we've prepared some new things for release today. When we started Android, we all know that there are SQlite database and ROOM database after Android x, and some lightweight data can be stored in the database; HarmonyOS also provides database operation and management.

Basic overview

Relational Database (RDB) is a database that manages data based on relational model. It creates a database based on relational model and stores data in the form of rows and columns.. Based on SQLite components, HarmonyOS Relational Database provides a complete set of management mechanism for local database, provides a series of add, delete, modify and query interfaces, and can also directly run the SQL statements entered by users to meet the needs of complex scenarios. The Relational Database provided by HarmonyOS has more perfect functions and higher query efficiency.

Configuration before creating a relational database

Before creating the database, we need to configure the database name, storage mode, log mode, synchronization mode, whether it is read-only mode and encrypt the database; Use StoreConfig Configure the internal class of builder() in builder; Through StoreConfig, we can also obtain the basic configuration information of a relational database, mainly including the following seven items:

• config.getName() get database name
• config.isReadOnly() sets whether the database is in read-only mode. The default is false
• config.getDatabaseFileType() sets the file type of the database. The default is NORMAL
• config.getJournalMode() sets the log mode of the database, which is null by default
• config.getStorageMode() sets the storage mode of the database. The default is disk
• config.getSyncMode() sets the synchronization mode of the database, which is null by default
• config.getEncryptKey() sets the database key, which is open and unencrypted by default

StoreConfig config = StoreConfig.newDefaultConfig("DemoSql.db");//Suffix db
System.out.println(config.getName());//Database name
System.out.println(config.isReadOnly());//Read only -- false by default
System.out.println(config.getDatabaseFileType());//Database -- default NORMAL
System.out.println(config.getJournalMode());//Log mode null
System.out.println(config.getStorageMode());//Storage MODE_DISK storage mode
System.out.println(config.getSyncMode());//Sync mode null
System.out.println(config.getEncryptKey());//The key null is open and unencrypted by default

Custom database configuration

After you know the seven properties of the basic settings of the database, you can set them through the internal method of Builder. First, you can view the code for creating the database through a custom database configuration item.

StoreConfig.Builder builder = new StoreConfig.Builder();
builder.setName("DemoSql.db");
builder.setReadOnly(false);
builder.setSyncMode(null);
builder.setJournalMode(StoreConfig.JournalMode.MODE_WAL);
builder.setStorageMode(StoreConfig.StorageMode.MODE_DISK);
builder.setDatabaseFileType(DatabaseFileType.NORMAL);
String key = "123456";
builder.setEncryptKey(key.getBytes());
StoreConfig config = builder.build();
RdbStore rdbStore = helper.getRdbStore(config, 1,callback , null);

The above configuration items can be used to define and configure the database. Here we explain what each of the following data items represents and what the corresponding parameters to be passed in.

builder.setName("DemoSql.db");

Set database name db as the file suffix.

builder.setReadOnly(false);

Set whether the database is read-only. After setting, the database can only be read and cannot be added or modified. Generally, set false.

builder.setSyncMode(null);

Set the database synchronization mode. The synchronization mode is generally used for multi application distributed development, multi node access to the database, and synchronize the data of one node to another node. The parameter type is the SyncMode enumeration under StoreConfig.

1. MODE_OFF turns off synchronization (default)
2. MODE_NORMAL normal synchronization
3. MODE_FULL full synchronization (copy everything to another node)
4. MODE_EXTRA synchronization, incremental synchronization (copy the new data to another node)

builder.setJournalMode(StoreConfig.JournalMode.MODE_WAL);

Set the database log mode. When the database is created, a log will be generated. This log will be generated and deleted along with the opening and closing of database operation transactions. The JournalMode enumeration under the parameter StoreConfig.

1. MODE_DELETE delete log sqlite is the default deletion mode. Generally, when a transaction is started, a rollback log will be created and a temporary file will be created. The newly added (modified) data will be placed in the temporary file. After the transaction is submitted, the data will be stored in the main file and the rollback log will be deleted
2. MODE_TRUNCATE intercept mode
3. MODE_ The rollback log is not deleted at the end of the persistent mode transaction. When adding (modifying) data, it will be overwritten in the header of the 0 index position of the rollback log. This can also achieve the effect of deleting the rollback log and reduce the disk overhead of repeatedly deleting disk files
4. MODE_ In memory mode, the log is written in the memory file instead of stored in the disk. Because there is no data available for recovery in the disk, if the transaction crashes or makes an error, the data in the memory will be lost; The advantage of memory is that it reads and writes faster than disk
5. MODE_ The wal mode can also be called the pre write log mode. When the database is connected for the first time, the wal file is created, and when the last database connection is closed, the wal file is deleted; If the last database connection is not closed normally, the wal file will be saved and the database will not be deleted until the next opening; Using wal mode will generate a shm shared memory file, which is used to provide a piece of shared memory for multiple wal modes to access the same database, which is also the default mode of Hongmeng database.
6. MODE_OFF turns off the log data directly written to the main file. There is no log, and the SQLite atom submission and rollback functions are static; It can only be checked and cannot be written, because submission is prohibited.

builder.setStorageMode(StoreConfig.StorageMode.MODE_DISK);

Set the storage location of database files, divided into disk and memory. The parameter type is StorageMode enumeration under storageconfig.

1. MODE_ The memory memory mode database is stored in memory and cannot be seen on disk. After the transaction crashes, the data is not renewable.
2. MODE_DISK disk mode (default) the database will be placed on the disk in file mode. As long as the database files on the disk are still there, the data can be regenerated. The default mode of Hongmeng database storage.

builder.setDatabaseFileType(DatabaseFileType.NORMAL);

Set the database file type. The parameter type is DatabaseFileType enumeration.

1. NORMAL normal database file (default)
2. BACKUP backup database files
3. CORRUPT database file is CORRUPT

builder.setEncryptKey(key.getBytes());

Setting the database key is equivalent to setting a password for the database. You need to use the same password to open the database. The parameter type is a byte data byte[] encryptKey.

The above operations can define the database configuration item.

Open encrypted relational database

When opening an encrypted relational database, ensure that the keys of setEncryptKey(key.getBytes()) are consistent, otherwise an exception of file is not a database will be thrown. The specific opening method is as follows:

private void openDB(){
   String key = "123456";
   StoreConfig.Builder builder = new StoreConfig.Builder()
                .setName("DemoSql.db")
                .setReadOnly(false)
                .setSyncMode(null)
                .setJournalMode(StoreConfig.JournalMode.MODE_WAL)
                .setStorageMode(StoreConfig.StorageMode.MODE_DISK)
                .setDatabaseFileType(DatabaseFileType.NORMAL)
                .setEncryptKey(key.getBytes());
   StoreConfig config = builder.build();
   DatabaseHelper helper = new DatabaseHelper(this);
   RdbStore rdbStore = helper.getRdbStore(config,1,openCallback,null);
}

It should be noted that this opencallback is a newly created RdbOpenCallback callback function, and the onCreate callback method does not write the operation of creating a data table.

private static final RdbOpenCallback openCallback = new RdbOpenCallback() {
    @Override
    public void onCreate(RdbStore rdbStore) {
       //The table already exists when the database is created, so you don't need to create it when you open it
    }

    @Override
    public void onUpgrade(RdbStore rdbStore, int i, int i1) {

    }
};

Addition, deletion, modification and query of relational database

Relational database is a local data operation mechanism based on SQLite. The encapsulation of SQLite data by HarmonyOS enables the addition, deletion, modification and query of relational database to directly call the interface to realize the function of addition, deletion, modification and query without writing SQL statements. It also supports the operation of native SQL statements.

The relational database provides the following interfaces:

• Creation and deletion of database
  
  builder() internal class to configure the database.
  onCreate() method, callback when the database is created.
  onUpgrade() method, the database upgrade is called back.
  The getRdbStore() method creates or opens the database according to the configuration.
  The deleteRdbStore() method deletes the specified database.

• Database encryption
  
  Add an encryption key to the database.

• Add, delete, modify and query
  newly added:
  to update:
  
  Delete:
  
  Query:
  

• Query of database predicate
  Relational database provides the predicate AbsRdbPredicates for setting database operation conditions, including two implementation subclasses RdbPredicates and RawRdbPredicates:
  Rdbpredictes: developers do not need to write complex SQL statements. They can automatically complete the splicing of SQL statements by calling condition related methods in this class, such as equalTo, noteequalto, groupBy, orderByAsc, beginsWith, etc., so that users can focus on business operations.
  Rawrdbpredictes: it can meet the scenarios of complex SQL statements, and supports developers to set the where condition clause and the whereArgs parameter themselves. The use of conditional interfaces such as equalTo is not supported.
  

• Query result set
  Relational database provides the result set returned by query. It points to a row of data in the query result for users to traverse and access the query result. The external API of ResultSet is shown in the following table.
  
  

• affair
  Relational database provides transaction mechanism to ensure the atomicity of user operation. When database operation is performed on a single piece of data, there is no need to start a transaction; When inserting a large amount of data, starting a transaction can ensure the accuracy of the data. If the midway operation fails, a rollback operation is performed.
  

• Transaction and result set observers
  Relational database provides transaction and result set observer capabilities. When the corresponding event is triggered, the observer will be notified.
  

• Database backup and recovery
  Users can save and back up the data of the current database, and recover the data when necessary.
  
  The values passed in by restore and backup methods are different, and the data backed up and restored are also different, mainly due to the difference between encryption and non encryption.

Create database

To create a database, first add the database configuration StoreConfig, and then obtain the database object through the getRdbStore method of the tool class DatabaseHelper to create it.

• Add database configuration (only the database name is specified here, and others are the default)

//First, prepare the database configuration information. Here, only the database name is configured, and others are the default values
StoreConfig config = StoreConfig.newDefaultConfig("DemoSql.db");//Suffix db

• Get DatabaseHelper instance object

DatabaseHelper helper = new DatabaseHelper(this);

• Through helper The getrdbstore () method creates the database

RdbStore store = helper.getRdbStore(config, 1, callback, ResultSetHook);

The code for creating a database is very simple. Here you have created a database with default configuration; The specific parameters are explained as follows:

• config database configuration
• 1 database version number
• callback RdbOpenCallback callback method
• ResultSetHook result set Hook callback method

There are a series of methods for RdbOpenCallback callback. Here is a brief description:

private static final RdbOpenCallback callback =  new RdbOpenCallback() {
    @Override
    public void onCreate(RdbStore rdbStore) {
        //onCreate creates the method that the database will call back
        //rdbStore created database

        //Create data table
        //IF NOT EXISTS create if table does not exist
        //Table name test
        //(fields in parentheses)
        //id integer type PRIMARY KEY AUTOINCREMENT
        //name TEXT text type NOT NULL cannot be empty
        //age INTEGER type NOT NULL cannot be empty
        //Decimal real
        rdbStore.executeSql("CREATE TABLE IF NOT EXISTS " +
                    "test(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER NOT NULL,salary REAL)");
   }

   @Override
   public void onUpgrade(RdbStore rdbStore, int i, int i1) {
       //Callback will occur during database upgrade
   }

   @Override
   public void onDowngrade(RdbStore store, int currentVersion, int targetVersion) {
       super.onDowngrade(store, currentVersion, targetVersion);
       //Callback occurs when the database is degraded
   }

   @Override
   public void onOpen(RdbStore store) {
       super.onOpen(store);
       //Callback when database is opened
   }

   @Override
   public void onCorruption(File databaseFile) {
       super.onCorruption(databaseFile);
       //If there is a problem with the database, it will call back
   }

};

1. onCreate() calls back when the database is created and returns the created database. Generally, some operations to initialize the data table are also done in the oncreate method
2. onUpgrade() callback during database version upgrade
3. onDowngrade() callback when the database version is degraded
4. onOpen() callback when the database is opened
5. Callback when onCorruption() database has an error

New data

Call the insert method of the database to insert data. The line number of the current new data is returned. If it is - 1, it returns failure.

First create a ValuesBucket instance object and set the value of the corresponding field. Note that the type cannot be mistaken:

ValuesBucket valuesBucket = new ValuesBucket();
valuesBucket.putInteger("id",1);
valuesBucket.putString("name","Zhang San");
valuesBucket.putInteger("age",20);
valuesBucket.putDouble("salary",9000.12);

Then insert through the insert method:

//test table name
//valuesBucket instance object
long insert = rdbStore.insert("test",valuesBucket);//If the returned insert is not - 1, the insertion is successful.
HiLog.error(hiLogLabel,""+insert);

In addition, every time you insert data, you need to instantiate a ValuesBucket object. If you insert multiple pieces of data at one time, you can implement it through the for loop.

Whether the data is successfully inserted can be judged by the value output from the console. Here, output 1 indicates that the insertion is successful.

Query data

There are two ways to query the database. The first is to query the data through predicate splicing, and the second is to query the data through native SQL statements.

1.rdbStore.query() queries data in the form of concatenation of predicates

There are two kinds of predicate queries: rdbpredictes and rawrdbpredictes. Rdbpredictes can meet some query methods, such as the value is equal to, not equal to, and the minimum value and maximum value are in a certain interval. Rawrdbpredictes can meet the scenarios of complex SQL statements and support developers to set where condition clauses and where args parameters themselves, However, RawRdbPredicates does not support calling the RdbPredicates conditional interface.

Query steps:

Create an instance object first:

//test database table name
RdbPredicates test = new RdbPredicates("test");

Set query criteria:

//Data with a value equal to value, here is data with an age equal to 20
test.equalTo("age",20);
//The sorting mode allows the queried data groups to be arranged in descending order according to the size of the key field
test.orderByDesc("salary");

Set result set:

//The returned data fields are loaded in a result set
String[] columns = new String[]{"id","name","age","salary"};

Query the result set through the query method:

ResultSet query = rdbStore.query(test, columns);

Loop out the values in the result set to see if they meet our expected conditions:

for (int i = 0;i<query.getRowCount();i++){
    query.goToRow(i);//What line does the pointer point to
    int id = query.getInt(query.getColumnIndexForName("id"));//Get the column index and get it by the name of the column
    String name = query.getString(query.getColumnIndexForName("name"));
    int age = query.getInt(query.getColumnIndexForName("age"));
    double salary = query.getDouble(query.getColumnIndexForName("salary"));
    HiLog.error(hiLogLabel,"id:"+id + " name:" + name + " age:" + age + " salary:" + salary);
}

2.rdbStore.querySql() query data with native SQL statements

The native SQL statement queries the data and calls the querySql() method. The first parameter is the SQL statement to query, and the second parameter is the value of the question mark in the statement.

For example, the condition here is age =?, Then the following String [] {"20"} corresponds to age=20.

ResultSet resultSet = rdbStore.querySql("select id,name,age," +
"salary from test where age=? order by salary desc", new String[]{"20"});

Update data

Update data to rdbstore For the update () method, the first parameter is a ValuesBucket object, which is what we want to modify the data. The second data is the predicate rdbpredictes, which points to what data we want to modify; So the example here is that we want to modify the data with id 1, change the name to Wang Wu and the age to 30.

1. Predicate modify data

First, build modification conditions:

RdbPredicates rdbPredicates = new RdbPredicates("test");
rdbPredicates.equalTo("id",1);

Then build and modify parameter values:

ValuesBucket valuesBucket1 = new ValuesBucket();
valuesBucket1.putString("name","Wang Wu");
valuesBucket1.putInteger("age",30);

Finally, the update method is called for modification.

int update = rdbStore.update(valuesBucket1, rdbPredicates);

2. Modify data with native SQL

Execute sql statements through executeSql() of the database to modify.

rdbStore.executeSql("update test set name='Wang Wu' where id=?",new Object[]{1});

Delete data

When calling the delete() method, you only need to pass in the qualified predicate. It returns the current number of rows affected by the deletion. If it fails, it returns 0.

1. Predicate query

RdbPredicates deleteRDB = new RdbPredicates("test");
deleteRDB.equalTo("age",20);
int delete = rdbStore.delete(deleteRDB);

2. Native SQL query

int delete = rdbStore.executeSql("delete from test where age=?",new Object[]{20});

The above is the basic operation of adding, deleting, modifying and querying the database.

The basic operation of relational database ends here~