PL / SQL是其中之一。
大多数人都试图避免这种情况。 很少有人真正喜欢它。 因为我在银行工作很多，所以我刚好患有斯德哥尔摩综合症。
即使PL / SQL语法和工具有时使我想起过去的美好时光……
“菲茨杰拉德，我们在追求布鲁因”。 我要倒带。” –“别牛，劳伦斯。 我们可以随时插入新的PL / SQL盒式磁带。”
公共领域的形象
…我仍然相信，过程语言（当然，任何一种语言）都可以与SQL结合使用，从而在生产率，性能和表达能力方面创造奇迹。
在本文中，我们稍后将看到如何使用jOOQ在Java中使用SQL（和PL / SQL）实现相同的功能 。
但是首先，一点点的历史…
从Java访问PL / SQL
Java开发人员特别避免编写自己的PL / SQL代码的最大原因之一是因为PL / SQL与Java之间的接口ojdbc是一个主要的难题。 我们将在以下示例中看到情况。
假设我们正在研究流行的Sakila数据库的Oracle端口 （ 最初是为MySQL创建的 ）。 该特定的Sakila / Oracle端口由DB Software Laboratory实施，并以BSD许可发布。
这是该Sakila数据库的部分视图。
使用vertabelo.com创建的ERD –了解如何在jOOQ中使用Vertabelo
现在，假设数据库中有一个API，该API不公开上述架构，而是公开一个PL / SQL API 。 该API可能看起来像这样：
CREATE TYPE LANGUAGE_T AS OBJECT (
language_id SMALLINT,
name CHAR(20),
last_update DATE
);
/
CREATE TYPE LANGUAGES_T AS TABLE OF LANGUAGE_T;
/
CREATE TYPE FILM_T AS OBJECT (
film_id int,
title VARCHAR(255),
description CLOB,
release_year VARCHAR(4),
language LANGUAGE_T,
original_language LANGUAGE_T,
rental_duration SMALLINT,
rental_rate DECIMAL(4,2),
length SMALLINT,
replacement_cost DECIMAL(5,2),
rating VARCHAR(10),
special_features VARCHAR(100),
last_update DATE
);
/
CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/
CREATE TYPE ACTOR_T AS OBJECT (
actor_id numeric,
first_name VARCHAR(45),
last_name VARCHAR(45),
last_update DATE
);
/
CREATE TYPE ACTORS_T AS TABLE OF ACTOR_T;
/
CREATE TYPE CATEGORY_T AS OBJECT (
category_id SMALLINT,
name VARCHAR(25),
last_update DATE
);
/
CREATE TYPE CATEGORIES_T AS TABLE OF CATEGORY_T;
/
CREATE TYPE FILM_INFO_T AS OBJECT (
film FILM_T,
actors ACTORS_T,
categories CATEGORIES_T
);
/ 您会立即注意到，在这种情况下，除了FILM_INFO_T类型起聚合作用外，这实际上只是模式的1：1副本，建模为Oracle SQL OBJECT和TABLE类型。
现在，我们的DBA（或我们的数据库开发人员）已经实现了以下API，供我们访问上述信息：
CREATE OR REPLACE PACKAGE RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
FUNCTION GET_ACTORS RETURN ACTORS_T;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
FUNCTION GET_FILMS RETURN FILMS_T;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/女士们，先生们，这就是现在的方式……
…繁琐地使用JDBC访问PL / SQL API
因此，为了避免笨拙的CallableStatement带有OUT参数注册和JDBC转义语法，我们将通过如下SQL语句来获取FILM_INFO_T记录：
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT rentals.get_film_info(1) FROM DUAL");
ResultSet rs = stmt.executeQuery()) {
// STRUCT unnesting here...
} 到目前为止，一切都很好。 幸运的是，有Java 7的try-with-resources可帮助我们清理那些无数的JDBC对象。 现在如何进行？ 我们将从这个ResultSet得到什么？ 一个java.sql.Struct ：
while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);
// And so on...
} 现在，您当中最勇敢的人将继续将java.sql.Struct转换为更加晦涩难懂的oracle.sql.STRUCT ，其中几乎不包含Javadoc，但包含大量不推荐使用的，特定于供应商的其他方法。
现在，让我们继续使用“标准API”。
插曲：
让我们花一点时间来了解Java 8时代的JDBC。
引入Java 5时，泛型也是如此。 我们重新编写了大型代码库，以删除现在不再需要的各种无意义的样板式转换。 除JDBC外。 当涉及到JDBC时，猜测适当的类型只是个运气。 我们通过按索引解引用元素来访问外部系统提供的复杂的嵌套数据结构，然后对结果数据类型进行疯狂的猜测。
Lambdas刚刚被引入，但是JDBC仍然与大型机对话。
隆达说，她把STRUCT放在阵列F-B4的插Kong73和75之间。 我想知道是否需要我的AC / DC转换器来插入它
公共领域的形象
然后…
龙在这里。 和结构
公共领域的原始图片
好的，这些杂物足够了。
让我们继续浏览STRUCT
while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);
Struct film_t = (Struct) film_info_t.getAttributes()[0];
String title = (String) film_t.getAttributes()[1];
Clob description_clob = (Clob) film_t.getAttributes()[2];
String description = description_clob.getSubString(1, (int) description_clob.length());
Struct language_t = (Struct) film_t.getAttributes()[4];
String language = (String) language_t.getAttributes()[1];
System.out.println("Film : " + title);
System.out.println("Description: " + description);
System.out.println("Language : " + language);
} 从我们在ResultSet位置1处收到的初始STRUCT中，我们可以继续按索引解引用属性。 不幸的是，我们将不断需要在Oracle（或某些文档）中查找SQL类型以记住属性的顺序：
CREATE TYPE FILM_INFO_T AS OBJECT (
film FILM_T,
actors ACTORS_T,
categories CATEGORIES_T
);
/ 事实并非如此！ 类型FILM_T的第一个属性是另一个嵌套STRUCT 。 然后，那些可怕的CLOB 。 上面的代码不是严格完成的。 在某些情况下，只有JDBC的维护者才能理解，必须调用java.sql.Clob.free()来确保及时释放资源。 请记住，取决于数据库和驱动程序配置， CLOB可能不在事务范围之内。
不幸的是，该方法称为free()而不是AutoCloseable.close() ，因此无法使用try-with-resources。 所以我们开始：
List<Clob> clobs = new ArrayList<>();
while (rs.next()) {
try {
Struct film_info_t = (Struct) rs.getObject(1);
Struct film_t = (Struct) film_info_t.getAttributes()[0];
String title = (String) film_t.getAttributes()[1];
Clob description_clob = (Clob) film_t.getAttributes()[2];
String description = description_clob.getSubString(1, (int) description_clob.length());
Struct language_t = (Struct) film_t.getAttributes()[4];
String language = (String) language_t.getAttributes()[1];
System.out.println("Film : " + title);
System.out.println("Description: " + description);
System.out.println("Language : " + language);
}
finally {
// And don't think you can call this early, either
// The internal specifics are mysterious!
for (Clob clob : clobs)
clob.free();
}
}就是这样 现在，我们在控制台上发现了一些不错的输出：
Film : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad
Scientist who must Battle a Teacher in
The Canadian Rockies
Language : English就是这样–您可能会想！ 但…
痛苦才刚刚开始
…因为我们还没有完成。 我们还需要从STRUCT反序列化两种嵌套表类型。 如果您还没有放弃（忍受我，好消息就在附近），您将很喜欢阅读有关如何获取和释放java.sql.Array 。 在胶卷印刷之后，让我们继续：
Array actors_t = (Array) film_info_t.getAttributes()[1];
Array categories_t = (Array) film_info_t.getAttributes()[2]; 同样，我们通过索引访问属性，我们必须记住这些索引，并且很容易破坏索引。 ACTORS_T数组不过是另一个包装的STRUCT ：
System.out.println("Actors : ");
Object[] actors = (Object[]) actors_t.getArray();
for (Object actor : actors) {
Struct actor_t = (Struct) actor;
System.out.println(
" " + actor_t.getAttributes()[1]
+ " " + actor_t.getAttributes()[2]);
}您会注意到一些事情：
-
Array.getArray()方法返回一个数组。 但是它声明返回Object。 我们必须手动投射。 - 即使这是一个明智的类型，我们也不能转换为
Struct[]。 但是ojdbc返回的类型是Object[]（包含Struct元素） - foreach循环也无法从右侧取消对
Struct引用。 没有办法将actor的类型强迫为我们所知道的 - 我们本可以使用Java 8和Streams等，但是不幸的是，所有可以传递给Streams API的lambda表达式都不允许抛出检查异常 。 JDBC会抛出检查异常。 那会更加丑陋。
无论如何。 现在我们终于实现了这一点，我们可以看到打印输出：
Film : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad
Scientist who must Battle a Teacher in
The Canadian Rockies
Language : English
Actors :
PENELOPE GUINESS
CHRISTIAN GABLE
LUCILLE TRACY
SANDRA PECK
JOHNNY CAGE
MENA TEMPLE
WARREN NOLTE
OPRAH KILMER
ROCK DUKAKIS
MARY KEITEL这种疯狂什么时候才能停止？
它会在这里停止！
到目前为止，本文读起来像是一篇教程（或者更像是中世纪的折磨），内容涉及如何将嵌套的用户定义类型从Oracle SQL反序列化为Java（不要再让我开始再次序列化它们！）
在下一节中，我们将看到如何使用jOOQ及其源代码生成器轻松实现完全相同的业务逻辑（列出ID为= 1的Film及其参与者）。 看一下这个：
// Simply call the packaged stored function from
// Java, and get a deserialised, type safe record
FilmInfoTRecord film_info_t = Rentals.getFilmInfo1(
configuration, new BigInteger("1"));
// The generated record has getters (and setters)
// for type safe navigation of nested structures
FilmTRecord film_t = film_info_t.getFilm();
// In fact, all these types have generated getters:
System.out.println("Film : " + film_t.getTitle());
System.out.println("Description: " + film_t.getDescription());
System.out.println("Language : " + film_t.getLanguage().getName());
// Simply loop nested type safe array structures
System.out.println("Actors : ");
for (ActorTRecord actor_t : film_info_t.getActors()) {
System.out.println(
" " + actor_t.getFirstName()
+ " " + actor_t.getLastName());
}
System.out.println("Categories : ");
for (CategoryTRecord category_t : film_info_t.getCategories()) {
System.out.println(category_t.getName());
}是吗
是!
哇，我的意思是，这就像所有这些PL / SQL类型和过程/函数实际上都是Java的一部分一样。 我们之前看到的所有警告都隐藏在这些生成的类型之后，并在jOOQ中实现，因此您可以专注于最初想要做的事情。 访问数据对象并对其进行有意义的工作。 不要序列化/反序列化它们！
让我们花点时间欣赏一下这种消费者广告：
还没说服？
我告诉过您不要让我开始将类型序列化为JDBC。 而且我不会，但是这是将类型序列化为jOOQ的方法，因为这真是小菜一碟！
让我们考虑另一种汇总类型，它返回客户的租赁历史记录：
CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
customer CUSTOMER_T,
films FILMS_T
);
/以及完整的PL / SQL软件包规格：
CREATE OR REPLACE PACKAGE RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
FUNCTION GET_ACTORS RETURN ACTORS_T;
FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T;
FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
FUNCTION GET_FILMS RETURN FILMS_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/ 因此，在致电RENTALS.GET_CUSTOMER_RENTAL_HISTORY我们可以找到客户曾经租借的所有电影。 让我们对所有FIRST_NAME为“ JAMIE”的客户进行此操作，这次，我们正在使用Java 8：
// We call the stored function directly inline in
// a SQL statement
dsl().select(Rentals.getCustomer(
CUSTOMER.CUSTOMER_ID
))
.from(CUSTOMER)
.where(CUSTOMER.FIRST_NAME.eq("JAMIE"))
// This returns Result<Record1<CustomerTRecord>>
// We unwrap the CustomerTRecord and consume
// the result with a lambda expression
.fetch()
.map(Record1::value1)
.forEach(customer -> {
System.out.println("Customer : ");
System.out.println("- Name : "
+ customer.getFirstName()
+ " " + customer.getLastName());
System.out.println("- E-Mail : "
+ customer.getEmail());
System.out.println("- Address : "
+ customer.getAddress().getAddress());
System.out.println(" "
+ customer.getAddress().getPostalCode()
+ " " + customer.getAddress().getCity().getCity());
System.out.println(" "
+ customer.getAddress().getCity().getCountry().getCountry());
// Now, lets send the customer over the wire again to
// call that other stored procedure, fetching his
// rental history:
CustomerRentalHistoryTRecord history =
Rentals.getCustomerRentalHistory2(dsl().configuration(), customer);
System.out.println(" Customer Rental History : ");
System.out.println(" Films : ");
history.getFilms().forEach(film -> {
System.out.println(" Film : "
+ film.getTitle());
System.out.println(" Language : "
+ film.getLanguage().getName());
System.out.println(" Description : "
+ film.getDescription());
// And then, let's call again the first procedure
// in order to get a film's actors and categories
FilmInfoTRecord info =
Rentals.getFilmInfo2(dsl().configuration(), film);
info.getActors().forEach(actor -> {
System.out.println(" Actor : "
+ actor.getFirstName() + " " + actor.getLastName());
});
info.getCategories().forEach(category -> {
System.out.println(" Category : "
+ category.getName());
});
});
});…以及上述内容产生的简短摘录：
Customer :
- Name : JAMIE RICE
- E-Mail : JAMIE.RICE@sakilacustomer.org
- Address : 879 Newcastle Way
90732 Sterling Heights
United States
Customer Rental History :
Films :
Film : ALASKA PHANTOM
Language : English
Description : A Fanciful Saga of a Hunter
And a Pastry Chef who must
Vanquish a Boy in Australia
Actor : VAL BOLGER
Actor : BURT POSEY
Actor : SIDNEY CROWE
Actor : SYLVESTER DERN
Actor : ALBERT JOHANSSON
Actor : GENE MCKELLEN
Actor : JEFF SILVERSTONE
Category : Music
Film : ALONE TRIP
Language : English
Description : A Fast-Paced Character
Study of a Composer And a
Dog who must Outgun a Boat
in An Abandoned Fun House
Actor : ED CHASE
Actor : KARL BERRY
Actor : UMA WOOD
Actor : WOODY JOLIE
Actor : SPENCER DEPP
Actor : CHRIS DEPP
Actor : LAURENCE BULLOCK
Actor : RENEE BALL
Category : Music如果您使用的是Java和PL / SQL ...
…然后，您应该单击下面的横幅并立即下载免费试用版，以试用jOOQ和Oracle：
根据BSD许可的条款，可从以下URL免费获得Sakila数据库的Oracle端口：
https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/Sakila/oracle-sakila-db
最后，是时候享受再次编写PL / SQL了！
翻译自: https://www.javacodegeeks.com/2014/11/painless-access-from-java-to-plsql-procedures-with-jooq.html
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
