This article shows different ways to call stored procedures from hibernate.
JDBC actions will not be covered.
Postgresql will be used as a database.
CREATE DATABASE example;
CREATE TABLE company (
company_id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE benefit (
benefit_id BIGSERIAL PRIMARY KEY,
name TEXT,
company_id BIGINT,
CONSTRAINT fk_company FOREIGN KEY (company_id)
REFERENCES company (company_id) MATCH SIMPLE
);
CREATE TABLE employee (
employee_id BIGSERIAL PRIMARY KEY,
first_name TEXT,
last_name TEXT,
company_id BIGINT,
CONSTRAINT fk_company FOREIGN KEY (company_id)
REFERENCES company (company_id) MATCH SIMPLE
);
CREATE TABLE employee_benefit (
employee_id BIGINT,
benefit_id BIGINT,
CONSTRAINT fk_employee FOREIGN KEY (employee_id)
REFERENCES employee (employee_id) MATCH SIMPLE ,
CONSTRAINT fk_benefit FOREIGN KEY (benefit_id)
REFERENCES benefit (benefit_id) MATCH SIMPLE
);
INSERT INTO company (name) VALUES ('TestCompany');
INSERT INTO employee (first_name, last_name, company_id) VALUES ('Emmanouil','Gkatziouras',1);
INSERT INTO benefit (name,company_id) VALUES ('gym',1);
INSERT INTO benefit (name,company_id) VALUES ('lunch',1);
Our postgresql function will return a set of employee benefits
CREATE OR REPLACE FUNCTION add_all_company_benefits(employeeId BIGINT,companyid BIGINT)
RETURNS TABLE(benefit_id BIGINT,name TEXT,company_id BIGINT) AS $$
DECLARE benefitid BIGINT;
BEGIN
FOR benefitid IN (SELECT benefit.benefit_id FROM benefit WHERE benefit.company_id=companyid) LOOP
IF (SELECT COUNT(*) FROM employee_benefit as eb
WHERE eb.employee_id=employeeid
AND eb.benefit_id=benefitid) = 0
THEN
INSERT INTO employee_benefit (employee_id, benefit_id)
VALUES (employeeId,benefitId);
END IF;
END LOOP;
RETURN QUERY
SELECT benefit.benefit_id,benefit.name,benefit.company_id FROM benefit
INNER JOIN employee_benefit ON employee_benefit.benefit_id = benefit.benefit_id
WHERE employee_benefit.employee_id=employeeId;
END;
$$ LANGUAGE plpgsql;
The entity mappings using JPA annotations follow
The company entity.
package com.gkatzioura.example.entity;
import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;
@Entity
@Table(name = "company")
public class Company {
@Id
@GeneratedValue
@Column(name = "company_id")
private Long Id;
@Column
String name;
@OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,mappedBy = "company")
private Set<Benefit> benefits = new HashSet<Benefit>();
public Long getId() {
return Id;
}
public void setId(Long id) {
Id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Benefit> getBenefits() {
return benefits;
}
public void setBenefits(Set<Benefit> benefits) {
this.benefits = benefits;
}
}
The employee entity.
package com.gkatzioura.example.entity;
import javax.persistence.*;
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue
@Column(name = "employee_id")
private Long id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@ManyToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
@JoinColumn(name = "company_id",referencedColumnName = "company_id")
private Company company;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Company getCompany() {
return company;
}
public void setCompany(Company company) {
this.company = company;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
}
The benefit entity.
package com.gkatzioura.example.entity;
import javax.persistence.*;
@Entity
@Table(name = "benefit")
public class Benefit {
@Id
@GeneratedValue
@Column(name = "benefit_id")
private Long id;
@Column(name = "name")
private String name;
@ManyToOne
@JoinColumn(name = "company_id")
private Company company;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Company getCompany() {
return company;
}
public void setCompany(Company company) {
this.company = company;
}
}
Our first approach would be by calling the procedure as a hibernate sql query.
Session session = sessionFactory.openSession();
List<Company> companies = session.createCriteria(Company.class)
.add(Restrictions.eq("name", companyName))
.list();
List<Employee> employees = session.createCriteria(Employee.class)
.add(Restrictions.eq("firstName",employeeName))
.list();
for(Company company:companies) {
for(Employee employee:employees) {
LOGGER.info("The company is "+company.getId()+" the employee is "+employee.getId());
SQLQuery query = (SQLQuery) session.createSQLQuery("SELECT*FROM add_all_company_benefits(:employeeId,:companyId)")
.addEntity(Benefit.class)
.setParameter("employeeId", employee.getId())
.setParameter("companyId", company.getId());
List result = query.list();
for(Integer i=0;i<result.size();i++) {
LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
}
}
}
session.close();
We will update the company entity by adding a NameQuery in the company entity.
@Entity
@Table(name = "company")
@NamedNativeQueries({
@NamedNativeQuery(
name = "AddAllCompanyBenefits",
query = "SELECT*FROM add_all_company_benefits(:employeeId,:companyId)",
resultClass = Benefit.class
)
})
public class Company {
}
Our hibernate actions will be refactored to
Query query = session.getNamedQuery("AddAllCompanyBenefits")
.setParameter("employeeId", employee.getId())
.setParameter("companyId", company.getId());
List result = query.list();
for(Integer i=0;i<result.size();i++) {
LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
}
Another way is to use hibernate’s ProcedureCall which is used on hibernate’s implementation of JPA’s StoredProcedureQuery
ProcedureCall procedureCall = session.createStoredProcedureCall("add_all_company_benefits");
procedureCall.registerParameter("EMPLOYEE", Long.class, ParameterMode.IN);
procedureCall.registerParameter("COMPANY", Long.class, ParameterMode.IN);
procedureCall.getParameterRegistration("EMPLOYEE").bindValue(employee.getId());
procedureCall.getParameterRegistration("COMPANY").bindValue(company.getId());
ProcedureOutputs procedureOutputs = procedureCall.getOutputs();
ResultSetOutput resultSetOutput = (ResultSetOutput) procedureOutputs.getCurrent();
List results = resultSetOutput.getResultList();
for(Integer i=0;i<results.size();i++) {
Object[] objects = (Object[]) results.get(i);
LOGGER.info("The benefit is "+objects[1]);
}
Emmanouil Gkatziouras 