MySQL - Connection using Singleton pattern or a ConnectionPool?_问答_开发者

The topic has already been on the table in some previous questions. New elements come with new problems. I am seeking here for the best way to handle database connection(s), concerning aspects of : maintainability, performance, security and implementation. I must mention that i am not interested in abstractions like Hibernate, Spring, etc at this time. Here are the facts :

Scenario 1 - Singleton pattern

The main ideas here are : verify connection details, instantiate the connection, write a static getter for her and use it as long as needed. Code sample :

  static Connection connection;
  private Statement stmt = null;

  connection = createConnection(...)
  stmt = connection.createStatement();

Queries will be executed using :

  stmt.execute();

Now, this may not sound too fancy, but..it works. I have total control over my connection, closing it, instantiating it, closing resultSet(must be done by every caller), etc.

Scenario 2 - Singleton pattern with PreparedStatement

As some people suggested here, i've introduced the PreparedStatement for executing the queries, and basically i've dropped the stmt variable, and instead i use the expression :

PreparedStatement pStmt= getConnection().prepareStatement(query);
pStmt.execute();

Using PMD code analyzer, i've noticed that pSmt may fail to close, which is true, because i cannot close it here, and i cannot instruct the caller to close it either. This approach leads to the next scenario.

Scenario 3 - Singleton pattern with static PreparedStatement

All set, i've declared :

private static PreparedStatement preparedStmt = null;

My executeQuery() method adapts itself to :

setPreparedStmt(getConnection().prepareStatement(query));
getPreparedStmt().execute();

where the setter and getter are simply .. setters and getters, with public visibility and static attribute. This approach is easy to understand, and the callers will have to close the PreparedStatement themselfs (no problem, but closing PreparedStatement will also close the ResultSet, if the case?). However, somehow i feel that re-using this pre-compiled statement is not all that safe with transactional aspect of the database. Are there any dangers here?

Scenario 4 - Using a Connection Pool

This aspect needs further research, but i get the main idea. I cannot go into details开发者_C百科 about this practice, but there are few questions here also :

4.1 I know that one can set the number of connections, the max size of the pool. What that number would be for a single user, for instance, resulting that i will multiply that number with the user count.

4.2 Using a pool of connections, should i use the Statement or the PreparedStatement when getting one connection from the pool and use it to execute some query?

4.3 It is my understanding that using this strategy, i have to close the connection after his task is finished. But what if, for some reasons, the connection fail to close? If that will, for some reason, repeat for a number of times equal to connection pool size..we are doomed.

OK, i will stop here, perhaps some updates will follow, depend on the answers to come. Manny thanks.

In just about every case imaginable, I would use a connection pool. The aspects of maintainability, performance, and implementation have been taken care of for you. Most servers provide a built-in data source for you that can be accessed through JNDI, and there are standalone connection pools as well.

As for security, use prepared statements for any query that requires user input for a parameter. That way, user input can never allow a SQL injection attack.

And about your questions:

4.1: Ideally, each page request will use 1 connection. So in order to figure out how many total pooled connections you need, you've got to do the math on how many requests you'll be getting per average query time (say, requests per second). Unless that number is high, then the default number of connections provided by most connection pools will suffice (usually 20, from what I've seen).

4.2: You can use either a Statement or PreparedStatement from the Connection you get from the pool. In most cases (including any in which user input is used in the query), you should use PreparedStatements.

4.3: If you put your connection closing code in the finally block of a try statement and remember to do this every time you access the database, then you should never run into the problem of running out of connections.