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

My question is that I want to retrieve marks between a lowest and highest range from a database. The user is prompted to enter the lowest and highest range from the keyboard. I have a problem in my sql query where it is not recognising the cpa1 & cpa2 range that I have specified from the keyboard input. Please help.

我的问题是我想从数据库中检索最低和最高范围之间的标记。提示用户从键盘输入最低和最高范围。我在我的SQL查询中遇到问题,它无法识别我从键盘输入中指定的cpa1和cpa2范围。请帮忙。

See my codes below:

请参阅下面的代码:

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();
}

2 个解决方案

#1

This

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

is not how you build a SQL query with parameters. Use a prepared statement:

不是如何使用参数构建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);

#2

What about that:

那个怎么样:

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

#1