how to set up a persistent connection with a DB server using Java?

Question

So I'm trying to optimize a Java application that uses the java sql library to read and write from a remote SQL server. (this is for a project).

We are limited to the following libraries:

com.mysql.*
java.io.*
java.lang.*
java.sql.*
java.text.*
java.util.*
javax.sql.*

And we have an 8MB memory limit on our JVM.

The way the code is set up right now, for every function that needs to access the database, a new connection is created and closed. for example:

public static void Read()
{
    Connection connection = NULL;
    try {
        connection  = DriverManager.getConnection(EnvManager.getDbUrl());
        Statement statement = connection.createStatement();

    statement.setFetchSize(Integer.MIN_VALUE);

        ******* FUNCTION ********
    }finally {
    if(connection != null) {
        try {
        connection.close();
        } catch (Exception e) {
        }
    }
}

     public static void Write()
{
    Connection connection = NULL;
    try {
        connection  = DriverManager.getConnection(EnvManager.getDbUrl());
        Statement statement = connection.createStatement(); 
    statement.setFetchSize(Integer.MIN_VALUE);

        ******* FUNCTION ********
    }finally {
    if(connection != null) {
        try {
        connection.close();
        } catch (Exception e) {
        }
    }
}

etc...

I'm trying to set up a persistent connection so that I can either pass in the connection object, i.e.

...
...
Connection connection = NULL;
try {
    connection  = DriverManager.getConnection(EnvManager.getDbUrl());
Read(connection);
Write(connection);

}finally {
    if(connection != null) {
        try {
        connection.close();
        } catch (Exception e) {
        }
    }
...
...

However, when I try to do this, whenever I try to write and then read a large file to the database (anything over 5 MB), my JVM keeps throwing errors that it's running out of memory.

My question is basically, what happens when you make a single connection object and keep passing it into a function? It seems like it somehow grows or replicates every time it's used.

I looked at this website:

http://benjchristensen.com/2008/05/27/mysql-jdbc-memory-usage-on-large-resultset/

Which suggested I use the statement.setFetchSize(Integer.MIN_VALUE); function, in order to curtail the JVM buffering the entire result set in memory by default, but that didn't help.

Any ideas on how to fix this issue would be greatly appreciated, thank you.

uaarkoti · Accepted Answer · 2012-03-23 19:39:11Z

If you are passing the same object reference to other methods, it should NOT be replicating anything. You are simply passing a reference to the object. what might be happening is that you are probably not closing your Statements or PreparedStatements everytime you execute your SQL.

You need to close all the resources that will consume resources after you are done executing your query. Here is an example

public void Read() {
    Connection conn = null;
    Statement statement = null;
    ResultSet rs = null;

    try {
        connection  = DriverManager.getConnection(EnvManager.getDbUrl());
        statment = connection.createStatement();
        statement.setFetchSize(Integer.MIN_VALUE);

        // Do more stuff, iterate to ResultSet etc...
    } catch (SQLException ex) {
        // Exception handling stuff
        ...
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) { /* ignored */}
        }
        if (statment != null) {
            try {
                statment.close();
            } catch (SQLException e) { /* ignored */}
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) { /* ignored */}
        }
    }
}

Alex · Accepted Answer · 2012-03-23 19:31:17Z

0

You probably need to close the statements in Read() and Write(). Resources associated with those objects (in this case, the buffers used to read/write the file data) aren't released until they're closed. Closing the connection automatically closes the statement that was created with it, but if you're going to keep the connection open then you need to close the statement when you're done with it.

answered Mar 23, 2012 at 19:31

Alex

14k1 gold badge40 silver badges51 bronze badges

Collectives™ on Stack Overflow

how to set up a persistent connection with a DB server using Java?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related