jianghuKnex
jianghuKnex概念
knex 是一个 Node.js 的 SQL 查询构建器,可简化开发者与 PostgreSQL、MySQL、SQLite 和 MSSQL 等常见数据库交互。
jianghuKnex 是在 Knex 基础上添加了数据历史特性的封装。
使用jianghuKnex之前的准备工作
安装
jianghuKnex是jianghuJS框架的默认插件。只要你的项目是基于jianghuJS框架的,就已经自动安装了jianghuKnex插件。
数据库设置
在使用jianghuKnex进行开发之前,请先确保你的数据库表符合jianghuJS框架里的数据库结构规范(https://openjianghu.org/doc/23/)
- 接下来,在配置文件
config/config.${env}.js
配置各个环境的数据库连接信息。
// config/config.${env}.js
exports.knex = {
client: {
dialect: 'mysql',
connection: {
host: '127.0.0.1',
port: 3306,
user: 'root',
password: '123456',
database: '${数据库名}',
},
pool: { min: 10, max: 100 },
acquireConnectionTimeout: 30000,
},
app: true,
},
};
使用方法
基本格式
- 在你的代码中使用jianghuKnex时,你可以按照以下的基本格式:
// 选择全部数据记录
await this.app.jianghuKnex('${表的名字}').select();
// 增加一条数据记录
await this.app.jianghuKnex('${表的名字}').jhInsert({id: 1, data: 'abc'});
// 更新id为1的数据记录
await this.app.jianghuKnex('${表的名字}').where({id: 1}).jhUpdate({data: '123'});
接下来,我们以一个学生管理系统为例,示范各种SQL数据操作的写法:
- 示例数据表名:'student_basic'
- 数据表部分数据字段:
- id:记录序号
- studentId:学生编号
- name:学生姓名
- gender:性别
- dateOfBirth:出生日期
- classId:班级编号
- level:年级
读取数据
- 读全部数据
await this.app.jianghuKnex('student_basic').select();
- 读取第一条数据
await this.app.jianghuKnex('student_basic').first();
- 仅读取某些字段 【需要试验】
// 读取第一条数据
await this.app.jianghuKnex('student_basic').column('studentId', 'name', 'classId').first();
// 读取全部数据
await this.app.jianghuKnex('student_basic').column('studentId', 'name', 'classId').select();
设置各种Where条件
在jianghuKnex中,where条件并非必要,根据查询的需要增加即可。
通用查询条件:where
const { jianghuKnex } = this.app
// 查询名叫“张三丰”、班级编号是“2021-01级-02班”的学生
await jianghuKnex('student_basic').where({ name: ‘张三丰’, classId: ‘2021-01级-02班’}).select();
// 模糊查询名为“张”开头的学生
await jianghuKnex('student_basic').where("name", "like", "张%").select();
和与或查询:andWhere、orWhere
const { jianghuKnex } = this.app
// 查询名叫“张三丰”并且身高大于180
await jianghuKnex("student_basic")
.where({"name": "张三丰"})
.andWhere("bodyHeight", ">", 180).select()
// 查询名叫“张三丰”或身高大于180
await jianghuKnex("student_basic")
.where({"name": "张三丰"})
.orWhere("bodyHeight", ">", 180).select()
在查询条件中规定多个值:whereIn
// 查找所有名字是“张三丰”或者“张无忌”的学生
await this.app.jianghuKnex('student_basic').whereIn("name", ["张三丰", "张无忌"]).select();
自定义sql查询(不建议使用,容易sql注入)
await this.app.jianghuKnex('student_basic').whereRaw('id = ? or bodyHeight > ?', [187, 180]).select();
设置Order By、Limit等条件
在jianghuKnex中,我们还可以对查询设置起始位置、条数以及排序规则。这些规则不是必须的,根据需要选填即可。
设置查询起始位置 offset
- 例如,从第100位开始查询:
await this.app.jianghuKnex('student_basic').offset(100).select();
设置查询条数 limit
- 例如,查询前10条数据:
await this.app.jianghuKnex('student_basic').limit(10).select();
设置排序规则
- 例如,按照年龄倒序排列:
await this.app.jianghuKnex('student_basic').orderBy('age', 'desc').select();
- 也可以设置多个排序规则。例如,按照班级正序排列,再按年龄倒序排列:
await this.app.jianghuKnex('student_basic').orderBy([
'classId', { column: 'age', order: 'desc' }
]).select();
插入数据 Insert
- 插入一条数据:
await this.app.jianghuKnex('student_basic', ctx).jhInsert({
studentId: 'G00100',
name: '小虾米',
gender: '男',
dateOfBirth:'2010-01-01',
classId:'2022-01级-01班'
level:1
});
- 插入多条数据,需要使用数组:
await this.app.jianghuKnex('student_basic', ctx).jhInsert([{
studentId: 'G00003',
name: '小虾米',
gender: 'male',
dateOfBirth:'2010-01-01',
classId:'2022-01级-02班'
level:1
}],[{
studentId: 'G00101',
name: '大虾米',
gender: 'male',
dateOfBirth:'2010-01-02',
classId:'2022-01级-02班'
level:1
}]);
修改数据 Update
- 修改学生编号为“G00101”的学生的姓名:
await this.app.jianghuKnex('student_basic',ctx).where({studentId: 'G00101'}).jhUpdate({
name: '张大虾'
});
可以通过设置where条件来修改多条数据。
删除数据 Delete
- 删除学生编号为“G00101”的数据:
await this.app.jianghuKnex('student_basic',ctx).where({studentId: 'G00101'}).jhDelete();
可以通过设置where条件来删除多条数据。
直接执行 sql 语句
jianghuKnex也支持直接执行sql语句。使用
raw
命令可以执行合法的sql语句。
注意!!我们极其不建议开发者拼接sql语句,这样很容易引起sql注入!!
- 用法示例:查询学生编号为“G00101”的数据:
const studentId = 'G00101';
await this.app.jianghuKnex.raw(`SELECT * FROM student_basic WHERE studentId = '${studentId}'`);
注意事项:使用jhInsert/Update/Delete要传入ctx
jianghuKnex在对数据进行插入、修改或删除时,可以将数据的历史记录,存入
_record_history
表中。但需要注意的是,你需要把环境变量ctx
作为一个参数传入jianghuKnex()
函数中,这样才能记录是哪个用户执行了这个操作。
- 在service中,一个完整的函数的写法是:
// app/service/demo.js
class DemoService extends Service {
async demoSelectFunction() {
const { ctx, app } = this;
const { jianghuKnex } = app;
const result = await jianghuKnex('sample-table').select();
return result;
}
async demoInsertFunction() {
const { ctx, app } = this;
const { jianghuKnex } = app;
const result = await jianghuKnex('sample-table', ctx).insert({id: 100, name: '张三'});
return result;
}
}
module.exports = DemoService;
jianghuKnex 事务处理
MySQL事务是一组SQL语句的执行,全部成功或全部失败。在MySQL中,使用BEGIN、COMMIT和ROLLBACK三个关键字来实现事务。如果所有SQL语句都执行成功,使用COMMIT提交事务,否则使用ROLLBACK回滚事务。
await jianghuKnex.transaction(async trx => {
await trx('student').insert({ name: 'xxx1' });
await trx('student').insert({ name: 'xxx2' });
});
参考资料
Knex.js 查询构建文档:https://knexjs.org/guide/query-builder.html#knex
小结
jianghuKnex 是在 Knex 基础上添加了数据历史特性的封装。它可以简化开发者与 PostgreSQL、MySQL、SQLite 和 MSSQL 等常见数据库交互。本文介绍了 jianghuKnex 的基础使用方法,包括增删改查、事务处理、以及高级用法,如查询高级用法(raw、join、count)和修改高级用法(increment)。
作业
- 使用 jianghuKnex 实现在 student 表中查询出年龄为 18 岁的所有学生记录并按照 id 降序排列。