This article show 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]);
}