文章目录
- 前言
- 一、GroovyShell
- 二、maven
- 三、解决方案
- 四、关键代码
- 4.1 数据库配置表(pg)
- 4.2 入参
- 4.3 分页查询
- 总结
前言
项目背景:查询多个表的数据列表和详情,但不想创建过多的po、dao、resp等项目文件。
一、GroovyShell
Apache Groovy是一种强大的、可选的类型和动态语言,具有静态类型和静态编译功能,用于Java平台,旨在通过简洁、熟悉和易于学习的语法提高开发人员的生产力。它可以与任何Java程序顺利集成,并立即为您的应用程序提供强大的功能,包括脚本功能、领域特定语言创作、运行时和编译时元编程以及函数式编程。
二、maven
<dependency><groupId>org.codehaus.groovy</groupId><artifactId>groovy-all</artifactId><version>2.4.7</version></dependency>
三、解决方案
- 数据存储sql(条件查询)
- 根据资源名称和条件入参查询sql
- GroovyShell获取sql
- 查询数据
四、关键代码
4.1 数据库配置表(pg)
INSERT INTO "data_resource"."resource_query_config" ("id", "resource_code", "resource_desc", "resource_sql") VALUES ('8a8ae4db8a1bf1cf018a1c1c0656004e', 'hospital_info', '医院-详情查询', 'def infoSql(String id) {StringBuilder sb = new StringBuilder();sb.append(" SELECT A.id,A.pac,A.name,A.address,A.levelcode,A.ownshipcode,A.area,A.buildingarea,B.respoperson,B.fillinpersontel,B.powersupplycode,B.watersupplycode,B.heatsupplycode,B.commsupportcode,B.plantypecode FROM hel_helthorg_p A ")
sb.append("LEFT JOIN helthorg_p_bu B ON A.id = B.gid ").append(" WHERE A.ID = ''").append(id).append("''");return sb.toString();
}');
INSERT INTO "data_resource"."resource_query_config" ("id", "resource_code", "resource_desc", "resource_sql") VALUES ('ff80808189914fbe018996854a420001', 'hospital_page', '医院-分页查询', 'import org.apache.commons.lang3.StringUtilsdef pageListSql(Map<String, Object> map) {StringBuilder sb = new StringBuilder();sb.append(" SELECT A.id, A.name,d.featurename,A.address,A.pac,B.respoperson,B.fillinpersontel,A.longitude,A.latitude ").append(" FROM helthorg_p A LEFT JOIN helthorg_p_bu B ON A.id = B.gid ").append(" LEFT JOIN code_feature d ON A.featurecode = d.featurecode WHERE a.isdeleted = ''0'' ");if (StringUtils.isNotBlank(map.get("distCode"))) {sb.append(" AND A.pac like :distCode ")}if (StringUtils.isNotBlank(map.get("resName"))) {sb.append(" AND A.NAME like :resName ")}return sb.toString();
}');
INSERT INTO "data_resource"."resource_query_config" ("id", "resource_code", "resource_desc", "resource_sql") VALUES ('ff80808189914fbe018996854a420013', 'hotel_info', '宾馆饭店-详情查询', 'def infoSql(String id) {StringBuilder sb = new StringBuilder();sb.append(" SELECT A.id, A.name,A.address,A.pac,A.ownshipcode as GAT_OWNSHIPCODE,A.starcode as HOTL_STARCODE,").append(" A.area,A.buildingarea,B.roomnum,B.bednum,B.meetmaxhold,B.respoperson,B.fillinpersontel,B.powersupplycode,B.watersupplycode,B.heatsupplycode,B.commsupportcode,B.plantypecode,A.longitude,A.latitude ").append(" FROM hotel_p A LEFT JOIN hotel_p_bu B ON A.id = B.gid ").append(" WHERE A.ID = ''").append(id).append("''");return sb.toString();
}');
INSERT INTO "data_resource"."resource_query_config" ("id", "resource_code", "resource_desc", "resource_sql") VALUES ('ff80808189914fbe018996854a420012', 'hotel_page', '宾馆饭店-分页查询', 'import org.apache.commons.lang3.StringUtilsdef pageListSql(Map<String, Object> map) {StringBuilder sb = new StringBuilder();sb.append(" SELECT A.id, A.name,A.address,A.pac,B.respoperson,B.fillinpersontel,A.longitude,A.latitude ").append(" FROM hotel_p A LEFT JOIN hotel_p_bu B ON A.id = B.gid ").append(" WHERE a.isdeleted = ''0'' ");if (StringUtils.isNotBlank(map.get("distCode"))) {sb.append(" AND A.pac like :distCode ")}if (StringUtils.isNotBlank(map.get("resName"))) {sb.append(" AND A.NAME like :resName ")}return sb.toString();
}');
4.2 入参
@QueryField 为封装jpa查询注解
/*** 资源查询类*/
@Data
public class ResourceQO extends PageQO {/*** 资源标识*/private String resCode;/*** 数据主键*/private List<String> id;/*** 行政区划编码*/@QueryField(type = QueryType.RIGHT_LIKE)private String distCode;/*** 资源名称*/@QueryField(type = QueryType.FULL_LIKE)private String resName;}
4.3 分页查询
public PageResult<Map<String, Object>> pageList(ResourceQO qo){//根据条件查询并拼接配置表数据Optional<ResourceQueryConfigPO> rqc = dao.findByResourceCode(qo.getResCode());BizPreconditions.checkArgumentNoStack(rqc.isPresent(), "资源标识不存在");// 处理区划编码;查询当前区划下的所有数据,截取,右 likeqo.setDistCode(processDistCode(qo.getDistCode()));// 动态获取SQLGroovyShell groovyShell = new GroovyShell();//装载解析脚本代码Script script = groovyShell.parse(rqc.get().getResourceSql());//执行String json = JsonUtil.of(qo);Map<String, Object> map = JsonUtil.ofMap(json, String.class, Object.class);String pageSql = (String) script.invokeMethod("pageListSql", map);String countSql = " select count(*) from ( " + pageSql +") as pc ";//jpa执行分页查询sql,并封装map返回Page<Map<String, Object>> pageList = dao.executeNativePageQuery(pageSql, countSql, qo);return PageAdapter.adapter(pageList, p -> p.getContent());}
总结
案例中有很多自定义封装的类,下面给出GroovyShell简单示例
SpringContextUtil
和neTypeToHdTypeServiceImpl
都是spring注入的bean
- SpringContextUtil是获取bean的通用工具,可参考 SpringBoot 获取bean
- NeTypeToHdTypeServiceImpl是具体业务服务
@GetMapping("/v1/test/{neId}")public Result<List<HdTypeResp>> test(@PathVariable Integer neId){//创建GroovyShellGroovyShell groovyShell = new GroovyShell();//装载解析脚本代码Script script = groovyShell.parse("package groovy\n" +"\n" +"import com.gsafety.bg.si.manage.service.NeTypeToHdTypeService\n" +"import com.gsafety.bg.si.manage.service.util.SpringContextUtil\n" +"\n" +"void HelloWorld(){\n" +" println \"\\033[33mhello world\\033[0m\"\n" +"}\n" +"\n" +"def findHdIdsByNeId(Integer neId) {\n" +" NeTypeToHdTypeService service = SpringContextUtil.getBean(\"neTypeToHdTypeServiceImpl\")\n" +" return service.findHdIdsByNeId(neId);\n" +"}\n");//执行HelloWorldscript.invokeMethod("HelloWorld", null);//执行findHdIdsByNeIdList<HdTypeResp> resps = (List<HdTypeResp>)script.invokeMethod("findHdIdsByNeId", neId);resps.forEach(r->{System.out.println("\033[32m"+r+"\033[0m");});return Result.success(resps);}
输出结果: