Java total memory and memory usage increase when using mysql function?

Question

We have a snippet of codes which does a select based on the mysql defined function.We have caught that once this portion is enabled then runtime.totalMemory() and used memory keep increasing. Below is the snippet of codes. The problem starts from String selectQuery4. Any idea why is this a problem?

double currentLong = Double.parseDouble(longitude);
double currentLat = Double.parseDouble(latitude); 

Statement stmt6 = null;
stmt6 = dbconn.createStatement(); 

String selectQuery3 = "Select geoFenceID,enterpriseID,clientID,geoFenceString,geoFenceType,geoFenceName,geoFenceDescription,geoFencePreference, geoFenceRadius From tblGeoFence Where ((enterpriseID="+enterpriseID+" And clientID=0) Or (enterpriseID="+enterpriseID+" And clientID="+clientID+")) And geoFenceStatus='a'"; 
ResultSet rs3 = stmt6.executeQuery(selectQuery3);

while(rs3.next()) {
    geoFenceID = rs3.getInt("geoFenceID");
    geoFenceType = rs3.getString("geoFenceType");
    geoFenceString = rs3.getString("geoFenceString");
    geoFenceRadius = rs3.getInt("geoFenceRadius");
    geoFencePreference = rs3.getString("geoFencePreference");
    String polygon  =  "GeomFromText('"+geoFenceString+"')";
    String point    =  "GeomFromText('POINT("+currentLong+"  "+currentLat+")')";

    Statement stmt7 = null;
    stmt7 = dbconn.createStatement();
    String selectQuery4 = "SELECT GISWithin("+point+","+polygon+") As geoFenceStatus";
    ResultSet rs4 = stmt7.executeQuery(selectQuery4);

    if(rs4.next()) {

        if(rs4.getInt("geoFenceStatus")==1) {

            geoFenceIDFound=geoFenceID;
            geoFenceName = rs3.getString("geoFenceName");
            geoFenceDescription = rs3.getString("geoFenceDescription");                                                     
            geoFencePreference = rs3.getString("geoFencePreference");   

            if(lastGeoFenceID==geoFenceID) {
                geoFenceInID = geoFenceID;                  
                break;
            } else {

                if(previousGeoFenceID==geoFenceID &&(previousTimeDifferenceInt<0 || geoFenceArriveTimeDifferenceInt <= 0)) //check the funny problem double entry back into the geo fence
                {
                    lastGeoFenceID=geoFenceID;
                    geoFenceInID = geoFenceID;
                    break;
                } else {
                    geoFenceAlertEmailMessage="\nGeo Fence Alert Arrival\nTime:"+dateTimer+"\nGeo Fence Name :"+geoFenceName+"\nGeo Fence Description :"+geoFenceDescription+"\nGeo Fence Preference:"+geoFencePreference;

                    if(lastGeoFenceEntryStatus.equals("") || lastGeoFenceEntryStatus.equals("Out")) {
                        geoFenceEntryStatus="In";
                    } else {
                        geoFenceEntryStatus="Out";
                    }
                }
            }
        }
    }
}

try {
    if ( rs4 != null ) {
        rs4.close();
    }                                        
    if ( stmt7!= null ) {
        stmt7.close();
    }
} catch(SQLException ex) {
    ex.printStackTrace(System.out);
}             
}           
try {
    if ( rs3 != null ) {
        rs3.close();
    }                              
    if ( stmt6!= null ){
        stmt6.close();
    }                              
} catch(SQLException ex) {
    ex.printStackTrace(System.out);
} 

Codes used to print the memory stats.

int mb = 1024*1024;
System.out.println("##### Heap utilization statistics [MB] #####");

//Print used memory
System.out.println("Used Memory:" + (runtime.totalMemory() - runtime.freeMemory()) / mb);

//Print free memory
System.out.println("Free Memory:" + runtime.freeMemory() / mb);

//Print total available memory
System.out.println("Total Memory:" + runtime.totalMemory() / mb);

//Print Maximum available memory
System.out.println("Max Memory:" + runtime.maxMemory() / mb);

Answer 1

Your code should call close in try/finally blocks. You should also use PreparedSTatements instead of parameters concat in a string.

Regarding your question, it is not sure you have a problem:

• How much iterations does your first while loop do ?

• use jconsole to connect to your jvm and call garbage collect after the loop end to see if memory drops down:

• if it does then you don't have a real pb

• if it does not then you have

Regards