I am using singleton database connection inside my java application, here is code of my connection manager class:
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 my code work perfect at the start but when time past it becomes really slow. What caused that problem and how can i fix that? At starting time my application handles around 20 queries per second, after 1 hour of running it reaches to 10 queries per second and after 3 days of running it reaches to 1 query per 10 seconds!! P.S: My application is a single user application that makes many queries through database. P.S: Here is my JVM parameters 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 database is remote and I have not any monitoring access to it for finding out what is going on there.
Here is the example of my usage:
String count="select count(*) as counter from TSN";
ResultSet rscount=DatabaseManager.executeQuery(count, "SNfinal");
if(rscount.next()) {
numberofNodes=rscount.getInt("counter");
}