java - SELECT COUNT(*) FROM (SELECT) AS SubQuery 在 MS ACCESS 中有效，但在 JDBC 中无效

Question

我的查询:

SELECT COUNT(*) 
FROM 
   (SELECT 
        dealerCode, 
        SUM(kg) AS totalKG, 
        SUM(price) AS totalPrice, 
        returnDate, BID 
    FROM meatReturns 
    GROUP BY BID, dealerCode, returnDate)

这在 MS Access 查询设计中运行良好，但在 JDBC 中不起作用。

查询在其 ResultSet 中出现 Syntax error in FROM clause。

对此有任何替代方案或解释吗？

更新:

这是我的代码:

private int getRowCount(String query){
       int size = 0; 
       try {
            query = query.replace(";", "");
            query = "SELECT COUNT(*) FROM (" + query +") AS subQuery;";

            System.out.println(query);

            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.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);


            ResultSet rs = stmt.executeQuery(); // ERROR HERE
            rs.beforeFirst();
            while(rs.next() && rs!=null){
                size = rs.getInt(1);
                System.out.println("Size : "+size);
            }
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
       return size;
   }

这是错误代码:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3117)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:252)
    at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6352)
    at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:154)
    at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:423)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:92)
    at com.eaca.MainFrame.getRowCount(MainFrame.java:139)
    at com.eaca.MainFrame.connectToDBWithRows(MainFrame.java:171)
    at com.eaca.MainFrame.loadMR(MainFrame.java:350)
    at com.eaca.MainFrame.access$600(MainFrame.java:65)
    at com.eaca.MainFrame$ChangeTab.stateChanged(MainFrame.java:486)
    at javax.swing.JTabbedPane.fireStateChanged(JTabbedPane.java:416)
    at javax.swing.JTabbedPane$ModelListener.stateChanged(JTabbedPane.java:270)
    at javax.swing.DefaultSingleSelectionModel.fireStateChanged(DefaultSingleSelectionModel.java:132)
    at javax.swing.DefaultSingleSelectionModel.setSelectedIndex(DefaultSingleSelectionModel.java:67)
    at javax.swing.JTabbedPane.setSelectedIndexImpl(JTabbedPane.java:616)
    at javax.swing.JTabbedPane.setSelectedIndex(JTabbedPane.java:591)
    at javax.swing.plaf.basic.BasicTabbedPaneUI$Handler.mousePressed(BasicTabbedPaneUI.java:3644)
    at java.awt.Component.processMouseEvent(Component.java:6502)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
    at java.awt.Component.processEvent(Component.java:6270)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4861)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4489)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:708)
    at java.awt.EventQueue$4.run(EventQueue.java:706)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
Exception in thread "AWT-EventQueue-0" java.lang.ArrayIndexOutOfBoundsException: 0
    at com.eaca.MainFrame.connectToDBWithRows(MainFrame.java:185)
    at com.eaca.MainFrame.loadMR(MainFrame.java:350)
    at com.eaca.MainFrame.access$600(MainFrame.java:65)
    at com.eaca.MainFrame$ChangeTab.stateChanged(MainFrame.java:486)
    at javax.swing.JTabbedPane.fireStateChanged(JTabbedPane.java:416)
    at javax.swing.JTabbedPane$ModelListener.stateChanged(JTabbedPane.java:270)
    at javax.swing.DefaultSingleSelectionModel.fireStateChanged(DefaultSingleSelectionModel.java:132)
    at javax.swing.DefaultSingleSelectionModel.setSelectedIndex(DefaultSingleSelectionModel.java:67)
    at javax.swing.JTabbedPane.setSelectedIndexImpl(JTabbedPane.java:616)
    at javax.swing.JTabbedPane.setSelectedIndex(JTabbedPane.java:591)
    at javax.swing.plaf.basic.BasicTabbedPaneUI$Handler.mousePressed(BasicTabbedPaneUI.java:3644)
    at java.awt.Component.processMouseEvent(Component.java:6502)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
    at java.awt.Component.processEvent(Component.java:6270)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4861)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4489)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:735)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:694)
    at java.awt.EventQueue$3.run(EventQueue.java:692)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:708)
    at java.awt.EventQueue$4.run(EventQueue.java:706)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:705)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

Answer 1

你的问题源于

1. 子查询是一个聚合查询(使用 SUM() 函数和一个 GROUP BY 子句)所以 Access 数据库引擎返回的结果集不是可更新，并且

2. 在您的 prepareStatement 调用中指定 ResultSet.TYPE_SCROLL_SENSITIVE。

这些条件相互冲突，导致错误。试试这个:

stmt = conn.prepareStatement(query,
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery();
// rs.beforeFirst();  // (disabled)