Geek Answers Handbook

Conventional singleton connection does not work well

I am using a single point database connection in my java application, here is the code of my connection manager:

public abstract class DatabaseManager { //Static instance of connection, only one will ever exist private static Connection connection = null; private static String dbName="SNfinal"; //Returns single instance of connection public static Connection getConnection(){ //If instance has not been created yet, create it if(DatabaseManager.connection == null){ initConnection(); } return DatabaseManager.connection; } //Gets JDBC connection instance private static void initConnection(){ try{ Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName="+dbName+";integratedSecurity=true"; DatabaseManager.connection = DriverManager.getConnection(connectionUrl); } catch (ClassNotFoundException e){ System.out.println(e.getMessage()); System.exit(0); } catch (SQLException e){ System.out.println(e.getMessage()); System.exit(0); } catch (Exception e){ } } public static ResultSet executeQuery(String SQL, String dbName) { ResultSet rset = null ; try { Statement st = DatabaseManager.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rset = st.executeQuery(SQL); //st.close(); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); } return rset; } public static void executeUpdate(String SQL, String dbName) { try { Statement st = DatabaseManager.getConnection().createStatement(); st.executeUpdate(SQL); st.close(); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); } } }

The problem is that my work with the code works from the very beginning, but when the time has passed, it becomes very slow. What caused this problem and how can I fix it? During launch, my application processes about 20 requests per second, after 1 hour of operation it reaches 10 requests per second and after 3 days of operation it reaches 1 request in 10 seconds! PS: My application is a single-user application that processes many requests through a database. PS: Here are my JVM options in eclipse.ini:

 --launcher.XXMaxPermSize 512M -showsplash org.eclipse.platform --launcher.XXMaxPermSize 512m --launcher.defaultAction openFile --launcher.appendVmargs -vmargs -Dosgi.requiredJavaVersion=1.6 -Xms500m -Xmx4G -XX:MaxHeapSize=4500m

Unfortunately, the database is deleted, and I do not have access to it to monitor what is happening there.

Here is an example of my use:

 String count="select count(*) as counter from TSN"; ResultSet rscount=DatabaseManager.executeQuery(count, "SNfinal"); if(rscount.next()) { numberofNodes=rscount.getInt("counter"); }
+6
source share
3 answers

You should use a disabled result set, for example CachedRowSet http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

 public static ResultSet executeQuery(String SQL, String dbName) { CachedRowSetImpl crs = new CachedRowSetImpl(); ResultSet rset = null ; Statement st = null; try { st = DatabaseManager.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rset = st.executeQuery(SQL); crs.populate(rset); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); }finally{ rset.close(); st.close(); } return crs; }

CachedRowSet implements a ResultSet, so it should behave like a ResultSet.

http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html

In addition to these changes, I would recommend using a unified data source to receive connections and close them, rather than supporting a single open connection.

http://brettwooldridge.imtqy.com/HikariCP/

Or if you arent java7, bonecp or c3po.

EDIT:

To answer your question, this solves your problem because CachedRowSetImpl does not support communication with the database during use. This allows you to close your Resultset and Statement after filling in the CachedRowSetImpl .

Hope that answers your question.

+1
source

What caused this problem and how can I fix it?

The main problem that arises here is the executeQuery() method. You do not close the Statement , I suppose you commented on the st.close() line because you need a ResultSet open for further processing. I see that your idea is to avoid duplicating JDBC code in your application, but this is the wrong approach.

Rule: close the ResultSet and after that close the Statement , otherwise you will not release the resources correctly, and you will reveal the problem that you are describing.

Here you can find a good explanation of how to close resources properly (note that in your case you do not need to close the connection)

Edit: An example would be

 try{ Statement st = DatabaseManager.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rsCount = st.executeQuery(count); //count="select count(*) as counter from TSN"; if(rsCount.next()) { numberofNodes=rscount.getInt("counter"); } } catch (SQLException e) { //log exception } finally { rsCount.close(); st.close(); }
+3
source

  • Although the connection manager automatically closed Statement and Resultset , it would be better if you close them immediately.

  • There is nothing else in the code that will affect your task with a single thread, so I'm sure something is wrong in your database. Try to find out if there is a database lock or an invalid column index. And also take a look at the status of the database request, find out where the bottleneck is.

+1
source

More articles:


All Articles