Select record from mysql database via user input keyboard Java

Question

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.

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

What class is stt? I highly doubt you can specify the cpa1 and cpa2 substitution values like you are doing with executeQuery. — pathfinderelite
– pathfinderelite, Commented May 2, 2015 at 17:32

score 2 · Accepted Answer · 2015-05-02 17:48:35Z

2

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:

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

edited May 2, 2015 at 17:48

answered May 2, 2015 at 17:30

user1907906

Sign up to request clarification or add additional context in comments.

4 Comments

yetin Over a year ago

how can I sue prepared statement with insert?

yetin Over a year ago

At conn.execute(ps) .. It is giving me an error as "The method execute(PreparedStatement) is undefined for the type Connection"

user1907906 Over a year ago

Sorry, that's execute on the PreparedStatement. But that is easy to find out if you read the JDBC tutorial and the documentation. SO is not a site where we solve your problem for you and you just sit back and wait for the solution. Do some work yourself!

yetin Over a year ago

Thanks man ! I edited your answer. The problem is resolved :)

chris · Accepted Answer · 2015-05-02 17:30:53Z

-1

What about that:

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

answered May 2, 2015 at 17:30

chris

1,8153 gold badges19 silver badges30 bronze badges

10 Comments

user1907906 Over a year ago

No! This opens a huge hole for SQL injection attacks.

pathfinderelite Over a year ago

@Tichodroma The values are of type float, not String. There is no security issue here.

user1907906 Over a year ago

@pathfinderelite Maybe in this special case. But it is a very bad habit to build SQL like this. Don't do it, even if you think you have everything under control.

chris Over a year ago

I got no mysql running but i assume it has to be something like "SELECT * FROM student WHERE CPA BETWEEN %f and %f order by CPA asc" (without the quotes around the numbers)

user1907906 Over a year ago

@chris Please don't do this. It is very bad advice.

|

Collectives™ on Stack Overflow

Select record from mysql database via user input keyboard Java

2 Answers 2

4 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related