Derby/Intellij java.sql.SQLSyntaxErrorException:表/视图‘酒精’不存在

Question

我尝试通过IntelliJ设置一个简单的嵌入式Derby数据库。当我试图从jdbc访问这些表时，我得到的错误是这些表不存在。与jdbc不同的是，当我为数据库输入终端时，同样的查询会得到数据。

​

​

方法

Scanner input = new Scanner(System.in);
        int wob = 0;
        ResultSet rset;
        Statement stmt;

        String qry = "SELECT * FROM ALCOHOL WHERE ALCOHOL.ALCOHOL_TYPE = ";

        do{
            System.out.println("");
            System.out.println("1: For Malt Beverages");
            System.out.println("2: For Wine");
            System.out.println("");
            wob = input.nextInt();
        }while(wob != 1 && wob != 2);

        stmt = conn.createStatement();
        rset = stmt.executeQuery(qry + wob);

        System.out.println(" ID|                     Name|               BrandName |APPELLATION |      Type");
        System.out.println("---|-------------------------|-------------------------|------------|----------");

        while(rset.next()){
            String ID = String.format("%1$"+3+ "s", rset.getString("AID"));
            String name = String.format("%1$"+25+ "s", rset.getString("NAME"));
            String brandname = String.format("%1$"+25+ "s", rset.getString("BRAND_NAME"));
            String app = String.format("%1$"+22+ "s", rset.getString("APPELLATION"));
            String type = String.format("%1$"+10+ "s", rset.getString("ALCOHOL_TYPE"));
            System.out.println(ID + "|" + name + "|" + brandname + "|" + app + "|" + type);
        }
        System.out.println("---|-------------------------|-------------------------|------------|----------");

        rset.close();
        stmt.close();
        input.close();

跟踪：

java.sql.SQLSyntaxErrorException: Table/View 'ALCOHOL' does not exist.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
    at Main.search(Main.java:89)
    at Main.main(Main.java:28)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: ERROR 42X05: Table/View 'ALCOHOL' does not exist.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
    at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
    at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
    at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
    at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 9 more

Answer 1

首先，表名是区分大小写的，请对其进行快速检查；其次，如果您的查询使用了正确的表名，则需要更改表名以添加模式详细信息。

这就是一个这样的例子，您如何使用一个简单的SELECT查询。

SELECT * FROM {schema}."{tablename}"

如果你想把上面的查询翻译成JAVA，你可能需要使用它，并且它应该可以工作。

String query = "SELECT * FROM {schema}.\"ALCOHOL\"";

希望这能对你有所帮助！