基于mybatis的java代码生成存储过程
问题:
项目中目前使用mybatis操作数据库，使用插件(mybatis-generator)自动生成代码，对于增改查，使用存储过程实现了一版本，方便使用。
insert代码生成器用法:
insert_code_generator( in_var_tbl_name [要插入的表名] )
1DROPPROCEDUREIFEXISTS insert_code_generator; 2 DELIMITER %%3CREATEPROCEDURE 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-- ################################################################################################################
9SET group_concat_max_len =4294967295;
10
11-- #----------#配置项#-----------------------#
12-- 表名映射为驼峰形式，tbl_cdk_user_info -> userInfo，默认去除表名的tbl前缀
13-- SET @noStrInTbl="tbl_cbm";
14SET@noStrInTbl="tbl";
15
16-- #----------------------------------------#
17
18
19SET@in_tbl_name=in_var_tbl_name;
20SET@in_db_name=DATABASE();
21if (in_var_tbl_name ISNULLOR in_var_tbl_name=""OR in_var_tbl_name="") then
22SELECT"入参in_var_tbl_name表名不能为空"AS"error";
23 leave label;
24ENDif;
25
26
27
28
29-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
30-- ########### 生成引用
31-- 生成service层实体类以备加Dao：CdkmallGoodsApply
32SET@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
34SET@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
36SELECT CONCAT_WS("","/* 新建对象*/
",@objName,"",@objRefName,"= new ",@objName,"();
/*设置属性*/
",
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")," */
")
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 "
"
45 )
46 ) INTO@insert_code
47FROM
48 information_schema.COLUMNS c
49WHERE
50 c.table_schema=DATABASE() AND
51 c.TABLE_NAME =@in_tbl_name;
52
53SET@insert_domain_code="
54public 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
73SELECTREPLACE(@insert_domain_code,"@objName",@objName) INTO@insert_domain_code;
74SELECTREPLACE(@insert_domain_code,"@objRefName",@objRefName) INTO@insert_domain_code;
75SELECTREPLACE(@insert_domain_code,"@in_tbl_name",@in_tbl_name) INTO@insert_domain_code;
76
77
78-- 引入所有的domain 和dao -- 开始 --
79SELECT
80 GROUP_CONCAT(
81 CONCAT_WS("","@Autowired
","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;
")
84 SEPARATOR "
"
85 ) INTO@autowired_all_domain
86
87FROM information_schema.`TABLES` t
88WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOTIN("fixed_col_table");
89
90SELECT
91 GROUP_CONCAT(
92 CONCAT_WS("","@Autowired
","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;
")
95 SEPARATOR "
"
96 ) INTO@autowired_all_dao
97
98FROM information_schema.`TABLES` t
99WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOTIN("fixed_col_table") ;
100
101
102
103-- #############################################################
104-- 以下为动态批量insert需要使用
105-- #############################################################
106
107DROPTABLEifEXISTS fixed_col_table;
108
109CREATEtableifnotexists fixed_col_table(
110 col VARCHAR(256) NOTNULL 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
120INSERTINTO fixed_col_table (col,data_type)
121SELECT
122 t1.column_name,t1.data_type
123FROM
124 information_schema.COLUMNS t1
125WHERE
126 t1.table_schema=@in_db_nameAND
127 t1.TABLE_NAME =@in_tbl_name
128;
129
130-- 转驼峰
131UPDATE 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类型
137UPDATE fixed_col_table SET jdbc_type=
138case data_type
139when"datetime"then"TIMESTAMP"
140when"tinyint"then"TINYINT"
141when"bigint"then"BIGINT"
142when"int"then"INTEGER"
143when"float"then"REAL"
144when"varchar"then"VARCHAR"
145END;
146
147-- java类型转换
148UPDATE fixed_col_table SET java_type=
149case data_type
150when"datetime"then"Date"
151when"tinyint"then"Byte"
152when"bigint"then"Long"
153when"int"then"Integer"
154when"float"then"REAL"
155when"varchar"then"String"
156END;
157
158
159-- 组成jdbc字符串：id --> #{idForQueryjdbcType=INTEGER}
160UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS("","#{item.",col_for_query,",jdbcType=",jdbc_type,"} ");
161
162/*
163col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment
164name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
165name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
166create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 创建时间 | 非空 | 无索引
167
168*/
169
170
171-- select * from fixed_col_table;
172
173SELECT
174 GROUP_CONCAT(t1.column_name SEPARATOR ", ") INTO@allColumnList
175FROM
176 information_schema.COLUMNS t1
177WHERE
178 t1.table_schema=@in_db_nameAND
179 t1.TABLE_NAME =@in_tbl_name
180;
181
182-- #{id,jdbcType=INTEGER} ,#{name,jdbcType=VARCHAR} ,#{status,jdbcType=TINYINT} ,#{validStartTime,jdbcType=TIMESTAMP}
183SELECT GROUP_CONCAT(col_for_query_jdbc) INTO@jdbcColumnList
184FROM fixed_col_table;
185
186-- select @jdbcColumnList;
187
188-- id,name,status,valid_start_time,valid_end_time,create_time,last_update_time
189SELECT GROUP_CONCAT(col) INTO@allColumnList
190FROM fixed_col_table;
191
192-- select @allColumnList;
193
194SET@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})
203int batchInsert(@Param("@objRefNameList") List<@objName> @objRefNameList);
204";
205
206SELECTREPLACE(@mapper_code,"@in_tbl_name",@in_tbl_name) INTO@mapper_code;
207SELECTREPLACE(@mapper_code,"@allColumnList",@allColumnList) INTO@mapper_code;
208SELECTREPLACE(@mapper_code,"@objRefName",@objRefName) INTO@mapper_code;
209SELECTREPLACE(@mapper_code,"@jdbcColumnList",@jdbcColumnList) INTO@mapper_code;
210SELECTREPLACE(@mapper_code,"@objName",@objName) INTO@mapper_code;
211
212-- SELECT @mapper_code;
213
214
215SET@dao_code="
216public 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
224SELECTREPLACE(@dao_code,"@objName",@objName) INTO@dao_code;
225SELECTREPLACE(@dao_code,"@objRefName",@objRefName) INTO@dao_code;
226
227-- SELECT @dao_code;
228
229SET@domain_code="
230public 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";
253SELECTREPLACE(@domain_code,"@insert_code",@insert_code) INTO@domain_code;
254SELECTREPLACE(@domain_code,"@objName",@objName) INTO@domain_code;
255SELECTREPLACE(@domain_code,"@objRefName",@objRefName) INTO@domain_code;
256SELECTREPLACE(@domain_code,"@in_tbl_name",@in_tbl_name) INTO@domain_code;
257
258-- SELECT @domain_code;
259
260
261
262-- #########################################################################################
263-- 打印真正的语句
264-- #########################################################################################
265
266SET@insert_sql_help="
267高级insert示例：
268 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
2691：replace into tbl_name(col_name, ...) values(...)
270如果存在primary or unique相同的记录，则先删除掉。再插入新记录。REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和
271
2722：insert ignore into tbl_name (...) values(...)
273当插入数据时，如出现错误时，如重复数据（PrimaryKey，或者unique索引），将不返回错误，只以警告形式返回。
274会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过当前插入的这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的
275
2763：INSERT INTO tablename(...) VALUES(...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;
277INSERT INTO books (...) VALUES (...) ON duplicate KEY UPDATE id = id 等效于 ignore
278没有重复的执行插入，重复了执行后面更新语句
279解决问题：以前是找到某条记录，找到就是更新，找不到就是插入，要写两条语句，现在一条语句就搞定了
280
2814：INSERT INTO books (...) values (...) WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)
282如果没有id=1的记录就插入，否则什么都不做
283";
284
285
286SELECT"代码","用途" LIMIT 0
287UNIONALL
288SELECT@insert_code , "实体类set方法"
289UNIONALL
290SELECT@insert_domain_code , "单条插入domain层方法"
291UNIONALL
292SELECT@mapper_code ,"批量插入mapper层"
293UNIONALL
294SELECT@dao_code ,"批量插入dao层"
295UNIONALL
296SELECT@domain_code ,"批量插入domain层"
297UNIONALL
298SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Dao ",@objRefName,"Dao;
") ,"引入dao"
299UNIONALL
300SELECT CONCAT_WS(""," int insertResult"," = ",@objRefName,"Dao.insert","(",@objRefName,");") , "调用dao"
301UNIONALL
302SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Domain ",@objRefName,"Domain;
") , "引入domain"
303UNIONALL
304SELECT CONCAT_WS("",@objRefName,"Domain.insert","(",@objRefName,");") , "调用domain"
305UNIONALL
306SELECT@autowired_all_domain , "引用所有domain"
307UNIONall
308SELECT@autowired_all_dao , "引入所有dao"
309UNIONALL
310SELECT" @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insert(@objName @objRefName);" , "返回自增键刚插入的新值，放在mapper层insert方法上"
311UNIONALL
312SELECT@insert_sql_help ,"高级insert示例"
313
314;
315
316droptable fixed_col_table;
317
318END%%
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/wanglifeng7172DROPPROCEDUREIFEXISTS select_code_generator;
3 DELIMITER %%
4CREATEPROCEDURE 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
11SET group_concat_max_len =4294967295;
12
13-- #----------#配置项#-----------------------#
14-- 表名映射为驼峰形式，tbl_cdk_user_info -> userInfo，默认去除表名的tbl前缀
15-- SET @noStrInTbl="tbl_cbm";
16SET@noStrInTbl="tbl";
17
18-- #----------------------------------------#
19
20
21SET@in_tbl_name=in_var_tbl_name;
22SET@in_db_name=DATABASE();
23
24
25
26-- #----------#配置项#-----------------------#
27
28if(in_var_return_type ISNULLOR in_var_return_type=""OR in_var_return_type=""OR in_var_return_type="list"OR in_var_return_type="list|obj") then
29SET@return_type_flag="list"; -- 返回结果配置为obj或者list
30ELSE
31SET@return_type_flag="obj";
32ENDif;
33
34SET@in_tbl_name=in_var_tbl_name;
35
36
37-- ########### 生成引用
38-- 生成service层实体类以备加Dao：CdkmallGoodsApply
39SET@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
41SET@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>
45SELECTif(@return_type_flag="list",CONCAT_WS("",@objRefName,"List"),@objRefName) INTO@return_type_ref;
46SELECTif(@return_type_flag="list",CONCAT_WS(""," List<",@objName,"> "),@objName) INTO@return_type;
47
48
49
50DROPTABLEifEXISTS fixed_col_table;
51
52CREATEtableifnotexists fixed_col_table(
53 col VARCHAR(256) NOTNULL 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
66if(in_static_col_list ISNULLOR in_static_col_list="static_query_col"OR in_static_col_list=""OR in_static_col_list=""OR in_static_col_list="") then
67SET in_static_col_list=null;
68ELSE
69-- 插入静态字段
70SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_static_col_list, ",",CONCAT_WS("","","static"),("")),"","static")");
71PREPARE stmt FROM@exec_sql;
72EXECUTE stmt;
73DEALLOCATEPREPARE stmt;
74ENDif;
75
76if(in_dynamic_col_list ISNULLOR in_dynamic_col_list="dynamic_query_col"OR in_dynamic_col_list=""OR in_dynamic_col_list=""OR in_dynamic_col_list="") then
77SET in_dynamic_col_list=null;
78ELSE
79-- 插入动态字段
80SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_dynamic_col_list, ",",CONCAT_WS("","","dynamic"),("")),"","dynamic")");
81PREPARE stmt FROM@exec_sql;
82EXECUTE stmt;
83DEALLOCATEPREPARE stmt;
84ENDif;
85
86if(in_list_col_list ISNULLOR in_list_col_list="list_query_col"OR in_list_col_list=""OR in_list_col_list=""OR in_list_col_list="") then
87SET in_static_col_list=null;
88ELSE
89-- 插入list集合字段
90SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_list_col_list, ",",CONCAT_WS("","","list"),("")),"","list")");
91PREPARE stmt FROM@exec_sql;
92EXECUTE stmt;
93DEALLOCATEPREPARE stmt;
94ENDif;
95
96
97/* 打印查询字段的驼峰格式：IdAndAmountAndApplyNoAndUuid */
98SELECT GROUP_CONCAT( tt SEPARATOR "And") INTO@byname
99FROM
100(
101SELECT
102REPLACE(REPLACE(REPLACE(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
103FROM fixed_col_table) t;
104
105-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
106-- 将col_for_query字段转成驼峰：create_time --> createTimeForQuery
107UPDATE 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";
109UPDATE 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";
111UPDATE 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
116UPDATE fixed_col_table SET data_type =
117(
118SELECT t1.data_type
119FROM
120 information_schema.COLUMNS t1
121WHERE
122 t1.table_schema=@in_db_nameAND
123 t1.TABLE_NAME =@in_tbl_name
124and t1.column_name =col
125);
126
127-- 更新注释字段 --> 创建时间 | 非空 |
128UPDATE fixed_col_table SET col_comment =
129(
130SELECT CONCAT_WS("",t1.column_comment," | ",if(t1.is_nullable="YES","可空","非空")," | ",case t1.COLUMN_KEY when"PRI"then"主键"else""end)
131FROM
132 information_schema.COLUMNS t1
133WHERE
134 t1.table_schema=@in_db_nameAND
135 t1.TABLE_NAME =@in_tbl_name
136and t1.column_name =col
137);
138-- 更新注释字段 --> | 唯一键 | uni_city_info_name(city_name)
139UPDATE fixed_col_table SET col_comment = CONCAT_WS("",col_comment,
140IFNULL(
141 (SELECT
142 CONCAT_WS(""," | ",IF(t.non_unique =0,"唯一键","索引")," | ", t.index_name,"(",
143 (
144SELECT
145 GROUP_CONCAT(tt.column_name ORDERBY tt.seq_in_index)
146FROM
147 information_schema.statistics tt
148WHERE tt.table_schema =DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name
149 )
150 ,")")
151FROM
152 information_schema.statistics t
153WHERE t.table_schema =@in_db_name
154AND t.TABLE_NAME =@in_tbl_name
155AND t.COLUMN_NAME=col
156 )
157 ,"无索引")
158);
159
160
161/*
162SELECT
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类型
183UPDATE fixed_col_table SET jdbc_type=
184case data_type
185when"datetime"then"TIMESTAMP"
186when"tinyint"then"TINYINT"
187when"bigint"then"BIGINT"
188when"int"then"INTEGER"
189when"varchar"then"VARCHAR"
190END;
191
192-- java类型转换
193UPDATE fixed_col_table SET java_type=
194case data_type
195when"datetime"then"Date"
196when"tinyint"then"Byte"
197when"bigint"then"Long"
198when"int"then"Integer"
199when"varchar"then"String"
200END
201WHERE col_type!="list";
202
203-- java如果是集合类型
204UPDATE fixed_col_table SET java_type=
205case data_type
206when"datetime"then"List<Date>"
207when"tinyint"then"List<Byte>"
208when"bigint"then"List<Long>"
209when"int"then"List<Integer>"
210when"varchar"then"List<String>"
211END
212WHERE col_type="list";
213
214-- 组成jdbc字符串：id --> #{idForQueryjdbcType=INTEGER}
215UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS("","#{",col_for_query,",jdbcType=",jdbc_type,"} ");
216
217/*
218col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment
219name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
220name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
221create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 创建时间 | 非空 | 无索引
222
223*/
224
225-- SELECT * from fixed_col_table;
226
227
228--
229/* 列举所有字段 @allColumnList
230id, 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*/
232SELECT
233 GROUP_CONCAT(CONCAT("t.",t1.column_name," as ",t1.column_name) SEPARATOR ", ") INTO@allColumnList
234FROM
235 information_schema.COLUMNS t1
236WHERE
237 t1.table_schema=@in_db_nameAND
238 t1.TABLE_NAME =@in_tbl_name
239;
240
241
242-- ################################################################################################################
243-- ################################ 静态mapper层 ####################################################
244-- ################################################################################################################
245
246/* 查询条件字段 @queryList
247id=#{idForQuery,jdbcType=BIGINT} and amount=#{amountForQuery,jdbcType=INTEGER} and apply_no=#{applyNoForQuery,jdbcType=VARCHAR}
248*/
249
250SELECT
251 CONCAT(""" ,GROUP_CONCAT(CONCAT("and ",col,"=",col_for_query_jdbc) SEPARATOR ""),"",
") INTO@queryList
252FROM 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*/
267SET@var_if_test_statement=""<if test ="{1}col_for_query ">AND {2}col= {3}col_for_query_jdbc </if>",
";
268
269SELECT
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
272FROM 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
289SET@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
295SELECT
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 "
") INTO@listQueryList
297FROM fixed_col_table WHERE col_type="list";
298
299
300-- SELECT @listQueryList;
301
302SET@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
314SELECTREPLACE(@mapper_sql,"@allColumnList",IFNULL(@allColumnList,"")) INTO@mapper_sql;
315SELECTREPLACE(@mapper_sql,"@in_tbl_name",IFNULL(@in_tbl_name,"")) INTO@mapper_sql;
316SELECTREPLACE(@mapper_sql,"@queryList",IFNULL(@queryList,"")) INTO@mapper_sql;
317SELECTREPLACE(@mapper_sql,"@dynamicQueryList",IFNULL(@dynamicQueryList,"")) INTO@mapper_sql;
318SELECTREPLACE(@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
328SELECT
329 GROUP_CONCAT(CONCAT("@Param("",col_for_query,"") ",java_type,"",col_for_query)SEPARATOR " , ") INTO@queryparamList
330FROM fixed_col_table ;
331
332-- SELECT @queryparamList;
333
334-- mapper层代码模板
335SET@mapper_code=
336"@mapper_sql
337@return_type selectBy@byname(@queryparamList);";
338
339SELECTREPLACE(@mapper_code,"@mapper_sql",IFNULL(@mapper_sql,"")) INTO@mapper_code;
340SELECTREPLACE(@mapper_code,"@return_type",IFNULL(@return_type,"")) INTO@mapper_code;
341SELECTREPLACE(@mapper_code,"@byname",IFNULL(@byname,"")) INTO@mapper_code;
342SELECTREPLACE(@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*/
358SELECT
359 GROUP_CONCAT(col_for_query) INTO@dao_queryparamList
360FROM fixed_col_table ;
361
362SELECT
363 GROUP_CONCAT(CONCAT(java_type,"",col_for_query)) INTO@dao_type_queryparamList
364FROM fixed_col_table ;
365-- select @dao_type_queryparamList;
366-- select @dao_queryparamList;
367
368-- dao层代码模板
369SET@dao_code=
370"public @return_type selectBy@byname(@dao_type_queryparamList){
371 return mapper.selectBy@byname(@dao_queryparamList);
372}";
373SELECTREPLACE(@dao_code,"@return_type",@return_type) INTO@dao_code;
374SELECTREPLACE(@dao_code,"@byname",@byname) INTO@dao_code;
375SELECTREPLACE(@dao_code,"@dao_type_queryparamList",@dao_type_queryparamList) INTO@dao_code;
376SELECTREPLACE(@dao_code,"@dao_queryparamList",@dao_queryparamList) INTO@dao_code;
377
378-- SELECT @dao_code;
379
380-- ################################################################################################################
381-- ################################ domain层需要的 ########################################################
382-- ################################################################################################################
383
384-- 打印注释：/* uuidForQuery | String | 逻辑主键 | 非空 | 唯一键 */
385SELECT
386 GROUP_CONCAT(concat(col_for_query," | ",java_type," | ",col_comment) SEPARATOR "
") INTO@comment_str
387FROM fixed_col_table ;
388
389-- select @comment_str;
390
391-- if(faceIpList == null ||faceIpList.size()==0)
392SELECT
393if
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
400selectREPLACE(@dao_queryparamList,",","+","+") INTO@if_params_list;
401
402SET@domain_code=
403"/*
404@comment_str
405*/
406public @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";
415SELECTREPLACE(@domain_code,"@comment_str",@comment_str) INTO@domain_code;
416SELECTREPLACE(@domain_code,"@return_type_ref",@return_type_ref) INTO@domain_code;
417SELECTREPLACE(@domain_code,"@return_type",@return_type) INTO@domain_code;
418SELECTREPLACE(@domain_code,"@byname",@byname) INTO@domain_code;
419SELECTREPLACE(@domain_code,"@dao_type_queryparamList",@dao_type_queryparamList) INTO@domain_code;
420SELECTREPLACE(@domain_code,"@dao_queryparamList",@dao_queryparamList) INTO@domain_code;
421SELECTREPLACE(@domain_code,"@objRefName",@objRefName) INTO@domain_code;
422SELECTREPLACE(@domain_code,"@if_params_list",@if_params_list) INTO@domain_code;
423SELECTREPLACE(@domain_code,"@if_judge",@if_judge) INTO@domain_code;
424SELECTREPLACE(@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 -- 开始 --
433SELECT
434 GROUP_CONCAT(
435 CONCAT_WS("","@Autowired
","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;
")
438 SEPARATOR "
"
439 ) INTO@autowired_all_domain
440
441FROM information_schema.`TABLES` t
442WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOTIN("fixed_col_table");
443
444SELECT
445 GROUP_CONCAT(
446 CONCAT_WS("","@Autowired
","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;
")
449 SEPARATOR "
"
450 ) INTO@autowired_all_dao
451
452FROM information_schema.`TABLES` t
453WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOTIN("fixed_col_table") ;
454
455-- 引入所有的domain 和dao -- 结束 --
456
457
458
459-- ################################################################################################################
460-- ################################ 打印语句 ########################################################
461-- ################################################################################################################
462
463SET@cacheable_code="
464cacheable的使用方式
465Mapper层：
466@Select({
467 "select",
468 " para_value",
469 "from tbl_cdkmall_sys_para where para_key=#{paraKey,jdbcType=VARCHAR}"
470 })
471String getSysPara(String paraKey);
472
473 dao层
474 // 查询系统配置表的固定模板
475@Cacheable(value = "{FM_spa_cdkmall_getSysPara}", key = "#p0", unless = "#result == null")
476public 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")
481public String getSysPara(String partnerNo,String paraKey,String paraIndex) {
482 return mapper.getSysPara( partnerNo, paraKey, paraIndex);
483}
484";
485
486SET@dynamic_code_templ="
487日期转换：
488select STR_TO_DATE("1998-03-12-01-01-01","%Y-%m-%d-%H-%i-%s") ; 字符串转日期
489select 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
50210
503210
504320
505";
506
507
508SELECT"代码","功能" LIMIT 0
509UNIONALL
510SELECT@mapper_code , "mapper层方法"
511UNIONall
512SELECT@dao_code , "dao层方法"
513UNIONall
514SELECT@domain_code , "domain层方法"
515UNIONall
516SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Dao ",@objRefName,"Dao;
") , "引入dao"
517UNIONall
518SELECT CONCAT_WS("","/*
",@comment_str,"*/","
",@return_type,"",@return_type_ref," = ",@objRefName,"Dao.selectBy",@byname,"(",@dao_queryparamList,");") , "调用dao"
519UNIONall
520SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Domain ",@objRefName,"Domain;
") , "引入domain"
521UNIONall
522SELECT CONCAT_WS("","/*
",@comment_str,"*/","
",@return_type,"",@return_type_ref," = ",@objRefName,"Domain.selectBy",@byname,"(",@dao_queryparamList,");") , "调用domain"
523UNIONall
524SELECT@autowired_all_domain , "引用所有domain"
525UNIONall
526SELECT@autowired_all_dao , "引入所有dao"
527UNIONALL
528SELECT@dynamic_code_templ ,"动态参考语句"
529UNIONALL
530SELECT@cacheable_code ,"Cacheable参考使用方法";
531
532-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
533DROPTABLEifEXISTS fixed_col_table;
534
535END%%
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/wanglifeng7172DROPPROCEDUREIFEXISTS update_code_generator;
3 DELIMITER %%
4CREATEPROCEDURE 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
12SET group_concat_max_len =4294967295;
13-- #----------#配置项#-----------------------#
14
15-- 表名映射为驼峰形式，tbl_cdk_user_info -> userInfo，默认去除表名的tbl前缀
16-- SET @noStrInTbl="tbl_cbm";
17SET@noStrInTbl="tbl";
18
19-- -------------------------------
20
21
22
23SET@in_tbl_name=in_var_tbl_name;
24SET@in_db_name=DATABASE();
25
26
27
28-- ########### 生成引用
29-- 生成service层实体类以备加Dao：CdkmallGoodsApply
30SET@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
32SET@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
37DROPTABLEifEXISTS fixed_col_table;
38
39CREATEtableifnotexists fixed_col_table(
40 col VARCHAR(256) NOTNULL 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-- 插入参数中的字段值
54if(in_static_col_list ISNULLOR in_static_col_list="static_query_col"OR in_static_col_list=""OR in_static_col_list=""OR in_static_col_list="") then
55SET in_static_col_list=null;
56ELSE
57-- 插入静态字段
58SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_static_col_list, ",",CONCAT_WS("","","static"),("")),"","static")");
59PREPARE stmt FROM@exec_sql;
60EXECUTE stmt;
61DEALLOCATEPREPARE stmt;
62ENDif;
63
64if(in_dynamic_col_list ISNULLOR in_dynamic_col_list="dynamic_query_col"OR in_dynamic_col_list=""OR in_dynamic_col_list=""OR in_dynamic_col_list="") then
65SET in_dynamic_col_list=null;
66ELSE
67-- 插入动态字段
68SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_dynamic_col_list, ",",CONCAT_WS("","","dynamic"),("")),"","dynamic")");
69PREPARE stmt FROM@exec_sql;
70EXECUTE stmt;
71DEALLOCATEPREPARE stmt;
72ENDif;
73
74if(in_list_col_list ISNULLOR in_list_col_list="list_query_col"OR in_list_col_list=""OR in_list_col_list=""OR in_list_col_list="") then
75SET in_list_col_list=null;
76ELSE
77-- 插入list集合字段
78SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_list_col_list, ",",CONCAT_WS("","","list"),("")),"","list")");
79PREPARE stmt FROM@exec_sql;
80EXECUTE stmt;
81DEALLOCATEPREPARE stmt;
82ENDif;
83
84if(in_var_update_col_list ISNULLOR 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
85SELECT"要更新的字段不能为空"AS"error";
86 leave label;
87ELSE
88-- 插入list集合字段
89SET@exec_sql= CONCAT_WS("","INSERT ignore INTO fixed_col_table(col,col_type) values ("",REPLACE(in_var_update_col_list, ",",CONCAT_WS("","","update"),("")),"","update")");
90PREPARE stmt FROM@exec_sql;
91EXECUTE stmt;
92DEALLOCATEPREPARE stmt;
93ENDif;
94
95if(in_var_dynamic_update_col_list ISNULLOR 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
96SET in_var_dynamic_update_col_list=NULL;
97ELSE
98-- 插入list集合字段
99SET@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")");
100PREPARE stmt FROM@exec_sql;
101EXECUTE stmt;
102DEALLOCATEPREPARE stmt;
103ENDif;
104
105-- select @exec_sql;
106
107/* 打印查询字段的驼峰格式：IdAndAmountAndApplyNoAndUuid */
108SELECT CONCAT("By",GROUP_CONCAT( tt SEPARATOR "And")) INTO@byname
109FROM
110(
111SELECT
112REPLACE(REPLACE(REPLACE(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
113FROM fixed_col_table where col_type IN ("static","dynamic","list")) t;
114
115
116
117-- 将col_for_query字段转成驼峰：create_time --> createTimeForQuery
118UPDATE 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";
120UPDATE 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";
122UPDATE 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";
124UPDATE 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";
126UPDATE 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
131UPDATE fixed_col_table SET data_type =
132(
133SELECT t1.data_type
134FROM
135 information_schema.COLUMNS t1
136WHERE
137 t1.table_schema=@in_db_nameAND
138 t1.TABLE_NAME =@in_tbl_name
139and t1.column_name =col
140);
141-- SELECT * FROM fixed_col_table;
142-- 更新注释字段 --> 创建时间 | 非空 |
143UPDATE fixed_col_table SET col_comment =
144(
145SELECT CONCAT_WS("",t1.column_comment," | ",if(t1.is_nullable="YES","可空","非空")," | ",case t1.COLUMN_KEY when"PRI"then"主键"else""end)
146FROM
147 information_schema.COLUMNS t1
148WHERE
149 t1.table_schema=@in_db_nameAND
150 t1.TABLE_NAME =@in_tbl_name
151and t1.column_name =col
152);
153-- 更新注释字段 --> | 唯一键 | uni_city_info_name(city_name)
154UPDATE fixed_col_table SET col_comment = CONCAT_WS("",col_comment,
155IFNULL(
156 (SELECT
157 CONCAT_WS(""," | ",IF(t.non_unique =0,"唯一键","索引")," | ", t.index_name,"(",
158 (
159SELECT
160 GROUP_CONCAT(tt.column_name ORDERBY tt.seq_in_index)
161FROM
162 information_schema.statistics tt
163WHERE tt.table_schema =DATABASE() AND tt.INDEX_NAME=t.index_name AND tt.TABLE_NAME=t.table_name
164 )
165 ,")")
166FROM
167 information_schema.statistics t
168WHERE t.table_schema =@in_db_name
169AND t.TABLE_NAME =@in_tbl_name
170AND t.COLUMN_NAME=col
171 )
172 ,"无索引")
173);
174
175
176/*
177SELECT
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类型
198UPDATE fixed_col_table SET jdbc_type=
199case data_type
200when"datetime"then"TIMESTAMP"
201when"tinyint"then"TINYINT"
202when"bigint"then"BIGINT"
203when"int"then"INTEGER"
204when"float"then"REAL"
205when"varchar"then"VARCHAR"
206END;
207
208-- java类型转换
209UPDATE fixed_col_table SET java_type=
210case data_type
211when"datetime"then"Date"
212when"tinyint"then"Byte"
213when"bigint"then"Long"
214when"int"then"Integer"
215when"float"then"REAL"
216when"varchar"then"String"
217END
218WHERE col_type!="list";
219
220-- java如果是集合类型
221UPDATE fixed_col_table SET java_type=
222case data_type
223when"datetime"then"List<Date>"
224when"tinyint"then"List<Byte>"
225when"bigint"then"List<Long>"
226when"int"then"List<Integer>"
227when"float"then"List<REAL>"
228when"varchar"then"List<String>"
229END
230WHERE col_type="list";
231
232-- 组成jdbc字符串：id --> #{idForQueryjdbcType=INTEGER}
233UPDATE fixed_col_table SET col_for_query_jdbc=CONCAT_WS("","#{",col_for_query,",jdbcType=",jdbc_type,"} ");
234
235/*
236col col_type data_type jdbc_type java_type col_for_query col_for_query_jdbc col_comment
237name static varchar VARCHAR String nameForQuery #{nameForQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
238name dynamic varchar VARCHAR String nameForDynamicQuery #{nameForDynamicQuery,jdbcType=VARCHAR} 卡面ip名称 | 非空 | | 索引 | idx_face_ip_name(name,status)
239create_time list datetime TIMESTAMP List<Date> createTimeForQueryList #{createTimeForQueryList,jdbcType=TIMESTAMP} 创建时间 | 非空 | 无索引
240
241*/
242
243-- SELECT * from fixed_col_table;
244
245
246--
247/* 列举所有字段 @allColumnList
248id, 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*/
250SELECT
251 GROUP_CONCAT(t1.column_name SEPARATOR ", ") INTO@allColumnList
252FROM
253 information_schema.COLUMNS t1
254WHERE
255 t1.table_schema=@in_db_nameAND
256 t1.TABLE_NAME =@in_tbl_name
257;
258
259
260-- ################################################################################################################
261-- ################################ 静态mapper层 ####################################################
262-- ################################################################################################################
263
264/* 查询条件字段 @queryList
265id=#{idForQuery,jdbcType=BIGINT} and amount=#{amountForQuery,jdbcType=INTEGER} and apply_no=#{applyNoForQuery,jdbcType=VARCHAR}
266*/
267
268SELECT
269 CONCAT(""" ,GROUP_CONCAT(CONCAT("and ",col,"=",col_for_query_jdbc) SEPARATOR ""),"",
") INTO@queryList
270FROM 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*/
277SELECT
278 CONCAT(""set " ,GROUP_CONCAT(CONCAT("",col,"=",col_for_query_jdbc) SEPARATOR " ,"),"",
") INTO@update_list
279FROM fixed_col_table WHERE col_type="update";
280
281-- 动态update语句
282-- "<if test = "idForDynamicUpdate != null"> , id= #{idForQuery,jdbcType=INTEGER} </if>",
283SET@var_if_test_statement=""<if test ="{1}col_for_query != null"> , {2}col= {3}col_for_query_jdbc </if>",";
284
285SELECT
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 "
") INTO@update_dynamic_list
287FROM fixed_col_table WHERE col_type="dynamic_update";
288
289SELECT 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*/
302SET@var_if_test_statement=""<if test ="{1}col_for_query ">AND {2}col= {3}col_for_query_jdbc </if>",";
303
304SELECT
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 "
") INTO@dynamicQueryList
307FROM 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
326SET@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
332SELECT
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 "
") INTO@listQueryList
334FROM fixed_col_table WHERE col_type="list";
335
336
337-- SELECT @listQueryList;
338
339
340
341
342SET@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
355SELECTREPLACE(@mapper_sql,"@update_list",IFNULL(@update_list,"")) INTO@mapper_sql;
356SELECTREPLACE(@mapper_sql,"@in_tbl_name",IFNULL(@in_tbl_name,"")) INTO@mapper_sql;
357SELECTREPLACE(@mapper_sql,"@queryList",IFNULL(@queryList,"")) INTO@mapper_sql;
358SELECTREPLACE(@mapper_sql,"@dynamicQueryList",IFNULL(@dynamicQueryList,"")) INTO@mapper_sql;
359SELECTREPLACE(@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
369SELECT
370 GROUP_CONCAT(CONCAT("@Param("",col_for_query,"") ",java_type,"",col_for_query)SEPARATOR " , ") INTO@queryparamList
371FROM fixed_col_table ;
372
373-- SELECT @queryparamList;
374
375-- mapper层代码模板
376SET@mapper_code=
377"@mapper_sql
378int update@byname(@queryparamList);";
379
380SELECTREPLACE(@mapper_code,"@mapper_sql",IFNULL(@mapper_sql,"")) INTO@mapper_code;
381SELECTREPLACE(@mapper_code,"@byname",IFNULL(@byname,"")) INTO@mapper_code;
382SELECTREPLACE(@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*/
398SELECT
399 GROUP_CONCAT(col_for_query) INTO@dao_queryparamList
400FROM fixed_col_table ;
401
402SELECT
403 GROUP_CONCAT(CONCAT(java_type,"",col_for_query)) INTO@dao_type_queryparamList
404FROM fixed_col_table ;
405-- select @dao_type_queryparamList;
406-- select @dao_queryparamList;
407
408-- dao层代码模板
409SET@dao_code=
410"public int update@byname(@dao_type_queryparamList){
411 return mapper.update@byname(@dao_queryparamList);
412}";
413
414SELECTREPLACE(@dao_code,"@byname",@byname) INTO@dao_code;
415SELECTREPLACE(@dao_code,"@dao_type_queryparamList",@dao_type_queryparamList) INTO@dao_code;
416SELECTREPLACE(@dao_code,"@dao_queryparamList",@dao_queryparamList) INTO@dao_code;
417
418-- SELECT @dao_code;
419
420-- ################################################################################################################
421-- ################################ domain层需要的 ########################################################
422-- ################################################################################################################
423
424-- 打印注释：/* uuidForQuery | String | 逻辑主键 | 非空 | 唯一键 */
425SELECT
426 GROUP_CONCAT(concat(col_for_query," | ",java_type," | ",col_comment) SEPARATOR "
") INTO@comment_str
427FROM fixed_col_table ;
428
429-- select @comment_str;
430
431
432-- idForQuery+","+nameForQuery+","+idForDynamicQuery+","+createTimeForDynamicQuery+","+lastUpdateTimeForQueryList
433selectREPLACE(@dao_queryparamList,",","+","+") INTO@if_params_list;
434
435SET@domain_code=
436"/*
437@comment_str
438*/
439public 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";
447SELECTREPLACE(@domain_code,"@comment_str",@comment_str) INTO@domain_code;
448SELECTREPLACE(@domain_code,"@objRefName",@objRefName) INTO@domain_code;
449SELECTREPLACE(@domain_code,"@byname",@byname) INTO@domain_code;
450SELECTREPLACE(@domain_code,"@dao_type_queryparamList",@dao_type_queryparamList) INTO@domain_code;
451SELECTREPLACE(@domain_code,"@dao_queryparamList",@dao_queryparamList) INTO@domain_code;
452SELECTREPLACE(@domain_code,"@if_params_list",@if_params_list) INTO@domain_code;
453SELECTREPLACE(@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 -- 开始 --
462SELECT
463 GROUP_CONCAT(
464 CONCAT_WS("","@Autowired
","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;
")
467 SEPARATOR "
"
468 ) INTO@autowired_all_domain
469
470FROM information_schema.`TABLES` t
471WHERE TABLE_SCHEMA=DATABASE()and TABLE_NAME NOTIN("fixed_col_table");
472
473SELECT
474 GROUP_CONCAT(
475 CONCAT_WS("","@Autowired
","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;
")
478 SEPARATOR "
"
479 ) INTO@autowired_all_dao
480
481FROM information_schema.`TABLES` t
482WHERE TABLE_SCHEMA=DATABASE() and TABLE_NAME NOTIN("fixed_col_table") ;
483
484-- 引入所有的domain 和dao -- 结束 --
485
486-- ################################################################################################################
487-- ################################ 对象更新语句 ########################################################
488-- ################################################################################################################
489-- SET @in_tbl_name="tbl_ams_user_behavior";
490
491DROPTABLEifEXISTS all_col_table;
492
493CREATEtableifnotexists all_col_table(
494 col VARCHAR(256) NOTNULL 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
504INSERTINTO all_col_table(col)
505SELECT
506 t1.column_name
507FROM
508 information_schema.COLUMNS t1
509WHERE
510 t1.table_schema=DATABASE() AND
511 t1.TABLE_NAME =@in_tbl_name
512;
513-- 转换成驼峰格式
514UPDATE 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
519UPDATE all_col_table SET data_type =
520(
521SELECT t1.data_type
522FROM
523 information_schema.COLUMNS t1
524WHERE
525 t1.table_schema=DATABASE() AND
526 t1.TABLE_NAME =@in_tbl_name
527and t1.column_name =col
528);
529
530
531
532-- 转换成jdbc类型
533UPDATE all_col_table SET jdbc_type=
534case data_type
535when"datetime"then"TIMESTAMP"
536when"tinyint"then"TINYINT"
537when"bigint"then"BIGINT"
538when"int"then"INTEGER"
539when"float"then"REAL"
540when"varchar"then"VARCHAR"
541END;
542
543
544
545UPDATE 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*/
552SET@var_if_test_statement=""<if test ="obj.{1}col_for_query "> {3}col_for_query_jdbc , </if>",";
553
554SELECT
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 "
") INTO@objUpdateAllCol
557FROM all_col_table ;
558
559-- select @objUpdateAllCol;
560
561SET@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
574SELECTREPLACE(@obj_mapper_sql,"@objUpdateAllCol",IFNULL(@objUpdateAllCol,"")) INTO@obj_mapper_sql;
575SELECTREPLACE(@obj_mapper_sql,"@in_tbl_name",IFNULL(@in_tbl_name,"")) INTO@obj_mapper_sql;
576SELECTREPLACE(@obj_mapper_sql,"@queryList",IFNULL(@queryList,"")) INTO@obj_mapper_sql;
577SELECTREPLACE(@obj_mapper_sql,"@dynamicQueryList",IFNULL(@dynamicQueryList,"")) INTO@obj_mapper_sql;
578SELECTREPLACE(@obj_mapper_sql,"@listQueryList",IFNULL(@listQueryList,"")) INTO@obj_mapper_sql;
579
580-- select @obj_mapper_sql;
581-- obj
582SELECT
583 GROUP_CONCAT(CONCAT("@Param("",col_for_query,"") ",java_type,"",col_for_query)SEPARATOR " , ") INTO@objQueryparamList
584FROM fixed_col_table WHERE col_type IN ("static","dynamic","list");
585
586-- mapper层代码模板
587SET@obj_mapper_code=
588"@obj_mapper_sql
589int update@byname(@objQueryparamList,@Param("obj") @objName @objRefName);";
590
591SELECTREPLACE(@obj_mapper_code,"@obj_mapper_sql",IFNULL(@obj_mapper_sql,"")) INTO@obj_mapper_code;
592SELECTREPLACE(@obj_mapper_code,"@byname",IFNULL(@byname,"")) INTO@obj_mapper_code;
593SELECTREPLACE(@obj_mapper_code,"@objQueryparamList",IFNULL(@objQueryparamList,"")) INTO@obj_mapper_code;
594SELECTREPLACE(@obj_mapper_code,"@objName",IFNULL(@objName,"")) INTO@obj_mapper_code;
595SELECTREPLACE(@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*/
607SELECT
608 GROUP_CONCAT(col_for_query) INTO@obj_dao_queryparamList
609FROM fixed_col_table WHERE col_type IN ("static","dynamic","list");
610
611SELECT
612 GROUP_CONCAT(CONCAT(java_type,"",col_for_query)) INTO@obj_dao_type_queryparamList
613FROM fixed_col_table WHERE col_type IN ("static","dynamic","list");
614
615SET@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
620SELECTREPLACE(@obj_dao_code,"@byname",@byname) INTO@obj_dao_code;
621SELECTREPLACE(@obj_dao_code,"@obj_dao_type_queryparamList",@obj_dao_type_queryparamList) INTO@obj_dao_code;
622SELECTREPLACE(@obj_dao_code,"@obj_dao_queryparamList",@obj_dao_queryparamList) INTO@obj_dao_code;
623SELECTREPLACE(@obj_dao_code,"@objName",IFNULL(@objName,"")) INTO@obj_dao_code;
624SELECTREPLACE(@obj_dao_code,"@objRefName",IFNULL(@objRefName,"")) INTO@obj_dao_code;
625
626
627-- ################################################################################################################
628-- ################################ 实体类，getter,setter方法 ###########################################
629-- ################################################################################################################
630
631SELECT CONCAT_WS("","/* 新建对象*/
",@objName,"",@objRefName,"= new ",@objName,"();
/*设置属性*/
",
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")," */
")
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 "
"
640 )
641 ) into@insert_code
642FROM
643 information_schema.COLUMNS c
644WHERE
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
658select"代码","用途" LIMIT 0
659unionall
660SELECT@mapper_code , "mapper层方法"
661UNIONALL
662SELECT@dao_code , "dao层方法"
663unionall
664SELECT@domain_code , "domain层方法"
665unionALL
666SELECT@obj_mapper_code ,"用对象式更新mapper层方法"
667unionALL
668SELECT@obj_dao_code ,"用对象式更新dao层方法"
669UNIONALL
670SELECT@insert_code ,"实体类getter|setter方法"
671unionALL
672SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Dao ",@objRefName,"Dao;
") , "引入dao"
673unionall
674SELECT CONCAT_WS("","/*
",@comment_str,"*/","
"," int updateResult"," = ",@objRefName,"Dao.update",@byname,"(",@dao_queryparamList,");") , "调用dao"
675unionall
676SELECT CONCAT_WS("","@Autowired
","private ",@objName,"Domain ",@objRefName,"Domain;
") , "引入domain"
677unionall
678SELECT CONCAT_WS("","/*
",@comment_str,"*/","
"," int updateResult"," = ",@objRefName,"Domain.update",@byname,"(",@dao_queryparamList,");") , "调用domain"
679unionall
680SELECT@autowired_all_domain , "引用所有domain"
681unionall
682SELECT@autowired_all_dao , "引入所有dao";
683
684DROPTABLEifEXISTS fixed_col_table;
685
686DROPTABLEifEXISTS all_col_table;
687-- SELECT * FROM fixed_col_table;
688END%%
689 DELIMITER ;
update_code_generator
本文来自云海天，作者：wanglifeng，转载请注明原文链接： https://www.cnblogs.com/wanglifeng717/p/15839391.html
本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15831071.html
以上是 基于mybatis的java代码生成存储过程 的全部内容， 来源链接： utcz.com/z/536260.html
