Hello Folks
This question comes up again and again and I must admit that it was a
pretty awkward thing for myself to figure out.
Big hand to Danch for pointing me in the right direction. What he
suggested works very well, so I've knocked up a quick Test CMP bean
for proof of concept. I reckon that quite a few people have a similar
problem and I'm thus sending some sample code.
Here is the TestBean implementation
===
package com.frontwire.cams.entity.test;
import java.rmi.RemoteException;
import java.sql.Connection;
import javax.ejb.CreateException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import com.frontwire.util.beanext.ExtendedJDBC;
public class TestBean implements EntityBean
{
public String id;
public String name;
public EntityContext ctx;
public String getName() throws RemoteException
{
return this.name;
}
public void setName( String name ) throws RemoteException
{
this.name = name;
}
public String ejbCreate( String name ) throws RemoteException, CreateException
{
Connection connection = ExtendedJDBC.getConnection();
this.id = ExtendedJDBC.getInsertId( connection );
this.name = name;
return null;
}
public void ejbPostCreate( String name ) throws RemoteException, CreateException
{
}
public void ejbActivate() throws RemoteException {
}
public void ejbPassivate() throws RemoteException {
}
public void ejbLoad() throws RemoteException {
}
public void ejbStore() throws RemoteException {
}
public void ejbRemove() throws RemoteException {
}
public void setEntityContext(EntityContext ctx) throws RemoteException {
this.ctx = ctx;
}
public void unsetEntityContext() throws RemoteException {
this.ctx = null;
}
}// TestBean
===
ExtendedJDBC is effectively a helper class I use for getting db
connections and retrieving the values for the primary key in the db.
Here are the relevant methods. Note that I'm setting the name of the
database to connect to from an environment variable.
===
public static Connection getConnection()
{
try {
InitialContext context = new InitialContext();
String poolname =
(String)context.lookup( java:/comp/env/DatabasePoolName );
String jndiName = java:/ + poolname;
DataSource ds =
(DataSource)context.lookup( jndiName );
return ds.getConnection();
}
catch( NamingException e ){
throw new EJBException( e.getMessage() );
}
catch( SQLException e ){
throw new EJBException( ExtendedJDBC: SQLException: + e.getMessage());
}
}
public static String getInsertId( Connection c )
{
String queryInsertIdMySQL = SELECT LAST_INSERT_ID();
String insertId = null;
try {
PreparedStatement s = c.prepareStatement( queryInsertIdMySQL );
ResultSet r = s.executeQuery();
if( r.next() ) {
insertId = r.getString( last_insert_id() );
try {
r.close();
}
catch( SQLException e ){
throw new EJBException( getInsertId: ResultSet.close()
+ e.getMessage() );
}
try {
s.close();
}
catch( SQLException e ){
throw new EJBException( getInsertId: PreparedStatement.close()
+ e.getMessage() );
}
}
else {
throw new EJBException( getInsertId: NULL insertId );
}
return insertId;
}
catch( SQLException e ){
throw new EJBException( getInsertId: + e.getMessage() );
}
}
===
I've started thinking about supporting retrieving the auto-increment
value for different databases. I wouldn't expect the method/sql
changing very much though. And in future implementation will probably
use an environment variable for determining which query to use for
retrieving that value.
Now I wonder whether one couldn't provide a similar utility with
JBoss? Some other vendors, like Orion, provide a helper package for
getting the next primary key value for a given class (probably a very
similar implemntation like PostgreSQL uses for sequences [a much
better way of doing it than mySQL IMO]).
Not all databases support such feature. However, I
