JPA中调用含有IN、OUT、INOUT参数的存储过程

  上篇文章提到过在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中的存储过程，用的是上一篇博文中的。