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:
.
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: .