EJB specification, BMP / CMPs, transaction

[jcordonn]

Hi !

I write some BMPs and I have the following problem: I create some beans with the transaction attribute 'required new'. I use JBoss-2.4.3 with Oracle 8.1.6. After a couple of insert/delete, it seems that the pool of JBDC connections hang, and i just see the change (BMP, database synchronisation) if I stop the connection pool.

In the EJB 2.0 specification, if I remember, they say that the ejbCreate method ( and the ejbRemove) can't start NEW transaction (required new), that's right ?

As you see in the code below, I call an insert method in my ejbCreate method, and specify my transaction attributes in these method.

Is this the right way for immediate EJB/database synchronisation ?

/**
* @ejb:create-method view-type="remote"
*/
public FtpBeanPK ejbCreate(int value, String name, byte[] xml) throws CreateException {

try {
insertRow(value, name, xml);
} catch (Exception ex) {
throw new CreateException("ejbCreate: " + ex.getMessage());
}

FtpBeanData data = getData();

data.setLocationValue(value);
data.setLocationName(name);
data.setXml(xml);

FtpBeanPK pk = new FtpBeanPK();
pk.locationValue = value;

return pk;
}

/**
* @ejb:interface-method
* @ejb:transaction type="RequiresNew"
* @param
* @param
* @param
* @exception
*/
public void insertRow (int value, String name, byte[] xml)
throws SQLException {

String insertStatement =
"insert into " + tablename + " values ( ? , ? , ?)";
PreparedStatement prepStmt =
con.prepareStatement(insertStatement);

prepStmt.setInt(1, value);
prepStmt.setString(2, name);
prepStmt.setBytes(3, xml);

prepStmt.executeUpdate();
prepStmt.close();

}

/**
* @ejb:interface-method
* @ejb:transaction type="RequiresNew"
* @param
* @exception
*/
public void deleteRow(FtpBeanPK pk) throws SQLException {

String deleteStatement =
"delete from " + tablename + " where value = ?";
PreparedStatement prepStmt =
con.prepareStatement(deleteStatement);

prepStmt.setInt(1, pk.locationValue);
prepStmt.executeUpdate();
prepStmt.close();
}


Thanks
/Joel

[davidjencks]

You don't say where your connection comes from. In jboss there currently is a limitation that you must get your connection after the tx starts and close it before it ends. (apparently may not be so true with bmt, but definitely with cmt). So, you can cache the datasource, but you have to get a new connection within each transaction.

Holding onto a connection over a transaction boundary kills it and it usually won't get disposed of properly, thus the pool exhaustion.

[jcordonn]

Hi ! That's my code ! After a couple of create/remove, the client hangs on a remove,....any idea ?

Thanks
/Joel

/**
* @ejb:bean name="ftp/FtpBean" type="BMP" jndi-name="ejb/ftp/FtpBean" view-type="remote"
* @ejb:home generate="remote"
* @ejb:interface generate="remote"
*
* @ejb:finder signature="Collection findAll()"
*
* @ejb:resource-ref res-name="jdbc/wpsDS" res-type="javax.sql.DataSource" res-auth="Container"
*
* @jboss:resource-ref res-ref-name="jdbc/wpsDS" resource-name="wpsDS"
* @jboss:resource-manager res-man-name="wpsDS" res-man-jndi-name="java:/wpsDS"

* @jboss:table-name "WETTER"
* @jboss:create-table "true"
* @jboss:remove-table "true"
* @jboss:tuned-updates "true"
* @jboss:read-only "false"
*/
public abstract class FtpBMPBean implements EntityBean {

private EntityContext context;
private DataSource datasource;
private Connection con;
private String tablename = "WETTER";

private String DS = "java:comp/env/jdbc/wpsDS";

// public methods -----------------------------------------------------

/**
* @ejb:pk-field
* @ejb:persistent-field
* @ejb:interface-method
*/
public abstract int getLocationValue();


/**
* @ejb:interface-method
*/
public abstract void setLocationValue(int value);

/**
* @ejb:persistent-field
* @ejb:interface-method
*/
public abstract String getLocationName();

/**
* @ejb:interface-method
*/
public abstract void setLocationName(String name);

/**
* @ejb:persistent-field
* @ejb:interface-method
*/
public abstract byte[] getXml();

/**
* @ejb:interface-method
*/
public abstract void setXml(byte[] xml);

/**
*/
public abstract void setData(FtpBeanData data);

/**
* @ejb:interface-method
*/
public abstract FtpBeanData getData();


// private methods -----------------------------------------------------

/**
* @ejb:create-method
* @ejb:transaction type="Required"
*/
public FtpBeanPK ejbCreate(int value, String name, byte[] xml) throws CreateException {
try {

// makeConnection already called when calling setEntityContext();
// acquire the connection IN a already started transaction
Connection mycon = datasource.getConnection();

insertRow(mycon, value, name, xml);

mycon.close(); // explicitly close the connection before the transaction complete.

} catch (Exception ex) {
throw new CreateException("ejbCreate: " + ex.getMessage());
}

FtpBeanData data = getData();

data.setLocationValue(value);
data.setLocationName(name);
data.setXml(xml);

FtpBeanPK pk = new FtpBeanPK();
pk.locationValue = value;

return pk;
}

public void ejbPostCreate(int value, String name, byte[] xml) {
}

public FtpBeanPK ejbFindByPrimaryKey(FtpBeanPK primaryKey) throws FinderException {

boolean result = true;
try {
result = selectByPrimaryKey(primaryKey);
} catch (Exception ex) {
throw new RuntimeException("ejbFindByPrimaryKey" + ex.getMessage());
}
if (result) {
return primaryKey;
}
else {
throw new FinderException
("Row for id " + primaryKey + " not found.");
}
}

/**
* @ejb:transaction type="Required"
*/
public void ejbRemove() throws RemoveException {


try {
// acquire the connection IN a already started transaction
Connection mycon = datasource.getConnection();

deleteRow(mycon, (FtpBeanPK)context.getPrimaryKey());

mycon.close();
} catch (Exception ex) {
throw new RemoveException("ejbRemove: " +
ex.getMessage());
}
}

/**
*/
public void setEntityContext(EntityContext context) {
this.context = context;
try {
getDatasource(); // cache the datasource and the connection
} catch (Exception ex) {
throw new RuntimeException("Unable to connect to database. " +
ex.getMessage());
}
}

/**
*/
public void unsetEntityContext() {
try {
con.close();
} catch (SQLException ex) {
throw new RuntimeException("unsetEntityContext: " + ex.getMessage());
}
}

public void ejbActivate() {
}

public void ejbPassivate() {
}

/**
*/
public void ejbLoad() {
try {
loadRow();
} catch (Exception ex) {
throw new RuntimeException("ejbLoad: " + ex.getMessage());
}
}

/**
*/
public void ejbStore() {
try {
storeRow();
} catch (Exception ex) {
throw new RuntimeException("ejbStore: " + ex.getMessage());
}
}


private void getDatasource() throws NamingException, SQLException {
InitialContext ic = new InitialContext();
datasource = (DataSource) ic.lookup(DS); // cached datasource
con = datasource.getConnection(); // cached connection
}

/**
get connection only when required. cf transaction context.
*/
/*
private void getConnection() throws SQLException {
if(con == null)
con = datasource.getConnection();
}
*/

/**
* BE CAREFUL: if you want the the insertRow method excutes in a
* transaction, be careful to open and close the datasource connection
* after and before the transaction complete.
*/
private void insertRow (Connection connnection, int value, String name, byte[] xml)
throws SQLException {

String insertStatement =
"insert into " + tablename + " values ( ? , ? , ?)";
PreparedStatement prepStmt =
connnection.prepareStatement(insertStatement);

prepStmt.setInt(1, value);
prepStmt.setString(2, name);
prepStmt.setBytes(3, xml);

prepStmt.executeUpdate();
prepStmt.close();

}

private void deleteRow(Connection aConnection, FtpBeanPK pk) throws SQLException {

String deleteStatement =
"delete from " + tablename + " where value = ?";
PreparedStatement prepStmt =
aConnection.prepareStatement(deleteStatement);

prepStmt.setInt(1, pk.locationValue);
prepStmt.executeUpdate();
prepStmt.close();
}

private boolean selectByPrimaryKey(FtpBeanPK pk) throws SQLException {

String selectStatement =
"SELECT name, value" +
" FROM " + tablename + " WHERE value = ?";
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);
prepStmt.setInt(1, pk.locationValue);

ResultSet rs = prepStmt.executeQuery();
boolean result = rs.next();
prepStmt.close();
return result;
}

private void loadRow() throws SQLException, FinderException {

String selectStatement =
"select name, xml from " + tablename + " where value = ?";

PreparedStatement prepStmt =
con.prepareStatement(selectStatement);

FtpBeanPK pk = (FtpBeanPK)context.getPrimaryKey();
prepStmt.setInt(1, pk.locationValue);

ResultSet rs = prepStmt.executeQuery();

if (rs.next()) {
FtpBeanData data = getData();

data.setLocationName(rs.getString(1));
data.setXml(rs.getBytes(2));

prepStmt.close();
}
else {
prepStmt.close();
throw new FinderException("No row for locationValue " + pk.locationValue +
" not found in database.");
}
}

private void storeRow() throws SQLException {


String updateStatement =
"update " + tablename + " set xml = ? where value = ? and name = ?";

PreparedStatement prepStmt =
con.prepareStatement(updateStatement);

FtpBeanData data = getData();

prepStmt.setBytes(1, data.getXml()); // convert the String to a byte array
prepStmt.setInt(2, data.getLocationValue());
prepStmt.setString(3, data.getLocationName());

int rowCount = prepStmt.executeUpdate();
prepStmt.close();

if (rowCount == 0) {
throw new RuntimeException("Storing row for locationName " + data.getLocationName() +
" and locationValue " + data.getLocationValue() + " failed.");
}
}

}