資料庫表之間關系：
- 一對一 （可以看做一對多的特例）
- 一對多
- 多對多
下圖將涵蓋了所有關系，
根據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 = "https://www.cnblogs.com/wanglifeng717/archive/2022/05/29/xx",access = JsonProperty.Access.WRITE_ONLY) 1026 @JsonProperty(value = "https://www.cnblogs.com/wanglifeng717/archive/2022/05/29/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.uj5u.com/houduan/482726.html
標籤：其他