java - JDBC - 结果集数据处理 : Strange behaviour : default fetchsize returned

Question

我有一个 oracle(10.2) PLSQL 过程，它从 sysrefcursor 中的表中获取 15 条记录。然后我将此游标作为结果集传递给 java 类。这个java类被加载到oracle中。

驱动程序名称:Oracle JDBC 驱动程序驱动程序版本:10.2.0.1.0驱动程序主要版本:10驱动程序次要版本:2

观察结果:

1 在 java 类中，当我迭代结果集时，我只得到前 10 条记录。

2 如果游标获取(20 条或更多记录)或(10 条或更少)，我可以在迭代结果集时获取所有记录。

3 我发现结果集的默认 fetchsize 是 10。如果我将 fetchSize 更改为 5，并且游标获取 8 条记录，那么我可以在迭代结果集时获取前 5 条记录。

4 如果游标获取(10 条或更多记录)或(5 条或更少)，我可以在迭代结果集时获取所有记录。

5 如果我把结果集的fetchSize改为1，那么无论游标取多少条记录，我都可以获得结果集中的所有记录。

为什么结果集表现得很奇怪？

public static BLOB createZip(BLOB prevBlob, String outPrefix, ResultSet entries, ResultSet rs, Long[] resultRows) throws Exception
{
    OracleConnection conn = null;
    BLOB retBLOB = null;
    int page   = 1;
    int curRow = 0;
    long totalRows = 0;
    try
    {
        conn = (OracleConnection) new OracleDriver().defaultConnection();
        ArrayList entryList = loadEntries(entries);
        retBLOB = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
        retBLOB.open(BLOB.MODE_READWRITE);
        OutputStream bOut = retBLOB.setBinaryStream(0L);
        ZipOutputStream zipOut = new ZipOutputStream(bOut);
        PrintStream out = new PrintStream(zipOut);
        zipOut.putNextEntry(new ZipEntry(outPrefix + "-p" + page + ".csv"));
        writeHeader(out, entryList);
        while (rs.next())
        {
            curRow++;
            totalRows++;
            if (curRow >= maxPageSize)
            {
                zipOut.closeEntry();
                page++;
                zipOut.putNextEntry(new ZipEntry(outPrefix + "-p" + page + ".csv"));
                writeHeader(out, entryList);
                curRow = 0;
            }
            for (int i = 0; i < entryList.size(); i++)
            {
                Entry e = (Entry) entryList.get(i);
                if (i != 0)
                {
                    out.print(",");
                }
                if (e.isEscape())
                    out.print("\"" + escapeExcel(rs.getString(e.getColumn())) + "\"");
                else
                    out.print("\"" + emptyExcel(rs.getString(e.getColumn())) + "\"");
            }
            out.println();
        }

        if (totalRows == 0)
        {
            out.println("\"No Entries Found\"");
        }
        resultRows[0] = new Long(totalRows);
        out.flush();
        zipOut.closeEntry();
        if (prevBlob != null)
        {
            byte[] buf = new byte[1024];
            InputStream bIn = prevBlob.binaryStreamValue();
            ZipInputStream zipIn = new ZipInputStream(bIn);

            ZipEntry inEntry = zipIn.getNextEntry();
            while (inEntry != null)
            {
                zipOut.putNextEntry(new ZipEntry(inEntry.getName()));
                int len;
                while ((len = zipIn.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                inEntry = zipIn.getNextEntry();
            }

            zipIn.close();

            try
            {
                prevBlob.freeTemporary();
            }
            catch (SQLException e) { }

        }

        zipOut.close();
        retBLOB.close();
        return retBLOB;
    }
    catch (Exception sex)
    {
        if (retBLOB != null)
        {
            try
            {
                retBLOB.freeTemporary();
            }
            catch (SQLException e) { }
        }
        throw sex;
    }
    finally
    {

        try { entries.close(); } catch (SQLException sex) { }
        try { rs.close(); } catch (SQLException sex) { }
        try
        {
            if (conn != null || !conn.isClosed())
            {
                conn.close();
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

    }

}

Answer 1

有一个解决方法。我在 while(rs.next()) 之前获取了列索引。下面的代码片段对我来说绝对有效。但我仍然无法理解为什么 resultSet.getString(columnName); 在 while(rs.next()) 内失败。

    ArrayList entryList = loadEntries(entries);
    int[] colIndx = new int[entryList.size()];
    ResultSetMetaData rsmd = rs.getMetaData();
    int numberOfColumns = rsmd.getColumnCount();
    for (int i = 0; i < entryList.size(); i++){
       Entry e = (Entry) entryList.get(i);
       for (int j = 1; j <= numberOfColumns; j++){
          if(rsmd.getColumnName(j).equalsIgnoreCase(e.getColumn()))
              colIndx[i] = j;
       }
    }
    try{
        while (rs.next()){
            for (int i = 0; i < colIndx.length ; i++){
                System.out.println("Column Values["+colIndx[i]+"] : "+rs.getString(colIndx[i]));
            }
        }
    }