CODEWITHSUNDEEP
javamybatis
bharanitharan
bharanitharan

Reputation: 2629

Mybatis Use generated keys for Batch Insert

I have done batch inserting in Mybatis and it is working fine. But I'm not sure how to store the generated primary keys for each row in the bean class. Here is my code,

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 (name) values (#{element.name})
            </foreach>
            SELECT * FROM dual
        </insert>
    </mapper>

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;
}
}

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;
}
}

In the above example Employee is the object to be persisted in database which contains list of Emp.

Upvotes: 6

Views: 14255

Answers (3)

Germ&#225;n S&#225;nchez Pastor
Germ&#225;n S&#225;nchez Pastor

Reputation: 66

For anyone facing this problems using @Annotations.

The main point is to use "list" as param and call it in generated keys. Also be sure of using @Insert as annotation.

 @Insert("""
                <script>
                INSERT INTO myTable (
                    value_x,
                    username
                    ) VALUES
                <foreach collection='list' item='item'  separator=','>
              ( #item.myValue, #{username} )
               </foreach>
               </script>
            """)
    @Options(useGeneratedKeys = true, keyColumn = "my_id_name_generated", keyProperty = "list.myNameVariableId")
    void insertAllObjects(@Param("list")List<Object> listObjects, String username);

Upvotes: 0

StormRider
StormRider

Reputation: 1

But I'm not sure how to store the generated primary keys for each row in the bean class.

If you want to map the generated primary key with your pojo then foreach inside the insert xml won't work. You'll have to write simple insert with useGeneratedKeys="true" and call it for each record that you want to persist.

I have given detailed answer here

Upvotes: 0

Anuj Patel
Anuj Patel

Reputation: 17839

Try using useGeneratedKeys="true" keyProperty="id" keyColumn="id" with your insert block.

i.e.

<insert id="insertEmployeeList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"  keyColumn="id">
INSERT ALL
  <foreach collection="list" item="element" index="index">
    INTO EMPLOYEE (name) values (#{element.name})
  </foreach>
</insert>

Why use select doing inside insert? Just wondering.

Upvotes: 2

Related Questions