MyBatis Insert List values

java, mybatis, oracle

Solution

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

Problem

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 ```

Original source