This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

The jOOQ User Manual. Multiple Pages : Meta model code generation : Procedures and packagesprevious : next

# Stored procedures in modern RDBMS

This is one of the most important reasons why you should consider jOOQ. Read also my article on dzone about why stored procedures become more and more important in future versions of RDMBS. In this section of the manual, we will learn how jOOQ handles stored procedures in code generation. Especially before UDT and ARRAY support was introduced to major RDBMS, these procedures tend to have dozens of parameters, with IN, OUT, IN OUT parameters mixed in all variations. JDBC only knows very basic, low-level support for those constructs. jOOQ heavily facilitates the use of stored procedures and functions via its source code generation. Essentially, it comes down to this:

# "Standalone" stored procedures and functions

Let's say you have these stored procedures and functions in your Oracle database 

-- Check whether there is an author in T_AUTHOR by that name
CREATE OR REPLACE FUNCTION f_author_exists (author_name VARCHAR2) RETURN NUMBER;

-- Check whether there is an author in T_AUTHOR by that name
CREATE OR REPLACE PROCEDURE p_author_exists (author_name VARCHAR2, result OUT NUMBER);

-- Check whether there is an author in T_AUTHOR by that name and get his ID
CREATE OR REPLACE PROCEDURE p_author_exists_2 (author_name VARCHAR2, result OUT NUMBER, id OUT NUMBER);

jOOQ will essentially generate two artefacts for every procedure/function:

Let's see what these things look like, in Java. The classes (simplified for the example): 

// The function has a generic type parameter <T> bound to its return value
public class FAuthorExists extends org.jooq.impl.AbstractRoutine<BigDecimal> {

    // Much like Tables, functions have static parameter definitions
    public static final Parameter<String> AUTHOR_NAME = // [...]

    // And much like TableRecords, they have setters for their parameters
    public void setAuthorName(String value) { // [...]
    public void setAuthorName(Field<String> value) { // [...]
}

public class PAuthorExists extends org.jooq.impl.AbstractRoutine<java.lang.Void> {

    // In procedures, IN, OUT, IN OUT parameters are all represented
    // as static parameter definitions as well
    public static final Parameter<String> AUTHOR_NAME = // [...]
    public static final Parameter<BigDecimal> RESULT = // [...]

    // IN and IN OUT parameters have generated setters
    public void setAuthorName(String value) { // [...]

    // OUT and IN OUT parameters have generated getters
    public BigDecimal getResult() { // [...]
}

public class PAuthorExists_2 extends org.jooq.impl.AbstractRoutine<java.lang.Void> {
    public static final Parameter<String> AUTHOR_NAME = // [...]
    public static final Parameter<BigDecimal> RESULT = // [...]
    public static final Parameter<BigDecimal> ID = // [...]

    // the setters...
    public void setAuthorName(String value) { // [...]

    // the getters...
    public BigDecimal getResult() { // [...]
    public BigDecimal getId() { // [...]
}

An example invocation of such a stored procedure might look like this: 

PAuthorExists p = new PAuthorExists();
p.setAuthorName("Paulo");
p.execute(configuration);
assertEquals(BigDecimal.ONE, p.getResult());

The above configuration is a Factory, holding a reference to a JDBC connection, as discussed in a previous section. If you use the generated convenience methods, however, things are much simpler, still: 

// Every schema has a single Routines class with convenience methods
public final class Routines {

    // Convenience method to directly call the stored function
    public static BigDecimal fAuthorExists(Configuration configuration, String authorName) { // [...]

    // Convenience methods to transform the stored function into a
    // Field<BigDecimal>, such that it can be used in SQL
    public static Field<BigDecimal> fAuthorExists(Field<String> authorName) { // [...]
    public static Field<BigDecimal> fAuthorExists(String authorName) { // [...]

    // Procedures with 0 OUT parameters create void methods
    // Procedures with 1 OUT parameter create methods as such:
    public static BigDecimal pAuthorExists(Configuration configuration, String authorName) { // [...]

    // Procedures with more than 1 OUT parameter return the procedure
    // object (see above example)
    public static PAuthorExists_2 pAuthorExists_2(Configuration configuration, String authorName) { // [...]
}

An sample invocation, equivalent to the previous example:

assertEquals(BigDecimal.ONE, Procedures.pAuthorExists(configuration, "Paulo"));

# jOOQ's understanding of procedures vs functions

jOOQ does not formally distinguish procedures from functions. jOOQ only knows about routines, which can have return values and/or OUT parameters. This is the best option to handle the variety of stored procedure / function support across the various supported RDBMS. For more details, read on about this topic, here:

blog.jooq.org/2011/10/17/what-are-procedures-and-functions-after-all/

# Packages in Oracle

Oracle uses the concept of a PACKAGE to group several procedures/functions into a sort of namespace. The SQL standard talks about "modules", to represent this concept, even if this is rarely implemented. This is reflected in jOOQ by the use of Java sub-packages in the source code generation destination package. Every Oracle package will be reflected by

Apart from this, the generated source code looks exactly like the one for standalone procedures/functions.

# Member functions and procedures in Oracle

Oracle UDT's can have object-oriented structures including member functions and procedures. With Oracle, you can do things like this: 

CREATE OR REPLACE TYPE u_author_type AS OBJECT (
  id NUMBER(7),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),

  MEMBER PROCEDURE LOAD,
  MEMBER FUNCTION count_books RETURN NUMBER
)

-- The type body is omitted for the example

These member functions and procedures can simply be mapped to Java methods: 

// Create an empty, attached UDT record from the Factory
UAuthorType author = create.newRecord(U_AUTHOR_TYPE);

// Set the author ID and load the record using the LOAD procedure
author.setId(1);
author.load();

// The record is now updated with the LOAD implementation's content
assertNotNull(author.getFirstName());
assertNotNull(author.getLastName());

For more details about UDT's see the Manual's section on User Defined Types


The jOOQ User Manual. Multiple Pages : Meta model code generation : Procedures and packagesprevious : next

Fork me on GitHub
The jOOQ Logo