后端根据前端时间筛选数据及mybatis里sql写法以及数组传参及本月本周本年数据查询

最新推荐文章于 2022-12-07 17:04:29 发布

权是

最新推荐文章于 2022-12-07 17:04:29 发布

阅读量1k

点赞数 2

文章标签： mysql java

本文链接：https://blog.csdn.net/gradonisis/article/details/115721109

版权

本文介绍了Java后端如何根据前端传来的日期参数进行数据筛选，展示了使用MyBatis框架编写SQL查询语句的详细过程。在控制器层，接收并格式化日期参数，然后调用DAO层的方法。DAO层中，定义了接收日期参数的方法，并在Mapper文件中编写动态SQL，根据条件进行查询。此外，还给出了处理数组参数的示例，以及针对特定日期范围获取数据的SQL查询。

摘要由CSDN通过智能技术生成

后端根据前端时间筛选数据及mybatis里sql写法以及数组传参

后段代码

后段代码

时间字段可用Date 修饰
如：Date startTime；
Date endTime；
控制器的写法：

 @ApiOperation("我的收藏")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "policyTitle", value = "政策标题", dataTypeClass = String.class),
            @ApiImplicitParam(name = "startTime", value = "开始时间", dataTypeClass = Date.class),
            @ApiImplicitParam(name = "endTime", value = "终止时间", dataTypeClass = Date.class),
            @ApiImplicitParam(name = "page", value = "页数", dataTypeClass = Integer.class),
            @ApiImplicitParam(name = "pageSize", value = "每页行数", dataTypeClass = Integer.class)
    })
    @GetMapping("getMyCollect")
    public ResponseModel<CommonPage<PolMyInfoDTO>> getMyCollect(@RequestParam(required = false) String policyTitle, @RequestParam(required = false)  @DateTimeFormat(pattern = "yyyy-MM-dd")Date startTime,
                                                                @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd")                         
                                                                Date endTime, @RequestParam Integer page,
                                                                @RequestParam Integer pageSize){
        String format=null;
        String format1=null;
        if(startTime !=null ) {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            format = simpleDateFormat.format(startTime);
        }
        if(endTime !=null){
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            format1 = simpleDateFormat.format(endTime);
        }
        CommonPage<PolMyInfoDTO> myPolPage = pageUtil.page(polInfoDao.showMyCol(policyTitle,  startTime,endTime, page, pageSize));
        return ResponseModel.success(myPolPage);
    }

Dao层的写法:

 List<PolInfo> showMyCol(String policyTitle, String policyStartTime,String policyEndTime);

mapper里sql写法：

<select id="showMyCol" resultType="com.nbpi.company.modules.pol.model.po.PolInfo" >
        SELECT *
        FROM pol_info a
        <where>
            and a.id=#{id}
            <if test="policyTitle !=null and policyTitle !=''">
                and a.policy_title like concat('%',#{policyTitle},'%')
            </if>
            <if test="policyStartTime !=null and policyStartTime !=''">
                AND DATE_FORMAT(a.policy_start_time,"%Y-%m-%d") >#{policyStartTime}
            </if>
            <if test="policyEndTime !=null and policyEndTime !=''">
                AND  #{policyEndTime}>DATE_FORMAT(a.policy_end_time,"%Y-%m-%d")
            </if>
            and a.is_del=false
        </where>
        ORDER BY a.id DESC
    </select>

当参数为数组时:

dao:
 List<TrainActivity> showMy(String name,Integer status,String startTime,String endTime,String type,Integer[] ids);

sql:
<select id="showMy" resultType="com.nbpi.company.modules.train.model.po.TrainActivity" >
        SELECT
        <include refid="proCompany"/>
        FROM train_activity a
        <where>
            <if test="name !=null and name !=''">
                AND a.activity_name LIKE concat('%',#{name},'%')
            </if>
            <if test="type !=null and type !=''">
                AND a.train_type LIKE concat('%',#{type},'%')
            </if>
            <if test="status !=null and status !=''">
                AND a.activity_status =#{status}
            </if>
            <if test="startTime !=null and startTime !=''">
                AND DATE_FORMAT(a.activity_start_time,"%Y-%m-%d") >=#{startTime}
            </if>
            <if test="endTime !=null and endTime !=''">
                AND  #{endTime}>=DATE_FORMAT(a.activity_end_time,"%Y-%m-%d")
            </if>
            <if test="ids!=null and ids !=''">
                and a.id in
                <foreach collection="ids" item="area" index="index" open="(" close=")" separator=",">
                    #{area}
                </foreach>
            </if>


        </where>
        ORDER BY a.id DESC
    </select>

获取本周数据

 <select id="findWeek" resultType="integer">
        select count(id)
        from train_activity_per
        where sign_status ='已报名'
        and WEEKOFYEAR(sign_time)=WEEKOFYEAR(NOW()) ;
    </select>

获取本月数据

  <select id="findMonth" resultType="integer">
        select count(id)
        from train_activity_per
        where sign_status ='已报名'
        and date_format(sign_time,'%Y-%m')=date_format(now(),'%Y-%m')
    </select>

获取本年数据

<select id="findYear" resultType="integer">
        select count(id)
        from train_activity_per
        where sign_status ='已报名'
        and date_format(sign_time,'%Y')=date_format(now(),'%Y')
    </select>