Call Stored Procedures with Hibernate and PostgreSQL

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