[JAVA] How to insert all at once with MyBatis

Overview

--When you want to insert List data, it is useless to insert one by one, so you want to insert all the data at once. --Customized Mapper automatically generated by MyBatisGenerator

Notes

--Be careful as it will be overwritten and disappear if you generate it again. --It seems that the method is different when the ID is AUTO INCREMENT.

environment

Method

There are two things to do.

  1. Define a batch insert method in the Mapper class
  2. Add batch insert settings to xml

Example

For example, suppose you have a table like this (MySQL).

user_friend.sql


CREATE TABLE user_friend (
  user_id int(10) NOT NULL,
  friend_id int(10) NOT NULL
  friend_name varchar(30) NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (user_id, friend_id)
);

Add a method to the Mapper class

--Add a method of insertBulk --Pass the List of the data you want to insert as an argument --It is optional to name it with @Param

UserFriendMapper.java


int insertBulk(@Param("friendList")List<UserFriend> friendList);

Add settings to xml

--Add ʻinsert id =" insertBulk " to ʻUserFriendMapper.xml --Specify in parameterType =" java.util.List " --Specify collection =" list " if you did not specify @ Param in Mapper

UserFriendMapper.xml


<!--Add this-->
<insert id="insertBulk" parameterType="java.util.List">
    insert into user_friend
      (user_id, friend_id, friend_name, created_at, updated_at)
    values
    <foreach collection="friendList" item="fr" separator=","> 
    (
      #{fr.userId,jdbcType=INTEGER}, #{fr.friendId,jdbcType=INTEGER},
      #{fr.friendName,jdbcType=VARCHAR}, #{fr.createdAt,jdbcType=TIMESTAMP},
      #{fr.updatedAt,jdbcType=TIMESTAMP}
    )
    </foreach>
</insert>

How to use on Java (Spring Boot) side

Just call the method you added to the Mapper class.

FriendService.java


@Service
public class FriendService {

	@Autowired
	private UserFriendMapper userFriendMapper;

	/**
	 * @number of return int inserts
	 */
	public int insertFriends(int userId, List<UserFriend> saveFriends) {

		return userFriendMapper.insertBulk(saveFriends);
	}
}

reference

-Database access (MyBatis3 edition) Entity batch registration

Recommended Posts

How to insert all at once with MyBatis
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
[How to insert a video in haml with Rails]
How to number (number) with html.erb
How to update with activerecord-import
How to insert a video
How to input multiple images at once using rake task
Mapping to a class with a value object in How to MyBatis
How to scroll horizontally with ScrollView
How to get started with slim
How to enclose any character with "~"
How to use mssql-tools with alpine
How to insert an external library
How to use MyBatis Mapper annotation
How to get along with Rails
How to start Camunda with Docker
How to call multiple names at once in the same category
How to crop an image with libGDX
How to adjustTextPosition with iOS Keyboard Extension
How to share files with Docker Toolbox
How to compile Java with VsCode & Ant
[Android] How to deal with dark themes
How to use BootStrap with Play Framework
[Rails] How to use rails console with docker
How to insert a video in Rails
How to switch thumbnail images with JavaScript
[Note] How to get started with Rspec
How to do API-based control with cancancan
How to achieve file download with Feign
How to update related models with accepts_nested_attributes_for
How to set JAVA_HOME with Maven appassembler-maven-plugin
How to implement TextInputLayout with validation function
How to handle sign-in errors with devise
How to delete data with foreign key
How to test private scope with JUnit
How to monitor nginx with docker-compose with datadog
How to deal with Precompiling assets failed.
How to achieve file upload with Feign
How to run Blazor (C #) with Docker
Consume multiple messages at once with spring-kafka
How to insert processing with any number of elements in iterative processing in Ruby
How to build Rails 6 environment with Docker
How to insert icons using Font awesome
How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)
How to download Oracle JDK 8 rpm with curl
[Java] How to test for null with JUnit
How to mock each case with Mockito 1x
How to mock each case with PowerMock + Mockito1x
How to save to multiple tables with one input
How to test interrupts during Thread.sleep with JUnit
How to use built-in h2db with spring boot
How to search multiple columns with gem ransack
How to use Java framework with AWS Lambda! ??
[Swift] How to link the app with Firebase
How to use Java API with lambda expression
How to get started with Eclipse Micro Profile
How to give your image to someone with docker
How to monitor SPA site transitions with WKWebView
How to write test code with Basic authentication
[Rails] How to easily implement numbers with pull-down
How to build API with GraphQL and Rails