百木园
数据库表之间关系：
- 一对一 （可以看做一对多的特例）
- 一对多
- 多对多
下图将涵盖了所有关系。
根据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; } }
B实体对象
@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); } }
B实体返回对象
生成代码工具如下：
1 DROP PROCEDURE IF EXISTS `print_pojo`; 2 DELIMITER $ 3 CREATE PROCEDURE `print_pojo`() 4 BEGIN 5 6 SET group_concat_max_len = 4294967295; 7 8 -- 表名去除那些前缀 9 -- SET @noStrInTbl=\'tbl_ams\'; 10 SET @noStrInTbl=\'tbl\'; 11 -- 通用dao层类名， 12 SET @common_dao_name=\'SapoDao\'; 13 -- domain类名后缀 14 SET @domain_suffix=\'Info\'; 15 16 SET @domain_prefix=\'tbl_sapo\'; 17 18 19 20 -- ######################begin:基础信息表维护################### 21 22 -- 保存所有表及表的所有字段 23 DROP TABLE if EXISTS all_col_table; 24 CREATE table if not exists all_col_table( 25 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT \'主键\', 26 tbl_name VARCHAR(256) COMMENT \'表名:tbl_sapo_admin_account\', 27 tbl_name_comment VARCHAR(256) COMMENT \'表注释\', 28 29 tbl_name_upper_camel VARCHAR(1024) COMMENT \'表名大写驼峰:SapoAdminAccount\', 30 tbl_name_lower_camel VARCHAR(1024) COMMENT \'表名小写驼峰:sapoAdminAccount\', 31 32 domain_tbl_name_upper_camel VARCHAR(1024) COMMENT \'doamin层表名驼峰:SapoAdminAccountInfo\', 33 domain_tbl_name_lower_camel VARCHAR(1024) COMMENT \'domain层表名引用驼峰:sapoAdminAccountInfo\', 34 35 col VARCHAR(256) COMMENT \'字段名:create_time\', 36 if_fk VARCHAR(512) NOT NULL DEFAULT \'no\' COMMENT \'外键标志，yes=外键\', 37 col_comment VARCHAR(512) COMMENT \'字段注释\', 38 col_lower_camel VARCHAR(256) COMMENT \'字段驼峰形式:createTime\', 39 col_upper_camel VARCHAR(256) COMMENT \'字段驼峰首字母大写:CreateTime\', 40 41 col_type VARCHAR(256) COMMENT \'字段类型，datetime,int\', 42 java_type VARCHAR(256) COMMENT \'java类型，LocalDateTime,Integer\', 43 44 col_setter VARCHAR(256) COMMENT \'setter模式：setCreateTime\', 45 col_getter VARCHAR(256) COMMENT \'getter模式：getCreateTime\', 46 47 json_property VARCHAR(256) COMMENT \'json_property字段\', 48 49 PRIMARY KEY (`id`) , 50 index (`tbl_name`,col) , 51 INDEX idx_1(col) 52 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; 53 54 55 -- 外键临时表 56 DROP TABLE if exists fk_def; 57 CREATE TABLE if not exists fk_def as 58 SELECT 59 t.TABLE_NAME AS tbl_name, 60 k.column_name AS col, 61 k.REFERENCED_TABLE_NAME AS rf_tbl_name, 62 k.REFERENCED_COLUMN_NAME AS rf_col 63 FROM 64 information_schema.TABLE_CONSTRAINTS t 65 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 66 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 67 AND t.TABLE_NAME = k.TABLE_NAME 68 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 69 WHERE 70 t.CONSTRAINT_TYPE = \'FOREIGN KEY\' 71 AND t.table_schema = DATABASE(); 72 73 ALTER TABLE `fk_def` 74 ADD INDEX `idx1` (tbl_name,col); 75 76 ALTER TABLE `fk_def` 77 ADD INDEX `idx2` (rf_tbl_name,rf_col); 78 79 80 -- select * from fk_def ; 81 -- ######################end:基础信息表维护################### 82 83 84 -- 将本库中所有表及所有字段插入表中: tbl_name,tbl_name_comment,col,col_comment,col_type 85 INSERT INTO all_col_table(tbl_name,tbl_name_comment,col,col_comment,col_type) 86 SELECT 87 t1.table_name, t2.TABLE_COMMENT,t1.column_name ,t1.COLUMN_COMMENT,t1.DATA_TYPE 88 FROM 89 information_schema.COLUMNS t1 JOIN information_schema.tables t2 ON t1.TABLE_NAME=t2.TABLE_NAME 90 WHERE 91 t1.table_schema= DATABASE() AND t1.TABLE_NAME LIKE \'tbl_%\' ORDER BY t1.TABLE_NAME,t1.ORDINAL_POSITION; 92 93 -- java类型转换 94 UPDATE all_col_table SET java_type= 95 case col_type 96 when \'datetime\' then \'LocalDateTime\' 97 when \'tinyint\' then \'Byte\' 98 when \'bigint\' then \'Long\' 99 when \'int\' then \'Integer\' 100 when \'varchar\' then \'String\' 101 END; 102 103 -- 字段转驼峰 104 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\'),\'_\',\'\') 105 ,\'\'); 106 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\'),\'_\',\'\') 107 ,\'\'); 108 -- getter,setter 109 UPDATE all_col_table SET col_setter=CONCAT_WS(\'\',\'set\',col_upper_camel); 110 UPDATE all_col_table SET col_getter=CONCAT_WS(\'\',\'get\',col_upper_camel); 111 112 113 -- 表名转驼峰 114 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\'),\'_\',\'\') ; 115 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\'),\'_\',\'\') ; 116 UPDATE all_col_table SET domain_tbl_name_upper_camel= CONCAT_WS(\'\',tbl_name_upper_camel,@domain_suffix); 117 UPDATE all_col_table SET domain_tbl_name_lower_camel= CONCAT_WS(\'\',tbl_name_lower_camel,@domain_suffix); 118 119 -- 更新if_fk是否外键标志 120 UPDATE all_col_table a SET a.if_fk= 121 ifnull((SELECT \'yes\' FROM fk_def f WHERE f.tbl_name=a.tbl_name AND a.col = f.col),\'no\'); 122 123 124 125 -- ######################end:基础信息表维护################### 126 127 128 -- ######################begin:基础字段代码表################### 129 -- 拼接代码使用 130 DROP TABLE if EXISTS all_col_code; 131 CREATE table if not exists all_col_code( 132 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT \'主键\', 133 tbl_name VARCHAR(256) COMMENT \'表名:tbl_sapo_admin_account\', 134 col VARCHAR(256) COMMENT \'字段名:create_time\', 135 col_type VARCHAR(256) COMMENT \'字段类型，datetime,int\', 136 if_fk VARCHAR(512) NOT NULL DEFAULT \'no\' COMMENT \'外键标志，yes=外键\', 137 138 139 col_code LONGTEXT COMMENT \'private Integer id;\', 140 domain_col_code LONGTEXT COMMENT \'@注解 private Integer id;\', 141 142 domain_to_model LONGTEXT COMMENT \'\', 143 144 getter_setter_code LONGTEXT COMMENT \'getter ,setter\', 145 domain_getter_setter_code LONGTEXT COMMENT \'domain层getter,setter\', 146 147 to_string_code LONGTEXT COMMENT \'toString代码\', 148 domain_to_string_code LONGTEXT COMMENT \'domain_toString代码\', 149 150 domain_construct_code LONGTEXT COMMENT \'domain构造方法\', 151 PRIMARY KEY (`id`) , 152 index (`tbl_name`,col) 153 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; 154 155 156 157 -- 初始化表名和字段 158 INSERT INTO all_col_code (tbl_name,col,col_type,if_fk) SELECT tbl_name,col,col_type,if_fk FROM all_col_table; 159 160 -- domain_to_model @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 161 SET @temp=\' item.%col_setter%(this.%col_getter%());\\r\\n\'; 162 163 UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk=\'no\'; 164 165 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\'; 166 167 UPDATE all_col_code SET domain_to_model = @temp WHERE if_fk=\'yes\'; 168 169 -- col_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 170 SET @temp = 171 \' 172 // %col_comment% [%col%] 173 private %java_type% %col_lower_camel%; 174 175 \'; 176 177 UPDATE all_col_code SET col_code = @temp; 178 179 -- domain_col_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 180 181 -- 外键 182 SET @temp = 183 \' 184 // 外键关联实体类(字段：[ %col% ] 注释：[ %col_comment% ]) 185 // %fk_tbl_name_comment%(关联表名：%fk_tbl_name% ) 186 @JsonProperty(\"%json_property%\") 187 private %fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%; 188 189 \'; 190 191 192 193 UPDATE all_col_code SET domain_col_code = @temp WHERE if_fk=\'yes\'; 194 195 -- datetime字段 196 SET @temp = 197 \' 198 // %col_comment% 199 @JsonProperty(\"%col_lower_camel%\") 200 @JsonFormat(pattern = \"yyyyMMddHHmmss\") 201 @JsonDeserialize(using = LocalDateTimeDeserializer.class) 202 @JsonSerialize(using = LocalDateTimeSerializer.class) 203 private %java_type% %col_lower_camel%; 204 \'; 205 206 UPDATE all_col_code SET domain_col_code = @temp WHERE col_type=\'datetime\'; 207 208 -- 普通字段 209 SET @temp= 210 \' 211 // %col_comment% 212 @JsonProperty(\"%col_lower_camel%\") 213 private %java_type% %col_lower_camel%; 214 215 \'; 216 UPDATE all_col_code SET domain_col_code = @temp WHERE domain_col_code IS NULL; 217 218 219 220 -- getter_setter_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 221 SET @temp= 222 \' 223 /** 224 * 设置 225 * %col_comment% 226 * 的方法 227 * 228 * @param %col_lower_camel% %col_comment% 229 */ 230 public %tbl_name_upper_camel% %col_setter%(%java_type% %col_lower_camel%){ 231 this.%col_lower_camel% = %col_lower_camel%; 232 return this; 233 } 234 235 /** 236 * 获取 237 * %col_comment% 238 * 的方法 239 * 240 * @return %col_comment% 241 */ 242 public %java_type% %col_getter%(){ 243 return %col_lower_camel%; 244 } 245 \' 246 ; 247 248 update all_col_code SET getter_setter_code=@temp ; 249 250 -- domain_getter_setter_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 251 252 -- 不带外键 253 SET @temp= 254 \' 255 /** 256 * 设置 257 * %col_comment% 258 * 的方法 259 * 260 * @param %col_lower_camel% %col_comment% 261 */ 262 public void %col_setter%(%java_type% %col_lower_camel%){ 263 this.%col_lower_camel% = %col_lower_camel%; 264 } 265 266 /** 267 * 获取 268 * %col_comment% 269 * 的方法 270 * 271 * @return %col_comment% 272 */ 273 public %java_type% %col_getter%(){ 274 return %col_lower_camel%; 275 } 276 \' 277 ; 278 update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk=\'no\'; 279 280 -- 外键字段 281 SET @temp= 282 \' 283 /** 284 * 设置 285 * %fk_tbl_name_comment%(%fk_tbl_name% ) 286 * 的方法 287 * 288 * @param %fk_tbl_name_comment%(%fk_tbl_name% ) 289 */ 290 public %fk_domain_tbl_name_upper_camel% set%fk_domain_tbl_name_upper_camel%(%fk_domain_tbl_name_upper_camel% %fk_domain_tbl_name_lower_camel%){ 291 this.%fk_domain_tbl_name_lower_camel% = %fk_domain_tbl_name_lower_camel%; 292 return this; 293 } 294 295 /** 296 * 获取 297 * %fk_tbl_name_comment%(%fk_tbl_name% ) 298 * 的方法 299 * 300 * @return %col_comment% 301 */ 302 public %fk_domain_tbl_name_upper_camel% get%fk_domain_tbl_name_upper_camel%(){ 303 return %fk_domain_tbl_name_lower_camel%; 304 } 305 \'; 306 307 update all_col_code SET domain_getter_setter_code=@temp WHERE if_fk=\'yes\' ; 308 309 -- to_string_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 310 SET @temp=\'\"%col_lower_camel%=\" + %col_lower_camel%\'; 311 312 UPDATE all_col_code SET to_string_code =@temp; 313 314 -- domain_to_string_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 315 -- 不是外键，延续之前的to_string_code 316 UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk=\'no\'; 317 318 -- 如果是外键 319 SET @temp=\'\"%fk_domain_tbl_name_lower_camel%=\" + %fk_domain_tbl_name_lower_camel%\'; 320 UPDATE all_col_code SET domain_to_string_code =@temp WHERE if_fk=\'yes\'; 321 322 -- domain_construct_code @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 323 -- 外键字段 324 SET @temp= 325 \' 326 // 外键关联对象：%fk_tbl_name_comment%(%fk_tbl_name%) 327 // 如果注解标注不需要该字段，则不用查询该字段 328 if(annotation != null && (! java.util.Arrays.asList(annotation.value()).contains(\"%json_property%\"))) { 329 // 不管状态，只用外键查询 330 // %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel% = dao.get%fk_tbl_name_upper_camel%By%fk_col_upper_camel%(%tbl_name_lower_camel%.%col_getter%()); 331 332 %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel%Result = null; 333 334 // 外键字段不为空才有去查的意义 335 if(%tbl_name_lower_camel%.%col_getter%() != null){ 336 // 组建查询条件 337 %fk_tbl_name_upper_camel% %fk_tbl_name_lower_camel%ForQuery = new %fk_tbl_name_upper_camel%(); 338 339 %fk_tbl_name_lower_camel%ForQuery.set%fk_col_upper_camel%(%tbl_name_lower_camel%.%col_getter%()); 340 // %fk_tbl_name_lower_camel%ForQuery.setStatus(%fk_tbl_name_upper_camel%.STATUS_INVALID); 341 342 // 查询，不可为空 343 %fk_tbl_name_lower_camel%Result = dao.get%fk_tbl_name_upper_camel%(%fk_tbl_name_lower_camel%ForQuery) ; 344 } 345 346 // 如果能查该外键对应的值，则进行赋值操作 347 if(%fk_tbl_name_lower_camel%Result !=null){ 348 349 // 判断该字段是否被注解标识，使用哪个子类 350 FkPojo fkano = thisClass.getAnnotation(FkPojo.class); 351 352 // 如果没有被注解标注，需要报错 353 if (fkano == null || fkano.value().length == 0) { 354 throw new Exception(thisClass.getName() + \" -> must have @PkPojo annotation and lenght !=0 \"); 355 } 356 357 // pojo不符合规范也要报错，注解用冒号分开[类名:全类名] 358 Map<String, String> map = new HashMap<String, String>(); 359 for (String s : fkano.value()) { 360 s = s.trim(); 361 String[] split = s.split(\":\"); 362 if (split == null || split.length != 2) { 363 throw new Exception( 364 thisClass.getName() + \" -> @PkPojo annotation format error [pojoName:pojoAllPath] \"); 365 } 366 map.put(split[0], split[1]); 367 } 368 //如果没有标识子类使用哪一个，则报错 369 if(map.get(\"%fk_domain_tbl_name_upper_camel%\")==null){ 370 throw new Exception(thisClass.getName() + \" -> @PkPojo annotation has no class: %fk_domain_tbl_name_upper_camel%\"); 371 } 372 // 反射出子类，将子类赋值给该对象。 373 Class<?> c1 = Class.forName(map.get(\"%fk_domain_tbl_name_upper_camel%\")); 374 Constructor<?> declaredConstructor = c1.getDeclaredConstructor(%common_dao_name%.class, %fk_tbl_name_upper_camel%.class); 375 this.%fk_domain_tbl_name_lower_camel% = (%fk_domain_tbl_name_upper_camel%)declaredConstructor.newInstance(dao, %fk_tbl_name_lower_camel%Result); 376 377 } 378 } 379 \'; 380 381 SET @temp:=REPLACE(@temp,\'%common_dao_name%\',@common_dao_name); 382 383 UPDATE all_col_code SET domain_construct_code=@temp WHERE if_fk=\'yes\'; 384 385 386 387 -- 不是外键字段 388 SET @temp= 389 \' 390 this.%col_lower_camel%=%tbl_name_lower_camel%.%col_getter%(); 391 \' 392 ; 393 UPDATE all_col_code SET domain_construct_code =@temp WHERE if_fk=\'no\'; 394 395 -- ######################begin:基础字段代码表################### 396 397 /* 398 -- 建立外键详情表需要打印出字段列表 399 SELECT 400 GROUP_CONCAT( 401 CONCAT_WS(\'\',\'a.\', t1.column_name ,\' as fk_\',t1.column_name ) 402 ) 403 FROM 404 information_schema.COLUMNS t1 405 WHERE 406 t1.table_schema= DATABASE() AND t1.TABLE_NAME=\'all_col_table\'; 407 408 */ 409 410 -- 外键明细表 411 DROP TABLE if exists fk_all_col_table; 412 CREATE TABLE if NOT exists fk_all_col_table 413 SELECT 414 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 415 ,a.tbl_name_upper_camel as fk_tbl_name_upper_camel,a.tbl_name_lower_camel as fk_tbl_name_lower_camel 416 ,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 417 ,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 418 ,a.col_upper_camel as fk_col_upper_camel,a.col_type as fk_col_type 419 ,a.java_type as fk_java_type,a.col_setter as fk_col_setter,a.col_getter as fk_col_getter 420 FROM all_col_table a 421 JOIN fk_def f 422 ON a.tbl_name=f.rf_tbl_name 423 WHERE f.rf_col=a.col 424 ; 425 426 ALTER TABLE `fk_all_col_table` 427 ADD INDEX `idx1` (tbl_name,col); 428 429 430 431 -- 更新 json_property ，及在json注解中显示的名字 432 UPDATE fk_all_col_table SET json_property= 433 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\'),\'_\',\'\') 434 ,\'Info\'); 435 436 -- json_property 437 438 UPDATE all_col_table SET json_property = col_lower_camel WHERE if_fk=\'no\'; 439 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) 440 WHERE a.if_fk=\'yes\'; 441 442 443 SET @temp=\' 444 UPDATE all_col_code c SET c.@col_code@= REPLACE(c.@col_code@,\\\'%@tbl_name@%\\\', 445 ifnull((select a.@tbl_name@ FROM @all_col_table@ a WHERE c.tbl_name=a.tbl_name AND c.col=a.col),\\\'%@tbl_name@%\\\') 446 ); 447 \'; 448 449 450 451 -- 所有执行替换的sql都在该表中 452 DROP TABLE if exists exe_sql_tbl; 453 CREATE TABLE if NOT EXISTS exe_sql_tbl as 454 SELECT (@id:=@id+1) as id,t1.TABLE_NAME,t.col, 455 replace( 456 replace( 457 REPLACE(@temp,\'@tbl_name@\',t1.COLUMN_NAME) 458 ,\'@all_col_table@\',t1.TABLE_NAME) 459 ,\'@col_code@\',t.col) 460 AS code 461 FROM 462 information_schema.COLUMNS t1 463 JOIN ( 464 SELECT \'col_code\' AS col 465 UNION ALL 466 SELECT \'domain_col_code\' 467 UNION ALL 468 SELECT \'getter_setter_code\' 469 UNION ALL 470 SELECT \'domain_getter_setter_code\' 471 UNION ALL 472 SELECT \'to_string_code\' 473 UNION ALL 474 SELECT \'domain_to_string_code\' 475 UNION ALL 476 SELECT \'domain_construct_code\' 477 UNION all 478 SELECT \'domain_to_model\' 479 ) t 480 JOIN (SELECT @id:=0) tt 481 WHERE 482 t1.table_schema= DATABASE() AND t1.TABLE_NAME IN (\'all_col_table\',\'fk_all_col_table\') 483 ; 484 485 ALTER TABLE `exe_sql_tbl` 486 ADD INDEX `idx1` (id); 487 488 -- select * from exe_sql_tbl; 489 -- 确定循环次数 490 SELECT MAX(id) INTO @var_count FROM exe_sql_tbl; 491 492 -- 循环 493 loop_lab:LOOP 494 if @var_count = 0 then 495 leave loop_lab; 496 END if; 497 498 -- 从表中取出一个sql 499 SELECT CODE INTO @exe_sql FROM exe_sql_tbl WHERE id =@var_count; 500 501 -- 动态执行 502 PREPARE stmt FROM @exe_sql ; 503 EXECUTE stmt ; 504 505 SET @var_count = @var_count -1; 506 END LOOP; 507 508 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 509 510 -- 表相关数据 511 DROP TABLE if EXISTS all_table; 512 CREATE table if not exists all_table( 513 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT \'主键\', 514 tbl_name VARCHAR(256) NOT NULL COMMENT \'表名:tbl_sapo_admin_account\', 515 tbl_name_upper_camel VARCHAR(1024) COMMENT \'表名驼峰:SapoAdminAccount\', 516 tbl_name_lower_camel VARCHAR(1024) COMMENT \'表名引用驼峰:sapoAdminAccount\', 517 domain_tbl_name_upper_camel VARCHAR(1024) COMMENT \'表名驼峰:SapoAdminAccount\', 518 domain_tbl_name_lower_camel VARCHAR(1024) COMMENT \'表名引用驼峰:sapoAdminAccount\', 519 pojo LONGTEXT COMMENT \'\', 520 domain_pojo LONGTEXT COMMENT \'\', 521 controller_res_pojo LONGTEXT COMMENT \'\', 522 controller_req_pojo LONGTEXT COMMENT \'\', 523 controller_pojo_annotation LONGTEXT COMMENT\'\', 524 controller_pojo_fk_pojo_annotation LONGTEXT COMMENT \'\', 525 domain_to_model LONGTEXT COMMENT \'\', 526 constant_code LONGTEXT COMMENT \'\', 527 domain_construct_code LONGTEXT COMMENT \'\', 528 list_domain_construct_code LONGTEXT COMMENT \'\', 529 to_string_code LONGTEXT COMMENT \'toString\', 530 domain_to_string_code LONGTEXT COMMENT \'toString\', 531 col_code LONGTEXT COMMENT \'getter_setter_code\', 532 list_col_code LONGTEXT COMMENT \'getter_setter_code\', 533 getter_setter_code LONGTEXT COMMENT \'\', 534 list_getter_setter_code LONGTEXT COMMENT \'\', 535 domain_col_code LONGTEXT COMMENT \'\', 536 domain_getter_setter_code LONGTEXT COMMENT \'\', 537 PRIMARY KEY (`id`) , 538 index (`tbl_name`) 539 ) ENGINE=InnoDB ; 540 541 -- 初始化表名等字段 542 INSERT INTO all_table (tbl_name,tbl_name_upper_camel,tbl_name_lower_camel,domain_tbl_name_upper_camel,domain_tbl_name_lower_camel) 543 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; 544 545 546 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 547 -- 设置 被外键关联的表。 例如：业务订单下面挂了好几个支付订单 548 -- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 549 550 551 DROP TABLE if EXISTS fk_list; 552 CREATE TABLE if NOT exists fk_list 553 SELECT f.rf_tbl_name ,f.rf_col 554 ,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 555 ,a.* 556 from fk_def f 557 JOIN all_col_table a 558 on f.tbl_name = a.tbl_name and f.col=a.col; 559 560 ALTER TABLE `fk_list` 561 ADD COLUMN `rf_tbl_name_lower_camel` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_tbl_name`; 562 563 ALTER TABLE `fk_list` 564 ADD COLUMN `rf_col_getter` VARCHAR(1024) NULL DEFAULT NULL AFTER `rf_col`; 565 566 ALTER TABLE `fk_list` 567 ADD INDEX `idx1` (rf_tbl_name); 568 569 -- SELECT * FROM fk_list; 570 -- list_col_code 571 572 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); 573 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); 574 575 -- domain_to_model 576 577 SET @temp= 578 \' 579 // 将domain对象转成model对象 580 public %tbl_name_upper_camel% domain2%tbl_name_upper_camel%(){ 581 %tbl_name_upper_camel% item = new %tbl_name_upper_camel%(); 582 %domain_to_model% 583 return item; 584 } 585 \'; 586 587 UPDATE all_table a SET a.domain_to_model= 588 REPLACE( 589 REPLACE(@temp,\'%domain_to_model%\', 590 ( 591 SELECT 592 GROUP_CONCAT( c.domain_to_model ORDER BY c.id SEPARATOR \'\') 593 FROM all_col_code c WHERE c.tbl_name=a.tbl_name 594 ) 595 ) 596 ,\'%tbl_name_upper_camel%\',a.tbl_name_upper_camel) 597 ; 598 599 -- list_col_code 600 601 SET @temp= 602 \' 603 // %tbl_name_comment% 集合 [%tbl_name%][%col%] 604 @JsonProperty(\"%json_property_list%\") 605 private List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List; 606 \' 607 ; 608 UPDATE all_table atb SET atb.list_col_code= 609 ( 610 SELECT GROUP_CONCAT( 611 REPLACE( 612 REPLACE( 613 REPLACE( 614 REPLACE( 615 REPLACE( 616 REPLACE(@temp,\'%tbl_name_comment%\',tbl_name_comment) 617 ,\'%tbl_name%\',tbl_name) 618 ,\'%col%\',col) 619 ,\'%json_property_list%\',json_property_list) 620 ,\'%domain_tbl_name_upper_camel%\',domain_tbl_name_upper_camel) 621 ,\'%domain_tbl_name_lower_camel%\',domain_tbl_name_lower_camel) 622 SEPARATOR \'\') 623 FROM fk_list t 624 WHERE atb.tbl_name=t.rf_tbl_name 625 ) ; 626 627 -- SELECT * FROM fk_list; 628 629 -- 外键list，构造对象语句 630 SET @temp= 631 \' 632 // 注解如果将该字段忽略了，就不需要查了。 633 if (annotation != null && (! java.util.Arrays.asList(annotation.value()).contains(\"%json_property_list%\"))) { 634 635 %tbl_name_upper_camel% %tbl_name_lower_camel%ForQuery = new %tbl_name_upper_camel%(); 636 %tbl_name_lower_camel%ForQuery.%col_setter%(%rf_tbl_name_lower_camel%.%rf_col_getter%()); 637 638 List<%tbl_name_upper_camel%> %tbl_name_lower_camel%List = dao.get%tbl_name_upper_camel%ListWithNull(%tbl_name_lower_camel%ForQuery); 639 640 // 如果查询的集合是空，也不用继续往下查了。 641 if (%tbl_name_lower_camel%List != null && %tbl_name_lower_camel%List.size() != 0) { 642 %domain_tbl_name_lower_camel%List = new ArrayList<%domain_tbl_name_upper_camel%>(%tbl_name_lower_camel%List.size()); 643 // 判断该字段是否被注解标识，使用哪个子类 644 FkPojo fkano = thisClass.getAnnotation(FkPojo.class); 645 646 // 如果没有被注解标注，需要报错 647 if (fkano == null || fkano.value().length == 0) { 648 throw new Exception(thisClass.getName() + \" -> must have @PkPojo annotation and lenght !=0 \"); 649 } 650 651 // pojo不符合规范也要报错，注解用冒号分开[类名:全类名] 652 Map<String, String> map = new HashMap<String, String>(); 653 for (String s : fkano.value()) { 654 s = s.trim(); 655 String[] split = s.split(\":\"); 656 if (split == null || split.length != 2) { 657 throw new Exception( 658 thisClass.getName() + \" -> @PkPojo annotation format error [pojoName:pojoAllPath] \"); 659 } 660 map.put(split[0], split[1]); 661 } 662 // 如果没有标识子类使用哪一个，则报错 663 if (map.get(\"%domain_tbl_name_upper_camel%\") == null) { 664 throw new Exception( 665 thisClass.getName() + \" -> @PkPojo annotation has no class: %domain_tbl_name_upper_camel%\"); 666 } 667 // 反射出子类，将子类赋值给该对象。 668 Class<?> c1 = Class.forName(map.get(\"%domain_tbl_name_upper_camel%\")); 669 Constructor<?> declaredConstructor = c1.getDeclaredConstructor(%common_dao_name%.class, %tbl_name_upper_camel%.class); 670 671 // 循环新建对象，将对象加入到集合中。 672 for (%tbl_name_upper_camel% item : %tbl_name_lower_camel%List) { 673 %domain_tbl_name_lower_camel%List.add((%domain_tbl_name_upper_camel%) declaredConstructor.newInstance(dao, item)); 674 } 675 } 676 } 677 \'; 678 679 SET @temp:=REPLACE(@temp,\'%common_dao_name%\',@common_dao_name); 680 681 682 update all_table a SET a.list_domain_construct_code= 683 ( 684 SELECT GROUP_CONCAT( 685 REPLACE( 686 REPLACE( 687 REPLACE( 688 REPLACE( 689 REPLACE( 690 REPLACE( 691 REPLACE( 692 REPLACE(@temp,\'%json_property_list%\',json_property_list) 693 ,\'%tbl_name_upper_camel%\',tbl_name_upper_camel) 694 ,\'%tbl_name_lower_camel%\',tbl_name_lower_camel) 695 ,\'%col_setter%\',col_setter) 696 ,\'%rf_tbl_name_lower_camel%\',rf_tbl_name_lower_camel) 697 ,\'%rf_col_getter%\',rf_col_getter) 698 ,\'%domain_tbl_name_lower_camel%\',domain_tbl_name_lower_camel) 699 ,\'%domain_tbl_name_upper_camel%\',domain_tbl_name_upper_camel) 700 order BY f.tbl_name separator \'\') 701 FROM fk_list f 702 WHERE f.rf_tbl_name=a.tbl_name 703 ); 704 705 706 707 -- list_getter_setter_code 708 709 SET @temp= 710 \' 711 712 public void set%domain_tbl_name_upper_camel%List(List<%domain_tbl_name_upper_camel%> %domain_tbl_name_lower_camel%List){ 713 this.%domain_tbl_name_lower_camel%List = %domain_tbl_name_lower_camel%List; 714 } 715 716 717 public List<%domain_tbl_name_upper_camel%> get%domain_tbl_name_upper_camel%List(){ 718 return %domain_tbl_name_lower_camel%List; 719 } 720 \'; 721 722 UPDATE all_table atb SET atb.list_getter_setter_code= 723 ( 724 SELECT GROUP_CONCAT( 725 REPLACE( 726 REPLACE( 727 REPLACE( 728 REPLACE( 729 REPLACE( 730 REPLACE(@temp,\'%tbl_name_comment%\',tbl_name_comment) 731 ,\'%tbl_name%\',tbl_name) 732 ,\'%col%\',col) 733 ,\'%json_property_list%\',json_property_list) 734 ,\'%domain_tbl_name_upper_camel%\',domain_tbl_name_upper_camel) 735 ,\'%domain_tbl_name_lower_camel%\',domain_tbl_name_lower_camel) 736 SEPARATOR \'\') 737 FROM fk_list t 738 WHERE atb.tbl_name=t.rf_tbl_name 739 ) ; 740 741 -- list_controller_pojo_annotation 742 743 -- list_controller_pojo_fk_pojo_annotation 744 745 746 747 -- col_code 748 UPDATE all_table a SET a.col_code= 749 ( 750 SELECT GROUP_CONCAT(col_code ORDER BY id SEPARATOR \'\') 751 FROM all_col_code 752 WHERE a.tbl_name =tbl_name 753 ); 754 755 756 757 758 -- getter_setter_code 759 UPDATE all_table a SET a.getter_setter_code= 760 ( 761 SELECT GROUP_CONCAT(getter_setter_code ORDER BY id SEPARATOR \'\') 762 FROM all_col_code 763 WHERE a.tbl_name =tbl_name 764 ); 765 766 767 -- domain_col_code 768 UPDATE all_table a SET a.domain_col_code= 769 ( 770 SELECT GROUP_CONCAT(domain_col_code ORDER BY id SEPARATOR \'\') 771 FROM all_col_code 772 WHERE a.tbl_name =tbl_name 773 ); 774 775 -- 将外键list加上 776 UPDATE all_table a SET a.domain_col_code=CONCAT_WS(\'\',domain_col_code,\'\\r\\n\',list_col_code); 777 778 -- domain_getter_setter_code 779 UPDATE all_table a SET a.domain_getter_setter_code= 780 ( 781 SELECT GROUP_CONCAT(domain_getter_setter_code ORDER BY id SEPARATOR \'\') 782 FROM all_col_code 783 WHERE a.tbl_name =tbl_name 784 ); 785 786 -- 将外键list加上 787 UPDATE all_table a SET a.domain_getter_setter_code=CONCAT_WS(\'\',domain_getter_setter_code,\'\\r\\n\',list_getter_setter_code); 788 789 790 -- toString() 791 792 SET @template= 793 \' 794 @Override 795 public String toString(){ 796 return \"%tbl_name_upper_camel% [\"+%to_string_code%+\" ]\"; 797 } 798 \'; 799 800 UPDATE all_table a SET a.to_string_code= 801 ( 802 SELECT 803 REPLACE( 804 REPLACE( 805 @template,\'%to_string_code%\',GROUP_CONCAT(to_string_code SEPARATOR \'+ \" , \" +\' ) 806 ) 807 ,\'%tbl_name_upper_camel%\',a.tbl_name_upper_camel 808 ) 809 FROM all_col_code 810 WHERE a.tbl_name = tbl_name 811 ); 812 813 -- domain_to_string_code 814 815 UPDATE all_table a SET a.domain_to_string_code= 816 ( 817 SELECT 818 REPLACE( 819 REPLACE(@template,\'%to_string_code%\', 820 GROUP_CONCAT(t.domain_to_string_code SEPARATOR \'+ \" , \" +\') 821 ),\'%tbl_name_upper_camel%\',a.domain_tbl_name_upper_camel 822 ) 823 FROM 824 ( 825 SELECT tbl_name,domain_to_string_code FROM all_col_code 826 UNION ALL /*以下为了展示 外键list*/ 827 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 828 ) t 829 WHERE a.tbl_name = t.tbl_name 830 ); 831 832 833 -- domain_construct 构造器 834 835 SET @template=\' 836 // 构造方法 837 public %domain_tbl_name_upper_camel%(%common_dao_name% dao ,%tbl_name_upper_camel% %tbl_name_lower_camel%) throws Exception { 838 super(); 839 //System.err.println(\"-----new %domain_tbl_name_upper_camel%--------------\"); 840 841 Class<? extends %domain_tbl_name_upper_camel%> thisClass = this.getClass(); 842 JsonIgnoreProperties annotation = thisClass.getAnnotation(JsonIgnoreProperties.class); 843 844 845 %domain_construct_code% 846 %list_domain_construct_code% 847 848 } 849 \'; 850 851 SET @template:=REPLACE(@template,\'%common_dao_name%\',@common_dao_name); 852 853 UPDATE all_table a SET a.domain_construct_code= 854 ( 855 SELECT 856 REPLACE( 857 REPLACE( 858 REPLACE( 859 REPLACE( 860 REPLACE(@template,\'%domain_construct_code%\',GROUP_CONCAT(domain_construct_code SEPARATOR \'\\r\\n\')) 861 ,\'%domain_tbl_name_lower_camel%\',a.domain_tbl_name_lower_camel) 862 ,\'%domain_tbl_name_upper_camel%\',a.domain_tbl_name_upper_camel) 863 ,\'%tbl_name_upper_camel%\',a.tbl_name_upper_camel) 864 ,\'%tbl_name_lower_camel%\',a.tbl_name_lower_camel) 865 866 FROM all_col_code 867 WHERE a.tbl_name = tbl_name 868 ); 869 -- 将外键list更新进去 870 UPDATE all_table SET domain_construct_code=REPLACE(domain_construct_code,\'%list_domain_construct_code%\',ifnull(list_domain_construct_code,\' \')); 871 872 -- constant 静态常量 873 874 SET @template=\' 875 //%biz_desc%(%tbl_name% [%col%]) : %col_value_desc% 876 public static final %java_type% %upper% = %col_value%; 877 \'; 878 879 -- 如果不存在就创建表。防止报错 880 CREATE TABLE if NOT EXISTS `sys_code_mapper` ( 881 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT \'主键\', 882 `create_time` datetime(3) NULL COMMENT \'创建时间\', 883 `biz_desc` varchar(255) NULL COMMENT \'业务含义\', 884 `tbl_name` varchar(255) NULL COMMENT \'表名\', 885 `col` varchar(255) NULL COMMENT \'字段\', 886 `col_value` varchar(255) NULL COMMENT \'字段值\', 887 `col_value_desc` varchar(255) NULL COMMENT \'字段值含义\', 888 `remark` varchar(255) NULL COMMENT \'备注\', 889 PRIMARY KEY (`id`) , 890 INDEX idx_1(tbl_name,col) 891 )COMMENT = \'系统代码映射表\'; 892 893 UPDATE all_table s SET s.constant_code = 894 ( 895 SELECT 896 GROUP_CONCAT( 897 REPLACE( 898 REPLACE( 899 REPLACE( 900 REPLACE( 901 REPLACE( 902 REPLACE( 903 REPLACE( 904 @template,\'%upper%\', CONCAT_WS(\'\',UPPER(d.col),\'_\',UPPER(d.remark)) ) 905 ,\'%biz_desc%\',d.biz_desc) 906 ,\'%tbl_name%\',d.tbl_name) 907 ,\'%col%\',d.col) 908 ,\'%col_value_desc%\',d.col_value_desc) 909 ,\'%java_type%\',a.java_type) 910 ,\'%col_value%\',if(a.java_type=\'String\',CONCAT_WS(\'\',\'\"\',d.col_value,\'\"\'),d.col_value)) 911 SEPARATOR \'\') 912 FROM sys_code_mapper d 913 JOIN all_col_table a 914 ON a.tbl_name=d.tbl_name AND a.col=d.col 915 where d.tbl_name=s.tbl_name 916 ); 917 918 -- controller_pojo_annotation 919 920 SET @template= 921 \' 922 @JsonIgnoreProperties({ 923 %controller_pojo_annotation% 924 }) 925 \'; 926 927 UPDATE all_table a SET a.controller_pojo_annotation= 928 REPLACE(@template,\'%controller_pojo_annotation%\', 929 ( 930 SELECT 931 GROUP_CONCAT( 932 CONCAT_WS(\'\',\'\"\',t.json_property,\'\"\') 933 order BY t.tbl_name,id SEPARATOR \',\\r\\n\' ) 934 FROM 935 ( 936 SELECT id,tbl_name,json_property FROM all_col_table 937 UNION ALL 938 SELECT id,rf_tbl_name,json_property_list FROM fk_list /* 该行：添加外键list语句*/ 939 ) t 940 WHERE t.tbl_name = a.tbl_name 941 ) 942 ); 943 944 945 946 SET @template=\'@FkPojo({ 947 %controller_pojo_fk_pojo_annotation% 948 })\'; 949 950 UPDATE all_table a SET a.controller_pojo_fk_pojo_annotation= 951 REPLACE(@template,\'%controller_pojo_fk_pojo_annotation%\', 952 ( 953 SELECT 954 GROUP_CONCAT( 955 CONCAT_WS(\'\',\'\"\',t.fk_domain_tbl_name_upper_camel,\':\',\'classQualifiedName\',\'\"\') 956 order BY t.tbl_name SEPARATOR \',\\r\\n\') 957 FROM 958 ( 959 SELECT tbl_name,fk_domain_tbl_name_upper_camel FROM fk_all_col_table 960 UNION ALL 961 SELECT rf_tbl_name,domain_tbl_name_upper_camel FROM fk_list /* 该行：添加外键list语句*/ 962 ) t WHERE t.tbl_name = a.tbl_name 963 ) 964 ); 965 966 967 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 968 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 969 -- 组java类代码@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 970 971 -- pojo 972 973 SET @template= 974 \' 975 976 977 public class %tbl_name_upper_camel% implements Serializable { 978 979 private static final long serialVersionUID = 1L; 980 981 public %tbl_name_upper_camel%(){} 982 983 public static %tbl_name_upper_camel% getInstance(){ 984 return new %tbl_name_upper_camel%(); 985 } 986 987 // 赋值一个一模一样的对象 988 public static %tbl_name_upper_camel% getInstance(%tbl_name_upper_camel% source){ 989 %tbl_name_upper_camel% target = new %tbl_name_upper_camel%(); 990 org.springframework.beans.BeanUtils.copyProperties(source,target); 991 return target; 992 } 993 994 %constant_code% 995 %col_code% 996 %getter_setter_code% 997 %to_string_code% 998 } 999 \'; 1000 1001 UPDATE all_table SET pojo= 1002 REPLACE( 1003 REPLACE( 1004 REPLACE( 1005 REPLACE( 1006 REPLACE( 1007 @template,\'%tbl_name_upper_camel%\',tbl_name_upper_camel) 1008 ,\'%col_code%\',col_code) 1009 ,\'%getter_setter_code%\',getter_setter_code) 1010 ,\'%to_string_code%\',to_string_code) 1011 ,\'%constant_code%\',ifnull(constant_code,\'\')) 1012 ; 1013 1014 -- domain_pojo 1015 SET @template= 1016 \' 1017 public class %tbl_name_upper_camel%%doamin_suffix% implements Serializable { 1018 1019 /* 1020 <!-- 时间类序列化包 --> 1021 <dependency> 1022 <groupId>com.fasterxml.jackson.datatype</groupId> 1023 <artifactId>jackson-datatype-jsr310</artifactId> 1024 </dependency> 1025 @JsonProperty(value = \"xx\",access = JsonProperty.Access.WRITE_ONLY) 1026 @JsonProperty(value = \"xx\",access = JsonProperty.Access.READ_ONLY) 1027 */ 1028 private static final long serialVersionUID = 1L; 1029 1030 public %tbl_name_upper_camel%%doamin_suffix%(){} 1031 1032 %domain_to_model% 1033 %constant_code% 1034 %col_code% 1035 %domain_construct_code% 1036 %getter_setter_code% 1037 %to_string_code% 1038 } 1039 \'; 1040 1041 -- select * from all_table; 1042 -- select * from all_col_table; 1043 1044 SET @template:=REPLACE(@template,\'%doamin_suffix%\',@domain_suffix); 1045 1046 UPDATE all_table SET domain_pojo= 1047 REPLACE( 1048 REPLACE( 1049 REPLACE( 1050 REPLACE( 1051 REPLACE( 1052 REPLACE( 1053 REPLACE( 1054 @template,\'%tbl_name_upper_camel%\',tbl_name_upper_camel) 1055 ,\'%col_code%\',domain_col_code) 1056 ,\'%getter_setter_code%\',domain_getter_setter_code) 1057 ,\'%to_string_code%\',domain_to_string_code) 1058 ,\'%domain_construct_code%\',domain_construct_code) 1059 ,\'%domain_to_model%\',domain_to_model) 1060 ,\'%constant_code%\',ifnull(constant_code,\'\')) 1061 ; 1062 1063 -- SELECT * FROM all_table; 1064 -- controller_res_pojo 1065 1066 SET @template= 1067 \' 1068 %controller_pojo_annotation% 1069 public class %domain_tbl_name_upper_camel%xx extends %domain_tbl_name_upper_camel%{ 1070 1071 private static final long serialVersionUID = 1L; 1072 1073 public %domain_tbl_name_upper_camel%xx(){} 1074 /* 1075 @NotNull(message=\" xx can not be null\") 验证对象是否不为null, 无法查检长度为0的字符串 1076 @Range(min = 1, max = 100000, message = \" xx not between [xx,xx] range\") 1077 1078 @NotBlank(message=\" xx can not be blank\") 检查约束 (字符串) 是不是Null还有被Trim的长度是否大于0,只对字符串,且会去掉前后空格. 1079 @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 \") 1080 1081 @NotEmpty(message=\" xx can not be null or empty\") 检查(集合)约束元素是否为NULL或者是EMPTY. 1082 @Size(min = 1, max = 1000, message = \" xx size not between [xx,xx]\") 1083 1084 @NotNull(message=\" xx can not be null\") 对象级联判断 1085 @Valid 1086 注：@JsonIgnoreProperties 和 @NotBlank 混用注意。可能忽略了字段，但是该字段上有非空注解。因为忽略肯定空，但是非空又在校验，故报错。 1087 所以忽略的字段不加非空判断。只能手动挨个加。 1088 */ 1089 %col_code% 1090 %getter_setter_code% 1091 %to_string_code% 1092 } 1093 \'; 1094 SET @template:=REPLACE(@template,\'%common_dao_name%\',@common_dao_name); 1095 1096 UPDATE all_table SET controller_req_pojo= 1097 REPLACE( 1098 REPLACE( 1099 REPLACE( 1100 REPLACE( 1101 REPLACE( 1102 REPLACE( 1103 REPLACE( 1104 REPLACE( 1105 REPLACE(@template,\'%controller_pojo_annotation%\',controller_pojo_annotation) 1106 ,\'%controller_pojo_fk_pojo_annotation%\',ifnull(controller_pojo_fk_pojo_annotation,\'\')) 1107 ,\'%domain_tbl_name_upper_camel%\',domain_tbl_name_upper_camel) 1108 ,\'%domain_tbl_name_lower_camel%\',domain_tbl_name_lower_camel) 1109 ,\'%tbl_name_lower_camel%\',tbl_name_lower_camel) 1110 ,\'%col_code%\',domain_col_code) 1111 ,\'%to_string_code%\',domain_to_string_code) 1112 ,\'%getter_setter_code%\',domain_getter_setter_code) 1113 ,\'%tbl_name_upper_camel%\',tbl_name_upper_camel); 1114 1115 1116 -- controller_res_pojo 1117 1118 SET @template= 1119 \' 1120 %controller_pojo_annotation% 1121 %controller_pojo_fk_pojo_annotation% 1122 public class %domain_tbl_name_upper_camel%xx extends %domain_tbl_name_upper_camel%{ 1123 1124 private static final long serialVersionUID = 1L; 1125 1126 public %domain_tbl_name_upper_camel%xx(){} 1127 1128 public %domain_tbl_name_upper_camel%xx(%common_dao_name% dao,%tbl_name_upper_camel% %tbl_name_lower_camel%) throws Exception{ 1129 super(dao,%tbl_name_lower_camel%); 1130 } 1131 } 1132 \'; 1133 SET @template:=REPLACE(@template,\'%common_dao_name%\',@common_dao_name); 1134 1135 UPDATE all_table SET controller_res_pojo= 1136 REPLACE( 1137 REPLACE( 1138 REPLACE( 1139 REPLACE( 1140 REPLACE( 1141 REPLACE( 1142 REPLACE(@template,\'%controller_pojo_annotation%\',controller_pojo_annotation) 1143 ,\'%controller_pojo_fk_pojo_annotation%\',ifnull(controller_pojo_fk_pojo_annotation,\'\')) 1144 ,\'%domain_tbl_name_upper_camel%\',domain_tbl_name_upper_camel) 1145 ,\'%domain_tbl_name_lower_camel%\',domain_tbl_name_lower_camel) 1146 ,\'%tbl_name_lower_camel%\',tbl_name_lower_camel) 1147 ,\'%col_code%\',domain_col_code) 1148 ,\'%tbl_name_upper_camel%\',tbl_name_upper_camel); 1149 1150 -- 查看执行替换的sql列表 1151 -- SELECT * FROM exe_sql_tbl; 1152 1153 -- 查看外键明细表 1154 -- SELECT * FROM fk_all_col_table; 1155 1156 -- 查看基础明细 1157 -- select * from all_col_table; 1158 1159 -- select * from all_col_code; 1160 1161 -- select * from all_table; 1162 1163 1164 1165 -- DROP TABLE sys_code_mapper; 1166 -- DROP TABLE fk_def; 1167 -- DROP TABLE exe_sql_tbl; 1168 -- DROP TABLE fk_all_col_table; 1169 -- DROP TABLE all_col_table; 1170 -- DROP TABLE all_col_code; 1171 1172 END$ 1173 DELIMITER ; 1174 1175 1176 CALL print_pojo(); 1177 1178 -- select * from all_col_table; 1179 1180 -- select * from all_col_code; 1181 1182 -- select * from all_table; 1183 1184 -- SELECT tbl_name,pojo,domain_pojo FROM all_table; 1185 1186 SELECT pojo FROM all_table; 1187 1188 SELECT 1189 \' 1190 @Target(ElementType.TYPE) 1191 @Retention(RetentionPolicy.RUNTIME) 1192 public @interface FkPojo { 1193 1194 public String[] value() default { }; 1195 } 1196 \' AS \'domain_pojo\' 1197 UNION all 1198 SELECT domain_pojo FROM all_table; 1199 1200 SELECT tbl_name,controller_req_pojo AS \'controller_req_pojo\' FROM all_table; 1201 1202 SELECT tbl_name,controller_res_pojo AS \'controller_res_pojo\' FROM all_table; 1203 1204 1205 1206 DROP TABLE if exists all_col_code; 1207 DROP TABLE if exists all_col_table; 1208 DROP TABLE if exists all_table; 1209 DROP TABLE if exists exe_sql_tbl; 1210 DROP TABLE if exists fk_all_col_table; 1211 DROP TABLE if exists fk_def; 1212 DROP TABLE if exists sys_code_mapper; 1213 DROP TABLE if exists fk_list; 1214 drop table if exists java_code; 1215 drop table if exists java_code_file; 1216 drop PROCEDURE if exists print_pojo; 1217 drop PROCEDURE if exists print_code; 1218 1219 /* 1220 DROP TABLE if exists all_col_code; 1221 DROP TABLE if exists all_col_table; 1222 DROP TABLE if exists all_table; 1223 DROP TABLE if exists exe_sql_tbl; 1224 DROP TABLE if exists fk_all_col_table; 1225 DROP TABLE if exists fk_def; 1226 DROP TABLE if exists sys_code_mapper; 1227 DROP TABLE if exists fk_list; 1228 drop table if exists java_code; 1229 drop table if exists java_code_file; 1230 drop PROCEDURE if exists print_pojo; 1231 drop PROCEDURE if exists print_code; 1232 1233 1234 1235 1236 SELECT * FROM all_col_table; 1237 1238 SELECT * FROM all_col_code; 1239 1240 SELECT * FROM fk_all_col_table; 1241 1242 SELECT * FROM all_table; 1243 */
生成实体类代码
来源：https://www.cnblogs.com/wanglifeng717/p/16324532.html
本站部分图文来源于网络，如有侵权请联系删除。