Overview of relational database
Relational Database RDB (Relational Database) is a database that manages data based on relational model. Based on SQLite components, HarmonyOS Relational Database provides a complete set of management mechanism for local database, and provides a series of add, delete, modify, query and other interfaces. It can also directly run SQL statements entered by users to meet the needs of complex scenarios. HarmonyOS provides The function of the Relational Database is more perfect and the query efficiency is more efficient.
Basic concepts
- Relational database
A database that manages data based on a relational model and stores data in the form of rows and columns.
- predicate
The term used to represent the nature and characteristics of data entities or the relationship between data entities in a database is mainly used to define the operating conditions of the database.
- Result set
It refers to the result set after user query, which can access data. The result set provides a flexible data access method, which can more easily get the data users want.
- SQLite database
A lightweight database is a relational database management system that complies with ACID. It is an open source project.
Operation mechanism
HarmonyOS relational database provides a general operation interface to the outside world. The bottom layer uses SQLite as the persistent storage engine and supports all database features of SQLite, including but not limited to transactions, indexes, views, triggers, foreign keys, parametric queries and precompiled SQL sentences.
Figure 1 # relational database operation mechanism
Default configuration
- If the log mode of the database is not specified, the default log mode is WAL (Write Ahead Log).
- If the drop mode of the database is not specified, the system defaults to FULL mode.
- The default size of shared memory used by HarmonyOS database is 2MB.
Constraints and limitations
- The maximum number of connection pools in the database is 4 to manage users' read and write operations.
- To ensure the accuracy of data, the database can only support one write operation at a time.
Scene introduction
Relational database is a local data operation mechanism implemented on the basis of SQLite. It provides a method for users to add, delete, modify and query data without writing native SQL statements. At the same time, it also supports native SQL statement operations.
Interface description
Creation and deletion of database
Relational databases provide database creation methods and corresponding deletion interfaces. The API s involved are as follows.
| Class name | Interface name | describe |
|---|---|---|
| DatabaseHelper | DatabaseHelper(Context context) | DatabaseHelper is an auxiliary class for database operation. When the database is created successfully, the database files will be stored in the directory specified by the context. The path where the database file is stored will vary depending on the context specified.
|
| StoreConfig.Builder | public StoreConfig builder() | Configure the database, including setting the database name, storage mode, log mode, synchronization mode, whether it is read-only, and database encryption. |
| RdbOpenCallback | public abstract void onCreate(RdbStore store) | The database is called back when it is created. The developer can initialize the table structure in this method and add some initialization data used by the application. |
| RdbOpenCallback | public abstract void onUpgrade(RdbStore store, int currentVersion, int targetVersion) | Callback during database upgrade. |
| RdbOpenCallback | public void onDowngrade(RdbStore store, int currentVersion, int targetVersion) | Called back when the database is degraded. |
| DatabaseHelper | public RdbStore getRdbStore(StoreConfig config, int version, RdbOpenCallback openCallback, ResultSetHook resultSetHook) | Create or open a database based on the configuration. |
| DatabaseHelper | public boolean deleteRdbStore(String name) | Deletes the specified database. |
Database encryption
Relational database provides the ability of database encryption. If a key is specified when creating the database, it will be created as an encrypted database. When using the database again, you still need to specify the same key to open the database correctly.
| Class name | Interface name | describe |
|---|---|---|
| StoreConfig.Builder | public StoreConfig.Builder setEncryptKey(byte[] encryptKey) | Set the configuration class of database encryption key for the database. When creating or opening the database, pass in the configuration class containing database encryption key to create or open the encrypted database. |
Addition, deletion, modification and query of database
The relational database provides the ability to add, delete, modify and query local data. The relevant API s are as follows.
- The new relational database provides an interface for inserting data. Enter the data to be stored through ValuesBucket, and judge whether the insertion is successful through the return value. When the insertion is successful, the line number of the latest inserted data is returned, and - 1 is returned when it fails.
Table 3} database insertion API
Class name
Interface name
describe
RdbStore
long insert(String table, ValuesBucket initialValues)
Insert data into the database.
- Table: the table name of the data to be added.
- initialValues: data to be inserted stored in ValuesBucket. It provides a series of put methods, such as putString(String columnName, String values) and putDouble(String columnName, double value), which are used to add data to ValuesBucket.
- to update
Call the update interface, pass in the data to be updated, and specify the update conditions through absrdbpredictes. The return value of this interface indicates the number of rows affected by the update operation. If the update fails, 0 is returned.
Table 4 database update API
Class name
Interface name
describe
RdbStore
int update(ValuesBucket values, AbsRdbPredicates predicates)
Update the data in the database table that meets the conditions specified by the predicate.
- values: the data to be updated stored in ValuesBucket.
- Predictions: Specifies the table name and conditions of the update operation. There are two implementation classes of AbsRdbPredicates: RdbPredicates and RawRdbPredicates.
- Rdbpredictes: supports calling interfaces such as equalTo provided by predicates to set update conditions.
- Rawrdbpredictes: only three parameters including table name, where conditional clause and whereArgs can be set, and interface calls such as equalTo are not supported.
- delete
Call the delete interface and specify the delete conditions through absrdbpredictes. The return value of this interface indicates the number of data rows deleted. You can judge whether the deletion is successful according to this value. If the deletion fails, 0 is returned.
Table 5 database deletion API
Class name
Interface name
describe
RdbStore
int delete(AbsRdbPredicates predicates)
Delete data.
Predictions: Rdb predicate, which specifies the table name and conditions of the delete operation. There are two implementation classes of AbsRdbPredicates: RdbPredicates and RawRdbPredicates.
- Rdbpredictes: supports calling interfaces such as equalTo provided by predicates to set update conditions.
- Rawrdbpredictes: only three parameters including table name, where conditional clause and whereArgs can be set, and interface calls such as equalTo are not supported.
- query
Relational database provides two ways to query data:
- Directly call the query interface. Using this interface, predicates containing query conditions will be automatically spliced into complete SQL statements for query operations without the need for users to pass in native SQL statements.
- Execute native SQL statements for query operations.
Table 6 database query API
Class name
Interface name
describe
RdbStore
ResultSet query(AbsRdbPredicates predicates, String[] columns)
Query data.
- Predicates: predicates, which can set query conditions. There are two implementation classes of AbsRdbPredicates: RdbPredicates and RawRdbPredicates.
- Rdbpredictes: supports calling interfaces such as equalTo provided by predicates to set query conditions.
- Rawrdbpredictes: only three parameters including table name, where conditional clause and whereArgs can be set, and interface calls such as equalTo are not supported.
- Columns: Specifies the columns returned by the query.
RdbStore
ResultSet querySql(String sql, String[] sqlArgs)
Execute native SQL statements for query operations.
sql: a native sql statement used for queries.
sqlArgs: the value of placeholder parameter in sql statement. If placeholder is not used in select statement, the parameter can be set to null.
Use of database predicates
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.
| Class name | Interface name | describe |
|---|---|---|
| RdbPredicates | RdbPredicates equalTo(String field, String value) | Set the predicate condition to satisfy that the field value is equal to the value value. |
| RdbPredicates | RdbPredicates notEqualTo(String field, String value) | Set the predicate condition to satisfy that the field and value values are not equal. |
| RdbPredicates | RdbPredicates beginsWith(String field, String value) | Set the predicate condition to satisfy that the field field starts with the value value. |
| RdbPredicates | RdbPredicates between(String field, int low, int high) | Set predicate conditions to ensure that the field is between the minimum value low and the maximum value high. |
| RdbPredicates | RdbPredicates orderByAsc(String field) | Set predicate conditions and arrange them in ascending order according to the field. |
| RawRdbPredicates | void setWhereClause(String whereClause) | Set the where conditional clause. |
| RawRdbPredicates | void setWhereArgs(List<String> whereArgs) | Set the whereArgs parameter, which represents the value of the placeholder in the where clause. |
Use of query result set
Relational database provides a result set ResultSet returned by query, which points to a row of data in the query result for users to traverse and access the query result. The ResultSet external API is as follows.
| Class name | Interface name | describe |
|---|---|---|
| ResultSet | boolean goTo(int offset) | Moves the specified offset from the current position of the result set. |
| ResultSet | boolean goToRow(int position) | Moves the result set to the specified location. |
| ResultSet | boolean goToNextRow() | Moves the result set back one row. |
| ResultSet | boolean goToPreviousRow() | Moves the result set forward one row. |
| ResultSet | boolean isStarted() | Judge whether the result set has been moved. |
| ResultSet | boolean isEnded() | Judge whether the current position of the result set is after the last row. |
| ResultSet | boolean isAtFirstRow() | Judge whether the current position of the result set is in the first row. |
| ResultSet | boolean isAtLastRow() | Judge whether the current position of the result set is in the last row. |
| ResultSet | int getRowCount() | Gets the number of records in the current result set. |
| ResultSet | int getColumnCount() | Gets the number of columns in the result set. |
| ResultSet | String getString(int columnIndex) | Gets the value of the specified column of the current row and returns it as String. |
| ResultSet | byte[] getBlob(int columnIndex) | Gets the value of the specified column of the current row and returns it as a byte array. |
| ResultSet | double getDouble(int columnIndex) | Gets the value of the specified column of the current row and returns it in double type. |
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, the rollback operation will be performed automatically.
| Class name | Interface name | describe |
|---|---|---|
| RdbStore | void beginTransaction() | Start the transaction. |
| RdbStore | void markAsCommit() | Set the mark of the transaction as successful. |
| RdbStore | void endTransaction() | End the transaction. If the markAsCommit method is executed before calling this method, the transaction will be committed, otherwise the transaction will be rolled back automatically. |
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.
| Class name | Interface name | describe |
|---|---|---|
| RdbStore | void beginTransactionWithObserver(TransactionObserver transactionObserver) | Start the transaction and observe the start, commit and rollback of the transaction. |
| ResultSet | void registerObserver(DataObserver observer) | Register observers for the result set. |
| ResultSet | void unregisterObserver(DataObserver observer) | Unregister the observer of the result set. |
Database backup and recovery
Users can save and back up the data of the current database, and can also recover the data when necessary.
| Class name | Interface name | describe |
|---|---|---|
| RdbStore | boolean restore(String srcName) | Database recovery interface to recover data from the specified unencrypted database file. |
| RdbStore | boolean restore(String srcName, byte[] srcEncryptKey, byte[] destEncryptKey) | Database recovery interface to recover data from specified database files (both encrypted and non encrypted). |
| RdbStore | boolean backup(String destName) | Database backup interface. The backed up database files are unencrypted. |
| RdbStore | boolean backup(String destName, byte[] destEncryptKey) | Database backup interface. This method is often used in the scenario of backing up an encrypted database. |
Development steps
1. Create a database.
- Configure database related information, including database name, storage mode, whether it is read-only mode, etc.
- Initialize the database table structure and related data.
- Create a database.
package com.example.sqliteproject.utils;
import ohos.app.Context;
import ohos.data.DatabaseHelper;
import ohos.data.rdb.RdbOpenCallback;
import ohos.data.rdb.RdbStore;
import ohos.data.rdb.StoreConfig;
/**
* Create auxiliary classes for sqlite database
*/
public class MyHelper extends RdbOpenCallback {
DatabaseHelper db;
private RdbStore rs;
private static MyHelper helper;
//Singleton design pattern
public static RdbStore getInstance(Context context,String DBName){
if (helper==null)
helper=new MyHelper(context,DBName);
return helper.rs;
}
public MyHelper(Context context,String DBName) {
db=new DatabaseHelper(context);
//Create database
StoreConfig config=StoreConfig.newDefaultConfig(DBName);
rs=db.getRdbStore(config,1,this,null);
}
@Override
public void onCreate(RdbStore rdbStore) {
//Create table
rdbStore.executeSql("create table if not exists student(id integer primary key autoincrement,name varchar(20),age int ,phone varchar(11),address text)");
}
@Override
public void onUpgrade(RdbStore rdbStore, int i, int i1) {
System.out.println("Version updated");
}
}
2. Encapsulate DBUtils tool class
- Create a DBUtils class, create a construction method using simple interest design pattern, and encapsulate the method of adding, deleting, modifying and querying
package com.example.sqliteproject.utils;
import ohos.app.Context;
import ohos.data.rdb.RdbPredicates;
import ohos.data.rdb.RdbStore;
import ohos.data.rdb.ValuesBucket;
import ohos.data.resultset.ResultSet;
/**
* Database tool class
*/
public class DBUtil {
public static DBUtil db;
private static RdbStore rs;
//Singleton design pattern
public static DBUtil getInstance(RdbStore rs){
if (db==null)
db=new DBUtil();
DBUtil.rs=rs;
return db;
}
//add to
public long insert(String tableName, ValuesBucket vb){
return rs.insert(tableName,vb);
}
//modify
public long update(String tableName, ValuesBucket vb,int id){
RdbPredicates rdbPredicates = new RdbPredicates(tableName).equalTo("id",id);
return rs.update(vb,rdbPredicates);
}
//delete
public long delete(String tableName, int id){
RdbPredicates rdbPredicates = new RdbPredicates(tableName).equalTo("id",id);
return rs.delete(rdbPredicates);
}
//query
public String select(String tableName){
String info="";
//Defines the array of fields to query
String[] columns = new String[] {"id", "name", "age", "phone","address"};
//Set the table to query and query criteria
RdbPredicates rdbPredicates = new RdbPredicates(tableName);
ResultSet resultSet = rs.query(rdbPredicates, columns);
//Loop through the result set of the query to obtain the data of all queries
while (resultSet.goToNextRow()){
int id=resultSet.getInt(resultSet.getColumnIndexForName("id"));
String name=resultSet.getString(resultSet.getColumnIndexForName("name"));
int age=resultSet.getInt(resultSet.getColumnIndexForName("age"));
String phone=resultSet.getString(resultSet.getColumnIndexForName("phone"));
String address=resultSet.getString(resultSet.getColumnIndexForName("address"));
info+=id+"\t"+name+"\t"+age+"\t"+phone+"\t"+address+"\n";
}
return info;
}
}
3. Add data
- Create the ValuesBucket class and add the data to be added in the form of key value. Key corresponds to the field name of the database table and value is the data corresponding to the stored field.
//add to
public long insert(String tableName, ValuesBucket vb){
return rs.insert(tableName,vb);
}- Define a button in the MainAbilitySlice class, encapsulate the data by clicking the button trigger event, and call the DBUtils class to execute the insert method
//add to
ValuesBucket vb=new ValuesBucket();
vb.putString("name","Zhang San");
vb.putInteger("age",20);
vb.putString("phone","18170072135");
vb.putString("address","Nanchang City, Jiangxi Province");
long i=DBUtil.getInstance(rs).insert("student",vb);
if (i>0)
new ToastDialog(MainAbilitySlice.this).setText("Added successfully").show();4. Modify data
- Store the content to be updated in the ValuesBucket object, and set the condition data on the rdbpredictes object
//modify
public long update(String tableName, ValuesBucket vb,int id){
RdbPredicates rdbPredicates = new RdbPredicates(tableName).equalTo("id",id);
return rs.update(vb,rdbPredicates);
}- Define a button in the MainAbilitySlice class, encapsulate the data by clicking the button trigger event, and call the DBUtils class to execute the update method
//modify
ValuesBucket vb1=new ValuesBucket();
vb1.putInteger("age",24);
long j=DBUtil.getInstance(rs).update("student",vb1,1);
if (j>0)
new ToastDialog(MainAbilitySlice.this).setText("Modified successfully").show();5. Delete data
- Encapsulate the conditions to be deleted in the rdbpredictes object, and delete the data that meets the screening conditions according to the table name
//delete
public long delete(String tableName, int id){
RdbPredicates rdbPredicates = new RdbPredicates(tableName).equalTo("id",id);
return rs.delete(rdbPredicates);
}- Define a button in the MainAbilitySlice class, encapsulate the data by clicking the button trigger event, and call the DBUtils class to execute the delete method
//delete
long x=DBUtil.getInstance(rs).delete("student",2);
if (x>0)
new ToastDialog(MainAbilitySlice.this).setText("Delete succeeded").show();6. Query data
- Encapsulate the query condition data and table name in the rdbpredictes object, and then call the query method through the RdbStore object to obtain the ResultSet object to judge whether more data is queried for circular traversal
//query
public String select(String tableName){
String info="";
//Defines the array of fields to query
String[] columns = new String[] {"id", "name", "age", "phone","address"};
//Set the table to query and query criteria
RdbPredicates rdbPredicates = new RdbPredicates(tableName);
ResultSet resultSet = rs.query(rdbPredicates, columns);
//Loop through the result set of the query to obtain the data of all queries
while (resultSet.goToNextRow()){
int id=resultSet.getInt(resultSet.getColumnIndexForName("id"));
String name=resultSet.getString(resultSet.getColumnIndexForName("name"));
int age=resultSet.getInt(resultSet.getColumnIndexForName("age"));
String phone=resultSet.getString(resultSet.getColumnIndexForName("phone"));
String address=resultSet.getString(resultSet.getColumnIndexForName("address"));
info+=id+"\t"+name+"\t"+age+"\t"+phone+"\t"+address+"\n";
}
return info;
}- Define a button in the MainAbilitySlice class, click the button to trigger an event to encapsulate the data, call the DBUtils class to execute the select method, and display the queried data on the text component
//query
String info= DBUtil.getInstance(rs).select("student");
tv_text.setText(info);7. Interface layout construction
Add an xml file in the layout folder. The code for building the layout is as follows:
<?xml version="1.0" encoding="utf-8"?>
<DirectionalLayout
xmlns:ohos="http://schemas.huawei.com/res/ohos"
ohos:height="match_parent"
ohos:width="match_parent"
ohos:orientation="vertical">
<Button
ohos:id="$+id:btn1"
ohos:height="match_content"
ohos:width="match_parent"
ohos:text_size="20vp"
ohos:margin="10fp"
ohos:padding="10fp"
ohos:text_color="#f00"
ohos:background_element="$graphic:background_button1"
ohos:text="add to"/>
<Button
ohos:id="$+id:btn2"
ohos:height="match_content"
ohos:width="match_parent"
ohos:text_size="20vp"
ohos:margin="10fp"
ohos:padding="10fp"
ohos:text_color="#f00"
ohos:background_element="$graphic:background_button1"
ohos:text="modify"/>
<Button
ohos:id="$+id:btn3"
ohos:height="match_content"
ohos:width="match_parent"
ohos:text_size="20vp"
ohos:margin="10fp"
ohos:padding="10fp"
ohos:text_color="#f00"
ohos:background_element="$graphic:background_button1"
ohos:text="delete"/>
<Button
ohos:id="$+id:btn4"
ohos:height="match_content"
ohos:width="match_parent"
ohos:text_size="20vp"
ohos:margin="10fp"
ohos:padding="10fp"
ohos:text_color="#f00"
ohos:background_element="$graphic:background_button1"
ohos:text="query"/>
<Text
ohos:id="$+id:tv_text"
ohos:height="match_content"
ohos:width="match_content"
ohos:background_element="$graphic:background_ability_main"
ohos:layout_alignment="center"
ohos:auto_font_size="true"
ohos:multiple_lines="true"
ohos:text="$string:mainability_HelloWorld"
ohos:text_size="30vp"
/>
</DirectionalLayout>The background style code corresponding to the button is as follows:
<?xml version="1.0" encoding="utf-8"?>
<shape
xmlns:ohos="http://schemas.huawei.com/res/ohos"
ohos:shape="rectangle">
<corners
ohos:radius="10"
/>
<solid
ohos:color="#00ff00"/>
</shape>8. Complete code of abilityslice class
package com.example.sqliteproject.slice;
import com.example.sqliteproject.ResourceTable;
import com.example.sqliteproject.utils.DBUtil;
import com.example.sqliteproject.utils.MyHelper;
import ohos.aafwk.ability.AbilitySlice;
import ohos.aafwk.content.Intent;
import ohos.agp.components.Button;
import ohos.agp.components.Component;
import ohos.agp.components.Text;
import ohos.agp.window.dialog.ToastDialog;
import ohos.data.rdb.RdbStore;
import ohos.data.rdb.ValuesBucket;
public class MainAbilitySlice extends AbilitySlice {
RdbStore rs;
private Button btn1,btn2,btn3,btn4;
private Text tv_text;
@Override
public void onStart(Intent intent) {
super.onStart(intent);
super.setUIContent(ResourceTable.Layout_ability_main);
rs=MyHelper.getInstance(getContext(),"StudentDB");
btn1= (Button) this.findComponentById(ResourceTable.Id_btn1);
btn2= (Button) this.findComponentById(ResourceTable.Id_btn2);
btn3= (Button) this.findComponentById(ResourceTable.Id_btn3);
btn4= (Button) this.findComponentById(ResourceTable.Id_btn4);
tv_text= (Text) this.findComponentById(ResourceTable.Id_tv_text);
btn1.setClickedListener(listener);
btn2.setClickedListener(listener);
btn3.setClickedListener(listener);
btn4.setClickedListener(listener);
}
private Component.ClickedListener listener=new Component.ClickedListener() {
@Override
public void onClick(Component component) {
switch (component.getId()){
case ResourceTable.Id_btn1:
//add to
ValuesBucket vb=new ValuesBucket();
vb.putString("name","Zhang San");
vb.putInteger("age",20);
vb.putString("phone","18170072135");
vb.putString("address","Nanchang City, Jiangxi Province");
long i=DBUtil.getInstance(rs).insert("student",vb);
if (i>0)
new ToastDialog(MainAbilitySlice.this).setText("Added successfully").show();
break;
case ResourceTable.Id_btn2:
//modify
ValuesBucket vb1=new ValuesBucket();
vb1.putInteger("age",24);
long j=DBUtil.getInstance(rs).update("student",vb1,1);
if (j>0)
new ToastDialog(MainAbilitySlice.this).setText("Modified successfully").show();
break;
case ResourceTable.Id_btn3:
//delete
long x=DBUtil.getInstance(rs).delete("student",2);
if (x>0)
new ToastDialog(MainAbilitySlice.this).setText("Delete succeeded").show();
break;
case ResourceTable.Id_btn4:
//query
String info= DBUtil.getInstance(rs).select("student");
tv_text.setText(info);
break;
}
}
};
@Override
public void onActive() {
super.onActive();
}
@Override
public void onForeground(Intent intent) {
super.onForeground(intent);
}
}
9. Case effect display
Click the Add button to execute the add operation. Click the button to add two pieces of data to the database, and the ToastDialog dialog box shows that the addition is successful. Click the query button to query the added data
Click Modify to specify that the age of the student whose primary key is 1 is 24 years old. After the modification is successful, click query to see the modified data
Add a few more pieces of data and query them. Click the delete button to delete the records of students with primary key 2. If you query again, you will find that there is no student information with primary key 2 in the database, indicating that the deletion is successful
