Question

jpa如何调用带out参数的存储过程

Answer 1

MySQL教程4 MySQL8运算符、函数、存储过程及新增数据类型 17.之创建带有IN和OUT参数的存储过程 学习猿地

Answer 2

案例：
实体类：

@Entity
@Table(name = "people")
@NamedStoredProcedureQuery(
name = "proc_page",
procedureName = "proc_page",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "tab",type = String.class),
@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "startItem",type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "size",type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.INOUT, 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 ),
@StoredProcedureParameter(mode = ParameterMode.OUT,name = "isFirst",type = String.class ),
@StoredProcedureParameter(mode = ParameterMode.OUT,name = "isLast",type = String.class )})

Service层：

@PersistenceContext
private EntityManager entityManager;
public JSONObject Page(JSONObject code){ //模仿JPA中 返回值为Page类型 的参数类型，封装成JSONObject 并返回

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();
query.execute();

//通过getOutputParameterValue方法获取存储过程中的OUT参数值
int pageCount = (int) query.getOutputParameterValue("pageCount");
int number = (int) query.getOutputParameterValue("startItem");
int Size = (int) query.getOutputParameterValue("size");
String sort = (String) query.getOutputParameterValue("sortRule");
int totalElements =(int) query.getOutputParameterValue("totalElements");
String isFirst = (String) query.getOutputParameterValue("isFirst");
String isLast = (String) query.getOutputParameterValue("isLast");
if (sort.equals (null) || sort == ""){ //该处纯属为了模仿 JPA 中 Page 返回的参数类型
sort = "null";
}

//将返回参数put进JSONObject，一并返回
JSONObject jsonObject = new JSONObject();
jsonObject.put("totalPage",pageCount);
jsonObject.put("totalElements",totalElements);
jsonObject.put("number",number);
jsonObject.put("size",Size);
jsonObject.put("sort",sort);
jsonObject.put("isFirst",isFirst);
jsonObject.put("isLast",isLast);
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;

}