基于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 ;

来源：https://www.cnblogs.com/wanglifeng717/p/15839391.html
图文来源于网络，如有侵权请联系删除。