EJB3 DAO stateless session bean design (use JDBC connection)

Question

I'm designing a DAO layer for a EJB3 project, they are stateless session bean. For some reason, part of DAOs won't use JPA, they access database directly with JDBC.

So instead of injecting EntityManager, I inject a Datasource and from it I can get the Connection, create Session and so on... In order to avoid the duplicated work, I made a POJO BaseClass, which get the Connection from Datasource at beginning and close it before destroy. All JDBC DAOs extend it, so they don't need do such job by themself. The BaseClass looks like this:

public abstract class MemDBDAO {

    @Resource(mappedName = "java:MagicCardDS")
    private DataSource dataSource;
    protected Connection dbConnection;

    @PostConstruct
    protected void startUp() {
        try {
            dbConnection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();  
        }
    }

    @PreDestroy
    protected void shutDown() {
        try {
            dbConnection.close();
        } catch (SQLException e) {
            e.printStackTrace();  
        }
    }

}

And for example one JDBC DAO looks like this:

@Stateless
public class SessionDAOBean extends MemDBDAO implements SessionDAO {


    @Override
    public void createSession(String sessionId, Integer userId) {
        try {
            PreparedStatement statement = dbConnection.prepareStatement(
                    "INSERT INTO session VALUES(?, ?, ?, ?)");
            statement.setString(1, sessionId);
            statement.setInt(2, userId);
            statement.setBoolean(3, false);
            statement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();  
        }
    }
...

When I deploy it to a JBoss 6 application server, I got these error messages:

20:51:12,546 INFO  [org.jboss.resource.connectionmanager.CachedConnectionManager] Closing a connection for you.  Please close them yourself: org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6@9bb04a: java.lang.Throwable: STACKTRACE
    at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:278) [:6.1.0.Final]
    at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:524) [:6.1.0.Final]
    at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941) [:6.1.0.Final]
    at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89) [:6.1.0.Final]
    at piapox.com.magiccard.server.dao.MemDBDAO.startUp(MemDBDAO.java:32) [:]
    at piapox.com.magiccard.server.dao.SessionDAOBean.startUp(SessionDAOBean.java:35) [:]
...

So how should I design the JDBC DAO Session beans? Is there any good pattern? Thanks!

Answer 1

Probably the connections established are being monitored by JBoss. It seems the server is trying to close connection even if its already closed, these messages can be ignored. Probably message doesn't interrupt normal processing & can be disabled by altering jboss config file.

Call back methods @PostConstruct & @PreDestroy are handled by the container. Its vendor specific as to create & destroy stateless session bean instance for each method invocation. As in pooled state, bean instance may be active for a long period.

Its better to open connection in method itself & closing it before exit in createSession method, finally is preferred in case of exceptions at runtime.

Also you can refer this bug, reported with JBoss.