java - 创建sql表时语法错误

Question

我正在尝试创建下表，但我不断收到错误。在我看来，Java Db 试图将“create table ORDER”解析为“ORDER BY”。我也尝试输入“CREATE TABLE”，但正如预期的那样，没有结果。如何解决这个问题？

public void createTable() throws SQLException {
        String createString = "create table ORDER "
                + "(ORDER_ID int  NOT NULL, " + "ORDER_NUMBER int NOT NULL, "
                + "PERSON_ID int NOT NULL, " + "PRIMARY KEY (ORDER_ID), "
                + "FOREIGN KEY (PERSON_ID) REFERENCES PERSON (PERSON_ID))";
        Statement stmt = null;
        System.out.println(createString);
        try {

            DatabaseMetaData meta = con.getMetaData();
            ResultSet tables = meta.getTables(null, null, "ORDER",
                    new String[] { "TABLE2" });
            int size = 0;
            while (tables.next()) {
                size++;
            }
            if (size == 0) {
                System.out.println("table created");
                stmt = con.createStatement();
                stmt.executeUpdate(createString);

            }
        } catch (SQLException e) {
            System.out.println("No connection");
            e.printStackTrace();
            // TODO make exeption
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

错误

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at line 1, column 14.

    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.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.executeUpdate(Unknown Source)
    at com.tuto.p4.OrderTable.createTable(OrderTable.java:38)
    at com.tuto.p4.Main.main(Main.java:18)
Caused by: java.sql.SQLException: Синтаксическая ошибка: Encountered "ORDER" at line 1, column 14.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 10 more
Caused by: ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 14.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(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)
    ... 4 more

Answer 1

我确实相信“ORDER”是一个保留字，因此您不应将其用作表名或列名。尝试使用简单的同义词来描述您的功能，例如“POSITION”或“SORTED”等。祝您好运!

编辑:如果您确实必须使用该词，您可以尝试将其用引号引起来，例如

create table `ORDER`

强制 SQL 引擎尝试将其作为字符串读取。