查数据 带条件的查并且 返回list

风险事件 Spring Boot整合MabatisPlus

Controller层

@RestController
@RequestMapping("/riskevent")
@Api(value = "/riskevent", description = "风险事件管理")
public class SEventController extends MpBaseController {
    @Autowired
    private SEventService eventService;
    /**
     * 获取 风险事件的list集合
     * @param eventDto
     * @return
     */
    @RequiresPermissions("scyf:riskevent:list")
    @GetMapping("/list")
    @ApiOperation(value ="/list", notes="风险事件分页查询")
    public TableDataInfo getEventList(@ApiParam(value = "筛选条件" )EventDto eventDto){
        Page<EventDto> page = (eventService.selectSEvent(getPage(),eventDto));
        return getDataTableByPage(page);
    }
}

Service层

public interface SEventService extends IService<SEvent> {
    /**
     * 查 风险事件
     * @param eventDto
     * @return
     */
    Page<EventDto> selectSEvent(Page page,EventDto eventDto);
}

@Service
public class SEventServiceImpl extends ServiceImpl<SEventMapper,SEvent> implements SEventService{
    @Autowired
    private SEventMapper eventMapper;
    @Autowired
    protected Validator validator;

    @Override
    public Page<EventDto> selectSEvent(Page page, EventDto eventDto) {
        String eventName = eventDto.getEventName();
        String[] params = {};
        if(eventName != null && !"".equals(eventName)){
            params = eventName.split(" ");
        }
        return eventMapper.selectSEventList(page,params);
    }
}

Mapper

@Param的作用就是给参数命名，比如在mapper里面某方法A（int id），当添加注解后A（@Param("userId") int id），也就是说外部想要取出传入的id值，只需要取它的参数名userId就可以了。将参数值传如SQL语句中，通过#{userId}进行取值给SQL的参数赋值。

public interface SEventMapper extends BaseMapper<SEvent> {


    /**
     * 根据条件 分页查询
     * @param page
     * @param params
     * @return
     */

    Page<EventDto> selectSEventList(IPage<EventDto> page,@Param("params") String[] params);
}

Mapper.xml

风险事件和风险单元是1：n的关系 所以用到association标签

<mapper namespace="com.ruoyi.mapper.SEventMapper">

    <resultMap type="com.ruoyi.scyf.api.domain.dto.EventDto" id="SysSEventResult">
        <id     property="eventId"       column="event_id"      />
        <result property="eventName"       column="event_name"      />
        <result property="eventStatus"        column="event_status"        />
        <result property="createTime"     column="create_time"    />
        <result property="createBy"     column="create_by"    />
        <result property="updateTime"     column="update_time"    />
        <result property="updateBy"     column="update_by"    />
        <result property="remark"     column="remark"    />
        <association property="sUnit" javaType="com.ruoyi.scyf.api.domain.SUnit">
            <id property="unitId" column="unit_id"></id>
            <result property="riskUnitName" column="risk_unit_name"></result>
            <result property="riskUnitId" column="risk_unit_id"></result>
            <result property="qrCode" column="qr_code"></result>
            <result property="longitude" column="longitude"></result>
            <result property="latitude" column="latitude"></result>
            <result property="xCoordinate" column="x_coordinate"></result>
            <result property="yCoordinate" column="y_coordinate"></result>
            <result property="addr" column="addr"></result>
            <result property="position" column="position"></result>
            <result property="safetyWarningSigns" column="safety_warning_signs"></result>
            <result property="post" column="post"></result>
            <result property="sign" column="sign"></result>
            <result property="assTime" column="ass_time"></result>
            <result property="exaTime" column="exa_time"></result>
            <result property="createBy" column="create_by"></result>
            <result property="createTime" column="create_time"></result>
            <result property="updateBy" column="update_by"></result>
            <result property="updateTime" column="update_time"></result>
        </association>
    </resultMap>
        <select id="selectSEventList" resultMap="SysSEventResult">
            select a.event_id,a.event_name,
            a.event_status,a.update_time,
            a.create_time,a.create_by,a.update_by,
            b.risk_unit_name
            from s_event a left join s_unit b on a.event_unit = b.unit_id where event_status =0
            <if test="params.length > 0 and params != null">
                and (
                <foreach collection="params" item="eventName" separator="or">
                    a.event_name like concat('%', #{eventName}, '%')
                </foreach>

                <foreach collection="params" item="eventStatus" open="or" separator="or">
                    a.event_status like concat('%', #{eventStatus}, '%')
                </foreach>
                <foreach collection="params" item="riskUnitName" open="or" separator="or">
                    b.risk_unit_name like concat('%', #{riskUnitName}, '%')
                </foreach>
                <foreach collection="params" item="name" open="or" separator="or">
                    date_format(a.create_time,'%Y-%m-%d')  like concat('%', #{name}, '%')
                </foreach>
                <foreach collection="params" item="name" open="or" separator="or">
                    date_format(a.update_time,'%Y-%m-%d')  like concat('%', #{name}, '%')
                </foreach>
                          )
             </if>
            order by  a.event_id desc
        </select>

当xml中使用 <if> 或者<foreach>标签时 可以在使用这些标签之前 添加 where 1 = 1

Eg:

<select id="getRiskUnitListLike" resultMap="SUnitResult">
        <include refid="all"></include>
        where 1 = 1
        <if test="query != null and query.length >0">
            and (
            <foreach collection="query" item="name" separator="or">
                t1.risk_unit_name like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t3.risk_name like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t3.dept_name like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t3.user_name like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t1.addr like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t1.position like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t1.update_time like concat('%', #{name}, '%')
            </foreach>
            <foreach collection="query" item="name"  open="or" separator="or">
                t2.class_name like concat('%', #{name}, '%')
            </foreach>
            )
        </if>
    </select>

where 1 = 1 的作用是：

当 不满足 if 条件时，可能会出现 不满足情况的现象，这个时候 如果不添加这条语句 会出现SQL错误，所以平时使用的时候 可以添加该条语句。