runSql 原始数据库访问

说明

runSql() 执行手工编写的 SQL。

luwak.runSql(entity: string, query: string, params?: object): any;

参数

• entity 名字用于识别数据库连接
• query 手写 SQL, 支持命名占位符，支持命名参数绑定
• params 绑定的对象，字段名与占位符名对应

返回值

• 自动识别 SQL CRUD 类型，返回对象、数组或受影响的行数
• SELECT 操作如果有 LIMIT 1 则返回一个对象，否则返回数组

单条查询

示例 1

var result = luwak.runSql("sp.hrm.employees", "SELECT version() as mysql_version LIMIT 1");

globalThis.output = result;

示例 1 查询结果

{
  "mysqlVersion": "8.0.36"
}

示例 2

var query = "SELECT * FROM hrm.employees WHERE emp_no = :empNo LIMIT 1";

var params = { empNo: 10010 };

var result = luwak.runSql("sp.hrm.employees", query, params);

globalThis.output = result;

信息

其中，SQL语句中的占位符是Named Binding，即：冒号加变量名。

Luwak在内部使用预处理语句来运行SQL，避免SQL注入攻击。

示例 2 查询结果

{
  "birthDate": "1963-06-01",
  "empNo": 10010,
  "firstName": "Duangkaew",
  "gender": "F",
  "hireDate": "1989-08-24",
  "lastName": "Piveteau"
}

分页查询

需要分别编写统计和分页的 SQL

var page = { pageSize: 3, current: 1 };
var params = {
    limit: page.pageSize,
    offset: (page.current - 1) * page.pageSize
}

var query = "SELECT * FROM hrm.employees LIMIT :limit OFFSET :offset";
var list = luwak.runSql("sp.hrm.employees", query, params);

var countSql = "SELECT count(*) as total FROM hrm.employees limit 1";
var row = luwak.runSql("sp.hrm.employees", query, params);

var result = {
  data: list,
  total: row.total,
};

globalThis.output = result;

分页查询结果

{
  "data": [
    {
      "birthDate": "1964-06-02",
      "empNo": 10002,
      "firstName": "Bezalel",
      "gender": "F",
      "hireDate": "1985-11-21",
      "lastName": "Simmel"
    },
    {
      "birthDate": "1959-12-03",
      "empNo": 10003,
      "firstName": "Parto",
      "gender": "M",
      "hireDate": "1986-08-28",
      "lastName": "Bamford"
    },
    {
      "birthDate": "1954-05-01",
      "empNo": 10004,
      "firstName": "Chirstian",
      "gender": "M",
      "hireDate": "1986-12-01",
      "lastName": "Koblick"
    }
  ],
  "total": 124
}

备注

Luwak 禁止执行 DDL (创建表、修改表结构等)