I'm writing an application that uses CMP to manage all my Entity Beans. It's been very refreshing to allow the container to manage all my jdbc connections, do the querying, etc.

Now I need to write a Session bean that can create new instances of an Entity bean. The bean in question has a simple single-field Integer primary key. In the past when I've written apps that deal more directly with the DB, I've had a method that does something like

SELECT MAX (id) from theTable
return id + 1;

I'd like to do the logical equivalent, but I don't have a jdbc connection to the db set up already since I haven't need one. And it feels like a hack to set up a method that opens a connection, runs a query against a table, then returns the single row-single column result, incremented by 1. It would be inconsistent with the way I've been interfacing with the DB through CMP.

I started creating a query in ejb-jar.xml that would be callable through the Home for the bean, but realized I don't know how to write ejb-ql that returns a single field (it always returns a whole row/entity). It quickly became apparent that what I was doing was not consistent with the way Entity beans using CMP interface with the DB.

So what would be the orthodox way to do this? From Google searches, it appears that weblogic may have a <automatic-key-generation> feature in their CMP, that allows the feature to be implemented in, for example, an Oracle sequence, but that feature doesn't appear to be part of the base EJB spec.

I can come up with a brute force way to do this, but I really want to line up with the ejb spec, and the best practices for solving this sort of problem using CMP.

TIA for any advice or info. Regards,