問題:
專案中目前使用mybatis操作資料庫，使用插件(mybatis-generator)自動生成代碼，對于增改查，使用存盤程序實作了一版本，方便使用，
insert代碼生成器用法:
insert_code_generator( in_var_tbl_name [要插入的表名] )
1 DROP PROCEDURE IF EXISTS insert_code_generator; 2 DELIMITER %% 3 CREATE PROCEDURE insert_code_generator(in_var_tbl_name VARCHAR(200)) 4 label:BEGIN 5 -- ################################################################################################################ 6 -- #################### 支持批量插入：mapper-dao-service生成 select 代碼 ######################### 7 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 8 -- ################################################################################################################ 9 SET group_concat_max_len = 4294967295; 10 11 -- #----------#配置項#-----------------------# 12 -- 表名映射為駝峰形式，tbl_cdk_user_info -> userInfo，默認去除表名的tbl前綴 13 -- SET @noStrInTbl='tbl_cbm'; 14 SET @noStrInTbl='tbl'; 15 16 -- #----------------------------------------# 17 18 19 SET @in_tbl_name=in_var_tbl_name; 20 SET @in_db_name=DATABASE(); 21 if (in_var_tbl_name IS NULL OR in_var_tbl_name='' OR in_var_tbl_name=' ') then 22 SELECT '入參in_var_tbl_name表名不能為空' AS 'error'; 23 leave label; 24 END if; 25 26 27 28 29 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 30 -- ########### 生成參考 31 -- 生成service層物體類以備加Dao：CdkmallGoodsApply 32 SET @objName=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(@in_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'),'_','') ; 33 -- 生成service層物體類以備接Dao: cdkmallGoodsApply 34 SET @objRefName=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(@in_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'),'_','') ; 35 36 SELECT CONCAT_WS('','/* 新建物件*/\r\n',@objName,' ',@objRefName,'= new ',@objName,'();\r\n\r\n/*設定屬性*/\r\n', 37 group_concat( 38 /* cdkmallGoodsApply.setUserUuid(userUuid); */ 39 CONCAT_WS( '' 40 ,CONCAT_WS('','/*',c.column_comment,' | ',c.column_type,' | ',if(c.is_nullable='YES','可空','非空'),if(c.extra='','',CONCAT_WS('',' | ',c.extra)),' | ','默認=',ifnull(c.COLUMN_DEFAULT,'null'),' */ \r\n') 41 ,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(TABLE_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'),'_','') 42 ,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('','.set','_',c.column_name), '_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'),'_','') 43 ,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('','(',c.column_name,');'), '_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'),'_','') 44 ) SEPARATOR '\r\n' 45 ) 46 ) INTO @insert_code 47 FROM 48 information_schema.COLUMNS c 49 WHERE 50 c.table_schema= DATABASE() AND 51 c.TABLE_NAME = @in_tbl_name; 52 53 SET @insert_domain_code=' 54 public void insert@objName(@objName @objRefName){ 55 int insertResult = 0; 56 try { 57 insertResult = @objRefNameDao.insert(@objRefName); 58 } catch (DuplicateKeyException e) { 59 bizLogger.error(" insert @in_tbl_name duplicateKeyException ,@objRefName : " 60 + @objRefName.toString()); 61 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 62 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate error,bizId=" + BizLogUtils.getValueOfBizId()); 63 } 64 if (insertResult != 1) { 65 bizLogger.error("insertResult=" + insertResult + ", insert result != 1 ,@objRefName : " 66 + @objRefName.toString()); 67 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 68 ResultInfo.SYS_INNER_ERROR.getDesc() + " insert fail [insertResult,bizId]="+insertResult+ BizLogUtils.getValueOfBizId()); 69 } 70 } 71 '; 72 73 SELECT REPLACE(@insert_domain_code,'@objName',@objName) INTO @insert_domain_code; 74 SELECT REPLACE(@insert_domain_code,'@objRefName',@objRefName) INTO @insert_domain_code; 75 SELECT REPLACE(@insert_domain_code,'@in_tbl_name',@in_tbl_name) INTO @insert_domain_code; 76 77 78 -- 引入所有的domain 和dao -- 開始 -- 79 SELECT 80 GROUP_CONCAT( 81 CONCAT_WS('','@Autowired\r\n','private ' 82 ,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(t.TABLE_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'),'_',''),'Domain ' 83 ,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(t.TABLE_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'),'_',''),'Domain;\r\n') 84 SEPARATOR '\r\n' 85 ) INTO @autowired_all_domain 86 87 FROM information_schema.`TABLES` t 88 WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOT IN('fixed_col_table'); 89 90 SELECT 91 GROUP_CONCAT( 92 CONCAT_WS('','@Autowired\r\n','private ' 93 ,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(t.TABLE_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'),'_',''),'Dao ' 94 ,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(t.TABLE_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'),'_',''),'Dao;\r\n') 95 SEPARATOR '\r\n' 96 ) INTO @autowired_all_dao 97 98 FROM information_schema.`TABLES` t 99 WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOT IN('fixed_col_table') ; 100 101 102 103 -- ############################################################# 104 -- 以下為動態批量insert需要使用 105 -- ############################################################# 106 107 DROP TABLE if EXISTS fixed_col_table; 108 109 CREATE table if not exists fixed_col_table( 110 col VARCHAR(256) NOT NULL COMMENT '欄位名', 111 col_type VARCHAR(256) COMMENT '欄位型別，static,dynamic,list', 112 data_type VARCHAR(256) COMMENT '資料型別；如：int', 113 jdbc_type VARCHAR(256) COMMENT 'jdbc型別：int->INTEGER,VARCHAR', 114 java_type VARCHAR(256) COMMENT 'java型別：datetime - > date,tinyint -> Byte', 115 col_for_query VARCHAR(256) COMMENT 'idForQuery', 116 col_for_query_jdbc VARCHAR(256) COMMENT '#{idForQueryjdbcType=INTEGER} ', 117 col_comment VARCHAR(512) COMMENT '欄位注釋' 118 ); 119 120 INSERT INTO fixed_col_table (col,data_type) 121 SELECT 122 t1.column_name,t1.data_type 123 FROM 124 information_schema.COLUMNS t1 125 WHERE 126 t1.table_schema= @in_db_name AND 127 t1.TABLE_NAME = @in_tbl_name 128 ; 129 130 -- 轉駝峰 131 UPDATE fixed_col_table SET col_for_query =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'),'_','') 132 ,''); 133 134 135 136 -- 轉換成jdbc型別 137 UPDATE fixed_col_table SET jdbc_type= 138 case data_type 139 when 'datetime' then 'TIMESTAMP' 140 when 'tinyint' then 'TINYINT' 141 when 'bigint' then 'BIGINT' 142 when 'int' then 'INTEGER' 143 when 'float' then 'REAL' 144 when 'varchar' then 'VARCHAR' 145 END; 146 147 -- java型別轉換 148 UPDATE fixed_col_table SET java_type= 149 case data_type 150 when 'datetime' then 'Date' 151 when 'tinyint' then 'Byte' 152 when 'bigint' then 'Long' 153 when 'int' then 'Integer' 154 when 'float' then 'REAL' 155 when 'varchar' then 'String' 156 END; 157 158 159 -- 組成jdbc字串：id --> #{idForQueryjdbcType=INTEGER} 160 UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS('','#{item.',col_for_query,',jdbcType=',jdbc_type,'} '); 161 162 /* 163 col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment 164 name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名稱 | 非空 | | 索引 | idx_face_ip_name(name,status) 165 name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名稱 | 非空 | | 索引 | idx_face_ip_name(name,status) 166 create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 創建時間 | 非空 | 無索引 167 168 */ 169 170 171 -- select * from fixed_col_table; 172 173 SELECT 174 GROUP_CONCAT(t1.column_name SEPARATOR ', ') INTO @allColumnList 175 FROM 176 information_schema.COLUMNS t1 177 WHERE 178 t1.table_schema= @in_db_name AND 179 t1.TABLE_NAME = @in_tbl_name 180 ; 181 182 -- #{id,jdbcType=INTEGER} ,#{name,jdbcType=VARCHAR} ,#{status,jdbcType=TINYINT} ,#{validStartTime,jdbcType=TIMESTAMP} 183 SELECT GROUP_CONCAT(col_for_query_jdbc) INTO @jdbcColumnList 184 FROM fixed_col_table; 185 186 -- select @jdbcColumnList; 187 188 -- id,name,status,valid_start_time,valid_end_time,create_time,last_update_time 189 SELECT GROUP_CONCAT(col) INTO @allColumnList 190 FROM fixed_col_table; 191 192 -- select @allColumnList; 193 194 SET @mapper_code=' 195 @Insert({ 196 "<script> ", 197 "insert into @in_tbl_name ( @allColumnList ) values", 198 "<foreach collection=\'@objRefNameList\' item=\'item\' index=\'index\' open=\'(\' separator=\'),(\' close=\')\'>", 199 "@jdbcColumnList ", 200 "</foreach>", 201 "</script>" 202 }) 203 int batchInsert(@Param("@objRefNameList") List<@objName> @objRefNameList); 204 '; 205 206 SELECT REPLACE(@mapper_code,'@in_tbl_name',@in_tbl_name) INTO @mapper_code; 207 SELECT REPLACE(@mapper_code,'@allColumnList',@allColumnList) INTO @mapper_code; 208 SELECT REPLACE(@mapper_code,'@objRefName',@objRefName) INTO @mapper_code; 209 SELECT REPLACE(@mapper_code,'@jdbcColumnList',@jdbcColumnList) INTO @mapper_code; 210 SELECT REPLACE(@mapper_code,'@objName',@objName) INTO @mapper_code; 211 212 -- SELECT @mapper_code; 213 214 215 SET @dao_code=' 216 public int batchInsert(List<@objName> @objRefNameList){ 217 if(@objRefNameList == null || @objRefNameList.size()==0){ 218 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(),ResultInfo.SYS_INNER_ERROR.getDesc() + ",batch insert empty ,bizId="+BizLogUtils.getValueOfBizId()); 219 } 220 return mapper.batchInsert(@objRefNameList); 221 } 222 '; 223 224 SELECT REPLACE(@dao_code,'@objName',@objName) INTO @dao_code; 225 SELECT REPLACE(@dao_code,'@objRefName',@objRefName) INTO @dao_code; 226 227 -- SELECT @dao_code; 228 229 SET @domain_code=' 230 public void batchInsert@objName(){ 231 int insertResult = 0; 232 List<@objName> @objRefNameList = new ArrayList<>(); 233 234 for(int i=0;i<1;i++){ 235 @insert_code 236 @objRefNameList.add( @objRefName); 237 } 238 239 try { 240 insertResult = @objRefNameDao.batchInsert(@objRefNameList); 241 } catch (DuplicateKeyException e) { 242 bizLogger.error(" batchInsert @in_tbl_name duplicateKeyException "); 243 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 244 ResultInfo.SYS_INNER_ERROR.getDesc() + " duplicate error,bizId=" + BizLogUtils.getValueOfBizId(),e); 245 } 246 if (insertResult != @objRefNameList.size()) { 247 bizLogger.error("insertResult=" + insertResult + ", insert result != 1 "); 248 throw new BusinessException(ResultInfo.SYS_INNER_ERROR.getCode(), 249 ResultInfo.SYS_INNER_ERROR.getDesc() + " insert fail [insertResult,bizId]="+insertResult+ BizLogUtils.getValueOfBizId()); 250 } 251 } 252 '; 253 SELECT REPLACE(@domain_code,'@insert_code',@insert_code) INTO @domain_code; 254 SELECT REPLACE(@domain_code,'@objName',@objName) INTO @domain_code; 255 SELECT REPLACE(@domain_code,'@objRefName',@objRefName) INTO @domain_code; 256 SELECT REPLACE(@domain_code,'@in_tbl_name',@in_tbl_name) INTO @domain_code; 257 258 -- SELECT @domain_code; 259 260 261 262 -- ######################################################################################### 263 -- 列印真正的陳述句 264 -- ######################################################################################### 265 266 SET @insert_sql_help=' 267 高級insert示例： 268 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 269 1：replace into tbl_name(col_name, ...) values(...) 270 如果存在primary or unique相同的記錄，則先洗掉掉，再插入新記錄，REPLACE陳述句會回傳一個數，來指示受影響的行的數目，該數是被洗掉和被插入的行數的和 271 272 2：insert ignore into tbl_name (...) values(...) 273 當插入資料時，如出現錯誤時，如重復資料（PrimaryKey，或者unique索引），將不回傳錯誤，只以警告形式回傳， 274 會忽略資料庫中已經存在的資料，如果資料庫沒有資料，就插入新的資料，如果有資料的話就跳過當前插入的這條資料，這樣就可以保留資料庫中已經存在資料，達到在間隙中插入資料的目的 275 276 3：INSERT INTO tablename(...) VALUES(...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...; 277 INSERT INTO books (...) VALUES (...) ON duplicate KEY UPDATE id = id 等效于 ignore 278 沒有重復的執行插入，重復了執行后面更新陳述句 279 解決問題：以前是找到某條記錄，找到就是更新，找不到就是插入，要寫兩條陳述句，現在一條陳述句就搞定了 280 281 4：INSERT INTO books (...) values (...) WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1) 282 如果沒有id=1的記錄就插入，否則什么都不做 283 '; 284 285 286 SELECT '代碼','用途' LIMIT 0 287 UNION ALL 288 SELECT @insert_code , '物體類set方法' 289 UNION ALL 290 SELECT @insert_domain_code , '單條插入domain層方法' 291 UNION ALL 292 SELECT @mapper_code ,'批量插入mapper層' 293 UNION ALL 294 SELECT @dao_code ,'批量插入dao層' 295 UNION ALL 296 SELECT @domain_code ,'批量插入domain層' 297 UNION ALL 298 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Dao ',@objRefName,'Dao;\r\n') ,'引入dao' 299 UNION ALL 300 SELECT CONCAT_WS('',' int insertResult',' = ',@objRefName,'Dao.insert','(',@objRefName,');') , '呼叫dao' 301 UNION ALL 302 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Domain ',@objRefName,'Domain;\r\n') , '引入domain' 303 UNION ALL 304 SELECT CONCAT_WS('',@objRefName,'Domain.insert','(',@objRefName,');') , '呼叫domain' 305 UNION ALL 306 SELECT @autowired_all_domain , '參考所有domain' 307 UNION all 308 SELECT @autowired_all_dao , '引入所有dao' 309 UNION ALL 310 SELECT ' @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id") \r\n int insert(@objName @objRefName);' , '回傳自增鍵剛插入的新值，放在mapper層insert方法上' 311 UNION ALL 312 SELECT @insert_sql_help ,'高級insert示例' 313 314 ; 315 316 drop table fixed_col_table; 317 318 END %% 319 DELIMITER ;insert_code_generator
select代碼生成器用法:
select_code_generator
( in_var_tbl_name [要查詢的表]
,in_var_return_type [回傳型別，list|obj]
,in_static_col_list [靜態查詢條件,欄位串列,形如："id,name,code"]
,in_dynamic_col_list [動態查詢條件,欄位串列,形如："id,name,code"]
,in_list_col_list [動態集合in查詢條件欄位串列,形如："id,name,code"]
)
1 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 2 DROP PROCEDURE IF EXISTS select_code_generator; 3 DELIMITER %% 4 CREATE PROCEDURE select_code_generator(in_var_tbl_name VARCHAR(200),in_var_return_type VARCHAR(200),in_static_col_list VARCHAR(1024),in_dynamic_col_list VARCHAR(1024),in_list_col_list VARCHAR(1024) ) 5 label:BEGIN 6 -- ################################################################################################################ 7 -- #################### 支持動態sql：mapper-dao-service生成 select 代碼 ############################# 8 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 9 -- ################################################################################################################ 10 11 SET group_concat_max_len = 4294967295; 12 13 -- #----------#配置項#-----------------------# 14 -- 表名映射為駝峰形式，tbl_cdk_user_info -> userInfo，默認去除表名的tbl前綴 15 -- SET @noStrInTbl='tbl_cbm'; 16 SET @noStrInTbl='tbl'; 17 18 -- #----------------------------------------# 19 20 21 SET @in_tbl_name=in_var_tbl_name; 22 SET @in_db_name=DATABASE(); 23 24 25 26 -- #----------#配置項#-----------------------# 27 28 if(in_var_return_type IS NULL OR in_var_return_type='' OR in_var_return_type=' ' OR in_var_return_type='list' OR in_var_return_type='list|obj') then 29 SET @return_type_flag= 'list'; -- 回傳結果配置為obj或者list 30 ELSE 31 SET @return_type_flag='obj'; 32 END if; 33 34 SET @in_tbl_name=in_var_tbl_name; 35 36 37 -- ########### 生成參考 38 -- 生成service層物體類以備加Dao：CdkmallGoodsApply 39 SET @objName=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(@in_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'),'_','') ; 40 -- 生成service層物體類以備接Dao: cdkmallGoodsApply 41 SET @objRefName=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(@in_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'),'_','') ; 42 43 44 -- 回傳值型別 WindIssueCardTask 或者 List<WindIssueCardTask> 45 SELECT if(@return_type_flag='list',CONCAT_WS('',@objRefName,'List'),@objRefName) INTO @return_type_ref; 46 SELECT if(@return_type_flag='list',CONCAT_WS('',' List<',@objName,'> '),@objName) INTO @return_type; 47 48 49 50 DROP TABLE if EXISTS fixed_col_table; 51 52 CREATE table if not exists fixed_col_table( 53 col VARCHAR(256) NOT NULL COMMENT '欄位名', 54 col_type VARCHAR(256) COMMENT '欄位型別，static,dynamic,list', 55 data_type VARCHAR(256) COMMENT '資料型別；如：int', 56 jdbc_type VARCHAR(256) COMMENT 'jdbc型別：int->INTEGER,VARCHAR', 57 java_type VARCHAR(256) COMMENT 'java型別：datetime - > date,tinyint -> Byte', 58 col_for_query VARCHAR(256) COMMENT 'idForQuery', 59 col_for_query_jdbc VARCHAR(256) COMMENT '#{idForQueryjdbcType=INTEGER} ', 60 col_comment VARCHAR(512) COMMENT '欄位注釋' 61 ); 62 63 64 65 66 if(in_static_col_list IS NULL OR in_static_col_list='static_query_col' OR in_static_col_list='' OR in_static_col_list=' ' OR in_static_col_list=' ') then 67 SET in_static_col_list= null; 68 ELSE 69 -- 插入靜態欄位 70 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_static_col_list, ',',CONCAT_WS('',"','static'),('")),"','static')"); 71 PREPARE stmt FROM @exec_sql; 72 EXECUTE stmt; 73 DEALLOCATE PREPARE stmt; 74 END if; 75 76 if(in_dynamic_col_list IS NULL OR in_dynamic_col_list='dynamic_query_col' OR in_dynamic_col_list='' OR in_dynamic_col_list=' ' OR in_dynamic_col_list=' ') then 77 SET in_dynamic_col_list= null; 78 ELSE 79 -- 插入動態欄位 80 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_dynamic_col_list, ',',CONCAT_WS('',"','dynamic'),('")),"','dynamic')"); 81 PREPARE stmt FROM @exec_sql; 82 EXECUTE stmt; 83 DEALLOCATE PREPARE stmt; 84 END if; 85 86 if(in_list_col_list IS NULL OR in_list_col_list='list_query_col' OR in_list_col_list='' OR in_list_col_list=' ' OR in_list_col_list=' ') then 87 SET in_static_col_list= null; 88 ELSE 89 -- 插入list集合欄位 90 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_list_col_list, ',',CONCAT_WS('',"','list'),('")),"','list')"); 91 PREPARE stmt FROM @exec_sql; 92 EXECUTE stmt; 93 DEALLOCATE PREPARE stmt; 94 END if; 95 96 97 /* 列印查詢欄位的駝峰格式：IdAndAmountAndApplyNoAndUuid */ 98 SELECT GROUP_CONCAT( tt SEPARATOR 'And') INTO @byname 99 FROM 100 ( 101 SELECT 102 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'),'_','') AS tt 103 FROM fixed_col_table) t; 104 105 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 106 -- 將col_for_query欄位轉成駝峰：create_time --> createTimeForQuery 107 UPDATE fixed_col_table SET col_for_query =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'),'_','') 108 ,'ForQuery') WHERE col_type='static'; 109 UPDATE fixed_col_table SET col_for_query =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'),'_','') 110 ,'ForDynamicQuery') WHERE col_type='dynamic'; 111 UPDATE fixed_col_table SET col_for_query =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'),'_','') 112 ,'ForQueryList') WHERE col_type='list'; 113 114 115 -- 更新欄位型別id --> int ,name -->varchar 116 UPDATE fixed_col_table SET data_type = 117 ( 118 SELECT t1.data_type 119 FROM 120 information_schema.COLUMNS t1 121 WHERE 122 t1.table_schema= @in_db_name AND 123 t1.TABLE_NAME = @in_tbl_name 124 and t1.column_name =col 125 ); 126 127 -- 更新注釋欄位 --> 創建時間 | 非空 | 128 UPDATE fixed_col_table SET col_comment = 129 ( 130 SELECT CONCAT_WS('',t1.column_comment,' | ',if(t1.is_nullable='YES','可空','非空'),' | ',case t1.COLUMN_KEY when 'PRI' then '主鍵' else '' end) 131 FROM 132 information_schema.COLUMNS t1 133 WHERE 134 t1.table_schema= @in_db_name AND 135 t1.TABLE_NAME = @in_tbl_name 136 and t1.column_name =col 137 ); 138 -- 更新注釋欄位 --> | 唯一鍵 | uni_city_info_name(city_name) 139 UPDATE fixed_col_table SET col_comment = CONCAT_WS('',col_comment, 140 IFNULL( 141 (SELECT 142 CONCAT_WS('',' | ',IF(t.non_unique = 0,'唯一鍵','索引'),' | ', t.index_name,'(', 143 ( 144 SELECT 145 GROUP_CONCAT(tt.column_name ORDER BY tt.seq_in_index) 146 FROM 147 information_schema.statistics tt 148 WHERE tt.table_schema = DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name 149 ) 150 ,')') 151 FROM 152 information_schema.statistics t 153 WHERE t.table_schema = @in_db_name 154 AND t.TABLE_NAME = @in_tbl_name 155 AND t.COLUMN_NAME=col 156 ) 157 ,'無索引') 158 ); 159 160 161 /* 162 SELECT 163 IF(t.non_unique = 0,'唯一鍵','索引') AS 'index_type', 164 t.TABLE_NAME AS 'table_name', 165 t.index_name AS 'index_name', 166 t.COLUMN_NAME AS 'column_name', 167 t.seq_in_index AS 'column_seq', 168 ( 169 SELECT 170 GROUP_CONCAT(tt.column_name ORDER BY tt.seq_in_index) 171 FROM 172 information_schema.statistics tt 173 WHERE tt.table_schema = DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name 174 ) AS cols 175 FROM 176 information_schema.statistics t 177 WHERE t.table_schema = @in_db_name 178 AND t.TABLE_NAME = @in_tbl_name 179 */ 180 181 182 -- 轉換成jdbc型別 183 UPDATE fixed_col_table SET jdbc_type= 184 case data_type 185 when 'datetime' then 'TIMESTAMP' 186 when 'tinyint' then 'TINYINT' 187 when 'bigint' then 'BIGINT' 188 when 'int' then 'INTEGER' 189 when 'varchar' then 'VARCHAR' 190 END; 191 192 -- java型別轉換 193 UPDATE fixed_col_table SET java_type= 194 case data_type 195 when 'datetime' then 'Date' 196 when 'tinyint' then 'Byte' 197 when 'bigint' then 'Long' 198 when 'int' then 'Integer' 199 when 'varchar' then 'String' 200 END 201 WHERE col_type!='list'; 202 203 -- java如果是集合型別 204 UPDATE fixed_col_table SET java_type= 205 case data_type 206 when 'datetime' then 'List<Date>' 207 when 'tinyint' then 'List<Byte>' 208 when 'bigint' then 'List<Long>' 209 when 'int' then 'List<Integer>' 210 when 'varchar' then 'List<String>' 211 END 212 WHERE col_type='list'; 213 214 -- 組成jdbc字串：id --> #{idForQueryjdbcType=INTEGER} 215 UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS('','#{',col_for_query,',jdbcType=',jdbc_type,'} '); 216 217 /* 218 col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment 219 name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名稱 | 非空 | | 索引 | idx_face_ip_name(name,status) 220 name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名稱 | 非空 | | 索引 | idx_face_ip_name(name,status) 221 create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 創建時間 | 非空 | 無索引 222 223 */ 224 225 -- SELECT * from fixed_col_table; 226 227 228 -- 229 /* 列舉所有欄位 @allColumnList 230 id, uuid, create_time, last_update_time, mall_goods_uuid, apply_no, sugar_biz_order_id, payment_pay_no, pay_info, expire_time, trade_type, status, refund_flag, refund_status, refund_accept_time, user_uuid, mall_uuid, amount 231 */ 232 SELECT 233 GROUP_CONCAT(CONCAT('t.',t1.column_name,' as ',t1.column_name) SEPARATOR ', ') INTO @allColumnList 234 FROM 235 information_schema.COLUMNS t1 236 WHERE 237 t1.table_schema= @in_db_name AND 238 t1.TABLE_NAME = @in_tbl_name 239 ; 240 241 242 -- ################################################################################################################ 243 -- ################################ 靜態mapper層 #################################################### 244 -- ################################################################################################################ 245 246 /* 查詢條件欄位 @queryList 247 id=#{idForQuery,jdbcType=BIGINT} and amount=#{amountForQuery,jdbcType=INTEGER} and apply_no=#{applyNoForQuery,jdbcType=VARCHAR} 248 */ 249 250 SELECT 251 CONCAT('"' ,GROUP_CONCAT(CONCAT('and ',col,'=',col_for_query_jdbc) SEPARATOR ' '),'",\r\n') INTO @queryList 252 FROM fixed_col_table WHERE col_type='static'; 253 254 -- SELECT @queryList; 255 256 257 -- ################################################################################################################ 258 -- ################################ 動態if mapper層 ################################################# 259 -- "<if test = 'statusForQuery != null'> and status = #{statusForQuery,jdbcType = DECIMAL} </if> ", 260 -- ################################################################################################################ 261 262 /* 263 "<if test = 'idForQuery != null'> AND id= #{idForQuery,jdbcType=INTEGER} </if>", 264 "<if test = 'nameForQuery != null'> AND name= #{nameForQuery,jdbcType=VARCHAR} </if>", 265 "<if test = 'createTimeForQuery != null'> AND create_time= #{createTimeForQuery,jdbcType=TIMESTAMP} </if>", 266 */ 267 SET @var_if_test_statement="\"<if test = '{1}col_for_query '> AND {2}col= {3}col_for_query_jdbc </if>\",\r\n"; 268 269 SELECT 270 GROUP_CONCAT(replace(replace(REPLACE(@var_if_test_statement,'{1}col_for_query',case data_type when 'varchar' then CONCAT(col_for_query,'!=null and ',col_for_query,'!=''') ELSE CONCAT(col_for_query,'!=null') END), 271 '{3}col_for_query_jdbc',col_for_query_jdbc),'{2}col',col) SEPARATOR '') INTO @dynamicQueryList 272 FROM fixed_col_table WHERE col_type='dynamic'; 273 274 -- SELECT @dynamicQueryList; 275 276 -- SELECT * from fixed_col_table; 277 278 -- ################################################################################################################ 279 -- ################################ 動態list mapper層 ################################################# 280 /* 281 "<if test = 'forumIdList != null and forumIdList.size() > 0'> and id in " , 282 " <foreach collection='forumIdList' item='item' index='index' open='(' separator=',' close=')'> " , 283 " #{item,jdbcType=NUMERIC} " , 284 " </foreach> " , 285 "</if>" , 286 */ 287 -- ################################################################################################################ 288 289 SET @var_list_test_statement="\"<if test = '{1}col_for_query != null and {1}col_for_query.size() > 0'> AND {2}col IN \" , 290 \" <foreach collection='{1}col_for_query' item='item' index='index' open='(' separator=',' close=')'> \" , 291 \" {3}col_for_query_jdbc \" , 292 \" </foreach> \" , 293 \"</if>\" ,"; 294 295 SELECT 296 GROUP_CONCAT(replace(replace(REPLACE(@var_list_test_statement,'{1}col_for_query',col_for_query),'{3}col_for_query_jdbc',replace(col_for_query_jdbc,col_for_query,'item')),'{2}col',col) SEPARATOR '\r\n') INTO @listQueryList 297 FROM fixed_col_table WHERE col_type='list'; 298 299 300 -- SELECT @listQueryList; 301 302 SET @mapper_sql='@Select({ 303 "<script> ", 304 "select @allColumnList ", 305 "from @in_tbl_name t ", 306 "<where> ", 307 @queryList@dynamicQueryList@listQueryList 308 "</where> ", 309 "</script>" 310 })'; 311 312 -- SELECT @queryList; 313 314 SELECT REPLACE(@mapper_sql,'@allColumnList',IFNULL(@allColumnList,'')) INTO @mapper_sql; 315 SELECT REPLACE(@mapper_sql,'@in_tbl_name',IFNULL(@in_tbl_name,'')) INTO @mapper_sql; 316 SELECT REPLACE(@mapper_sql,'@queryList',IFNULL(@queryList,'')) INTO @mapper_sql; 317 SELECT REPLACE(@mapper_sql,'@dynamicQueryList',IFNULL(@dynamicQueryList,'')) INTO @mapper_sql; 318 SELECT REPLACE(@mapper_sql,'@listQueryList',IFNULL(@listQueryList,'')) INTO @mapper_sql; 319 320 -- SELECT @mapper_sql; 321 322 323 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 324 /* 查詢欄位的引數形式串列 @queryparamList 325 @Param("idForQuery") Integer idForQuery , @Param("createTimeForQuery") Date createTimeForQuery , @Param("idForDynamicQuery") Integer idForDynamicQuery , @Param("idForQueryList") List<Integer> idForQueryList 326 */ 327 328 SELECT 329 GROUP_CONCAT(CONCAT('@Param("',col_for_query,'") ',java_type,' ',col_for_query)SEPARATOR ' , ') INTO @queryparamList 330 FROM fixed_col_table ; 331 332 -- SELECT @queryparamList; 333 334 -- mapper層代碼模板 335 SET @mapper_code= 336 '@mapper_sql 337 @return_type selectBy@byname(@queryparamList);'; 338 339 SELECT REPLACE(@mapper_code,'@mapper_sql',IFNULL(@mapper_sql,'')) INTO @mapper_code; 340 SELECT REPLACE(@mapper_code,'@return_type',IFNULL(@return_type,'')) INTO @mapper_code; 341 SELECT REPLACE(@mapper_code,'@byname',IFNULL(@byname,'')) INTO @mapper_code; 342 SELECT REPLACE(@mapper_code,'@queryparamList',IFNULL(@queryparamList,'')) INTO @mapper_code; 343 344 -- SELECT @mapper_code; 345 346 -- ################################################################################################################ 347 -- ################################ dao層需要的 ######################################################## 348 -- ################################################################################################################ 349 350 /* 351 @dao_type_queryparamList : 352 Integer idForQuery,String nameForQuery,Integer idForDynamicQuery,Date createTimeForDynamicQuery,List<Date> lastUpdateTimeForQueryList 353 354 @dao_queryparamList: 355 idForQuery,nameForQuery,idForDynamicQuery,createTimeForDynamicQuery,lastUpdateTimeForQueryList 356 357 */ 358 SELECT 359 GROUP_CONCAT(col_for_query) INTO @dao_queryparamList 360 FROM fixed_col_table ; 361 362 SELECT 363 GROUP_CONCAT(CONCAT(java_type,' ',col_for_query)) INTO @dao_type_queryparamList 364 FROM fixed_col_table ; 365 -- select @dao_type_queryparamList; 366 -- select @dao_queryparamList; 367 368 -- dao層代碼模板 369 SET @dao_code= 370 'public @return_type selectBy@byname(@dao_type_queryparamList){ 371 return mapper.selectBy@byname(@dao_queryparamList); 372 }'; 373 SELECT REPLACE(@dao_code,'@return_type',@return_type) INTO @dao_code; 374 SELECT REPLACE(@dao_code,'@byname',@byname) INTO @dao_code; 375 SELECT REPLACE(@dao_code,'@dao_type_queryparamList',@dao_type_queryparamList) INTO @dao_code; 376 SELECT REPLACE(@dao_code,'@dao_queryparamList',@dao_queryparamList) INTO @dao_code; 377 378 -- SELECT @dao_code; 379 380 -- ################################################################################################################ 381 -- ################################ domain層需要的 ######################################################## 382 -- ################################################################################################################ 383 384 -- 列印注釋：/* uuidForQuery | String | 邏輯主鍵 | 非空 | 唯一鍵 */ 385 SELECT 386 GROUP_CONCAT(concat(col_for_query,' | ',java_type,' | ',col_comment) SEPARATOR '\r\n') INTO @comment_str 387 FROM fixed_col_table ; 388 389 -- select @comment_str; 390 391 -- if(faceIpList == null ||faceIpList.size()==0) 392 SELECT 393 if 394 (@return_type_flag='list' 395 ,CONCAT_WS('','if(',@return_type_ref,' == null ||',@return_type_ref,'.size()==0)') 396 ,CONCAT_WS('','if(',@return_type_ref,' == null)') 397 ) INTO @if_judge; 398 399 -- idForQuery+","+nameForQuery+","+idForDynamicQuery+","+createTimeForDynamicQuery+","+lastUpdateTimeForQueryList 400 select REPLACE(@dao_queryparamList,',','+","+') INTO @if_params_list; 401 402 SET @domain_code= 403 '/* 404 @comment_str 405 */ 406 public @return_type getBy@byname(@dao_type_queryparamList){ 407 @return_type @return_type_ref = @objRefNameDao.selectBy@byname(@dao_queryparamList); 408 @if_judge { 409 bizLogger.warn("select @in_tbl_name result is null or size=0 [@dao_queryparamList] : " + @if_params_list); 410 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 411 } 412 return @return_type_ref; 413 } 414 '; 415 SELECT REPLACE(@domain_code,'@comment_str',@comment_str) INTO @domain_code; 416 SELECT REPLACE(@domain_code,'@return_type_ref',@return_type_ref) INTO @domain_code; 417 SELECT REPLACE(@domain_code,'@return_type',@return_type) INTO @domain_code; 418 SELECT REPLACE(@domain_code,'@byname',@byname) INTO @domain_code; 419 SELECT REPLACE(@domain_code,'@dao_type_queryparamList',@dao_type_queryparamList) INTO @domain_code; 420 SELECT REPLACE(@domain_code,'@dao_queryparamList',@dao_queryparamList) INTO @domain_code; 421 SELECT REPLACE(@domain_code,'@objRefName',@objRefName) INTO @domain_code; 422 SELECT REPLACE(@domain_code,'@if_params_list',@if_params_list) INTO @domain_code; 423 SELECT REPLACE(@domain_code,'@if_judge',@if_judge) INTO @domain_code; 424 SELECT REPLACE(@domain_code,'@in_tbl_name',@in_tbl_name) INTO @domain_code; 425 426 -- SELECT @domain_code; 427 428 -- SELECT * from fixed_col_table; 429 430 431 432 -- 引入所有的domain 和dao -- 開始 -- 433 SELECT 434 GROUP_CONCAT( 435 CONCAT_WS('','@Autowired\r\n','private ' 436 ,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(t.TABLE_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'),'_',''),'Domain ' 437 ,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(t.TABLE_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'),'_',''),'Domain;\r\n') 438 SEPARATOR '\r\n' 439 ) INTO @autowired_all_domain 440 441 FROM information_schema.`TABLES` t 442 WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOT IN('fixed_col_table'); 443 444 SELECT 445 GROUP_CONCAT( 446 CONCAT_WS('','@Autowired\r\n','private ' 447 ,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(t.TABLE_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'),'_',''),'Dao ' 448 ,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(t.TABLE_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'),'_',''),'Dao;\r\n') 449 SEPARATOR '\r\n' 450 ) INTO @autowired_all_dao 451 452 FROM information_schema.`TABLES` t 453 WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOT IN('fixed_col_table') ; 454 455 -- 引入所有的domain 和dao -- 結束 -- 456 457 458 459 -- ################################################################################################################ 460 -- ################################ 列印陳述句 ######################################################## 461 -- ################################################################################################################ 462 463 SET @cacheable_code=' 464 cacheable的使用方式 465 Mapper層： 466 @Select({ 467 "select", 468 " para_value", 469 "from tbl_cdkmall_sys_para where para_key=#{paraKey,jdbcType=VARCHAR}" 470 }) 471 String getSysPara(String paraKey); 472 473 dao層 474 // 查詢系統配置表的固定模板 475 @Cacheable(value = "https://www.cnblogs.com/wanglifeng717/archive/2022/01/24/{FM_spa_cdkmall_getSysPara}", key = "#p0", unless = "#result == null") 476 public String getSysPara(String paraKey) { 477 return mapper.getSysPara(paraKey); 478 } 479 480 @Cacheable(value = "https://www.cnblogs.com/wanglifeng717/archive/2022/01/24/{FM_cdk_parnter_escrow_getSysPara}", key = "#partnerNo+"_"+#paraKey+"_"+#paraIndex", unless = "#result == null") 481 public String getSysPara(String partnerNo,String paraKey,String paraIndex) { 482 return mapper.getSysPara( partnerNo, paraKey, paraIndex); 483 } 484 '; 485 486 SET @dynamic_code_templ=" 487 日期轉換： 488 select STR_TO_DATE('1998-03-12-01-01-01','%Y-%m-%d-%H-%i-%s') ; 字串轉日期 489 select DATE_FORMAT(NOW(),'%Y-%m-%d-%H-%i-%s'); 日期轉字串 490 491 大于小于特殊符號：< lt; | > gt; 492 <if test='startTime!=null and startTime!='''> and a.create_time >= #{startTime,jdbcType = TIMESTAMP}</if> 493 <if test='endTime!=null and endTime!='''> and a.create_time <= #{endTime,jdbcType = TIMESTAMP}</if> 494 495 判斷空及空字串,模糊查詢： 496 <if test='xx!=null and xx!='''> xx like concat('%',#{xx,jdbcType = VARCHAR},'%') 497 498 手動分頁： 499 limit (page-1)*size,size; PAGE>=1,第一頁=1 500 size=10 501 PAGE (PAGE-1)*10 502 1 0 503 2 10 504 3 20 505 "; 506 507 508 SELECT '代碼','功能' LIMIT 0 509 UNION ALL 510 SELECT @mapper_code , 'mapper層方法' 511 UNION all 512 SELECT @dao_code , 'dao層方法' 513 UNION all 514 SELECT @domain_code , 'domain層方法' 515 UNION all 516 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Dao ',@objRefName,'Dao;\r\n') , '引入dao' 517 UNION all 518 SELECT CONCAT_WS('','/*\r\n',@comment_str,'*/','\r\n',@return_type,' ',@return_type_ref,' = ',@objRefName,'Dao.selectBy',@byname,'(',@dao_queryparamList,');') , '呼叫dao' 519 UNION all 520 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Domain ',@objRefName,'Domain;\r\n') , '引入domain' 521 UNION all 522 SELECT CONCAT_WS('','/*\r\n',@comment_str,'*/','\r\n',@return_type,' ',@return_type_ref,' = ',@objRefName,'Domain.selectBy',@byname,'(',@dao_queryparamList,');') , '呼叫domain' 523 UNION all 524 SELECT @autowired_all_domain , '參考所有domain' 525 UNION all 526 SELECT @autowired_all_dao , '引入所有dao' 527 UNION ALL 528 SELECT @dynamic_code_templ ,'動態參考陳述句' 529 UNION ALL 530 SELECT @cacheable_code ,'Cacheable參考使用方法'; 531 532 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 533 DROP TABLE if EXISTS fixed_col_table; 534 535 END %% 536 DELIMITER ;select_code_generator
update代碼生成器用法:
update_code_generator
( in_var_tbl_name [要查詢的表]
,in_var_update_col_list [要更新的欄位串列]
,in_var_dynamic_update_col_list [要動態更新的欄位串列]
,in_static_col_list [靜態查詢條件,欄位串列,形如："id,name,code"]
,in_dynamic_col_list [動態查詢條件,欄位串列,形如："id,name,code"]
,in_list_col_list [動態集合in查詢條件欄位串列,形如："id,name,code"]
)
1 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 2 DROP PROCEDURE IF EXISTS update_code_generator; 3 DELIMITER %% 4 CREATE PROCEDURE update_code_generator(in_var_tbl_name VARCHAR(200),in_var_update_col_list VARCHAR(1024),in_var_dynamic_update_col_list VARCHAR(1024),in_static_col_list VARCHAR(1024),in_dynamic_col_list VARCHAR(1024),in_list_col_list VARCHAR(1024) ) 5 6 label:BEGIN 7 -- ################################################################################################################ 8 -- #################### 支持動態sql : ######################################### 9 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 10 -- ################################################################################################################ 11 12 SET group_concat_max_len = 4294967295; 13 -- #----------#配置項#-----------------------# 14 15 -- 表名映射為駝峰形式，tbl_cdk_user_info -> userInfo，默認去除表名的tbl前綴 16 -- SET @noStrInTbl='tbl_cbm'; 17 SET @noStrInTbl='tbl'; 18 19 -- ------------------------------- 20 21 22 23 SET @in_tbl_name=in_var_tbl_name; 24 SET @in_db_name=DATABASE(); 25 26 27 28 -- ########### 生成參考 29 -- 生成service層物體類以備加Dao：CdkmallGoodsApply 30 SET @objName=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(@in_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'),'_','') ; 31 -- 生成service層物體類以備接Dao: cdkmallGoodsApply 32 SET @objRefName=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(@in_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'),'_','') ; 33 34 35 36 37 DROP TABLE if EXISTS fixed_col_table; 38 39 CREATE table if not exists fixed_col_table( 40 col VARCHAR(256) NOT NULL COMMENT '欄位名', 41 col_type VARCHAR(256) COMMENT '欄位型別，static,dynamic,list,update_col,dynamic_update_col', 42 data_type VARCHAR(256) COMMENT '資料型別；如：int', 43 jdbc_type VARCHAR(256) COMMENT 'jdbc型別：int->INTEGER,VARCHAR', 44 java_type VARCHAR(256) COMMENT 'java型別：datetime - > date,tinyint -> Byte', 45 col_for_query VARCHAR(256) COMMENT 'idForQuery', 46 col_for_query_jdbc VARCHAR(256) COMMENT '#{idForQueryjdbcType=INTEGER} ', 47 col_comment VARCHAR(512) COMMENT '欄位注釋' 48 ); 49 50 51 52 53 -- 插入引數中的欄位值 54 if(in_static_col_list IS NULL OR in_static_col_list='static_query_col' OR in_static_col_list='' OR in_static_col_list=' ' OR in_static_col_list=' ') then 55 SET in_static_col_list= null; 56 ELSE 57 -- 插入靜態欄位 58 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_static_col_list, ',',CONCAT_WS('',"','static'),('")),"','static')"); 59 PREPARE stmt FROM @exec_sql; 60 EXECUTE stmt; 61 DEALLOCATE PREPARE stmt; 62 END if; 63 64 if(in_dynamic_col_list IS NULL OR in_dynamic_col_list='dynamic_query_col' OR in_dynamic_col_list='' OR in_dynamic_col_list=' ' OR in_dynamic_col_list=' ') then 65 SET in_dynamic_col_list= null; 66 ELSE 67 -- 插入動態欄位 68 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_dynamic_col_list, ',',CONCAT_WS('',"','dynamic'),('")),"','dynamic')"); 69 PREPARE stmt FROM @exec_sql; 70 EXECUTE stmt; 71 DEALLOCATE PREPARE stmt; 72 END if; 73 74 if(in_list_col_list IS NULL OR in_list_col_list='list_query_col' OR in_list_col_list='' OR in_list_col_list=' ' OR in_list_col_list=' ') then 75 SET in_list_col_list= null; 76 ELSE 77 -- 插入list集合欄位 78 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_list_col_list, ',',CONCAT_WS('',"','list'),('")),"','list')"); 79 PREPARE stmt FROM @exec_sql; 80 EXECUTE stmt; 81 DEALLOCATE PREPARE stmt; 82 END if; 83 84 if(in_var_update_col_list IS NULL OR in_var_update_col_list='update_col' OR in_var_update_col_list='' OR in_var_update_col_list=' ' OR in_var_update_col_list=' ') then 85 SELECT '要更新的欄位不能為空' AS 'error'; 86 leave label; 87 ELSE 88 -- 插入list集合欄位 89 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_var_update_col_list, ',',CONCAT_WS('',"','update'),('")),"','update')"); 90 PREPARE stmt FROM @exec_sql; 91 EXECUTE stmt; 92 DEALLOCATE PREPARE stmt; 93 END if; 94 95 if(in_var_dynamic_update_col_list IS NULL OR in_var_dynamic_update_col_list='dynamic_update_col' OR in_var_dynamic_update_col_list='' OR in_var_dynamic_update_col_list=' ' OR in_var_dynamic_update_col_list=' ') then 96 SET in_var_dynamic_update_col_list=NULL; 97 ELSE 98 -- 插入list集合欄位 99 SET @exec_sql = CONCAT_WS('',"INSERT ignore INTO fixed_col_table(col,col_type) values ('",REPLACE(in_var_dynamic_update_col_list, ',',CONCAT_WS('',"','dynamic_update'),('")),"','dynamic_update')"); 100 PREPARE stmt FROM @exec_sql; 101 EXECUTE stmt; 102 DEALLOCATE PREPARE stmt; 103 END if; 104 105 -- select @exec_sql; 106 107 /* 列印查詢欄位的駝峰格式：IdAndAmountAndApplyNoAndUuid */ 108 SELECT CONCAT('By',GROUP_CONCAT( tt SEPARATOR 'And')) INTO @byname 109 FROM 110 ( 111 SELECT 112 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'),'_','') AS tt 113 FROM fixed_col_table where col_type IN ('static','dynamic','list')) t; 114 115 116 117 -- 將col_for_query欄位轉成駝峰：create_time --> createTimeForQuery 118 UPDATE fixed_col_table SET col_for_query =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'轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420444.html
標籤：其他
下一篇：省去跨表聯查與注釋查詢的存盤程序