Spring data JPA calls stored procedures to process return parameters and result sets

First, the environment

1. This essay is based on the spring data JPA project

2. Database for MySQL version 5.7.9

Second, the content

1. Create a new stored procedure Pro_query_object

BEGIN

#Routine body goes here...a_theme_code varchar (), out Num int

Select o.obj_code,o.obj_name,o.obj_id from Qt_object o where O.theme_code=a_theme_code;

Select COUNT (*) into num from Qt_object o where O.theme_code=a_theme_code GROUP by O.theme_code;

END

2. Create a new entity, first define the entity field of the result set that we need to return, and then add the @namedstoredprocedurequeries annotation bound stored procedure

• @Entity
  @NamedStoredProcedureQueries ({
  //Management list
  @NamedStoredProcedureQuery (name = "Pro_general_list", procedurename = "Pro_general_list",
  resultclasses = {Qtobject.class},
  parameters = {
  @StoredProcedureParameter (mode = parametermode.in, name = "A_theme_code", type = String.class ),
  @StoredProcedureParameter (mode = parametermode.out, name = "num", type = Integer.class)//Record Total number of bars that meet the conditions
                   }),
  })
  Public class Qtobject {
  @Id
  @Column (name = "obj_id")
  private String objId;
  private String Obj_code;
  private String obj_name;
  //Omit get, set here
  }
  
  here @NamedStoredProcedureQueries can write multiple stored procedures, using "," separated,

@NamedStoredProcedureQuery中The procedurename parameter is the name of the stored procedure in the database; the name parameter is the moniker of the stored procedure in JPA, and the resultclasses parameter is the entity name that returns the result set bindings (processing result set important parameters);  中使用 Storedprocedureparameter to define the in, out parameters used by the stored procedure。

完成实体后，我们在编写调用方法

Call a stored procedure
Public Storedprocedurequery Callstore (String themecode) {
Storedprocedurequery store = this.entityManager.createNamedStoredProcedureQuery ("Pro_general_list");
Store.setparameter ("A_theme_code", Themecode);
Store.execute ();
return store;
}

When you call a stored procedure, you need to inject the entity manager Entitymanager, call the Createnamedstoredprocedurequery method in it, pass in the JPA name of the stored procedure, and then just pass in the in parameter. Returns the Storedprocedurequery object after execution.

4. Result processing

Public   resultinfo queryinitgeneral (String themecode) {
resultinfo<list> resultinfo = new resultinfo<list> ();
storedprocedurequery storedprocedurequery= callstore (start_date, End_date,themecode,deptcode,obj_name,start_ Num,end_num);
integer nums = (integer) storedprocedurequery.getoutputparametervalue ("num");
resultinfo.setrows (Storedprocedurequery.getresultlist ());
resultinfo.settotal (nums);
Resultinfo.setresult (1);
resultinfo.setmsg (" query succeeded! ");
 return resultinfo;
}

The Getresultlist () method in Storedprocedurequery can be used to return the result set after the execution of the stored procedure (where the result set is one, many have not been experimented), and then uses the Storedprocedurequery The Getoutputparametervalue () method can return an out parameter

Spring data JPA calls stored procedures to process return parameters and result sets