Description:
Mark,
This is an enhancement request for http://www.mysql.com/documentation/connector-j/index.html
Background
==========
Since a few days, I am struggling to get connection pooling, prepared statements, and a preparation for mysql-clustering all set up correctly, pooling being the last I added.
Without getConnection() and .close() statements around each prepared-statement, all seemed to work fine so far (not having done any load test yet). Now, I get shortly after startup:
ERROR [main] (MySqlSystem.java:7975) - sql exception: PreparedStatement is closed.
ERROR [main] (DatabaseException.java:38) - PreparedStatement is closed.
DEBUG [main] (Globals.java:997) - PreparedStatement is closed.
ERROR [main] (DatabaseException.java:38) - PreparedStatement is closed.
com.mydomain.myApp.db.DatabaseException: PreparedStatement is closed.
at com.mydomain.myApp.util.Globals.loadCategoryProps(Globals.java:998)
at com.mydomain.myApp.util.Globals.loadData(Globals.java:172)
at com.mydomain.myApp.util.Globals.<init>(Globals.java:104)
at com.mydomain.myApp.util.Globals.getInstance(Globals.java:126)
at com.mydomain.myApp.db.Database.init(Database.java:172)
at javax.servlet.GenericServlet.init(GenericServlet.java:256)
few more background bits:
- we are using tomcat 4.1.27/struts1.1
- we are shielding the business application/GUI struts Action classes from the
database by a abstract class DBSystem.java and our relatively large file
MySQLSystem.java is implementing this.
- for the pool, we use org.apache.commons.dbcp.BasicDataSource
Sample code
===========
from web.xml:
<servlet>
<servlet-name>database</servlet-name>
<servlet-class>com.myDomain.myApp.db.Database</servlet-class>
<!-- prepare for the clustering we currently do not use it yet -->
<init-param>
<param-name>writeURL</param-name>
<param-value>jdbc:mysql://writeMasterHost:3306/myDB?autoReconnect=true</param-value>
</init-param>
<init-param>
<param-name>readURL</param-name>
<param-value>jdbc:mysql://dnsRoundRobinOrOtherLoadBalancedReadHost:3306/myDB?autoReconnect=true</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>
I would rather have had the connection pool management outside this
encapsulated MySQLSystem.java, but so far I figured, this is best done inside.
Database.java - (the database servlet specified in web.xml):
<<...
BasicDataSource dataSource =
(BasicDataSource) getServletContext().getAttribute(
Globals.DATA_SOURCE_KEY);
...
readURL =
(String) getServletConfig().getInitParameter(
Constants.READ_URL_KEY);
writeURL =
(String) getServletConfig().getInitParameter(
Constants.WRITE_URL_KEY);
...
if (driverClass.indexOf("mysql") > -1) {
db = new MySqlSystem(dataSource, writeURL, readURL);
log.debug("Using MySql.");
} else {
log.error("No supported database available.");
throw new ServletException("No supported database available.");
}
...
try {
db.initialize();
} catch (Exception dbe) {
...>>
Next DBSystem.java:
<<...
public abstract class DBSystem {
/**
* The connection pool.
*/
protected BasicDataSource rds = null;
protected BasicDataSource wds = null;
...
public abstract User getUser(String login, boolean status)
throws DatabaseException;
...>>
Now the MySQLSystem.java:
<<...
private Connection connRead = null;
private Connection connWrite = null;
...
public MySqlSystem(
BasicDataSource dataSource,
String writeURL,
String readURL) {
this.rds = dataSource;
this.wds = dataSource;
rds.setUrl(readURL);
wds.setUrl(writeURL);
}
...
public void initialize()
throws IOException, SAXException, DatabaseException {
try {
connRead = rds.getConnection();
connWrite = wds.getConnection();
...
insUserStmt =
connWrite.prepareStatement(
"INSERT INTO "
+ TBL_USER
+ "(login, "
+ " password, "
+ " forename, "
+ " lastname, "
...
+ " company ) "
+ " VALUES (?,ENCRYPT( ?,?), ?,
...
+ " ? );");
...
selUserStmt =
connRead.prepareStatement(
" SELECT * "
+ " FROM "
+ TBL_USER
+ " WHERE login = ? "
+ " AND active = ? ");
...
} // end initialize()
...
public User getUser(String login, boolean status)
throws DatabaseException {
User user = null;
try {
selUserStmt.getConnection();
selUserStmt.setString(1, login);
selUserStmt.setBoolean(2, status); // active / pending
log.debug(
"selUserStmt: "
+ ((DelegatingPreparedStatement) selUserStmt)
.getDelegate()
.toString());
// log sql
ResultSet rs = selUserStmt.executeQuery();
//execute the query and read out the result
int i = 0;
if (rs == null) {
log.debug("row set is null!");
}
if (!rs.next()) {
log.debug("NO_MORE_SEL_RESULTS" + i);
} else {
do {
i++;
// read out the result
user = user = resultSetStarToOneBusUser(rs);
} while (rs.next());
if (i > 1) {
log.error("more than one user not allowed!");
throw new DatabaseException("more than one user not allowed!");
}
}
selUserStmt.close();
} catch (SQLException se) {
log.error(se.getMessage());
throw new DatabaseException(se.getMessage());
}
return user;
} // end getUser()
...>>
Now in the web application, I would call this for example in LoginAction.java:
<<...
public ActionForward execute(
ActionMapping actionMapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
...
DBSystem db =
(DBSystem) servlet.getServletContext().getAttribute(
Constants.DATABASE_KEY);
...
try {
user = db.getUser(loginForm.getLogin());
if (user != null) {
...>>
What I would hope such a sample would clear:
============================================
- how to actively manage connections with PreparedStatements?
- do I need to explicitly introduce multiple connection pool to be ready
for clustering or is the above just fine?
- any other thoughts on my abstract class if I eventually want to grow towards
MaxDB or other?
Looking forward to your reaction!
Ralf
How to repeat:
.
Suggested fix:
.