后端开发 - Java实现服务端复杂查询的几种方法

1.使用动态拼接SQL查询

    /**
     * 查询 数据
     * @return
     * @throws ParseException
     */
    @RequestMapping("/getTableAList")
    @ResponseBody
    public JsonResult getTableAList(@RequestBody String json, HttpSession session){
        JSONObject jsonObject = JSON.parseObject(json);//解析json
        String voucherDate = jsonObject.getString("voucherDate");
        String sortTag = jsonObject.getString("sortTag");
        StringBuilder sb = new StringBuilder(" select distinct t.* " +
                "from table_a a " +
                "right join table_b b" +
                "on a.id = b.a_id" +
                "where a.name = " +
                GetUserUtils.GetUserUnitId(session));
        String[] strArr = voucherDate.split(",");
        //拼接字符串
        if (strArr[0] != null && !strArr[0].equals(" ")) {
            sb.append(" and t.creat_time >= '" + strArr[0] + "'");
        }
        if (strArr[1] != null && !strArr[1].equals(" ")) {
            sb.append(" and t.creat_time <= '" + strArr[1] + "'");
        }
        //排序方式
        if (sortTag != null && !sortTag.equals("")){
            if (!sortTag.equals("sortTime")){
                sb.append(" order by t.creat_time DESC");
            }else{
                sb.append(" order by t.voucher_code  DESC ");
            }
        }
                
        Query query = entityManager.createNativeQuery(sb.toString(),VoucherTotal.class);
        Pageable pageable = new PageRequest(Integer.valueOf(pageNumber) - 1, Integer.valueOf(   pageSize));
        query.setFirstResult(pageable.getPageSize() * pageable.getPageNumber());
        query.setMaxResults(pageable.getPageSize());
        //获取查询结果的个数
        Integer count = queryAll.getResultList().size();
        //将查询到的结果放入列表中
        List<VoucherTotal> resultList = new ArrayList<VoucherTotal>(query.getResultList());
        Integer count = queryAll.getResultList().size();
        return JsonResult.success(count, "查询成功", resultList);
    }

2. 使用存储结构

存储结构的优势是速度快，因为sql是编译好的，只要加上查询的字段就可以了

MySQL的存储过程大概长这样：

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    #Routine body goes here...
    DECLARE c int;
    if a is null then set a = 0; 
    end if;
    if b is null then set b = 0;
    end if;
    set sum  = a + b;
END

调用存储结构的方法

    /**
     * EntityManager的查询方法 
     * @return
     */
    @RequestMapping("/demoForProc/{a}/{b}")
    @ResponseBody
    public JsonResult demoForProc(@PathVariable Integer a,@PathVariable Integer b){
        Query query = entityManager.createNativeQuery("{call proc_adder(?,?)}");
        query.setParameter(1, a);
        query.setParameter(2, b);
        Integer result=(Integer) query.getSingleResult();
        entityManager.close();
        return JsonResult.success(result);
    }

Jpa调用存储结构的方法略显复杂这里就不写了

3…（待续）