异常教程
使用 Hibernate 和 PostgreSQL 调用存储过程
💡一则或许对你有用的小广告
欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论
- 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于
Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;- 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;
截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2800+ 小伙伴加入学习 ，欢迎点击围观
本文展示了从 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);