在公司中,常常有需要批量插入得业务需求,但是有时粗心大意就导致代码报错,特此记录一下核心细节。此处使用的数据只是简单模仿,实际生产环境还是需要结合具体情况而定
1.新建数据库中表如下
2.数据库对应实体类
public class Person {
private int id;
private String name;
private int age;
public Person(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public Person() {
}
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;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
3.application.yml配置
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.119.128:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
mybatis.type-aliases-package=com.cd.pojo
4.pom.xml依赖如下
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.19</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
5.Mapper层接口如下
@Mapper
@Component
public interface PersonMapper {
/**
* 批量插入多条数据
* @param personList
* @return
*/
@Insert({
"<script>",
"insert into person(id,name,age) values ",
"<foreach collection='item' item='item' index='index' separator=','>",
"(#{item.id}, #{item.name},#{item.age})",
"</foreach>",
"</script>"
})
int addPersonBatch(@Param(value="item") List<Person> personList);
}
/**
** 批量删除
/
/**
* 根据id 批量删除
**/
@Delete({"<script> delete from " + COLD_AISLE_TABLE_NAME + " WHERE id IN "
+ "<foreach collection = 'ids' item = 'id' index = 'index' open = '(' separator= ',' close = ')' >"
+ " #{id} "
+ "</foreach>"
+ "</script>"})
int batchDelete(@Param("ids") List<Integer> ids);
/**
* 插入一条数据
**/
@Insert("INSERT INTO " + SHIELD_RULE_TABLE_NAME + " (name, description,state,level,timeCondition,action,priority,createTime,updateTime,deleteTime,removed) VALUES (" +
"#{name}, #{description}, #{state}, #{level},#{timeCondition},#{action},#{priority},#{createTime},#{updateTime},#{deleteTime},#{removed})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int addShieldRule(ShieldRule shieldRule);
/**
* 根据ID 更新数据
**/
@Update("update " + SHIELD_RULE_TABLE_NAME + " set name=#{name},description=#{description} ,state=#{state},level=#{level},timeCondition=#{timeCondition},action=#{action},priority=#{priority},updateTime=#{updateTime}" +
" where id=#{id} and removed=0")
int updateShieldRule(ShieldRule shieldRule);
/**
* 查询所有
**/
@Select("SELECT * from " + SHIELD_RULE_TABLE_NAME + " where removed=0 ORDER BY id DESC ")
List<ShieldRule> selectAll();
/**
* 根据类型和名称查询2, 用于更新时候查询
**/
@Select("SELECT count(*) from " + SHIELD_RULE_TABLE_NAME + " where name=#{name} AND id !=#{id} and removed=0 ")
int selectByName2(@Param("id") int id, @Param("name") String name);
/**
* 根据名称 匹配
**/
@Select("<script> SELECT * from " + SHIELD_RULE_TABLE_NAME +
"<where>" +
"<if test='state != null'> AND state=#{state}</if>" +
"<if test='name != null'> AND name like '%" + "${name}" + "%'</if>" +
"AND removed = 0 " +
"</where> ORDER BY id DESC </script>")
List<ShieldRule> searchShieldRuleListByName(@Param("name") String name, @Param("state") Integer state);
/**
* 根据id 批量更新,逻辑删除
**/
@Update({"<script> UPDATE " + SHIELD_RULE_TABLE_NAME + " SET deleteTime = #{deleteTime}, removed = -1 "
+ "WHERE id IN "
+ "<foreach collection = 'ids' item = 'id' index = 'index' open = '(' separator= ',' close = ')' >"
+ " #{id} "
+ "</foreach>"
+ "</script>"})
int batchUpdateShieldRule(@Param("deleteTime") long deleteTime, @Param("ids") List<Integer> ids);
/**
* 批量更新
* @param shieldRuleUpdateList
*/
@Update("<script>" +
"<foreach collection = 'list' item ='item' open='' close='' separator=';'>" +
"update shield_rule set name=#{item.name},description=#{item.description},state=#{item.state},level=#{item.level}," +
" cloudBoxId=#{item.cloudBoxId},timeCondition=#{item.timeCondition},action=#{item.action},priority=#{item.priority}," +
" updateTime=#{item.updateTime},companyId=#{item.companyId} where id =#{item.id} and removed=0 " +
"</foreach>" +
"</script>")
void updateBatchShieldRule(@Param("list")List<ShieldRule> shieldRuleUpdateList);
/**
* 批量更新状态
*
* @param ids
* @param state*/
@Update({"<script> UPDATE " + SHIELD_RULE_TABLE_NAME + " SET state = #{state}, updateTime = #{updateTime} "
+ "WHERE removed = 0 and id IN "
+ "<foreach collection = 'ids' item = 'id' index = 'index' open = '(' separator= ',' close = ')' >"
+ " #{id} "
+ "</foreach>"
+ "</script>"})
int updateBatch(@Param("ids") List<Integer> ids, @Param("state")int state, @Param("updateTime")long updateTime);
/**
* 批量查询
* @param ids
* @return
*/
@Select("<script> SELECT * FROM " + SHIELD_RULE_TABLE_NAME +
" WHERE id in " +
" <foreach collection='list' item='id' index='index' open='(' separator=',' close=')' >" +
" #{id}" +
" </foreach>" +
" AND removed = 0 " +
" </script>")
List<ShieldRule> selectByIds(@Param("list") List<Integer> ids);
@Select("<script> SELECT * from " + EXPORTER_TABLE_NAME +
"<where>" +
"<if test='list != null'>" + "cloudBoxId in"+
" <foreach collection='list' item='id' index='index' open='(' separator=',' close=')' >" +
" #{id}" +
" </foreach>" +
"</if>" +
"<if test='item.name != null'> name like '%" + "${item.name}" + "%'</if>" +
"<if test='item.deviceId != 0'> AND id in (select distinct exporterId from signal_collect where deviceId = #{item.deviceId})</if>" +
"</where> ORDER BY id DESC </script>")
List<Exporter> searchExportListPage(@Param("list") List<Integer> cloudBoxIdList,@Param("item") ExporterQuery exporterQuery);
/**
* 子查询
* @param ids
* @return
*/
@Select("<script> SELECT * from " + EXPORTER_TABLE_NAME +
"<where>" +
"<if test='item.name != null'> name like '%" + "${item.name}" + "%'</if>" +
"<if test='item.componentInstanceId != 0'> AND id in (select distinct exporterId from signal_collect where componentInstanceId = #{item.componentInstanceId})</if>" +
"</where> ORDER BY id DESC </script>")
List<Exporter> searchExportListPage(@Param("item") ExporterQuery exporterQuery);
/**
* 查询柴发发电一天某个类型耗能
*
*/
@Select("<script> SELECT type,SUM(electricity) as daySum,electricityRuleId from " + DIESEL_GENERATOR_INCOMING_ELECTRICITY_DATA +
"<where>" +
"<if test='item.electricityRuleId != 0'> electricityRuleId = #{item.electricityRuleId} </if>" +
"AND startTime >= #{item.startTime} AND endTime <= #{item.endTime} "+
"</where> GROUP BY type,electricityRuleId </script>")
List<DieselGeneratorIncomingElectricityOneDataVo> selectDieselGeneratorIncomingOneDataByTime(@Param("item") DieselGeneratorIncomingElectricityDataQuery dieselGeneratorIncomingElectricityDataQuery);
@Select("<script> SELECT type,SUM(electricity) as electricitySum FROM " + cabinet_electricity_data_table_name +
" WHERE cabinetId in " +
" <foreach collection='list' item='id' index='index' open='(' separator=',' close=')' >" +
" #{id}" +
" </foreach>" +
" AND startTime >= #{startTime} AND endTime <= #{endTime} group by type " +
" </script>")
List<CabinetElectricityDataVo> selectByTime(@Param("list") List<Integer> cabinetIdList, @Param("startTime") long startTime, @Param("endTime") long endTime);
**6.在SpringBoot得测试类中编写如下代码
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class InsertTest {
@Autowire
private PersonMapper personMapper;
@Test
public void test3(){
List<Person> personList = new ArrayList<Person>();
personList.add(new Person(3,"张三",18));
personList.add(new Person(4,"小明",19));
personList.add(new Person(3,"张涨",20));
personList.add(new Person(3,"张强",21));
personMapper.addPersonBatch(personList);
}
##测试批量插入成功##
分页工具类:在不使用分页插件的情况下,自定义分页工具
/**
* 封装分页的相关信息
*/
public class Page {
// 当前页码
private int current = 1;
// 每页显示最大上限
private int limit = 10;
// 数据总数(用于计算总页数)
private int rows;
//查询路径(用于复用分页链接)
private String path;
public int getCurrent() {
return current;
}
public void setCurrent(int current) {
if(current>=1){
this.current = current;
}
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
if(limit >=1 && limit <= 100){
this.limit = limit;
}
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
if(rows >=0){
this.rows = rows;
}
}
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
/**
* 获取当前页的起始行
* @return
*/
public int getOff(){
// current * limit - limit
return (current - 1) * limit;
}
/**
* 获取总的页数
* @return
*/
public int getTotal(){
// rows / limit [+1]
if(rows % limit == 0){
return rows / limit;
}else {
return rows / limit +1;
}
}
/**
* 获取当前页面展示的起始页码(比如当前页是第三页,则当前页的起始页码是1,设置一页只能看到五个页码)
* @return
*/
public int getFrom(){
int from = current - 2;
return from < 1 ? 1 :from;
}
/**
* 获取当前页面展示的结束页码
* @return
*/
public int getTo(){
int to = current + 2;
int total = getTotal();
return to > total ? total : to;
}
@Override
public String toString() {
return "Page{" +
"current=" + current +
", limit=" + limit +
", rows=" + rows +
", path='" + path + '\'' +
'}';
}
}
xml形式的动态sql:
动态添加
<insert id="insertBatch" parameterType="java.util.List">
<script>
INSERT INTO table_name (column1, column2)
VALUES
<foreach collection="list" item="item" separator=",">
<if test="item.column1 != null">
#{item.column1},
</if>
<if test="item.column2 != null">
#{item.column2},
</if>
</foreach>
</script>
</insert>
动态更新:
<update id="updateBatch" parameterType="java.util.List">
<script>
<foreach collection="list" item="item" separator=";">
UPDATE table_name
<set>
<if test="item.column1 != null">
column1 = #{item.column1},
</if>
<if test="item.column2 != null">
column2 = #{item.column2},
</if>
</set>
WHERE id = #{item.id}
</foreach>
</script>
</update>
- 动态查询数据:
<select id="selectBatch" parameterType="java.util.List" resultType="com.example.User">
<script>
SELECT *
FROM table_name
WHERE 1 = 1
<foreach collection="list" item="item" separator="AND">
<if test="item.column1 != null">
AND column1 = #{item.column1}
</if>
<if test="item.column2 != null">
AND column2 = #{item.column2}
</if>
</foreach>
</script>
</select>
- 删除数据:
<delete id="deleteBatch" parameterType="java.util.List">
<script>
DELETE FROM table_name
WHERE column1 IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.column1}
</foreach>
</script>
</delete>