SpringDataJpa 多表动态Sql模糊查询

sql

 

@Query(value = " SELECT" +
            " wu.*,wa.sn wa_sn,wh.sn wh_sn,wc.sn wc_sn,wd.sn wd_sn,wp.sn wp_sn,wa.act_type wa_act_type,wc.work_type wc_act_type,wd.work_type wd_act_type,wh.act_type wh_act_type,wp.work_type wp_act_type,wa.start_time wa_start_time,wa.end_time wa_end_time,wa.working_still wa_working_still,wh.start_time wh_start_time,wh.end_time wh_end_time,wh.working_still wh_working_still" +
            "                FROM wx_user wu " +
            "                LEFT JOIN wx_actor wa  ON wa.openid = wu.openid" +
            "                LEFT JOIN wx_direct wd ON wd.openid = wu.openid" +
            "                LEFT JOIN wx_hunter wh ON wh.openid = wu.openid" +
            "                LEFT JOIN wx_company wc ON wc.openid = wu.openid" +
            "                LEFT JOIN wx_provide wp ON wp.openid = wu.openid " +
            "                WHERE 1=1" +
            "                and IF ( ?1 != '', wu.role_type = ?1, 1=1) " +
            "                and IF ( ?2 != '', wu.sex = ?2, 1=1) " +
            "                and IF ( ?3 != '', wu.phone like %?3%, 1=1) " +
            "                and IF ( ?4 != '', wu.real_name like %?4%, 1=1) " +
            "                and IF ( ?5 != '', wu.wx_account like %?5%, 1=1)" +
            "                and IF ( ?6 != '', wu.create_time > ?6, 1=1)  " +
            "                and IF ( ?7 != '', wu.create_time < ?7, 1=1)  order by wu.create_time desc " ,nativeQuery = true)

mapper

List<Map<String,Object>> queryAllRolesInfo(Integer roleType, Long sex, String phone, String realName, String wxAccount,String startTime,String endTime);

service

   List<Map<String,Object>> queryAllRolesInfo(Integer roleType, Long sex, String phone, String realName, String wxAccount,String startTime,String endTime);

serviceImpl

   @Override
    public List<Map<String, Object>> queryAllRolesInfo(Integer roleType, Long sex, String phone, String realName, String wxAccount, String startTime, String endTime ) {
        List<Map<String, Object>> maps = tuserRepository.queryAllRolesInfo(roleType, sex, phone, realName, wxAccount,startTime,endTime);
        return maps;
    }

controller

    @GetMapping("/allRoles/info")
    public ApiResponse queryAllRolesInfo(Integer roleType, Long sex, String phone, String realName, String wxAccount,String startTime,String endTime ) {
        Map map = new HashMap();
        List<Map<String, Object>> maps = userService.queryAllRolesInfo(roleType, sex, phone, realName, wxAccount, startTime,endTime);
        map.put("list",maps);
        return ApiResponse.ok(map);
    }