上篇文章提到过在JPA中调用Mysql分页的存储过程,经过查找资料,发现JPA中有@NamedStoredProcedureQuery注解来专门处理JPA对存储过程的调用,并包含了对存储过程中IN、OUT、INOUT参数的处理。现整合了一个 SpringBoot + JPA + fastJSON 的Demo,实现了对Mysql存储过程的调用。用fastJSON 纯属为了将返回的分页数据封装成类似JPA中的Page返回类型。
实体类:
@Entity
@Table(name = "people")
@NamedStoredProcedureQuery(
name = "proc_page",
procedureName = "proc_page",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "tab",type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "startItem",type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "pageSize",type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "sortRule",type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "order_field",type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT,name = "totalElements",type= Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT,name = "pageCount",type = Integer.class )})
public class people {
private int id;
private String name;
private String sex;
public people() {
}
public people(int id, String name, String sex) {
this.id = id;
this.name = name;
this.sex = sex;
}
@Id
@GeneratedValue
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
Service层:
@Service
public class ProcedureService {
@PersistenceContext
private EntityManager entityManager;
/***
* 模仿JPA中 返回值为Page类型 的参数类型,
* 封装成JSONObject 并返回
* @param code
* @return
*/
public JSONObject Page(JSONObject code) {
String tableName = code.getString("Name");
int start = code.getInteger("start");
int size = code.getInteger("size");
String order_by = code.getString("sort_Rule");
String order_field = code.getString("order_field");
/*此处参数需与实体类中对应*/
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("proc_page")
.setParameter("tab",tableName) .setParameter("startItem",start)
.setParameter("size",size) .setParameter("sortRule",order_by)
.setParameter("order_field",order_field);
List list = query.getResultList();
/*通过getOutputParameterValue方法获取存储过程中的OUT参数值*/
int pageCount = (int) query.getOutputParameterValue("pageCount");
int totalElements =(int) query.getOutputParameterValue("totalElements");
/*该处为了模仿 JPA 中 Page 对象返回的参数类型*/
if (null.equals (order_by) || ""== order_by){
order_by = "null";
}
/*将返回参数put进JSONObject,一并返回*/
JSONObject jsonObject = new JSONObject();
jsonObject.put("totalPage",pageCount);
jsonObject.put("totalElements",totalElements);
jsonObject.put("number",start);
jsonObject.put("size",size);
jsonObject.put("sort",order_by);
jsonObject.put("content",list); return jsonObject;
}
}
Controller层:
@Autowired private
ProcedureService service;
@RequestMapping(value = "/findAll",method = RequestMethod.POST)
public JSONObject findAll(@RequestBody JSONObject code){
Long start = System.currentTimeMillis();
JSONObject object = service.Page(code);
Long end = System.currentTimeMillis();
System.out.println("存储过程分页用时:" + (end - start) + "ms"); return object;
}
本人是用的Swagger进行测试的,同样的数据下,测试结果和JPA的分页速度相差无几,稍微快上那么一丢丢,5毫秒以内。至于该Demo中的存储过程,用的是上一篇博文中的。