Skip to main content

执行 SQL

在 DataQL 中执行一条SQL，然后将 SQL 的结果集进行处理非常简单。

// 声明一个 SQL
var dataSetFun = @@sql() <%
    select * from category limit 10;
%>

// 执行这个 SQL，并返回结果
return dataSetFun();

SQL 参数化

为了防止 SQL 注入，通常都使用带参的SQL。

// 声明一个 SQL
var dataSetFun = @@sql(itemCode) <%
    select * from category where co_code = #{itemCode} limit 10;
%>

// 执行这个 SQL，并返回结果
return dataSetFun(${itemCode});

SQL 注入

SQL 注入能力是为了一些特殊场景需要拼接 SQL 而准备的，例如：动态排序字段和排序规则。

其中参数: orderField 是排序字段，orderType 是排序类型

// 使用 DataQL 拼接字符串
var orderBy = ${orderField} + " " + ${orderType};

// 声明一个可以注入的 SQL
var dataSetFun = @@sql(itemCode,orderString) <%
    select * from category where co_code = #{itemCode} order by ${orderString} limit 10;
%>

// 执行这个 SQL，并返回结果
return dataSetFun(${itemCode}, orderBy);

Ognl 表达式

SQL 执行器可以将一个对象作为参数传入。通过 SQL 模版中的 ognl 表达式来获取对应的值，例如：一个对象插入 SQL

// 例子数据
var testData = {
    "name"   : "马三",
    "age"    : 26,
    "status" : 0
}

// insert语句模版
var insertSqlFun = @@sql(userInfo) <%
    insert into user_info (
        name,
        age,
        status,
        create_time
    ) values (
        #{userInfo.name},
        #{userInfo.age},
        #{userInfo.status},
        now()
    )
%>

// 插入数据
return insertSqlFun(testData);

批量操作

DataQL 的 SQL 执行器支持批量 Insert\Update\Delete\Select 操作，最常见的场景是批量插入数据。批量操作必须满足下列几点要求：

  • 入参必须是 List
  • 如果有多个入参。所有参数都必须是 List 并且长度必须一致。
  • @@sql()<% ... %> 写法升级为批量写法 @@sql[]()<% ... %>
  • 如果批量操作的 SQL 中存在 SQL注入，那么批量操作会自动退化为：循环遍历模式

还是上面插入数据的例子，采用批量模式之后 SQL 部分不变。只是把 @@sql 改为 @@sql[]；入参数转换为数组即可。

// 例子数据
var testData = [
    { "name" : "马一", "age" : 26, "status" : 0 },
    { "name" : "马二", "age" : 26, "status" : 0 },
    { "name" : "马三", "age" : 26, "status" : 0 }
]

// insert语句模版
var insertSqlFun = @@sql[](userInfo) <%
    insert into user_info (
        name,
        age,
        status,
        create_time
    ) values (
        #{userInfo.name},
        #{userInfo.age},
        #{userInfo.status},
        now()
    )
%>

// 批量操作
return insertSqlFun(testData);
tip

由于批量操作底层执行 SQL 使用的是 java.sql.Statement.executeBatch 方法，因此执行 insertSQL 的返回值是一组 int 数组。

执行结果拆包

拆包是指，例如执行 select count(*) from ... 这种语句时 SQL 执行器自动将返回的一行一列数据拆解为 int 类型值。

拆包分为三个模式，可以通过 hint FRAGMENT_SQL_OPEN_PACKAGE 来改变，默认为：column

  • 拆包模式可以通过 hint 改变，例如：hint FRAGMENT_SQL_OPEN_PACKAGE = 'row'
不指定 hint 的情况下，会返回 category 表的总记录数
var dataSetFun = @@sql() <% select count(*) as cnt from category; %>
var result =  dataSetFun();

// 结果 result =  10
拆包模式变更为 row ，返回整行对象
hint FRAGMENT_SQL_OPEN_PACKAGE = "row" // 拆包模式设置为：行
var dataSet = @@sql() <% select count(*) as cnt from category; %>
var result =  dataSet();

// 结果 result = { "cnt" : 10 }
关闭拆包，返回值为标准的 List/Map
hint FRAGMENT_SQL_OPEN_PACKAGE = "off" // 拆包模式设置为：关闭
var dataSetFun = @@sql() <% select count(*) as cnt from category; %>
var result =  dataSetFun();

// 结果 result = [ { "cnt" : 10 } ]

结果列名拼写转换

列名拼写转换是指，从数据库中查询返回的列名信息。按照某个规则进行统一处理，例如：所有 key 全部转为驼峰。

这个功能十分有意义，他可以让使用 DataQL 查询数据库返回的列信息具有很高的程序可读性。

var dataSetFun = @@sql() <% select user_id, user_name from category; %>
var result =  dataSetFun();
// 在不指定 Hint 的情况下，Mysql 会返回 类似下面的数据：
// result = [
//   { "user_id" : "1", "user_name" : "马三" }
//   { "user_id" : "2", "user_name" : "马四" }
//   { "user_id" : "3", "user_name" : "马五" }
// ]

在某些数据库上同样的 SQL 可能列名会统一转成大写。

因此可以通过一个 hint FRAGMENT_SQL_COLUMN_CASE 来控制 SQL 执行器对列名的统一处理。比如：

统一转驼峰
hint FRAGMENT_SQL_COLUMN_CASE = "hump"
var dataSetFun = @@sql() <% select user_id, user_name from category; %>
var result =  dataSetFun();

// 在不指定 Hint 的情况下，Mysql 会返回 类似下面的数据：
// result = [
//   { "userId" : "1", "userName" : "马三" }
//   { "userId" : "2", "userName" : "马四" }
//   { "userId" : "3", "userName" : "马五" }
// ]

分页查询

分页查询默认是关闭的，需要通过设置 hint FRAGMENT_SQL_QUERY_BY_PAGE 将其打开

  • hint FRAGMENT_SQL_QUERY_BY_PAGE = true

打开分页查询之后执行 SQL 操作需要经过 4 个步骤。

  • 定义分页SQL
  • 创建分页查询对象
  • 设置分页信息
  • 执行分页查询
// SQL 执行器切换为分页模式
hint FRAGMENT_SQL_QUERY_BY_PAGE = true

// 定义查询SQL
var dataSetFun = @@sql() <%
    select * from category
%>

// 创建分页查询对象
var pageQuery =  dataSetFun();

// 设置分页信息
run pageQuery.setPageInfo({
    "pageSize"    : 10, // 页大小
    "currentPage" : 3   // 第3页
});

// 执行分页查询
var result = pageQuery.data();

分页信息

分页查询场景中会有一个更加明细的分页数据，DataQL 分页对象通过 pageInfo 方法即可拿到这个信息。

// SQL 执行器切换为分页模式
hint FRAGMENT_SQL_QUERY_BY_PAGE = true

// 定义查询SQL
var dataSetFun = @@sql() <%
    select * from category
%>

// 创建分页查询对象
var pageQuery =  dataSetFun();

// 设置分页信息
run pageQuery.setPageInfo({
    "pageSize"    : 10, // 页大小
    "currentPage" : 3   // 第3页
});

// 获得分页信息
var result = pageQuery.pageInfo();
// result = {
//    "enable"         : true,  // 启用了分页，当 pageSize > 0 时为 true
//    "pageSize"       : 4,     // 每页大小
//    "totalCount"     : 17,    // 记录总数
//    "totalPage"      : 5,     // 总页数
//    "currentPage"    : 3,     // 当前页数
//    "recordPosition" : 12     // 当前页第一条记录所处的记录第几行。
// }
tip

获取分页信息时需要获取总记录数，因此会产生一条 count 的查询。

关于第一页：方式一​

在前端框架分页机制中，第一页相当多是从 1 开始计算的。然而默认情况下 SQL 执行器的第一页的页码是从 0 开始的。

因此前端如果传递 page1 = 1 的情况下需要做一些处理。通常情况下是 -1 例如：

tip

如果API发布采用的是 GET 方法还需要通过 toInt 转换函数 转换，否则会无法正确识别参数是否为数字。

import 'net.hasor.dataql.fx.basic.ConvertUdfSource' as convert;
hint FRAGMENT_SQL_QUERY_BY_PAGE = true
...
run queryPage.setPageInfo({
    "pageSize"    : 5, // 页大小
    "currentPage" : (convert.toInt(${pageNumber}) -1)
});
...

关于第一页：方式二（推荐）​

除了 -1 之外，DataQL 在 4.1.8 版本中加入了 hint FRAGMENT_SQL_QUERY_BY_PAGE_NUMBER_OFFSET， 可以简单的通过配置 hint 让 SQL 执行器自动处理从 1 开始作为页码的启始编号。

hint FRAGMENT_SQL_QUERY_BY_PAGE = true
hint FRAGMENT_SQL_QUERY_BY_PAGE_NUMBER_OFFSET = 1
...
run pageQuery.setPageInfo({
    "pageSize"    : 5, // 页大小
    "currentPage" : 1  // 第1页，在设置 FRAGMENT_SQL_QUERY_BY_PAGE_NUMBER_OFFSET 之前 第一页要设置为 0
});
...

数据库事务

SQL 执行器本身并不支持数据库事务，事务的能力需要借助事务函数来实现。

import 'net.hasor.dataql.fx.db.TransactionUdfSource' as tran; //引入事务函数
...
return tran.required(() -> {
    ... // 事务
    return ...
});
...

DataQL 的事务函数还可以嵌套使用。

...
return tran.required(() -> {
    ... // 事务
    var dat = tran.required(() -> {
        ... // 嵌套事务
        return ...
    });
    ...
    return dat;
});
...

支持完整的 7 个传播属性。

类型说明用法
REQUIRED加入已有事务tran.required(() -> { … });
REQUIRES_NEW独立事务tran.requiresNew(() -> { … });
NESTED嵌套事务tran.nested(() -> { … });
SUPPORTS跟随环境tran.supports(() -> { … });
NOT_SUPPORTED非事务方式tran.notSupported(() -> { … });
NEVER排除事务tran.never(() -> { … });
MANDATORY要求环境中存在事务tran.tranMandatory(() -> { … });

多数据源

SQL执行器在 4.1.4 版本之后提供了可以通过 hint FRAGMENT_SQL_DATA_SOURCE 来切换数据源的能力。 在没有指定数据源 hint 的情况下数据源采用的是默认数据源

配置多个数据源的方式如下
public class MyModule implements Module {
    public void loadModule(ApiBinder apiBinder) throws Throwable {
        DataSource defaultDs = ...;
        DataSource dsA = ...;
        DataSource dsB = ...;
        apiBinder.installModule(new JdbcModule(Level.Full, defaultDs));   // 默认数据源
        apiBinder.installModule(new JdbcModule(Level.Full, "ds_A", dsA)); // 数据源A
        apiBinder.installModule(new JdbcModule(Level.Full, "ds_B", dsB)); // 数据源B
    }
}
在 DataQL 中选择数据源
// 如果不设置 FRAGMENT_SQL_DATA_SOURCE 使用的是 defaultDs 数据源。
//   - 设置值为 "ds_A" ，使用的是 dsA 数据源。
//   - 设置值为 "ds_B" ，使用的是 dsB 数据源。
hint FRAGMENT_SQL_DATA_SOURCE = "ds_A"

// 声明一个 SQL
var dataSetFun = @@sql() <% select * from category limit 10; %>
// 使用 特定数据源来执行SQL。
return dataSetFun();

多条查询

多条查询是指，一次SQL 执行的过程中包含了 一个以上的 SQL 语句。一个典型的场景就是：在执行某个查询语句之前需要执行一些预备的动作。另一个场景就是 insert 语句之后拿到 自增列的 ID：

var dataSetFun = @@sql() <%
    set character_set_connection = 'utf8';
    select * from my_option;
%>

return dataSetFun();

在多条查询语句的场景下，衍生出多条 SQL 结果如何处理的问题。默认情况下只会返回最后一个 SQL 语句的结果。

用户可以根据自己的选项，通过 DataQL 提供的 hint FRAGMENT_SQL_MULTIPLE_QUERIES 来控制结果的处理模式。