通用查询解决方案
数据库表之间关系：
- 一对一 （可以看做一对多的特例）
- 一对多
- 多对多
下图将涵盖了所有关系。
根据restful接口风格，我们最终会落到一个实体上，示例按照b表。其他表同理。
GET https://ip:port/xx/xx/1/0/b
查询的场景：
- 只需要b表的某些元素
- 需要b某些元素，及相关联的a表某些元素。
- 需要b某些元素，及a,c,d某些元素。
思路： 通过数据库的外键，自动生成代码。在新建实体类时，自动查询关联的实体。关联实体查询一层一层传导下去。
通过在返回对象实体上加注解，标识需要返回的字段，及需要关联查询的对象，防止洪泛式关联对象查询。
class B{ 自身属性； 关联的外键对象a； 被关联的对象c集合； 构造方法（）{ 查询自身属性； 查询关联的外键对象a； 查询被关联的对象c集合； } }
生成代码示例如下：
public class BInfo implements Serializable { private static final long serialVersionUID = 1L; public BInfo(){} // 主键 @JsonProperty("id") private Integer id; // 名称 @JsonProperty("name") private String name; // 详情 @JsonProperty("detail") private String detail; // 状态:0-无效，1-有效，2-编辑 @JsonProperty("status") private Integer status; // 外键关联实体类(字段：[ a_id ] 注释：[ ]) // 业务实体(关联表名：tbl_a ) @JsonProperty("tblAInfo") private AInfo aInfo; // 业务实体 集合 [tbl_c][b_id] @JsonProperty("tblCInfoList") private List<CInfo> cInfoList; // 构造方法 public BInfo(SapoDao dao ,B b) throws Exception { super(); //System.err.println("-----new BInfo--------------"); Class<? extends BInfo> thisClass = this.getClass(); JsonIgnoreProperties annotation = thisClass.getAnnotation(JsonIgnoreProperties.class); // 外键关联对象：业务实体(tbl_a) // 如果注解标注不需要该字段，则不用查询该字段 if(annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("tblAInfo"))) { // 不管状态，只用外键查询 // A a = dao.getAById(b.getAId()); A aResult = null; // 外键字段不为空才有去查的意义 if(b.getAId() != null){ // 组建查询条件 A aForQuery = new A(); aForQuery.setId(b.getAId()); // aForQuery.setStatus(A.STATUS_INVALID); // 查询，不可为空 aResult = dao.getA(aForQuery) ; } // 如果能查该外键对应的值，则进行赋值操作 if(aResult !=null){ // 判断该字段是否被注解标识，使用哪个子类 FkPojo fkano = thisClass.getAnnotation(FkPojo.class); // 如果没有被注解标注，需要报错 if (fkano == null || fkano.value().length == 0) { throw new Exception(thisClass.getName() + " -> must have @PkPojo annotation and lenght !=0 "); } // pojo不符合规范也要报错，注解用冒号分开[类名:全类名] Map<String, String> map = new HashMap<String, String>(); for (String s : fkano.value()) { s = s.trim(); String[] split = s.split(":"); if (split == null || split.length != 2) { throw new Exception( thisClass.getName() + " -> @PkPojo annotation format error [pojoName:pojoAllPath] "); } map.put(split[0], split[1]); } //如果没有标识子类使用哪一个，则报错 if(map.get("AInfo")==null){ throw new Exception(thisClass.getName() + " -> @PkPojo annotation has no class: AInfo"); } // 反射出子类，将子类赋值给该对象。 Class<?> c1 = Class.forName(map.get("AInfo")); Constructor<?> declaredConstructor = c1.getDeclaredConstructor(SapoDao.class, A.class); this.aInfo = (AInfo)declaredConstructor.newInstance(dao, aResult); } } this.detail=b.getDetail(); this.id=b.getId(); this.name=b.getName(); this.status=b.getStatus(); // 注解如果将该字段忽略了，就不需要查了。 if (annotation != null && (! java.util.Arrays.asList(annotation.value()).contains("tblCInfoList"))) { C cForQuery = new C(); cForQuery.setBId(b.getId()); List<C> cList = dao.getCListWithNull(cForQuery); // 如果查询的集合是空，也不用继续往下查了。 if (cList != null && cList.size() != 0) { cInfoList = new ArrayList<CInfo>(cList.size()); // 判断该字段是否被注解标识，使用哪个子类 FkPojo fkano = thisClass.getAnnotation(FkPojo.class); // 如果没有被注解标注，需要报错 if (fkano == null || fkano.value().length == 0) { throw new Exception(thisClass.getName() + " -> must have @PkPojo annotation and lenght !=0 "); } // pojo不符合规范也要报错，注解用冒号分开[类名:全类名] Map<String, String> map = new HashMap<String, String>(); for (String s : fkano.value()) { s = s.trim(); String[] split = s.split(":"); if (split == null || split.length != 2) { throw new Exception( thisClass.getName() + " -> @PkPojo annotation format error [pojoName:pojoAllPath] "); } map.put(split[0], split[1]); } // 如果没有标识子类使用哪一个，则报错 if (map.get("CInfo") == null) { throw new Exception( thisClass.getName() + " -> @PkPojo annotation has no class: CInfo"); } // 反射出子类，将子类赋值给该对象。 Class<?> c1 = Class.forName(map.get("CInfo")); Constructor<?> declaredConstructor = c1.getDeclaredConstructor(SapoDao.class, C.class); // 循环新建对象，将对象加入到集合中。 for (C item : cList) { cInfoList.add((CInfo) declaredConstructor.newInstance(dao, item)); } } } } /** * 设置 * 主键 * 的方法 * * @param id 主键 */ public void setId(Integer id){ this.id = id; } /** * 获取 * 主键 * 的方法 * * @return 主键 */ public Integer getId(){ return id; } /** * 设置 * 名称 * 的方法 * * @param name 名称 */ public void setName(String name){ this.name = name; } /** * 获取 * 名称 * 的方法 * * @return 名称 */ public String getName(){ return name; } /** * 设置 * 详情 * 的方法 * * @param detail 详情 */ public void setDetail(String detail){ this.detail = detail; } /** * 获取 * 详情 * 的方法 * * @return 详情 */ public String getDetail(){ return detail; } /** * 设置 * 状态:0-无效，1-有效，2-编辑 * 的方法 * * @param status 状态:0-无效，1-有效，2-编辑 */ public void setStatus(Integer status){ this.status = status; } /** * 获取 * 状态:0-无效，1-有效，2-编辑 * 的方法 * * @return 状态:0-无效，1-有效，2-编辑 */ public Integer getStatus(){ return status; } /** * 设置 * 业务实体(tbl_a ) * 的方法 * * @param 业务实体(tbl_a ) */ public AInfo setAInfo(AInfo aInfo){ this.aInfo = aInfo; return this; } /** * 获取 * 业务实体(tbl_a ) * 的方法 * * @return */ public AInfo getAInfo(){ return aInfo; } public void setCInfoList(List<CInfo> cInfoList){ this.cInfoList = cInfoList; } public List<CInfo> getCInfoList(){ return cInfoList; } }
@JsonIgnoreProperties({ "id", "name", "detail", "status", "tblAInfo", "tblCInfoList" }) @FkPojo({ "CInfo:classQualifiedName", "AInfo:classQualifiedName" }) public class BInfoxx extends BInfo{ private static final long serialVersionUID = 1L; public BInfoxx(){} public BInfoxx(SapoDao dao,B b) throws Exception{ super(dao,b); } }
生成代码工具(存储过程实现)如下：
content DROP PROCEDURE IF EXISTS `print_pojo`; DELIMITER $ CREATE PROCEDURE `print_pojo`() BEGIN SET group_concat_max_len = 4294967295; -- 表名去除那些前缀 -- SET @noStrInTbl='tbl_ams'; SET @noStrInTbl='tbl'; -- 通用dao层类名， SET @common_dao_name='SapoDao'; -- domain类名后缀 SET @domain_suffix='Info'; SET @domain_prefix='tbl_sapo'; -- ######################begin:基础信息表维护################### -- 保存所有表及表的所有字段 DROP TABLE if EXISTS all_col_table; CREATE table if not exists all_col_table( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', tbl_name VARCHAR(256) COMMENT '表名:tbl_sapo_admin_account', tbl_name_comment VARCHAR(256) COMMENT '表注释', tbl_name_upper_camel VARCHAR(1024) COMMENT '表名大写驼峰:SapoAdminAccount', tbl_name_lower_camel VARCHAR(1024) COMMENT '表名小写驼峰:sapoAdminAccount', domain_tbl_name_upper_camel VARCHAR(1024) COMMENT 'doamin层表名驼峰:SapoAdminAccountInfo', domain_tbl_name_lower_camel VARCHAR(1024) COMMENT 'domain层表名引用驼峰:sapoAdminAccountInfo', col VARCHAR(256) COMMENT '字段名:create_time', if_fk VARCHAR(512) NOT NULL DEFAULT 'no' COMMENT '外键标志，yes=外键', col_comment VARCHAR(512) COMMENT '字段注释', col_lower_camel VARCHAR(256) COMMENT '字段驼峰形式:createTime', col_upper_camel VARCHAR(256) COMMENT '字段驼峰首字母大写:CreateTime', col_type VARCHAR(256) COMMENT '字段类型，datetime,int', java_type VARCHAR(256) COMMENT 'java类型，LocalDateTime,Integer', col_setter VARCHAR(256) COMMENT 'setter模式：setCreateTime', col_getter VARCHAR(256) COMMENT 'getter模式：getCreateTime', json_property VARCHAR(256) COMMENT 'json_property字段', PRIMARY KEY (`id`) , index (`tbl_name`,col) , INDEX idx_1(col) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; -- 外键临时表 DROP TABLE if exists fk_def; CREATE TABLE if not exists fk_def as SELECT t.TABLE_NAME AS tbl_name, k.column_name AS col, k.REFERENCED_TABLE_NAME AS rf_tbl_name, k.REFERENCED_COLUMN_NAME AS rf_col FROM information_schema.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND t.TABLE_NAME = k.TABLE_NAME AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' AND t.table_schema = DATABASE(); ALTER TABLE `fk_def` ADD INDEX `idx1` (tbl_name,col); ALTER TABLE `fk_def` ADD INDEX `idx2` (rf_tbl_name,rf_col); -- select * from fk_def ; -- ######################end:基础信息表维护################### -- 将本库中所有表及所有字段插入表中: tbl_name,tbl_name_comment,col,col_comment,col_type INSERT INTO all_col_table(tbl_name,tbl_name_comment,col,col_comment,col_type) SELECT t1.table_name, t2.TABLE_COMMENT,t1.column_name ,t1.COLUMN_COMMENT,t1.DATA_TYPE FROM information_schema.COLUMNS t1 JOIN information_schema.tables t2 ON t1.TABLE_NAME=t2.TABLE_NAME WHERE t1.table_schema= DATABASE() AND t1.TABLE_NAME LIKE 'tbl_%' ORDER BY t1.TABLE_NAME,t1.ORDINAL_POSITION; -- java类型转换 UPDATE all_col_table SET java_type= case col_type when 'datetime' then 'LocalDateTime' when 'tinyint' then 'Byte' when 'bigint' then 'Long' when 'int' then 'Integer' when 'varchar' then 'String' when 'longtext' then 'String' when 'char' then 'String' when 'blob' then 'byte[]' when 'mediumblob' then 'byte[]' when 'longblob' then 'byte[]' END; -- 字段转驼峰 UPDATE all_col_table SET col_lower_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); UPDATE all_col_table SET col_upper_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS('','_',col), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); -- getter,setter UPDATE all_col_table SET col_setter=CONCAT_WS('','set',col_upper_camel); UPDATE all_col_table SET col_getter=CONCAT_WS('','get',col_upper_camel); -- 表名转驼峰 UPDATE all_col_table SET tbl_name_upper_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, @noStrInTbl, ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ; UPDATE all_col_table SET tbl_name_lower_camel =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tbl_name, CONCAT(@noStrInTbl,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ; UPDATE all_col_table SET domain_tbl_name_upper_camel= CONCAT_WS('',tbl_name_upper_camel,@domain_suffix); UPDATE all_col_table SET domain_tbl_name_lower_camel= CONCAT_WS('',tbl_name_lower_camel,@domain_suffix); -- 更新if_fk是否外键标志 UPDATE all_col_table a SET a.if_fk= ifnull((SELECT 'yes' FROM fk_def f WHERE f.tbl_name=a.tbl_name AND a.col = f.col),'no'); -- ######################end:基础信息表维护################### -- ######################begin:基础字段代码表################### -- 拼接代码使用 DROP TABLE if EXISTS all_col_code; CREATE table if not exists all_col_code( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', tbl_name VARCHAR(256) COMMENT '表名:tbl_sapo_admin_account', col VARCHAR(256) COMMENT '字段名:create_time', col_type VARCHAR(256) COMMENT '字段类型，datetime,int', if_fk VARCHAR(512) NOT NULL DEFAULT 'no' COMMENT '外键标志，yes=外键', col_code LONGTEXT COMMENT 'private Integer id;', domain_col_code LONGTEXT COMMENT '@注解 private Integer id;', domain_to_model LONGTEXT COMMENT '', getter_setter_code LONGTEXT COMMENT 'getter ,setter', domain_getter_setter_code LONGTEXT COMMENT 'domain层getter,setter', to_string_code LONGTEXT COMMENT 'toString代码', domain_to_string_code LONGTEXT COMMENT 'domain_toString代码', domain_construct_code LONGTEXT COMMENT 'domain构造方法', hook_get_query_obj LONGTEXT COMMENT '', map2Obj LONGTEXT COMMENT '', PRIMARY KEY (`id`) , index (`tbl_name`,col) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; -- 初始化表名和字段 INSERT INTO all_col_code (tbl_name,col,col_type,if_fk) SELECT tbl_name,col,col_type,if_fk FROM all_col_table; -- domain_to_model @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET @temp=' item.%col_setter%(this.%col_getter%());\r\n'; UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk='no'; SET @temp=' item.%col_setter%(this.get%fk_domain_tbl_name_upper_camel%()==null?null:this.get%fk_domain_tbl_name_upper_camel%().%fk_col_getter%());\r\n'; UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk='yes'; -- col_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET @temp = ' // %col_comment% [%col%] private %java_type% %col_lower_camel%; '; UPDATE all_col_code SET col_code = @temp; -- domain_col_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 外键 SET @temp = ' // 外键关联实体类(字段：[ %col% ] 注释：[ %col_comment% ]) // %fk_tbl_name_comment%(关联表名：%fk_tbl_name% ) // @JsonAlias(value={"%json_property%"}) // @JsonProperty("%json_property%") @JsonIgnore protected %fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%; '; UPDATE all_col_code SET domain_col_code = @temp WHERE if_fk='yes'; -- datetime字段 SET @temp = ' // %col_comment% //@JsonProperty("%col_lower_camel%") //@JsonAlias(value={"%col_lower_camel%"}) //@JsonFormat(pattern = "yyyyMMddHHmmss") //@JsonDeserialize(using = LocalDateTimeDeserializer.class) //@JsonSerialize(using = LocalDateTimeSerializer.class) @JsonIgnore protected %java_type% %col_lower_camel%; '; UPDATE all_col_code SET domain_col_code = @temp WHERE col_type='datetime'; -- 普通字段 SET @temp= ' // %col_comment% // @JsonProperty("%col_lower_camel%") // @JsonAlias(value={"%col_lower_camel%"}) @JsonIgnore protected %java_type% %col_lower_camel%; '; UPDATE all_col_code SET domain_col_code = @temp WHERE domain_col_code IS NULL; -- getter_setter_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET @temp= ' /** * 设置 * %col_comment% * 的方法 * * @param %col_lower_camel% %col_comment% */ public %tbl_name_upper_camel% %col_setter%(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " = "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% < xx < public %tbl_name_upper_camel% %col_setter%LessThan(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " < "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% <= xx < public %tbl_name_upper_camel% %col_setter%LessThanEqual(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " <= "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% > xx > public %tbl_name_upper_camel% %col_setter%GreatThan(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " > "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% >= xx > public %tbl_name_upper_camel% %col_setter%GreatThanEqual(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " >= "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% = %col_lower_camel% + xx ; public %tbl_name_upper_camel% %col_setter%PlusSpecNum(%java_type% %col_lower_camel%){ if(%col_lower_camel% !=null && org.apache.commons.lang3.StringUtils.isNotBlank(%col_lower_camel%.toString())) { colFlagMap.put("%col_lower_camel%", " = %col_lower_camel% + "); } this.%col_lower_camel% = %col_lower_camel%; return this; } // %col_lower_camel% in (3,5) 只支持字符串或者数字类型 public %tbl_name_upper_camel% %col_setter%InList(List<%java_type%> list) { if(list!=null && list.size()!=0) { StringBuilder sb = new StringBuilder(); for(%java_type% s : list) { sb.append("\'").append(s).append("\',"); } // \'3\',\'5\' String str = sb.deleteCharAt(sb.length()-1).toString(); colFlagMap.put("%col_lower_camel%", " in ("+ str +" ) "); this.%col_lower_camel% = list.get(0); } return this; } // %col_lower_camel% is null public %tbl_name_upper_camel% %col_setter%IsNull() { colFlagMap.put("%col_lower_camel%", " is null "); return this; } /** * 获取 * %col_comment% * 的方法 * * @return %col_comment% */ public %java_type% %col_getter%(){ return %col_lower_camel%; } ' ; update all_col_code SET getter_setter_code=@temp ; -- domain_getter_setter_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 不带外键 SET @temp= ' /** * 设置 * %col_comment% * 的方法 * * @param %col_lower_camel% %col_comment% */ public void %col_setter%(%java_type% %col_lower_camel%){ this.%col_lower_camel% = %col_lower_camel%; } /** * 获取 * %col_comment% * 的方法 * * @return %col_comment% */ public %java_type% %col_getter%(){ return %col_lower_camel%; } ' ; update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk='no'; -- 外键字段 SET @temp= ' /** * 设置 * %fk_tbl_name_comment%(%fk_tbl_name% ) * 的方法 * * @param %fk_tbl_name_comment%(%fk_tbl_name% ) */ public %domain_tbl_name_upper_camel% set%fk_domain_tbl_name_upper_camel%(%fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%){ this.%fk_domain_tbl_name_lower_camel% = %fk_domain_tbl_name_lower_camel%; return this; } /** * 获取 * %fk_tbl_name_comment%(%fk_tbl_name% ) * 的方法 * * @return %col_comment% */ public %fk_domain_tbl_name_upper_camel% get%fk_domain_tbl_name_upper_camel%(){ return %fk_domain_tbl_name_lower_camel%; } '; update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk='yes' ; -- to_string_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET @temp='"%col_lower_camel%=" + %col_lower_camel%'; UPDATE all_col_code SET to_string_code =@temp; -- domain_to_string_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 不是外键，延续之前的to_string_code UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk='no'; -- 如果是外键 SET @temp='"%fk_domain_tbl_name_lower_camel%=" + %fk_domain_tbl_name_lower_camel%'; UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk='yes'; -- domain_construct_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 外键字段 SET @temp= ' // 【############################# 外键字段 #############################】 // 外键关联对象：%fk_tbl_name_comment%(%fk_tbl_name%) object = colMap.get("%fk_tbl_name_lower_camel%%domain_suffix%"); if (object != null && object instanceof Map<?, ?>&& ! ((Map<?,?>)object).isEmpty() && %tbl_name_lower_camel%.%col_getter%() != null) { %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel%ForQuery = new %fk_tbl_name_upper_camel%(); if(threadObj.get()!=null && threadObj.get().get("%fk_tbl_name_upper_camel%")!=null) { %fk_tbl_name_lower_camel%ForQuery = %fk_tbl_name_upper_camel%.getInstance((%fk_tbl_name_upper_camel% ) threadObj.get().get("%fk_tbl_name_upper_camel%")); } if(%fk_tbl_name_lower_camel%ForQuery.get%fk_col_upper_camel%() != null){ throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),"fk col can not set in ThreadLocalCache.threadObj: %fk_tbl_name_lower_camel%ForQuery.get%fk_col_upper_camel%() != null"); } %fk_tbl_name_lower_camel%ForQuery.set%fk_col_upper_camel%(%tbl_name_lower_camel%.%col_getter%()); this.%fk_tbl_name_lower_camel%%domain_suffix% = new %fk_tbl_name_upper_camel%%domain_suffix%(%fk_tbl_name_lower_camel%ForQuery,(Map<String, Object>) object); } '; SET @temp:=REPLACE(@temp,'%common_dao_name%',@common_dao_name); SET @temp:=REPLACE(@temp,'%domain_suffix%',@domain_suffix); UPDATE all_col_code SET domain_construct_code=@temp WHERE if_fk='yes'; -- 不是外键字段 SET @temp= ' this.%col_lower_camel%=%tbl_name_lower_camel%.%col_getter%(); ' ; UPDATE all_col_code SET domain_construct_code =@temp WHERE if_fk='no'; -- hook_get_query_obj 字段 -- 外键字段 SET @temp= ' // 外键，引用其他表 //ThreadLocalCache.setThreadObj(this.getClass().getSimpleName(),%fk_tbl_name_upper_camel%.getInstance().setStatus(%fk_tbl_name_upper_camel%.STATUS_VALID));\r\n '; UPDATE all_col_code SET hook_get_query_obj=@temp WHERE if_fk='yes'; -- 非外键字段 UPDATE all_col_code SET hook_get_query_obj ='' WHERE if_fk='no'; -- map2Obj SET @temp = ' case "%col%" : res.%col_setter%((%java_type%) transType(map.get(s), %java_type%.class)) ; break;'; UPDATE all_col_code SET map2Obj =@temp ; -- ######################begin:基础字段代码表################### /* -- 建立外键详情表需要打印出字段列表 SELECT GROUP_CONCAT( CONCAT_WS('','a.', t1.column_name ,' as fk_',t1.column_name ) ) FROM information_schema.COLUMNS t1 WHERE t1.table_schema= DATABASE() AND t1.TABLE_NAME='all_col_table'; */ -- 外键明细表 DROP TABLE if exists fk_all_col_table; CREATE TABLE if NOT exists fk_all_col_table SELECT f.tbl_name,f.col,a.tbl_name AS json_property,a.tbl_name as fk_tbl_name,a.tbl_name_comment as fk_tbl_name_comment ,a.tbl_name_upper_camel as fk_tbl_name_upper_camel,a.tbl_name_lower_camel as fk_tbl_name_lower_camel ,a.domain_tbl_name_upper_camel as fk_domain_tbl_name_upper_camel,a.domain_tbl_name_lower_camel as fk_domain_tbl_name_lower_camel ,a.col as fk_col,a.if_fk as fk_if_fk,a.col_comment as fk_col_comment,a.col_lower_camel as fk_col_lower_camel ,a.col_upper_camel as fk_col_upper_camel,a.col_type as fk_col_type ,a.java_type as fk_java_type,a.col_setter as fk_col_setter,a.col_getter as fk_col_getter FROM all_col_table a JOIN fk_def f ON a.tbl_name=f.rf_tbl_name WHERE f.rf_col=a.col ; ALTER TABLE `fk_all_col_table` ADD INDEX `idx1` (tbl_name,col); -- 更新 json_property ，及在json注解中显示的名字 UPDATE fk_all_col_table SET json_property= CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(json_property, CONCAT(@domain_prefix,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,'Info'); -- json_property UPDATE all_col_table SET json_property = col_lower_camel WHERE if_fk='no'; UPDATE all_col_table a SET a.json_property = (SELECT f.json_property FROM fk_all_col_table f WHERE a.tbl_name =f.tbl_name AND a.col=f.col) WHERE a.if_fk='yes'; SET @temp=' UPDATE all_col_code c SET c.@col_code@= REPLACE(c.@col_code@,\'%@tbl_name@%\', ifnull((select a.@tbl_name@ FROM @all_col_table@ a WHERE c.tbl_name=a.tbl_name AND c.col=a.col),\'%@tbl_name@%\') ); '; -- 所有执行替换的sql都在该表中 DROP TABLE if exists exe_sql_tbl; CREATE TABLE if NOT EXISTS exe_sql_tbl as SELECT (@id:=@id+1) as id,t1.TABLE_NAME,t.col, replace( replace( REPLACE(@temp,'@tbl_name@',t1.COLUMN_NAME) ,'@all_col_table@',t1.TABLE_NAME) ,'@col_code@',t.col) AS code FROM information_schema.COLUMNS t1 JOIN ( SELECT 'col_code' AS col UNION ALL SELECT 'domain_col_code' UNION ALL SELECT 'getter_setter_code' UNION ALL SELECT 'domain_getter_setter_code' UNION ALL SELECT 'to_string_code' UNION ALL SELECT 'domain_to_string_code' UNION ALL SELECT 'domain_construct_code' UNION all SELECT 'domain_to_model' UNION all SELECT 'hook_get_query_obj' UNION all SELECT 'map2Obj' ) t JOIN (SELECT @id:=0) tt WHERE t1.table_schema= DATABASE() AND t1.TABLE_NAME IN ('all_col_table','fk_all_col_table') ; ALTER TABLE `exe_sql_tbl` ADD INDEX `idx1` (id); -- select * from exe_sql_tbl; -- 确定循环次数 SELECT MAX(id) INTO @var_count FROM exe_sql_tbl; -- 循环 loop_lab:LOOP if @var_count = 0 then leave loop_lab; END if; -- 从表中取出一个sql SELECT CODE INTO @exe_sql FROM exe_sql_tbl WHERE id =@var_count; -- 动态执行 PREPARE stmt FROM @exe_sql ; EXECUTE stmt ; SET @var_count = @var_count -1; END LOOP; -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 表相关数据 DROP TABLE if EXISTS all_table; CREATE table if not exists all_table( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', tbl_name VARCHAR(256) NOT NULL COMMENT '表名:tbl_sapo_admin_account', tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount', tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount', domain_tbl_name_upper_camel VARCHAR(1024) COMMENT '表名驼峰:SapoAdminAccount', domain_tbl_name_lower_camel VARCHAR(1024) COMMENT '表名引用驼峰:sapoAdminAccount', pojo LONGTEXT COMMENT '', domain_pojo LONGTEXT COMMENT '', controller_res_pojo LONGTEXT COMMENT '', controller_req_pojo LONGTEXT COMMENT '', controller_json_filter longtext comment '', controller_pojo_annotation LONGTEXT COMMENT'', controller_pojo_col_set longtext comment '', controller_pojo_fk_pojo_annotation LONGTEXT COMMENT '', controller_pojo_static_map longtext comment '', domain_to_model LONGTEXT COMMENT '', constant_code LONGTEXT COMMENT '', domain_construct_code LONGTEXT COMMENT '', list_domain_construct_code LONGTEXT COMMENT '', to_string_code LONGTEXT COMMENT 'toString', domain_to_string_code LONGTEXT COMMENT 'toString', col_code LONGTEXT COMMENT 'getter_setter_code', list_col_code LONGTEXT COMMENT 'getter_setter_code', getter_setter_code LONGTEXT COMMENT '', list_getter_setter_code LONGTEXT COMMENT '', domain_col_code LONGTEXT COMMENT '', domain_getter_setter_code LONGTEXT COMMENT '', hook_get_query_obj LONGTEXT COMMENT '', list_hook_get_query_obj LONGTEXT COMMENT '', map2Obj LONGTEXT COMMENT '', PRIMARY KEY (`id`) , index (`tbl_name`) ) ENGINE=InnoDB ; -- 初始化表名等字段 INSERT INTO all_table (tbl_name,tbl_name_upper_camel,tbl_name_lower_camel,domain_tbl_name_upper_camel,domain_tbl_name_lower_camel) SELECT DISTINCT tbl_name,tbl_name_upper_camel,tbl_name_lower_camel,domain_tbl_name_upper_camel,domain_tbl_name_lower_camel FROM all_col_table; -- 设置 controller_json_filter update all_table set controller_json_filter=concat_ws('','@JsonFilter("',concat(REPLACE(UUID(),'-',''),DATE_FORMAT(SYSDATE(3),'%Y_%m_%d_%H%i%s%S')),'")'); -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 设置 被外键关联的表。 例如：业务订单下面挂了好几个支付订单 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ DROP TABLE if EXISTS fk_list; CREATE TABLE if NOT exists fk_list SELECT f.rf_tbl_name ,f.rf_col ,CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.tbl_name, CONCAT(@domain_prefix,'_'), ''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,'InfoList') AS json_property_list ,a.* from fk_def f JOIN all_col_table a on f.tbl_name = a.tbl_name and f.col=a.col; ALTER TABLE `fk_list` ADD COLUMN `rf_tbl_name_lower_camel` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_tbl_name`; ALTER TABLE `fk_list` ADD COLUMN `rf_col_getter` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_col`; ALTER TABLE `fk_list` ADD INDEX `idx1` (rf_tbl_name); -- SELECT * FROM fk_list; -- list_col_code UPDATE fk_list l SET l.rf_tbl_name_lower_camel=(SELECT a.tbl_name_lower_camel FROM all_col_table a WHERE a.tbl_name=l.rf_tbl_name AND a.col = l.rf_col); UPDATE fk_list l SET l.rf_col_getter=(SELECT a.col_getter FROM all_col_table a WHERE a.tbl_name=l.rf_tbl_name AND a.col = l.rf_col); -- domain_to_model SET @temp= ' // 将domain对象转成model对象 public %tbl_name_upper_camel% domain2%tbl_name_upper_camel%(){ %tbl_name_upper_camel% item = new %tbl_name_upper_camel%(); %domain_to_model% return item; } '; UPDATE all_table a SET a.domain_to_model= REPLACE( REPLACE(@temp,'%domain_to_model%', ( SELECT GROUP_CONCAT( c.domain_to_model ORDER BY c.id SEPARATOR '') FROM all_col_code c WHERE c.tbl_name=a.tbl_name ) ) ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel) ; -- list_col_code SET @temp= ' // %tbl_name_comment% 集合 [%tbl_name%][%col%] // @JsonProperty("%json_property_list%") // @JsonAlias(value={"%json_property_list%"}) @JsonIgnore protected List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List; ' ; UPDATE all_table atb SET atb.list_col_code= ( SELECT GROUP_CONCAT( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@temp,'%tbl_name_comment%',tbl_name_comment) ,'%tbl_name%',tbl_name) ,'%col%',col) ,'%json_property_list%',json_property_list) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) SEPARATOR '') FROM fk_list t WHERE atb.tbl_name=t.rf_tbl_name ) ; -- SELECT * FROM fk_list; -- 外键list，构造对象语句 SET @temp= ' //【##################### 集合字段 #############################】 object = colMap.get("%domain_tbl_name_lower_camel%List"); if(object!= null && object instanceof Map<?, ?> && ! ((Map<?,?>)object).isEmpty()) { %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery = new %tbl_name_upper_camel%(); if(threadObj.get()!=null && threadObj.get().get("%tbl_name_upper_camel%")!=null) { %tbl_name_lower_camel%ForQuery = %tbl_name_upper_camel%.getInstance((%tbl_name_upper_camel% ) threadObj.get().get("%tbl_name_upper_camel%")); } if(%tbl_name_lower_camel%ForQuery.%col_getter%() !=null){ throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),"list col fk col can not set in ThreadLocalCache.threadObj: %tbl_name_lower_camel%ForQuery.%col_getter% !=null"); } %tbl_name_lower_camel%ForQuery.%col_setter%(%rf_tbl_name_lower_camel%.%rf_col_getter%()); this.%domain_tbl_name_lower_camel%List = %domain_tbl_name_upper_camel%.getObjInfoList(%tbl_name_lower_camel%ForQuery,(Map<String, Object>) object); }else if(object!= null && object instanceof Map<?, ?> && ((Map<?,?>)object).isEmpty()) { this.%domain_tbl_name_lower_camel%List = new ArrayList<>(0); } '; SET @temp:=REPLACE(@temp,'%common_dao_name%',@common_dao_name); update all_table a SET a.list_domain_construct_code= ( SELECT GROUP_CONCAT( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@temp,'%json_property_list%',json_property_list) ,'%tbl_name_upper_camel%',tbl_name_upper_camel) ,'%tbl_name_lower_camel%',tbl_name_lower_camel) ,'%col_setter%',col_setter) ,'%col_getter%',col_getter) ,'%rf_tbl_name_lower_camel%',rf_tbl_name_lower_camel) ,'%rf_col_getter%',rf_col_getter) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) order BY f.tbl_name separator '') FROM fk_list f WHERE f.rf_tbl_name=a.tbl_name ); -- list_hook_get_query_obj SET @temp= ' // 被别人当做外键引用 // ThreadLocalCache.setThreadObj(this.getClass().getSimpleName(),%tbl_name_upper_camel%.getInstance().setStatus(%tbl_name_upper_camel%.STATUS_VALID));\r\n '; update all_table a SET a.list_hook_get_query_obj= ( SELECT GROUP_CONCAT( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@temp,'%json_property_list%',json_property_list) ,'%tbl_name_upper_camel%',tbl_name_upper_camel) ,'%tbl_name_lower_camel%',tbl_name_lower_camel) ,'%col_setter%',col_setter) ,'%rf_tbl_name_lower_camel%',rf_tbl_name_lower_camel) ,'%rf_col_getter%',rf_col_getter) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) order BY f.tbl_name separator '') FROM fk_list f WHERE f.rf_tbl_name=a.tbl_name ); -- list_getter_setter_code SET @temp= ' public void set%domain_tbl_name_upper_camel%List(List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List){ this.%domain_tbl_name_lower_camel%List = %domain_tbl_name_lower_camel%List; } public List<%domain_tbl_name_upper_camel%> get%domain_tbl_name_upper_camel%List(){ return %domain_tbl_name_lower_camel%List; } '; UPDATE all_table atb SET atb.list_getter_setter_code= ( SELECT GROUP_CONCAT( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@temp,'%tbl_name_comment%',tbl_name_comment) ,'%tbl_name%',tbl_name) ,'%col%',col) ,'%json_property_list%',json_property_list) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) SEPARATOR '') FROM fk_list t WHERE atb.tbl_name=t.rf_tbl_name ) ; -- list_controller_pojo_annotation -- list_controller_pojo_fk_pojo_annotation -- col_code UPDATE all_table a SET a.col_code= ( SELECT GROUP_CONCAT(col_code ORDER BY id SEPARATOR '') FROM all_col_code WHERE a.tbl_name =tbl_name ); -- hook_get_query_obj UPDATE all_table a SET a.hook_get_query_obj= ( SELECT GROUP_CONCAT(hook_get_query_obj ORDER BY id SEPARATOR '') FROM all_col_code WHERE a.tbl_name =tbl_name and if_fk='yes' ); -- getter_setter_code UPDATE all_table a SET a.getter_setter_code= ( SELECT GROUP_CONCAT(getter_setter_code ORDER BY id SEPARATOR '') FROM all_col_code WHERE a.tbl_name =tbl_name ); -- domain_col_code UPDATE all_table a SET a.domain_col_code= ( SELECT GROUP_CONCAT(domain_col_code ORDER BY id SEPARATOR '') FROM all_col_code WHERE a.tbl_name =tbl_name ); -- 将外键list加上 UPDATE all_table a SET a.domain_col_code=CONCAT_WS('',domain_col_code,'\r\n',list_col_code); -- domain_getter_setter_code UPDATE all_table a SET a.domain_getter_setter_code= ( SELECT GROUP_CONCAT(domain_getter_setter_code ORDER BY id SEPARATOR '') FROM all_col_code WHERE a.tbl_name =tbl_name ); -- 将外键list加上 UPDATE all_table a SET a.domain_getter_setter_code=CONCAT_WS('',domain_getter_setter_code,'\r\n',list_getter_setter_code); -- toString() SET @template= ' @Override public String toString(){ return "%tbl_name_upper_camel% ["+%to_string_code%+" ]"; } '; UPDATE all_table a SET a.to_string_code= ( SELECT REPLACE( REPLACE( @template,'%to_string_code%',GROUP_CONCAT(to_string_code SEPARATOR '+ " , " +' ) ) ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel ) FROM all_col_code WHERE a.tbl_name = tbl_name ); -- domain_to_string_code UPDATE all_table a SET a.domain_to_string_code= ( SELECT REPLACE( REPLACE(@template,'%to_string_code%', GROUP_CONCAT(t.domain_to_string_code SEPARATOR '+ " , " +') ),'%tbl_name_upper_camel%',a.domain_tbl_name_upper_camel ) FROM ( SELECT tbl_name,domain_to_string_code FROM all_col_code UNION ALL /*以下为了展示 外键list*/ SELECT rf_tbl_name AS tbl_name,CONCAT_WS('','"',domain_tbl_name_lower_camel,'List = "+',domain_tbl_name_lower_camel,'List') AS domain_to_string_code FROM fk_list ) t WHERE a.tbl_name = t.tbl_name ); -- domain_construct 构造器 SET @template=' // 构造方法 @SuppressWarnings("unchecked") public %domain_tbl_name_upper_camel%(%tbl_name_upper_camel% %tbl_name_lower_camel%,Map<String, Object> colMap) throws Exception { super(); this.colMap=colMap; Object object =null; // %tbl_name_lower_camel% 为查询条件，如果没有找到，直接报错，有外层抓住异常进行处理 %tbl_name_lower_camel% = dao.getObj(%tbl_name_lower_camel%); %domain_construct_code% %list_domain_construct_code% } '; SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name); UPDATE all_table a SET a.domain_construct_code= ( SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@template,'%domain_construct_code%',GROUP_CONCAT(domain_construct_code SEPARATOR '\r\n')) ,'%domain_tbl_name_lower_camel%',a.domain_tbl_name_lower_camel) ,'%domain_tbl_name_upper_camel%',a.domain_tbl_name_upper_camel) ,'%tbl_name_upper_camel%',a.tbl_name_upper_camel) ,'%tbl_name_lower_camel%',a.tbl_name_lower_camel) FROM all_col_code WHERE a.tbl_name = tbl_name ); -- 将外键list更新进去 UPDATE all_table SET domain_construct_code=REPLACE(domain_construct_code,'%list_domain_construct_code%',ifnull(list_domain_construct_code,' ')); -- constant 静态常量 SET @template=' //%biz_desc%(%tbl_name% [%col%]) : %col_value_desc% public static final %java_type% %upper% = %col_value%; '; -- 如果不存在就创建表。防止报错 CREATE TABLE if NOT EXISTS `sys_code_mapper` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `create_time` datetime(3) NULL COMMENT '创建时间', `biz_desc` varchar(255) NULL COMMENT '业务含义', `tbl_name` varchar(255) NULL COMMENT '表名', `col` varchar(255) NULL COMMENT '字段', `col_value` varchar(255) NULL COMMENT '字段值', `col_value_desc` varchar(255) NULL COMMENT '字段值含义', `remark` varchar(255) NULL COMMENT '备注', PRIMARY KEY (`id`) , INDEX idx_1(tbl_name,col) )COMMENT = '系统代码映射表'; UPDATE all_table s SET s.constant_code = ( SELECT GROUP_CONCAT( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( @template,'%upper%', CONCAT_WS('',UPPER(d.col),'_',UPPER(d.remark)) ) ,'%biz_desc%',d.biz_desc) ,'%tbl_name%',d.tbl_name) ,'%col%',d.col) ,'%col_value_desc%',d.col_value_desc) ,'%java_type%',a.java_type) ,'%col_value%',if(a.java_type='String',CONCAT_WS('','"',d.col_value,'"'),d.col_value)) SEPARATOR '') FROM sys_code_mapper d JOIN all_col_table a ON a.tbl_name=d.tbl_name AND a.col=d.col where d.tbl_name=s.tbl_name ); -- controller_pojo_annotation SET @template= ' @JsonIgnoreProperties({ %controller_pojo_annotation% }) '; UPDATE all_table a SET a.controller_pojo_annotation= REPLACE(@template,'%controller_pojo_annotation%', ( SELECT GROUP_CONCAT( CONCAT_WS('','"',t.json_property,'"') order BY t.tbl_name,id SEPARATOR ',\r\n' ) FROM ( SELECT id,tbl_name,json_property FROM all_col_table UNION ALL SELECT id,rf_tbl_name,json_property_list FROM fk_list /* 该行：添加外键list语句*/ ) t WHERE t.tbl_name = a.tbl_name ) ); -- controller_pojo_col_set SET @template= ' // json序列化需要的字段 public static String[] set ={ %controller_pojo_col_set% }; '; UPDATE all_table a SET a.controller_pojo_col_set= REPLACE(@template,'%controller_pojo_col_set%', ( SELECT GROUP_CONCAT( CONCAT_WS('',' "',t.json_property,'"') order BY t.tbl_name,id SEPARATOR ',\r\n' ) FROM ( SELECT id,tbl_name,json_property FROM all_col_table UNION ALL SELECT id,rf_tbl_name,json_property_list FROM fk_list /* 该行：添加外键list语句*/ ) t WHERE t.tbl_name = a.tbl_name ) ); -- controller_pojo_fk_pojo_annotation SET @template='@FkPojo({ %controller_pojo_fk_pojo_annotation% })'; UPDATE all_table a SET a.controller_pojo_fk_pojo_annotation= REPLACE(@template,'%controller_pojo_fk_pojo_annotation%', ( SELECT GROUP_CONCAT( CONCAT_WS('','"',t.fk_domain_tbl_name_upper_camel,':','classQualifiedName','"') order BY t.tbl_name SEPARATOR ',\r\n') FROM ( SELECT tbl_name,fk_domain_tbl_name_upper_camel FROM fk_all_col_table UNION ALL SELECT rf_tbl_name,domain_tbl_name_upper_camel FROM fk_list /* 该行：添加外键list语句*/ ) t WHERE t.tbl_name = a.tbl_name ) ); -- controller_pojo_static_map SET @template=' public static Map<String, Class<?>> classMap = new HashMap<>(); // 属性及对应的class类关系。有则实例化相应的类 static { %controller_pojo_static_map% } '; UPDATE all_table a SET a.controller_pojo_static_map= REPLACE(@template,'%controller_pojo_static_map%', ( SELECT GROUP_CONCAT( CONCAT_WS('',' //classMap.put("',t.fk_domain_tbl_name_lower_camel,'",Object.class)') order BY t.tbl_name SEPARATOR ';\r\n') FROM ( SELECT tbl_name,fk_domain_tbl_name_lower_camel FROM fk_all_col_table UNION ALL SELECT rf_tbl_name,concat_ws('',domain_tbl_name_lower_camel,'List') FROM fk_list /* 该行：添加外键list语句*/ ) t WHERE t.tbl_name = a.tbl_name ) ); -- map2Obj SET @temp= ' public %tbl_name_upper_camel% map2Obj(Map<String, Object> map) { if(map==null || map.size()==0) { return null; } %tbl_name_upper_camel% res = new %tbl_name_upper_camel%(); for (String s : map.keySet()) { switch (s) { %map2Obj% default:break; } } return res; } public List<%tbl_name_upper_camel%> mapList2ObjList(List<Map<String, Object>> list) { if(list==null || list.size()==0) { return new ArrayList<%tbl_name_upper_camel%>(0); } List<%tbl_name_upper_camel%> res = new ArrayList<%tbl_name_upper_camel%>(list.size()); for(Map<String, Object> map :list) { if(map2Obj(map) !=null){ res.add(map2Obj(map)); } } return res; } '; UPDATE all_table a SET map2Obj = ( SELECT REPLACE( REPLACE(@temp,'%tbl_name_upper_camel%',tbl_name_upper_camel) ,'%map2Obj%',GROUP_CONCAT(map2Obj SEPARATOR '\r\n')) FROM all_col_code c WHERE c.tbl_name = a.tbl_name ); -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- pojo SET @template= ' public class %tbl_name_upper_camel% extends CommonModel implements Serializable { private static final long serialVersionUID = 1L; public %tbl_name_upper_camel%(){} public static %tbl_name_upper_camel% getInstance(){ return new %tbl_name_upper_camel%(); } // 赋值一个一模一样的对象 @SuppressWarnings("unchecked") public static %tbl_name_upper_camel% getInstance(%tbl_name_upper_camel% source){ %tbl_name_upper_camel% clone = org.apache.commons.lang3.SerializationUtils.clone(source); clone.colFlagMap= (HashMap<String, String>) source.colFlagMap.clone(); return clone; } %constant_code% %col_code% %getter_setter_code% %to_string_code% %map2Obj% } '; UPDATE all_table SET pojo= REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( @template,'%tbl_name_upper_camel%',tbl_name_upper_camel) ,'%col_code%',col_code) ,'%getter_setter_code%',getter_setter_code) ,'%to_string_code%',to_string_code) ,'%map2Obj%',map2Obj) ,'%constant_code%',ifnull(constant_code,'')) ; -- domain_pojo SET @template= ' public class %tbl_name_upper_camel%%domain_suffix% extends ThreadLocalCache implements Serializable , ResData{ /* <!-- 时间类序列化包 --> <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-jsr310</artifactId> </dependency> @JsonProperty(value = "xx",access = JsonProperty.Access.WRITE_ONLY) @JsonProperty(value = "xx",access = JsonProperty.Access.READ_ONLY) */ @JsonIgnore public Map<String, Object> colMap; @JsonAnyGetter public Map<String, Object> getSonsMap() throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException { /* map格式： "实体类中字段名":"要展示的新名字" "code":"storeCode" "name":"newName" */ HashMap<String, Object> resultMap = new HashMap<>(); // 如果要展示的字段为空。直接返回 if(colMap==null || colMap.isEmpty()) { return resultMap; } Set<String> keySet = colMap.keySet(); for(String key : keySet) { Object value = colMap.get(key); if(value ==null) { return resultMap; } // 如果是map ,获取"_jsonPropertyName" // {name=teacherName, person={_jsonPropertyName=newPersonName, name=wlf, age=new_age}} if(value instanceof Map<?,?>) { if(((Map<?,?>)value).get("_jsonPropertyName")==null || ((String)((Map<?,?>)value).get("_jsonPropertyName")).isEmpty()) { value = key; }else { value =(String) ((Map<?,?>)value).get("_jsonPropertyName"); } } // 例如：["a":"b"],b作为新键key，a对应字段的值作为value if(!"_jsonPropertyName".equals(key)) { resultMap.put( (String) value, this.getClass().getDeclaredField(key).get(this)); } } return resultMap; } private static final long serialVersionUID = 1L; public %tbl_name_upper_camel%%domain_suffix%(){} %domain_to_model% %constant_code% %col_code% %domain_construct_code% %getter_setter_code% %to_string_code% public List<%tbl_name_upper_camel%%domain_suffix%> getObjList(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery,Map<String, Object> colMap) throws Exception { // 默认查询500个出来。 PageInfo pageInfo = new PageInfo (1,500); if(threadObj.get()!=null && threadObj.get().get("%tbl_name_upper_camel%Page")!=null) { pageInfo =(PageInfo) threadObj.get().get("%tbl_name_upper_camel%Page"); } List<%tbl_name_upper_camel%> objList = dao.getObjListWithEmptyByPage(%tbl_name_lower_camel%ForQuery, pageInfo.getPageNum(), pageInfo.getPageSize()).getPageList(); ArrayList<%tbl_name_upper_camel%%domain_suffix%> resList = new ArrayList<>(objList.size()); for(%tbl_name_upper_camel% item : objList) { try{ resList.add(new %tbl_name_upper_camel%%domain_suffix%(item,colMap)); } catch(Exception e){ bizLogger.warn("new %tbl_name_upper_camel%%domain_suffix% has error",e); } } return resList; } public static List<%tbl_name_upper_camel%%domain_suffix%> getObjInfoList(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery,Map<String, Object> colMap) throws Exception { return (new %tbl_name_upper_camel%%domain_suffix%()).getObjList(%tbl_name_lower_camel%ForQuery,colMap); } public static ObjPage<%tbl_name_upper_camel%%domain_suffix%> getObjInfoListByPage(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery,Map<String, Object> colMap,Integer pageNum,Integer pageSize) throws Exception { // 获取总个数 int objCount = dao.getObjCount(%tbl_name_lower_camel%ForQuery); // 设置分页条件 ThreadLocalCache.setQueryPage(%tbl_name_lower_camel%ForQuery, pageNum, pageSize); List<%tbl_name_upper_camel%%domain_suffix%> objInfoList = getObjInfoList(%tbl_name_lower_camel%ForQuery,colMap); ObjPage<%tbl_name_upper_camel%%domain_suffix%> objPage = new ObjPage<%tbl_name_upper_camel%%domain_suffix%>(); objPage.setTotal(objCount); objPage.setPageList(objInfoList); return objPage; } } '; -- select * from all_table; -- select * from all_col_table; SET @template:=REPLACE(@template,'%domain_suffix%',@domain_suffix); UPDATE all_table SET domain_pojo= REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( @template,'%tbl_name_upper_camel%',tbl_name_upper_camel) ,'%col_code%',domain_col_code) ,'%getter_setter_code%',domain_getter_setter_code) ,'%to_string_code%',domain_to_string_code) ,'%domain_construct_code%',domain_construct_code) ,'%tbl_name_lower_camel%',tbl_name_lower_camel) ,'%domain_to_model%',domain_to_model) ,'%constant_code%',ifnull(constant_code,'')) ; -- SELECT * FROM all_table; -- controller_res_pojo SET @template= ' %controller_pojo_annotation% public class %domain_tbl_name_upper_camel%xx extends %domain_tbl_name_upper_camel%{ private static final long serialVersionUID = 1L; public %domain_tbl_name_upper_camel%xx(){} /* @NotNull(message=" xx can not be null") 验证对象是否不为null, 无法查检长度为0的字符串 @Range(min = 1, max = 100000, message = " xx not between [xx,xx] range") @NotBlank(message=" xx can not be blank") 检查约束 (字符串) 是不是Null还有被Trim的长度是否大于0,只对字符串,且会去掉前后空格. @Pattern(regexp = "^1([38][0-9]|4[579]|5[0-3,5-9]|6[6]|7[0135678]|9[89])\\d{8}$", message = "xx not match regular expression ") @NotEmpty(message=" xx can not be null or empty") 检查(集合)约束元素是否为NULL或者是EMPTY. @Size(min = 1, max = 1000, message = " xx size not between [xx,xx]") @NotNull(message=" xx can not be null") 对象级联判断 @Valid 注：@JsonIgnoreProperties 和 @NotBlank 混用注意。可能忽略了字段，但是该字段上有非空注解。因为忽略肯定空，但是非空又在校验，故报错。 所以忽略的字段不加非空判断。只能手动挨个加。 */ %col_code% %getter_setter_code% %to_string_code% } '; SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name); UPDATE all_table SET controller_req_pojo= REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@template,'%controller_pojo_annotation%',controller_pojo_annotation) ,'%controller_pojo_fk_pojo_annotation%',ifnull(controller_pojo_fk_pojo_annotation,'')) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) ,'%tbl_name_lower_camel%',tbl_name_lower_camel) ,'%col_code%',domain_col_code) ,'%to_string_code%',domain_to_string_code) ,'%getter_setter_code%',domain_getter_setter_code) ,'%tbl_name_upper_camel%',tbl_name_upper_camel); -- controller_res_pojo SET @template= ' %controller_json_filter% public class %domain_tbl_name_upper_camel%Res extends %domain_tbl_name_upper_camel%{ %controller_pojo_static_map% %controller_pojo_col_set% private static final long serialVersionUID = 1L; public %domain_tbl_name_upper_camel%Res(){} public %domain_tbl_name_upper_camel%Res(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery) throws Exception{ super(%tbl_name_lower_camel%ForQuery, classMap); //将set中需要展示的字段,注册到json过滤器中。 set中的字段列表只负责字段的展示与否。 ThreadLocalCache.simpleFilterProvider.addFilter(this.getClass().getAnnotation(JsonFilter.class).value(), SimpleBeanPropertyFilter.filterOutAllExcept(set)); } // 获取对象集合 @SuppressWarnings("unchecked") public List<%domain_tbl_name_upper_camel%Res> getObjList(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery) throws Exception{ return super.getObjList(%tbl_name_lower_camel%ForQuery); } public static List<%domain_tbl_name_upper_camel%Res> getObjInfoList(%tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery) throws Exception{ return (new %domain_tbl_name_upper_camel%Res()).getObjList(%tbl_name_lower_camel%ForQuery); } } '; SET @template:=REPLACE(@template,'%common_dao_name%',@common_dao_name); UPDATE all_table SET controller_res_pojo= REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(@template,'%controller_pojo_annotation%',controller_pojo_annotation) ,'%controller_pojo_fk_pojo_annotation%',ifnull(controller_pojo_fk_pojo_annotation,'')) ,'%controller_pojo_static_map%',controller_pojo_static_map) ,'%domain_tbl_name_upper_camel%',domain_tbl_name_upper_camel) ,'%domain_tbl_name_lower_camel%',domain_tbl_name_lower_camel) ,'%tbl_name_lower_camel%',tbl_name_lower_camel) ,'%col_code%',domain_col_code) ,'%controller_json_filter%',controller_json_filter) ,'%controller_pojo_col_set%',controller_pojo_col_set) ,'%hook_get_query_obj%',ifnull(hook_get_query_obj,'')) ,'%list_hook_get_query_obj%',ifnull(list_hook_get_query_obj,'')) ,'%tbl_name_upper_camel%',tbl_name_upper_camel); -- 查看执行替换的sql列表 -- SELECT * FROM exe_sql_tbl; -- 查看外键明细表 -- SELECT * FROM fk_all_col_table; -- 查看基础明细 -- select * from all_col_table; -- select * from all_col_code; -- select * from all_table; -- DROP TABLE sys_code_mapper; -- DROP TABLE fk_def; -- DROP TABLE exe_sql_tbl; -- DROP TABLE fk_all_col_table; -- DROP TABLE all_col_table; -- DROP TABLE all_col_code; END$ DELIMITER ; CALL print_pojo(); -- select * from all_col_table; -- select * from all_col_code; -- select * from all_table; -- SELECT tbl_name,pojo,domain_pojo FROM all_table; SELECT ' public abstract class CommonModel { public HashMap<String, String> colFlagMap=new HashMap<String, String>(); public abstract CommonModel map2Obj(Map<String, Object> map); public abstract List<?> mapList2ObjList(List<Map<String, Object>> list); // 数据类型转换。 public Object transType(Object value, Class<?> type) { // 如果类型一致，如需转换 if (value.getClass().equals(type)) { return value; } Object convert = org.apache.commons.beanutils.ConvertUtils.convert(value, type); // 将时间字段特殊处理下。 if (convert.getClass().getTypeName().equals("java.sql.Timestamp")) { convert = ((java.sql.Timestamp) convert).toLocalDateTime(); } return convert; } } ' AS pojo UNION ALL SELECT pojo FROM all_table; SELECT ' public class ThreadLocalCache extends BaseLogger { public static final ThreadLocal<Map<String, Object>> threadObj = new ThreadLocal<Map<String, Object>>(); public static final CommonDao dao = MySpringUtils.getBean(CommonDao.class); public static void setQueryObj(Object obj) { Map<String, Object> map = threadObj.get(); if (map == null) { map = new HashMap<>(); threadObj.set(map); } map.put(obj.getClass().getSimpleName(), obj); } public static void setQueryPage(Object obj,Integer pageNum,Integer pageSize) { Map<String, Object> map = threadObj.get(); if (map == null) { map = new HashMap<>(); threadObj.set(map); } map.put(obj.getClass().getSimpleName()+"Page", new PageInfo(pageNum,pageSize)); } public static void clearQueryObj() { threadObj.remove(); } @SuppressWarnings("unchecked") public static Map<String,Object> getColMap(String methodName) { try { String resJson = dao.getObj(SapoInterfaceDef.getInstance().setName(methodName)).getResJson(); return json.jsonToObject(resJson, Map.class); } catch (Exception e) { throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+" , inteface not config"); } } @SuppressWarnings("unchecked") public static List<Map<String,Object>> getColMapList(String methodName) { ArrayList<Map<String,Object>> list = new ArrayList<>(); try { List<SapoInterfaceDef> objList = dao.getObjList(SapoInterfaceDef.getInstance().setName(methodName)); for(SapoInterfaceDef s:objList) { list.add((Map<String,Object>)json.jsonToObject(s.getResJson(), Map.class)); } return list; } catch (Exception e) { throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+" , inteface not config"); } } } ' AS 'domain_pojo' UNION all SELECT ' @Component public class MySpringUtils extends BaseLogger implements org.springframework.context.ApplicationContextAware { private static org.springframework.context.ApplicationContext applicationContext; public void setApplicationContext(org.springframework.context.ApplicationContext applicationContext) throws BeansException { if (MySpringUtils.applicationContext == null) { MySpringUtils.applicationContext = applicationContext; } } // 获取applicationContext public static org.springframework.context.ApplicationContext getApplicationContext() { return applicationContext; } // 通过name获取 Bean. public static Object getBean(String name) { return getApplicationContext().getBean(name); } // 通过class获取Bean. public static <T> T getBean(Class<T> clazz) { return getApplicationContext().getBean(clazz); } // 通过name,以及Clazz返回指定的Bean public static <T> T getBean(String name, Class<T> clazz) { return getApplicationContext().getBean(name, clazz); } public static Object getTarget(Object dest, Map<String, Object> addProperties) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException { PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean(); // 得到原对象的属性 java.beans.PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(dest); Map<String, Class<?>> propertyMap = new HashMap<>(); for (java.beans.PropertyDescriptor d : descriptors) { if (!"class".equalsIgnoreCase(d.getName())) { propertyMap.put(d.getName(), d.getPropertyType()); } } addProperties.forEach((k, v) -> propertyMap.put(k, v.getClass())); // 构建新的对象 DynamicBean dynamicBean = new DynamicBean(dest.getClass(), propertyMap); for (Map.Entry<String, Class<?>> entry : propertyMap.entrySet()) { if (!addProperties.containsKey(entry.getKey())) {// 原来的值 dynamicBean.setValue(entry.getKey(), propertyUtilsBean.getNestedProperty(dest, entry.getKey())); } else {// 新增的值 dynamicBean.setValue(entry.getKey(), addProperties.get(entry.getKey())); } } return dynamicBean.getTarget(); } private static class DynamicBean { /** * 目标对象 */ private Object target; /** * 属性集合 */ private org.springframework.cglib.beans.BeanMap beanMap; public DynamicBean(Class<?> superclass, Map<String, Class<?>> propertyMap) { this.target = generateBean(superclass, propertyMap); this.beanMap = org.springframework.cglib.beans.BeanMap.create(this.target); } /** * bean 添加属性和值 * * @param property * @param value */ public void setValue(String property, Object value) { beanMap.put(property, value); } /** * 获取属性值 * * @param property * @return */ public Object getValue(String property) { return beanMap.get(property); } /** * 获取对象 * * @return */ public Object getTarget() { return this.target; } /** * 根据属性生成对象 * * @param superclass * @param propertyMap * @return */ private Object generateBean(Class<?> superclass, Map<String, Class<?>> propertyMap) { BeanGenerator generator = new BeanGenerator(); if (null != superclass) { generator.setSuperclass(superclass); } BeanGenerator.addProperties(generator, propertyMap); return generator.create(); } } } ' union all select ' @JsonTypeInfo(use = JsonTypeInfo.Id.NAME, property = "type", visible = true) @JsonSubTypes({ // 值-->子类映射 @JsonSubTypes.Type(value = ObjNode.class, name = "object"), @JsonSubTypes.Type(value = ColNode.class, name = "string"), @JsonSubTypes.Type(value = ColNode.class, name = "integer"), @JsonSubTypes.Type(value = ColNode.class, name = "boolean"), @JsonSubTypes.Type(value = ColNode.class, name = "number"), @JsonSubTypes.Type(value = ArrNode.class, name = "array") }) public class Node { public String type; public String colName; private Object mock; public Object getMock() { return mock; } public void setMock(Object mock) { this.mock = mock; } public String getColName() { return colName; } public void setColName(String colName) { this.colName = colName; } public String getType() { return type; } public void setType(String type) { this.type = type; } @Override public String toString() { return "Node [type=" + type + ", colName=" + colName + "]"; } } ' union all select ' public class PageInfo { private Integer pageNum; private Integer pageSize; public Integer getPageNum() { return pageNum; } public void setPageNum(Integer pageNum) { this.pageNum = pageNum; } public Integer getPageSize() { return pageSize; } public void setPageSize(Integer pageSize) { this.pageSize = pageSize; } public PageInfo(Integer pageNum, Integer pageSize) { super(); this.pageNum = pageNum; this.pageSize = pageSize; } } ' union all select ' @Configuration public class LocalDateTimeGlobalConfig { @Bean public ObjectMapper initObjectMapper(){ ObjectMapper objectMapper=new ObjectMapper(); JavaTimeModule javaTimeModule=new JavaTimeModule(); javaTimeModule.addDeserializer(LocalDateTime.class,new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))); javaTimeModule.addSerializer(LocalDateTime.class,new LocalDateTimeSerializer(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))); //localDateTime按照 "yyyy-MM-dd HH:mm:ss"的格式进行序列化、反序列化 objectMapper.registerModule(javaTimeModule); return objectMapper; } } ' union all select ' public class ArrNode extends Node { private List<String> required; private String description; private Node items; public List<String> getRequired() { return required; } public void setRequired(List<String> required) { this.required = required; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Node getItems() { return items; } public void setItems(Node items) { this.items = items; } @Override public String toString() { return "ArrNode [description=" + description + ", items=" + items + "]"; } } ' UNION ALL select ' public class ColNode extends Node{ private String description; private String example; private String colName; public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public String getExample() { return example; } public void setExample(String example) { this.example = example; } public String getColName() { return colName; } public void setColName(String colName) { this.colName = colName; } @Override public String toString() { return "colNode [description=" + description + ", type=" + type + ", example=" + example + ", colName=" + colName + "]"; } } ' UNION ALL select ' public class ObjNode extends Node { private List<String> required; private String description; private Map<String, Node> properties; public List<String> getRequired() { return required; } public void setRequired(List<String> required) { this.required = required; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Map<String, Node> getProperties() { return properties; } public void setProperties(Map<String, Node> properties) { this.properties = properties; } @Override public String toString() { return "ObjNode [required=" + required + ", description=" + description + ", properties=" + properties + "]"; } } ' union all select ' public class ResDataObj implements ResData{ } ' UNION ALL SELECT domain_pojo FROM all_table; SELECT t.tbl_name ,CONCAT('{\r\n"_jsonPropertyName":""\r\n',GROUP_CONCAT(CONCAT(', ',col1,":",col2) ORDER BY t.id asc SEPARATOR '\r\n' ),'\r\n}') AS '字段名:展示名' from all_table a join ( select (@id:=@id+1) AS id,tbl_name AS tbl_name, JSON_QUOTE(col_lower_camel) AS col1,JSON_QUOTE(col_lower_camel) AS col2 from all_col_table where if_fk='no' UNION ALL SELECT (@id:=@id+1) as id,tbl_name AS tbl_name, JSON_QUOTE(fk_domain_tbl_name_lower_camel) AS col1 ,'{}' AS col2 from fk_all_col_table UNION all select (@id:=@id+1) AS id,rf_tbl_name AS tbl_name ,JSON_QUOTE(concat(domain_tbl_name_lower_camel,'List')) AS col1, '{}' AS col2 from fk_list ) t on a.tbl_name = t.tbl_name JOIN (SELECT @id:=0) tt GROUP BY t.tbl_name ; SELECT tbl_name,controller_req_pojo AS 'controller_req_pojo' FROM all_table; -- SELECT tbl_name,controller_res_pojo AS 'controller_res_pojo' FROM all_table; SELECT ' //定制返回字段 ArrayList<Object> arrayList = new ArrayList<>(); for (SapoStoreDeviceInfo sapoStoreDeviceInfo : objInfoList) { LgDeviceInfo lgDeviceInfo = lgThinQService.getDeviceInfo(sapoStoreDeviceInfo.getDeviceId()); Integer remain = lgDeviceInfo.getRemain(); HashMap<String, Object> map = new HashMap<>(); map.put("remainTime", remain); arrayList.add(MySpringUtils.getTarget(sapoStoreDeviceInfo, map)); } HashMap<String, Object> hashMap = new HashMap<>(); hashMap.put("storeDeviceInfoList", arrayList); return (ResData) MySpringUtils.getTarget(new ResDataObj(), hashMap); // 返回多个对象 实例 HashMap<String, Object> hashMap = new HashMap<>(); hashMap.put("storeDeviceInfoList", objInfoList); return (ResData) MySpringUtils.getTarget(new ResDataObj(), hashMap); // 返回分页数据 SapoOrderInfo.getObjInfoListByPage(sapoOrderForQuery, colMap,pageNum, pageSize).setPageObjName("sapoOrderInfoList"); ' AS 'service方法'; SELECT ' @RestController @RequestMapping("/sapo/biz") public class StoresController extends BaseController { @Autowired private HttpServletRequest httpServletRequest; @Autowired private BizLoggerComponent bizLoggerComponent; /* service层方法 */ @Autowired private StoresService storeSevice; /* public BaseResponse getStores10( @RequestHeader(name = Constant.USER_TOKEN, required = Constant.IF_USER_TOKEN_CHECK) String token , @PathVariable("store-code") String storeCode , @Valid GetStores10Req request, BindingResult bindingResult ) throws Exception */ @GetMapping("/sapo/biz/1/1/orders/{order-no}") public BaseResponse getOrderDetail11( @RequestHeader(name = Constant.USER_TOKEN, required = Constant.IF_USER_TOKEN_CHECK) String token, @PathVariable("order-no") String orderNo, HttpServletRequest httpServletRequest) throws Exception { BaseResponse baseResponse = new BaseResponse(ResultInfo.SYS_SUCCESS); /* 获取本方法名 */ String methodName = Thread.currentThread().getStackTrace()[1].getMethodName(); /* 设置日志编号 */ BizLogUtils.putValueOfBizId(UuidUtils.getUuid()); /* 输出请求日志 */ bizLoggerComponent.receiveRestRequestLog(bizLogger, httpServletRequest.getServletPath(), orderNo, " [controller method] " + methodName + " method request "); /* 校验请求数据 */ // checkValidation(bindingResult); /* 获取返回数据json定义 */ Map<String, Object> colMap = ThreadLocalCache.getColMap(methodName); /* TODO ########################## 调用服务层 ###################################### */ ResData resData = getOrderDetailService.getOrderDetail11(colMap,token, orderNo); baseResponse.setResData(resData); /* 输出应答日志 */ bizLoggerComponent.sendRestResponseLog(bizLogger, httpServletRequest.getServletPath(), baseResponse, " [controller method] " + methodName + " method reposnse "); return baseResponse; } } ' AS 'controller方法'; select ' private static void jsonObj2Map(HashMap<String, Object> resultMap, String nodeName, Node node) { HashMap<String, Object> hashMap = new HashMap<>(); if (node instanceof ObjNode) { Map<String, Node> properties = ((ObjNode) node).getProperties(); Set<String> keySet = properties.keySet(); for (String s : keySet) { Node node2 = properties.get(s); jsonObj2Map(hashMap, s, node2); } hashMap.put("_jsonPropertyName", nodeName); if (node.getColName() == null || node.getColName().isEmpty()) { resultMap.put(nodeName, hashMap); } else { resultMap.put(node.getColName(), hashMap); } } else if (node instanceof ArrNode) { Node items = ((ArrNode) node).getItems(); String str = items.getColName(); if(str==null) { str=nodeName; } jsonObj2Map(hashMap, str, items); hashMap.put("_jsonPropertyName", nodeName); if (node.getColName() == null || node.getColName().isEmpty()) { resultMap.put(nodeName, hashMap); } else { resultMap.put(node.getColName(), hashMap); } } else { if (((ColNode) node).getColName() == null || ((ColNode) node).getColName().isEmpty()) { resultMap.put(nodeName, nodeName); } else { resultMap.put(((ColNode) node).getColName(), nodeName); } } } public static String getJsonColMap(String jsonSchema) throws IOException { ObjectMapper objectMapper = new ObjectMapper(); ObjectReader reader = objectMapper.readerFor(Node.class); Node node = reader.readValue(jsonSchema); // System.err.println(node.toString()); HashMap<String, Object> resultMap = new HashMap<>(); jsonObj2Map(resultMap, "root", node); return objectMapper.writeValueAsString(resultMap); } public static void main(String[] args) throws IOException { String jsonSchema = ""; System.err.println(getJsonColMap(jsonSchema)); } ' as 'JSONSchema转json'; DROP TABLE if exists all_col_code; DROP TABLE if exists all_col_table; DROP TABLE if exists all_table; DROP TABLE if exists exe_sql_tbl; DROP TABLE if exists fk_all_col_table; DROP TABLE if exists fk_def; -- DROP TABLE if exists sys_code_mapper; DROP TABLE if exists fk_list; drop table if exists java_code; drop table if exists java_code_file; drop PROCEDURE if exists print_pojo; drop PROCEDURE if exists print_code; DROP TABLE if exists all_col_code; DROP TABLE if exists all_col_table; DROP TABLE if exists all_table; DROP TABLE if exists exe_sql_tbl; DROP TABLE if exists fk_all_col_table; DROP TABLE if exists fk_def; DROP TABLE if exists fk_list; drop table if exists java_code; drop table if exists java_code_file; drop PROCEDURE if exists print_pojo; drop PROCEDURE if exists print_code; /* SELECT * FROM all_col_table; SELECT * FROM all_col_code; SELECT * FROM fk_all_col_table; SELECT * FROM all_table; */
生成代码工具(java实现)如下：
package cn.com.fmsh.nfcos.sapo.biz; import java.util.*; import java.util.regex.*; import java.util.stream.*; import com.fasterxml.jackson.annotation.*; import com.fasterxml.jackson.databind.*; import java.io.*; import java.sql.*; /** * 数据库实体类生成工具 * * @author wanglifeng */ public class DbModelGenerator { // 数据库驱动名 // Oracle: oracle.jdbc.OracleDriver // MariaDB: org.mariadb.jdbc.Driver // MySQL: com.mysql.jdbc.Driver private static String ORACLE_DATABASE_DRIVER = "oracle.jdbc.OracleDriver"; private static String MARIADB_DATABASE_DRIVER = "org.mariadb.jdbc.Driver"; private static String MYSQL_DATABASE_DRIVER = "com.mysql.jdbc.Driver"; private static String driver = MYSQL_DATABASE_DRIVER; // 数据库连接配置 // private static String url = "jdbc:mysql://gz-cdb-guj8pt5q.sql.tencentcdb.com:62661/db_cake_dev?useUnicode=true&characterEncoding=utf8&useSSL=false"; private static String url = "jdbc:mariadb://192.168.110.22:3306/db_sapo_dev?useUnicode=true&characterEncoding=utf8&useSSL=false"; // 数据库用户名 // private static String username = "du_cake_dev"; private static String username = "du_sapo_dev"; // 数据库密码 // private static String password = "nfc2022dev"; private static String password = "nfc2022dev"; // 指定数据库表对应的实体类的生成位置 private static String targetProject = "src/test/java"; private static String modelPackageName = "cn.com.fmsh.nfcos.sapo.biz.test1"; private static String domainPackageName = "cn.com.fmsh.nfcos.sapo.biz.test2"; private static String servicePackageName = "cn.com.fmsh.nfcos.sapo.biz.test3"; // 指定需要进行代码生成的相关数据库表,空则为生成所有 private static List<String> tableNameList = new ArrayList<>(); static { // tableNameList.add("tbl_task"); } private static HashMap<String, String> jdbcType2javaMap = new HashMap<String, String>(); static { // jdbc类型和java类型映射关系 jdbcType2javaMap.put("VARCHAR", "String"); jdbcType2javaMap.put("INTEGER", "Integer"); jdbcType2javaMap.put("TIMESTAMP", "LocalDateTime"); jdbcType2javaMap.put("TINYINT", "Byte"); jdbcType2javaMap.put("BIGINT", "Long"); jdbcType2javaMap.put("BLOB", "byte[]"); jdbcType2javaMap.put("LONGBLOB", "byte[]"); jdbcType2javaMap.put("DATETIME", "LocalDateTime"); jdbcType2javaMap.put("LONGTEXT", "String"); jdbcType2javaMap.put("SMALLINT", "Short"); jdbcType2javaMap.put("INT", "Integer"); jdbcType2javaMap.put("VARCHAR2", "String"); jdbcType2javaMap.put("CHAR", "String"); jdbcType2javaMap.put("NUMBER", "BigDecimal"); jdbcType2javaMap.put("DATE", "LocalDateTime"); jdbcType2javaMap.put("NVARCHAR2", "String"); jdbcType2javaMap.put("TIMESTAMP(6)", "LocalDateTime"); jdbcType2javaMap.put("CLOB", "String"); } private static String jdbcTypeToJava(String jdbcType) { String javaDataType = jdbcType2javaMap.get(jdbcType.toUpperCase()); if (javaDataType == null) { System.out.println("Unknow data type, please add to jdbcType2javaMap " + jdbcType); return "String"; } return javaDataType; } // 将表名转化为model实体类名称 private static String tblName2ModelFileName(String tblName) { String className = firstUpper(lineToHump(tblName.toLowerCase()));// tbl_sapo_act => TblSapoAct if (className.startsWith("Tbl")) { className = className.substring(3); // TblSapoAct => SapoAct } return className; } // 将表明转化成domain实体类名称 private static String tblName2DomainFileName(String tblName) { // 表名转文件名。 String modelName = lineToHump(tblName.toLowerCase());//tbl_sapo_act => tblSapoAct if (modelName.startsWith("tbl")) { modelName = modelName.substring(3); } String className = firstUpper(modelName + "Info"); // SapoActInfo return className; } // TODO public static void main(String[] args) throws IOException { try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); return; } try { Connection con = DriverManager.getConnection(url, username, password); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // 获取map,内容为：[表名:字段集合] Map<String, List<TableColumn>> tableAndTheirColumnsMap = getTablesAndTheirColumnsMap(st); // 获取所有表的外键集合 List<RefTableColumn> fkInfo = getFkInfo(st); // 获取所有的表的常量集合，没有常量就打印日志即可 Map<String, List<ConstantDefine>> tableAndTheirConstantsMap = new HashMap<>(); try { tableAndTheirConstantsMap = getTableAndTheirConstantsMap(st); } catch (Exception e) { System.err.println("No constant info, " + e.getMessage()); } // 遍历所有的表 Iterator<Map.Entry<String, List<TableColumn>>> iterator = tableAndTheirColumnsMap.entrySet().iterator(); while (iterator.hasNext()) { Map.Entry<String, List<TableColumn>> entry = iterator.next(); // 循环,一个表,一个表的生成model文件,方法入参：表名，字段列表，该表所有常量 generateModelFile(entry.getKey(), entry.getValue(), tableAndTheirConstantsMap.get(entry.getKey())); // 循环,一个表,一个表的生成domain文件 generateDomainFile(entry.getKey(), entry.getValue(), fkInfo); } System.out.println("----------Generate database entity finished----------"); // ResData 接口 writeDomainContentInFile("ResData",template4ResData()); // pageInfo 类 writeDomainContentInFile("PageInfo",template4PageInfo()); // MySpringUtils 类 writeDomainContentInFile("MySpringUtils",template4MySpringUtils()); // LocalDateTimeGlobalConfig 类 writeDomainContentInFile("LocalDateTimeGlobalConfig",template4LocalDateTimeGlobalConfig()); // ThreadLocalCache 类 writeDomainContentInFile("ThreadLocalCache",template4ThreadLocalCache()); // model writeModelContentToFile(template4CommonDaoFile(), "CommonDao"); writeModelContentToFile(template4CommonModelFile(), "CommonModel"); writeModelContentToFile(template4CommonMapperFile(), "CommonMapper"); writeModelContentToFile(template4ObjPageFile(), "ObjPage"); // service 层方法 writeServiceContentToFile(template4DataAuthAspectFile(), "Aspect4DataAuth"); writeServiceContentToFile(template4Aspect4ControllerFile(),"Aspect4Controller"); if (con != null) { con.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static String template4ThreadLocalCache() { String template = String.join(System.lineSeparator(), "package {packageName};", "", "import java.util.*;", "", "import {modelPackageName}.*;", "", "public class ThreadLocalCache extends BaseLogger {", "", " public static final ThreadLocal<Map<String, Object>> threadObj = new ThreadLocal<Map<String, Object>>();", "", " public static final CommonDao dao = MySpringUtils.getBean(CommonDao.class);", " ", " ", " public static void setQueryObj(Object obj) {", " Map<String, Object> map = threadObj.get();", "", " if (map == null) {", " map = new HashMap<>();", " threadObj.set(map);", " }", " map.put(obj.getClass().getSimpleName(), obj);", " }", " ", " public static void setQueryPage(Object obj,Integer pageNum,Integer pageSize) {", " Map<String, Object> map = threadObj.get();", "", " if (map == null) {", " map = new HashMap<>();", " threadObj.set(map);", " }", " ", " map.put(obj.getClass().getSimpleName()+\"Page\", new PageInfo(pageNum,pageSize));", " }", " ", " public static void clearQueryObj() {", " threadObj.remove();", " }", " ", " ", " public String toString() {", " ", " cn.hutool.json.JSONObject parseObj = cn.hutool.json.JSONUtil.parseObj(this);", " parseObj.remove(\"fixedMessageMap\");", " parseObj.remove(\"colFlagMap\");", " ", " return parseObj.toString();", " ", " }", "}" ); return template.replace("{packageName}",domainPackageName ).replace("{modelPackageName}", modelPackageName); } public static String template4LocalDateTimeGlobalConfig() { String template = String.join(System.lineSeparator(), "package {packageName};", "import com.fasterxml.jackson.databind.*;", "import com.fasterxml.jackson.datatype.jsr310.*;", "import com.fasterxml.jackson.datatype.jsr310.deser.*;", "import com.fasterxml.jackson.datatype.jsr310.ser.*;", "", "import java.time.*;", "import java.time.format.*;", "import org.springframework.context.annotation.*;", "", "@Configuration", "public class LocalDateTimeGlobalConfig {", "", " @Bean", " public ObjectMapper initObjectMapper(){", " ObjectMapper objectMapper=new ObjectMapper();", " JavaTimeModule javaTimeModule=new JavaTimeModule();", " javaTimeModule.addDeserializer(LocalDateTime.class,new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern(\"yyyyMMddHHmmss\")));", " javaTimeModule.addSerializer(LocalDateTime.class,new LocalDateTimeSerializer(DateTimeFormatter.ofPattern(\"yyyyMMddHHmmss\")));", " //localDateTime按照 \"yyyy-MM-dd HH:mm:ss\"的格式进行序列化、反序列化", " objectMapper.registerModule(javaTimeModule);", "", " return objectMapper;", " }", "}"); return template.replace("{packageName}",domainPackageName ); } public static String template4MySpringUtils() { String template = String.join(System.lineSeparator(), "package {packageName};", "", "import java.util.*;", "", "import java.lang.reflect.*;", "import org.apache.commons.beanutils.*;", "import org.springframework.beans.*;", "import org.springframework.cglib.beans.*;", "import org.springframework.stereotype.*;", "", "@Component", "public class MySpringUtils implements org.springframework.context.ApplicationContextAware {", "", " private static org.springframework.context.ApplicationContext applicationContext;", "", " public void setApplicationContext(org.springframework.context.ApplicationContext applicationContext) throws BeansException {", " if (MySpringUtils.applicationContext == null) {", " MySpringUtils.applicationContext = applicationContext;", " }", "", " }", "", " // 获取applicationContext", " public static org.springframework.context.ApplicationContext getApplicationContext() {", " return applicationContext;", " }", "", " // 通过name获取 Bean.", " public static Object getBean(String name) {", " return getApplicationContext().getBean(name);", " }", "", " // 通过class获取Bean.", " public static <T> T getBean(Class<T> clazz) {", " return getApplicationContext().getBean(clazz);", " }", "", " // 通过name,以及Clazz返回指定的Bean", " public static <T> T getBean(String name, Class<T> clazz) {", " return getApplicationContext().getBean(name, clazz);", " }", "", " public static Object getTarget(Object dest, Map<String, Object> addProperties) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {", " PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();", " // 得到原对象的属性", " java.beans.PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(dest);", " Map<String, Class<?>> propertyMap = new HashMap<>();", " for (java.beans.PropertyDescriptor d : descriptors) {", " if (!\"class\".equalsIgnoreCase(d.getName())) {", " propertyMap.put(d.getName(), d.getPropertyType());", " }", " }", " addProperties.forEach((k, v) -> propertyMap.put(k, v.getClass()));", " // 构建新的对象", " DynamicBean dynamicBean = new DynamicBean(dest.getClass(), propertyMap);", " for (Map.Entry<String, Class<?>> entry : propertyMap.entrySet()) {", "", " if (!addProperties.containsKey(entry.getKey())) {// 原来的值", " dynamicBean.setValue(entry.getKey(), propertyUtilsBean.getNestedProperty(dest, entry.getKey()));", " } else {// 新增的值", " dynamicBean.setValue(entry.getKey(), addProperties.get(entry.getKey()));", " }", "", " }", " return dynamicBean.getTarget();", " }", "", " private static class DynamicBean {", " /**", " * 目标对象", " */", " private Object target;", "", " /**", " * 属性集合", " */", " private org.springframework.cglib.beans.BeanMap beanMap;", "", " public DynamicBean(Class<?> superclass, Map<String, Class<?>> propertyMap) {", " this.target = generateBean(superclass, propertyMap);", " this.beanMap = org.springframework.cglib.beans.BeanMap.create(this.target);", " }", "", " /**", " * bean 添加属性和值", " *", " * @param property", " * @param value", " */", " public void setValue(String property, Object value) {", " beanMap.put(property, value);", " }", "", " /**", " * 获取属性值", " *", " * @param property", " * @return", " */", " public Object getValue(String property) {", " return beanMap.get(property);", " }", "", " /**", " * 获取对象", " *", " * @return", " */", " public Object getTarget() {", " return this.target;", " }", "", " /**", " * 根据属性生成对象", " *", " * @param superclass", " * @param propertyMap", " * @return", " */", " private Object generateBean(Class<?> superclass, Map<String, Class<?>> propertyMap) {", " BeanGenerator generator = new BeanGenerator();", " if (null != superclass) {", " generator.setSuperclass(superclass);", " }", " BeanGenerator.addProperties(generator, propertyMap);", " return generator.create();", " }", " }", "", "}"); return template.replace("{packageName}",domainPackageName ); } public static String template4PageInfo() { String template = String.join(System.lineSeparator(), "package {packageName};", "public class PageInfo {", "", " private Integer pageNum;", " private Integer pageSize;", " ", " public Integer getPageNum() {", " return pageNum;", " }", " public void setPageNum(Integer pageNum) {", " this.pageNum = pageNum;", " }", " public Integer getPageSize() {", " return pageSize;", " }", " public void setPageSize(Integer pageSize) {", " this.pageSize = pageSize;", " }", " ", " public PageInfo(Integer pageNum, Integer pageSize) {", " super();", " this.pageNum = pageNum;", " this.pageSize = pageSize;", " }", "}" ); return template.replace("{packageName}",domainPackageName ); } public static String template4ResData() { String template = String.join(System.lineSeparator(), "package {packageName};", "public interface ResData {}"); return template.replace("{packageName}",domainPackageName ); } // 获取map,内容为：[表名:字段集合] private static Map<String, List<TableColumn>> getTablesAndTheirColumnsMap(Statement st) throws SQLException { HashMap<String, List<TableColumn>> modelInfo = new HashMap<>(); String sql4model; if (MYSQL_DATABASE_DRIVER.equals(driver) || MARIADB_DATABASE_DRIVER.equals(driver)) { sql4model = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT " + "from information_schema.COLUMNS " + "where TABLE_SCHEMA = (select database())"; } else if (ORACLE_DATABASE_DRIVER.equals(driver)) { sql4model = "SELECT t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,c.COMMENTS " + "from user_tab_columns t,user_col_comments c " + "where t.TABLE_NAME = c.TABLE_NAME and t.COLUMN_NAME = c.COLUMN_NAME"; } else { System.out.println("Unsupport database " + driver); return modelInfo; } ResultSet rs4model = st.executeQuery(sql4model); // 将ResultSet转成list ArrayList<TableColumn> list = new ArrayList<>(); while (rs4model.next()) { TableColumn element = new TableColumn(); element.setTableName(rs4model.getString(1)); element.setColumnName(rs4model.getString(2)); element.setDataType(rs4model.getString(3)); element.setColumnComment(rs4model.getString(4)); list.add(element); } // 按照表名进行分类 return list.stream().collect(Collectors.groupingBy(s -> s.getTableName(), Collectors.toList())); } // 获取每个表的外键。 private static List<RefTableColumn> getFkInfo(Statement st) throws SQLException { List<RefTableColumn> fkInfo = new ArrayList<>(); String sql4fk; if (MYSQL_DATABASE_DRIVER.equals(driver) || MARIADB_DATABASE_DRIVER.equals(driver)) { sql4fk = "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME " + "from information_schema.KEY_COLUMN_USAGE " + "where TABLE_SCHEMA = (select database()) and REFERENCED_TABLE_SCHEMA = (select database())"; } else if (ORACLE_DATABASE_DRIVER.equals(driver)) { sql4fk = "......"; } else { System.out.println("Unsupport database " + driver); return fkInfo; } ResultSet rs4fk = st.executeQuery(sql4fk); while (rs4fk.next()) { RefTableColumn refTableColumn = new RefTableColumn(); refTableColumn.setTableName(rs4fk.getString(1)); refTableColumn.setColumnName(rs4fk.getString(2)); refTableColumn.setReferencedTableName(rs4fk.getString(3)); refTableColumn.setReferencedColumnName(rs4fk.getString(4)); fkInfo.add(refTableColumn); } if (rs4fk != null) { rs4fk.close(); } return fkInfo; } private static Map<String, List<ConstantDefine>> getTableAndTheirConstantsMap(Statement st) throws SQLException { List<ConstantDefine> list = new ArrayList<>(); String sql4Constant; if (MYSQL_DATABASE_DRIVER.equals(driver) || MARIADB_DATABASE_DRIVER.equals(driver)) { sql4Constant = "SELECT biz_desc, tbl_name, col, col_value, col_value_desc, remark " + "from sys_code_mapper"; } else if (ORACLE_DATABASE_DRIVER.equals(driver)) { sql4Constant = "SELECT biz_desc, tbl_name, col, col_value, col_value_desc, remark " + "from sys_code_mapper"; } else { System.err.println("Unsupport database " + driver); return null; } ResultSet rs4Constant = st.executeQuery(sql4Constant); while (rs4Constant.next()) { ConstantDefine constant = new ConstantDefine(); constant.setBizDesc(rs4Constant.getString(1)); constant.setTblName(rs4Constant.getString(2)); constant.setCol(rs4Constant.getString(3)); constant.setColValue(rs4Constant.getString(4)); constant.setColValueDesc(rs4Constant.getString(5)); constant.setRemark(rs4Constant.getString(6)); list.add(constant); } if (rs4Constant != null) { rs4Constant.close(); } // 按照表名进行分类 return list.stream().collect(Collectors.groupingBy(s -> s.getTblName(), Collectors.toList())); } private static String modelTemplate() { String template = String.join(System.lineSeparator(), "package {packageName};", "", "{import}", "public class {className} extends CommonModel implements Serializable {", "", " private static final long serialVersionUID = 1L;", "", " public {className}() {", " fixedMessageMap.put(COL_LIST, \"{colList}\");", " fixedMessageMap.put(TBL_NAME, \"{tblName}\");", " }", "", " public static {className} getInstance() {", " return new {className}();", " }", "", " // 赋值一个一模一样的对象", " public static {className} getInstance({className} source) {", " return cn.hutool.json.JSONUtil.parseObj(source).toBean(source.getClass());", " }", "", "{constant}", "{fields}", "{method}", "}"); return template; } private static String domainTemplate() { String template = String.join(System.lineSeparator(), "package {packageName};", "", "{import}", "public class {className} extends ThreadLocalCache implements Serializable , ResData {", "", " @JsonIgnore", " public Map<String, Object> colMap;", "", " @JsonAnyGetter", " public Map<String, Object> getSonsMap() throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException {", " /*", " map格式： ", " \"实体类中字段名\":\"要展示的新名字\"", " \"code\":\"storeCode\"", " \"name\":\"newName\"", " */", " HashMap<String, Object> resultMap = new HashMap<>();", " // 如果要展示的字段为空。直接返回", " if(colMap==null || colMap.isEmpty()) {", " return resultMap;", " }", " Set<String> keySet = colMap.keySet();", " for(String key : keySet) {", " Object value = colMap.get(key);", " if(value ==null) {", " return resultMap;", " }", " // 如果是map ,获取\"_jsonPropertyName\"", " // {name=teacherName, person={_jsonPropertyName=newPersonName, name=wlf, age=new_age}}", " if(value instanceof Map<?,?>) {", " if(((Map<?,?>)value).get(\"_jsonPropertyName\")==null || ((String)((Map<?,?>)value).get(\"_jsonPropertyName\")).isEmpty()) {", " value = key;", " } else {", " value =(String) ((Map<?,?>)value).get(\"_jsonPropertyName\");", " }", " }", " // 例如：[\"a\":\"b\"],b作为新键key，a对应字段的值作为value ；非_jsonPropertyName的普通key:value，倒置后加入map", " if(!\"_jsonPropertyName\".equals(key)) {", " resultMap.put( (String) value, this.getClass().getDeclaredField(key).get(this));", " }", " }", " return resultMap;", " }", "", " private static final long serialVersionUID = 1L;", "", " public {className}() {", " }", "", " // 将domain对象转成model对象", " public {modelName} domain2{modelName}(){", " {modelName} item = new {modelName}();", "{domain2Model}", " return item;", " }", "", "{fields}", " // 构造方法", " @SuppressWarnings(\"unchecked\")", " public {className}({modelName} {lowerModelName}, Map<String, Object> colMap) throws Exception {", " super();", " this.colMap=colMap;", " Object object =null;", "", " // {lowerModelName} 为查询条件，如果没有找到，直接报错，有外层抓住异常进行处理", " {lowerModelName} = dao.getObj({lowerModelName});", "", "{domainConstructor}", " }", "", "{method}", "}"); return template; } private static String template4domainGetObjList(String modelName) { String template = String.join(System.lineSeparator(), "", " public List<{modelName}Info> getObjList({modelName} {modelNameLower}ForQuery,Map<String, Object> colMap) throws Exception {", " ", " // 默认查询500个出来。", " PageInfo pageInfo = new PageInfo (1,500);", " ", " if(threadObj.get()!=null && threadObj.get().get(\"{modelName}Page\")!=null) {", " pageInfo =(PageInfo) threadObj.get().get(\"{modelName}Page\");", " }", " ", " List<{modelName}> objList = dao.getObjListWithEmptyByPage({modelNameLower}ForQuery, pageInfo.getPageNum(), pageInfo.getPageSize()).getPageList();", " ", " ", " ArrayList<{modelName}Info> resList = new ArrayList<>(objList.size());", " ", " for({modelName} item : objList) {", " try{", " item.colFlagMap.clear(); // 清空查询条件", " item.setId(item.getId()); // 只设置id去查询", " resList.add(new {modelName}Info(item,colMap));", " } catch(Exception e){", " bizLogger.warn(\"new {modelName}Info has error\",e);", " }", " }", " ", " return resList;", " }", " ", " public static List<{modelName}Info> getObjInfoList({modelName} {modelNameLower}ForQuery,Map<String, Object> colMap) throws Exception {", " return (new {modelName}Info()).getObjList({modelNameLower}ForQuery,colMap);", " }", " ", " public static ObjPage<{modelName}Info> getObjInfoListByPage({modelName} {modelNameLower}ForQuery,Map<String, Object> colMap,Integer pageNum,Integer pageSize) throws Exception {", " ", " // 获取总个数", " int objCount = dao.getObjCount({modelNameLower}ForQuery);", " ", " // 设置分页条件", " ThreadLocalCache.setQueryPage({modelNameLower}ForQuery, pageNum, pageSize);", " ", " List<{modelName}Info> objInfoList = getObjInfoList({modelNameLower}ForQuery,colMap);", " ", " ObjPage<{modelName}Info> objPage = new ObjPage<{modelName}Info>();", " objPage.setTotal(objCount);", " objPage.setPageList(objInfoList);", " ", " return objPage;", " }", " ", "", System.lineSeparator()); return template.replace("{modelName}", modelName).replace("{modelNameLower}", firstLower(modelName)); } private static String fieldTemplate() { String template = String.join(System.lineSeparator(), " /*", " * {columnComment}", " */", " private {dataType} {columnName};", ""); return template; } private static String domainFieldTemplate(String columnComment,String dataType,String columnName) { String template = String.join(System.lineSeparator(), " /*", " * {columnComment}", " */", " @JsonIgnore", " protected {dataType} {columnName};", System.lineSeparator()); return template.replace("{columnComment}", columnComment).replace("{dataType}", dataType).replace("{columnName}", columnName); } private static String methodTemplate4Model(String javaDataType) { String commonTemplate = String.join(System.lineSeparator(), " public {dataType} get{upperColumnName}() {", " return {columnName};", " }", " ", " // and {columnName} = xx", " public {className} set{upperColumnName}({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColOperatorXXTemplate(\"{dataType}\",\"{columnName}\",\"=\"));", " this.{columnName} = {columnName};", " return this;", " }", " ", " // and {columnName} is null ", " public {className} set{upperColumnName}IsNull(){", " colFlagMap.put(\"{columnName}\", getAndColIsNullTemplate(\"{columnName}\"));", " return this;", " }"); String lessOrGreatTemplate = String.join(System.lineSeparator(), " // and {columnName} < xx <", " public {className} set{upperColumnName}LessThan({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColOperatorXXTemplate(\"{dataType}\",\"{columnName}\",\"<\"));", " this.{columnName} = {columnName};", " return this;", " }", " // and {columnName} <= xx <", " public {className} set{upperColumnName}LessThanEqual({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColOperatorXXTemplate(\"{dataType}\",\"{columnName}\",\"<=\"));", " this.{columnName} = {columnName};", " return this;", " }", " ", " // and {columnName} > xx >", " public {className} set{upperColumnName}GreatThan({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColOperatorXXTemplate(\"{dataType}\",\"{columnName}\",\">\"));", " this.{columnName} = {columnName};", " return this;", " }", " ", " // and {columnName} >= xx >", " public {className} set{upperColumnName}GreatThanEqual({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColOperatorXXTemplate(\"{dataType}\",\"{columnName}\",\">=\"));", " this.{columnName} = {columnName};", " return this;", " }"); String plusOrMinusTemplate = String.join(System.lineSeparator(), " // {columnName} = {columnName} + xx ;", " public {className} set{upperColumnName}PlusSpecNum({dataType} num){", " colFlagMap.put(\"{columnName}\", getAndColPlusXXTemplate(\"{columnName}\", num.toString()));", " //this.{columnName} = ({dataType}) (this.{columnName}==null?0:this.{columnName} + num);", " return this;", " }", " ", " // {columnName} = {columnName} - xx ;", " public {className} set{upperColumnName}MinusSpecNum({dataType} num){", " colFlagMap.put(\"{columnName}\", getAndColMinusXXTemplate(\"{columnName}\", num.toString()));", " //this.{columnName} =({dataType}) (this.{columnName}==null?0:this.{columnName} - num);", " return this;", " }"); String inTemplate = String.join(System.lineSeparator(), " // {columnName} in (3,5) 只支持字符串或者数字类型", " public {className} set{upperColumnName}InList(List<{dataType}> list) {", " ", " if(list!=null && list.size()!=0) {", " StringBuilder sb = new StringBuilder();", " for({dataType} s : list) {", " sb.append(\"'\").append(s).append(\"',\");", " }", " // '3','5'", " String str = sb.deleteCharAt(sb.length()-1).toString();", " colFlagMap.put(\"{columnName}\", getAndColInXXTemplate(\"{columnName}\", str));", " }", " ", " return this;", " }"); String likeTemplate = String.join(System.lineSeparator(), " // and {columnName} like %xx% ", " public {className} set{upperColumnName}ByFuzzy({dataType} {columnName}){", " colFlagMap.put(\"{columnName}\", getAndColFuzzyXXTemplate(\"{dataType}\",\"{columnName}\"));", " this.{columnName} = {columnName};", " return this;", " }"); if (javaDataType.equals("Integer") || javaDataType.equals("Byte") || javaDataType.equals("Long") || javaDataType.equals("Short")) { return String.join(System.lineSeparator(), commonTemplate, lessOrGreatTemplate, plusOrMinusTemplate, inTemplate); } else if (javaDataType.equals("String")) { return String.join(System.lineSeparator(), commonTemplate, lessOrGreatTemplate, inTemplate, likeTemplate); } else if (javaDataType.equals("LocalDateTime")) { return String.join(System.lineSeparator(), commonTemplate, lessOrGreatTemplate, inTemplate); } else { return commonTemplate; } } private static String methodTemplate4Domain(String columnComment,String columnName,String className,String dataType) { String template = String.join(System.lineSeparator(), " /*", " * 设置", " * {columnComment}", " * 的方法", " *", " */", " public {className} set{upperColumnName}({dataType} {columnName}) {", " this.{columnName} = {columnName};", " return this;", " }", "", " /*", " * 获取", " * {columnComment}", " * 的方法", " *", " */", " public {dataType} get{upperColumnName}() {", " return {columnName};", " }", System.lineSeparator()); return template.replace("{columnComment}", columnComment).replace("{columnName}",columnName ) .replace("{upperColumnName}", firstUpper(columnName)).replace("{className}",className ).replace("{dataType}",dataType ); } private static String constantTemplate(ConstantDefine constant, String colValue, String javaDataType) { String template = String.join(System.lineSeparator(), " // {bizDesc}({tblName} [{col}]) : {colValueDesc}", " public static final {dataType} {upperCol}_{upperRemark} = {colValue};", ""); return template.replace("{bizDesc}", constant.getBizDesc()).replace("{tblName}", constant.getTblName()) .replace("{col}", constant.getCol()).replace("{upperCol}", constant.getCol().toUpperCase()) .replace("{colValueDesc}", constant.getColValueDesc()).replace("{dataType}", javaDataType) .replace("{upperRemark}", constant.getRemark().toUpperCase()).replace("{colValue}", colValue); } private static String domain2ModelNormal() { String template = String.join(System.lineSeparator(), " item.set{upperColumnName}(this.get{upperColumnName}());", ""); return template; } // 示例：item.setActCode(this.getSapoActInfo()==null?null:this.getSapoActInfo().getCode()); private static String domain2ModelFk(String humpColumnName,String fkModelDomainName,String referencedColumnName) { String template = String.join(System.lineSeparator(), " item.set{upperColumnName}(this.get{fkModelDomainName}() == null ? null : this.get{fkModelDomainName}().get{referencedColumnName}());", ""); return template.replace("{upperColumnName}", firstUpper(humpColumnName)) .replace("{fkModelDomainName}", firstUpper(fkModelDomainName)) .replace("{referencedColumnName}", firstUpper(referencedColumnName)); } private static String domainConstructorNormal() { String template = String.join(System.lineSeparator(), " this.{columnName} = {lowerModelName}.get{upperColumnName}();", System.lineSeparator()); return template; } private static String domainConstructorFk() { String template = String.join(System.lineSeparator(), " // 【############################# 外键字段 #############################】", " // 外键关联对象：{referencedTableName}", " object = colMap.get(\"{lowerFkModelDomainName}\");", " if (object != null && object instanceof Map<?, ?>&& ! ((Map<?,?>)object).isEmpty() && {lowerModelName}.get{upperColumnName}() != null) {", " {fkModelName} {lowerFkModelName}ForQuery = new {fkModelName}();", " if(threadObj.get()!=null && threadObj.get().get(\"{fkModelName}\")!=null) {", " {lowerFkModelName}ForQuery = {fkModelName}.getInstance(({fkModelName}) threadObj.get().get(\"{fkModelName}\"));", " }", " if({lowerFkModelName}ForQuery.get{referencedColumnName}() != null){", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),\"fk col can not set in ThreadLocalCache.threadObj: {lowerFkModelName}ForQuery.get{referencedColumnName}() != null\");", " }", " {lowerFkModelName}ForQuery.set{referencedColumnName}({lowerModelName}.get{upperColumnName}());", " this.{lowerFkModelDomainName} = new {fkModelDomainName}({lowerFkModelName}ForQuery,(Map<String, Object>) object);", " }", ""); return template; } private static String domainConstructorListCol(String tableNameUpper, String tableNameLower, String columnNameUpper,String referencedTableNamelower,String referencedColumnNameUpper) { String template = String.join(System.lineSeparator(), " //【##################### 集合字段 #############################】", " ", " object = colMap.get(\"{tableNameLower}InfoList\");", " if(object!= null && object instanceof Map<?, ?> && ! ((Map<?,?>)object).isEmpty()) {", " {tableNameUpper} {tableNameLower}ForQuery = new {tableNameUpper}();", " if(threadObj.get()!=null && threadObj.get().get(\"{tableNameUpper}\")!=null) {", " {tableNameLower}ForQuery = {tableNameUpper}.getInstance(({tableNameUpper} ) threadObj.get().get(\"{tableNameUpper}\"));", " }", " ", " // 外键字段不能再ThreadLocal中设置", " if({tableNameLower}ForQuery.get{columnNameUpper}() !=null){", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),\"list col fk col can not set in ThreadLocalCache.threadObj: {tableNameLower}ForQuery.get{columnNameUpper} !=null\");", " }", "", " {tableNameLower}ForQuery.set{columnNameUpper}({referencedTableNamelower}.get{referencedColumnNameUpper}());", " ", " this.{tableNameLower}InfoList = {tableNameUpper}Info.getObjInfoList({tableNameLower}ForQuery,(Map<String, Object>) object);", " }else if(object!= null && object instanceof Map<?, ?> && ((Map<?,?>)object).isEmpty()) {", " this.{tableNameLower}InfoList = new ArrayList<>(0);", " }", System.lineSeparator()); return template.replace("{tableNameUpper}",tableNameUpper ) .replace("{tableNameLower}",tableNameLower ) .replace("{columnNameUpper}", columnNameUpper) .replace("{referencedTableNamelower}", referencedTableNamelower) .replace("{referencedColumnNameUpper}", referencedColumnNameUpper); } private static void generateModelFile(String tableName, List<TableColumn> tableColumnList, List<ConstantDefine> constantList) throws IOException { // 如果该表在黑名单中，直接返回，不生成实体类。 if (!(tableNameList.isEmpty() || tableNameList.contains(tableName))) { return; } System.out.println("Generate {tableName} model".replace("{tableName}", tableName)); // 将表名转化为实体类名。 String className = tblName2ModelFileName(tableName); // import语句列表 List<String> importList = new ArrayList<>(); importList.add("import java.io.Serializable;"); importList.add("import java.util.*;"); // 拼接字段列表 `id`,`name`,`age` StringBuilder colListSb = new StringBuilder(); tableColumnList.stream().forEach((col) -> { colListSb.append("`").append(col.getColumnName()).append("`").append(" , "); }); String colList = colListSb.deleteCharAt(colListSb.length() - 2).toString(); // 先生成常量字符串 StringBuilder constantSb = new StringBuilder(); StringBuilder fieldsSb = new StringBuilder(); StringBuilder methodSb = new StringBuilder(); // 循环每个字段 for (TableColumn element : tableColumnList) { String columnName = element.getColumnName(); String dataType = element.getDataType(); String columnComment = element.getColumnComment(); String humpColumnName = lineToHump(columnName.toLowerCase()); String javaDataType = jdbcTypeToJava(dataType.toUpperCase()); if (columnComment == null) { columnComment = ""; } // 根据数据类型，添加import语句 addDateTypeImport(importList, javaDataType); // 如果有常量集合,遍历常量集合,拼接常量语句 if (constantList != null && constantList.size() != 0) { constantList.stream().forEach((constant) -> { String colValue = constant.getColValue(); // 如果常量代表的字段是字符类型，则拼接字符串 if (javaDataType.equals("String")) { colValue = "\"" + colValue + "\""; } // 拼接上模板 if (columnName.equals(constant.getCol())) { constantSb.append(constantTemplate(constant, colValue, javaDataType)); } }); } // 属性字段 fieldsSb.append(replace4Field(fieldTemplate(), humpColumnName, javaDataType, columnComment)) .append(System.lineSeparator()); // 方法内容 methodSb.append( replace4Field(methodTemplate4Model(javaDataType), humpColumnName, javaDataType, columnComment)) .append(System.lineSeparator()); } // import内容 StringBuilder importSb = new StringBuilder(); for (String importPackage : importList) { importSb.append(importPackage).append(System.lineSeparator()); } // 生成mode文件内容 String content = replace4Model(importSb.toString(), constantSb.toString(), fieldsSb.toString(), methodSb.toString(), className, colList, tableName); // 把model文件内容写入文件 writeModelContentToFile(content, className); } /* * 功能：将model文件内容写入到文件中 * */ private static void writeModelContentToFile(String content, String className) throws FileNotFoundException, IOException { String filePath = targetProject + File.separator + modelPackageName.replaceAll("\\.", Matcher.quoteReplacement(File.separator)); File pathFile = new File(filePath); if (!pathFile.exists()) { pathFile.mkdirs(); } File file = new File(filePath + File.separator + className + ".java"); BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false))); bw.write(content); bw.close(); } private static void writeServiceContentToFile(String content, String className) throws FileNotFoundException, IOException { String filePath = targetProject + File.separator + servicePackageName.replaceAll("\\.", Matcher.quoteReplacement(File.separator)); File pathFile = new File(filePath); if (!pathFile.exists()) { pathFile.mkdirs(); } File file = new File(filePath + File.separator + className + ".java"); BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false))); bw.write(content); bw.close(); } private static void generateDomainFile(String tableName, List<TableColumn> tableInfo, List<RefTableColumn> fkInfoList) throws IOException { // 如果表名在黑名单中，就不用为该表生成代码 if (!(tableNameList.isEmpty() || tableNameList.contains(tableName))) { return; } System.out.println("Generate {tableName} domain info".replace("{tableName}", tableName)); String modelName = tblName2ModelFileName(tableName); String className = tblName2DomainFileName(tableName); // import列表 List<String> importList = new ArrayList<>(); importList.add("import java.io.Serializable;"); importList.add("import java.util.*;"); importList.add("import com.fasterxml.jackson.annotation.*;"); importList.add("import " + modelPackageName + ".*;"); StringBuilder domain2ModelSb = new StringBuilder(); StringBuilder fieldsSb = new StringBuilder(); StringBuilder domainConstructorSb = new StringBuilder(); StringBuilder methodSb = new StringBuilder(); for (TableColumn element : tableInfo) { String columnName = element.getColumnName(); String dataType = element.getDataType(); String columnComment = element.getColumnComment()==null?"":element.getColumnComment(); String humpColumnName = lineToHump(columnName.toLowerCase()); String javaDataType = jdbcTypeToJava(dataType.toUpperCase()); // 根据数据类型添加import语句。 addDateTypeImport(importList, javaDataType); // 判断该表该字段是否为外键字段。 boolean isFk = false; String referencedTableName = ""; String referencedColumnName = ""; for (RefTableColumn fkInfo : fkInfoList) { if (tableName.equals(fkInfo.getTableName()) && columnName.equals(fkInfo.getColumnName())) { isFk = true; referencedTableName = fkInfo.getReferencedTableName(); referencedColumnName = fkInfo.getReferencedColumnName(); break; } } // 如果是外键字段 if (isFk) { // 外键对象model类名，domain类名。 String fkModelName = tblName2ModelFileName(referencedTableName); String fkModelDomainName =tblName2DomainFileName(referencedTableName); StringBuilder fkCommentSb = new StringBuilder(); // 如果字段时外键字段。字段属性 fkCommentSb.append("外键关联实体类(字段：[").append(columnName).append("] 注释：[").append(columnComment) .append("], 来自").append(referencedTableName).append("表").append(referencedColumnName).append(")"); fieldsSb.append(domainFieldTemplate(fkCommentSb.toString(), fkModelDomainName, firstLower(fkModelDomainName))); // domain --> model 语句。item.setActCode(this.getSapoActInfo() == null ? null : this.getSapoActInfo().getCode()); domain2ModelSb.append(domain2ModelFk(humpColumnName, fkModelDomainName, referencedColumnName)); domainConstructorSb .append(domainConstructorFk().replace("{upperColumnName}", firstUpper(humpColumnName)) .replace("{referencedTableName}", referencedTableName) .replace("{referencedColumnName}", firstUpper(referencedColumnName)) .replace("{fkModelName}", firstUpper(fkModelName)) .replace("{lowerFkModelName}", firstLower(fkModelName)) .replace("{fkModelDomainName}", firstUpper(fkModelDomainName)) .replace("{lowerFkModelDomainName}", firstLower(fkModelDomainName))) .append(System.lineSeparator()); methodSb.append(methodTemplate4Domain( "外键对象"+fkModelDomainName, firstLower(fkModelDomainName), className, fkModelDomainName)); } else { domain2ModelSb.append(replace4Field(domain2ModelNormal(), humpColumnName, javaDataType, columnComment)); fieldsSb.append(domainFieldTemplate(columnComment.toString(), javaDataType, firstLower(humpColumnName))); domainConstructorSb .append(replace4Field(domainConstructorNormal(), humpColumnName, javaDataType, columnComment)); methodSb.append(methodTemplate4Domain( columnComment, humpColumnName, className, javaDataType)); } } StringBuilder importSb = new StringBuilder(); for (String importPackage : importList) { importSb.append(importPackage).append(System.lineSeparator()); } /* 生成生成list类型的字段即 核心主表：teacher,merch_type tableName | columnName | referencedTableName| referencedColumnName student | teacherId | teacher | id merch | type_code | merch_type | code */ List<RefTableColumn> fieldList = fkInfoList.stream().filter((fkInfo)->{return fkInfo.getReferencedTableName().equals(tableName);}).collect(Collectors.toList()); for(RefTableColumn field:fieldList) { StringBuilder fieldCommentSb=new StringBuilder(); String tableNameUpper = firstUpper(tblName2ModelFileName(field.getTableName()));// SapoMerch String tableNameLower = firstLower(tblName2ModelFileName(field.getTableName()));// sapoMerch String columnNameUpper = firstUpper(lineToHump(field.getColumnName())); //TypeCode String referencedTableNamelower= firstLower(tblName2ModelFileName(field.getReferencedTableName()));//sapoMerchType String referencedColumnNameUpper = firstUpper(lineToHump(field.getReferencedColumnName())); //Code fieldCommentSb.append("被表[").append(field.getTableName()).append("] : [").append(field.getColumnName()).append("]字段引用"); fieldsSb.append(domainFieldTemplate(fieldCommentSb.toString(),"List<"+tableNameUpper+"Info>", tableNameLower+"InfoList")); methodSb.append(methodTemplate4Domain( tableNameLower+"InfoList", tableNameLower+"InfoList", className, "List<"+tableNameUpper+"Info>")); domainConstructorSb.append(domainConstructorListCol(tableNameUpper, tableNameLower, columnNameUpper, referencedTableNamelower, referencedColumnNameUpper)); } // domain getObjList方法。 methodSb.append(template4domainGetObjList(modelName)); String content = generateDomain(importSb.toString(), domain2ModelSb.toString(), fieldsSb.toString(), domainConstructorSb.toString(), methodSb.toString(), modelName, className); // 将内容写进domain文件 writeDomainContentInFile(className,content); } private static void writeDomainContentInFile(String className,String content) throws IOException { String filePath = targetProject + File.separator + domainPackageName.replaceAll("\\.", Matcher.quoteReplacement(File.separator)); File pathFile = new File(filePath); if (!pathFile.exists()) { pathFile.mkdirs(); } File file = new File(filePath + File.separator + className + ".java"); BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false))); bw.write(content); bw.close(); } // 添加import语句 private static void addDateTypeImport(List<String> importList, String javaDataType) { if (!importList.contains("import java.time.LocalDateTime;") && javaDataType.equals("LocalDateTime")) { importList.add("import java.time.LocalDateTime;"); } if (!importList.contains("import java.math.BigDecimal;") && javaDataType.equals("BigDecimal")) { importList.add("import java.math.BigDecimal;"); } return; } private static String replace4Field(String template, String columnName, String dataType, String columnComment) { return template.replace("{columnName}", firstLower(columnName)) .replace("{upperColumnName}", firstUpper(columnName)).replace("{dataType}", dataType) .replace("{columnComment}", columnComment); } private static String replace4Model(String importPackage, String constant, String fields, String method, String className, String colList, String tblName) { return modelTemplate().replace("{import}", importPackage).replace("{constant}", constant) .replace("{fields}", fields).replace("{method}", method).replace("{packageName}", modelPackageName) .replace("{className}", className).replace("{colList}", colList).replace("{tblName}", tblName); } private static String generateDomain(String importPackage, String domain2Model, String fields, String domainConstructor, String method, String modelName, String className) { return domainTemplate().replace("{import}", importPackage).replace("{domain2Model}", domain2Model) .replace("{fields}", fields).replace("{domainConstructor}", domainConstructor) .replace("{method}", method).replace("{modelName}", firstUpper(modelName)) .replace("{lowerModelName}", firstLower(modelName)).replace("{packageName}", domainPackageName) .replace("{className}", className); } // 下划线转驼峰 private static String lineToHump(String str) { java.util.regex.Pattern linePattern = Pattern.compile("_(\\w)"); java.util.regex.Matcher matcher = linePattern.matcher(str); StringBuffer sb = new StringBuffer(); while (matcher.find()) { matcher.appendReplacement(sb, matcher.group(1).toUpperCase()); } matcher.appendTail(sb); return sb.toString(); } // 首字母转大写 private static String firstUpper(String name) { return name.replaceFirst(name.substring(0, 1), name.substring(0, 1).toUpperCase()); } // 首字母转小写 private static String firstLower(String name) { return name.replaceFirst(name.substring(0, 1), name.substring(0, 1).toLowerCase()); } public static String template4ObjPageFile() { String template = String.join(System.lineSeparator(), "package {packageName};", "import {domainPackageName}.*;", "", "import java.util.*;", "", "import com.fasterxml.jackson.annotation.*;", "", "/**", " * Title: ObjPage Description:", " * ", " * @author wanglifeng", " * @param <T>", " * 数据库表model对应的实体类", " */", "public class ObjPage<T> implements ResData {", "", " @JsonIgnore", " private int total;", "", " @JsonIgnore", " private List<T> pageList = new ArrayList<>();", "", " @JsonIgnore", " private String pageObjName;", "", " public String getPageObjName() {", " return pageObjName;", " }", "", " public ObjPage<T> setPageObjName(String pageObjName) {", " this.pageObjName = pageObjName;", " return this;", " }", "", " public ObjPage() {", " super();", " // TODO Auto-generated constructor stub", " }", "", " public ObjPage(String pageObjName) {", " super();", " this.pageObjName = pageObjName;", " }", "", " public int getTotal() {", " return total;", " }", "", " public void setTotal(int total) {", " this.total = total;", " }", "", " public List<T> getPageList() {", " return pageList;", " }", "", " public void setPageList(List<T> pageList) {", " this.pageList = pageList;", " }", "", " @JsonAnyGetter", " public Map<String, Object> getSonsMap()", " throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException {", " /*", " * map格式： \"实体类中字段名\":\"要展示的新名字\" \"code\":\"storeCode\" \"name\":\"newName\"", " */", " HashMap<String, Object> resultMap = new HashMap<>();", "", " resultMap.put(\"total\", total);", " if (pageList != null && pageList.size() != 0) {", " resultMap.put(firstCharToLower(pageList.get(0).getClass().getSimpleName()) + \"List\", pageList);", " } else if (pageObjName != null) {", " resultMap.put(pageObjName, pageList);", " } else {", " resultMap.put(\"pageObjList\", pageList);", " }", "", " return resultMap;", "", " }", "", " public String firstCharToLower(String str) {", "", " return str.replaceFirst(str.substring(0, 1), str.substring(0, 1).toLowerCase());", " }", "", "}"); return template.replace("{packageName}",modelPackageName ).replace("{domainPackageName}", domainPackageName); } public static String template4CommonMapperFile() { String template = String.join(System.lineSeparator(), "package {packageName};", "", "import org.apache.ibatis.annotations.*;", "", "import java.util.*;", "", "/** ", "* Title: CommonMapper ", "* Description: ", "* @author wanglifeng ", "*/", "@Mapper", "public interface CommonMapper {", "", " // 单条插入:id自增", " @Insert({ \"<script> insert into ${tblName} <set> ${commonSet} </set> </script>\"})", " @Options(useGeneratedKeys = true, keyProperty = \"item.id\", keyColumn = \"id\")", " int insertObj(", " @Param(\"item\") Object objForInsert", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonSet\") String commonSet", " );", " ", " // 单条插入:id不自增", " @Insert({ \"<script> insert into ${tblName} <set> ${commonSet} </set> </script>\"})", " int insertObjNoIncr(", " @Param(\"item\") Object objForInsert", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonSet\") String commonSet", " );", " ", " // 删除操作", " @Delete({ ", " \"<script> \",", " \"delete from ${tblName} \",", " \"<where> ${commonWhere} </where>\", ", " \"</script>\" ", " }) ", " int deleteObj(", " @Param(\"queryObj\") Object objForDelete", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonWhere\") String commonWhere", " );", " // 查询对象", " @Select({ ", " \"<script> \",", " \"select ${colList} from ${tblName}\",", " \"<where> ${commonWhere} </where>\", ", " \"</script>\" ", " })", " Map<String, Object> getObj(", " @Param(\"queryObj\") Object objForQuery", " ,@Param(\"colList\") String colList", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonWhere\") String commonWhere", " );", "", " // 分页查询list", " @Select({ ", " \"<script> \",", " \"select count(1) from ${tblName}\",", " \"<where> ${commonWhere} </where>\", ", " \"</script>\" ", " }) ", " int getCount(", " @Param(\"queryObj\") Object objForQuery", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonWhere\") String commonWhere", " );", " @Select({ ", " \"<script> \",", " \"select ${colList} from ${tblName} \",", " \"<where> ${commonWhere} </where> \", ", " \" order by create_time desc limit #{num,jdbcType=INTEGER} ,#{size,jdbcType=INTEGER} \",", " \"</script>\" ", " }) ", " List<Map<String, Object>> getObjListByPage(", " @Param(\"queryObj\") Object objForQuery", " ,@Param(\"colList\") String colList", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonWhere\") String commonWhere", " ,@Param(\"num\") Integer pageNum", " ,@Param(\"size\") Integer pageSize", " );", " ", " ", " //查询对象集合", " @Select({ ", " \"<script> \",", " \"select ${colList} from ${tblName}\",", " \"<where> ${commonWhere} </where>\", ", " \"</script>\" ", " })", " List<Map<String, Object>> getObjList(", " @Param(\"queryObj\") Object objForQuery", " ,@Param(\"colList\") String colList", " ,@Param(\"tblName\") String tblName", " ,@Param(\"commonWhere\") String commonWhere", " );", " ", " @Update({ \"<script> update ${tblName} <set> ${commonSet} </set> <where> ${commonWhere} </where> </script>\"})", " int updateObj(", " @Param(\"item\") Object objForUpdate", " ,@Param(\"queryObj\") Object objForQuery", " ,@Param(\"commonSet\") String commonSet", " ,@Param(\"commonWhere\") String commonWhere", " ,@Param(\"tblName\") String tblName", " );", " ", "}" ); return template.replace("{packageName}",modelPackageName ); } public static String template4CommonModelFile() { String template = String.join(System.lineSeparator(), "package {packageName};", "import java.util.*;", "import java.util.regex.*;", "", "/** ", "* Title: CommonModel ", "* Description: ", "* @author wanglifeng ", "*/", "public abstract class CommonModel {", "", " public HashMap<String, String> colFlagMap = new HashMap<String, String>();", "", " public HashMap<String, String> fixedMessageMap = new HashMap<String, String>();", "", " public static final String TBL_NAME = \"tblName\";", " public static final String COL_LIST = \"colList\";", "", " // java 类型和jdbc类型映射关系", " protected static HashMap<String, String> java2JdbcTypeMap = new HashMap<String, String>();", "", " static {", " // java类型和jdbc类型映射", " java2JdbcTypeMap.put(\"String\", \"VARCHAR\");", " java2JdbcTypeMap.put(\"Integer\", \"INTEGER\");", " java2JdbcTypeMap.put(\"LocalDateTime\", \"TIMESTAMP\");", " java2JdbcTypeMap.put(\"Byte\", \"TINYINT\");", " java2JdbcTypeMap.put(\"Long\", \"BIGINT\");", " java2JdbcTypeMap.put(\"byte[]\", \"LONGVARBINARY\");", " }", "", " protected static String getAndColOperatorXXTemplate(String javaType, String colName, String operator) {", "", " String template = \" and `%col%` %operator% #{%queryObj-item%.%col_camel%,jdbcType=%jdbcType%} \";", " template = template.replace(\"%col%\", humpToLine(colName));", " template = template.replace(\"%col_camel%\", colName);", " template = template.replace(\"%jdbcType%\", java2JdbcTypeMap.get(javaType));", " template = template.replace(\"%operator%\", operator);", "", " return template;", " }", "", " protected static String getAndColFuzzyXXTemplate(String javaType, String colName) {", "", " String template = \" and `%col%` like concat('%',#{queryObj.%col_camel%,jdbcType=%jdbcType%},'%')\";", "", " template = template.replace(\"%col%\", humpToLine(colName));", " template = template.replace(\"%col_camel%\", colName);", " template = template.replace(\"%jdbcType%\", java2JdbcTypeMap.get(javaType));", "", " return template;", " }", "", " protected static String getAndColInXXTemplate(String colName, String listStr) {", "", " String template = \" and `%col%` in (%listStr%)\";", "", " template = template.replace(\"%col%\", humpToLine(colName));", " template = template.replace(\"%listStr%\", listStr);", "", " return template;", " }", "", " protected static String getAndColPlusXXTemplate(String colName, String num) {", "", " String template = \" and `%col%` = `%col%` + \" + num;", " template = template.replace(\"%col%\", humpToLine(colName));", " return template;", " }", "", " protected static String getAndColMinusXXTemplate(String colName, String num) {", "", " String template = \" and `%col%` = `%col%` - \" + num + \" \";", " template = template.replace(\"%col%\", humpToLine(colName));", " return template;", " }", "", " protected static String getAndColIsNullTemplate(String colName) {", " String template = \" and `%col%` is null \";", " template = template.replace(\"%col%\", humpToLine(colName));", " return template;", " }", "", " public String getTblName() {", " if (fixedMessageMap.containsKey(TBL_NAME)) {", " return fixedMessageMap.get(TBL_NAME);", " } else {", " throw new RuntimeException(\"commonModel find no tblName\");", " }", "", " }", "", " public String getColList() {", " if (fixedMessageMap.containsKey(COL_LIST)) {", " return fixedMessageMap.get(COL_LIST);", " } else {", " throw new RuntimeException(\"commonModel find no colList\");", " }", " }", "", " public String getQueryTerm() {", " ", " if(colFlagMap.isEmpty()) {", " throw new RuntimeException(\"commonModel getQueryTerm() colFlagMap is empty\");", " }", "", " StringBuilder sb = new StringBuilder();", "", " Set<String> keySet = colFlagMap.keySet();", " for (String s : keySet) {", " sb.append(colFlagMap.get(s));", " }", "", " return sb.toString().replace(\"%queryObj-item%\", \"queryObj\");", " }", "", " public String getUpdateTerm() {", "", " if(colFlagMap.isEmpty()) {", " throw new RuntimeException(\"commonModel getUpdateTerm() colFlagMap is empty\");", " }", " ", " StringBuilder sb = new StringBuilder();", "", " Set<String> keySet = colFlagMap.keySet();", " for (String s : keySet) {", " sb.append(colFlagMap.get(s).replace(\" and \", \" \")).append(\",\");", " }", "", " return sb.toString().replace(\"%queryObj-item%\", \"item\");", " }", "", " /**", " * 驼峰转下划线,最后转为大写", " * ", " * @param str", " * @return", " */", " private static String humpToLine(String str) {", " java.util.regex.Pattern humpPattern = java.util.regex.Pattern.compile(\"[A-Z]\");", " java.util.regex.Matcher matcher = humpPattern.matcher(str);", " StringBuffer sb = new StringBuffer();", " while (matcher.find()) {", " matcher.appendReplacement(sb, \"_\" + matcher.group(0).toLowerCase());", " }", " matcher.appendTail(sb);", " return sb.toString();", " }", "", " /**", " * 下划线转驼峰,正常输出", " * ", " * @param str", " * @return", " */", " private static String lineToHump(String str) {", " java.util.regex.Pattern linePattern = Pattern.compile(\"_(\\\\w)\");", " java.util.regex.Matcher matcher = linePattern.matcher(str.toLowerCase());", " StringBuffer sb = new StringBuffer();", " while (matcher.find()) {", " matcher.appendReplacement(sb, matcher.group(1).toUpperCase());", " }", " matcher.appendTail(sb);", " return sb.toString();", " }", " ", " public String toString() {", " ", " cn.hutool.json.JSONObject parseObj = cn.hutool.json.JSONUtil.parseObj(this);", " parseObj.remove(\"fixedMessageMap\");", " parseObj.remove(\"colFlagMap\");", " ", " return parseObj.toString();", " ", " }", "", "}" ); return template.replace("{packageName}",modelPackageName ); } public static String template4CommonDaoFile() { String template = String.join(System.lineSeparator(), "/*", "* Title: CommonDao.java", "* Description: ", "* Copyright: Copyright (c) 2019", "* Company: fmsh", "* @author wanglifeng", "*/", "package {packageName};", "", "import java.util.*;", "import java.util.regex.*;", "", "import org.springframework.beans.factory.annotation.*;", "import org.springframework.dao.*;", "import org.springframework.stereotype.*;", "", "", "/** ", "* Title: CommonDao ", "* Description: ", "* @author wanglifeng ", "*/", "@Service", "public class CommonDao extends BaseLogger {", " @Autowired", " protected CommonMapper mapper;", "", " @Autowired", " protected CommonDao dao ;", " ", " public CommonDao() {", "", " }", "", " /**", " * 驼峰转下划线,最后转为大写", " * ", " * @param str", " * @return", " */", " private String humpToLine(String str) {", " java.util.regex.Pattern humpPattern = java.util.regex.Pattern.compile(\"[A-Z]\");", " java.util.regex.Matcher matcher = humpPattern.matcher(str);", " StringBuffer sb = new StringBuffer();", " while (matcher.find()) {", " matcher.appendReplacement(sb, \"_\" + matcher.group(0).toLowerCase());", " }", " matcher.appendTail(sb);", " return sb.toString();", " }", "", " /**", " * 下划线转驼峰,正常输出", " * ", " * @param str", " * @return", " */", " private String lineToHump(String str) {", " java.util.regex.Pattern linePattern = Pattern.compile(\"_(\\\\w)\");", " java.util.regex.Matcher matcher = linePattern.matcher(str.toLowerCase());", " StringBuffer sb = new StringBuffer();", " while (matcher.find()) {", " matcher.appendReplacement(sb, matcher.group(1).toUpperCase());", " }", " matcher.appendTail(sb);", " return sb.toString();", " }", "", " /**", " * 通用插入,id自增。插入结果不为空，否则报错", " * ", " * @param objForInsert", " * 需要插入的对象", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", " public <T> void insertObj(T objForInsert) {", "", " CommonModel model = checkInputObj(objForInsert);", "", " Integer result = null;", " try {", " result = mapper.insertObj(objForInsert, model.getTblName(), model.getUpdateTerm());", " } catch (DuplicateKeyException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), \"commondao insertObj method ,tblName=\"", " + model.getTblName() + \" , duplicate exception ,bizId=\" + BizLogUtils.getValueOfBizId(), e);", " }", "", " // 如果没有查到东西，报错", " if (result == null || result == 0) {", "", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao insertObj method insert result is null or zero ,tblName= \" + model.getTblName()", " + \",bizId=\" + BizLogUtils.getValueOfBizId());", " }", " }", "", " /**", " * 通用插入,id不自增。插入结果不为空，否则报错", " * ", " * @param objForInsert", " * 需要插入的对象", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", " public <T> void insertObjNoIncr(T objForInsert) {", "", " CommonModel model = checkInputObj(objForInsert);", "", " Integer result = null;", " try {", " result = mapper.insertObjNoIncr(objForInsert, model.getTblName(), model.getUpdateTerm());", " } catch (DuplicateKeyException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao insertObjNoIncr method ,tblName=\" + model.getTblName() + \" , duplicate exception ,bizId=\"", " + BizLogUtils.getValueOfBizId(),", " e);", " }", "", " // 如果没有查到东西，报错", " if (result == null || result == 0) {", "", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao insertObjNoIncr method insert result is null or zero ,tblName= \" + model.getTblName()", " + \",bizId=\" + BizLogUtils.getValueOfBizId());", " }", " }", "", " /**", " * 通用删除。", " * ", " * @param objForDelete", " * 需要删除的对象，用于组where条件", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", " public <T> void deleteObj(T objForDelete) {", "", " CommonModel model = checkInputObj(objForDelete);", "", " Integer result;", " try {", " result = mapper.deleteObj(objForDelete, model.getTblName(), dao.getQueryTerm(model));", " } catch (DataIntegrityViolationException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " ResultInfo.SYS_INNER_ERROR.getDesc() + \" commondao deleteObj method , delete \" + model.getTblName()", " + \", DataIntegrityViolationException maybe cause by fk or col can not null , bizId=\"", " + BizLogUtils.getValueOfBizId(),", " e);", " }", "", " if (result == null || result == 0) {", "", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), \"commondao deleteObj method delete \"", " + model.getTblName() + \" result is null or zero ,bizId=\" + BizLogUtils.getValueOfBizId());", " }", " }", "", " /**", " * 通用条件更新,返回更新结果", " * ", " * @param objForUpdate", " * 更新条件对象", " * @param objForQuery", " * 查询条件对象", " * @param <T>", " * 数据库表model实体类", " * @return 更新结果", " * @author wanglifeng", " */", " public <T> int updateObjWithResult(T objForUpdate, T objForQuery) {", "", " CommonModel modelUpdate = checkInputObj(objForUpdate);", " CommonModel modelQuery = checkInputObj(objForQuery);", "", " Integer result=0;", " try {", " result = mapper.updateObj(objForUpdate, objForQuery, modelUpdate.getUpdateTerm(), dao.getQueryTerm(modelQuery),", " modelQuery.getTblName());", " } catch (DuplicateKeyException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), \"commondao updateObjWithResult method ,tblName=\"", " + modelQuery.getTblName() + \" , duplicate exception ,bizId=\" + BizLogUtils.getValueOfBizId(), e);", " } catch (DataIntegrityViolationException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao updateObjWithResult method ,tblName=\" + modelQuery.getTblName()", " + \" , DataIntegrityViolationException maybe cause by fk or col can not null , bizId=\"", " + BizLogUtils.getValueOfBizId(),", " e);", " }", "", " return result;", " }", " ", " ", " ", " /**", " * 通用条件更新,如果更新结果为零，则直接报错。", " * ", " * @param objForUpdate", " * 更新条件对象", " * @param objForQuery", " * 查询条件对象", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", " public <T> void updateObj(T objForUpdate, T objForQuery) {", "", " ", " if(updateObjWithResult(objForUpdate, objForQuery)==0) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao updateObj method ,tblName=\" + checkInputObj(objForQuery).getTblName()", " + \" , update result is null or zero , bizId=\" + BizLogUtils.getValueOfBizId());", " }", " ", " }", "", " private <T> CommonModel checkInputObj(T objForQuery) {", " CommonModel model = objForQuery instanceof CommonModel ? (CommonModel) objForQuery : null;", "", " // 入参进行判单", " if (model == null || model.colFlagMap.isEmpty()) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \" commondao checkInputObj method input [objForQuery is null] or [objForQuery not CommonModel child class] or [CommonModel.colFlagMap.isEmpty] ,bizId=\"", " + BizLogUtils.getValueOfBizId());", " }", "", " return model;", " }", "", " ", " /**", " * 通用分页模糊查询,返回对象集合,集合可以为空", " * ", " * @param objForQuery", " * 组where条件的对象", " * @param pageNum", " * 页码", " * @param pageSize", " * 每页条数", " * @param <T>", " * 数据库表model实体类", " * @return 返回T对象集合", " * @author wanglifeng", " */", " public <T> int getObjCount(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " return mapper.getCount(objForQuery, model.getTblName(), dao.getQueryTerm(model)); ", " }", " ", " /**", " * 通用查询,返回对象,对象不为空，否则报错", " * ", " * @param objForQuery", " * 组where条件的对象", " * @return T 返回T对象", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", "", " public <T> T getObj(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " // 查询结果map集合", " Map<String, Object> map = mapper.getObj(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(model));", "", " // 如果没有查到东西，报错", " if (map == null || map.size() == 0) {", " bizLogger.warn(\" commondao getObj method select \" + model.getTblName() + \" , but result is null \");", " throw new BusinessException(ResultInfo.NO_DATA.getCode(),", " ResultInfo.NO_DATA.getDesc() + \" ,bizId=\" + BizLogUtils.getValueOfBizId());", " }", "", " return tansMap2Obj(objForQuery.getClass(), map);", "", " }", " ", " public String getQueryTerm(CommonModel model) {", " return model.getQueryTerm();", " }", " /**", " * 通过主键id查询对象,返回对象,对象不为空，否则报错", " * ", " * @param objForQuery", " * 组where条件的对象", " * @return T 返回T对象", " * @param <T>", " * 数据库表model实体类", " * @author wanglifeng", " */", " public <T> T getObjById(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", " ", " // 复制出一个同样的对象。", " CommonModel bean = cn.hutool.json.JSONUtil.parseObj(objForQuery).toBean(objForQuery.getClass());", "", " String idWhereTerm = model.colFlagMap.get(\"id\");", " // 清空条件列表", " bean.colFlagMap.clear();", " // 只保留id的条件", " bean.colFlagMap.put(\"id\", idWhereTerm);", " ", " ", " // 查询结果map集合", " Map<String, Object> map = mapper.getObj(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(bean));", "", " // 如果没有查到东西，报错", " if (map == null || map.size() == 0) {", " bizLogger.warn(\" commondao getObj method select \" + model.getTblName() + \" , but result is null \");", " throw new BusinessException(ResultInfo.NO_DATA.getCode(),", " ResultInfo.NO_DATA.getDesc() + \" ,bizId=\" + BizLogUtils.getValueOfBizId());", " }", "", " return tansMap2Obj(objForQuery.getClass(), map);", "", " }", "", " /**", " * 通用查询,返回对象,对象可为空", " * ", " * @param objForQuery", " * 组where条件的对象", " * ", " * @param <T>", " * 数据库表model实体类", " * @return T T类型对象", " * @author wanglifeng", " */", " public <T> T getObjWithNull(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " // 查询结果map集合", " Map<String, Object> map = mapper.getObj(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(model));", "", " // 如果没有查到东西，返回空", " if (map == null || map.size() == 0) {", "", " return null;", " }", "", " return tansMap2Obj(objForQuery.getClass(), map);", " }", "", " /**", " * 通用查询,返回对象集合,集合不为空，否则报错", " * ", " * @param objForQuery", " * 组where条件的对象", " * @param <T>", " * 数据库表model实体类", " * @return 返回T对象集合", " * @author wanglifeng", " */", " public <T> List<T> getObjList(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " // 查询结果map集合", " List<Map<String, Object>> list = mapper.getObjList(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(model));", "", " // 如果没有查到东西，报错", " if (list == null || list.size() == 0) {", " bizLogger", " .warn(\"commondao getObjList select \" + model.getTblName() + \" , but result list is null or empty \");", " throw new BusinessException(ResultInfo.NO_DATA.getCode(),", " ResultInfo.NO_DATA.getDesc() + \" ,bizId=\" + BizLogUtils.getValueOfBizId());", " }", "", " List<T> arrayList = new ArrayList<T>(list.size());", "", " list.stream().forEach(map -> arrayList.add(tansMap2Obj(objForQuery.getClass(), map)));", "", " return arrayList;", " }", "", " /**", " * 通用查询,返回对象集合,集合可为空", " * ", " * @param objForQuery", " * 组where条件的对象", " * @param <T>", " * 数据库表model实体类", " * @return 返回T对象集合", " * @author wanglifeng", " */", " public <T> List<T> getObjListWithEmpty(T objForQuery) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " // 查询结果map集合", " List<Map<String, Object>> list = mapper.getObjList(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(model));", "", " // 如果没有查到东西，报错", " if (list == null || list.size() == 0) {", " return new ArrayList<T>(0);", " }", "", " List<T> arrayList = new ArrayList<T>(list.size());", "", " list.stream().forEach(map -> arrayList.add(tansMap2Obj(objForQuery.getClass(), map)));", "", " return arrayList;", " }", "", " /**", " * 通用分页模糊查询,返回对象集合,集合可以为空", " * ", " * @param objForQuery", " * 组where条件的对象", " * @param pageNum", " * 页码", " * @param pageSize", " * 每页条数", " * @param <T>", " * 数据库表model实体类", " * @return 返回T对象集合", " * @author wanglifeng", " */", " public <T> ObjPage<T> getObjListWithEmptyByPage(T objForQuery, Integer pageNum, Integer pageSize) {", "", " CommonModel model = checkInputObj(objForQuery);", "", " if (pageNum < 1 || pageSize < 1) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \" commondao getObjListWithEmptyByPage method input pageNum or pageSize < 1 ,bizId=\"", " + BizLogUtils.getValueOfBizId());", " }", "", " pageNum = (pageNum - 1) * pageSize;", "", " ObjPage<T> objPage = new ObjPage<T>();", " List<T> arrayList = new ArrayList<T>();", " // 默认按照create_time 降序排列。即刚创建的在前面显示", " int count = mapper.getCount(objForQuery, model.getTblName(), dao.getQueryTerm(model));", "", " objPage.setTotal(count);", " // 如果查询结果为0，或者最大值已经大于起始值。没必要进行下一步查询", " if (count == 0 || count <= pageNum) {", " objPage.setPageList(arrayList);", " return objPage;", " }", "", " List<Map<String, Object>> result = mapper.getObjListByPage(objForQuery, model.getColList(), model.getTblName(),", " dao.getQueryTerm(model), pageNum, pageSize);", "", " result.stream().forEach(map -> arrayList.add(tansMap2Obj(objForQuery.getClass(), map)));", " objPage.setPageList(arrayList);", "", " // service层可以从Page中获取总条数", " return objPage;", " }", "", " private <T> T tansMap2Obj(Class<? extends Object> class1, Map<String, Object> map) {", "", " // 最终返回值", " HashMap<String, Object> resultMap = new HashMap<String, Object>(map.size());", "", " // 将下划线map转成驼峰map。例如：key=create_time ->key=createTime", " Set<String> keySet = map.keySet();", " for (String s : keySet) {", " resultMap.put(lineToHump(s), map.get(s));", " }", "", " // 利用json反序列化新建对象。", " cn.hutool.json.JSONObject jsonObject = new cn.hutool.json.JSONObject();", " jsonObject.putAll(resultMap);", "", " return jsonObject.toBean(class1);", " }", "", " /**", " * 通过id字段更新对象,如果更新结果为零。报错", " * ", " * @param objForUpdate", " * 组where条件的对象", " * @param <T>", " * 数据库表model对象", " * @author wanglifeng", " * ", " */", " public <T> void updateObjById(T objForUpdate) {", "", " if(updateObjByIdWithResult(objForUpdate)==0) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), \"commondao updateObjById method ,tblName=\"", " + checkInputObj(objForUpdate).getTblName() + \" , update result is null or zero , bizId=\" + BizLogUtils.getValueOfBizId());", " }", " }", " ", " /**", " * 通过id字段更新对象,返回更新结果", " * ", " * @param objForUpdate", " * 组where条件的对象", " * @param <T>", " * 数据库表model对象", " * @return 更新结果", " * @author wanglifeng", " * ", " */", " public <T> int updateObjByIdWithResult(T objForUpdate) {", "", " CommonModel model = checkInputObj(objForUpdate);", "", " String whereTerm = model.colFlagMap.get(\"id\");", "", " // 复制出一个同样的对象。", " CommonModel bean = cn.hutool.json.JSONUtil.parseObj(objForUpdate).toBean(objForUpdate.getClass());", "", " String idWhereTerm = model.colFlagMap.get(\"id\").replace(\"%queryObj-item%\", \"item\");", " // 清空条件列表", " bean.colFlagMap.clear();", " // 只保留id的条件", " bean.colFlagMap.put(\"id\", idWhereTerm);", " ", " model.colFlagMap.remove(\"id\");", " ", " Integer result=0;", " try {", " result = mapper.updateObj(objForUpdate, null, model.getUpdateTerm(), dao.getQueryTerm(bean), model.getTblName());", " } catch (DuplicateKeyException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), \"commondao updateObjById method ,tblName=\"", " + model.getTblName() + \" , duplicate exception ,bizId=\" + BizLogUtils.getValueOfBizId(), e);", " } catch (DataIntegrityViolationException e) {", " throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),", " \"commondao updateObjById method ,tblName=\" + model.getTblName()", " + \" , DataIntegrityViolationException maybe cause by fk or col can not null , bizId=\"", " + BizLogUtils.getValueOfBizId(),", " e);", " }", "", " model.colFlagMap.put(\"id\", whereTerm);", " ", " return result;", " }", "", "}" ); return template.replace("{packageName}",modelPackageName ); } public static String template4Aspect4ControllerFile() { String template = String.join(System.lineSeparator(), "package {packageName};", "import {domainPackageName}.*;", "import {modelPackageName}.*;", "import org.aspectj.lang.*;", "import org.aspectj.lang.annotation.*;", "import org.springframework.beans.factory.annotation.*;", "import org.springframework.stereotype.*;", "", "/**", " * Title: AuthAspect Description:", " * ", " * @author wanglifeng", " */", "@Aspect", "@Component", "public class Aspect4Controller extends BaseController {", "", " @Autowired", " private HttpServletRequest httpServletRequest;", "", " @Autowired", " private BizLoggerComponent bizLoggerComponent;", "", " /**", " * 定义切点。 拦截 controller包下所有类的所有方法", " */", " @Pointcut(\"execution(* cn.com.fmsh.cake.controller.*.*(..)) \")", " public void controllerMethodPointCut() {", " };", " ", " // 只拦截特定入参的方法，入参必须是requestData,bindingResult这两个参数，否则拦截不到这个方法。", " // 例如：notify1( @RequestBody @Valid NotifyData requestData,BindingResult bindingResult)", " @Around(\"controllerMethodPointCut() && args(requestData,bindingResult)\")", " public Object controllerMethodPointCut(ProceedingJoinPoint joinPoint,Object requestData,BindingResult bindingResult) throws Throwable {", "", " System.err.println(\"###################### hit controllerMethodPointCut ###################\");", " // 接口路径", " String apiPath = httpServletRequest.getServletPath();", " // 获取header", " String header = httpServletRequest.getHeader(\"Content-Type\");", "", " /* 设置日志编号 */", " BizLogUtils.putValueOfBizId(UuidUtil.getUUID());", "", " ", " System.err.println(\"path : \" + apiPath);", "", " System.err.println(\"header : \" + header);", " ", " System.err.println(\"bindingResult: \"+bindingResult.toString());", " ", " ", " /* 校验请求数据 */", " checkValidation(bindingResult);", " ", " // 打印日志，入参", " bizLoggerComponent.receiveRestRequestLog(bizLogger, httpServletRequest.getServletPath(), requestData,", " \" into [controller method] : \" + joinPoint.getSignature().getName() );", "", " // TODO 根据业务，获取角色信息", " // TODO 根据角色、api路径：放入ThreadLocal为后续数据授权能做准备。", " // TODO 判断该角色是否对该api有权限访问", " ", " ", " // 执行 真正的 controller方法。", " Object obj = joinPoint.proceed();", "", " // 打印日志，返回日志。", " bizLoggerComponent.sendRestResponseLog(bizLogger, httpServletRequest.getServletPath(), obj,", " \" outof [controller method] : \" + joinPoint.getSignature().getName() );", "", " return obj;", "", " }", "", "}" ); return template.replace("{packageName}", servicePackageName).replace("{modelPackageName}",modelPackageName ).replace("{domainPackageName}", domainPackageName); } public static String template4DataAuthAspectFile() { String template = String.join(System.lineSeparator(), "package {packageName};", "import {domainPackageName}.*;", "import {modelPackageName}.*;", "import org.aspectj.lang.*;", "import org.aspectj.lang.annotation.*;", "import org.springframework.beans.factory.annotation.*;", "import org.springframework.stereotype.*;", "", "/** ", "* Title: AuthAspect ", "* Description: ", "* @author wanglifeng ", "*/", "@Aspect", "@Component", "public class Aspect4DataAuth {", "", " /**", " * 获取查询条件切点。拦截CommonDao中getQueryTerm方法。在查询条件后织入权限校验语句。", " */", " @Pointcut(\"execution(* CommonDao.getQueryTerm(..)) \")", " public void commonDaoGetQueryTermPointCut() {", " };", " ", " // 重建查询语句通知", " //@Around(\"commonDaoGetQueryTermPointCut() && args(model)\")", " public String rebuildQueryTerm(ProceedingJoinPoint joinPoint,CommonModel model) throws Throwable {", " ", " String originalQueryTerm =(String) joinPoint.proceed();", " String tblName = model.getTblName();", " ", " System.err.println(\" ############## hit commonDaoGetQueryTermPointCut() ############## \");", " System.err.println(\"originalQueryTerm: \"+originalQueryTerm);", " ", " // 通过threadLocal获取 接口路径，角色: ThreadLocalCache.threadObj", " // TODO 通过【接口+角色+表名】 获取权限条件列表。拼接权限过滤Sql. 例如：and code in (3,4,5) and create_time < xxx", " ", " ", " return originalQueryTerm+\" and 1=1\";", " }", " ", " ", "}" ); return template.replace("{packageName}",servicePackageName ).replace("{domainPackageName}", domainPackageName).replace("{modelPackageName}", modelPackageName); } public void assistControllDemo() { //###################################### //###################################### /********** controller 示例 ****************/ //###################################### //###################################### // @RestController // @RequestMapping("/sapo/biz") // public class StoresController extends BaseController { // // @Autowired // private HttpServletRequest httpServletRequest; // // @Autowired // private BizLoggerComponent bizLoggerComponent; // // /* service层方法 */ // @Autowired // private StoresService storeSevice; // // // // /* // public BaseResponse getStores10( // @RequestHeader(name = Constant.USER_TOKEN, required = Constant.IF_USER_TOKEN_CHECK) String token // , @PathVariable("store-code") String storeCode // , @Valid GetStores10Req request, BindingResult bindingResult // ) throws Exception // */ // @GetMapping("/sapo/biz/1/1/orders/{order-no}") // public BaseResponse getOrderDetail11( // @RequestHeader(name = Constant.USER_TOKEN, required = Constant.IF_USER_TOKEN_CHECK) String token, // @PathVariable("order-no") String orderNo, HttpServletRequest httpServletRequest) // throws Exception { // BaseResponse baseResponse = new BaseResponse(ResultInfo.SYS_SUCCESS); // /* 获取本方法名 */ // String methodName = Thread.currentThread().getStackTrace()[1].getMethodName(); // /* 设置日志编号 */ // BizLogUtils.putValueOfBizId(UuidUtils.getUuid()); // /* 输出请求日志 */ // bizLoggerComponent.receiveRestRequestLog(bizLogger, httpServletRequest.getServletPath(), orderNo, // " [controller method] " + methodName + " method request "); // /* 校验请求数据 */ // // checkValidation(bindingResult); // /* 获取返回数据json定义 */ // Map<String, Object> colMap = ThreadLocalCache.getColMap(methodName); // // // /* TODO ########################## 调用服务层 ###################################### */ // ResData resData = getOrderDetailService.getOrderDetail11(colMap,token, orderNo); // // // baseResponse.setResData(resData); // /* 输出应答日志 */ // bizLoggerComponent.sendRestResponseLog(bizLogger, httpServletRequest.getServletPath(), baseResponse, // " [controller method] " + methodName + " method reposnse "); // // return baseResponse; // } // //} } public void assistServiceDemo() { //###################################### //###################################### /********** service 示例 *********************************/ //###################################### //###################################### // //定制返回字段 // ArrayList<Object> arrayList = new ArrayList<>(); // for (SapoStoreDeviceInfo sapoStoreDeviceInfo : objInfoList) { // // LgDeviceInfo lgDeviceInfo = lgThinQService.getDeviceInfo(sapoStoreDeviceInfo.getDeviceId()); // // Integer remain = lgDeviceInfo.getRemain(); // // HashMap<String, Object> map = new HashMap<>(); // map.put("remainTime", remain); // // // 在sapoStoreDeviceInfo对象中加入属性remainTime属性及其对应值。 // arrayList.add(MySpringUtils.getTarget(sapoStoreDeviceInfo, map)); // // } // // // 新建属性，属性名->storeDeviceInfoList,属性值->arrayList // HashMap<String, Object> hashMap = new HashMap<>(); // hashMap.put("storeDeviceInfoList", arrayList); // // // 新建ResData(), 将hashMap中属性及属性对应的值，加入到新建的ResData对象中 // return (ResData) MySpringUtils.getTarget(new ResData(){}, hashMap); // // //返回多个对象 实例 // HashMap<String, Object> hashMap = new HashMap<>(); // hashMap.put("storeDeviceInfoList", objInfoList); // // return (ResData) MySpringUtils.getTarget(new ResData(){}, hashMap); // // //返回分页数据 // SapoOrderInfo.getObjInfoListByPage(sapoOrderForQuery, colMap,pageNum, pageSize).setPageObjName("sapoOrderInfoList"); } public void assistGenerateJsonSql() { /* - -- ################################################## -- ################################################## -- 为每个表生成jsonMap对象。 -- ################################################## -- ################################################## -- 形如： -- { -- "_jsonPropertyName":"" -- , "id":"id" -- , "code":"code" -- , "createTime":"createTime" -- , "lastUpdateTime":"lastUpdateTime" -- , "sapoActType":{} -- , "sapoActReleaseInfoList:{} -- , "sapoActScopeInfoList:{} -- , "sapoUserCouponInfoList:{} -- } -- 表名去除前缀。默认是:tbl_ SET @pre_fix="tbl_"; DROP TABLE if EXISTS all_col_table; CREATE table if not exists all_col_table( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT primary key COMMENT '主键', tbl_name VARCHAR(256) COMMENT '表名:tbl_sapo_admin_account', tbl_name_comment VARCHAR(256) COMMENT '表注释', tbl_name_camel VARCHAR(256) COMMENT '表明驼峰', if_fk VARCHAR(512) NOT NULL DEFAULT 'no' COMMENT '外键标志，yes=外键', col VARCHAR(256) COMMENT '字段', col_type VARCHAR(256) COMMENT '字段类型', col_comment VARCHAR(256) COMMENT '字段注释', col_camel VARCHAR(256) COMMENT '字段驼峰' ); INSERT INTO all_col_table(tbl_name,tbl_name_comment,col,col_comment,col_type) SELECT t1.table_name, t2.TABLE_COMMENT,t1.column_name ,t1.COLUMN_COMMENT,t1.DATA_TYPE FROM information_schema.COLUMNS t1 JOIN information_schema.tables t2 ON t1.TABLE_NAME=t2.TABLE_NAME WHERE t1.table_schema= DATABASE() AND t1.TABLE_NAME LIKE 'tbl_%' ORDER BY t1.TABLE_NAME,t1.ORDINAL_POSITION; UPDATE all_col_table SET col_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(col,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); UPDATE all_col_table SET tbl_name_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(tbl_name,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); DROP table if EXISTS all_fk_table; CREATE TABLE if not exists all_fk_table as SELECT t.TABLE_NAME AS tbl_name, t.TABLE_NAME AS tbl_name_camel, k.column_name AS col, k.column_name AS col_camel, k.REFERENCED_TABLE_NAME AS rf_tbl_name, k.REFERENCED_TABLE_NAME AS rf_tbl_name_camel, k.REFERENCED_COLUMN_NAME AS rf_col , k.REFERENCED_COLUMN_NAME AS rf_col_camel FROM information_schema.TABLE_CONSTRAINTS t JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND t.TABLE_NAME = k.TABLE_NAME AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' AND t.table_schema = DATABASE(); UPDATE all_fk_table SET tbl_name_camel=CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(tbl_name_camel,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); UPDATE all_fk_table SET col_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(col_camel,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); UPDATE all_fk_table SET rf_tbl_name_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(rf_tbl_name_camel,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); UPDATE all_fk_table SET rf_col_camel =CONCAT_WS('',REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(rf_col_camel,@pre_fix,''), '_z', 'Z'), '_y', 'Y'), '_x', 'X'), '_w', 'W'), '_v', 'V'), '_u', 'U'), '_t', 'T'), '_s', 'S'), '_r', 'R'), '_q', 'Q'), '_p', 'P'), '_o', 'O'), '_n', 'N'), '_m', 'M'), '_l', 'L'), '_k', 'K'), '_j', 'J'), '_i', 'I'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') ,''); -- 更新if_fk是否外键标志 UPDATE all_col_table a SET a.if_fk= ifnull((SELECT 'yes' FROM all_fk_table f WHERE f.tbl_name=a.tbl_name AND a.col = f.col),'no'); SELECT t.tbl_name ,CONCAT('{\r\n"_jsonPropertyName":""\r\n',GROUP_CONCAT(CONCAT(', ',col1,":",col2) ORDER BY t.id asc SEPARATOR '\r\n' ),'\r\n}') AS '字段名:展示名' FROM (SELECT TABLE_NAME AS tbl_name FROM information_schema.tables WHERE table_schema=DATABASE()) a join ( select (@id:=@id+1) AS id,tbl_name AS tbl_name, CONCAT("\"",col_camel,"\"") AS col1,JSON_QUOTE(col_camel) AS col2 from all_col_table where if_fk='no' UNION ALL SELECT (@id:=@id+1) as id,tbl_name AS tbl_name, CONCAT("\"",rf_tbl_name_camel,'Info',"\"") AS col1 ,'{}' AS col2 from all_fk_table UNION all select (@id:=@id+1) AS id,rf_tbl_name AS tbl_name ,CONCAT("\"",tbl_name_camel,'InfoList',"\"") AS col1, '{}' AS col2 from all_fk_table ) t on a.tbl_name = t.tbl_name JOIN (SELECT @id:=0) tt GROUP BY t.tbl_name ; DROP TABLE if exists all_col_table; DROP TABLE if exists all_fk_table; */ } public void assistMethodDemo() { //###################################### //###################################### /********** 方法示例 *********************************/ //###################################### //###################################### // public static final JacksonComponent json=MySpringUtils.getBean(JacksonComponent.class); // //通过方法名，找到json字符串，将json转换成map // @SuppressWarnings("unchecked") // public static Map<String,Object> getColMap(String methodName) { // try { // String resJson = dao.getObj(SapoInterfaceDef.getInstance().setName(methodName)).getResJson(); // return json.jsonToObject(resJson, Map.class); // } catch (Exception e) { // throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+" , inteface not config"); // } // } // //通过方法名，获取json字符串集合。 // @SuppressWarnings("unchecked") // public static List<Map<String,Object>> getColMapList(String methodName) { // ArrayList<Map<String,Object>> list = new ArrayList<>(); // try { // List<SapoInterfaceDef> objList = dao.getObjList(SapoInterfaceDef.getInstance().setName(methodName)); // for(SapoInterfaceDef s:objList) { // list.add((Map<String,Object>)json.jsonToObject(s.getResJson(), Map.class)); // } // return list; // } catch (Exception e) { // throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc()+" , inteface not config"); // } // } } public void assistControllReqPojoDemo() { // // ###################################### // //###################################### // // // 实体类：入参校验 // // //###################################### // //###################################### // // @JsonIgnoreProperties({ // "id", // "code", // "createTime", // "lastUpdateTime", // "name", // "detail", // "resource", // "actTypeInfo", // "status", // "beginTime", // "endTime", // "actReleaseInfoList", // "actScopeInfoList", // "userCouponInfoList" //}) // // public class SapoActInfoxx extends SapoActInfo{ // // private static final long serialVersionUID = 1L; // // public SapoActInfoxx(){} // /* // @NotNull(message=" xx can not be null") 验证对象是否不为null, 无法查检长度为0的字符串 // @Range(min = 1, max = 100000, message = " xx not between [xx,xx] range") // // @NotBlank(message=" xx can not be blank") 检查约束 (字符串) 是不是Null还有被Trim的长度是否大于0,只对字符串,且会去掉前后空格. // @Pattern(regexp = "^1([38][0-9]|4[579]|5[0-3,5-9]|6[6]|7[0135678]|9[89])\d{8}$", message = "xx not match regular expression ") // // @NotEmpty(message=" xx can not be null or empty") 检查(集合)约束元素是否为NULL或者是EMPTY. // @Size(min = 1, max = 1000, message = " xx size not between [xx,xx]") // // @NotNull(message=" xx can not be null") 对象级联判断 // @Valid // 注：@JsonIgnoreProperties 和 @NotBlank 混用注意。可能忽略了字段，但是该字段上有非空注解。因为忽略肯定空，但是非空又在校验，故报错。 // 所以忽略的字段不加非空判断。只能手动挨个加。 // */ // // // 主键 // // @JsonProperty("id") // // @JsonAlias(value={"id"}) // @JsonIgnore // protected Integer id; // // // // 活动编码 // // @JsonProperty("code") // // @JsonAlias(value={"code"}) // @JsonIgnore // protected String code; // // // // 创建时间 // //@JsonProperty("createTime") // //@JsonAlias(value={"createTime"}) // //@JsonFormat(pattern = "yyyyMMddHHmmss") // //@JsonDeserialize(using = LocalDateTimeDeserializer.class) // //@JsonSerialize(using = LocalDateTimeSerializer.class) // @JsonIgnore // protected LocalDateTime createTime; //} } public void assistHowToUseIt() { /* 传统方式： 1. 定义dao方法 2. 定义mapper方法 3. 准备相关参数，调用相关dao方法 缺点： 1. 查询条件(=、>、<、in)与条件的个数不确定，要定义的dao太多 2. 数据库字段有升级要修改的model和mapper很麻烦且容易出错。 3. 形式内容太多，编写效率不高 新的方式： 1. 以model对象为基本操作单位。 2. 不同字段的不同查询条件对应了对象相关属性的不同方法。 3. 通用的dao层方法，解析model对象被设置了那些条件，进行sql拼接。 */ // public class ApplicationTests extends BaseTest { // // // @Autowired // private JacksonComponent jacksonComponent; // // @Autowired // CommonDao dao; // // @Test // public void test() throws Exception { // //// /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; //// /*!40101 SET NAMES utf8 */; //// /*!50503 SET NAMES utf8mb4 */; //// /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; //// /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; //// /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; // // //// CREATE TABLE IF NOT EXISTS `tbl_sapo_act` ( //// `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', //// `code` varchar(100) NOT NULL COMMENT '活动编码', //// `create_time` datetime(3) NOT NULL COMMENT '创建时间', //// `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', //// `name` varchar(255) NOT NULL COMMENT '活动名', //// `detail` varchar(1024) DEFAULT NULL COMMENT '活动描述', //// `resource` varchar(1024) DEFAULT NULL COMMENT '活动资源', //// `act_type_code` varchar(100) NOT NULL COMMENT '活动类型编码，tbl_sapo_act_type表code', //// `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑', //// `begin_time` datetime(3) NOT NULL COMMENT '开始时间', //// `end_time` datetime(3) NOT NULL COMMENT '结束时间', //// PRIMARY KEY (`id`), //// UNIQUE KEY `uni_idx_act_code` (`code`), //// KEY `idx_act_act_type_code` (`act_type_code`), //// KEY `idx_act_status_begin_time_end_time` (`status`,`begin_time`,`end_time`), //// CONSTRAINT `fk_act_act_type_code` FOREIGN KEY (`act_type_code`) REFERENCES `tbl_sapo_act_type` (`code`) //// ) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COMMENT='活动'; // // //// /*!40000 ALTER TABLE `tbl_sapo_act` DISABLE KEYS */; //// INSERT INTO `tbl_sapo_act` (`id`, `code`, `create_time`, `last_update_time`, `name`, `detail`, `resource`, `act_type_code`, `status`, `begin_time`, `end_time`) VALUES //// (1, 'buy-wash-release-dry', '2022-06-24 14:15:04.000', NULL, '购买洗衣送烘干券', '购买洗衣送烘干券', NULL, 'inner-rule-release-coupon-act', 1, '2022-06-24 14:15:24.000', '2023-09-24 14:15:25.000'), //// (2, 'buy-dry-release-wash', '2022-06-24 14:16:43.000', NULL, '购买烘干送洗衣券', '购买烘干送洗衣券', NULL, 'inner-rule-release-coupon-act', 1, '2022-06-24 14:17:01.000', '2023-09-24 14:17:02.000'), //// (5, '1111', '2022-07-28 15:53:38.000', '2022-08-02 15:37:36.751', '1112', '112', '112', 'banner-release-coupon-act', 2, '2022-07-29 07:53:30.000', '2022-08-01 07:53:34.000'), //// (6, '222', '2022-08-02 14:30:38.054', '2022-08-02 14:30:38.054', '222', '222', '222', 'inner-rule-release-coupon-act', 1, '2022-08-02 14:30:23.000', '2022-08-18 14:30:34.000'), //// (7, 'console-release-coupon', '2022-08-18 08:49:09.000', NULL, '控制台派发券', NULL, NULL, 'console-release-coupon-act', 1, '2022-08-18 08:49:48.000', '2022-08-18 08:49:50.000'), //// (112, '33', '2022-11-08 16:24:09.000', NULL, '33', NULL, NULL, 'banner-release-coupon-act', 2, '2022-11-08 16:24:17.000', '2022-11-08 16:24:23.000'); //// /*!40000 ALTER TABLE `tbl_sapo_act` ENABLE KEYS */; // // //// CREATE TABLE IF NOT EXISTS `tbl_sapo_act_release` ( //// `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', //// `create_time` datetime(3) NOT NULL COMMENT '创建时间', //// `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', //// `release_code` varchar(100) NOT NULL COMMENT '券派发code', //// `act_code` varchar(100) NOT NULL COMMENT '活动code,tbl_sapo_act表code', //// `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑', //// PRIMARY KEY (`id`), //// KEY `idx_act_release_act_code` (`act_code`), //// KEY `idx_act_release_release_code` (`release_code`), //// CONSTRAINT `fk_act_release_act_code` FOREIGN KEY (`act_code`) REFERENCES `tbl_sapo_act` (`code`), //// CONSTRAINT `fk_act_release_release_code` FOREIGN KEY (`release_code`) REFERENCES `tbl_sapo_coupon_release` (`code`) //// ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='活动的券发放'; // // //// /*!40000 ALTER TABLE `tbl_sapo_act_release` DISABLE KEYS */; //// INSERT INTO `tbl_sapo_act_release` (`id`, `create_time`, `last_update_time`, `release_code`, `act_code`, `status`) VALUES //// (1, '2022-06-24 14:18:10.000', '2022-06-24 14:18:11.000', 'release-dry-coupon', 'buy-wash-release-dry', 1), //// (2, '2022-06-24 14:18:24.000', '2022-06-24 14:18:47.000', 'release-wash-coupon', 'buy-dry-release-wash', 1), //// (4, '2022-08-02 16:38:55.509', '2022-08-02 16:38:55.509', '111111', '222', 1); //// /*!40000 ALTER TABLE `tbl_sapo_act_release` ENABLE KEYS */; // // //// CREATE TABLE IF NOT EXISTS `tbl_sapo_act_type` ( //// `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', //// `code` varchar(100) NOT NULL COMMENT '编码', //// `create_time` datetime(3) NOT NULL COMMENT '创建时间', //// `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', //// `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效，2-编辑', //// `name` varchar(255) NOT NULL COMMENT '名称', //// `detail` varchar(255) DEFAULT NULL COMMENT '详情', //// PRIMARY KEY (`id`), //// UNIQUE KEY `uni_idx_act_type_code` (`code`) //// ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='活动类型'; // // //// /*!40000 ALTER TABLE `tbl_sapo_act_type` DISABLE KEYS */; //// INSERT INTO `tbl_sapo_act_type` (`id`, `code`, `create_time`, `last_update_time`, `status`, `name`, `detail`) VALUES //// (3, 'inner-rule-release-coupon-act', '2022-06-24 14:28:20.000', NULL, 1, '内部规则触发送优惠券活动', NULL), //// (4, 'banner-show-act', '2022-06-24 14:29:39.000', NULL, 1, 'banner静态展示性活动', NULL), //// (5, 'act-center-show-act', '2022-06-24 14:30:23.000', NULL, 1, '活动中心静态展示性活动', NULL), //// (6, 'banner-release-coupon-act', '2022-06-24 14:31:14.000', NULL, 1, 'banner投放券活动', NULL), //// (7, 'act-center-release-coupon-act', '2022-06-24 14:31:43.000', NULL, 1, '活动中心投放券活动', NULL), //// (8, 'console-release-coupon-act', '2022-08-18 08:47:51.000', NULL, 1, '控制台派发优惠券活动', NULL); //// /*!40000 ALTER TABLE `tbl_sapo_act_type` ENABLE KEYS */; // // //// /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; //// /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; //// /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; //// /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */; // // // // // ****************************************************************** // // model提供的查询条件拼接操作示例 // // ****************************************************************** // // // select .... from tbl_sapo_act where id = 3 // SapoAct sapoAct = SapoAct.getInstance().setId(3); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id is null // sapoAct = SapoAct.getInstance().setIdIsNull(); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id < 3 // sapoAct = SapoAct.getInstance().setIdLessThan(3); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id <= 3 // sapoAct = SapoAct.getInstance().setIdLessThanEqual(3); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id > 3 // sapoAct = SapoAct.getInstance().setIdGreatThan(3); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id >= 3 // sapoAct = SapoAct.getInstance().setIdGreatThanEqual(3); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where id in (3,5) // sapoAct = SapoAct.getInstance().setIdInList(Arrays.asList(1,2,3)); // dao.getObjListWithEmpty(sapoAct); // // // select ... from tbl_sapo_act where code like %xx% // sapoAct = SapoAct.getInstance().setActTypeCodeByFuzzy("xx"); // dao.getObjListWithEmpty(sapoAct); // // // update tbl_sapo_act set status = status + 2 , name='xx' where id = 3 and code ='xx' // SapoAct sapoActForQuery = SapoAct.getInstance().setId(3).setCode("xx"); // SapoAct sapoActForUpdate = SapoAct.getInstance().setStatusPlusSpecNum(2).setName("xx"); // dao.updateObjWithResult(sapoActForUpdate, sapoActForQuery); // // // // update tbl_sapo_act set status = status - 2 , name='yy' where id = 3 and code ='xx' // sapoActForUpdate = SapoAct.getInstance().setStatusMinusSpecNum(2).setName("yy"); // dao.updateObjWithResult(sapoActForUpdate, sapoActForQuery); // // // // // // ****************************************************************** // // dao 层测试 // // ****************************************************************** // // // 获取单个对象，通过id获取对象，只用id作为查询条件,其他不管字段不管 // // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `id` = ? // SapoAct getById = dao.getObjById(SapoAct.getInstance().setStatus(2).setId(5).setCode("1111")); // System.err.println("getById : "+getById); // // // // 获取单个对象，通过条件获取 // // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` = ? and `id` = ? and `status` = ? // SapoAct getByObj = dao.getObj(SapoAct.getInstance().setStatus(2).setId(5).setCode("1111")); // System.err.println("getByObj : "+getByObj); // // // // 获取集合 // // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') // SapoAct setCodeByFuzzy = SapoAct.getInstance().setCodeByFuzzy("buy"); // List<SapoAct> objList = dao.getObjList(setCodeByFuzzy.setStatusInList(Arrays.asList(1,2,3))); // System.err.println("objList : "+ objList); // // // 分页查询 // // select count(1) from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') // // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') order by create_time desc limit ? ,? // ObjPage<SapoAct> objListByPage = dao.getObjListWithEmptyByPage(setCodeByFuzzy.setStatusInList(Arrays.asList(1,2,3)), 1, 2); // System.err.println("objListByPage : "+ objListByPage.getPageList()); // // // // 插入语句 // // insert into tbl_sapo_act SET `code` = ? , `create_time` = ? , `resource` = ? , `name` = ? , `act_type_code` = ? , `begin_time` = ? , `end_time` = ? // SapoAct insertObj = SapoAct.getInstance().setBeginTime(LocalDateTime.now()).setEndTime(LocalDateTime.now()).setCreateTime(LocalDateTime.now()).setActTypeCode("banner-release-coupon-act").setCode(UUID.randomUUID().toString()).setName("name").setResource("test"); // dao.insertObjNoIncr(insertObj); // System.err.println("insertObjNoIncr : "+insertObj.getId()); // null 不自动回填 // // insertObj = SapoAct.getInstance().setBeginTime(LocalDateTime.now()).setEndTime(LocalDateTime.now()).setCreateTime(LocalDateTime.now()).setActTypeCode("banner-release-coupon-act").setCode(UUID.randomUUID().toString()).setName("name").setResource("test"); // dao.insertObj(insertObj); // System.err.println("insertObj : "+insertObj.getId()); // 37 自动回填id主键的值 // // // // 更新语句 // SapoAct objForUpdate = dao.getObj(SapoAct.getInstance().setId(1).setStatus(1).setCode("buy-wash-release-dry")); // // update tbl_sapo_act SET `code` = ? , `create_time` = ? , `name` = ? , `act_type_code` = ? , `detail` = ? , `begin_time` = ? , `end_time` = ? , `status` = ? WHERE `id` = ? // dao.updateObjById(objForUpdate.setName("222")); // // // SapoAct objForQuery = SapoAct.getInstance().setCodeByFuzzy("buy"); // SapoAct objForUpdate1 = SapoAct.getInstance().setName("cc"); // // update tbl_sapo_act SET `name` = ? WHERE `code` like concat('%',?,'%') // dao.updateObj(objForUpdate1, objForQuery); // // // // 删除语句 // // delete from tbl_sapo_act WHERE `code` = ? // dao.deleteObj(SapoAct.getInstance().setCode("33")); // // // 获取符合添加条件对象个数 // // select count(1) from tbl_sapo_act WHERE `code` = ? // int objCount = dao.getObjCount(SapoAct.getInstance().setCode("buy")); // // // // ****************************************************************** // // domain 测试用例 // // ****************************************************************** // // String resultJson="{ " + // " \"_jsonPropertyName\": \"\", " + // " \"id\": \"id\", " + // " \"code\": \"code\", " + // " \"createTime\": \"createTime\", " + // " \"lastUpdateTime\": \"lastUpdateTime\", " + // " \"name\": \"name\", " + // " \"detail\": \"detail\", " + // " \"resource\": \"resource\", " + // " \"status\": \"status\", " + // " \"beginTime\": \"beginTime\", " + // " \"endTime\": \"endTime\", " + // " \"sapoActTypeInfo\": { " + // " \"_jsonPropertyName\": \"\", " + // " \"id\": \"id\", " + // " \"code\": \"code\", " + // " \"createTime\": \"createTime\", " + // " \"lastUpdateTime\": \"lastUpdateTime\", " + // " \"status\": \"status\", " + // " \"name\": \"name\", " + // " \"detail\": \"detail\" " + // " }, " + // " \"sapoActReleaseInfoList\": { " + // " \"_jsonPropertyName\": \"\", " + // " \"id\": \"id\", " + // " \"createTime\": \"createTime\", " + // " \"lastUpdateTime\": \"lastUpdateTime\", " + // " \"status\": \"status\" " + // " } " + // "}"; // // // @SuppressWarnings("unchecked") // Map<String,Object> colMap = jacksonComponent.jsonToObject(resultJson, Map.class); // // SapoActInfo sapoActInfo = new SapoActInfo(SapoAct.getInstance().setId(1), colMap); // System.err.println("sapoActInfo : "+jacksonComponent.objectToJsonString(sapoActInfo)); // // sapoActInfo : {"code":"buy-wash-release-dry","createTime":"20220624141504","resource":null,"name":"购买洗衣送烘干券","sapoActReleaseInfoList":[{"createTime":"20220624141810","id":1,"lastUpdateTime":"20220624141811","status":1}],"id":1,"detail":"购买洗衣送烘干券","beginTime":"20220624141524","endTime":"20230924141525","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1} // // List<SapoActInfo> sapoActInfoList = SapoActInfo.getObjInfoList(SapoAct.getInstance().setStatus(1), colMap); // System.err.println("sapoActInfoList : "+jacksonComponent.objectToJsonString(sapoActInfoList)); // // sapoActInfoList : [{"code":"console-release-coupon","createTime":"20220818084909","resource":null,"name":"控制台派发券","sapoActReleaseInfoList":[],"id":7,"detail":null,"beginTime":"20220818084948","endTime":"20220818084950","sapoActTypeInfo":{"code":"console-release-coupon-act","createTime":"20220818084751","name":"控制台派发优惠券活动","id":8,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1},{"code":"222","createTime":"20220802143038","resource":"222","name":"222","sapoActReleaseInfoList":[{"createTime":"20220802163855","id":4,"lastUpdateTime":"20220802163855","status":1}],"id":6,"detail":"222","beginTime":"20220802143023","endTime":"20220818143034","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":"20220802143038","status":1},{"code":"buy-dry-release-wash","createTime":"20220624141643","resource":null,"name":"购买烘干送洗衣券","sapoActReleaseInfoList":[{"createTime":"20220624141824","id":2,"lastUpdateTime":"20220624141847","status":1}],"id":2,"detail":"购买烘干送洗衣券","beginTime":"20220624141701","endTime":"20230924141702","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1},{"code":"buy-wash-release-dry","createTime":"20220624141504","resource":null,"name":"购买洗衣送烘干券","sapoActReleaseInfoList":[{"createTime":"20220624141810","id":1,"lastUpdateTime":"20220624141811","status":1}],"id":1,"detail":"购买洗衣送烘干券","beginTime":"20220624141524","endTime":"20230924141525","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1}] // // // } } } class TableColumn { private String tableName; private String columnName; private String dataType; private String columnComment; public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } public String getColumnName() { return columnName; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getDataType() { return dataType; } public void setDataType(String dataType) { this.dataType = dataType; } public String getColumnComment() { return columnComment; } public void setColumnComment(String columnComment) { this.columnComment = columnComment; } }; class RefTableColumn { private String tableName; private String columnName; private String referencedTableName; private String referencedColumnName; public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } public String getColumnName() { return columnName; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getReferencedTableName() { return referencedTableName; } public void setReferencedTableName(String referencedTableName) { this.referencedTableName = referencedTableName; } public String getReferencedColumnName() { return referencedColumnName; } public void setReferencedColumnName(String referencedColumnName) { this.referencedColumnName = referencedColumnName; } }; class ConstantDefine { private String bizDesc; private String tblName; private String col; private String colValue; private String colValueDesc; private String remark; public String getBizDesc() { return bizDesc; } public void setBizDesc(String bizDesc) { this.bizDesc = bizDesc; } public String getTblName() { return tblName; } public void setTblName(String tblName) { this.tblName = tblName; } public String getCol() { return col; } public void setCol(String col) { this.col = col; } public String getColValue() { return colValue; } public void setColValue(String colValue) { this.colValue = colValue; } public String getColValueDesc() { return colValueDesc; } public void setColValueDesc(String colValueDesc) { this.colValueDesc = colValueDesc; } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark; } } // 将jsonSchema转化成map class JsonSchema2Map{ @JsonTypeInfo(use = JsonTypeInfo.Id.NAME, property = "type", visible = true) @JsonSubTypes({ // 值-->子类映射 @JsonSubTypes.Type(value = ObjNode.class, name = "object"), @JsonSubTypes.Type(value = ColNode.class, name = "string"), @JsonSubTypes.Type(value = ColNode.class, name = "integer"), @JsonSubTypes.Type(value = ColNode.class, name = "boolean"), @JsonSubTypes.Type(value = ColNode.class, name = "number"), @JsonSubTypes.Type(value = ArrNode.class, name = "array") }) static class Node { public String type; public String colName; private Object mock; public Object getMock() { return mock; } public void setMock(Object mock) { this.mock = mock; } public String getColName() { return colName; } public void setColName(String colName) { this.colName = colName; } public String getType() { return type; } public void setType(String type) { this.type = type; } @Override public String toString() { return "Node [type=" + type + ", colName=" + colName + "]"; } } static class ObjNode extends Node { private List<String> required; private String description; private Map<String, Node> properties; public List<String> getRequired() { return required; } public void setRequired(List<String> required) { this.required = required; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Map<String, Node> getProperties() { return properties; } public void setProperties(Map<String, Node> properties) { this.properties = properties; } @Override public String toString() { return "ObjNode [required=" + required + ", description=" + description + ", properties=" + properties + "]"; } } static class ColNode extends Node{ private String description; private String example; private String colName; public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public String getExample() { return example; } public void setExample(String example) { this.example = example; } public String getColName() { return colName; } public void setColName(String colName) { this.colName = colName; } @Override public String toString() { return "colNode [description=" + description + ", type=" + type + ", example=" + example + ", colName=" + colName + "]"; } } static class ArrNode extends Node { private List<String> required; private String description; private Node items; public List<String> getRequired() { return required; } public void setRequired(List<String> required) { this.required = required; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Node getItems() { return items; } public void setItems(Node items) { this.items = items; } @Override public String toString() { return "ArrNode [description=" + description + ", items=" + items + "]"; } } private static void jsonObj2Map(HashMap<String, Object> resultMap, String nodeName, Node node) { HashMap<String, Object> hashMap = new HashMap<>(); if (node instanceof ObjNode) { Map<String, Node> properties = ((ObjNode) node).getProperties(); Set<String> keySet = properties.keySet(); for (String s : keySet) { Node node2 = properties.get(s); jsonObj2Map(hashMap, s, node2); } hashMap.put("_jsonPropertyName", nodeName); if (node.getColName() == null || node.getColName().isEmpty()) { resultMap.put(nodeName, hashMap); } else { resultMap.put(node.getColName(), hashMap); } } else if (node instanceof ArrNode) { Node items = ((ArrNode) node).getItems(); String str = items.getColName(); if(str==null) { str=nodeName; } jsonObj2Map(hashMap, str, items); hashMap.put("_jsonPropertyName", nodeName); if (node.getColName() == null || node.getColName().isEmpty()) { resultMap.put(nodeName, hashMap); } else { resultMap.put(node.getColName(), hashMap); } } else { if (((ColNode) node).getColName() == null || ((ColNode) node).getColName().isEmpty()) { resultMap.put(nodeName, nodeName); } else { resultMap.put(((ColNode) node).getColName(), nodeName); } } } public static String getJsonColMap(String jsonSchema) throws IOException { ObjectMapper objectMapper = new ObjectMapper(); ObjectReader reader = objectMapper.readerFor(Node.class); Node node = reader.readValue(jsonSchema); //System.err.println(node.toString()); HashMap<String, Object> resultMap = new HashMap<>(); jsonObj2Map(resultMap, "root", node); return objectMapper.writeValueAsString(resultMap); } // 将jsonSchema 转化为key:value的map-json字符串 public static void main(String[] args) throws IOException { String jsonSchema ="{\"required\":[\"resCode\",\"resDesc\"],\"type\":\"object\",\"properties\":{\"resCode\":{\"description\":\"应答码\",\"type\":\"string\",\"example\":\"0000\"},\"resDesc\":{\"description\":\"应答信息\",\"type\":\"string\",\"example\":\"success\"},\"resData\":{\"description\":\"应答数据\",\"type\":\"object\",\"properties\":{\"storeDeviceInfoList\":{\"description\":\"门店设备对象实体集合\",\"type\":\"array\",\"items\":{\"required\":[\"id\",\"code\",\"createTime\",\"deviceId\",\"deviceStatus\",\"runStatus\",\"ownType\",\"deviceModelInfo\",\"storeInfo\"],\"description\":\"门店设备\",\"type\":\"object\",\"properties\":{\"code\":{\"description\":\"设备编号\",\"type\":\"string\",\"example\":null},\"deviceId\":{\"description\":\"设备唯一标识(非外键)和LG交互的设备凭证\",\"type\":\"string\",\"example\":null},\"nickName\":{\"description\":\"设备别名\",\"type\":\"string\",\"example\":null},\"detail\":{\"description\":\"设备描述\",\"type\":\"string\",\"example\":null},\"location\":{\"description\":\"设备在门店内位置描述\",\"type\":\"string\",\"example\":null},\"runStatus\":{\"description\":\"运行状态:0-空闲,1-已锁定,2-工作中,3-故障\",\"type\":\"integer\",\"example\":null},\"deviceModelInfo\":{\"required\":[\"id\",\"code\",\"createTime\",\"status\",\"name\",\"brandInfo\",\"deviceTypeInfo\"],\"description\":\"设备\",\"type\":\"object\",\"properties\":{\"code\":{\"description\":\"设备型号代码\",\"type\":\"string\",\"example\":null},\"name\":{\"description\":\"设备型号名\",\"type\":\"string\",\"example\":null},\"detail\":{\"description\":\"设备型号描述\",\"type\":\"string\",\"example\":null},\"brandInfo\":{\"required\":[\"id\",\"code\",\"createTime\",\"status\",\"name\"],\"description\":\"设备品牌(LG)\",\"type\":\"object\",\"properties\":{\"code\":{\"description\":\"业务编码code\",\"type\":\"string\",\"example\":null},\"name\":{\"description\":\"名称\",\"type\":\"string\",\"example\":null},\"detail\":{\"description\":\"详情\",\"type\":\"string\",\"example\":null}}},\"deviceTypeInfo\":{\"required\":[\"id\",\"code\",\"createTime\",\"status\",\"name\"],\"description\":\"设备类型\",\"type\":\"object\",\"properties\":{\"code\":{\"description\":\"业务编码code\",\"type\":\"string\",\"example\":null},\"name\":{\"description\":\"设备类型名(洗衣机,烘干机)\",\"type\":\"string\",\"example\":null},\"detail\":{\"description\":\"详情\",\"type\":\"string\",\"example\":null}}}}}}}}}}}}"; System.err.println(getJsonColMap(jsonSchema)); } }
工具代码使用示例如下：
1. 通用dao代码
2. 使用通过配置返回json决定，要查询的数据。
@Test public void test() throws Exception { // /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; // /*!40101 SET NAMES utf8 */; // /*!50503 SET NAMES utf8mb4 */; // /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; // /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; // /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; // // CREATE TABLE IF NOT EXISTS `tbl_sapo_act` ( // `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', // `code` varchar(100) NOT NULL COMMENT '活动编码', // `create_time` datetime(3) NOT NULL COMMENT '创建时间', // `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', // `name` varchar(255) NOT NULL COMMENT '活动名', // `detail` varchar(1024) DEFAULT NULL COMMENT '活动描述', // `resource` varchar(1024) DEFAULT NULL COMMENT '活动资源', // `act_type_code` varchar(100) NOT NULL COMMENT '活动类型编码，tbl_sapo_act_type表code', // `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑', // `begin_time` datetime(3) NOT NULL COMMENT '开始时间', // `end_time` datetime(3) NOT NULL COMMENT '结束时间', // PRIMARY KEY (`id`), // UNIQUE KEY `uni_idx_act_code` (`code`), // KEY `idx_act_act_type_code` (`act_type_code`), // KEY `idx_act_status_begin_time_end_time` (`status`,`begin_time`,`end_time`), // CONSTRAINT `fk_act_act_type_code` FOREIGN KEY (`act_type_code`) REFERENCES `tbl_sapo_act_type` (`code`) // ) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COMMENT='活动'; // // /*!40000 ALTER TABLE `tbl_sapo_act` DISABLE KEYS */; // INSERT INTO `tbl_sapo_act` (`id`, `code`, `create_time`, `last_update_time`, `name`, `detail`, `resource`, `act_type_code`, `status`, `begin_time`, `end_time`) VALUES // (1, 'buy-wash-release-dry', '2022-06-24 14:15:04.000', NULL, '购买洗衣送烘干券', '购买洗衣送烘干券', NULL, 'inner-rule-release-coupon-act', 1, '2022-06-24 14:15:24.000', '2023-09-24 14:15:25.000'), // (2, 'buy-dry-release-wash', '2022-06-24 14:16:43.000', NULL, '购买烘干送洗衣券', '购买烘干送洗衣券', NULL, 'inner-rule-release-coupon-act', 1, '2022-06-24 14:17:01.000', '2023-09-24 14:17:02.000'), // (5, '1111', '2022-07-28 15:53:38.000', '2022-08-02 15:37:36.751', '1112', '112', '112', 'banner-release-coupon-act', 2, '2022-07-29 07:53:30.000', '2022-08-01 07:53:34.000'), // (6, '222', '2022-08-02 14:30:38.054', '2022-08-02 14:30:38.054', '222', '222', '222', 'inner-rule-release-coupon-act', 1, '2022-08-02 14:30:23.000', '2022-08-18 14:30:34.000'), // (7, 'console-release-coupon', '2022-08-18 08:49:09.000', NULL, '控制台派发券', NULL, NULL, 'console-release-coupon-act', 1, '2022-08-18 08:49:48.000', '2022-08-18 08:49:50.000'), // (112, '33', '2022-11-08 16:24:09.000', NULL, '33', NULL, NULL, 'banner-release-coupon-act', 2, '2022-11-08 16:24:17.000', '2022-11-08 16:24:23.000'); // /*!40000 ALTER TABLE `tbl_sapo_act` ENABLE KEYS */; // // CREATE TABLE IF NOT EXISTS `tbl_sapo_act_release` ( // `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', // `create_time` datetime(3) NOT NULL COMMENT '创建时间', // `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', // `release_code` varchar(100) NOT NULL COMMENT '券派发code', // `act_code` varchar(100) NOT NULL COMMENT '活动code,tbl_sapo_act表code', // `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效,2-编辑', // PRIMARY KEY (`id`), // KEY `idx_act_release_act_code` (`act_code`), // KEY `idx_act_release_release_code` (`release_code`), // CONSTRAINT `fk_act_release_act_code` FOREIGN KEY (`act_code`) REFERENCES `tbl_sapo_act` (`code`), // CONSTRAINT `fk_act_release_release_code` FOREIGN KEY (`release_code`) REFERENCES `tbl_sapo_coupon_release` (`code`) // ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='活动的券发放'; // // /*!40000 ALTER TABLE `tbl_sapo_act_release` DISABLE KEYS */; // INSERT INTO `tbl_sapo_act_release` (`id`, `create_time`, `last_update_time`, `release_code`, `act_code`, `status`) VALUES // (1, '2022-06-24 14:18:10.000', '2022-06-24 14:18:11.000', 'release-dry-coupon', 'buy-wash-release-dry', 1), // (2, '2022-06-24 14:18:24.000', '2022-06-24 14:18:47.000', 'release-wash-coupon', 'buy-dry-release-wash', 1), // (4, '2022-08-02 16:38:55.509', '2022-08-02 16:38:55.509', '111111', '222', 1); // /*!40000 ALTER TABLE `tbl_sapo_act_release` ENABLE KEYS */; // // CREATE TABLE IF NOT EXISTS `tbl_sapo_act_type` ( // `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', // `code` varchar(100) NOT NULL COMMENT '编码', // `create_time` datetime(3) NOT NULL COMMENT '创建时间', // `last_update_time` datetime(3) DEFAULT NULL COMMENT '最后更新时间', // `status` int(10) unsigned NOT NULL DEFAULT 2 COMMENT '状态:0-无效,1-有效，2-编辑', // `name` varchar(255) NOT NULL COMMENT '名称', // `detail` varchar(255) DEFAULT NULL COMMENT '详情', // PRIMARY KEY (`id`), // UNIQUE KEY `uni_idx_act_type_code` (`code`) // ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='活动类型'; // // /*!40000 ALTER TABLE `tbl_sapo_act_type` DISABLE KEYS */; // INSERT INTO `tbl_sapo_act_type` (`id`, `code`, `create_time`, `last_update_time`, `status`, `name`, `detail`) VALUES // (3, 'inner-rule-release-coupon-act', '2022-06-24 14:28:20.000', NULL, 1, '内部规则触发送优惠券活动', NULL), // (4, 'banner-show-act', '2022-06-24 14:29:39.000', NULL, 1, 'banner静态展示性活动', NULL), // (5, 'act-center-show-act', '2022-06-24 14:30:23.000', NULL, 1, '活动中心静态展示性活动', NULL), // (6, 'banner-release-coupon-act', '2022-06-24 14:31:14.000', NULL, 1, 'banner投放券活动', NULL), // (7, 'act-center-release-coupon-act', '2022-06-24 14:31:43.000', NULL, 1, '活动中心投放券活动', NULL), // (8, 'console-release-coupon-act', '2022-08-18 08:47:51.000', NULL, 1, '控制台派发优惠券活动', NULL); // /*!40000 ALTER TABLE `tbl_sapo_act_type` ENABLE KEYS */; // // /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; // /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; // /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; // /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */; /* 传统方式： 1. 定义dao方法 2. 定义mapper方法 3. 准备相关参数，调用相关dao方法 缺点： 1. 查询条件(=、>、<、in)与条件的个数不确定，要定义的dao太多 2. 数据库字段有升级要修改的model和mapper很麻烦且容易出错。 3. 形式内容太多，编写效率不高 新的方式： 1. 以model对象为基本操作单位。 2. 不同字段的不同查询条件对应了对象相关属性的不同方法。 3. 通用的dao层方法，解析model对象被设置了那些条件，进行sql拼接。 */ // ****************************************************************** // model提供的查询条件拼接操作示例 // ****************************************************************** // select ... from tbl_sapo_act where id = 3 SapoAct sapoAct = SapoAct.getInstance().setId(3); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id is null sapoAct = SapoAct.getInstance().setIdIsNull(); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id < 3 sapoAct = SapoAct.getInstance().setIdLessThan(3); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id <= 3 sapoAct = SapoAct.getInstance().setIdLessThanEqual(3); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id > 3 sapoAct = SapoAct.getInstance().setIdGreatThan(3); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id >= 3 sapoAct = SapoAct.getInstance().setIdGreatThanEqual(3); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where id in (3,5) sapoAct = SapoAct.getInstance().setIdInList(Arrays.asList(1,2,3)); dao.getObjListWithEmpty(sapoAct); // select ... from tbl_sapo_act where code like %xx% sapoAct = SapoAct.getInstance().setActTypeCodeByFuzzy("xx"); dao.getObjListWithEmpty(sapoAct); // update tbl_sapo_act set status = status + 2 , name='xx' where id = 3 and code ='xx' SapoAct sapoActForQuery = SapoAct.getInstance().setId(3).setCode("xx"); SapoAct sapoActForUpdate = SapoAct.getInstance().setStatusPlusSpecNum(2).setName("xx"); dao.updateObjWithResult(sapoActForUpdate, sapoActForQuery); // update tbl_sapo_act set status = status - 2 , name='yy' where id = 3 and code ='xx' sapoActForUpdate = SapoAct.getInstance().setStatusMinusSpecNum(2).setName("yy"); dao.updateObjWithResult(sapoActForUpdate, sapoActForQuery); // ****************************************************************** // dao 层测试 // ****************************************************************** // 获取单个对象，通过id获取对象，只用id作为查询条件,其他不管字段不管 // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `id` = ? SapoAct getById = dao.getObjById(SapoAct.getInstance().setStatus(2).setId(5).setCode("1111")); System.err.println("getById : "+getById); // 获取单个对象，通过条件获取 // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` = ? and `id` = ? and `status` = ? SapoAct getByObj = dao.getObj(SapoAct.getInstance().setStatus(2).setId(5).setCode("1111")); System.err.println("getByObj : "+getByObj); // 获取集合 // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') SapoAct setCodeByFuzzy = SapoAct.getInstance().setCodeByFuzzy("buy"); List<SapoAct> objList = dao.getObjList(setCodeByFuzzy.setStatusInList(Arrays.asList(1,2,3))); System.err.println("objList : "+ objList); // 分页查询 // select count(1) from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') // select `id` , `code` , `create_time` , `last_update_time` , `name` , `detail` , `resource` , `act_type_code` , `status` , `begin_time` , `end_time` from tbl_sapo_act WHERE `code` like concat('%',?,'%') and `status` in ('1','2','3') order by create_time desc limit ? ,? ObjPage<SapoAct> objListByPage = dao.getObjListWithEmptyByPage(setCodeByFuzzy.setStatusInList(Arrays.asList(1,2,3)), 1, 2); System.err.println("objListByPage : "+ objListByPage.getPageList()); // 插入语句 // insert into tbl_sapo_act SET `code` = ? , `create_time` = ? , `resource` = ? , `name` = ? , `act_type_code` = ? , `begin_time` = ? , `end_time` = ? SapoAct insertObj = SapoAct.getInstance().setBeginTime(LocalDateTime.now()).setEndTime(LocalDateTime.now()).setCreateTime(LocalDateTime.now()).setActTypeCode("banner-release-coupon-act").setCode(UUID.randomUUID().toString()).setName("name").setResource("test"); dao.insertObjNoIncr(insertObj); System.err.println("insertObjNoIncr : "+insertObj.getId()); // null 不自动回填 insertObj = SapoAct.getInstance().setBeginTime(LocalDateTime.now()).setEndTime(LocalDateTime.now()).setCreateTime(LocalDateTime.now()).setActTypeCode("banner-release-coupon-act").setCode(UUID.randomUUID().toString()).setName("name").setResource("test"); dao.insertObj(insertObj); System.err.println("insertObj : "+insertObj.getId()); // 37 自动回填id主键的值 // 更新语句 SapoAct objForUpdate = dao.getObj(SapoAct.getInstance().setId(1).setStatus(1).setCode("buy-wash-release-dry")); // update tbl_sapo_act SET `code` = ? , `create_time` = ? , `name` = ? , `act_type_code` = ? , `detail` = ? , `begin_time` = ? , `end_time` = ? , `status` = ? WHERE `id` = ? dao.updateObjById(objForUpdate.setName("222")); SapoAct objForQuery = SapoAct.getInstance().setCodeByFuzzy("buy"); SapoAct objForUpdate1 = SapoAct.getInstance().setName("cc"); // update tbl_sapo_act SET `name` = ? WHERE `code` like concat('%',?,'%') dao.updateObj(objForUpdate1, objForQuery); // 删除语句 // delete from tbl_sapo_act WHERE `code` = ? dao.deleteObj(SapoAct.getInstance().setCode("33")); // 获取符合添加条件对象个数 // select count(1) from tbl_sapo_act WHERE `code` = ? int objCount = dao.getObjCount(SapoAct.getInstance().setCode("buy")); // ****************************************************************** // domain 测试用例 // ****************************************************************** String resultJson="{ " + " \"_jsonPropertyName\": \"\", " + " \"id\": \"id\", " + " \"code\": \"code\", " + " \"createTime\": \"createTime\", " + " \"lastUpdateTime\": \"lastUpdateTime\", " + " \"name\": \"name\", " + " \"detail\": \"detail\", " + " \"resource\": \"resource\", " + " \"status\": \"status\", " + " \"beginTime\": \"beginTime\", " + " \"endTime\": \"endTime\", " + " \"sapoActTypeInfo\": { " + " \"_jsonPropertyName\": \"\", " + " \"id\": \"id\", " + " \"code\": \"code\", " + " \"createTime\": \"createTime\", " + " \"lastUpdateTime\": \"lastUpdateTime\", " + " \"status\": \"status\", " + " \"name\": \"name\", " + " \"detail\": \"detail\" " + " }, " + " \"sapoActReleaseInfoList\": { " + " \"_jsonPropertyName\": \"\", " + " \"id\": \"id\", " + " \"createTime\": \"createTime\", " + " \"lastUpdateTime\": \"lastUpdateTime\", " + " \"status\": \"status\" " + " } " + "}"; @SuppressWarnings("unchecked") Map<String,Object> colMap = jacksonComponent.jsonToObject(resultJson, Map.class); SapoActInfo sapoActInfo = new SapoActInfo(SapoAct.getInstance().setId(1), colMap); System.err.println("sapoActInfo : "+jacksonComponent.objectToJsonString(sapoActInfo)); // sapoActInfo : {"code":"buy-wash-release-dry","createTime":"20220624141504","resource":null,"name":"购买洗衣送烘干券","sapoActReleaseInfoList":[{"createTime":"20220624141810","id":1,"lastUpdateTime":"20220624141811","status":1}],"id":1,"detail":"购买洗衣送烘干券","beginTime":"20220624141524","endTime":"20230924141525","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1} List<SapoActInfo> sapoActInfoList = SapoActInfo.getObjInfoList(SapoAct.getInstance().setStatus(1), colMap); System.err.println("sapoActInfoList : "+jacksonComponent.objectToJsonString(sapoActInfoList)); // sapoActInfoList : [{"code":"console-release-coupon","createTime":"20220818084909","resource":null,"name":"控制台派发券","sapoActReleaseInfoList":[],"id":7,"detail":null,"beginTime":"20220818084948","endTime":"20220818084950","sapoActTypeInfo":{"code":"console-release-coupon-act","createTime":"20220818084751","name":"控制台派发优惠券活动","id":8,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1},{"code":"222","createTime":"20220802143038","resource":"222","name":"222","sapoActReleaseInfoList":[{"createTime":"20220802163855","id":4,"lastUpdateTime":"20220802163855","status":1}],"id":6,"detail":"222","beginTime":"20220802143023","endTime":"20220818143034","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":"20220802143038","status":1},{"code":"buy-dry-release-wash","createTime":"20220624141643","resource":null,"name":"购买烘干送洗衣券","sapoActReleaseInfoList":[{"createTime":"20220624141824","id":2,"lastUpdateTime":"20220624141847","status":1}],"id":2,"detail":"购买烘干送洗衣券","beginTime":"20220624141701","endTime":"20230924141702","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1},{"code":"buy-wash-release-dry","createTime":"20220624141504","resource":null,"name":"购买洗衣送烘干券","sapoActReleaseInfoList":[{"createTime":"20220624141810","id":1,"lastUpdateTime":"20220624141811","status":1}],"id":1,"detail":"购买洗衣送烘干券","beginTime":"20220624141524","endTime":"20230924141525","sapoActTypeInfo":{"code":"inner-rule-release-coupon-act","createTime":"20220624142820","name":"内部规则触发送优惠券活动","id":3,"detail":null,"lastUpdateTime":null,"status":1},"lastUpdateTime":null,"status":1}] } }
