How to call an Oracle PL/SQL function from a Java Hibernate application

While the Hibernate framework for Java handles basic SQL (as HQL) calls, and can also deal with Oracle PL/SQL procedures, it doesn't have a method for directly calling a PL/SQL function, and retrieving the return value. Here is an approach that appears to work.

Notice that, instead of the createQuery() method I have used the createSQLQuery() method. This allows me to use a PL/SQL call, instead of being restricted to HQL. The advantages of this approach is that it lets me reference Oracle's DUAL table without receiving an unmapped error, and it also lets me use a prepared statement to map in my parameters, thus trapping out SQL injection attacks.

The downside is that, because it is using a PL/SQL query instead of an HQL query, it is not DBMS agnostic, and so not portable.

 

---

-- Oracle PL/SQL function
CREATE OR REPLACE FUNCTION get_some_string_fnc (
    p_argument IN INTEGER
)
RETURN VARCHAR2
IS
BEGIN
    v_result VARCHAR2(64) := NULL
    -- Do some stuff
    RETURN(v_result);
END;

---

/*
 * Java code in DAO object
 * Hibernate does not handle calling functions very simply,
 * so this bit is Oracle PL/SQL specific, with the DUAL reference,
 * which requires using createSQLQuery() instead of the more
 * portable createQuery() that uses HQL.
 */
String funString = "SELECT get_some_string_fnc(:p_argument) FROM DUAL";
Query funCall = session.createSQLQuery( funString );
int argument = 1234;
String funRslt;
try {
    funCall.setParameter( "p_argument", argument );
    funRslt = (String)funCall.uniqueResult();
} catch ( HibernateException he ) {
    log.error( he.toString() );
    funRslt = "unavailable";
}

Nobody is singing the Aria.

The lack of clear and readily available documentation for support of Section 508, WAI-ARIA, and other accessibility standards in the various JSF libraries, such as ICEfaces and PrimeFaces, is shocking, and really pretty shameful.

Given that the support of user with various disabilities is not only the morally and ethically right thing to do, but in many cases is actually required by law in the US and most other countries, it points to a lack of interest by those privileged to not have to deal with those disabilities.

I suppose that means me, too.

Viewing fake composites, or compositing fake views

Using Hibernate to retrieve data from a database view that contains a join can be tricky, as Hibernate expects there to be a unique primary key. The trick is to fake a composite key using the @Embeddable annotation in a PK model class that contains the properties/@Column references chosen for the composite key, and a the @EmbeddedId annotation in the main model class to include the PK class as the id property.

There's lots of documentation in cyberspace on the syntax for that, but there's one piece that almost all of them miss, and that is how you actually reference the properties that are defined in the @Embeddable compound key class.

First, do not duplicate the properties in both models; they should only occur in the PK model. The PK class is defined as a property of the main model class, so the reference is model.id.property for each element of the CK, instead of model.property.

I created accessors in the main model for the key properties that test that there is an instance of the PK model defined, primarily for testing. Example:

    public String getFirstName() {
        if ( null == this.id ) {
            return null;
        }
        return this.id.getFirstName();
    }

    public void setFirstName(String firstName) {
        if ( null == this.id ) {
            this.id = new ErmsDocOrgPrsnDetailPK();
        }
        this.id.setFirstName( firstName );
    }

It remains to be seen whether those will actually be needed.

Java object cloning

A note to myself from 15 August 2014:

To quickly clone an java object, including all the children and relationships, use SerializationUtils in Apache Commons Lang:

MyObject newObject = (MyObject) SerializationUtils.clone(objectToClone);

(http://www.kianworknotes.com/2014/08/how-to-quickily-clone-object-in-java.html)