java - 无法使用java从sql数据库读取数据

Question

我正在尝试从我的 sql 数据库中的 ORDER 表中读取数据，但每次尝试时都会出现错误:

run-single:

数据库连接

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at line 1, column 15.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at glassfish.Neasdasda.initializeDB1(Neasdasda.java:1495)
    at glassfish.Neasdasda.order(Neasdasda.java:1463)
    at glassfish.Neasdasda$3.handle(Neasdasda.java:103)
    at glassfish.Neasdasda$3.handle(Neasdasda.java:99)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Node.fireEvent(Node.java:8413)
    at javafx.scene.control.Button.fire(Button.java:185)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
    at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
    at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:381)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(GlassViewEventHandler.java:417)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:416)
    at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
    at com.sun.glass.ui.View.notifyMouse(View.java:937)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "ORDER" at line 1, column 15.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
    ... 52 more

我连接到数据库没有问题，我使用的查询语句也没有问题，因为我已经使用相同的查询语句从不同的表中读取。

    public void order(Stage currentScreen2){


        GridPane pane2 = new GridPane();
        Button btShowDetails = new Button("Show Details");
        pane2.add(btShowDetails, 1, 4);
        pane2.setAlignment(Pos.CENTER);
        pane2.setPadding(new Insets(40.5,42.5,43.5,40.5));
        pane2.setHgap(15.5);
        pane2.setVgap(20.5);


            Scene scene5 = new Scene(pane2, 1200, 400);




        currentScreen2.setTitle("Employees");
        currentScreen2.setScene(scene5);
        currentScreen2.show();
        initializeDB();


  //@Override // Override the start method in the Application class

        initializeDB2();



    hBox1.getChildren().addAll(new Label("ID"), tfid, btShowDetails);


    vBox1.getChildren().addAll(hBox1, lblordid, lblcustid, lblprice, lbldescrip, lblpay);

    tfid.setPrefColumnCount(6);
    //Set event listener 
    btShowDetails.setOnAction(e -> showDetail());

    // Create a scene and place it in the stage

  }

  private void initializeDB2() {
    try {
          // Establish a connection
      Connection connection = DriverManager.getConnection
        //Test1 is the name of the database you created
        ("jdbc:derby://localhost:1527/SandwichDB");
      System.out.println("Database connected");
        //Next we want to write the SQL statement we want to run. Note that the fields such as firstName and lastName are named the same as in the Table. The Question mark is the input we will later put in
      String queryString = "SELECT * FROM ORDER WHERE ORDERID = ?";

      // Create a statement
//Set the prepared Statement to run the queryString we previously set up
      preparedStatementss = connection.prepareStatement(queryString);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  private void showDetail() {
    //This takes the number entered in the text field and puts it into a string called id
    String id = tfid.getText();
    try {
    //Next we want to replace the ? in the previous query with the input 
    //The 1 is notifying we want to replace the first ?
      preparedStatementss.setString(1, id);
      ResultSet rset = preparedStatementss.executeQuery();
    //Now it goes through each field and assigns them to a variable 
      if (rset.next()) {
        String ordered = rset.getString(1);
        String custard = rset.getString(2);
        String pricerd = rset.getString(3);
        String descripered = rset.getString(4);
        String payered = rset.getString(5);

        // Display result in a label
        lblordid.setText("Order ID: " + ordered);
        lblcustid.setText("Customer ID: " + custard);
        lblprice.setText("Price: " + pricerd);
        lbldescrip.setText("Description: " + descripered);
        lblpay.setText("Payment Type: " + payered);
      } else {
        lblordid.setText("Not found");
      }
    }
    catch (Exception ex) {
            ex.printStackTrace();
                }




}       

这是我用来尝试从数据库中读取的代码。请记住，它是从除此表之外的所有其他表中读取的，我们将不胜感激任何帮助。

Answer 1

ORDER 是关键字的一部分(例如 ORDER BY)，您需要引用表名。变化

String queryString = "SELECT * FROM ORDER WHERE ORDERID = ?";

到

String queryString = "SELECT * FROM `ORDER` WHERE ORDERID = ?";