My love for stored procedures

When I first started coding Oracle PL/SQL for my employer, I realised that I found myself back in medieval ages. A syntax reminding me of Ada and Pascal, ultra-strong typing, compilers that find compilation errors about 100'000 lines later, etc etc. I was young (OK I still am), full of visions and ideas, so this was about as hard as reality could get. Especially since I have friends working over at the Scala labs in Lausanne, Switzerland... developing for the "future", not the "past". I guess, today I would have joined this facebook group.

But I haven't. Actually, I have started to completely change my mind about those archaic pieces of logic deep down in the database. They're very much alive! In our company, we're using stored procedures as a means of interfacing various systems with our huge database (several billions of records in some tables). Actually, the database has become a middleware between our own software components. On one server (or from a script, etc) we call a stored procedure with a UDT message parameter. The procedure updates some tables and puts the UDT in an Oracle AQ. The AQ is consumed by another server. While this is not a "J2EE standard" architecture, it works very well and efficiently.

Stored procedure evolution

Check out this hilariously angry article from 2004. People have furiously hated stored procedures for a long time and with a lot of passion, and they still do today. But the stored procedures aren't gone. Au contraire, they're even more important today, as databases tend to hold more and more data that cannot be handled on the business tier as far as many operations are concerned. Check out "newer" databases such as MySQL, Postgres, H2, HSQLDB, etc. They all ship with their own stored procedure languages. So it's not just about the "archaic", "evil" monoliths provided by Oracle, IBM (DB2), or Microsoft.

So, where is this going?

In the IT-business, we live in a fast-paced world. None of us actually knows where we're going in the next 10 years. I'm not here to discuss that overly religious topic. Maybe I'm wrong about the stored procedure business. But I love them for their performance and closeness to the data they manipulate. And I think I share this love with many of you out there. The same applies for SQL. I'm not here to discuss the object-relational impedance mismatch either and whose fault it is etc... It's a boring and over-rated discussion. After all, we don't know where these things are heading either.

jOOQ

Instead I'm focusing on something else. I recently published an article on dzone and on the server side about jOOQ. I had lots of interesting, constructive, and motivating feedback, and a first committer! In the meantime, we have established a sound support for stored procedures (and UDT's) that integrate well into jOOQ's DSL. Because one of the most tiresome tasks when using stored procedures, is to call and map them from a higher-level language such as Java and JDBC.

Generate PROCEDURES as methods, PACKAGES as packages, UDTs as classes

Like many other persistence tools, jOOQ ships with code generation. It will also generate classes for your stored procedures and for your UDT's. Check out this PL/SQL example package:

CREATE OR REPLACE PACKAGE library AS

    -- A procedure checking the existence of an author and
    -- providing the result as an OUT parameter
    PROCEDURE p_author_exists (author_name VARCHAR2, result OUT NUMBER);

    -- A function checking the existence of an author and
    -- providing the result as a RETURN value
    FUNCTION f_author_exists (author_name VARCHAR2) RETURN NUMBER;
END library;

And its generated (simplified) Java representation:

public final class Library {

  // Invoke the stored procedure on a connection. The return value
  // represents the OUT parameter
.
  public static BigDecimal pAuthorExists(
    Connection connection,
    String authorName)
  throws SQLException { ... }

  // Invoke the stored function on a connection
  public static BigDecimal fAuthorExists(
    Connection connection,
    String authorName)
  throws SQLException { ... }

  // Use the stored function as a Field in jOOQ's query DSL
  public static Field<BigDecimal> fAuthorExists(String authorName) { ... }

  // Use the stored function as a Field taking another field as parameter
  public static Field<BigDecimal> fAuthorExists(Field<String> authorName) { ... }
}

Apart from calling stored functions and procedures directly from your Java application, you can also embed them in a jOOQ query like this:

// Let's say PERSONS is a table holding FIRST_NAME and LAST_NAME fields
// Then the following code will use those fields as generated Java objects
Factory create = new Factory(connection);
Field<BigDecimal> isAuthor = Library.fAuthorExists(LAST_NAME).as("is_author");

// Find persons born in 1981 and check whether they are authors
Result<?> result = create
  .select(FIRST_NAME, LAST_NAME, isAuthor)
  .from(PERSONS)
  .where(YEAR_OF_BIRTH.equal(1981)).fetch();

for (Record record : result) {
  System.out.println(
    record.getValue(LAST_NAME) + " is an author : " +
    record.getValue(isAuthor));
}

You can embed the stored function wherever you can in SQL, i.e. in SELECT clauses, in ORDER BY clauses, in GROUP BY clauses. For example, you filter the authors directly in the database, not in your Java code:

Result<?> result = create
  .select(FIRST_NAME, LAST_NAME)
  .from(PERSONS)
  .where(YEAR_OF_BIRTH.equal(1981))
  .and(Library.fAuthorExists(LAST_NAME).equal(1))
  .fetch();

Now if you have UDT's in your stored procedure (which is a lot more common than having UDT's in tables), then you can have jOOQ map them easily to the Java world for you as well. Let's say you have this UDT:

CREATE TYPE u_address_type AS OBJECT (
  street u_street_type, -- A nested UDT!
  zip VARCHAR2(50),
  city VARCHAR2(50),
  country VARCHAR2(50)
)

And this UDT is used in the following standalone (i.e. not in a package) stored procedure:

CREATE OR REPLACE PROCEDURE p_check_address
(address IN OUT u_address_type);

Then, these pieces of Java code (simplified for the example) are generated for you:

public class UAddressTypeRecord extends UDTRecordImpl<UAddressTypeRecord> {

  // The nested UDT is referenced
  public UStreetTypeRecord getStreet();
  public String getZip();
  public String getCity();
  public String getCountry();
}

And you will be able to communicate with your database using code similar to the following one:

// Create the nested UDT structure
UAddressTypeRecord address = new UAddressTypeRecord();
UStreetTypeRecord street = new UStreetTypeRecord();
street.setStreet("Bahnhofstrasse");
street.setNo("1");
address.setStreet(street);
address.setZip("8001");
address.setCity("Zurich");
address.setCountry("Switzerland");

// Pass the UDT as a parameter to the database. Note how the OUT parameter
// is mapped as a return value to the method. If you have several OUT parameters
// A value object containing all qualified parameters is generated and returned
UAddressTypeRecord resultingAddress = Procedures.pCheckAddress(address);

Conclusion

In the early days of jOOQ, I thought I was creating an alternative to Hibernate or JPA, which are excellent tools, but they are OR-mappers (see the introduction about religious persistence beliefs). Since I'm in love with SQL, I don't want any mapping to the OO-world. (I'm also in love with OO, that's why it's called j-OO-Q).

But in the mean time, I have realised that jOOQ is something entirely different. It can go side-by-side with Hibernate, JPA, or other tools, providing much ease of access to your beloved (or hated) database functionality in the way you're used to in Java. With code generation, access to vendor-specific constructs becomes as easy as it can be. You can call your stored procedures as if they were regular Java methods, without knowing the details about JDBC's advanced features. You can make your database an integral part of your application. You can make stored procedures fun! Heck, you can even change your mind like I did! :-)

Check back on jOOQ for future versions, with more features and databases to come. And maybe you'll commit the odd patch to integrate that beloved database-feature of yours in jOOQ!