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

To clarify: Are you using the same connection object to perform all the queries in your application? What specific connection class are you using? — Sasha Goldshtein
– Sasha Goldshtein, Commented Aug 30, 2011 at 7:27
Hard to say. Can you describe your application? Is it a batch job? A swing application? A web application? A standalone server where you can connect to using sockets? etc... — Lukas Eder
– Lukas Eder, Commented Aug 30, 2011 at 7:27
When you say "open the connection every time" you want to mean "do a new connection object every time"? — Charliemops
– Charliemops, Commented Aug 30, 2011 at 7:29
From your posted code, it seems pretty clear that you're not properly closing your resources... See an example of how to do that in my answer... — Lukas Eder
– Lukas Eder, Commented Aug 30, 2011 at 7:42

Lukas Eder · Accepted Answer · 2011-08-30 07:53:50Z

5

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

edited Aug 30, 2011 at 7:53

answered Aug 30, 2011 at 7:37

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deepak Over a year ago

So just looking at your example and the edit i have added probably tat might be the cause!!! am i right ??

Lukas Eder Over a year ago

I'm pretty sure it is. Not closing those resources is a very common memory leak

Deepak Over a year ago

so do i have to use connection pool still to solve the problem ??

Lukas Eder Over a year ago

As Michael says, with connection pools, you're not using less memory. It's easier to handle the lifecycle of connections when you have lots of threads accessing the database (e.g. in a web server). But in your case, connection pools probably won't be necessary. At least not for the memory problem at hand

Michael Wiles · Accepted Answer · 2011-08-30 07:27:56Z

5

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.

answered Aug 30, 2011 at 7:27

Michael Wiles

21.4k18 gold badges75 silver badges111 bronze badges

4 Comments

Deepak Over a year ago

So does tat mean i should get rid of the static variables and use connection pool ??

JB Nizet Over a year ago

If it's a Swing application with just one thread accessing the database, a single connection is enough. Having several connections won't use less memory than having just one. But I don't see how a single connection object could use the whole memory.

Deepak Over a year ago

@JB Nizet: actually every time i execute a query the objects are created. I have no idea why tat happens. I have added the code if you can have a look and tell me what it is I will be pleased.

JB Nizet Over a year ago

@Deepak: Look at Lukas Eder's answer.

Matteo · Accepted Answer · 2011-08-30 07:31:38Z

0

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

answered Aug 30, 2011 at 7:31

Matteo

15k11 gold badges70 silver badges109 bronze badges

Comments

Charliemops · Accepted Answer · 2011-08-30 07:41:07Z

0

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.

answered Aug 30, 2011 at 7:41

Charliemops

76913 silver badges32 bronze badges

2 Comments

Deepak Over a year ago

can you point out the line where i am creating new objects every time!! tats where i am lost!! thanks!!

Charliemops Over a year ago

Well, you create a new connection when you creates a new variables object. If you call once time in all execution to this constructor, my answer lost importance, but if you are calling it ervery time, then, here is where you are creating a lot of connection objects. This is not necessary bad, but if you do it, you must close the resources (like @Lukas Eder does in his code)

Collectives™ on Stack Overflow

Problem with static variables in java

4 Answers 4

4 Comments

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related