MyBatis动态SQL

1. 配置依赖

		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

2. 在SpringBoot启动类添加MapperScan注解扫描mapper包

@MapperScan("com.exceeddata.rollout.api.mapper")

3. 在Mapper类中使用动态SQL

package com.exceeddata.rollout.api.mapper;

import com.exceeddata.rollout.api.model.Rollout;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface RolloutMapper {

    @Insert("insert into rollout(user_id, type_id, software_id, software_version, project_id, version_module_id, roStatus)" +
            " values (#{userId}, #{typeId}, #{softwareId}, #{softwareVersion}, #{projectId}, #{versionModuleId}, #{roStatus})")
    @Options(useGeneratedKeys=true, keyProperty="rolloutId")
    Long insertRollout(Rollout rollout);

    @Update("update rollout set hawkbit_rollout_id = #{hawkbit_rollout_id} " +
            " where rollout_id = #{rollout_id} ")
    Long updateRolloutWithHawkbitDsId(@Param("hawkbit_rollout_id") final String hawkbitRolloutId,
                                      @Param("rollout_id") final Long rolloutid);

    @Update("update rollout set roStatus = #{roStatus} " +
            "where hawkbit_rollout_id = #{hawkbit_rollout_id} ")
    Long updateRoStatus(@Param("hawkbit_rollout_id") final String hawkbitRolloutId,
                        @Param("roStatus") final String roStatus);

    @Select("<script> select R.rollout_id as rolloutId, UNIX_TIMESTAMP(R.rollout_time)*1000 as rolloutTime, R.hawkbit_rollout_id as hawkbitRolloutId, R.software_id as softwareId, R.software_version as softwareVersion, R.project_id as projectId, R.version_module_id as versionModuleId, R.user_id as userId, R.roStatus " +
            "from  rollout as R " +
            "where R.type_id = #{type_id} " +
            "<if test=\"user_id != null\"> and R.user_id = #{user_id} </if>" +
            "<if test=\"software_id != null\"> and software_id = #{software_id} </if>" +
            "<if test=\"software_version != null\"> and software_version = #{software_version} </if>" +
            "<if test=\"roStatus != null\"> and roStatus = #{roStatus} </if>" +
            "order by R.rollout_time desc " +
            "limit #{limit} offset #{limitOffset} </script>")
    List<Rollout> getRolloutList(@Param("software_id") final String softwareId,
                                 @Param("software_version") final String softwareVersion,
                                 @Param("user_id") final String userId,
                                 @Param("type_id") final int rolloutType,
                                 @Param("roStatus") final String roStatus,
                                 @Param("limit") final int limit,
                                 @Param("limitOffset") final int limitOffset);

    @Select("<script> select count(1)" +
            "from  rollout as R " +
            "where R.type_id = #{type_id} " +
            "<if test=\"user_id != null\"> and R.user_id = #{user_id} </if>" +
            "<if test=\"software_id != null\"> and software_id = #{software_id} </if>" +
            "<if test=\"software_version != null\"> and software_version = #{software_version} </if>" +
            "<if test=\"roStatus != null\"> and roStatus = #{roStatus} </if>" +
            "order by R.rollout_time desc " +
            "limit #{limit} offset #{limitOffset} </script>")
    int getRolloutCount(@Param("software_id") final String softwareId,
                       	@Param("software_version") final String softwareVersion,
                       	@Param("user_id") final String userId,
                       	@Param("type_id") final int rolloutType,
                        @Param("roStatus") final String roStatus,
                       	@Param("limit") final int limit,
                       	@Param("limitOffset") final int limitOffset);

    @Select("select R.rollout_id as rolloutId, UNIX_TIMESTAMP(R.rollout_time)*1000 as rolloutTime, R.hawkbit_rollout_id as hawkbitRolloutId, R.software_id as softwareId, R.software_version as softwareVersion, R.project_id as projectId, R.version_module_id as versionModuleId, R.user_id as userId " +
            "from rollout as R " +
            "where rollout_id = #{rollout_id} ")
    Rollout getRollout(@Param("rollout_id") final int rolloutId);
}