Importing Stored Procedures in Apache Cayenne

Executive Summary

Add to the cayenne plugin's dbImport section in your pom.xml:

<includeProcedure>Procedure_Name</includeProcedure>

Run your maven commands:

mvn cayenne:cdbimport
mvn cayenne:cgen

The second should result in:

Add procedure Procedure_Name

You can use your procedure via:

// Returns a ProcedureResult
ProcedureCall
.query("Procedure_Name")
.param("param_name", param_value)
.call(context); // The previously created context.

To return a particular class:

ProcedureCall
.query("Procedure_Name",ModelClass.class)
.param("param_name", param_value)
.call(context); // The previously created context.

To return a List of your particular class:

ProcedureCall
.query("Procedure_Name",ModelClass.class)
.param("param_name", param_value)
.call(context) // The previously created context.
.firstList();

Backstory

I'm currently using Apache Cayenne for a project I'm working on for a friend. It's been really useful so far, but a couple days ago I realized it wasn't automatically importing my new stored procedure. Although there's code for calling procedures, I was having a hard time ensuring my procedure was imported when I ran the cdbimport/cdbgen commands. Additionally, I was having a really hard time finding resources/examples.

In the end I ended up using "ol' reliable"- ctrl + space. After stepping through the sub-sections in pom.xml's cayenne plugin section I found one tag named <includeProcedure>. Adding it to the <dbImport> section is what finally did the trick:

<build>
    <plugins>
            <!-- Other entries... -->
        <plugin>
             <groupId>org.apache.cayenne.plugins</groupId>
            <artifactId>cayenne-maven-plugin</artifactId>
            <version>${cayenne.version}</version>
            <configuration>
                <!-- Other entries... -->
                <dbImport>
                    <defaultPackage>com.example</defaultPackage>
                    <includeProcedure>Procedure_Name</includeProcedure>
                </dbImport>
            </configuration>
                    <!-- Other entries... -->
        </plugin>
    </plugins>
</build>

After making this change, run your cayenne cdbimport/cgen commands again and you will be able to use the Procedure like so:

ProcedureCall
.query("Procedure_Name")
.param("param_name", param_value)
.call(context); // The previously created context.

There's more to the ProcedureCall than just this example, so be sure to check out the Executive Summary above and its Javadoc.

Hopefully this will save someone some time in the future.