I am writing a data access class that will be used by both EJBs and JSPs. It gets database connection from a datasource configured with server, but I am not sure if I need to commit the connection from within the DAO class after executing sqls. Could anybody tell me what the good practice is?

Here are some tests I did:

I tested with both Oracle and Mysql, by creating a table and insert a couple of rows into it from a jsp page. When I do not commit from the DAO, oracle would not do any insert (though the table got created, why?). When I use commit, oracle works fine, but MySql has some problem -- some connections to database are not released, and finally mysql server run out of connections.

It seems adding the commit line is the reason for the long held connections, but till now I am not sure it's the ejb or the jsp page that is holding the connection for ever (both called the DAO object many times)

Any ideas? Or anybody else has seen the eaten-up connection problem? I am using JBoss2.4. Thanks very much.

LL