java如何写查询服务器_后端开发 - 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 resultList = new ArrayList(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…(待续)