通过用户输入键盘Java从mysql数据库中选择记录

Question

我的问题是，我想从数据库中检索最低范围和最高范围之间的标记。提示用户从键盘输入最低和最高的范围。在我的sql查询中，它没有识别我从键盘输入中指定的cpa1 & cpa2范围，我遇到了一个问题。请帮帮忙。

见下面的代码：

try{
    Scanner input = new Scanner(System.in);
    System.out.print("Kindly enter the lowest CPA threshold : ");
    cpa1 = input.nextFloat();

    System.out.println();

    System.out.println("Kindly enter highest CPA threshold : ");
    cpa2 = input.nextFloat();

     rs = stt.executeQuery("SELECT * FROM student WHERE CPA BETWEEN `cpa1` and `cpa2` order by CPA asc");
     while(rs.next()){
         String id = rs.getString("student_id");
         String name = rs.getString("student_name");
         String gender = rs.getString("gender");
         float cpa = rs.getFloat("CPA");
         Date enrol = rs.getDate("enrollment_date");

         try{
             FileWriter writer = new FileWriter("StudentRange.txt");

         }catch(Exception e){
             e.printStackTrace();
         }
     }
     input.close(); 
}catch(Exception e){
    e.printStackTrace();
}

Answer 1

这

rs = stt.executeQuery("SELECT * FROM student WHERE CPA BETWEEN `cpa1` and `cpa2` order by CPA asc");

不是如何使用参数构建SQL查询的。使用准备陈述

String query = "SELECT * FROM student WHERE CPA BETWEEN ? and ? order by CPA asc";
PreparedStatement ps = con.prepareStatement(query);
ps.setFloat(1, cpa1);
ps.setFloat(2, cpa2);
ps.execute(ps);

Answer 2

那又如何呢？

rs = stt.executeQuery(String.format("SELECT * FROM student WHERE CPA BETWEEN `%f` and `%f` order by CPA asc", cpa1, cpa2);