-
FEATURED COMPONENTS
First time here? Check out the FAQ!
Hi,
I want to use the Tomcat Connection Pool to handle ZK's DB Connections. I tried it with 5 maxActive (testcase), BUT AFTER! 4x doSQL() calls ZK is doing "nothing" and showing "progressing" ... I have to handle 200 Connections at the same time, so why is ZK/Tomcat not handling more than 5 connections?
Any help appreciated :/
------- CODE ------------
So I got Eclipse Kepler SR2 + Tomcat 6.0.
I added a "context.xml" to the /META-INF Folder
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/localdb" auth="Container"
type="javax.sql.DataSource" username="a" password="b"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/mydb"
maxActive="5" maxIdle="1" />
</Context>
I created a class for the DataConnection
public class DataConnection {
private DataSource dataSource;
public DataConnection() {
}
private void connectToDataBase() {
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
dataSource = (DataSource) envContext.lookup("jdbc/localdb");
} catch (NamingException e) {
e.printStackTrace();
}
}
public Connection getConnection() {
try {
if (dataSource == null || dataSource.getConnection().isClosed()) {
connectToDataBase();
}
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
And i tried a preparedStatement
private DataConnection dataConnection;
@Init
public void init() {
dataConnection = new DataConnection();
}
private void doSQL(int pk) {
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
String selectSQL = "SELECT * FROM myTable WHERE pk=?";
connection = dataConnection.getConnection();
preparedStatement = connection.prepareStatement(selectSQL);
preparedStatement.setInt(1, pk);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != resultSet) {
resultSet.close();
System.out.println("resultSet.close();");
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != preparedStatement) {
preparedStatement.close();
System.out.println("preparedStatement.close();");
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (null != connection) {
connection.close();
System.out.println("connection.close();");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Just to clarify for anyone reading this - standard Tomcat connection pooling works just fine, if you use it correctly.
You don't have to use HikariCP or anything else (not to say of course anything else is necessarily bad).
We've been using standard tomcat connection pools for years with great success.
Yes, trying to understand code now - my understanding is that "dataSource = (DataSource) envContext.lookup("jdbc/localdb");" will actually get a connection. Therefore the "return dataSource.getConnection();" is incorrect.
However I have to preface all my comments with - I use Jython - more than Java so am rusty with my java. Java expert not around at moment.
Have a try with PSI Probe (http://psi-probe.googlecode.com/) to see what connections are in use after just one get of a connection (and in theory one release).
So for whatever reason you are not releasing them.
First, I would change the maxActive to 20 - Postgres/tomcat can handle 20 no problem.
Second I would remove the maxidle - don't see the point.
Third (and the big one). You should get a connection, run your SQL statement, then release connection. That way you only hold a connection for a few milliseconds (or however long your SQL statement takes).
The whole point about connections is you want to share them. To do this you MUST release them asap.
Make sure in you always release (in your "finally") even on errors.
Assuming you do this, then there should be no problems.
Yes, these are not the final values. 5 maxActive was for test purpose only. Maxidle can be removed too.
Have you read my code? I acquire a connection in doSQL()
connection = dataConnection.getConnection();
and finally i release it. So I don't get, why after 4 calls of "doSQL()", ZK is progressing and nothing is working :<
Is the problem
if (dataSource == null || dataSource.getConnection().isClosed()) {
connectToDataBase();
Won't this get 2 connections. I assume you are trying to see if the existing connection is closed but you will get another connection (which will be closed), then you do connectToDatabase which will get another connection. Should it not be "connection.isClosed()"
Maybe instead after close, you set to null and check for nulls?
I really don't know. I was kind of in a hurry and couldn't fix it, so I tried
https://github.com/brettwooldridge/HikariCP
and I really like it. For anyone else who is using connection pools, try it out.
Thank you Bobzk for your help :)
P.S.: http://psi-probe.googlecode.com/ is really good!
Asked: 2014-06-25 09:56:48 +0800
Seen: 27 times
Last updated: Jun 29 '14