Bug #2010 Add documentation on connetion pooling and clustering if using prepared statemen
Submitted: 4 Dec 2003 5:56 Modified: 18 Jan 2010 14:25
Reporter: Ralf Hauser Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Documentation Severity:S4 (Feature request)
Version:3.1.0 nightly OS:Any (any (using RH9))
Assigned to: Tony Bedford CPU Architecture:Any
Triage: D4 (Minor)

[4 Dec 2003 5:56] Ralf Hauser
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:
.
[14 Dec 2003 5:08] Ralf Hauser
regarding clustering, see the dbcp RFE http://nagoya.apache.org/bugzilla/show_bug.cgi?id=25511
[4 Jul 2008 14:54] MC Brown
Assigning to Tony
[18 Jan 2010 14:25] Tony Bedford
I am closing this bug as it will be fixed as part of the Connector/J docs rewrite that is described by WL4462. A note of this bug has been made there.