本地数据库测试：学生表
本地数据库测试：学生表
下面使用 STUDENTS 表测试数据库增删改查。
字段说明：
ID -> 自增主键
STUDENT_NO -> 学号，唯一
NAME -> 姓名
GENDER -> 性别
AGE -> 年龄
CLASS_NAME -> 班级
PHONE -> 手机号
SCORE -> 成绩
CREATED_AT -> 创建时间
UPDATED_AT -> 更新时间
1. 创建学生表
const CREATE_STUDENTS_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS STUDENTS (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
STUDENT_NO TEXT NOT NULL UNIQUE,
NAME TEXT NOT NULL,
GENDER TEXT DEFAULT '',
AGE INTEGER DEFAULT 0,
CLASS_NAME TEXT DEFAULT '',
PHONE TEXT DEFAULT '',
SCORE REAL DEFAULT 0,
CREATED_AT INTEGER NOT NULL,
UPDATED_AT INTEGER NOT NULL
)
`;
创建表：
await this.rdbStore?.execute(
CREATE_STUDENTS_TABLE_SQL
);
建议在数据库初始化时执行：
async initDatabase(
context: Context
): Promise<void> {
const config:
relationalStore.StoreConfig = {
name: 'STUDENT_DATABASE',
securityLevel:
relationalStore.SecurityLevel.S1
};
this.rdbStore =
await relationalStore.getRdbStore(
context,
config
);
await this.rdbStore.execute(
CREATE_STUDENTS_TABLE_SQL
);
await this.rdbStore.execute(
CREATE_STUDENTS_INDEX_SQL
);
}
2. 创建索引
学号已经有 UNIQUE 约束，查询学号时会自动保证唯一。
班级、成绩和更新时间也可以建立索引：
const CREATE_STUDENTS_INDEX_SQL = `
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_CLASS_NAME
ON STUDENTS(CLASS_NAME);
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_SCORE
ON STUDENTS(SCORE);
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_UPDATED_AT
ON STUDENTS(UPDATED_AT);
`;
如果鸿蒙数据库不支持一次执行多条 SQL，分开执行：
await this.rdbStore?.execute(`
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_CLASS_NAME
ON STUDENTS(CLASS_NAME)
`);
await this.rdbStore?.execute(`
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_SCORE
ON STUDENTS(SCORE)
`);
await this.rdbStore?.execute(`
CREATE INDEX IF NOT EXISTS
IDX_STUDENTS_UPDATED_AT
ON STUDENTS(UPDATED_AT)
`);
3. 插入一条学生数据
使用 SQL 插入
const insertSql = `
INSERT INTO STUDENTS (
STUDENT_NO,
NAME,
GENDER,
AGE,
CLASS_NAME,
PHONE,
SCORE,
CREATED_AT,
UPDATED_AT
) VALUES (
'STUDENT_NO_001',
'学生姓名',
'男',
18,
'CLASS_001',
'PHONE_NUMBER',
85.5,
${Date.now()},
${Date.now()}
)
`;
await this.rdbStore?.execute(insertSql);
不建议把用户输入直接拼到 SQL 中。更推荐使用参数绑定：
const values:
relationalStore.ValuesBucket = {
STUDENT_NO: 'STUDENT_NO_001',
NAME: '学生姓名',
GENDER: '男',
AGE: 18,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER',
SCORE: 85.5,
CREATED_AT: Date.now(),
UPDATED_AT: Date.now()
};
await this.rdbStore?.insert(
'STUDENTS',
values,
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
);
4. 批量插入学生
const students:
relationalStore.ValuesBucket[] = [
{
STUDENT_NO: 'STUDENT_NO_001',
NAME: '学生一',
GENDER: '男',
AGE: 18,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER_001',
SCORE: 85,
CREATED_AT: Date.now(),
UPDATED_AT: Date.now()
},
{
STUDENT_NO: 'STUDENT_NO_002',
NAME: '学生二',
GENDER: '女',
AGE: 19,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER_002',
SCORE: 92,
CREATED_AT: Date.now(),
UPDATED_AT: Date.now()
}
];
for (const student of students) {
await this.rdbStore?.insert(
'STUDENTS',
student,
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
);
}
数据量较大时，可以使用事务：
await this.rdbStore?.beginTransaction();
try {
for (const student of students) {
await this.rdbStore?.insert(
'STUDENTS',
student,
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
);
}
await this.rdbStore?.commit();
} catch (error) {
await this.rdbStore?.rollBack();
LogUtils.error(
TAG,
`批量插入失败: ${JSON.stringify(error)}`
);
}
5. 查询全部学生
SQL 查询
const resultSet =
await this.rdbStore?.querySql(`
SELECT
ID,
STUDENT_NO,
NAME,
GENDER,
AGE,
CLASS_NAME,
PHONE,
SCORE,
CREATED_AT,
UPDATED_AT
FROM STUDENTS
ORDER BY ID DESC
`);
读取查询结果
const students:
relationalStore.ValuesBucket[] = [];
if (resultSet) {
try {
while (resultSet.goToNextRow()) {
students.push({
ID: resultSet.getLong(
resultSet.getColumnIndex('ID')
),
STUDENT_NO: resultSet.getString(
resultSet.getColumnIndex('STUDENT_NO')
),
NAME: resultSet.getString(
resultSet.getColumnIndex('NAME')
),
GENDER: resultSet.getString(
resultSet.getColumnIndex('GENDER')
),
AGE: resultSet.getLong(
resultSet.getColumnIndex('AGE')
),
CLASS_NAME: resultSet.getString(
resultSet.getColumnIndex('CLASS_NAME')
),
PHONE: resultSet.getString(
resultSet.getColumnIndex('PHONE')
),
SCORE: resultSet.getDouble(
resultSet.getColumnIndex('SCORE')
)
});
}
} finally {
resultSet.close();
}
}
查询完成后必须关闭：
resultSet.close();
6. 根据学号查询学生
const resultSet =
await this.rdbStore?.querySql(`
SELECT *
FROM STUDENTS
WHERE STUDENT_NO = 'STUDENT_NO_001'
LIMIT 1
`);
使用参数查询：
const resultSet =
await this.rdbStore?.querySql(
'SELECT * FROM STUDENTS WHERE STUDENT_NO = ? LIMIT 1',
['STUDENT_NO_001']
);
读取一条数据：
if (resultSet) {
try {
if (resultSet.goToNextRow()) {
const name = resultSet.getString(
resultSet.getColumnIndex('NAME')
);
const score = resultSet.getDouble(
resultSet.getColumnIndex('SCORE')
);
console.info(
`姓名=${name}, 成绩=${score}`
);
}
} finally {
resultSet.close();
}
}
7. 根据班级查询学生
const resultSet =
await this.rdbStore?.querySql(
`
SELECT *
FROM STUDENTS
WHERE CLASS_NAME = ?
ORDER BY SCORE DESC
`,
['CLASS_001']
);
只查询成绩大于 90 分的学生：
const resultSet =
await this.rdbStore?.querySql(
`
SELECT
STUDENT_NO,
NAME,
CLASS_NAME,
SCORE
FROM STUDENTS
WHERE SCORE >= ?
ORDER BY SCORE DESC
`,
[90]
);
8. 模糊查询学生姓名
const resultSet =
await this.rdbStore?.querySql(
`
SELECT *
FROM STUDENTS
WHERE NAME LIKE ?
ORDER BY ID DESC
`,
['%学生%']
);
查询班级中姓名包含关键词的学生：
const resultSet =
await this.rdbStore?.querySql(
`
SELECT *
FROM STUDENTS
WHERE CLASS_NAME = ?
AND NAME LIKE ?
`,
['CLASS_001', '%关键词%']
);
9. 查询学生数量
查询全部数量：
const resultSet =
await this.rdbStore?.querySql(`
SELECT COUNT(*) AS TOTAL
FROM STUDENTS
`);
let total = 0;
if (resultSet) {
try {
if (resultSet.goToNextRow()) {
total = resultSet.getLong(
resultSet.getColumnIndex('TOTAL')
);
}
} finally {
resultSet.close();
}
}
查询某个班级的学生数量：
const resultSet =
await this.rdbStore?.querySql(
`
SELECT COUNT(*) AS TOTAL
FROM STUDENTS
WHERE CLASS_NAME = ?
`,
['CLASS_001']
);
10. 分页查询
第一页，每页 20 条：
const page = 1;
const pageSize = 20;
const offset = (page - 1) * pageSize;
const resultSet =
await this.rdbStore?.querySql(
`
SELECT *
FROM STUDENTS
ORDER BY ID DESC
LIMIT ? OFFSET ?
`,
[pageSize, offset]
);
封装成方法：
async queryStudents(
page: number,
pageSize: number
): Promise<void> {
const offset =
(page - 1) * pageSize;
const resultSet =
await this.rdbStore?.querySql(
`
SELECT *
FROM STUDENTS
ORDER BY ID DESC
LIMIT ? OFFSET ?
`,
[pageSize, offset]
);
if (!resultSet) {
return;
}
try {
while (resultSet.goToNextRow()) {
const studentNo =
resultSet.getString(
resultSet.getColumnIndex('STUDENT_NO')
);
console.info(
`studentNo=${studentNo}`
);
}
} finally {
resultSet.close();
}
}
数据量很大时，分页比一次性查询全部更合适：
首次加载 -> LIMIT 20
滑动到底 -> 查询下一页
没有更多 -> 停止查询
11. 修改学生信息
使用 SQL 修改
await this.rdbStore?.execute(
`
UPDATE STUDENTS
SET
NAME = ?,
AGE = ?,
CLASS_NAME = ?,
SCORE = ?,
UPDATED_AT = ?
WHERE STUDENT_NO = ?
`,
[
'新姓名',
19,
'CLASS_002',
95.5,
Date.now(),
'STUDENT_NO_001'
]
);
使用 RdbPredicates 修改
const values:
relationalStore.ValuesBucket = {
NAME: '新姓名',
AGE: 19,
CLASS_NAME: 'CLASS_002',
SCORE: 95.5,
UPDATED_AT: Date.now()
};
const predicates =
new relationalStore.RdbPredicates(
'STUDENTS'
);
predicates.equalTo(
'STUDENT_NO',
'STUDENT_NO_001'
);
await this.rdbStore?.update(
values,
predicates
);
更新时优先使用：
STUDENT_NO
不要只使用姓名：
// 姓名可能重复
predicates.equalTo(
'NAME',
'学生姓名'
);
12. 删除一个学生
使用 SQL 删除
await this.rdbStore?.execute(
`
DELETE FROM STUDENTS
WHERE STUDENT_NO = ?
`,
['STUDENT_NO_001']
);
使用 RdbPredicates 删除
const predicates =
new relationalStore.RdbPredicates(
'STUDENTS'
);
predicates.equalTo(
'STUDENT_NO',
'STUDENT_NO_001'
);
const deleteCount =
await this.rdbStore?.delete(
predicates
);
console.info(
`删除数量=${deleteCount}`
);
13. 批量删除学生
const studentNos: string[] = [
'STUDENT_NO_001',
'STUDENT_NO_002',
'STUDENT_NO_003'
];
const predicates =
new relationalStore.RdbPredicates(
'STUDENTS'
);
predicates.in(
'STUDENT_NO',
studentNos
);
await this.rdbStore?.delete(
predicates
);
也可以使用 SQL：
await this.rdbStore?.execute(
`
DELETE FROM STUDENTS
WHERE STUDENT_NO IN (?, ?, ?)
`,
[
'STUDENT_NO_001',
'STUDENT_NO_002',
'STUDENT_NO_003'
]
);
14. 清空学生表
删除全部数据：
await this.rdbStore?.execute(
'DELETE FROM STUDENTS'
);
重置自增 ID：
await this.rdbStore?.execute(
'DELETE FROM sqlite_sequence WHERE name = ?',
['STUDENTS']
);
测试环境可以这样清空：
async clearStudents(): Promise<void> {
await this.rdbStore?.execute(
'DELETE FROM STUDENTS'
);
await this.rdbStore?.execute(
'DELETE FROM sqlite_sequence WHERE name = ?',
['STUDENTS']
);
}
生产环境不要随便执行清空操作。
15. 修改表结构
增加邮箱字段：
await this.rdbStore?.execute(`
ALTER TABLE STUDENTS
ADD COLUMN EMAIL TEXT DEFAULT ''
`);
增加备注字段：
await this.rdbStore?.execute(`
ALTER TABLE STUDENTS
ADD COLUMN REMARK TEXT DEFAULT ''
`);
不要重复执行同一条 ALTER TABLE：
ALTER TABLE STUDENTS ADD COLUMN EMAIL TEXT;
否则字段已经存在时会报错。
可以使用数据库版本控制：
const DATABASE_VERSION = 2;
async upgradeDatabase(
oldVersion: number
): Promise<void> {
if (oldVersion < 2) {
await this.rdbStore?.execute(`
ALTER TABLE STUDENTS
ADD COLUMN EMAIL TEXT DEFAULT ''
`);
}
}
16. 测试数据
const TEST_STUDENTS = [
{
STUDENT_NO: 'STUDENT_NO_001',
NAME: '学生一',
GENDER: '男',
AGE: 18,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER_001',
SCORE: 85.5
},
{
STUDENT_NO: 'STUDENT_NO_002',
NAME: '学生二',
GENDER: '女',
AGE: 19,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER_002',
SCORE: 92
},
{
STUDENT_NO: 'STUDENT_NO_003',
NAME: '学生三',
GENDER: '男',
AGE: 18,
CLASS_NAME: 'CLASS_002',
PHONE: 'PHONE_NUMBER_003',
SCORE: 76
}
];
批量添加：
for (const item of TEST_STUDENTS) {
await this.rdbStore?.insert(
'STUDENTS',
{
...item,
CREATED_AT: Date.now(),
UPDATED_AT: Date.now()
},
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
);
}
17. 数据库测试顺序
1. 创建 STUDENTS 表
2. 插入一条学生
3. 查询全部学生
4. 根据学号查询
5. 根据班级查询
6. 修改学生成绩
7. 删除一条学生
8. 批量插入
9. 分页查询
10. 清空测试数据
18. 常见问题
插入重复学号
原因：
STUDENT_NO 设置了 UNIQUE
处理方式：
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
或者插入前查询：
SELECT *
FROM STUDENTS
WHERE STUDENT_NO = ?
查询结果为空
检查：
1. 是否真正执行了 insert
2. 是否使用了同一个数据库文件
3. 表名是否写成 STUDENTS
4. 条件字段是否写错
5. insert 是否还没有 await 完成
查询几次后变慢
检查：
1. ResultSet 是否 close
2. 是否每次 SELECT *
3. 是否重复查询全部数据
4. 是否需要分页
5. 查询字段是否建立索引
删除成功但页面还显示
数据库和页面数组是两份数据：
await deleteStudent(studentNo);
this.studentList =
this.studentList.filter((student) => {
return student.STUDENT_NO !== studentNo;
});
数据库删除成功后，还要同步：
页面列表
选中列表
分组列表
缓存数据
19. 脱敏后的完整测试案例
class StudentDatabase {
private store?:
relationalStore.RdbStore;
async createTable(): Promise<void> {
await this.store?.execute(`
CREATE TABLE IF NOT EXISTS STUDENTS (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
STUDENT_NO TEXT NOT NULL UNIQUE,
NAME TEXT NOT NULL,
GENDER TEXT DEFAULT '',
AGE INTEGER DEFAULT 0,
CLASS_NAME TEXT DEFAULT '',
PHONE TEXT DEFAULT '',
SCORE REAL DEFAULT 0,
CREATED_AT INTEGER NOT NULL,
UPDATED_AT INTEGER NOT NULL
)
`);
}
async insertStudent(): Promise<void> {
await this.store?.insert(
'STUDENTS',
{
STUDENT_NO: 'STUDENT_NO_001',
NAME: '学生姓名',
GENDER: '男',
AGE: 18,
CLASS_NAME: 'CLASS_001',
PHONE: 'PHONE_NUMBER',
SCORE: 88.5,
CREATED_AT: Date.now(),
UPDATED_AT: Date.now()
},
relationalStore.ConflictResolution
.ON_CONFLICT_REPLACE
);
}
async queryStudents(): Promise<void> {
const resultSet =
await this.store?.querySql(`
SELECT *
FROM STUDENTS
ORDER BY ID DESC
`);
if (!resultSet) {
return;
}
try {
while (resultSet.goToNextRow()) {
const studentNo =
resultSet.getString(
resultSet.getColumnIndex('STUDENT_NO')
);
const name =
resultSet.getString(
resultSet.getColumnIndex('NAME')
);
console.info(
`studentNo=${studentNo}, name=${name}`
);
}
} finally {
resultSet.close();
}
}
async updateStudent(
studentNo: string,
score: number
): Promise<void> {
await this.store?.execute(
`
UPDATE STUDENTS
SET SCORE = ?, UPDATED_AT = ?
WHERE STUDENT_NO = ?
`,
[score, Date.now(), studentNo]
);
}
async deleteStudent(
studentNo: string
): Promise<void> {
await this.store?.execute(
`
DELETE FROM STUDENTS
WHERE STUDENT_NO = ?
`,
[studentNo]
);
}
}
示例中的隐私字段统一使用：
STUDENT_NO_001
学生姓名
PHONE_NUMBER
CLASS_001
浙公网安备 33010602011771号