后端开发小技巧 （五）

后端开发小技巧 （五）

1. Mapper层 一些常用的SQL语句

1.1查询当天的记录+条数

    <select id="selectToday" resultType="java.lang.Integer">
        SELECT *
        FROM 表
        WHERE to_char(create_time, 'YYYY-MM-DD HH24:MI:SS') = to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS')
    </select>

    <select id="selectToday" resultType="java.lang.Integer">
        SELECT COUNT(*)
        FROM 表
        WHERE to_char(create_time, 'YYYY-MM-DD HH24:MI:SS') = to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS')
    </select>

1.2 查询最近10条记录

    <select id="selectlately" resultType="cn.piesat.sar.entity.TaskInfo">
        SELECT 字段
        FROM 表  ORDER BY create_time DESC  LIMIT 10 offset 0
    </select>

1.3 查询最近3天的记录

  <select id="selectday" resultType="cn.piesat.sar.entity.TaskInfo">
   select * from sar_task_workflow_relation 
where to_date(create_time,'YYYY-MM-DD HH24:MI:SS') between (now() - interval '3 Days') and now() 
</select>

1.4 查询最近一周的记录

  <select id="selectweek" resultType="cn.piesat.sar.entity.TaskInfo">

select * from sar_task_workflow_relation 
where to_date(create_time,'YYYY-MM-DD HH24:MI:SS') between (now() - interval '1 Weeks') and now() 
</select>

2. mybatis include refid="Base_Column_List"含义

<sql id="Base_Column_List" >
        collegeID, collegeName
</sql> 
 
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
       select 
        <include refid="Base_Column_List" />
        from t_notification_template
       where id = #{id,jdbcType=BIGINT}
 </select>

这个在MyBatis查询数据库的sql中经常会出现。它的在上面已经定义，作用相当于 *** ，**

Base_Column_List是固定的几个字段，而用*号的话会降低查询效率，因为后期数据库的字段会不断增加。

本文链接：https://blog.youkuaiyun.com/weixin_40569991/article/details/88724739

3. 多条件查询 实现拦截参数 ServiceImpl

  @Override
    public ServiceResult taskPlanConditionList(Map<String, Object> paramMap) {

        ServiceResult serviceResult = new ServiceResult(false);
//        if(null==paramMap || paramMap.size()==0){
//            serviceResult.setMessage("查询数据为空");
//            return serviceResult;
//        }
        if(null!=paramMap.get("pageNum")&&null!=paramMap.get("pageSize")) {
            Integer pageNum = Integer.parseInt(paramMap.get("pageNum").toString());
            Integer pageSize = Integer.parseInt(paramMap.get("pageSize").toString());
            if (!StringUtils.isEmpty(pageNum) && !StringUtils.isEmpty(pageSize)) {
                pageNum = pageSize * (pageNum - 1);
            }
            paramMap.put("pageNum", pageNum);
        }
        Integer total = mapper.queryTaskPlanConditionCount(paramMap);
        List<Map<String, Object>> dataModelList = mapper.queryTaskPlanConditionList(paramMap);
        Map<String, Object> result = new HashMap<>();

//        去重 java8 新特性
//        start 去重记录
//        List<Map<String, Object>> unique = dataModelList.stream().distinct().collect(Collectors.toList());
//        System.out.println(unique.toString());
//        end

        if(dataModelList.size()>0 && null!= dataModelList){
            result.put("list", dataModelList);
            result.put("total", total);
            serviceResult.setSuccess(true);
            serviceResult.success(result,"查找成功",0);
        }else {
            serviceResult.success(result,"查找失败",-1);
        }
        return serviceResult;
    }