本项目主要用于演示 Bean Searcher 与 mybatis 融合,既支持 bs 几乎所有功能, 又基于 mybatis 去查询.
改动 1 处:
- 底层改成了 mybatis 执行
解决 2 个问题:
- bs 不支持事务, 解决.
- bs 不支持 mybatis 拦截器, 解决.
原理: 不变更 bs 源码的基础上,通过继承与覆盖bean的方式加以改造,将 bs 的底层改成了 mybatis 执行.
目的: 最初是因为数据范围控制拦截器, 需要写两份, 一份是 mybatis, 另一份是 bs 的拦截器, 很是苦恼, 虽然通过设计只需要写一份, 但是存在的风险和代码债务很重, 并且存在不支持事务问题.
原:
.field(Employee::getName, "j").op(Contain.class)
改:
.field(Employee::getName, "j").op(MyContain.class)
或
.field(Employee::getName, "j").op(Ops.Contain)
( Ops.Contain 已经被定义为指向 MyContain.class, 更多请查看 com.xunmo.bs.config.bs.Ops )
已知:
1. 超时项的设置, 是失效的, 超时时间这个功能不兼容.
2. 不支持 bean Search 多数据源, 请使用 mybatis 的多数据源, 这个需要自行测试兼容性.
3. 不支持 MapUtils.builder().sql() 方法, 如果无法避免使用 sql() , 请使用重写后的 Bs.builder().sql(), 并且原语法
.sql("($1 in (?) or $2 like '%' || ? || '%')"
需要更正为
.sql("($1 in (#{aaa}) or $2 like '%' || ${胡乱定义即可} || '%')"
参数名胡乱定义即可, 支持 #/?
1. 访问请求,调用 Bean Searcher框架提供API
2. mybatis 拦截器打印日志
3. 查看日志,完成本项目目的。
实际请求
请求链接:
POST http://localhost/test/project/list
请求头:
Content-Type: application/x-www-form-urlencoded
请求参数:
areaName_op:il
areaName:其他,市直
beginDate_op:bt
beginDate:1997-01-01,2023-01-01
2023-03-19 16:31:41.156 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.project4sp.global.GlobalAspect-->
【1f8a9c1b5b04471f882abd8dce617d8e】【请求 URL】:POST http://localhost/test/project/list
【1f8a9c1b5b04471f882abd8dce617d8e】【请求方法】:com.xunmo.biz.project.controller.ProjectController.list()
【1f8a9c1b5b04471f882abd8dce617d8e】【请求参数】:{"areaName":["其他,市直"],"areaName_op":["il"],"beginDate":["1997-01-01,2023-01-01"],"beginDate_op":["bt"]}
【1f8a9c1b5b04471f882abd8dce617d8e】【body】:[{}]
2023-03-19 16:31:41.157 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.bs.config.bs.MySqlExecutor--> select count(*) s_count from "T_XM" where ("AREA" in (#{areaName_0}, #{areaName_1})) and ("KGRQ" between #{beginDate_0} and #{beginDate_1})
2023-03-19 16:31:41.195 INFO [1f8a9c1b5b04471f882abd8dce617d8e]p6spy--> Execute statement SQL:select 1
2023-03-19 16:31:41.199 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.current.dataScope.ProjectDataScope--> 进入项目判断权限范围
2023-03-19 16:31:41.223 INFO [1f8a9c1b5b04471f882abd8dce617d8e]p6spy--> Execute statement SQL:SELECT count(*) s_count FROM "T_XM" WHERE ("AREA" IN ('其他', '市直')) AND ("KGRQ" BETWEEN '1997-01-01T00:00:00.000+0800' AND '2023-01-01T00:00:00.000+0800')
2023-03-19 16:31:41.298 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.current.dataScope.ProjectDataScope--> 进入项目判断权限范围
2023-03-19 16:31:41.431 INFO [1f8a9c1b5b04471f882abd8dce617d8e]p6spy--> Execute statement SQL:SELECT "XMID" c_0, "XMMC" c_1, "JDH" c_2, "JSDWID" c_3, "JSDW" c_4, "XMDZ" c_5, "GCLX" c_6, "GCSX" c_7, "AREA" c_8, "GCZLDJ" c_9, "GLDJ" c_10, "GLLB" c_11, "LCS" c_12, "JAF" c_13, "KGRQ" c_14, "WGRQ" c_15, "GCSX1" c_16, "XMJK" c_17, "XMGK" c_18, "JDFZR" c_19, "JDR" c_20, "FGLD" c_21, "XMZT" c_22, "ADDTIME" c_23, "ADDUSER" c_24, (SELECT "REALNAME" FROM "T_UNIT_USER" WHERE "USERID" = "ADDUSER") c_25, "JDFZRXM" c_26, "XMZT1" c_27, "XMZT2" c_28, "BWDJ" c_29, "SJTTL" c_30, "BWS" c_31, "MTCD" c_32, "MTKD" c_33, "YQCD" c_34, "YQKD" c_35, "DCMJ" c_36, "JZWS" c_37, "JZMJ" c_38, "DLDJ" c_39, "XTLX" c_40, "YLDJ" c_41, "CZSL" c_42, "BFB" c_43, "GLLJKD" c_44, "GLQLCD" c_45, "GLQLKD" c_46, "GLSJSD" c_47, "GLQLSJHZ" c_48, "GCGS" c_49, "GQ" c_50, "RYBDSM" c_51, "GLDQSL" c_52, "GLDQYM" c_53, "GLZXQSL" c_54, "GLZXQYM" c_55, "GLHTLJSL" c_56, "GLHTLJYM" c_57, "SDXCLJSL" c_58, "SDXCLJCD" c_59, "GLXSQSL" c_60, "GLXSQYM" c_61, "GLXLQSL" c_62, "GLXLQYM" c_63, "GLGQSL" c_64, "GLGQYM" c_65, "LXGGQSL" c_66, "LXGGQYM" c_67, "DMCZSL" c_68, "DXCZSL" c_69, "GJCZSL" c_70, "ABBREVIATION" c_71, "JDGLDW" c_72, "XGSJ" c_73, "DRSJ" c_74, "SFDR" c_75, (SELECT string_agg("REALNAME", ',') FROM "T_UNIT_USER" WHERE "USERID" IN (SELECT unnest(string_to_array("T_XM"."JDR", ',')))) c_76, (SELECT "REALNAME" FROM "T_UNIT_USER" WHERE "USERID" = "T_XM"."FGLD") c_77, (SELECT "ZT1" FROM "T_XM_ZT" WHERE "XMID" = "T_XM"."XMID" AND ("BDID" = '' OR "BDID" IS NULL) ORDER BY "KSSJ" DESC LIMIT 1) c_78, (SELECT "BFB" FROM "T_XM_ZT" WHERE "XMID" = "T_XM"."XMID" AND ("BDID" = '' OR "BDID" IS NULL) ORDER BY "KSSJ" DESC LIMIT 1) c_79 FROM "T_XM" WHERE ("AREA" IN ('其他', '市直')) AND ("KGRQ" BETWEEN '1997-01-01T00:00:00.000+0800' AND '2023-01-01T00:00:00.000+0800') ORDER BY "XMID" LIMIT 15 OFFSET 0
2023-03-19 16:31:41.446 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.project4sp.global.GlobalAspect-->
【1f8a9c1b5b04471f882abd8dce617d8e】【请求耗时】:290毫秒
【1f8a9c1b5b04471f882abd8dce617d8e】【已分配内存】:453m 【已分配内存中的剩余空间】:286m 【浏览器类型】:UNKNOWN 【操作系统】:UNKNOWN
【1f8a9c1b5b04471f882abd8dce617d8e】【原始User-Agent】:apifox/1.0.0 (https://www.apifox.cn)
2023-03-19 16:31:41.446 INFO [1f8a9c1b5b04471f882abd8dce617d8e]com.xunmo.utils.LogUtil--> ----------------------------------------------------------------
请求链接:
POST http://localhost/test/project/list
请求头:
Content-Type: application/x-www-form-urlencoded
请求参数:
areaName_op:il
areaName:其他,市直
beginDate_op:bt
beginDate:1997-01-01,2023-01-01
buildingArea_op:gt
buildingArea:1
pageNo:1
pageSize:20
orderBy:id:desc
2023-03-19 16:41:26.532 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.project4sp.global.GlobalAspect-->
【3fc2d50c389a419c9c84e0cffd582550】【请求 URL】:POST http://localhost/test/project/list
【3fc2d50c389a419c9c84e0cffd582550】【请求方法】:com.xunmo.biz.project.controller.ProjectController.list()
【3fc2d50c389a419c9c84e0cffd582550】【请求参数】:{"areaName_op":["il"],"buildingArea_op":["gt"],"pageSize":["20"],"orderBy":["id:desc"],"buildingArea":["1"],"beginDate":["1997-01-01,2023-01-01"],"beginDate_op":["bt"],"areaName":["其他,市直"],"pageNo":["1"]}
【3fc2d50c389a419c9c84e0cffd582550】【body】:[{}]
2023-03-19 16:41:26.533 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.bs.config.bs.MySqlExecutor--> select count(*) s_count from "T_XM" where ("JZMJ" > #{buildingArea}) and ("AREA" in (#{areaName_0}, #{areaName_1})) and ("KGRQ" between #{beginDate_0} and #{beginDate_1})
2023-03-19 16:41:26.560 INFO [3fc2d50c389a419c9c84e0cffd582550]p6spy--> Execute statement SQL:select 1
2023-03-19 16:41:26.564 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.current.dataScope.ProjectDataScope--> 进入项目判断权限范围
2023-03-19 16:41:26.625 INFO [3fc2d50c389a419c9c84e0cffd582550]p6spy--> Execute statement SQL:SELECT count(*) s_count FROM "T_XM" WHERE ("JZMJ" > 1.0) AND ("AREA" IN ('其他', '市直')) AND ("KGRQ" BETWEEN '1997-01-01T00:00:00.000+0800' AND '2023-01-01T00:00:00.000+0800')
2023-03-19 16:41:26.676 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.current.dataScope.ProjectDataScope--> 进入项目判断权限范围
2023-03-19 16:41:26.727 INFO [3fc2d50c389a419c9c84e0cffd582550]p6spy--> Execute statement SQL:SELECT "XMID" c_0, "XMMC" c_1, "JDH" c_2, "JSDWID" c_3, "JSDW" c_4, "XMDZ" c_5, "GCLX" c_6, "GCSX" c_7, "AREA" c_8, "GCZLDJ" c_9, "GLDJ" c_10, "GLLB" c_11, "LCS" c_12, "JAF" c_13, "KGRQ" c_14, "WGRQ" c_15, "GCSX1" c_16, "XMJK" c_17, "XMGK" c_18, "JDFZR" c_19, "JDR" c_20, "FGLD" c_21, "XMZT" c_22, "ADDTIME" c_23, "ADDUSER" c_24, (SELECT "REALNAME" FROM "T_UNIT_USER" WHERE "USERID" = "ADDUSER") c_25, "JDFZRXM" c_26, "XMZT1" c_27, "XMZT2" c_28, "BWDJ" c_29, "SJTTL" c_30, "BWS" c_31, "MTCD" c_32, "MTKD" c_33, "YQCD" c_34, "YQKD" c_35, "DCMJ" c_36, "JZWS" c_37, "JZMJ" c_38, "DLDJ" c_39, "XTLX" c_40, "YLDJ" c_41, "CZSL" c_42, "BFB" c_43, "GLLJKD" c_44, "GLQLCD" c_45, "GLQLKD" c_46, "GLSJSD" c_47, "GLQLSJHZ" c_48, "GCGS" c_49, "GQ" c_50, "RYBDSM" c_51, "GLDQSL" c_52, "GLDQYM" c_53, "GLZXQSL" c_54, "GLZXQYM" c_55, "GLHTLJSL" c_56, "GLHTLJYM" c_57, "SDXCLJSL" c_58, "SDXCLJCD" c_59, "GLXSQSL" c_60, "GLXSQYM" c_61, "GLXLQSL" c_62, "GLXLQYM" c_63, "GLGQSL" c_64, "GLGQYM" c_65, "LXGGQSL" c_66, "LXGGQYM" c_67, "DMCZSL" c_68, "DXCZSL" c_69, "GJCZSL" c_70, "ABBREVIATION" c_71, "JDGLDW" c_72, "XGSJ" c_73, "DRSJ" c_74, "SFDR" c_75, (SELECT string_agg("REALNAME", ',') FROM "T_UNIT_USER" WHERE "USERID" IN (SELECT unnest(string_to_array("T_XM"."JDR", ',')))) c_76, (SELECT "REALNAME" FROM "T_UNIT_USER" WHERE "USERID" = "T_XM"."FGLD") c_77, (SELECT "ZT1" FROM "T_XM_ZT" WHERE "XMID" = "T_XM"."XMID" AND ("BDID" = '' OR "BDID" IS NULL) ORDER BY "KSSJ" DESC LIMIT 1) c_78, (SELECT "BFB" FROM "T_XM_ZT" WHERE "XMID" = "T_XM"."XMID" AND ("BDID" = '' OR "BDID" IS NULL) ORDER BY "KSSJ" DESC LIMIT 1) c_79 FROM "T_XM" WHERE ("JZMJ" > 1.0) AND ("AREA" IN ('其他', '市直')) AND ("KGRQ" BETWEEN '1997-01-01T00:00:00.000+0800' AND '2023-01-01T00:00:00.000+0800') ORDER BY c_0 DESC LIMIT 20 OFFSET 0
2023-03-19 16:41:26.729 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.project4sp.global.GlobalAspect-->
【3fc2d50c389a419c9c84e0cffd582550】【请求耗时】:197毫秒
【3fc2d50c389a419c9c84e0cffd582550】【已分配内存】:423m 【已分配内存中的剩余空间】:269m 【浏览器类型】:UNKNOWN 【操作系统】:UNKNOWN
【3fc2d50c389a419c9c84e0cffd582550】【原始User-Agent】:apifox/1.0.0 (https://www.apifox.cn)
2023-03-19 16:41:26.729 INFO [3fc2d50c389a419c9c84e0cffd582550]com.xunmo.utils.LogUtil--> ----------------------------------------------------------------
1. 修改MySQL相关配置: application.properties
2. 运行 Application
加入依赖
<dependency>
<groupId>com.xunmo</groupId>
<artifactId>bean-searcher-adapt-mybatis-starter</artifactId>
<version>4.1.2</version>
</dependency>
加入个人仓库
<repositories>
<!-- 曾玉飞 maven 个人仓库 -->
<repository>
<id>maven-repo-master</id>
<url>https://raw.github.com/zengyufei/maven-repo/master/</url>
<snapshots>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
</snapshots>
</repository>
</repositories>
如果无法获取依赖, 刷新多几次, 因为正常访问 github 不太稳定
1. 打开浏览器访问:http://localhost:8080/ 效果如下:
-
各种复杂条件组合过滤
-
年龄统计(支持多字段统计)
-
任意字段后端排序(点击表头)
-
分页查询功能
-
总条数统计
OK,页面做的虽然粗糙,但是一个列表检索的功能基本上展示了,下面主要看下在后端, Bean Searcher 是如何简化我们的代码。
有同学看到这会想,若要实现以上演示的的可以按照各种条件 组合检索、排序、分页 和 统计 的功能,那后端的代码量至少也得上百行吧。Bean Searcher 告诉你,不用,关键代码,就一句!啥?我怎么不信?请看代码:
@RestController
public class DemoController {
@Autowired
private Searcher searcher;
/**
* 列表检索接口
*/
@GetMapping("/employee/index")
public Object index(HttpServletRequest request) {
// 组合检索、排序、分页 和 统计 都在这一句代码中实现了
return searcher.search(Employee.class, // 指定实体类
MapUtils.flat(request.getParameterMap()), // 收集页面请求参数
new String[] { "age" }); // 统计字段:年龄
}
}
检索条件呢?检索方式呢?排序呢?分页呢?通通都交给 Bean Sarcher 去实现啦,世界突然如此美好!
咦!,这方法的 返回值怎么是 Object,接收参数怎么是 HttpServletRequest,这让我的 文档工具 Swagger 怎么用?
可能看到此处很多人都由此疑问,实际上这些与 Bean Searcher 都没有关系,它只是需要一个 Map<String, Object>
类型的参数,其它的你爱咋写就咋写,比如你可以把它等效的写成这样:
@GetMapping("/employee/index")
public SearchResult<Employee> index1(String name, String department, Integer page, Integer size, String sort, String order,
@RequestParam(value = "name-op", required = false) String name_op,
@RequestParam(value = "name-ic", required = false) String name_ic,
@RequestParam(value = "age-0", required = false) Integer age_0,
@RequestParam(value = "age-1", required = false) Integer age_1,
@RequestParam(value = "age-op", required = false) String age_op,
@RequestParam(value = "department-op", required = false) String department_op,
@RequestParam(value = "department-ic", required = false) String department_ic,
@RequestParam(value = "entryDate-0", required = false) String entryDate_0,
@RequestParam(value = "entryDate-1", required = false) String entryDate_1,
@RequestParam(value = "entryDate-op", required = false) String entryDate_op) {
// 使用 MapUtils 构建检索参数
Map<String, Object> params = MapUtils.builder()
.field(Employee::getName, name).op(name_op).ic(name_ic)
.field(Employee::getAge, age_0, age_1).op(age_op)
.field(Employee::getDepartment, department).op(department_op).ic(department_ic)
.field(Employee::getEntryDate, entryDate_0, entryDate_1).op(entryDate_op)
.orderBy(sort, order)
.page(page, size)
.build();
// 组合检索、排序、分页 和 统计 都在这一句代码中实现了
return searcher.search(Employee.class, params, new String[] { "age" });
}
因为该例支持的参数比较多,所以这种写法看起来就稍微臃肿一点,但 实际检索的地方仍只是最后一行代码!
至于为什么可以支持这么多的参数,请参阅 Bean Searcher 的文档的参数章节,本例重在体验,具体细节不做讨论。
细心的同学会发现在上述代码里用到一个 Employee 这个类。没错,它就是用来告诉 bean-searcher 如何与数据库字段映射的一个实体类:
@SearchBean(
tables = "employee e, department d", // 员工表 与 部门表
joinCond = "e.department_id = d.id" // 连接条件
)
public class Employee {
@DbField("e.id")
private Long id;
@DbField("e.name")
private String name;
@DbField("e.age")
private Integer age;
@DbField("d.name")
private String department;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")
@DbField("e.entry_date")
private Date entryDate;
// Getter and Setter ...
}
Bean Searcher 2.x 版本,已经实现了 spring-boot-starter 化,所在,在spring-boot 项目里,它只需要在application.properties
里配几个必须的信息即可:
另外,在 IDEA 里,bean-searcher的配置是有提示的哦:
- Bean Searcher 设计的目标并不是替代某个ORM框架,它只是为了弥补现有ORM框架在复杂列表检索中的不便,实际项目中,配合使用它们,效果或会更好。
- 本例只是 Bean Searcher 在联表检索中的一个简单的演示,更多用法,请参阅: https://bs.zhxu.cn
- 看完这些,大家有没有觉得 Bean Searcher 正好可以帮到你呢?如果是,就点个 Star 吧 ^_^
- Fork Bean Searcher 仓库
- 新建 Feat_xxx 分支
- 提交代码
- 新建 Pull Request