Next Sequence Value Using Hibernate Entity Manager

To retrieve the next value of an Orcale sequence using the Hibernate Entity Manager first create a result set mapping.

@SqlResultSetMapping(name = "NextSequenceVal", columns = { @ColumnResult(name = "NEXTVAL") })

The mapping can go anywhere, I like to put it on the entity it is most closely associated with.
Then in your DAO.

    public Long getNextElecTransUniqueId()
    {
        Query query = entityManager.createNativeQuery("SELECT MY_SEQUENCE.NEXTVAL from Dual",
                "NextSequenceVal");
        // Workaround for
        // http://opensource.atlassian.com/projects/hibernate/browse/EJB-434
        // which breaks query.getSingleResult()
        return ((BigDecimal) query.getResultList().get(0)).longValue();

    }

This example is written for Hibernate 3.3.2, Hibernate Entity Manager 3.4.0, Oracle 10g.

Note: in Entity Manager 3.4.0 if you use query.getSingleResult() you will get the exception:
Exception: org.hibernate.exception.SQLGrammarException: could not execute query^M
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)^M
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)^M
at org.hibernate.loader.Loader.doList(Loader.java:2235)^M
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)^M
at org.hibernate.loader.Loader.list(Loader.java:2124)^M
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)^M
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)^M
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)^M
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)^M
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)^M
Caused by: java.sql.SQLException: ORA-02287: sequence number not allowed here

Adventures in Grails

I was recently face with the need to rewrite an entire (ok, 3/4 finished) web application from scratch. The app provided SOAP services for external clients, had integrated search (in both the web interface and WS), restricted user access and search results based on profile and needed to be very scalable. We had previously used AppFuse to build the app, so it was using Spring, Hibernate, Lucene, Compass, Acegi,WebWork, CXF (after upgrading XFire) using WS-Security, etc… I needed to maintain the robustness and scalability of those tools while increasing the flexibility and prototyping efficiency. Enter Grails.

I have been using Groovy to do my scripting tasks for the last 4-5 months. I love it. I can write a script with the same ease and power as I can in Perl or Python and have direct access to my domain model and Hibernate DAOs. Once its working, it is no great leap to wire it up in Spring and have it run on a timer in my app. Groovy alone was enough to get me to give Grails a shot. Once there the near instant ability to create a web UI for CRUD with just three total lines of controller code had me hooked. The fact that plugins for XFire, Acegi and Compass/Lucene already exist make it appear to be the perfect platform for what I need to accomplish. Sure the plugins are young, and I may have to do some work to get some of them to the level I need, but that is the beauty of open source.

I plan to blog my experiences as I put the app together. Stay tuned to see how it goes.

Getting Started

I found a wealth of information to get me started using Grails.

• Grails online Docs http://grails.org/Installation
• Grails podcasts http://grails.org/Grails+Podcast
• Grails screencasts http://grails.org/Grails+Screencasts
• InfoQ’s free ebook http://infoq.com/minibooks/grails

Web Services (XFire)

Once I had an idea of how to start building the app with Grails I figured I would get the Web Services running with the XFire plugin. Using the getting started section of the XFire plugin page I quickly had a simple service up and running.
grails install-plugin xfire
grails create-service Test

Add the expose property to the service.
static expose=['xfire']

and
grails run-app
fire off a test SOAP message and IT WORKS!!!!!
Cool, now I need some data in the DB to see how that looks. Enter it by hand? No, I’ll create a controller and use the web UI! OK, now run the app and fire off a test message….

Pttthhhbbbbb!?!?!?!

Unexpected EOF in prolog at [row,col {unknown-source}]: [1,0]

What the….? Adding a controller breaks the web services? My first wrinkle!
Turns out that you have to pay attention to the “Upgrade to Grails 0.5(URL Mapping)” section on the XFire plugin page too. If you don’t add the following constraint to the Grails URL mapping the controller framework mangles the request.

static mappings = {
          "/$controller/$action?/$id?"{
              constraints {
                        controller(matches:/.*[^(services)].*/)
                  }
          }
}

Thanks to Jason Morris-5 on the Grails mailing list, he worked through the same issue just a few weeks earlier and found the answer.Well, can’t beat an active mailing list. Another plus. All in all a very good experience so far.

Getting the sorted and paginated contents of a collection in Hibernate

Normally getting the contents of a collection from Hibernate is as easy as retrieving the parent object and calling ‘parent.getCollection()’. But what if you have no interest in the parent and the result set could be huge? I found some help in the hibernate.org ‘Tips and Tricks’ section, but not quite what I was looking for. The solutions given there used filters or pre-defined sorting on the collection.
Sorting
Pagination

The following query retrieves the contents of a collection sorts and paginates them:

public List<Category> getSubCategories(Long categoryId, int start, int size) {
        Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
        String queryString = "select subCategory from SubCategory as subCategory, Category as category where category.id = " + categoryId + " and subCategory in elements(category.subCategories) order by subCategory.name";
        List<Category> subCategories = getList(session.createQuery(queryString), start, size);
        return subCategories;
    }

I’m using Spring, but any way of getting a Hibernate session should work.

Here Category and SubCategory look like:

public class Category {
    protected Long id;

    protected String name;
    protected Set<SubCategory> subCategories = new HashSet<SubCategory>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<SubCategory> getSubCategories() {
        return subCategories;
    }

    public void setSubCategories(Set<SubCategory> subCategories) {
        this.subCategories = subCategories;
    }
}

public class SubCategory {
    protected Long id;
    protected String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

I have intentionally left out the xdoclet tags I use to configure hibernate. The mapping is as simple as it gets.