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);
}