使用 Hibernate 和 PostgreSQL 调用存储过程

本文展示了从 Hibernate 调用存储过程的不同方法。

不包括 JDBC 操作。

PostgreSQL 将用作数据库。

 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 函数将返回一组员工福利

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

使用 JPA 注释的实体映射如下

公司实体。

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

雇员实体。

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

受益实体。

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

我们的第一种方法是将过程作为休眠 sql 查询来调用。

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

我们将通过在公司实体中添加 NameQuery 来更新公司实体。

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

我们的休眠动作将被重构为

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

另一种方法是使用 hibernate 的 ProcedureCall，它用于 hibernate 对 JPA 的 StoredProcedureQuery 的实现

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