Close

August 16, 2009

Apache Cayenne – Simple Count Query

I’m currently working on a project involved with using Apache Cayenne, a pretty lightweight ORM (object relationship mapping framework). So far I have been very impressed with its ease of use over other framework such as Hibernate and the minimalistic approach it takes to the mapping. Configuration files are used at a minimum and they are extremely easy to understand.

The one thing that I have found a bit lacking, are straightforward real world examples. While, the Java documentation is fantastic and there are only a few minimal “Getting Started” pages on the main website, I really couldn’t find a good example of some more complicated logic examples.

In anycase, here is some code that hopefully will save someone else a bit of time. This is an example of how to call a “NamedQuery” in Cayenne that accepts a single parameter and returns a count of rows for a particular table. This assumes at least a basic understanding of the Cayenne system and that you have at least been through the tutorial.

First the SQL. Using the modeler create a named query called “MyCountTest”.
The sql should look like the text below. “$taskid”, is the name of our input parameter and one of the power features of the Cayenne system. Easy to use parameters!

select count(*) as count  from MyTable where id=$taskid

Now our code to call the named parameter

 
            DataContext context = DataContext.createDataContext(); //get our context
            HashMap parameters = new HashMap(1);  //create a hashmap to hold our parameter
            parameters.put("id", "1"); //add our parameter for our named query
            List records = context.performQuery("MyCountTest",parameters,true);
            DataRow dr=(DataRow) records.get(0);  //Get back the first record..should only be 1 record.
            String cnt=dr.get("count").toString();  //get our "count" variable back that we "selected INTO" in our sql string.
            System.out.println("Our Count: "+cnt);  //print out our "count"

This is a very rough code sample! It needs some type of error handling to ensure that any Cayenne system errors are handled properly.

My hats off to the good folks at the Apache Cayenne project and the fantastic work that they have accomplished!

Recent Posts