Accessing PL/SQL from Java
One of the biggest reasons why Java developers in particular refrain from writing their own PL/SQL code is because the interface between PL/SQL and Java – ojdbc – is a major pain. We’ll see in the following examples how that is. Assume we’re working on an Oracle-port of the popular Sakila database (originally created for MySQL). This particular Sakila/Oracle port was implemented by DB Software Laboratory and published under the BSD license. Here’s a partial view of that Sakila database.
ERD created with vertabelo.com – learn how to use Vertabelo with jOOQ
Now, let’s assume that we have an API in the database that doesn’t expose the above schema, but exposes a PL/SQL API instead. The API might look something like this:
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
);
/
OBJECT and TABLE types, apart from the FILM_INFO_T type, which acts as an aggregate.
Now, our DBA (or our database developer) has implemented the following API for us to access the above information:
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;
/
… tediously access the PL/SQL API with JDBC
So, in order to avoid the awkward CallableStatement with itsOUT parameter registration and JDBC escape syntax, we’re going to fetch a FILM_INFO_T record via a SQL statement like this:
try (PreparedStatement stmt = conn.prepareStatement(
"SELECT rentals.get_film_info(1) FROM DUAL");
ResultSet rs = stmt.executeQuery()) {
// STRUCT unnesting here...
}
ResultSet? A java.sql.Struct:
while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);
// And so on...
}
java.sql.Struct to an even more obscure and arcane oracle.sql.STRUCT, which contains almost no Javadoc, but tons of deprecated additional, vendor-specific methods.
For now, let’s stick with the “standard API”, though.
Interlude:
Let’s take a moment to appreciate JDBC in times of Java 8.
When Java 5 was introduced, so were generics. We have rewritten our big code bases to remove all sorts of meaningless boilerplate type casts that are now no longer needed. With the exception of JDBC. When it comes to JDBC, guessing appropriate types is all a matter of luck. We’re accessing complex nested data structures provided by external systems by dereferencing elements by index, and then taking wild guesses at the resulting data types.
Lambdas have just been introduced, yet JDBC still talks to the mainframe.
Image in public domain
Original image in public domain
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);
}
STRUCT that we received at position 1 from the ResultSet, we can continue dereferencing attributes by index. Unfortunately, we’ll constantly need to look up the SQL type in Oracle (or in some documentation) to remember the order of the attributes:
CREATE TYPE FILM_INFO_T AS OBJECT (
film FILM_T,
actors ACTORS_T,
categories CATEGORIES_T
);
/
FILM_T is yet another, nested STRUCT. And then, those horrible CLOBs. The above code is not strictly complete. In some cases that only the maintainers of JDBC can fathom, java.sql.Clob.free() has to be called to be sure that resources are freed in time. Remember that CLOB, depending on your database and driver configuration, may live outside the scope of your transaction.
Unfortunately, the method is called free() instead of AutoCloseable.close(), such that try-with-resources cannot be used. So here we go:
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
That’s about it – You may think! But…
The pain has only started
… because we’re not done yet. There are also two nested table types that we need to deserialise from theSTRUCT. If you haven’t given up yet (bear with me, good news is nigh), you’ll enjoy reading about how to fetch and unwind a java.sql.Array. Let’s continue right after the printing of the film:
Array actors_t = (Array) film_info_t.getAttributes()[1];
Array categories_t = (Array) film_info_t.getAttributes()[2];
ACTORS_T array is nothing but yet another wrapped 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]);
}
- The
Array.getArray()method returns an array. But it declares returningObject. We have to manually cast. - We can’t cast to
Struct[]even if that would be a sensible type. But the type returned by ojdbc isObject[](containingStructelements) - The foreach loop also cannot dereference a
Structfrom the right hand side. There’s no way of coercing the type ofactorinto what we know it really is - We could’ve used Java 8 and Streams and such, but unfortunately, all lambda expressions that can be passed to the Streams API disallow throwing of checked exceptions. And JDBC throws checked exceptions. That’ll be even uglier.
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
When will this madness stop?
It’ll stop right here! So far, this article read like a tutorial (or rather: medieval torture) of how to deserialise nested user-defined types from Oracle SQL to Java (don’t get me started on serialising them again!) In the next section, we’ll see how the exact same business logic (listing Film with ID=1 and its actors) can be implemented with no pain at all using jOOQ and its source code generator. Check this out:
// 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());
}
Not convinced yet?
I told you not to get me started on serialising the types to JDBC. And I won’t, but here’s how to serialise the types to jOOQ, because that’s a piece of cake! Let’s consider this other aggregate type, that returns a customer’s rental history:
CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
customer CUSTOMER_T,
films FILMS_T
);
/
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 we can find all the films that a customer has ever rented. Let’s do that for all customers whose FIRST_NAME is “JAMIE”, and this time, we’re using 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
If you’re using Java and PL/SQL…
… then you should click on the below banner and download the free trial right now to experiment with jOOQ and Oracle:
The Oracle port of the Sakila database is available from this URL for free, under the terms of the BSD license:
https://www.jooq.org/sakila
Finally, it is time to enjoy writing PL/SQL again!
Looks nice – quick question though.
How does jOOQ come up with these ‘Rentals’ operations?
– getCustomer
– getCustomerRentalHistory2
– getFilmInfo2
Where does the mapping happen between this and the actual packaged functions that are exposed from the DB?
There’s a source code generator that reverse engineers your database meta data. In the case of Oracle, it will search ALL_OBJECTS for packages, functions, and procedures. There are also queries to ALL_COLL_TYPES to find out about VARRAY and TABLE types, as well as queries to ALL_TYPES to find out about OBJECT types. In the end, all of your PL/SQL code will have a Java API representation. This way, whenever you change your PL/SQL code, your Java API for it will automatically change as well, potentially leading to compilation errors, if your calling code is no longer correct.
Hope this helps,
Let me know if you have any additional questions, and I’ll be very happy to answer
Lukas