CODEWITHSUNDEEP
javaoracle-databasemybatis
bharanitharan
bharanitharan

Reputation: 2629

MyBatis Insert List values

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

Upvotes: 16

Views: 126261

Answers (5)

Adelin
Adelin

Reputation: 18951

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>

Upvotes: 24

bharanitharan
bharanitharan

Reputation: 2629

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

Upvotes: 22

R&#244;mulo G&#246;elzer Portolann
R&#244;mulo G&#246;elzer Portolann

Reputation: 191

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.

Upvotes: 13

PeterKhang
PeterKhang

Reputation: 61

[tistory]http://woniperstory.tistory.com/194 : This link shows the right answer !!!

public  someClass {

    public void someMethod() {

        Map<String,Object> map =  new HashMap<String,Object>() ;
        List<Emp> list =  new ArrayList<Emp>() ;

        ::::  add Emp Objects to list

        map.put("list", list)

        public Integer insertEmployeeList(map) ;
    }
}

public interface TestDAO {
    public Integer insertEmployeeList(Map<String,Object> map) throws SQLException;
}

<?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.Map">  
INSERT INTO EMPLOYEE (id, name) VALUES  
<foreach collection="list" item="element" index="index" separator=",">  
    ( #{element.id}, #{element.name} )  
</foreach>  
</insert>  
</mapper>

Upvotes: 2

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

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.

Upvotes: 2

Related Questions