一、 MyBatis分页查询与多条件查询:(日期区间查询等)
1、导入Mybatis分页插件助手依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
2、Mapper接口
1.1、这里三个参数是查询条件
1.2、startDate和endDate是日期区间查询,开始时间和结束时间区间查询
//根据条件查询全部数据
List<HistoricalAlarm> getQuery(@Param("waterName") String waterName, @Param("startDate") String startDate,@Param("endDate") String endDate);
3、Mybatis
3.1、这里主要以第一个条件查询为主
3.2、第二个是查询是根据名字和日期查询,日期查询就不需要模糊查询了,准确查询
3.3、这里主要展示给大家看怎么写,也方便我记忆
<!--根据条件查询数据-->
<select id="getQuery" resultType="HistoricalAlarm">
select * from historical_alarm
<where>
<trim prefixOverrides="and|or">
<if test="waterName !=null and waterName !=''">
<![CDATA[ alarmPoint like CONCAT('%',#{waterName},'%')]]>
</if>
<if test="startDate !=null and startDate !=''">
and alarmDate <![CDATA[>=]]> #{startDate}
</if>
<if test="endDate !=null and endDate !=''">
and alarmDate <![CDATA[<=]]> #{endDate}
</if>
</trim>
</where>
</select>
<!--条件查询-->
<select id="getQuery" resultType="VideoStatistics">
select * from video_statistics
<where>
<trim prefixOverrides="and|or">
<if test="videoName !=null and videoName !=''">
<![CDATA[ and videoName like CONCAT('%',#{videoName},'%')]]>
</if>
<if test="uploadDate!=null and uploadDate!=''">
<![CDATA[ and uploadDate = #{uploadDate}]]>
</if>
</trim>
</where>
</select>
4、Service接口
//根据条件查询全部数据
PageInfo<HistoricalAlarm> getQuery(Integer pageNum,Integer pageSize,String waterName, String startDate,String endDate);
5、ServiceImpl实现类
/**
* 分页查询及条件查询
* @param pageNum
* @param pageSize
* @param waterName
* @param startDate
* @param endDate
* @return
*/
@Override
public PageInfo<HistoricalAlarm> getQuery(Integer pageNum, Integer pageSize, String waterName, String startDate, String endDate) {
//分页
PageHelper.startPage(pageNum,pageSize);
//条件查询
List<HistoricalAlarm> queryList = historcalAlarmMapper.getQuery(waterName, startDate, endDate);
//封装条件查询
PageInfo<HistoricalAlarm> pageInfo =new PageInfo<>(queryList);
return pageInfo;
}
6、Controller层
/**
*
* @param pageNum
* @param pageSize
* @param startDate
* @return alarmDate
*/
@GetMapping("/historAlarm")
public RespBean historAlarm(@RequestParam(defaultValue = "1")Integer pageNum, @RequestParam(defaultValue = "10")Integer pageSize, String waterName,String startDate,String endDate){
PageInfo<HistoricalAlarm> querylist = historcalAlarmService.getQuery(pageNum, pageSize, waterName, startDate, endDate);
System.out.println("结果为:"+querylist);
try {
if (querylist !=null){
return RespBean.ok("查询成功",querylist);
}
} catch (Exception e) {
e.printStackTrace();
}
return RespBean.error("查询失败");
}
这里就不测试了
二、 Mybatis批量删除
1、Mapper接口
//根据id批量删除数据
int delByIds(@Param("ids") Integer[] ids);
2、Mybatis
<!--根据Id批量删除数据-->
<delete id="delByIds" parameterType="int">
delete from role where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
3、Service接口
//根据id批量删除数据
int delByIds(Integer[] ids);
4、ServiceImpl实现类
/**
* 根据Id删除数据
* @param id
* @return
*/
@Override
public int delById(Integer id) {
int count = authorityManageMapper.delById(id);
return count;
}
5、Controller层
/**
* 批量删除
* @return
*/
@DeleteMapping("/delByIds/{ids}")
public RespBean delByIds(@PathVariable("ids") Integer[] ids){
int count = authorityManageService.delByIds(ids);
System.out.println(count);
try {
if (count>1){
return RespBean.ok("批量删除成功","共"+count+"条受影响");
}
} catch (Exception e) {
e.printStackTrace();
}
return RespBean.error("批量删除失败");
}
这里就不测试了