In this year’s Java Advent Calendar, we’re thrilled to have been asked to feature a mini-series showing you a couple of advanced and very interesting topics that we’ve been working on when developing jOOQ.
The series consists of:
- Dec 17: How jOOQ Leverages Generic Type Safety in its DSL
- Dec 18: How jOOQ Allows for Fluent Functional-Relational Interactions in Java 8
- Dec 19: How jOOQ Helps Pretend that Your Stored Procedures are a Part of Java
Don’t miss any of these!
How jOOQ helps pretend that your stored procedures are a part of Java
This article was originally published fully on the jOOQ blog
Stored procedures are an interesting way to approach data processing. Some Java developers tend to stay clear of them for rather dogmatic reasons, such as:
- They think that the database is the wrong place for business logic
- They think that the procedural aspect of those languages is ill-suited for their domain
But in practice, stored procedures are an excellent means of handling data manipulations simply for the fact that they can execute complex logic right where the data is. This completely removes all effects that network latency and bandwidth will have on your application, otherwise. As we’re looking into supporting SAP HANA for jOOQ 3.6, we can tell you that running jOOQ’s 10000 integration test queries connecting from a local machine to the cloud takes a lot longer. If you absolutely want to stay in Java land, then you better also deploy your Java application into the cloud, close to that database (SAP HANA obviously offers that feature). But much better than that, move some of the logic into the database!
If you’re doing calculations on huge in-memory data sets, you should better get your code into that same memory, rather than shuffling around memory pieces between possibly separate physical memory addresses. Companies like Hazelcast essentially do the same, except that their in-memory database is written in Java, so you can also write your “stored procedure” in Java.
With SQL databases, procedural SQL languages are king. And because of their tight integration with SQL, they’re much superior for the job than any Java based stored procedure architecture.
I knoow, but JDBC’s CallableStatement… Arrrgh!
Yes. As ever so often (and as mentioned before in our previous articles, one very important reason why many Java developers don’t like working with SQL is JDBC. Binding to a database via JDBC is extremely tedious and keeps us from working efficiently. Let’s have a look at a couple of PL/SQL binding examples:
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
);
/
You’ll notice immediately, that this is essentially just a 1:1 copy of the schema in this case modelled as Oracle SQL 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;
/
This, ladies and gentlemen, is how you can now…
… tediously access the PL/SQL API with JDBC
So, in order to avoid the awkward CallableStatement with its OUT 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...
}
So far so good. Luckily, there is Java 7’s try-with-resources to help us clean up those myriad JDBC objects. Now how to proceed? What will we get back from this ResultSet? A java.sql.Struct:
while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);
// And so on...
}
Now, the brave ones among you would continue downcasting the 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. Let’s continue navigating our 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);
}
This could go on and on. The pain has only started, we haven’t even covered arrays yet. The details can be seen here in the original article.
Anyway. Now that we’ve finally achieved this, we can see the print output:
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());
}
Is that it?
Yes!
Wow, I mean, this is just as though all those PL/SQL types and procedures / functions were actually part of Java. All the caveats that we’ve seen before are hidden behind those generated types and implemented in jOOQ, so you can concentrate on what you originally wanted to do. Access the data objects and do meaningful work with them. Not serialise / deserialise them!
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
);
/
And the full PL/SQL package specs:
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;
/
So, when calling 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());
});
});
});
… and a short extract of the output produced by the above:
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://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/Sakila/oracle-sakila-db
Finally, it is time to enjoy writing PL/SQL again!
And things get even better!
jOOQ is free and Open Source for use with Open Source databases, and it offers commercial licensing for use with commercial databases. So, if you’re using Firebird, MySQL, or PostgreSQL, you can leverage all your favourite database’s procedural SQL features and bind them easily to Java for free!
For more information about jOOQ or jOOQ’s DSL API, consider these resources:
- The jOOQ documentation page
- The Java Fluent API Designer Crash Course
- A webinar with Arun Gupta from Red Hat about jOOQ and JavaEE
- A presentation about jOOQ at GeeCON Krakow
That’s it with this year’s mini-series on jOOQ. Have a happy Holiday season!
This post is part of the Java Advent Calendar and is licensed under the Creative Commons 3.0 Attribution license. If you like it, please spread the word by sharing, tweeting, FB, G+ and so on!
Author: Lukas Eder
Founder and CEO at Data Geekery, the company behind jOOQ
steinhauer.software