mysql foreach insert_MyBatis_动态sql_foreach_mysql下foreach批量插入的两种方式

方法1:

笔记要点

出错分析与总结

工程组织

数据库组织

0.重新修改Bean类    修改

1.定义接口

//批量插入

public void addEmps(@Param("emps")List emps);

2.定义XML映射文件

INSERT INTO tbl_employee(`last_name`,`email`,`gender`,`d_id`)

VALUES(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})

3.编写测试代码

public SqlSessionFactory getSqlSessionFactory() throwsIOException {

String resource= "mybatis-config.xml";

InputStream inputStream=Resources.getResourceAsStream(resource);return newSqlSessionFactoryBuilder().build(inputStream);

}

@Testpublic void test12() throwsException {

SqlSession openSession=getSqlSessionFactory().openSession();try{

System.out.println("++++++++++---- 2.测试动态SQL: mysql下的foreach的批量插入");

EmployeeMapper_DynamicSQL mapper= openSession.getMapper(EmployeeMapper_DynamicSQL.class);

List emps=new ArrayList<>();

emps.add(new Employee(null,"smith" ,"smith@qq.com" , "1",new Department(1)));

emps.add(new Employee(null,"aliex" ,"aliex@qq.com" , "0",new Department(1)));

mapper.addEmps(emps);

openSession.commit();

}finally{

openSession.close();

}

}

测试结果

++++++++++---- 2.测试动态SQL: mysql下的foreach的批量插入

DEBUG12-05 16:43:52,848 ==> Preparing: INSERT INTO tbl_employee(`last_name`,`email`,`gender`,`d_id`) VALUES (?,?,?,?) , (?,?,?,?) (BaseJdbcLogger.java:145)

DEBUG12-05 16:43:52,873 ==> Parameters: smith(String), smith@qq.com(String), 1(String), 1(Integer), aliex(String), aliex@qq.com(String), 0(String), 1(Integer) (BaseJdbcLogger.java:145)

DEBUG12-05 16:43:52,875 <== Updates: 2 (BaseJdbcLogger.java:145)

方法2:     使用多条mysql语句

1.开启allowMultiQueries=true属性--支持一次查询多条语句, 进入全局配置文件中

2.定义XML映射文件

INSERT INTO tbl_employee(last_name,email,gender,d_id)

VALUES (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id});

3.编写测试代码

@Testpublic void test12() throwsException {

SqlSession openSession=getSqlSessionFactory().openSession();try{

System.out.println("++++++++++---- 2-2.测试动态SQL: mysql下的foreach的批量插入");

EmployeeMapper_DynamicSQL mapper= openSession.getMapper(EmployeeMapper_DynamicSQL.class);

List emps=new ArrayList<>();

emps.add(new Employee(null,"smith2" ,"smith@qq.com" , "1",new Department(1)));

emps.add(new Employee(null,"aliex2" ,"aliex@qq.com" , "0",new Department(1)));

mapper.addEmps(emps);

openSession.commit();

}finally{

openSession.close();

}

}

测试结果

idea环境下, 测试失败!原因如下:

++++++++++---- 2-2.测试动态SQL: mysql下的foreach的批量插入

DEBUG12-05 17:14:10,399 ==> Preparing: INSERT INTO tbl_employee(last_name,email,gender,d_id) VALUES (?,?,?,?); ; INSERT INTO tbl_employee(last_name,email,gender,d_id) VALUES (?,?,?,?); (BaseJdbcLogger.java:145)

DEBUG12-05 17:14:10,424 ==> Parameters: smith2(String), smith@qq.com(String), 1(String), 1(Integer), aliex2(String), aliex@qq.com(String), 0(String), 1(Integer) (BaseJdbcLogger.java:145)

org.apache.ibatis.exceptions.PersistenceException:

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versionfor the right syntax to use near ';

INSERT INTO tbl_employee(last_name,email,gender,d_id)' at line 3

### The error may involve defaultParameterMap

### The error occurredwhilesetting parameters

### SQL: INSERT INTO tbl_employee(last_name,email,gender,d_id) VALUES (?,?,?,?); ; INSERT INTO tbl_employee(last_name,email,gender,d_id) VALUES (?,?,?,?);

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versionfor the right syntax to use near ';

INSERT INTO tbl_employee(last_name,email,gender,d_id)' at line 3

........

....