MySql batch database operations, mysql database
- 1) Batch insert
- Batch operations mainly use the foreach of Mybatis to traverse the parameter list and perform corresponding operations,
- Therefore, the batch insert, update, and delete statements are similar, but SQL statements are slightly different.
- Mysql batch operation requires database connection configuration allowMultiQueries = true.
- <Insert id = "batchInsert" parameterType = "java. util. List" useGeneratedKeys = "true">
- <Foreach close = "" collection = "list" index = "index" item = "item" open = "" separator = ";">
- Insert into user (name, age, dept_code) values
- (# {Item. name, jdbcType = VARCHAR },
- # {Item. age, jdbcType = INTEGER },
- # {Item. deptCode, jdbcType = VARCHAR}
- )
- </Foreach>
- </Insert>
- Or
- <Insert id = "batchInsert" parameterType = "java. util. List" useGeneratedKeys = "true">
- Insert into user (name, age, dept_code) values
- <Foreach collection = "list" index = "index" item = "item" open = "" close = "" separator = ",">
- (# {Item. name, jdbcType = VARCHAR },
- # {Item. age, jdbcType = INTEGER },
- # {Item. deptCode, jdbcType = VARCHAR}
- )
- </Foreach>
- </Insert>
- (2) Batch update
- <Update id = "batchUpdate" parameterType = "java. util. List">
- <Foreach close = "" collection = "list" index = "index" item = "item" open = "" separator = ";">
- Update user set name = # {item. name, jdbcType = VARCHAR}, age = # {item. age, jdbcType = INTEGER}
- Where id = # {item. id, jdbcType = INTEGER}
- </Foreach>
- </Update>
- (3) Batch Delete
- <Delete id = "batchDelete" parameterType = "java. util. List">
- <Foreach close = "" collection = "list" index = "index" item = "item" open = "" separator = ";">
- Delete from user
- Where id = # {item. id, jdbcType = INTEGER}
- </Foreach>
- </Delete>
- Ii. Fuzzy search
- <Select id = "selectLikeName" parameterType = "java. lang. String" resultMap = "BaseResultMap">
- Select
- <Include refid = "Base_Column_List"/>
- From user
- Where name like CONCAT ('%', # {name}, '% ')
- </Select>
