“This is the 12th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”


  • Business scenario:

    • The sign-in table is a process. After the audit is completed, it needs to be automatically added to the table data to facilitate the development of the export function of the table. Therefore, we need to obtain the slave table ID of batch insert to maintain the association relationship between the master and slave tables.
  • Concrete implementation:

    • Create entity class: Since the returned ID is automatically injected, select the entity class to receive the increment id.
@Data
public class SignIn implements Serializable {
    private long id;// Note that the id is of type long
    private String owner;
    private String modifier;
    private String last_modified;
    private String created_at;
    private String modified_method;
    private String app_key;
    private String app_extend_key;
    private String canjiayixiangxin;
    private String canjiashijian;
    private String canjiaren;
    private String yanlianliushuihao;
}
Copy the code
  • Mapper:
    • Note: Only parameterType= “” is required for normal insertion.
    • We need to add the following three if we want to return id:
    • UseGeneratedKeys =”true” : allows JDBC support for automatic primary key generation, driver compatibility is required (if set to true this setting enforces automatic primary key generation, although some drivers are incompatible but still work)
    • KeyProperty =” ID “: takes the key value of the ID
    • KeyColumn =” ID “: Specifies the id value
<! Insert data into table --> <insert id="insertParameter"  useGeneratedKeys="true" keyProperty="id" keyColumn="id" >
        insert into
        	${tableId}(
         owner,modifier,last_modified,created_at,modified_method,app_key,app_extend_key, canjiayixiangxin,canjiashijian,canjiaren,yanlianliushuihao)
        values
        <foreach collection="list" index="index" item="ids" separator=",">
            (
            #{ids.owner},#{ids.modifier},#{ids.last_modified},#{ids.created_at},#		{ids.modified_method},#{ids.app_key},#{ids.app_extend_key}, #{ids.canjiayixiangxin},#{ids.canjiashijian},#{ids.canjiaren},#{ids.yanlianliushuihao}
            )
        </foreach>
    </insert>
Copy the code
  • Service layer:
  • Assuming you have the data set you want to insert.
  • Note: There is a common problem that many blogs on the web overlook: the returned ID is repeated in the collection. Here’s why.
  • why:In essence, when we bulk insert, we execute insert statements on a collection of entity classes, using foreach for bulk inserts.
  • We convert the List
    > type to List< entity class >. Use the mapToBean method to convert.

  • Because we’re doing this in a loop, we have to re-create an object each time, otherwise the ID will be overwritten.
// If the data is empty, no further execution is required
if (CollectionUtils.isEmpty(resultList)){
     return;
  }
 // Format the time
 SimpleDateFormat sdfDay = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
 List<SignIn> signInList = new ArrayList<>();
 for (Map map : resultList){
 	map.put("created_at",sdfDay.format(map.get("created_at")));
 	map.put("canjiashijian",sdfDay.format(map.get("canjiashijian")));
 	map.put("last_modified",sdfDay.format(map.get("last_modified")));
 	map.remove("id");
 	// Convert to entity class, where the object needs to be new one at a time, otherwise the ID will be overwritten with the last ID
 	SignIn signIn = new SignIn();
 	signInList.add(BeanUtils.mapToBean(map,signIn));
  }
Copy the code
  • This will get all the interpolated ids.
  • The following provides the following Map to the entity method for you:
public class BeanUtils {

    private BeanUtils(a) {throw new IllegalStateException();
    }
    /** * map to bean **@param map
     * @param bean
     * @param <T>
     * @return* /
    public static <T> T mapToBean(Map<String, Object> map, T bean) {
        BeanMap beanMap = BeanMap.create(bean);
        beanMap.putAll(map);
        returnbean; }}Copy the code

The road ahead is long, I see no end, I will search high and low

If you think I bloggers write well! Writing is not easy, please like, follow, comment and give encouragement to the blogger ~ Hahah