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";
}

Bang Important

When using a framework that in some cases does weirdly nested stuff that overrides your CSS (I'm looking at you, ICEFaces), the CSS directive !important can be your friend. But maybe it's the sort of friend you need to keep a close eye on.

This is something it took me days to find documentation for, and certainly could cause a maintenance headache, but it falls into the the good-to-know category.

Case in point: with an ICEFaces ace:dataTable, displaying multiple rows from a database query, the table would not display the full number of rows, even though it was sufficiently large to do so, because the <div> that contains all the rows, class name ui-datatable-scrollable-body, was getting its max-height set to 100px, no matter what I did. This was enough to show three of four rows. I was finally able to get it to work with this CSS entry:

.ui-datatable-scrollable-body { max-height:250px!important; }

I may have to do some further tweaking so as not to torch any other datatables I may use elsewhere in the application, but at least now I can move forward. And I'll keep glancing over my shoulder, making sure this helper doesn't try to stab me in the back.

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.

SOP

What do you call it when the person knowledgeable on an legacy technology retires, there is not a replacement hired yet, and then an internal customer requests enhancements involving that technology and some other legacy systems?

Oh, yea, and the development environment for that legacy system has gone AWOL? And then your manager's manager very publicly put you in charge of dealing with it?

Oh, yea, business as usual.

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.

Doing and being

"Focus not on doing less or doing more, but on doing what you value." —Gretchen Rubin

And while we're at it, why doesn't doing rhyme with boing?

Virtual Reality as Operating System

Virtual world systems, such as Second Live (SL), Opensimulator (Opensim), and High Fidelity (HiFi), can be considered  as immersive, collaborative operating systems.

This is especially true as they support Turing-equivalent scripting languages, as well as interface design, and distributed asset management and directory systems ranging from basic (SL) to more advanced (HiFi).

It would be an interesting exercise to map common Operating System user operations from Unix-like, Windows, and Mac OS X to immersive equivalents. I'm sure there are lists available of most commonly used command line and GUI operations.

One of my favorite xkcd quotes

"You're curious and smart and bored, and all you see is the choice between working hard and slacking off.  There are so many adventures that you miss because you're waiting to think of a plan.  To find them, look for tiny interesting choices.  And remember that you are always making up the future as you go."

– Randall Munroe, xkcd

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)

Apache Tomcat JNDI mapping

Suppose that an intrepid Java developer is writing a web application that runs in Apache Tomcat, and that connects to a DBMS using a JNDI (Java Naming and Directory Interface) specification. And suppose that, instead of the usual Tomcat location for the JNDI entry—conf/context.xml—her manager has told her to put all application specific Tomcat configuration in conf/server.xml instead. How then does she make those JNDI entries visible to the app, without modifying any other Tomcat configuration files?

She does it by creating or editing META-INF/context.xml in the web app, with a <ResourceLink> whose name matches the JNDI <Resource> in the server.xml file.

Examples

Apache Tomcat conf/server.xml:

---

...
   <GlobalNamingResources>
    <Resource   name="jdbc/oraDataSource"
                description="Oracle JNDI Datasource"
                auth="Container"
                type="javax.sql.DataSource"
                driverClassName="oracle.jdbc.OracleDriver"
                url="jdbc:oracle:thin:@AMEDMRMCA4397.amed.ds.army.mil:1521:MRMCD"
                username="user"
                password="pass" />

    <Environment name="Common.Web.APPNAME" type="java.lang.String" value="WebAppName" />   
  </GlobalNamingResources>
...


---

Web app META-INF/context.xml:

---

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE project>
<Context>
    <ResourceLink name="jdbc/oraDataSource" type="javax.sql.DataSource" global="jdbc/oraDataSource" />
    <ResourceLink name="Common.Web.APPNAME" type="java.lang.String" global="Common.Web.APPNAME" />
</Context> 

---

Without that magic mapping piece, she will probably get an error about 'no suitable driver' at the first attempt to open as a database session, and that may be frustrating to debug, and make her sad.

Genesis

So, here I am at the genesis of a new blog, which I created on a whim as a way to share some of the developer notes I've been saving for a while, as well as new thoughts and ideas that apply to the use of analytical engines (which some call computers).

I recently found these 10 Rules to Be a Better Programmer, and thought the list to be a pretty good summation of some things I've learned, and am still learning. My comments are italicised (and use Java terminology, but are mostly generalisable).

1. Don't repeat yourself
   This takes some wisdom, but in general it's true. If you find yourself writing the same bit of code more than a handful of lines more than twice, it's time to encapsulate.
2. Name your variables to say what they are for, not what data type they are.
   Yes! Hungarian notation is anathema left over from the bad old days of using tonnes of global variables! If you can't remember the data types of your variables, your routines are too big! Variables (and packages, interfaces, classes, enumerations) should be nouns or noun phrases. A special exception applies to loop counter variables, where "i", "j", "k", and so forth may used by long-standing convention; even there, something like "nameCount" isn't always bad.
3. Give your methods a clear name for what they are going to do.
   See the previous item. Methods should be verbs or verb phrases. Names like "retrieveDocument()" or "isReady()" are good, while "documents()" or "ready()" are not so much.
4. Don't use magic numbers or string literals.
   There are exceptions, but yeah, in general.
5. Write your methods so they can be tested without having any dependencies on other parts of the application, where possible.
   A very good idea. And a good mocking package is a big help.
6. Don't be afraid to ask for help.
   Of these recommendations, I find this one of the most important, and yet I continually forget it. I do count searching via Google, and developer-specific sites like Stack overflow and Code Ranch to be a partial implementation, but I still need to learn to ask people directly.
7. Follow the boy scout rule; fix buggy or messy code; don't leave it for someone else. "Do a good turn daily." But don't fall into the trap of constantly refactoring, and never moving on.
8. Share knowledge with others.
   This is the reciprocation of asking for help. There are many ways to share knowledge, including, oh, I don't know, maybe writing a blog. We'll see how that goes.
9. Don't interrupt your colleagues whilst they are in flow.
   Yes! Working in a cubicle farm does not give you permission, either. Headphones can be a useful indicator.
10. Use criticism as a positive instead of a negative.
    The word "critic" comes to us from a Greek root that has to do with judging, and is related to "crisis". (Etymonline) It's better to mentor and teach via dialogue, I think.