MyBatis 3.3.1 BULK Insert multi-line write-back self-increment ID

MyBatis 3.3.1 supports the ability to bulk insert multi-line writeback self-increment IDs, see the support insert multiple rows and Write-back ID #547. Implementation Principle

The implementation principle is an SQL statement:

INSERT into TableName (column-a, [Column-b, ...])
VALUES (' value-1a ', [' value-1b ', ...]),
(' value-2a ', [' value-2b ', ...]),

The databases that support the above SQL syntax features are: DB2, SQL Server (since version 10.0-i.e.), PostgreSQL (since version 8.2), MySQL, SQLite (since ve Rsion 3.7.11) and H2. actual Combat

This article shows you how to use MyBatis 3.3.1 This new feature through an example. preparatory work

MySQL Build table sql:

CREATE TABLE ' tb_user ' (
  ' id ' BIGINT (a) NOT null auto_increment,
  ' name ' VARCHAR () ' "Not NULL,
  ' password ' VAR CHAR () Not NULL,
  ' age ' SMALLINT (3) is not NULL,
  ' email ' VARCHAR (+) NOT NULL,
  ' Gender ' SMALLINT (2) is not NULL DEF Ault ' 0 ',
  ' register_time ' DATETIME not null,
  ' status ' SMALLINT (2) is not null DEFAULT ' 0 ',
  PRIMARY KEY (' id '), 
  key ' User_index ' (' name ')
) Engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin;

Maven Dependency

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactid>mybatis</ artifactid>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.0</version>
</dependency>

Coding Code

1, User.java

public class User {
    private long ID;
    private String name;
    private String password;
    private int age;
    private String Email;
    private int gender;
    private int status;
    Private Date registertime;  Registration time public

    long GetId () {
        return ID;
    }

    public void SetId (long id) {
        this.id = ID;
    }
    ...
}

2, Usermapper.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.bytebeats.codelab.mybatis.mapper.UserMapper" >
    <insert id= "Batchinsert" Usegeneratedkeys= "true" keyproperty= "id" >
        INSERT into Tb_user
        (name, Password,age, Email,gender,register _time)
        VALUES
        <foreach collection= "list" item= "user" index= "index" separator= "," >
          (#{user.name}, #{user.password},#{user.age},#{user.email},
          #{user.gender},#{user.registertime})
        </foreach>
      </insert>
</mapper>

Note The foreach statement that the value of the collection must be "list", otherwise it will be an error.

3, Userdaoimpl.java

@Repository ("Userdao") Public
class Userdaoimpl implements Iuserdao {

    @Autowired
    private Ibasedao Basedao ;

    @Override public
    int Insertbatch (list<user> List) {

        return basedao.getsqlsession (). Insert (" Com.bytebeats.codelab.mybatis.mapper.UserMapper.batchInsert ", list);
    }
}

Where Ibasedao inherits from the Org.mybatis.spring.support.SqlSessionDaoSupport class, the code is as follows:

Import Org.mybatis.spring.support.SqlSessionDaoSupport;

/**
 * In general mybatis-spring usage, you do not need to use the Sqlsessionfactorybean directly or the corresponding sqlsessionfactory.
 * Instead, the session factory will be injected into the Mapperfactorybean or other DAO that extends the Sqlsessiondaosupport.
 *
 * @author Ricky Fung
 * @create 2016-08-29 20:56 */public
class Ibasedao extends Sqlsessiondaosupport {

}

4. Test Cases

@RunWith (Springjunit4classrunner.class) @ContextConfiguration (locations = {"Classpath:applicationContext.xml"})

    public class Iuserdaotest {@Autowired private Iuserdao Userdao;
        @Test public void Testinsertbatch () {list<user> userlist = new arraylist<> ();
        User User1 = new user ();
        User1.setname ("Ricky");
        User1.setpassword ("1234");
        User1.setage (27);
        User1.setemail ("ricky_feng@163.com");
        User1.setgender (1);
        User1.setregistertime (New Date ());

        Userlist.add (user1);
        User User2 = new user ();
        User2.setname ("Zhang San");
        User2.setpassword ("Bat");
        User2.setage (25);
        User2.setemail ("ricky_feng@163.com");
        User2.setgender (0);
        User2.setregistertime (New Date ());

        Userlist.add (User2);
        int update = Userdao.insertbatch (userlist);

        System.out.println ("Update:" +update); for (User user:userlist) {System.out.printlN ("ID:" +user.getid ()); }
    }
}

To do this, you can see that the user Object id attribute has a value. Summary

Mapper INSERT statement <foreach collection= "list" item= "user" index= "index" separator= "," > Collection name must be list, You can't call another name or you'll get an error. Internal implementation principles can be consulted:
mybatis3.3.x Technical Insider (15): MyBatis's foreach BULK INSERT, returns a list of primary key IDs for this article.