CODEWITHSUNDEEP
javasqlconnection
gfppaste
gfppaste

Reputation: 1121

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

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.

Upvotes: 0

Views: 1888

Answers (2)

uaarkoti
uaarkoti

Reputation: 3657

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 */}
        }
    }
}

Upvotes: 1

Alex
Alex

Reputation: 13941

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.

Upvotes: 0

Related Questions