Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ

Posted on by lukaseder
The current edition of the Java magazine has an article about Big Data Best Practices for JDBC and JPA by Josh Juneau: http://www.javamagazine.mozaicreader.com/MayJune2016 The article shows how to use a stored procedure with JDBC (notice how resources aren’t closed, unfortunately. This is commonly forgotten, even in Java Magazine articles) 

// Using JDBC to call upon a database stored
// procedure
CallableStatement cs = null;
try {
    cs = conn.prepareCall("{call DUMMY_PROC(?,?)}");
    cs.setString(1, "This is a test");
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.executeQuery();

    // Do something with result
    String returnStr = cs.getString(2);
} catch (SQLException ex){
    ex.printStackTrace();
}
And with JPA: 

// Utilize JPA to call a database stored procedure
// Add @NamedStoredProcedureQuery to entity class
@NamedStoredProcedureQuery(
    name="createEmp", procedureName="CREATE_EMP",
    parameters = {
        @StoredProcedureParameter(
            mode= ParameterMode.IN,
            type=String.class,
            name="first"),
        @StoredProcedureParamter(
            mode = ParameterMode.IN,
            type=String.class,
            name="last")
    })

// Calling upon stored procedure
StoredProcedureQuery qry =
    em.createStoredProcedureQuery("createEmp");
qry.setParameter("first", "JOSH");
qry.setParameter("last","JUNEAU");
qry.execute();
Specifically the latter was also recently discussed in blog posts by Vlad Mihalcea and Thorben Janssen.

Do you like verbosity and complexity?

No? We neither. This is why we give you a third option instead: Just use jOOQ. Here’s the equivalent jOOQ code: 

// JDBC example:
String returnStr = Routines.dummyProc(
    config, "This is a test");

// JPA example
Routines.createEmp(config, "JOSH", "JUNEAU");
Yes! That’s it. Don’t waste time manually configuring your bind variables with JDBC API calls, or JPA annotations. No one likes writing annotations for stored procedures. With jOOQ and jOOQ’s code generator, procedure calls are:
  • A one-liner
  • A no-brainer
  • A way to bring back the fun to stored procedures
Learn more about using Oracle stored procedures with nested collections and object types here:
Painless Access from Java to PL/SQL Procedures with jOOQ

Published by lukaseder

I made jOOQ

10 thoughts on “Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ”

  3. Well, in this case, complexity is a strength rather than a weakness: at least the JPA example is complex enough to warrant an explanation in a magazine.

    That certainly wouldn’t happen to the JOOQ one-liners :P

    Reply

  4. OK, but you’re cheating by hiding the code generation overhead. This is what allows your “one liners”…
    Also you could compare to Spring templates which are easier and safer than plain JDBC, and more readable than JPA annotations.

    Reply

    1. Why is it cheating? With the other technologies, you have to always manually type that glue code somehow. There are no code generators yet (to be fair, jOOQ is a JDBC code generator).

      OK, I will eventually compare to Spring templates. And then, we’ll do some really sophisticated Oracle stored procedure usage with procedures returning nested tables of objects and call methods on those objects. Let’s see how spring templates will fare :)

      Reply

  5. In all good faith I wanted to add that (in a JEE context) in case a stored proc / function parameter
    is an SQL custom type/struct (hello PostgreSQL!), being so because that makes SQL life easier,
    understandable, explainable and (imho) more elegant, and even if said custom type is a lean one,
    consisting of standard SQL types, then good luck, sweat, pain and tears implementing all the mapping
    for shoving that down the throat of JPA/JDBC.
    Leave aside that probably we need to extend upon the underlying JPA implementation in order to instruct
    it about the custom types, so byebye app server interchangeability.
    My warm thank you Lukas for this jewel. If you ever find yourself in my hometown mikasa sukasa :-)

    @KPNC: Cheating at build/compile time where the machine is instructed to do some work
    that will produce artifacts for our runtime sanity and elegance (and probably performance too,
    it usually comes along the others), well, that is one hell of a “cheat” and I am COMMITing it :-D
    Seriously though, I think there is ROI when we automate protocol and protocol glue code interactions.
    (in a stretch(?) of its meaning I consider that the Java/app JDBC RDBMS interaction
    has the characteristics of a protocol, a rich/complex one for sure so thanx again Lukas!).
    Its the same with what we can do with Swagger where we can automate the generation of JAX-RS HTTP interfaces (@POST, @GET etc.) and the beans/DTOs we exchange over the HTTP protocol,
    all out of a single source of truth YAML or JSON file where we define our app’s protocol usage.

    Reply

    1. so byebye app server interchangeability.

      Did you seriously consider that a feature? :) I mean, this interchangeability was always a myth, both on the app server side as well as the JVM side or the SQL side. People are losing out on so many great feature because they limit themselves to use only the least common denominator of them all, i.e. some 5%.

      Reply

      1. Seriously, interchangeability has passed my mind but then, fortunately soon enough, reality kicked in and straightened things out :-)
        I’d say that not only are people missing out on great features but they consume more resources and probably introduce more bugs on their way without them.
        Instead commit with one JEE server (or commit with Spring or whatever Java stack fits the bill anyways) and fully exploit their capabilities. Same with the DB.
        Hm, the Linux kernel may be interchangeable :-)

        Reply

Leave a Reply