CODEWITHSUNDEEP
javamysql
Pascal Bayer
Pascal Bayer

Reputation: 2613

Java JDBC: MySQL write Out of Memory

Where is the memory leak in this code? This function is executed millions of times with an extensive usage of memory, causing an out of memory exception after 2.4million execusions.

public static void saveCall(Call call) {
        conn = getInstance();

        if (conn != null) {
            try {
                calendar.setTime(call.getDate());
                String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                        + "VALUES(null, ?, ?, ?, ?)";
                PreparedStatement preparedStatement = conn
                        .prepareStatement(sql);
                preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
                preparedStatement.setLong(2, call.getDuration());
                preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
                preparedStatement.setLong(4, call.getPhonePartner()
                        .getPhoneNumber());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Upvotes: 0

Views: 535

Answers (3)

lifus
lifus

Reputation: 8482

My two cents

JDBC is tricky.

In this case, PreparedStatement should be closed in order to avoid resource leak.

public static void saveCall(Call call) {
    conn = getInstance();

    if (conn != null) {
        calendar.setTime(call.getDate());
        String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                    + "VALUES(null, ?, ?, ?, ?)";

        PreparedStatement preparedStatement = null;            
        try {               
            conn.prepareStatement(sql);
            preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
            preparedStatement.setLong(2, call.getDuration());
            preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
            preparedStatement.setLong(4, call.getPhonePartner()
                    .getPhoneNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }
}

Here is Java 7 solution:

public static void saveCall(Call call) {
    conn = getInstance();

    if (conn != null) {
        calendar.setTime(call.getDate());
        String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                    + "VALUES(null, ?, ?, ?, ?)";

        try (PreparedStatement preparedStatement = conn.prepareStatement(sql)) {                
            preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
            preparedStatement.setLong(2, call.getDuration());
            preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
            preparedStatement.setLong(4, call.getPhonePartner()
                    .getPhoneNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Upvotes: 0

Madhu Sharan
Madhu Sharan

Reputation: 68

@Pascal Bayer : you should CLOSE your connections after the SQL operations. The modified code with close() would look something like the one below.

public static void saveCall(Call call) {
        conn = getInstance();

        if (conn != null) {
            try {
                calendar.setTime(call.getDate());
                String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                        + "VALUES(null, ?, ?, ?, ?)";
                PreparedStatement preparedStatement = conn
                        .prepareStatement(sql);
                preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
                preparedStatement.setLong(2, call.getDuration());
                preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
                preparedStatement.setLong(4, call.getPhonePartner()
                        .getPhoneNumber());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
              // good practice of closing connections as soon as 
              // the SQL operations are       completed
              if(!conn.isClosed())
                conn.close();
            }
        }
}

Cheers, Madhu.

Upvotes: 0

Cyril
Cyril

Reputation: 537

If your program make an extensive memory usage, there might be no leak but only a garbage collector problem. IE, your garbage came too late to free some space for creating new objects.

From here, you might want to profile your code when running your queries (visualvm or jconsole provided with any jdk). You shall see how your memory space is filled (garbage behavior and objects size).

Then, if needed, you will need to configure your jvm garbage collection The extensive documentation is here : http://www.oracle.com/technetwork/java/javase/gc-tuning-6-140523.html If you share your memory profile, we might help you configure it.

EDIT : There was a memory leak and I was wrong ;-)

Upvotes: 1

Related Questions