SpringBoot+Mybatis常见业务场景sql

在公司中，常常有需要批量插入得业务需求，但是有时粗心大意就导致代码报错，特此记录一下核心细节。此处使用的数据只是简单模仿，实际生产环境还是需要结合具体情况而定

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 &gt;= #{item.startTime} AND endTime &lt;= #{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 &gt;= #{startTime} AND endTime &lt;= #{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>

3. 动态查询数据：

<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>

4. 删除数据：

<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>

MyBatis批量插入大量数据优化分析