java - 我正在尝试使用 java derby EmbeddedDriver 创建一个表，但我不明白这些错误是什么意思？

Question

错误:

run:
Loaded the appropriate driver
Created and connected to database localDB2

----- SQLException -----
  SQL State:  42X01
  Error Code: 30000
  Message:    Syntax error: Encountered "user" at line 1, column 14.

___ DBTest completed ___
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "user" 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.execute(Unknown Source)
    at localDataBase.start(localDataBase.java:56)
    at localDataBase.main(localDataBase.java:17)
Caused by: java.sql.SQLException: Syntax error: Encountered "user" 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 "user" 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
BUILD SUCCESSFUL (total time: 0 seconds)

代码:

import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class localDataBase {
    private String driver =   "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:";
    private String dbName =   "localDB2";
    private DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    private Date date = new Date();
    private Calendar cal = Calendar.getInstance();
    public static void main(String[] args) {
        new localDataBase().start();
        System.out.println("\n___ DBTest completed ___");
    }

    void start() {
    // Load derby driver to start derby engine 
    try {
        Class.forName(driver);
            System.out.println("Loaded the appropriate driver");
    } catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        System.exit(1);
        }

    // establishing a connection to it

    // Initializers; declared and set to nulls before the try-block
    // to be accessible after the try-block scope
    Connection conn = null;
    PreparedStatement psInsertUser = null;
        PreparedStatement psInsertAdmin = null;
        PreparedStatement psInsertBill = null;
    PreparedStatement psUpdate = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

        conn = DriverManager.getConnection(protocol + dbName +
                           ";create=true", "test", "test");

            System.out.println("Created and connected to database " + dbName);

            stmt = conn.createStatement();


            // Create User table
            stmt.execute("create table user(" +
             "id int, " +
             "firstName varchar(20), " +
             "lastName varchar(20)" +
                         "userName varchar(20))");

            System.out.println("\n___ Created table user ___");

            // Create Administrator table
            stmt.execute("create table administrator(" +
             "id int, " +
             "firstName varchar(20), " +
             "lastName varchar(20)" +
                         "userName varchar(20))");

            System.out.println("\n___ Created administrator user ___");

            // Create Bill table
            stmt.execute("create table bill(" +
                         "yearOfProduction int, " +
             "serialNumber varchar(20), " +
                         "value int, " +
             "location varchar(20)" +
                         "dateAndTimeCounted varchar(20))");

            System.out.println("\n___ Created table bill ___");

            //PreparedStatement to insert into User table
            psInsertUser = conn.prepareStatement
        ("insert into user values (?, ?, ?, ?)");

            // User1 Data
            psInsertUser.setInt(1, 1);  //insert ID
        psInsertUser.setString(2, "Davy ");    //insert firstName
        psInsertUser.setString(3, "Jones");    //insert lastName
            psInsertUser.setString(3, "User1");    //insert userName
            psInsertUser.executeUpdate();

        System.out.println("\n ____ Inserted: User, Davy Jones");



            //PreparedStatement to insert into Administrator table
            psInsertAdmin = conn.prepareStatement
        ("insert into administrator values (?, ?, ?, ?)");

            // Admin1 Data
            psInsertAdmin.setInt(1, 1);  //insert ID
        psInsertAdmin.setString(2, "Cutler ");    //insert firstName
        psInsertAdmin.setString(3, "Beckett");    //insert lastName
            psInsertAdmin.setString(3, "Admin1");    //insert userName
            psInsertAdmin.executeUpdate();

        System.out.println("\n ____ Inserted: Administrator, Cutler Beckett");


            //PreparedStatement to insert into Bill table
            psInsertBill = conn.prepareStatement
        ("insert into bill values (?, ?, ?, ?, ?)");

            // Bill1 Data
            psInsertBill.setInt(1, 2011);  //insert year of production
        psInsertBill.setString(2, "XY3215616 ");    //insert serial number
        psInsertBill.setInt(3, 50);    //insert value
            psInsertBill.setString(4, "Jordan-Irbid-JUST-CairoAmmanBank");    //insert         Location
            psInsertBill.setString(5, dateFormat.format(cal.getTime()).toString()); //insert date and time
            psInsertBill.executeUpdate();

        System.out.println("\n ____ Inserted: Bill, XY3215616 50");



        //Show user table data
        rs = stmt.executeQuery
        ("SELECT * FROM user order by id");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "Id", "First Name", "Last Name", "User Name");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getString("lastName"),
                                   rs.getString("userName"));

            //Show administrator table data
        rs = stmt.executeQuery
        ("SELECT * FROM administrator order by id");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "Id", "First Name", "Last Name", "User Name");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getString("lastName"),
                                   rs.getString("userName"));

            //Show user bill data
        rs = stmt.executeQuery
        ("SELECT * FROM bill order by serialNumber");

        System.out.printf("\n\n%4s%20s%20s%20s\n",
                   "year Of Production", "Serial Number", "Value", "Location", "date and time");
        while (rs.next())
        System.out.printf("%4d%20s%20s%20s%20s\n",
                   rs.getInt("id"),
                   rs.getString("firstName"),
                                   rs.getInt("value"),
                                   rs.getString("location"),
                                   rs.getString("dateAndTimeCounted"));


        // Commit the transaction. Any changes will be persisted to the database now.
            conn.commit();
            System.out.println("Committed the transaction");

        try {
        // the shutdown=true attribute shuts down Derby
        DriverManager.getConnection("jdbc:derby:;shutdown=true");
        System.out.println("Derby shut down normally");
        }
        catch (SQLException se) {
        System.err.println("Derby did not shut down normally");
        }
    }
    catch (SQLException sqle) {
            printSQLException(sqle);
        } finally {
            // release all open resources to avoid unnecessary memory usage
            // Connection
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException sqle) {
                printSQLException(sqle);
            }
        }
    } // start()

    public static void printSQLException(SQLException e) {
    System.err.println("\n----- SQLException -----");
    System.err.println("  SQL State:  " + e.getSQLState());
    System.err.println("  Error Code: " + e.getErrorCode());
    System.err.println("  Message:    " + e.getMessage());
    // for stack traces, you may refer to derby.log
    e.printStackTrace(System.err);
    }
}

Answer 1

我也遇到了同样的问题。当我将表名从 user 更改为 User1 时，问题得到解决。 User 是一个保留关键字，因此它在 Derby 中会抛出错误，但在 MySql 工作台上它会正常工作，因为它使用 '' 作为转义序列。