StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL - Connection using Singleton pattern or a ConnectionPool?
text
Body
copied!<p>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 : <em>maintainability</em>, <em>performance</em>, <em>security</em> and <em>implementation</em>. I must mention that i am not interested in abstractions like Hibernate, Spring, etc at this time. Here are the facts : </p> <hr> <p>Scenario 1 - Singleton pattern</p> <hr> <p> 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 : </p> <pre><code> static Connection connection; private Statement stmt = null; connection = createConnection(...) stmt = connection.createStatement(); </code></pre> <p>Queries will be executed using : </p> <pre><code> stmt.execute(); </code></pre> <p>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.</p> <hr> <p> Scenario 2 - Singleton pattern with PreparedStatement </p> <hr> <p>As some people suggested here, i've introduced the PreparedStatement for executing the queries, and basically i've dropped the <strong>stmt</strong> variable, and instead i use the expression :</p> <pre><code>PreparedStatement pStmt= getConnection().prepareStatement(query); pStmt.execute(); </code></pre> <p>Using PMD code analyzer, i've noticed that <strong>pSmt</strong> 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.</p> <hr> <p> Scenario 3 - Singleton pattern with static PreparedStatement</p> <hr> <p>All set, i've declared :</p> <pre><code>private static PreparedStatement preparedStmt = null; </code></pre> <p>My executeQuery() method adapts itself to :</p> <pre><code>setPreparedStmt(getConnection().prepareStatement(query)); getPreparedStmt().execute(); </code></pre> <p>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 <code>PreparedStatement</code> themselfs (no problem, but closing <code>PreparedStatement</code> will also close the <code>ResultSet</code>, 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?</p> <hr> <p> Scenario 4 - Using a Connection Pool</p> <hr> <p>This aspect needs further research, but i get the main idea. I cannot go into details about this practice, but there are few questions here also :</p> <p>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.</p> <p><p>4.2 Using a pool of connections, should i use the <code>Statement</code> or the <code>PreparedStatement</code> when getting one connection from the pool and use it to execute some query? <p>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. <p> OK, i will stop here, perhaps some updates will follow, depend on the answers to come. Manny thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload