Mapper.xml (Mapper xml file)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="TestDAO">
<insert id="insertEmployeeList" parameterType="java.util.List">
INSERT INTO EMPLOYEE (id, name) VALUES
<foreach collection="list" item="element" index="index" open="(" separator="," close=")">
#{element.id}, #{element.name}
</foreach>
</insert>
</mapper>
Employee.java
public class Employee {
private List<Emp> list = new ArrayList<Emp>();
public List<Emp> getList() {
return list;
}
public void setList(List<Emp> list) {
this.list = list;
}
}
Emp.java
public class Emp {
public Emp(int id, String name) {
this.id = id;
this.name = name;
}
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
TestDAO.java
public interface TestDAO {
public Integer insertEmployeeList(List<Emp> empList) throws SQLException;
}
Main.java
public class Main {
public static void main (String args[]) {
TestDAO tm = session.getMapper(TestDAO.class);
Employee e = new Employee();
Emp e11 = new Emp(123,"abc");
Emp e12 = new Emp(456,"def");
e.getList().add(e11);
e.getList().add(e12);
tm.insertEmployeeList(e.getList());
}
}
Exception I'm getting is:
Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values
The error may involve com.XXXX.sample.test.dao.TestDAO.insertEmployeeList-Inline
The error occurred while setting parameters
Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values
Mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxxx.sample.test.dao.TestDAO">
<insert id="insertEmployeeList" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="element" index="index" >
INTO EMPLOYEE (id,name) values (#{element.id},#{element.name})
</foreach>
SELECT * FROM dual
</insert>
</mapper>
This is how the query should be there in Mapper xml