Program with lots of queries just stops, no exceptions

Question

I'm running a program that is making a query for several thousand individuals. About 2/3 of the way through the list, it just stops...no exception, nothing. It just won't continue.

I'm not sure exactly what is going on here, why it just stops. I don't see anything wrong with the data (which would generate an exception anyway). Am I doing too many queries in a row?

Thanks in advance for any suggestions.

File inputFile = new File(datafile);
BufferedReader br = new BufferedReader(new FileReader(inputFile));
List <WRLine> empList = new ArrayList<>();
String s;
int counter = 0;
while ((s = br.readLine()) != null) {
  String[] sLine = s.split(",");
  if (sLine.length > 3) {
    try {
      //if it's a number, it's not a name.  Skip the line.
      int i = Integer.parseInt(sLine[0].trim());
    } catch (Exception e) {
      //if it's not a number and not blank, add it to the list
      if (!sLine[2].equals("")) {
        try {
          int q = Integer.parseInt(sLine[2].trim());
          WRLine wr = new WRLine(sLine[0], sLine[2], sLine[3]);
          empList.add(wr);
        } catch (Exception ex) {
          //continue
        }
      }
    }
  }
}
//empList contains 1,998 items
Map<String, Integer> resultMap = new HashMap<>();
Iterator i = empList.iterator();
try {
  String connectionURL = "jdbc:mysql://" + ip + ":" + port + "/" + dbName + "?user=" + userName + "&password=" + pw;
  Class.forName("com.mysql.jdbc.Driver");
  Connection conn = DriverManager.getConnection(connectionURL);
  PreparedStatement ps = null;
  ResultSet rs = null;
  String query = "";
  while (i.hasNext()) {
    WRLine wr = (WRLine) i.next();
    System.out.println("Searching " + wr.getName() + "...");
    query = "Select count(*) as APPLIED from request where (requestDate like '%2017%' or requestDate like '%2018%') AND officer=(select id from officer where employeenumber=?)";
    ps = conn.prepareStatement(query);
    ps.setString(1, wr.getEmployeeNum());
    rs = ps.executeQuery();
    while (rs.next()) {
      int queryResult = rs.getInt("APPLIED");
      //if the division is already in there
      if (resultMap.containsKey(wr.getDivision())) {
        Integer tmp = resultMap.get(wr.getDivision());
        tmp = tmp + queryResult;
        resultMap.put(wr.getDivision(), tmp);
      } else {
        resultMap.put(wr.getDivision(), queryResult);
      }
    }
  }

  rs.close();
  ps.close();
  conn.close();
} catch (Exception e) {
  e.printStackTrace();
}
//report by division

There could be a lot of things. Are you using a connection pool? If yes, then what is the size of the pool? Have you checked the DB side and verified that it is serving all the requests? — user2004685, Feb 26 '18 at 20:18
You could generate thread dump to see what code is executed when application hangs. If you are running on Linux you may face this issue: https://stackoverflow.com/questions/2327220/oracle-jdbc-intermittent-connection-issue — Ivan, Feb 26 '18 at 20:20
I don't know if it'll help, but I would pull the "query = ..." and the "ps = conn.." outside of the while loop. There is no need to execute them more than once. — bcr666, Feb 26 '18 at 20:29
You're creating a lot of resultsets and only close the last at the end of the while-loop. Close each resultset before continuing with the next entry of the Iterator. — Lothar, Feb 26 '18 at 20:43
You're likely exhausting the resources, because you never close all the many resources that are allocated in your loop. **Use try-with-resources** to help ensure correct resource management. — Andreas, Feb 26 '18 at 20:52

score 1 · Answer 1 · answered Feb 26 '18 at 22:52

Summarizing what others have said in the comments, your problem could be due to improper JDBC resource handling. With Java 7 and above, you should use the try-with-resources statement, which frees resources automatically. Also, as of JDBC 4, you don't need to call Class.forName() explicitly. Finally, you should never prepare a PreparedStatement inside a loop when the only thing that changes is the bind variable.

Putting this together, the data access part could be rewritten as

String connectionURL = "jdbc:mysql://" + ip + ":" + port + "/" + dbName 
    + "?user=" + userName + "&password=" + pw;
String query = "Select count(*) as APPLIED from request where " 
    + "(requestDate like '%2017%' or requestDate like '%2018%') " 
    + "AND officer=(select id from officer where employeenumber=?)";

try (Connection conn = DriverManager.getConnection(connectionURL);
     PreparedStatement ps = conn.prepareStatement(query)) {
    while (i.hasNext()) {
        WRLine wr = (WRLine) i.next();
        System.out.println("Searching " + wr.getName() + "...");
        ps.setString(1, wr.getEmployeeNum());

        // the result set is wrapped in its own try-with-resources
        // so that it gets properly deallocated after reading
        try (ResultSet rs = ps.executeQuery()) {

            // SQL count is a scalar function so we can just use if instead of while
            if (rs.next()) {
                int queryResult = rs.getInt("APPLIED");

                //if the division is already in there
                if (resultMap.containsKey(wr.getDivision())) {
                    Integer tmp = resultMap.get(wr.getDivision());
                    tmp = tmp + queryResult;
                    resultMap.put(wr.getDivision(), tmp);
                } else {
                    resultMap.put(wr.getDivision(), queryResult);
                }
            }
        }
    }
} catch (SQLException e) {
    // consider wrapping as a RuntimeException and rethrowing instead of just logging
    // because these are usually caused by 
    // programming errors or fatal problems with the DB
    e.printStackTrace();
}

Program with lots of queries just stops, no exceptions

1 Answers1