MS Access [Microsoft][ODBC Driver Manager]游标状态无效

Question

我在这个代码片段中有错误:

   private String[][] connectToDB(String query) throws ClassNotFoundException{
        String[][] results = null;
        try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String db = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:/EACA_AgroVentures1.accdb";
                conn = DriverManager.getConnection(db);
                stmt = conn.prepareStatement(query);
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData rsm = rs.getMetaData();
                rs.beforeFirst();
                int columns = rsm.getColumnCount();
                int rows = getRowCount(rs);
                //int rows = rs.getFetchSize();
                int rowCount = 0;
                results = new String[rows][columns];
                //System.out.println(rows+" "+columns);
                while((rs!=null) && (rs.next())){                    
                     for(int i = 1; i < columns; i++){
                        results[rowCount][i-1] = rs.getString(i); // --> ERROR SHOWS HERE
                        //System.out.println(rowCount+","+i+" = "+rs.getString(i));
                     }
                     rowCount++;
                }
                rs.getStatement().close();
                conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
        return results;
    }

我的查询包含以下内容:

private void loadMR(){
      try {
            String query = "SELECT dealerCode, SUM(kg) AS totalKG, SUM(price) AS totalPrice, returnDate, BID FROM meatReturns GROUP BY BID, dealerCode, returnDate;";            
                Object[][] result = connectToDB(query);
// some more code below..

我尝试使用第一个代码与另一种方法中给出的其他查询:

private void loadDealers(){
        try {
            String query = "SELECT * FROM Dealers";            
            Object[][] result = connectToDBWithRows(
query);
// some more code..

，它运行得很好。这是怎么回事?我怎样才能解决这个问题?

UPDATE: connectToDBWithRows和connectToDB的唯一区别是管理resultSet的while循环

// Snippet from connectToDBWithRows()
while((rs!=null) && (rs.next())){
                    for(int i = 0; i < columns; i++){
                        if (i == 0){
                           // Do nothing
                        }else{
                           results[rowCount][i] = rs.getString(i);
                           //System.out.println(rowCount+","+i+" = "+rs.getString(i)); 
                        }
                    }
                    rowCount++;
                }

这是我的getRowCount()方法

  private int getRowCount(ResultSet resultSet){
        int size = 0;
        try {
            resultSet.last();
            size = resultSet.getRow();
            resultSet.beforeFirst();
        }
        catch(Exception ex) {
            return 0;
        }
        return size;
    }

Answer 1

我注意到，有时Access在引用sql语句中的列时需要指定表名。试试以下命令:

private void loadMR(){
      try {
            String query = "SELECT meatReturns.dealerCode, SUM(meatReturns.kg) AS totalKG, SUM(meatReturns.price) AS totalPrice, meatReturns.returnDate, meatReturns.BID FROM meatReturns GROUP BY meatReturns.BID, meatReturns.dealerCode, meatReturns.returnDate";
                Object[][] result = connectToDBWithRows(query);