java - 使用Java(Eclipse)时出现SQL语法错误

我正在使用java eclipse以及mySQL工作台来相互连接。现在，我正在使用Java编写一些查询的代码，但是在查询问题中出现了一个特定的问题，该错误是我不熟悉的，并且在进行了一些可以正常工作的sql查询后，我第一次遇到了问题。任何帮助表示赞赏。我将列出代码，然后继续执行错误。

public static void stopTimes() throws SQLException
    {
    stmt = conn.createStatement();
    System.out.println("Enter Route Number: ");
    String route = scan.nextLine();
    System.out.println("Direction?");
    String dir = scan.nextLine();
    System.out.println("Enter the first stop number:");
    String stop1 = scan.nextLine();
    System.out.println("Enter the second stop number:");
String stop2 = scan.nextLine();
String sql;

  sql = "SELECT DISTINCT bus_stops.stopID, street1, street2 FROM bus_stops INNER JOIN scheduled_stops ON bus_stops.stopID = scheduled_stops.stopID"+
              "WHERE scheduled_stops.routeNumber = '"+route+"' "+
              "AND scheduled_stops.direction = '"+dir+"' "+
              "AND scheduled_stops.stopTime > (SELECT stopTime FROM scheduled_stops WHERE scheduled_stops.stopID = '"+stop1+"' ORDER BY scheduled_stops.stopTime asc limit 1)"+
              "AND scheduled_stops.stopTime < (SELECT stopTime FROM scheduled_stops WHERE scheduled_stops.stopID = '"+stop2+"' ORDER BY scheduled_stops.stopTime asc limit 1)";


      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         String stopID  = rs.getString("stopID");
         String street1 = rs.getString("street1");
         String street2 = rs.getString("street2");
         String stopTime = rs.getString("stopTime");

         //Display values
         System.out.print("Stop ID: " + stopID);
         System.out.print(", Name: " + street1 + "/" +street2);
         System.out.println(", Time: " + stopTime);
      }

}

java.sql.SQLException: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near'
scheduled_stops.routeNumber = '051' AND scheduled_stops.direction = 'Northbound''
at line 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2949)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:959)
at TransitManager.stopsOnRoute(TransitManager.java:460)
at TransitManager.main(TransitManager.java:104)

最佳答案

在生成查询时，请记住一件事，首先要在sql shell或工具中运行它，然后以适当的间距和字符串操作在代码中实现。

 sql = "SELECT DISTINCT bus_stops.stopID, street1, street2 FROM bus_stops INNER JOIN scheduled_stops ON bus_stops.stopID = scheduled_stops.stopID"+
                  " WHERE scheduled_stops.routeNumber = '"+route+"' "+
                  " AND scheduled_stops.direction = '"+dir+"' "+
                  " AND scheduled_stops.stopTime > (SELECT stopTime FROM scheduled_stops WHERE scheduled_stops.stopID = '"+stop1+"' ORDER BY scheduled_stops.stopTime asc limit 1)"+
                  " AND scheduled_stops.stopTime < (SELECT stopTime FROM scheduled_stops WHERE scheduled_stops.stopID = '"+stop2+"' ORDER BY scheduled_stops.stopTime asc limit 1)";