Problem with static variables in java

Question

I am using static variables pretty much heavily in my application. Now after the application status is finished I am facing a problem in garbage collection. The variables that are declares as static are never garbage collected and my memory runs out quickly.

The specific problem is on mysql connection. I am storing the connection variable in a static variable and so I don't have to open the connection every time I run a query. This leads to a problem of usage of all memory every time I use the connection variable to execute the query and the used memory is not released. Is it a good idea to store the connection variable in static variable ? when I tried to open and close the connection every time without static variable I solved the memory management problem but the responsiveness of the application is slowed down by 10 to 20 times.

Do you need more information to understand this problem ? If yes please ask me without down voting. Thanks!

EDIT This is my connector class

import java.sql.*;

public class connect {

    public Connection conn = null;

    public connect() {
        try {
            if (conn == null) {
                String userName = "root";
                String password = "password";               
                String url = "jdbc:mysql://localhost/pos?zeroDateTimeBehavior=convertToNull";                
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = DriverManager.getConnection(url, userName, password);               
                System.out.println("Database connection established");               
            }
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");           
        }
    }
}

This is my class where i am storing the connection

public class variables {
    public static connect con = new connect();
}

And this method i use to execute the query

public class mysql_query {
public static ResultSet execute_mysql(Connection con, String sqlStatement) {
        try {
            //ResultSet result = null;
            java.sql.Statement cs = con.createStatement();
            ResultSet result = cs.executeQuery(sqlStatement);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(mysql_query.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }

 public static void main(String args[]){
     String sql = "SELECT * FROM pos_user_login WHERE moderator='1' AND "
                    + "company_id='1'";

     ResultSet rs = execute_mysql(variables.con.conn, sql);
  }
}

Answer 1

Just an idea: You might not be closing your ResultSet and Statement objects, correctly. If you don't do that, the MySQL JDBC driver might keep a hold on many resources that you don't need anymore. Especially ResultSet can be very painful, as some parts of the database cursor are still in memory.

An example to give you an idea is this:

PreparedStatement stmt = null;
ResultSet rs = null;

try {
    stmt = connection.prepareStatement(...);
    rs = stmt.executeQuery();
}

// Close your resources in a finally block! Because the finally block
// is executed even if you have exceptions in the try block.
// If you do this a lot of times, write utility methods...
finally {
    try {
        if (rs != null) {
            rs.close();
        }
    } catch (SQLException ignore) {}

    try {
        if (stmt != null) {
            stmt.close();
        }
    } catch (SQLException ignore) {}
}