MyBatis Insert List values

Question

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

Answer 1

You can use annotations (@org.apache.ibatis.annotations.Insert) for executing a single insert for your entire list.

Remember: You wont need any sql provider class.

public interface SomethingMapper {

    @Insert({
        "<script>",
        "INSERT INTO your_database_name.your_table_name",
            "(column1_int, column2_str, column3_date, column4_time)",
        "VALUES" +  
            "<foreach item='each_item_name' collection='theCollection' open='' separator=',' close=''>" +
                "(" +
                    "#{each_item_name.column1,jdbcType=INTEGER},",
                    "#{each_item_name.column2,jdbcType=VARCHAR},",
                    "(SELECT SOME_DB_FUNCTION(#{each_item_name.column3,jdbcType=DATE})),",
                    "#{each_item_name.period.start,jdbcType=TIME}" +
                ")" +
            "</foreach>",
    "</script>"})
    void insertBatchSomething(@Param("theCollection") List<Something> theCollection);

}

Output SQL if you have 2 items:

SQL: INSERT INTO your_database_name.your_table_name (column1_int, column2_str, column3_date, column4_time) VALUES (?, ?, (SELECT SOME_DB_FUNCTION(?)), ?), (?, ?, (SELECT SOME_DB_FUNCTION(?)), ?)

P.S.

@Insert receives a String[], so for each value it will add a whitespace between Strings.

Answer 2

As I use MySQL as a Data base, after a couple of tries this is how it worked for me.

<insert id="insert" parameterType="java.util.List">
        INSERT INTO games (
        id,
        game_id,
        game_vendor,
        game_code,
        game_name,
        game_type)
        VALUES
        <foreach collection="list" item="element" index="index" open="(" separator="),("  close=")">
            #{element.id},
            #{element.gameId},
            #{element.gameVendor},
            #{element.gameCode},
            #{element.gameName},
            #{element.gameType},
       </foreach>
    </insert>
</mapper>

Answer 3

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

Answer 4

Configure your logging system to print out the generated Queries, then try to execute them to your DB directly.

In your case, the expected query SHOULD be

INSERT INTO EMPLOYEE (id, name) VALUES
(123, "abc")(456, "def")

as far as I can remember that is not valid. INSERT INTO only takes one set of VALUES. I looked up the documentation and it looks like this there as well.

See this answer on how to INSERT multiple rows in oracle.