跳转到内容
Trantor 文档

TSQL用户指南

1. 基础

构造TSQL有两种方式，一种是通过我们的DSL，一种是通过字符串拼接方式

推荐使用DSL方式

1.1 使用TSQL DSL

使用TSQL-DSL的理由

  • 易于维护，SQL维护简单，采用TSQL DSL拼装SQL，后期易于维护，相对于字符串拼接修改不易报错

  • 安全，能有效防止SQL注入

  • 规范，容易规范代码

  • 高效，不需要单独传递SQL参数，TSQL DSL可以直接获取params，查询时直接走预编译模式。

  • 灵活，TSQL DSL 会自动给字段拼接反引号，可以做一些动态SQL的拼接。

目前TSQL DSL只提供了基于模型的查询接口，后期有需求将继续提供create update接口，上面列举了一些简单的场景，使用中有其他场景不知道怎么使用TSQL DSL构造TSQL查询语句欢迎咨询DataStore。TSQL DSL主要是基于开源持久层框架JOOQ进行了简单的改造，基本用法和JOOQ保持一致。

1.2 DSL使用示例

1.2.1 简单示例

TSQL：想要的T-SQL查询语句

select id, siteName from WarehouseExtBO where id = 1;

DSL：通过DSL构造TSQL查询语句，获取查询结果

Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field))
        .select(TSQL.field(WarehouseExtBO.siteName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.id_field).eq(1));
  或者
Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field), TSQL.field(WarehouseExtBO.siteName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.id_field).eq(1));
  //查询：
  List<WarehouseExtBO> result =  DS.findAll(query);
  ....

TSQL：想要的T-SQL查询语句

  select * from WarehouseExtBO;

DSL：通过DSL构造TSQL查询语句

Query query = TSQL.selectFrom(WarehouseExtBO.class);
  或者
Query query = TSQL.select().from(WarehouseExtBO.class);

TSQL：想要的T-SQL查询语句

select id, siteName from WarehouseExtBO where id = 1 order by createdAt;

DSL：通过DSL构造TSQL查询语句，获取查询结果

Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field))
        .select(TSQL.field(WarehouseExtBO.siteName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.id_field).eq(1))
        .orderBy(TSQL.field(WarehouseExtBO.createdAt_field));
  //查询：
  List<WarehouseExtBO> result =  DS.findAll(query);
  ....

TSQL：想要的T-SQL查询语句

select id, siteName from WarehouseExtBO where id = 1 order by createdAt desc;

DSL：通过DSL构造TSQL查询语句，获取查询结果

Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field))
        .select(TSQL.field(WarehouseExtBO.siteName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.id_field).eq(1))
        .orderBy(TSQL.field(WarehouseExtBO.createdAt_field).desc());
  //查询：
  List<WarehouseExtBO> result =  DS.findAll(query);
  ....

TSQL：想要的T-SQL查询语句

select id, siteName from WarehouseExtBO where id = 1 group by createdAt;

DSL：通过DSL构造TSQL查询语句，获取查询结果

Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field))
        .select(TSQL.field(WarehouseExtBO.siteName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.id_field).eq(1))
        .groupBy(TSQL.field(WarehouseExtBO.createdAt_field));
  //查询：
  List<WarehouseExtBO> result =  DS.findAll(query);
  ....

TSQL：想要的T-SQL查询语句

select count(1) from WarehouseExtBO;

DSL：通过DSL构造TSQL查询语句，获取查询结果

Query query = TSQL.selectCount().from(WarehouseExtBO.class);
  //查询：
  IntResult result =  DS.count(query);
  ....

1.2.2 包含Link相关字段的关联查询

TSQL：想要的T-SQL查询语句

select
  `*`, `parentSite`.`*`, `person`.`personName`
from
  md_SiteBO
where `person`.`personName` = '张三';

DSL：通过DSL构造TSQL查询语句

Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(WarehouseExtBO.parentSite_field, "*"))
        .select(TSQL.field(WarehouseExtBO.person_field, PersonBO.personName_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.person_field, PersonBO.personName_field)
               .eq("张三"));

TSQL：想要的T-SQL查询语句

  select
    id
  from
    WarehouseExtBO
  where companyEntity.entityName = 'entityName' and channel.channelName = 'channelName';

DSL：通过DSL构造TSQL查询语句

Query query = TSQL.select(TSQL.field(WarehouseExtBO.id_field))
        .from(WarehouseExtBO.class)
        .where(TSQL.field(WarehouseExtBO.companyEntity_field, EntityBO.entityName_field)
               .eq("entityName"))
        .and(TSQL.field(WarehouseExtBO.channel_field, ChannelBO.channelName_field)
             .eq("channelName"));

2. 示例

场景一: 两级关联模型查询

模型定义

  • OrderBO
@Model(name = "Order")
public class OrderBO extends BaseModel {


    //**************  存储字段 *************


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String orderTitle;


    //**************  计算字段 *************


    @LookupMeta
    @Field(name = "Order line list")
    private List<OrderLineBO> orderLineList;


}
  • OrderLineBO
@Model(name = "Order line")
public class OrderLineBO extends BaseModel {


    //**************  存储字段 *************


  @LinkMeta
    @Field(name = "Order")
    private OrderBO order;


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String orderLineTitle;
}

API使用

创建: 创建主模型 + 子模型

  • DS API写法
// 下面这段逻辑适用于Func
Long orderId = DS.nextId(OrderBO.class);
orderToCreate.setOrderId(orderId);


for (OrderLineBO orderLineToCreate : orderLineListToCreate) {
  Long orderLineId = DS.nextId(OrderLineBO.class);
    orderLineToCreate.setId(orderLineId);
    orderLineToCreate.setOrder(orderToCreate);
}


// 持久化主模型
DS.create(orderToCreate);


// 持久化子模型
DS.create(orderLineListToCreate);

  • 生成的T-SQL

  • 实际执行的SQL

  • JSON 格式结果集

更新: 更新主子模型 + 子模型

  • DS API写法
// 持久化主模型
DS.update(orderToUpdate);


if (orderToUpdate.getOrderLineList() == null
    || orderToUpdate.getOrderLineList().isEmpty()) {
  throw new BusinessException("Order.orderLineListCantBeEmpty")
}


// 持久化子模型
DS.update(orderToUpdate.getOrderLineList());

  • 生成的T-SQL

  • 实际执行的SQL

  • JSON 格式结果集

对象查询: 通过主模型ID查询主模型，会自动带出当前Link和LinkMany模型的id，不会自动带出Lookup类型的模型id

  • DS API写法
OrderBO resultOrder = DS.findById(OrderBO.class, id);
  • 生成的T-SQL
SELECT * FROM `trade_OrderBO` WHERE `id` = '<id>';
  • 实际执行的SQL
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (`id` = '1')
    AND `isDeleted` = 0;
  • JSON 格式结果集
{
    "requestId": "0jt6q9qno3",
    "err": null,
    "res": {
        "selectResult": [
            {
                "id": 1,
                "orderTitle": "order1",
                "updatedAt": 1614568392000,
                "createdAt": 1614568392000,
                "isDeleted": false,
                "deletedAt": 0,
                "updatedBy": null,
                "createdBy": null
            }
        ]
    },
    "success": true
}

对象查询: 通过主模型ID查询主子模型数据

  • DS API写法
OrderBO resultOrder = DS.findOne(OrderBO.class,
                                 "*, `orderLineList`.*",
                                 "`id` = ?",
                                 order.getId());
  • TSQL-DSL写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderBO.orderLineList_field, "*"))
        .from(OrderBO.class)
        .where(TSQL.field(OrderBO.id_field).eq(order.getId()));


OrderBO result = DS.findOne(query);
  • 生成的T-SQL
SELECT *, `orderLineList`.* FROM `trade_OrderBO` WHERE `id` = '<id>';
  • 实际执行的SQL
--查询主模型
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (`id` = '1')
    AND `isDeleted` = 0;


--根据主模型id查询子模型（orderLineList为主模型上的lookup字段，关联字段在主表上）
SELECT
    `order` AS `order`,
    `id`,
    `orderLineTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`,
    `order` AS `order`
FROM
    `trade__order_line_b_o`
WHERE
    (`order` IN (1))
    AND `isDeleted` = 0;
  • JSON 格式结果集
{
    "requestId": "ayxvkmsaac",
    "err": null,
    "res": {
        "selectResult": [
            {
                "id": 1,
                "orderTitle": "order1",
                "updatedAt": 1614568392000,
                "createdAt": 1614568392000,
                "isDeleted": false,
                "deletedAt": 0,
                "updatedBy": null,
                "createdBy": null,
                "orderLineList": [
                    {
                        "order": {
                            "id": 1
                        },
                        "id": 1,
                        "orderLineTitle": "orderLineTitle1",
                        "updatedAt": 1614568392000,
                        "createdAt": 1614568392000,
                        "isDeleted": false,
                        "deletedAt": 0,
                        "updatedBy": null,
                        "createdBy": null
                    }
                ]
            }
        ]
    },
    "success": true
}

对象查询: 通过子模型ID反查主子模型

  • DS API写法
OrderLineBO resultOrderLine = DS.findOne(OrderLineBO.class,
                                 "*, order.*",
                                 "id = ?",
                                 orderLine.getId());
  • TSQL-DSL写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderLineBO.order_field, "*"))
        .from(OrderLineBO.class)
        .where(TSQL.field(OrderLineBO.id_field).eq(orderLine.getId()));


OrderLineBO result = DS.findOne(query);
  • 生成的T-SQL
SELECT *, `order`.* FROM `trade_OrderLineBO` WHERE `id` = '<id>';
  • 实际执行的SQL
--查询trade_OrderLineBO模型，同时查询link字段order
SELECT
    `id`,
    `orderLineTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`,
    `order` AS `order`
FROM
    `trade__order_line_b_o`
WHERE
    (`id` = '1')
    AND `isDeleted` = 0;


--根据上一步查到的order id查询OrderBO模型表
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (`id` IN (1))
    AND `isDeleted` = 0;
  • JSON 格式结果集
{
    "requestId": "500d1b8lq2",
    "err": null,
    "res": {
        "selectResult": [
            {
                "id": 1,
                "orderLineTitle": "orderLineTitle1",
                "updatedAt": 1614568392000,
                "createdAt": 1614568392000,
                "isDeleted": false,
                "deletedAt": 0,
                "updatedBy": null,
                "createdBy": null,
                "order": {
                    "id": 1,
                    "orderTitle": "order1",
                    "updatedAt": 1614568392000,
                    "createdAt": 1614568392000,
                    "isDeleted": false,
                    "deletedAt": 0,
                    "updatedBy": null,
                    "createdBy": null
                }
            }
        ]
    },
    "success": true
}

列表查询: 通过主模型条件查询主子模型

  • DS API写法
List<OrderBO> resultOrderList = DS.findAll(OrderBO.class,
                                  "*, `orderLineList`.*",
                                  "`orderTitle` like ?",
                                   order.getOrderTitle() + "%");
  • TSQL-DSL写法
TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderBO.orderLineList_field, "*"))
        .from(OrderBO.class)
        .where(TSQL.field(OrderBO.orderTitle_field).startsWith(order.getOrderTitle()));
List<OrderBO> orderBOList = DS.findAll(query);
  • 生成的T-SQL
SELECT *, `orderLineList`.* FROM `trade_OrderBO` WHERE `orderTitle` like 'order%';
  • 实际执行的SQL
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (`orderTitle` LIKE 'order%')
    AND `isDeleted` = 0;
  • JSON 格式结果集
{
    "requestId": "2tgrmo38c0",
    "err": null,
    "res": {
        "selectResult": [
            {
                "id": 1,
                "orderTitle": "order1",
                "updatedAt": 1614568392000,
                "createdAt": 1614568392000,
                "isDeleted": false,
                "deletedAt": 0,
                "updatedBy": null,
                "createdBy": null,
                "orderLineList": [
                    {
                        "order": {
                            "id": 1
                        },
                        "id": 1,
                        "orderLineTitle": "orderLineTitle1",
                        "updatedAt": 1614568392000,
                        "createdAt": 1614568392000,
                        "isDeleted": false,
                        "deletedAt": 0,
                        "updatedBy": null,
                        "createdBy": null
                    }
                ]
            }
        ]
    },
    "success": true
}

列表查询: 通过主子模型的条件查询主子模型

  • DS API写法
List<OrderBO> resultOrderList = DS
            .findAll(
                OrderBO.class,
                "*, orderLineList.*",
                "orderTitle like ? and orderLineList.orderLineTitle like ?",
                 order.getOrderTitle() + "%", "%Title"
            );
  • TSQL-DSL
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderBO.orderLineList_field, "*"))
        .from(OrderBO.class)
        .where(TSQL.field(OrderBO.orderTitle_field).startsWith(order.getOrderTitle() + "%"))
        .and(TSQL.field(OrderBO.orderLineList_field,
                        OrderLineBO.orderLineTitle_field).endsWith("Title"));


List<OrderBO> orderBOList = DS.findAll(query);
  • 生成的T-SQL
SELECT *, `orderLineList`.* FROM `trade_OrderBO`
WHERE `orderTitle` LIKE 'order%'
AND `orderLineList`.`orderLineTitle` LIKE '%Title';
  • 实际执行的SQL
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (
        `orderTitle` LIKE 'order%'
        AND `id` IN (
            SELECT
                `order`
            FROM
                `trade__order_line_b_o`
            WHERE
                `orderLineTitle` LIKE '%Title'
        )
    )
    AND `isDeleted` = 0;

列表查询: 通过is null条件查询主子模型

  • DS API写法
List<OrderBO> resultOrderList = DS
            .findAll(
                OrderBO.class,
                "*, orderLineList.*",
                "orderTitle is null and orderLineList.orderLineTitle is null"
            );
  • TSQL-DSL写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderBO.orderLineList_field, "*"))
        .from(OrderBO.class)
        .where(TSQL.field(OrderBO.orderTitle_field).isNull())
        .and(TSQL.field(OrderBO.orderLineList_field,
                        OrderLineBO.orderLineTitle_field).isNull());


List<OrderBO> orderBOList2 = DS.findAll(query);
  • 生成的T-SQL
SELECT *, `orderLineList`.* FROM `trade_OrderBO`
WHERE `orderTitle` is null
AND `orderLineList`.`orderLineTitle` is null ;
  • 实际执行的SQL
SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (
        `orderTitle` IS NULL
        AND `id` IN (
            SELECT
                `order`
            FROM
                `trade__order_line_b_o`
            WHERE
                `orderLineTitle` IS NULL
        )
    )
    AND `isDeleted` = 0;

分页查询: 通过主模型条件查询主子模型

  • DS API写法
Paging<OrderBO> paging = DS.paging(OrderBO.class,
                                   "*, orderLineList.*",
                                   "orderTitle like ?",
                                   new Page(1, 20), "%" + order.getOrderTitle());
  • TSQL-DSL写法
//获取分页数据
Page page = new Page(1, 20);
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderBO.orderLineList_field, "*"))
        .from(OrderBO.class)
        .where(TSQL.field(OrderBO.orderTitle_field).endsWith(order.getOrderTitle()))
        .limit(page.first(), page.limit());
List<OrderBO> result =  DS.findAll(query);
Paging<OrderBO> paging = new Paging<>();
paging.setData(result);


//是否需要查询分页总数量
if (!page.isSkipCount()) {
    Query queryTotal = TSQL.selectCount()
            .from(OrderBO.class)
            .where(TSQL.field(OrderBO.orderTitle_field).endsWith(order.getOrderTitle()));
    long total =  DS.count(queryTotal);
    paging.setTotal(total);
}
return paging;
  • 生成的T-SQL
SELECT count(*) FROM `trade_OrderBO` WHERE `orderTitle` like '%order';
SELECT *, orderLineList.* FROM `trade_OrderBO` WHERE `orderTitle` like '%order' LIMIT 0,20;
  • 实际执行的SQL
SELECT COUNT(*) FROM `trade__order_b_o` WHERE ( `orderTitle` LIKE '%order' ) AND `isDeleted` = 0 ;


SELECT
    `id`,
    `orderTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`
FROM
    `trade__order_b_o`
WHERE
    (`orderTitle` LIKE 'order%')
    AND `isDeleted` = 0
LIMIT
    0, 20;


SELECT
    `order` AS `order`,
    `id`,
    `orderLineTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`,
    `order` AS `order`
FROM
    `trade__order_line_b_o`
WHERE
    (`order` IN (1))
    AND `isDeleted` = 0;

场景二: 多层嵌套查询

图示

多层嵌套查询

模型定义

  • ContractBO
@Model(name = "Contract")
public class ContractBO extends BaseModel {


    //**************  存储字段 *************


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String contractTitle;


    //**************  计算字段 *************


    @LookupMeta
    @Field(name = "Order list")
    private List<OrderBO> orderList;


    @LookupMeta
    @Field(name = "Order line list")
    private List<OrderLineBO> orderLineList;


}
  • OrderBO
@Model(name = "Order")
public class OrderBO extends BaseModel {


    //**************  存储字段 *************


    @LinkMeta
    @Field(name = "Contract")
    private ContractBO contract;


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String orderTitle;


    //**************  计算字段 *************


    @LookupMeta
    @Field(name = "Order line list")
    private List<OrderLineBO> orderLineList;


}
  • OrderLineBO
@Model(name = "Order line")
public class OrderLineBO extends BaseModel {


    //**************  存储字段 *************


    @LinkMeta
    @Field(name = "Contract")
    private ContractBO contract;


  @LinkMeta
    @Field(name = "Order")
    private OrderBO order;


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String orderLineTitle;
}

API使用

对象查询: 通过主模型ID查询多层主子模型

  • DS API写法
ContractBO resultContract = DS.findOne(ContractBO.class,
                                       "*, `orderList`.*, `orderLineList`.*",
                                       "`id` = ?",
                                       contract.getId());
  • TSQL-DSL写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(ContractBO.orderList_field, "*"))
        .select(TSQL.field(ContractBO.orderLineList_field, "*"))
        .from(ContractBO.class)
        .where(TSQL.field(ContractBO.id_field).eq(contractBO.getId()));


ContractBO resultContract = DS.findOne(query);
  • 生成的T-SQL
SELECT *, `orderList`.*, `orderLineList`.* FROM `trade_ContractBO` WHERE `id` = '1'
  • 实际执行的SQL
--查询trade_ContractBO模型表
SELECT `id` , `contractTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy`
FROM `trade__contract_b_o`
WHERE ( `id` = '1' ) AND `isDeleted` = 0 ;
--查询orderList关联的模型表
SELECT `contract` AS `contract` , `id` , `orderLineTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `order` AS `order` , `contract` AS `contract`
FROM `trade__order_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;
--查询orderLineList关联的模型表
SELECT `contract` AS `contract` , `id` , `orderTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `contract` AS `contract`
FROM `trade__order_line_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;

对象查询: 通过子模型ID反查多层主子模型

  • DS API写法
OrderLineBO resultOrderLineBO = DS.findOne(OrderLineBO.class,
                                       "*, order.*, contract.*",
                                       "id = ?",
                                       orderLine.getId());
  • TSQL-DSL 写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(OrderLineBO.order_field, "*"))
        .select(TSQL.field(OrderLineBO.contract_field, "*"))
        .from(OrderLineBO.class)
        .where(TSQL.field(OrderLineBO.id_field).eq(orderLineBO.getId()));


OrderLineBO result = DS.findOne(query);
  • 生成的T-SQL
SELECT *, `order`.*, `contract`.* FROM `trade_OrderLineBO` WHERE `id` = '1';
  • 实际执行的SQL
--查询子模型数据，同时查出order,contract关联的模型id
SELECT `id` , `orderLineTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy`
FROM `trade__order_line_b_o`
WHERE ( `id` = '1' ) AND `isDeleted` = 0 ;


--根据第一步查询出的contract id查询trade_ContractBO模型表
SELECT `id` , `contractTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy`
FROM `trade__contract_b_o`
WHERE ( `id` IN (1 ) ) AND `isDeleted` = 0 ;


--根据第一步查询出的order id查询trade_OrderBO模型关联表
SELECT `id` , `orderTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `contract` AS `contract`
FROM `trade__order_b_o`
WHERE ( `id` IN (1 ) ) AND `isDeleted` = 0 ;

列表查询: 通过主模型条件查询多层主子模型

  • DS API写法
List<OrderBO> paging = DS.findAll(ContractBO.class,
                                  "*, orderList.*, orderLineList.*",
                                  "contractTitle like ?",
                                   contractBO.getContractTitle() + "%");
  • TSQL-DSL写法
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(ContractBO.orderList_field, "*"))
        .select(TSQL.field(ContractBO.orderLineList_field, "*"))
        .from(ContractBO.class)
        .where(TSQL.field(ContractBO.contractTitle_field)
               .startsWith(contractBO.getContractTitle()));


ContractBO resultContract = DS.findAll(query);
  • 生成的T-SQL
SELECT *, `orderList`.*, `orderLineList`.*
FROM `trade_ContractBO`
WHERE contractTitle like 'contractTitle%';
  • 实际执行的SQL
--根据条件查询主模型数据，
SELECT `id` , `contractTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy`
FROM `trade__contract_b_o`
WHERE ( `contractTitle` LIKE 'contractTitle%' ) AND `isDeleted` = 0 ;
--根据trade_ContractBO模型id过滤查询trade_OrderLineBO模型表
SELECT `contract` AS `contract` , `id` , `orderLineTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `order` AS `order` , `contract` AS `contract`
FROM `trade__order_line_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;
--根据trade_ContractBO模型id过滤查询trade_OrderBO模型表
SELECT `contract` AS `contract` , `id` , `orderTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `contract` AS `contract`
FROM `trade__order_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;

分页查询: 通过主模型条件查询多层主子模型

  • DS API写法
Paging<ContractBO> resultContractPaging = DS
        .paging(
            ContractBO.class,
            "*, orderList.*, orderLineList.*",
            "id = ?",
            new Page(1, 20),
            contract.getId()
        );
  • TSQL-DSL
//获取分页数据
Page page = new Page(1, 20);
Query query = TSQL.select(TSQL.field("*"))
        .select(TSQL.field(ContractBO.orderList_field, "*"))
        .select(TSQL.field(ContractBO.orderLineList_field, "*"))
        .from(ContractBO.class)
        .where(TSQL.field(ContractBO.id_field).eq(contract.getId()))
        .limit(page.first(), page.limit());
List<ContractBO> result =  DS.findAll(query);
Paging<OrderBO> paging = new Paging<>();
paging.setData(result);


//是否需要查询分页总数量
if (!page.isSkipCount()) {
    Query queryTotal = TSQL.selectCount()
        .from(ContractBO.class)
        .where(TSQL.field(ContractBO.id_field).eq(contract.getId()))
    long total =  DS.count(queryTotal);
    paging.setTotal(total);
}
return paging;
  • 生成的T-SQL
SELECT count(*) FROM `trade_ContractBO` WHERE id = '1';
SELECT *, `orderList`.*, `orderLineList`.* FROM `trade_ContractBO` WHERE id = '1' LIMIT 0,20;
  • 实际执行的SQL
SELECT COUNT(*) FROM `trade__contract_b_o` WHERE ( `id` = '1' ) AND `isDeleted` = 0 ;


SELECT `id` , `contractTitle` , `updatedAt` , `createdAt
FROM `trade__contract_b_o`
WHERE ( `id` = '1' ) AND `isDeleted` = 0 LIMIT 0  , 20  ;
--根据trade_ContractBO模型id过滤查询trade_OrderLineBO模型表
SELECT `contract` AS `contract` , `id` , `orderLineTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `order` AS `order` , `contract` AS `contract`
FROM `trade__order_line_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;
--根据trade_ContractBO模型id过滤查询trade_OrderBO模型表
SELECT `contract` AS `contract` , `id` , `orderTitle` , `updatedAt` , `createdAt` , `isDeleted` , `deletedAt` , `UpdatedBy` AS `updatedBy` , `CreatedBy` AS `createdBy` , `contract` AS `contract`
FROM `trade__order_b_o`
WHERE ( `contract` IN (1 ) ) AND `isDeleted` = 0 ;

场景三: 单模型

模型定义

  • OrderBO
@Model(name = "Order")
public class OrderBO extends BaseModel {


    //**************  存储字段 *************


    @TextMeta(length = 64)
    @Field(name = "Order title")
    private String orderTitle;


    @Field(name = "count")
    private int count;


     @Field(name = "tax amount")
    private Currency taxAmt;


    @DateTimeMeta(unit = TimeUnit.SECONDS)
    @Field(name = "Ordered Time")
    private Date orderedTime;
}

API使用

创建: 创建单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
Long orderId = DS.nextId(OrderBO.class);
orderToCreate.setId(orderId);
IntResult result = DS.create(orderToCreate);
  • 生成的T-SQL
insert into `trade_OrderBo` (`id`, `orderTitle`, `count`, `orderedTime`, `createdBy`)
values (1, 'this is title', 1000, 1533312000000, 1)
  • 实际执行的SQL
INSERT INTO `trade__order_b_o` (`id`, `orderTitle`, `count`, `orderedTime`, `createdBy`,`isDeleted`, `createdAt` , `updatedAt`)
VALUES (1, 'this is title', 1000, 1533312000000, 1, 0, '2021-03-02 11:58:47' , '2021-03-02 11:58:47')
  • JSON 格式结果集
{
    "requestId": "ey1yii3m4m",
    "err": null,
    "res": {
        "insertesult": [
            1
        ]
    },
    "success": true
}

创建: 批量创建单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
for (OrderBO orderToCreate: orderListToCreate){
  Long orderId = DS.nextId(OrderBO.class);
  orderToCreate.setOrderId(orderId);
}


IntResult result = DS.create(orderListToCreate);
  • 生成的T-SQL
insert into `trade_OrderBo` (`id`, `orderTitle`, `count`, `orderedTime`, `createdBy`)
values (1, 'this is title', 1000, 1533312000000, 1),(2, 'this is title', 1000, 1533312000000, 1),(3, 'this is title', 1000, 1533312000000, 1)
  • 实际执行的SQL
INSERT INTO `trade__order_b_o` (`id`, `orderTitle`, `count`, `orderedTime`, `createdBy`,`isDeleted`, `createdAt` , `updatedAt`)
VALUES (1, 'this is title', 1000, 1533312000000, 1, 0, '2021-03-02 11:58:47' , '2021-03-02 11:58:47'),(2, 'this is title', 1000, 1533312000000, 1, 0, '2021-03-02 11:58:47' , '2021-03-02 11:58:47'),(3, 'this is title', 1000, 1533312000000, 1, 0, '2021-03-02 11:58:47' , '2021-03-02 11:58:47')
  • JSON 格式结果集
{
    "requestId": "ey1yii3m4m",
    "err": null,
    "res": {
        "insertResult": [
            1,2,3
        ]
    },
    "success": true
}

更新: 更新单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
BooleanResult result = DS.update(orderToUpdate);
// 下面这段逻辑适用于Flow
final BooleanResult result = DS.update(orderToUpdate);
  • 生成的T-SQL
update `trade_OrderBo`
set `orderTitle` = 'this is title',`count` = 2000,`updatedBy` = 1
where `id` = 1;
  • 实际执行的SQL
UPDATE `trade__order_b_o`
SET `orderTitle` = 'this is title',`count` = 2000,`updatedBy` = 1,`updatedAt`= '2021-03-02 11:58:47'
WHERE ( `id` = 1 ) AND `isDeleted` = 0 ;
  • JSON 格式结果集
{
    "requestId": "uqpnvpd1tz",
    "err": null,
    "res": {
        "updateResult": {
            "count": 1
        }
    },
    "success": true
}

更新: 根据条件更新单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
IntResult result = DS.update(OrderBO.class, "set `count` = `count` + 1", "`id` = 1");
  • DS DSL写法
Query update = TSQL.update(OrderBO.class)
        .set(TSQL.field(OrderBO.count_field), (Object) TSQL.field(OrderBO.count_field).add(1))
        .where(TSQL.field(OrderBO.id_field).eq(1));
IntResult result = DS.update(update);
  • 生成的T-SQL
UPDATE `trade_OrderBO` SET `count` = `count` + 1 WHERE `id` = 1;
  • 实际执行的SQL
UPDATE `trade__order_b_o` SET `count` = `count` + 1 WHERE ( `id` = 1 ) AND `isDeleted` = 0 ;

更新: 批量更新单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
IntResult result = DS.update(orderListToUpdate);
// 下面这段逻辑适用于Flow
final IntResult result = DS.update(orderToUpdate);
  • 生成的T-SQL
update `trade_OrderBo` set `orderTitle` = 'this is title',`count` = 2000,`updatedBy` = 1 where `id` = 1;
update `trade_OrderBo` set `orderTitle` = 'this is title',`count` = 3000,`updatedBy` = 1 where `id` = 2;
update `trade_OrderBo` set `orderTitle` = 'this is title',`count` = 4000,`updatedBy` = 1 where `id` = 3;
  • 实际执行的SQL
UPDATE `trade__order_b_o` SET `orderTitle` = 'this is title',`count` = 2000,`updatedBy` = 1,`updatedAt`= '2021-03-02 11:58:47' WHERE ( `id` = 1 ) AND `isDeleted` = 0 ;
UPDATE `trade__order_b_o` SET `orderTitle` = 'this is title',`count` = 3000,`updatedBy` = 1,`updatedAt`= '2021-03-02 11:58:47' WHERE ( `id` = 2 ) AND `isDeleted` = 0 ;
UPDATE `trade__order_b_o` SET `orderTitle` = 'this is title',`count` = 4000,`updatedBy` = 1,`updatedAt`= '2021-03-02 11:58:47' WHERE ( `id` = 3 ) AND `isDeleted` = 0 ;
  • JSON 格式结果集
{
    "requestId": "tm5zrnaq4c",
    "err": null,
    "res": {
        "updateResult": {
            "count": 3
        }
    },
    "success": true
}

删除: 更新单模型数据

  • DS API写法
// 下面这段逻辑适用于Func
BooleanResult result = DS.delete(orderToDelete);
// 下面这段逻辑适用于Flow
final BooleanResult result = DS.delete(orderToDelete);
  • 生成的T-SQL
delete from `trade_OrderBo` where `id` = 1
  • 实际执行的SQL
delete from `trade__order_b_o` where ( `id` = 1 ) AND `isDeleted` = 0 ;
  • JSON 格式结果集
{
    "requestId": "fuo7p5pjvm",
    "err": null,
    "res": {
        "deleteResult": {
            "count": 1
        }
    },
    "success": true
}

查询: 通过is null条件查询模型

  • DS API写法
OrderLineBO resultOrderLine = DS.findOne(OrderLineBO.class,
                                 "*",
                                 "orderLineTitle is null");
  • TSQL-DSL
Query query = TSQL.select(TSQL.field("*"))
    .select(TSQL.field(OrderLineBO.order_field, "*"))
    .from(OrderLineBO.class)
    .where(TSQL.field(OrderLineBO.orderLineTitle_field).isNull());


OrderLineBO resultOrderLine = DS.findOne(query);
  • 生成的T-SQL
SELECT *, `order`.* FROM `trade_OrderLineBO` WHERE `orderLineTitle` is null;
  • 实际执行的SQL
SELECT
    `id`,
    `orderLineTitle`,
    `updatedAt`,
    `createdAt`,
    `isDeleted`,
    `deletedAt`,
    `UpdatedBy` AS `updatedBy`,
    `CreatedBy` AS `createdBy`,
    `order` AS `order`
FROM
    `trade__order_line_b_o`
WHERE
    (`orderLineTitle` IS NULL)
    AND `isDeleted` = 0;

场景四: JOIN查询

当模型间不存在Link，LinkMany或者LookUp关系时，但是仍然需要进行关联查询，TSQL也提供了JOIN支持，下面简单举例

普通leftJoin查询

  • TSQL-DSL
Query joinQuery = TSQL.select(TSQL.field("company", "*"))
        .select(TSQL.field("staff", "*"))
        .from(TSQL.table(Company.class).as("company"))
        .leftJoin(TSQL.table(Staff.class).as("staff"))
        .on(TSQL.field("company", Company.name_field).eq(TSQL.field("staff", Staff.staffName_field)))
        .where(TSQL.field("company", Company.code_field).eq("abc"));
        List<Map<String, Object>> result =  DS.find(joinQuery);
  • 生成的TSQL
select company.*,
        staff.*
from base_Company as company
left join base_Staff as staff on company.companyName = staff.staffName
where company.code = 'abc'
  • 实际执行的SQL
select company.*,
        staff.*
from base__company as company
left join base__staff as staff on company.companyName = staff.staffName
where company.code = 'abc'

innerJoin和rightJoin类似

包含分页的Join查询

如果TSQL包含JOIN语句，是无法使用DS类中提供的page相关接口的，可以通过如下方案解决： 分页查询包括两部分，一是查询分页总数，二是查询当前页数据，page接口内部自动生成了查询total的sql，所以在不使用page接口时我们要自己按需查询total (1) 只查询主模型数据 如：

  • TSQL-DSL
Page page = new Page();
Query countQuery = TSQL.selectCount()
        .from(TSQL.table(Company.class).as("company"))
        .leftJoin(TSQL.table(Staff.class).as("staff"))
        .on(TSQL.field("company", Company.name_field).eq(TSQL.field("staff", Staff.staffName_field)))
        .where(TSQL.field("company", Company.code_field).eq("abc"));


long total = DS.count(countQuery);


Query joinQuery = TSQL.select(TSQL.field("company", "*"))
        .from(TSQL.table(Company.class).as("company"))
        .leftJoin(TSQL.table(Staff.class).as("staff"))
        .on(TSQL.field("company", Company.name_field).eq(TSQL.field("staff", Staff.staffName_field)))
        .where(TSQL.field("company", Company.code_field).eq("abc"))
        .limit(page.first(), page.limit());


List<Company> data = DS.findAll(joinQuery);
return new Paging(total, data);
  • 生成的TSQL
select count(1)
from base_Company as company
left join base_Staff as staff on company.companyName = staff.staffName
where company.code = 'abc';


select company.*
from base_Company as company
left join base_Staff as staff on company.companyName = staff.staffName
where company.code = 'abc' limit 1, 10;
  • 实际执行的SQL
select count(1)
from base__company as company
left join base__staff as staff on company.companyName = staff.staffName
where company.code = 'abc';


select company.*
from base__company as company
left join base__staff as staff on company.companyName = staff.staffName
where company.code = 'abc' limit 1, 10;

(2) 既查询主模型数据也查询被JOIN模型的数据 如：

  • TSQL-DSL
Page page = new Page();
        Query countQuery = TSQL.selectCount()
        .from(TSQL.table(Company.class).as("company"))
        .leftJoin(TSQL.table(Staff.class).as("staff"))
        .on(TSQL.field("company", Company.name_field).eq(TSQL.field("staff", Staff.staffName_field)))
        .where(TSQL.field("company", Company.code_field).eq("abc"));
long total = DS.count(countQuery);


Query joinQuery = TSQL.select(TSQL.field("company", "*"))
        .select(TSQL.field("staff", "*"))
        .from(TSQL.table(Company.class).as("company"))
        .leftJoin(TSQL.table(Staff.class).as("staff"))
        .on(TSQL.field("company", Company.name_field).eq(TSQL.field("staff", Staff.staffName_field)))
        .where(TSQL.field("company", Company.code_field).eq("abc"))
        .limit(page.first(), page.limit());


List<Company> data = DS.findAll(joinQuery);
return new Paging(total, data);
  • 生成的TSQL
select count(1)
from base_Company as company
left join base_Staff as staff on company.companyName = staff.staffName
where company.code = 'abc';


select company.*, staff.*
from base_Company as company
left join base_Staff as staff on company.companyName = staff.staffName
where company.code = 'abc' limit 1, 10;
  • 实际执行的SQL
select count(1)
from base__company as company
left join base__staff as staff on company.companyName = staff.staffName
where company.code = 'abc';


select company.*, staff.*
from base__company as company
left join base__staff as staff on company.companyName = staff.staffName
where company.code = 'abc' limit 1, 10;

场景五:怎么使用JSON函数

JSON_CONTAINS

假如现在有下面的数据，期望筛选出username包含”a”值的数据

username [“a”, “b”, “c”]

TSQL DSL组装方式

Query query = TSQL.select(TSQL.field("id"))
        .from(User.class)
        .where(TSQL.condition("JSON_CONTAINS(`username`, \"a\")"));

TSQL

select * from base_User where JSON_CONTAINS(`username`, '"a"');

JSON_CONTAINS过滤多值写法

Query query = TSQL.select(TSQL.field("id"))
        .from(User.class)
        .where(TSQL.condition("JSON_CONTAINS(`nickname`, JSON_ARRAY("1","2","3"))"));

如果值为下面格式，期望筛选出nickname包含1的数据

nickname [1, 2, 3]

TSQL DSL组装方式

Query query = TSQL.select(TSQL.field("id"))
        .from(User.class)
        .where(TSQL.condition("JSON_CONTAINS(`nickname`, 1)"));

TSQL

select * from base_User where JSON_CONTAINS(`nickname`, '1');

JSON_ARRAY_APPEND

assistIds为辅助核算值，期望通过JSON_ARRAY_APPEND处理科目新增辅助核算的时候添加辅助核算值

if(CollectionUtils.isNotEmpty(assistIds)){
    updateSql.set(TSQL.field(VoucherLineBO.assistAccountingIds_field),
            (Object) TSQL.field("JSON_ARRAY_APPEND(`"+VoucherLineBO.assistAccountingIds_field+"`, '$', "+String.join(", '$', ", assistIds.stream().map(Object::toString).collect(Collectors.toSet()))+")", Object.class));
}

TSQL

set `assistAccountingIds` = JSON_ARRAY_APPEND(`assistAccountingIds`, '$', 642035)

查询ES

DS目前会有两种方式查询ES中的数据，一是会根据TSQL生成ES DSL去请求ES服务器,二是通过SearchQuery对象直接生成ES DSL去查询ES服务器，下面的文档也会写出两种方式实现同一功能的不同写法。另外根据业务需求，目前所有的查询都是分页查询。具体的Search API调用方法可以参考文档: 《Search API查询ES》

3. 最佳实践

3.1 查询

对于查询类的TSQL，尽可能使用预编译模式，因为DataStore会缓存原始SQL以及对应的逻辑执行计划，走预编译模式的SQL，只会在第一次请求时候走一次语法树的解析，这个解析过程需要1-5ms的开销，因此推荐大家尽量使用预编译模式。

  • 推荐的DS API写法
List<OrderBO> resultOrderList = DS
            .findAll(
                OrderBO.class,
                "*, orderLineList.*",
                "orderTitle = ?",
                order.getOrderTitle()
            );
  • 不推荐的DS API写法
List<OrderBO> resultOrderList = DS
            .findAll(
                OrderBO.class,
                "*, orderLineList.*",
                "orderTitle = " + order.getOrderTitle()
            );

区别点在于下面的写法直接把condition拼接到了where条件中，没有用?占位符。