Call Stored Procedure with JPA 2.1
詳細
JPA 2.1 introduces APIs to call Stored Procedure. It has similar programming pattern as executing a common CRUD sql statement.
@NamedStoredProcedureQuery: defines details of the stored proc to call
@StoredProcedureParameter: defines IN/OUT parameters of the stored proc
StoredProcedureQuery: Interface used to control stored procedure query execution
For more example: http://www.baeldung.com/jpa-stored-procedures
JPA 2.1 introduces APIs to call Stored Procedure. It has similar programming pattern as executing a common CRUD sql statement.
@NamedStoredProcedureQuery: defines details of the stored proc to call
@StoredProcedureParameter: defines IN/OUT parameters of the stored proc
StoredProcedureQuery: Interface used to control stored procedure query execution
-- create a function
CREATE OR REPLACE FUNCTION test1.process_upload(p1 text, p2 integer) RETURNS integer AS
$$
DECLARE
arow record;
counter integer := 0;
BEGIN
FOR arow IN (SELECT * FROM test1.buffer_table)
LOOP
-- process this row
counter = counter + 1;
END LOOP;
RETURN counter;
END;
$$ LANGUAGE plpgsql;
-- test the function
select test1.process_upload('jwang', 10);
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@Entity
@Table(name = "buffer_table", schema = "test1")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
name="processUpload", // query name used in your app
procedureName = "process_upload", // name of stored proc in db
//resultClasses = {BufferTable.class},
parameters = {
@StoredProcedureParameter(name="p1", type=String.class, mode=ParameterMode.IN),
@StoredProcedureParameter(name="p2", type=Integer.class, mode=ParameterMode.IN)
}
)
})
public class BufferTable implements Serializable {
@Id
@Column(name="id")
private Integer id;
@Column(name="name")
private String name;
@Column(name="created")
private Date created;
@Column(name="department_name")
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
}
public int processUpload() {
StoredProcedureQuery spQuery = em.createNamedStoredProcedureQuery("processUpload");
// use named param - positional param can be used as well.
spQuery.setParameter("p1", "jwang");
spQuery.setParameter("p2", 10);
List results = spQuery.getResultList();
Integer result = results.size() > 0 ? (Integer) results.get(0) : null;
log.info("Result from executing stored proc: " + result);
return result != null ? result.intValue() : 0;
}
For more example: http://www.baeldung.com/jpa-stored-procedures