基于mybatis的java代码生成存储过程
问题:
项目中目前使用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 ;
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 = "{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 = "{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 ;
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'), '_h', 'H'), '_g', 'G'), '_f', 'F'), '_e', 'E'), '_d', 'D'), '_c', 'C'), '_b', 'B'), '_a', 'A'),'_','') 119 ,'ForQuery') WHERE col_type='static'; 120 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'),'_','') 121 ,'ForDynamicQuery') WHERE col_type='dynamic'; 122 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'),'_','') 123 ,'ForQueryList') WHERE col_type='list'; 124 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'),'_','') 125 ,'ForUpdate') WHERE col_type='update'; 126 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'),'_','') 127 ,'ForDynamicUpdate') WHERE col_type='dynamic_update'; 128 129 130 -- 更新字段类型id --> int ,name -->varchar 131 UPDATE fixed_col_table SET data_type = 132 ( 133 SELECT t1.data_type 134 FROM 135 information_schema.COLUMNS t1 136 WHERE 137 t1.table_schema= @in_db_name AND 138 t1.TABLE_NAME = @in_tbl_name 139 and t1.column_name =col 140 ); 141 -- SELECT * FROM fixed_col_table; 142 -- 更新注释字段 --> 创建时间 | 非空 | 143 UPDATE fixed_col_table SET col_comment = 144 ( 145 SELECT CONCAT_WS('',t1.column_comment,' | ',if(t1.is_nullable='YES','可空','非空'),' | ',case t1.COLUMN_KEY when 'PRI' then '主键' else '' end) 146 FROM 147 information_schema.COLUMNS t1 148 WHERE 149 t1.table_schema= @in_db_name AND 150 t1.TABLE_NAME = @in_tbl_name 151 and t1.column_name =col 152 ); 153 -- 更新注释字段 --> | 唯一键 | uni_city_info_name(city_name) 154 UPDATE fixed_col_table SET col_comment = CONCAT_WS('',col_comment, 155 IFNULL( 156 (SELECT 157 CONCAT_WS('',' | ',IF(t.non_unique = 0,'唯一键','索引'),' | ', t.index_name,'(', 158 ( 159 SELECT 160 GROUP_CONCAT(tt.column_name ORDER BY tt.seq_in_index) 161 FROM 162 information_schema.statistics tt 163 WHERE tt.table_schema = DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name 164 ) 165 ,')') 166 FROM 167 information_schema.statistics t 168 WHERE t.table_schema = @in_db_name 169 AND t.TABLE_NAME = @in_tbl_name 170 AND t.COLUMN_NAME=col 171 ) 172 ,'无索引') 173 ); 174 175 176 /* 177 SELECT 178 IF(t.non_unique = 0,'唯一键','索引') AS 'index_type', 179 t.TABLE_NAME AS 'table_name', 180 t.index_name AS 'index_name', 181 t.COLUMN_NAME AS 'column_name', 182 t.seq_in_index AS 'column_seq', 183 ( 184 SELECT 185 GROUP_CONCAT(tt.column_name ORDER BY tt.seq_in_index) 186 FROM 187 information_schema.statistics tt 188 WHERE tt.table_schema = DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name 189 ) AS cols 190 FROM 191 information_schema.statistics t 192 WHERE t.table_schema = @in_db_name 193 AND t.TABLE_NAME = @in_tbl_name 194 */ 195 196 197 -- 转换成jdbc类型 198 UPDATE fixed_col_table SET jdbc_type= 199 case data_type 200 when 'datetime' then 'TIMESTAMP' 201 when 'tinyint' then 'TINYINT' 202 when 'bigint' then 'BIGINT' 203 when 'int' then 'INTEGER' 204 when 'float' then 'REAL' 205 when 'varchar' then 'VARCHAR' 206 END; 207 208 -- java类型转换 209 UPDATE fixed_col_table SET java_type= 210 case data_type 211 when 'datetime' then 'Date' 212 when 'tinyint' then 'Byte' 213 when 'bigint' then 'Long' 214 when 'int' then 'Integer' 215 when 'float' then 'REAL' 216 when 'varchar' then 'String' 217 END 218 WHERE col_type!='list'; 219 220 -- java如果是集合类型 221 UPDATE fixed_col_table SET java_type= 222 case data_type 223 when 'datetime' then 'List<Date>' 224 when 'tinyint' then 'List<Byte>' 225 when 'bigint' then 'List<Long>' 226 when 'int' then 'List<Integer>' 227 when 'float' then 'List<REAL>' 228 when 'varchar' then 'List<String>' 229 END 230 WHERE col_type='list'; 231 232 -- 组成jdbc字符串：id --> #{idForQueryjdbcType=INTEGER} 233 UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS('','#{',col_for_query,',jdbcType=',jdbc_type,'} '); 234 235 /* 236 col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment 237 name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status) 238 name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status) 239 create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 创建时间 | 非空 | 无索引 240 241 */ 242 243 -- SELECT * from fixed_col_table; 244 245 246 -- 247 /* 列举所有字段 @allColumnList 248 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 249 */ 250 SELECT 251 GROUP_CONCAT(t1.column_name SEPARATOR ', ') INTO @allColumnList 252 FROM 253 information_schema.COLUMNS t1 254 WHERE 255 t1.table_schema= @in_db_name AND 256 t1.TABLE_NAME = @in_tbl_name 257 ; 258 259 260 -- ################################################################################################################ 261 -- ################################ 静态mapper层 #################################################### 262 -- ################################################################################################################ 263 264 /* 查询条件字段 @queryList 265 id=#{idForQuery,jdbcType=BIGINT} and amount=#{amountForQuery,jdbcType=INTEGER} and apply_no=#{applyNoForQuery,jdbcType=VARCHAR} 266 */ 267 268 SELECT 269 CONCAT('"' ,GROUP_CONCAT(CONCAT('and ',col,'=',col_for_query_jdbc) SEPARATOR ' '),'",\r\n') INTO @queryList 270 FROM fixed_col_table WHERE col_type='static'; 271 272 -- SELECT @queryList; 273 274 /* 更新字段 @updateList 275 " set id=#{idForQuery,jdbcType=BIGINT} , amount=#{amountForQuery,jdbcType=INTEGER} , apply_no=#{applyNoForQuery,jdbcType=VARCHAR} " 276 */ 277 SELECT 278 CONCAT('"set ' ,GROUP_CONCAT(CONCAT('',col,'=',col_for_query_jdbc) SEPARATOR ' ,'),'",\r\n') INTO @update_list 279 FROM fixed_col_table WHERE col_type='update'; 280 281 -- 动态update语句 282 -- "<if test = 'idForDynamicUpdate != null'> , id= #{idForQuery,jdbcType=INTEGER} </if>", 283 SET @var_if_test_statement="\"<if test = '{1}col_for_query != null'> , {2}col= {3}col_for_query_jdbc </if>\","; 284 285 SELECT 286 GROUP_CONCAT(replace(replace(REPLACE(@var_if_test_statement,'{1}col_for_query',col_for_query),'{3}col_for_query_jdbc',col_for_query_jdbc),'{2}col',col) SEPARATOR '\r\n') INTO @update_dynamic_list 287 FROM fixed_col_table WHERE col_type='dynamic_update'; 288 289 SELECT CONCAT_WS('',@update_list,@update_dynamic_list) INTO @update_list; 290 291 -- SELECT @updateList; 292 -- ################################################################################################################ 293 -- ################################ 动态if mapper层 ################################################# 294 -- "<if test = 'statusForQuery != null'> and status = #{statusForQuery,jdbcType = DECIMAL} </if> ", 295 -- ################################################################################################################ 296 297 /* 298 "<if test = 'idForQuery != null'> AND id= #{idForQuery,jdbcType=INTEGER} </if>", 299 "<if test = 'nameForQuery != null'> AND name= #{nameForQuery,jdbcType=VARCHAR} </if>", 300 "<if test = 'createTimeForQuery != null'> AND create_time= #{createTimeForQuery,jdbcType=TIMESTAMP} </if>", 301 */ 302 SET @var_if_test_statement="\"<if test = '{1}col_for_query '> AND {2}col= {3}col_for_query_jdbc </if>\","; 303 304 SELECT 305 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), 306 '{3}col_for_query_jdbc',col_for_query_jdbc),'{2}col',col) SEPARATOR '\r\n') INTO @dynamicQueryList 307 FROM fixed_col_table WHERE col_type='dynamic'; 308 309 -- SELECT @dynamicQueryList; 310 311 -- SELECT * from fixed_col_table; 312 313 314 315 -- ################################################################################################################ 316 -- ################################ 动态list mapper层 ################################################# 317 /* 318 "<if test = 'forumIdList != null and forumIdList.size() > 0'> and id in " , 319 " <foreach collection='forumIdList' item='item' index='index' open='(' separator=',' close=')'> " , 320 " #{item,jdbcType=NUMERIC} " , 321 " </foreach> " , 322 "</if>" , 323 */ 324 -- ################################################################################################################ 325 326 SET @var_list_test_statement="\"<if test = '{1}col_for_query != null and {1}col_for_query.size() > 0'> AND {2}col IN \" , 327 \" <foreach collection='{1}col_for_query' item='item' index='index' open='(' separator=',' close=')'> \" , 328 \" {3}col_for_query_jdbc \" , 329 \" </foreach> \" , 330 \"</if>\" ,"; 331 332 SELECT 333 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 334 FROM fixed_col_table WHERE col_type='list'; 335 336 337 -- SELECT @listQueryList; 338 339 340 341 342 SET @mapper_sql='@Update({ 343 "<script> ", 344 "update @in_tbl_name ", 345 @update_list 346 "<where>", 347 @queryList@dynamicQueryList 348 @listQueryList 349 "</where>", 350 "</script>" 351 })'; 352 353 -- SELECT @queryList; 354 355 SELECT REPLACE(@mapper_sql,'@update_list',IFNULL(@update_list,'')) INTO @mapper_sql; 356 SELECT REPLACE(@mapper_sql,'@in_tbl_name',IFNULL(@in_tbl_name,'')) INTO @mapper_sql; 357 SELECT REPLACE(@mapper_sql,'@queryList',IFNULL(@queryList,'')) INTO @mapper_sql; 358 SELECT REPLACE(@mapper_sql,'@dynamicQueryList',IFNULL(@dynamicQueryList,'')) INTO @mapper_sql; 359 SELECT REPLACE(@mapper_sql,'@listQueryList',IFNULL(@listQueryList,'')) INTO @mapper_sql; 360 361 -- SELECT @mapper_sql; 362 363 364 365 /* 查询字段的参数形式列表 @queryparamList 366 @Param("idForQuery") Integer idForQuery , @Param("createTimeForQuery") Date createTimeForQuery , @Param("idForDynamicQuery") Integer idForDynamicQuery , @Param("idForQueryList") List<Integer> idForQueryList 367 */ 368 369 SELECT 370 GROUP_CONCAT(CONCAT('@Param("',col_for_query,'") ',java_type,' ',col_for_query)SEPARATOR ' , ') INTO @queryparamList 371 FROM fixed_col_table ; 372 373 -- SELECT @queryparamList; 374 375 -- mapper层代码模板 376 SET @mapper_code= 377 '@mapper_sql 378 int update@byname(@queryparamList);'; 379 380 SELECT REPLACE(@mapper_code,'@mapper_sql',IFNULL(@mapper_sql,'')) INTO @mapper_code; 381 SELECT REPLACE(@mapper_code,'@byname',IFNULL(@byname,'')) INTO @mapper_code; 382 SELECT REPLACE(@mapper_code,'@queryparamList',IFNULL(@queryparamList,'')) INTO @mapper_code; 383 384 -- SELECT @mapper_code; 385 386 -- ################################################################################################################ 387 -- ################################ dao层需要的 ######################################################## 388 -- ################################################################################################################ 389 390 /* 391 @dao_type_queryparamList : 392 Integer idForQuery,String nameForQuery,Integer idForDynamicQuery,Date createTimeForDynamicQuery,List<Date> lastUpdateTimeForQueryList 393 394 @dao_queryparamList: 395 idForQuery,nameForQuery,idForDynamicQuery,createTimeForDynamicQuery,lastUpdateTimeForQueryList 396 397 */ 398 SELECT 399 GROUP_CONCAT(col_for_query) INTO @dao_queryparamList 400 FROM fixed_col_table ; 401 402 SELECT 403 GROUP_CONCAT(CONCAT(java_type,' ',col_for_query)) INTO @dao_type_queryparamList 404 FROM fixed_col_table ; 405 -- select @dao_type_queryparamList; 406 -- select @dao_queryparamList; 407 408 -- dao层代码模板 409 SET @dao_code= 410 'public int update@byname(@dao_type_queryparamList){ 411 return mapper.update@byname(@dao_queryparamList); 412 }'; 413 414 SELECT REPLACE(@dao_code,'@byname',@byname) INTO @dao_code; 415 SELECT REPLACE(@dao_code,'@dao_type_queryparamList',@dao_type_queryparamList) INTO @dao_code; 416 SELECT REPLACE(@dao_code,'@dao_queryparamList',@dao_queryparamList) INTO @dao_code; 417 418 -- SELECT @dao_code; 419 420 -- ################################################################################################################ 421 -- ################################ domain层需要的 ######################################################## 422 -- ################################################################################################################ 423 424 -- 打印注释：/* uuidForQuery | String | 逻辑主键 | 非空 | 唯一键 */ 425 SELECT 426 GROUP_CONCAT(concat(col_for_query,' | ',java_type,' | ',col_comment) SEPARATOR '\r\n') INTO @comment_str 427 FROM fixed_col_table ; 428 429 -- select @comment_str; 430 431 432 -- idForQuery+","+nameForQuery+","+idForDynamicQuery+","+createTimeForDynamicQuery+","+lastUpdateTimeForQueryList 433 select REPLACE(@dao_queryparamList,',','+","+') INTO @if_params_list; 434 435 SET @domain_code= 436 '/* 437 @comment_str 438 */ 439 public void update@byname(@dao_type_queryparamList){ 440 int updateResult = @objRefNameDao.update@byname(@dao_queryparamList); 441 if (updateResult!=1) { 442 bizLogger.warn("update @in_tbl_name result result !=1 [updateResult, @dao_queryparamList] : "+updateResult+","+ @if_params_list); 443 throw new BusinessException(ResultInfo.NO_DATA.getCode(),ResultInfo.NO_DATA.getDesc() + " ,bizId="+BizLogUtils.getValueOfBizId()); 444 } 445 } 446 '; 447 SELECT REPLACE(@domain_code,'@comment_str',@comment_str) INTO @domain_code; 448 SELECT REPLACE(@domain_code,'@objRefName',@objRefName) INTO @domain_code; 449 SELECT REPLACE(@domain_code,'@byname',@byname) INTO @domain_code; 450 SELECT REPLACE(@domain_code,'@dao_type_queryparamList',@dao_type_queryparamList) INTO @domain_code; 451 SELECT REPLACE(@domain_code,'@dao_queryparamList',@dao_queryparamList) INTO @domain_code; 452 SELECT REPLACE(@domain_code,'@if_params_list',@if_params_list) INTO @domain_code; 453 SELECT REPLACE(@domain_code,'@in_tbl_name',@in_tbl_name) INTO @domain_code; 454 455 -- SELECT @domain_code; 456 457 -- SELECT * from fixed_col_table; 458 459 460 461 -- 引入所有的domain 和dao -- 开始 -- 462 SELECT 463 GROUP_CONCAT( 464 CONCAT_WS('','@Autowired\r\n','private ' 465 ,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 ' 466 ,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') 467 SEPARATOR '\r\n' 468 ) INTO @autowired_all_domain 469 470 FROM information_schema.`TABLES` t 471 WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOT IN('fixed_col_table'); 472 473 SELECT 474 GROUP_CONCAT( 475 CONCAT_WS('','@Autowired\r\n','private ' 476 ,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 ' 477 ,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') 478 SEPARATOR '\r\n' 479 ) INTO @autowired_all_dao 480 481 FROM information_schema.`TABLES` t 482 WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOT IN('fixed_col_table') ; 483 484 -- 引入所有的domain 和dao -- 结束 -- 485 486 -- ################################################################################################################ 487 -- ################################ 对象更新语句 ######################################################## 488 -- ################################################################################################################ 489 -- SET @in_tbl_name='tbl_ams_user_behavior'; 490 491 DROP TABLE if EXISTS all_col_table; 492 493 CREATE table if not exists all_col_table( 494 col VARCHAR(256) NOT NULL COMMENT '字段名', 495 col_type VARCHAR(256) COMMENT '字段类型，static,dynamic,list', 496 data_type VARCHAR(256) COMMENT '数据类型；如：int', 497 jdbc_type VARCHAR(256) COMMENT 'jdbc类型：int->INTEGER,VARCHAR', 498 java_type VARCHAR(256) COMMENT 'java类型：datetime - > date,tinyint -> Byte', 499 col_for_query VARCHAR(256) COMMENT 'idForQuery', 500 col_for_query_jdbc VARCHAR(256) COMMENT '#{idForQueryjdbcType=INTEGER} ', 501 col_comment VARCHAR(512) COMMENT '字段注释' 502 ); 503 504 INSERT INTO all_col_table(col) 505 SELECT 506 t1.column_name 507 FROM 508 information_schema.COLUMNS t1 509 WHERE 510 t1.table_schema= DATABASE() AND 511 t1.TABLE_NAME = @in_tbl_name 512 ; 513 -- 转换成驼峰格式 514 UPDATE all_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'),'_','') 515 ,''); 516 517 518 -- 更新字段类型id --> int ,name -->varchar 519 UPDATE all_col_table SET data_type = 520 ( 521 SELECT t1.data_type 522 FROM 523 information_schema.COLUMNS t1 524 WHERE 525 t1.table_schema= DATABASE() AND 526 t1.TABLE_NAME = @in_tbl_name 527 and t1.column_name =col 528 ); 529 530 531 532 -- 转换成jdbc类型 533 UPDATE all_col_table SET jdbc_type= 534 case data_type 535 when 'datetime' then 'TIMESTAMP' 536 when 'tinyint' then 'TINYINT' 537 when 'bigint' then 'BIGINT' 538 when 'int' then 'INTEGER' 539 when 'float' then 'REAL' 540 when 'varchar' then 'VARCHAR' 541 END; 542 543 544 545 UPDATE all_col_table SET col_for_query_jdbc=CONCAT_WS('',col,'=#{obj.',col_for_query,',jdbcType=',jdbc_type,'} '); 546 547 /* 548 "<if test = 'idForQuery != null'> AND id= #{idForQuery,jdbcType=INTEGER} </if>", 549 "<if test = 'nameForQuery != null'> AND name= #{nameForQuery,jdbcType=VARCHAR} </if>", 550 "<if test = 'createTimeForQuery != null'> AND create_time= #{createTimeForQuery,jdbcType=TIMESTAMP} </if>", 551 */ 552 SET @var_if_test_statement="\"<if test = 'obj.{1}col_for_query '> {3}col_for_query_jdbc , </if>\","; 553 554 SELECT 555 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 obj.',col_for_query,'!=''') ELSE CONCAT(col_for_query,'!=null') END), 556 '{3}col_for_query_jdbc',col_for_query_jdbc),'{2}col',col) SEPARATOR '\r\n') INTO @objUpdateAllCol 557 FROM all_col_table ; 558 559 -- select @objUpdateAllCol; 560 561 SET @obj_mapper_sql='@Update({ 562 "<script> ", 563 "update @in_tbl_name ", 564 "<set>", 565 @objUpdateAllCol 566 "</set>", 567 "<where>", 568 @queryList@dynamicQueryList 569 @listQueryList 570 "</where>", 571 "</script>" 572 })'; 573 574 SELECT REPLACE(@obj_mapper_sql,'@objUpdateAllCol',IFNULL(@objUpdateAllCol,'')) INTO @obj_mapper_sql; 575 SELECT REPLACE(@obj_mapper_sql,'@in_tbl_name',IFNULL(@in_tbl_name,'')) INTO @obj_mapper_sql; 576 SELECT REPLACE(@obj_mapper_sql,'@queryList',IFNULL(@queryList,'')) INTO @obj_mapper_sql; 577 SELECT REPLACE(@obj_mapper_sql,'@dynamicQueryList',IFNULL(@dynamicQueryList,'')) INTO @obj_mapper_sql; 578 SELECT REPLACE(@obj_mapper_sql,'@listQueryList',IFNULL(@listQueryList,'')) INTO @obj_mapper_sql; 579 580 -- select @obj_mapper_sql; 581 -- obj 582 SELECT 583 GROUP_CONCAT(CONCAT('@Param("',col_for_query,'") ',java_type,' ',col_for_query)SEPARATOR ' , ') INTO @objQueryparamList 584 FROM fixed_col_table WHERE col_type IN ('static','dynamic','list'); 585 586 -- mapper层代码模板 587 SET @obj_mapper_code= 588 '@obj_mapper_sql 589 int update@byname(@objQueryparamList,@Param("obj") @objName @objRefName);'; 590 591 SELECT REPLACE(@obj_mapper_code,'@obj_mapper_sql',IFNULL(@obj_mapper_sql,'')) INTO @obj_mapper_code; 592 SELECT REPLACE(@obj_mapper_code,'@byname',IFNULL(@byname,'')) INTO @obj_mapper_code; 593 SELECT REPLACE(@obj_mapper_code,'@objQueryparamList',IFNULL(@objQueryparamList,'')) INTO @obj_mapper_code; 594 SELECT REPLACE(@obj_mapper_code,'@objName',IFNULL(@objName,'')) INTO @obj_mapper_code; 595 SELECT REPLACE(@obj_mapper_code,'@objRefName',IFNULL(@objRefName,'')) INTO @obj_mapper_code; 596 597 598 -- dao层代码模板 599 /* 600 @dao_type_queryparamList : 601 Integer idForQuery,String nameForQuery,Integer idForDynamicQuery,Date createTimeForDynamicQuery,List<Date> lastUpdateTimeForQueryList 602 603 @dao_queryparamList: 604 idForQuery,nameForQuery,idForDynamicQuery,createTimeForDynamicQuery,lastUpdateTimeForQueryList 605 606 */ 607 SELECT 608 GROUP_CONCAT(col_for_query) INTO @obj_dao_queryparamList 609 FROM fixed_col_table WHERE col_type IN ('static','dynamic','list'); 610 611 SELECT 612 GROUP_CONCAT(CONCAT(java_type,' ',col_for_query)) INTO @obj_dao_type_queryparamList 613 FROM fixed_col_table WHERE col_type IN ('static','dynamic','list'); 614 615 SET @obj_dao_code= 616 'public int update@byname(@obj_dao_type_queryparamList,@objName @objRefName){ 617 return mapper.update@byname(@obj_dao_queryparamList,@objRefName); 618 }'; 619 620 SELECT REPLACE(@obj_dao_code,'@byname',@byname) INTO @obj_dao_code; 621 SELECT REPLACE(@obj_dao_code,'@obj_dao_type_queryparamList',@obj_dao_type_queryparamList) INTO @obj_dao_code; 622 SELECT REPLACE(@obj_dao_code,'@obj_dao_queryparamList',@obj_dao_queryparamList) INTO @obj_dao_code; 623 SELECT REPLACE(@obj_dao_code,'@objName',IFNULL(@objName,'')) INTO @obj_dao_code; 624 SELECT REPLACE(@obj_dao_code,'@objRefName',IFNULL(@objRefName,'')) INTO @obj_dao_code; 625 626 627 -- ################################################################################################################ 628 -- ################################ 实体类，getter,setter方法 ########################################### 629 -- ################################################################################################################ 630 631 SELECT CONCAT_WS('','/* 新建对象*/\r\n',@objName,' ',@objRefName,'= new ',@objName,'();\r\n\r\n/*设置属性*/\r\n', 632 group_concat( 633 /* cdkmallGoodsApply.setUserUuid(userUuid); */ 634 CONCAT_WS( '' 635 ,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') 636 ,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'),'_','') 637 ,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'),'_','') 638 ,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'),'_','') 639 ) SEPARATOR '\r\n' 640 ) 641 ) into @insert_code 642 FROM 643 information_schema.COLUMNS c 644 WHERE 645 c.table_schema= DATABASE() AND 646 c.TABLE_NAME = @in_tbl_name; 647 648 649 650 651 652 653 -- ################################################################################################################ 654 -- ################################ 打印语句 ######################################################## 655 -- ################################################################################################################ 656 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 657 658 select '代码','用途' LIMIT 0 659 union all 660 SELECT @mapper_code , 'mapper层方法' 661 UNION ALL 662 SELECT @dao_code , 'dao层方法' 663 union all 664 SELECT @domain_code , 'domain层方法' 665 union ALL 666 SELECT @obj_mapper_code ,'用对象式更新mapper层方法' 667 union ALL 668 SELECT @obj_dao_code ,'用对象式更新dao层方法' 669 UNION ALL 670 SELECT @insert_code ,'实体类getter|setter方法' 671 union ALL 672 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Dao ',@objRefName,'Dao;\r\n') , '引入dao' 673 union all 674 SELECT CONCAT_WS('','/*\r\n',@comment_str,'*/','\r\n',' int updateResult',' = ',@objRefName,'Dao.update',@byname,'(',@dao_queryparamList,');') , '调用dao' 675 union all 676 SELECT CONCAT_WS('','@Autowired\r\n','private ',@objName,'Domain ',@objRefName,'Domain;\r\n') , '引入domain' 677 union all 678 SELECT CONCAT_WS('','/*\r\n',@comment_str,'*/','\r\n',' int updateResult',' = ',@objRefName,'Domain.update',@byname,'(',@dao_queryparamList,');') , '调用domain' 679 union all 680 SELECT @autowired_all_domain , '引用所有domain' 681 union all 682 SELECT @autowired_all_dao , '引入所有dao'; 683 684 DROP TABLE if EXISTS fixed_col_table; 685 686 DROP TABLE if EXISTS all_col_table; 687 -- SELECT * FROM fixed_col_table; 688 END %% 689 DELIMITER ;
本文来自博客园，作者：wanglifeng，转载请注明原文链接： https://www.cnblogs.com/wanglifeng717/p/15839391.html
本文来自博客园，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15831071.html
