Abrupt Resultset behavior in java

I am stuck with an issue over past 2-3 days. It seems to be a minor issue but I have not been able to catch it.

The issue is with resultset rs in the code. The while (rs.next()) statement in method mergeTable is behaving abruptly. For the first time, On some occasions it goes inside the while loop, sometimes it doesn’t and on few occasions when it does enter the while loop, it comes out abruptly throwing resultset exhausted exception. I googled and found that the resultset might have been getting closed by some other thread accessing the code. But this is a simple standalone java application and is not multi-threaded.

I am also taking care the other two resultsets, check and checktarget doesn’t interfere with resultset rs. I am closing the statements and resultsets also. Can you plz have a look at the code and see if I am missing out on something.

The two database instances being used are 10.180.22.93:1521:V3demo(called source database) and 10.180.22.93:1521:fusiondb(called target database). rs resultset is from the source database.Resultsets check and checktarget are from the target database. So the resultset rs will be from table A from source database and resultsets check and checktarget will be from table A from target database.

    static String mergeTable() throws Exception {
    String result = "ERROR";
    int error = 0;
    String tableString = "<table " + tablename + ">";

    PreparedStatement preparedSelect = null;
    PreparedStatement preparedSelectTarget = null;
    Statement selectSourceStmt = null;
    ResultSet checkTarget = null;
    ResultSet rs = null;
    try {

        logger.println("====================================================================================");
        logger.println("Processing table:" + tablename);
        System.out.println("====================================================================================");
        System.out.println("Processing table:" + tablename);

        // Create query to fetch records from the source
        String sourceQuery = "SELECT * FROM " + tablename;
        if (owner.trim().equals("F1") || owner.trim().equals("C1") || owner.trim().equals("CM"))
            sourceQuery = sourceQuery + " WHERE OWNER_FLG='" + owner + "'";

        // Get the result set

        selectSourceStmt = source.createStatement();
        rs = selectSourceStmt.executeQuery(sourceQuery);

        System.out.println(sourceQuery);

        String selectSQL = "SELECT COUNT(*) FROM " + tablename + " WHERE ";
        String selectSQLTarget = "SELECT * FROM " + tablename + " WHERE "; // ankush

        ResultSetMetaData metaData = rs.getMetaData();

        List list = new ArrayList();
        List typesList = new ArrayList();

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            String columnName = metaData.getColumnName(i);
            list.add(columnName); // list contains the entire list of columns of the source
            typesList.add(metaData.getColumnType(i));

        }

        for (int i = 1; i < keys.length; i++) {

            if (i == 1) {
                selectSQL = selectSQL + " " + keys[i] + "= ?";
                selectSQLTarget = selectSQLTarget + " " + keys[i] + "= ?"; //ankush 
            }

            else {
                selectSQL = selectSQL + " AND " + keys[i] + "= ?";
                selectSQLTarget = selectSQLTarget + " AND " + keys[i] + "= ?"; //ankush
            }

        }

        logger.println("Select SQL:" + selectSQL);
        logger.println("selectSQLTarget:" + selectSQLTarget); //ankush

        preparedSelect = target.prepareStatement(selectSQL);
        preparedSelectTarget = target.prepareStatement(selectSQLTarget); //ankush

        int updateCount = 0, insertCount = 0, errorCount = 0;
        // rs contains the entire table snapshot of source  based on the owner flag
        if (rs != null) {

            while (rs.next()) {
                try {
                    int i, count;

                    // check if record exists or not; keys contain the values of primary columns specified in the.lst file
                    for (int j = 1; j < keys.length; j++) {
                        preparedSelect.setObject(j, rs.getObject(keys[j])); // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared

                    }

                    ResultSet check = preparedSelect.executeQuery(); // check is the target resultset for the primary key values in current row of source resultset

                    check.next();

                    count = check.getInt(1); // count gives the row/s fetched from target based on the values in source.
                    check.close();

                    // check if record exists or not; keys contain the values of primary columns specified in the.lst file
                    for (int j = 1; j < keys.length; j++) {
                        // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared
                        preparedSelectTarget.setObject(j, rs.getObject(keys[j]));

                    }

                    // check is the target resultset for the primary key values in current row of source resultset  
                    checkTarget = preparedSelectTarg开发者_如何学Pythonet.executeQuery();

                    checkTarget.next();

                    // if record exists  UPDATE CONDITION
                    if (true) { // if there is a record in target for a row in source, update target
                        String rowString = "<row>";
                        String rowDiffFlag = "N";
                        // if merge flag is Y
                        if (mergeFlag.equals("Y")) {
                            String colDiffFlag = "";
                            String sourceColVal = "";
                            String targetColVal = "";
                            // list contains the column names
                            for (i = 0; i < list.size(); i++) {
                                System.out.println("value of i " + i);
                            }
                            i++; // ?????

                        } else {
                                logger.print("Did not update Record:");


                        }
                        rowString = rowString + "</row>";

                        if (rowDiffFlag.equals("Y")) {
                            tableString = tableString + rowString;
                        }

                    } else { // if there is no record in target for a row in source, insert into target
                        String sourceColVal = "";
                        String rowString = "<row>";
                        for (i = 0; i < list.size(); i++) { //looping through columns in a row
                            System.out.println("column " + i);
                            }

                        rowString = rowString + "</row>";
                        tableString = tableString + rowString;
                    }

                } catch (Exception e1) {

                    e1.printStackTrace(logger);

                }
            }
        }

    } catch (Exception e) {

        e.printStackTrace(logger);

    } finally {
        preparedSelect.close();
        preparedSelectTarget.close();
        selectSourceStmt.close();
        checkTarget.close();
        rs.close();
    }

    tableString = tableString + "</table>";
    formXmlString(tableString);

    if (error == 0) result = "SUCCESS";

    return result;
}

---

Oh, my, you've got far too much going on here. I see HTML and JDBC code mingled together. That's a bad idea.

Database connections aren't thread-safe. You say you're taking care to ensure that your ResultSets don't interfere, but this error suggests otherwise. I would refactor this code to isolate the persistence and make every thing simpler. This just looks wrong to me, but I'm not willing to pore through it to figure out why.

---

Are you sure the resultset exhausted exception is on the rs ResultSet and not on the other two possible ResultSets : check and checkTarget ?