Статьи

Используйте хранимые процедуры с Hibernate

Январь 7, 2019

В этой статье показаны различные способы вызова хранимых процедур из спящего режима. Действия JDBC не будут покрыты. Postgresql будет использоваться в качестве базы данных.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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);

Наша функция postgresql вернет набор вознаграждений работникам:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
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;

Ниже приведены сопоставления сущностей с использованием аннотаций JPA:

Субъект компании

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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;
    }
}

Сотрудник юридического лица

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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;
    }
}

Субъект выгоды

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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;
    }
}

Наш первый подход заключается в вызове процедуры в виде спящего SQL-запроса.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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();

Мы обновим сущность компании, добавив NameQuery в сущность компании.

01
02
03
04
05
06
07
08
09
10
11
@Entity
@Table(name = "company")
@NamedNativeQueries({
        @NamedNativeQuery(
                name = "AddAllCompanyBenefits",
                query = "SELECT*FROM add_all_company_benefits(:employeeId,:companyId)",
                resultClass = Benefit.class
        )
})
public class Company {
}

Наши спящие действия будут реорганизованы для:

1
2
3
4
5
6
7
8
9
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());
}

Другой способ заключается в использовании затраченного процедурой hibernate, которая используется в реализации hibernate для JPA StoredProcedureQuery:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
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]);
}
Ссылка: Используйте хранимые процедуры с Hibernate от нашего партнера JCG Эммануила Гкатциоураса в блоге gkatzioura .
54
Share: