Java程序查询数据库错误

Question

我正在制作一个程序，它使用 Java 作为前端来对数据进行一些 SQL 操作。我已经设置了大部分内容，并且能够连接到我的数据库，但是由于某种原因，当我尝试运行查询时，它返回一行“无法执行查询”，我无法弄清楚为什么它无法执行查询。

public class application {

   public static void main (String args[]) throws Exception,
                 IOException, SQLException {
      try {
          Class.forName ("oracle.jdbc.driver.OracleDriver");
      } catch (ClassNotFoundException e) {
          System.out.println ("Could not load the driver");
      }
      String user = readEntry("Enter userid: ");
      String pass = readEntry("Enter password: ");
      Connection conn = DriverManager.getConnection (
                  "jdbc:oracle:thin:@serveraddress",user,pass);

      boolean done = false;
      do {
         printMenu();
         System.out.print("Type in your option: ");
         System.out.flush();
         String ch = readLine();
         System.out.println();
         switch (ch.charAt(0)) {
             case '1': studentModules(conn);
                 break;
             //some other cases
             case '0': done = true;
                 break;
             default : System.out.println(" Not a valid option ");
         } //switch
     } while(!done);

  } // main

  private static void studentModules(Connection conn) throws SQLException, IOException {

  String sqlString = null;
  Statement stmt = conn.createStatement();

  sqlString = "select Student_id" +
            "from STUDENT;";

  ResultSet rset;
  ResultSet rset2;

  try {
     rset = stmt.executeQuery(sqlString);
  } catch (SQLException e) {
     System.out.println("Could not execute query");
     stmt.close();
     return;
  }

  String print;

  while (rset.next()) {

      print = rset.getString(1) + ": ";

      sqlString = "select Module_code" +
              "from EXAM" +
              "where Student_id = " + rset.getString(1) + ";";

      try {
         rset2 = stmt.executeQuery(sqlString);
      } catch (SQLException e) {
         System.out.println("Could not execute query");
         stmt.close();
         return;
      }

      while (rset2.next()) {
         print = print + rset.getString(1) + " ";
      }

      System.out.println(print);

   }

   stmt.close();

}

我知道这是一段很长的代码，但我不明白为什么会出错。我知道它在尝试查询“SELECT Student_id FROM STUDENT”时出错并被捕获，但该查询没有错误，我不明白为什么它会在那里抛出错误？

感谢您的帮助。

Answer 1

"select Student_id" +
        "from STUDENT;";

解析为select Student_idfrom STUDENT之类的内容，但不会计算。做

"select Student_id from STUDENT";

相反。

sqlString = "select Module_code" +
          "from EXAM" +
          "where Student_id = " + rset.getString(1) + ";";

应该是

sqlString = "select Module_code from EXAM " +
          " where Student_id = " + rset.getString(1);

同样。

不要编写最后的“;”顺便说一句 - 有些数据库(如 ORACLE)非常讨厌这一点。

哦，最终结果集中的循环变量您需要访问 rset2 而不是 rset，我猜:

while (rset2.next()) {
     print = print + rset2.getString(1) + " "; //CHECK
  }

附加

您可能还想考虑使用 PreparedStatement - 字符串连接将为 SQL 注入(inject)打开大门，而您可能希望阻止这种情况。