I have a REST service architecture that routes requests to a class with this method. This method opens a database connection, queries values, and finally close the database connection. After, JSON is constructed from the database values and returned via REST.
public HashMap<Integer, Artifact> queryDBValues(String userNo) {
DAO dao = getDAO(DB1);
Connection connection = dao.instantiateConnection();
ResultSet resultSet = null;
Statement statement = null;
HashMap<Integer, Artifact> artifacts = new HashMap<Integer, Artifact>();
try {
statement = connection.createStatement();
String stmntStr = "select * from myTable";
resultSet = statement.executeQuery(stmntStr);
int i = 0;
while (resultSet.next()) {
Artifact artifact = new Artifact();
artifact.setArtifactId(resultSet.getString("id"));
artifacts.put(i, artifact);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
dao.closeSQLComponents(resultSet, connection);
}
return artifacts;
}
DAO is an abstract class that is instantiated based on the value passed in getDAO(). I do this because I connect to multiple databases and I connect and disconnect from classes that extend this class.
I received an error from the mysql database after connecting which states: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
The connections are being closed on finally. In the long term I plan to use c3p0 connection pooling. Is this sufficient enough to recycle connections for now with low use ?
I don't see anything fundamentally wrong with your code, except it's a bit strange to close result sets and connections, but not statements.
Another bizarre thing is that a DAO is supposed to be an object that encapsulates the data access code. It's the DAO that should contain the JDBC logic.
That said, why don't you do the right thing now, instead of waiting to have 100,000 lines of code to refactor? Use a connection pool as soon as possible. It would have two huge advantages: