JianghuJS

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 配置各个环境的数据库连接信息。
  1. // config/config.${env}.js
  2. exports.knex = {
  3.   client: {
  4.     dialect: 'mysql',
  5.       connection: {
  6.         host: '127.0.0.1',
  7.         port: 3306,
  8.         user: 'root',
  9.         password: '123456',
  10.         database: '${数据库名}',
  11.       },
  12.       pool: { min: 10, max: 100 },
  13.       acquireConnectionTimeout: 30000,
  14.     },
  15.     app: true,
  16.   },
  17. };

使用方法

基本格式

  • 在你的代码中使用jianghuKnex时，你可以按照以下的基本格式：
  1. // 选择全部数据记录
  2. await this.app.jianghuKnex('${表的名字}').select(); 
  3. // 增加一条数据记录
  4. await this.app.jianghuKnex('${表的名字}').jhInsert({id: 1, data: 'abc'}); 
  5. // 更新id为1的数据记录
  6. await this.app.jianghuKnex('${表的名字}').where({id: 1}).jhUpdate({data: '123'});

接下来，我们以一个学生管理系统为例，示范各种SQL数据操作的写法:

  • 示例数据表名：'student_basic'
  • 数据表部分数据字段：
    • id：记录序号
    • studentId：学生编号
    • name：学生姓名
    • gender：性别
    • dateOfBirth：出生日期
    • classId：班级编号
    • level：年级

读取数据

  • 读全部数据
  1. await this.app.jianghuKnex('student_basic').select();
  • 读取第一条数据
  1. await this.app.jianghuKnex('student_basic').first();
  • 仅读取某些字段 【需要试验】
  1. // 读取第一条数据
  2. await this.app.jianghuKnex('student_basic').column('studentId', 'name', 'classId').first();
  4. // 读取全部数据
  5. await this.app.jianghuKnex('student_basic').column('studentId', 'name', 'classId').select();

设置各种Where条件

在jianghuKnex中，where条件并非必要，根据查询的需要增加即可。

通用查询条件：where

  1. const { jianghuKnex } = this.app
  3. // 查询名叫“张三丰”、班级编号是“2021-01级-02班”的学生
  4. await jianghuKnex('student_basic').where({ name: ‘张三丰’, classId: ‘2021-01级-02班’}).select();
  6. // 模糊查询名为“张”开头的学生
  7. await jianghuKnex('student_basic').where("name", "like", "张%").select();

和与或查询：andWhere、orWhere

  1. const { jianghuKnex } = this.app
  3. // 查询名叫“张三丰”并且身高大于180
  4. await jianghuKnex("student_basic")
  5.     .where({"name": "张三丰"})
  6.     .andWhere("bodyHeight", ">", 180).select()
  8. // 查询名叫“张三丰”或身高大于180
  9. await jianghuKnex("student_basic")
  10.     .where({"name": "张三丰"})
  11.     .orWhere("bodyHeight", ">", 180).select()

在查询条件中规定多个值：whereIn

  1. // 查找所有名字是“张三丰”或者“张无忌”的学生
  2. await this.app.jianghuKnex('student_basic').whereIn("name", ["张三丰", "张无忌"]).select();

自定义sql查询（不建议使用，容易sql注入）

  1. await this.app.jianghuKnex('student_basic').whereRaw('id = ? or bodyHeight > ?', [187, 180]).select();

设置Order By、Limit等条件

在jianghuKnex中，我们还可以对查询设置起始位置、条数以及排序规则。这些规则不是必须的，根据需要选填即可。

设置查询起始位置 offset

  • 例如，从第100位开始查询：
  1. await this.app.jianghuKnex('student_basic').offset(100).select();

设置查询条数 limit

  • 例如，查询前10条数据：
  1. await this.app.jianghuKnex('student_basic').limit(10).select();

设置排序规则

  • 例如，按照年龄倒序排列：
  1. await this.app.jianghuKnex('student_basic').orderBy('age', 'desc').select();
  • 也可以设置多个排序规则。例如，按照班级正序排列，再按年龄倒序排列：
  1. await this.app.jianghuKnex('student_basic').orderBy([
  2.   'classId', { column: 'age', order: 'desc' }
  3. ]).select();

插入数据 Insert

  • 插入一条数据：
  1. await this.app.jianghuKnex('student_basic', ctx).jhInsert({
  2.   studentId: 'G00100',
  3.   name: '小虾米',
  4.   gender: '男',
  5.   dateOfBirth：'2010-01-01',
  6.   classId：'2022-01级-01班'
  7.   level：1
  8. });
  • 插入多条数据，需要使用数组：
  1. await this.app.jianghuKnex('student_basic', ctx).jhInsert([{
  2.   studentId: 'G00003',
  3.   name: '小虾米',
  4.   gender: 'male',
  5.   dateOfBirth：'2010-01-01',
  6.   classId：'2022-01级-02班'
  7.   level：1
  8. }],[{
  9.   studentId: 'G00101',
  10.   name: '大虾米',
  11.   gender: 'male',
  12.   dateOfBirth：'2010-01-02',
  13.   classId：'2022-01级-02班'
  14.   level：1
  15. }]);

修改数据 Update

  • 修改学生编号为“G00101”的学生的姓名：
  1. await this.app.jianghuKnex('student_basic',ctx).where({studentId: 'G00101'}).jhUpdate({
  2.   name: '张大虾'
  3. });

可以通过设置where条件来修改多条数据。

删除数据 Delete

  • 删除学生编号为“G00101”的数据：
  1. await this.app.jianghuKnex('student_basic',ctx).where({studentId: 'G00101'}).jhDelete();

可以通过设置where条件来删除多条数据。

直接执行 sql 语句

jianghuKnex也支持直接执行sql语句。使用raw命令可以执行合法的sql语句。
注意！！我们极其不建议开发者拼接sql语句，这样很容易引起sql注入！！

  • 用法示例：查询学生编号为“G00101”的数据：
  1. const studentId = 'G00101';
  2. await this.app.jianghuKnex.raw(`SELECT * FROM student_basic WHERE studentId = '${studentId}'`);

注意事项：使用jhInsert/Update/Delete要传入ctx

jianghuKnex在对数据进行插入、修改或删除时，可以将数据的历史记录，存入_record_history表中。但需要注意的是，你需要把环境变量ctx作为一个参数传入jianghuKnex()函数中，这样才能记录是哪个用户执行了这个操作。

  • 在service中，一个完整的函数的写法是：
  1. // app/service/demo.js
  3. class DemoService extends Service {
  4.   async demoSelectFunction() {
  5.     const { ctx, app } = this;
  6.     const { jianghuKnex } = app;
  7.     const result = await jianghuKnex('sample-table').select();
  8.     return result;
  9.   }
  10.     
  11.   async demoInsertFunction() {
  12.     const { ctx, app } = this;
  13.     const { jianghuKnex } = app;
  14.     const result = await jianghuKnex('sample-table', ctx).insert({id: 100, name: '张三'});
  15.     return result;
  16.   }
  17. }
  19. module.exports = DemoService;

jianghuKnex 事务处理

MySQL事务是一组SQL语句的执行，全部成功或全部失败。在MySQL中，使用BEGIN、COMMIT和ROLLBACK三个关键字来实现事务。如果所有SQL语句都执行成功，使用COMMIT提交事务，否则使用ROLLBACK回滚事务。

  1. await jianghuKnex.transaction(async trx => {  
  2.     await trx('student').insert({ name: 'xxx1' });
  3.     await trx('student').insert({ name: 'xxx2' });
  4. });

参考资料

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 降序排列。