Problem with static variables in java

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 vari开发者_如何学Goable 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);
  }
}

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) {}
}

Maybe it'd be better to look at using a connection pool rather than the static variable... Connection pools maintain a bunch of open connections and serve them out when they're needed. Should solve your performance problem and your memory problem.

a static variable will not garbage collected but if you are just storing a some connection data it should not be a problem. What are you exactly storing?

Matteo

Well, judging by what you say, you have an object (let's call it Obj) class which contains the static variable with the connection. Due to you creates a new Obj each time and you stores it at that moment, I think you are doing a lot of copies of the connection which the JVM is unable to clean because they are static.

You could consider the possibility of store this kind of information in a Model class, or remove the static mark in order to let the JVM collect this objects properly.