Is it possible to use container-managed persistence and take advantage of database triggers and sequences to populate the primary key? If so are there any good examples?

Dan Christopherson

I'll talk about sequence type mechanisms first: triggers probably won't work for reasons I'll discuss later.

The technique you'd want to use would be to retrieve your new key value in your ejbCreate method(s) and assign it to the appropriate field in your bean. That way the CMP mechanism in the container can pick it up and will have an identity for your bean. Note that your ejbCreate method needs to return an instance of your primary key class (in EJB 1.1 - if you're still at 1.0, consider a different container)

You should be able to get your value with a little bit of BMP looking code: get the DataSource from JNDI (ctx.lookup("java:comp/env/jdbc/MyConnection") type things), get a Connection from the DataSource, then execute the SQL ("SELECT myseq.nextval from dual" if i remember Oracle correctly). Remember to close the connection to ensure taht it is re-pooled properly.

Triggers probably won't work (assuming you were going to use them to assign values to the primary key columns directly). This is because most containers really, really think they need to know the value of your primary key ahead of time, largely (I believe) to make sure that their cache is right, but also because they really need to explicitely check for key-violations themselves so that they can throw DuplicateKeyException (catching and interpreting the SQLException from the JDBC driver would be vendor dependent and would also have to assume that the primary key columns are actually defined as primary keys - not a safe assumption for a container to make, unfortunately)

By the same token, you'll not be able to get your CMP container to emit SQL that hits your sequence within the insert statement: it requires assumptions that container developers just can't make (like the existence and syntax of your RDBMS' sequence feature, as well as all of the above).