Sorting in JDBC preparedStatement

Question

I want to sort an SQL statement based on user input, using a preparedStatement. I have a student table with three columns id, name and age, and the user can choose which column the results are sorted by.

try
   {
    System.out.println("Enter the column name to sort the data with");
    Scanner sc=new Scanner(System.in);
    String str=sc.next();
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","SYSTEM","SYSTEM");
    if(str.equals("id"))
    {
        String query="select * from STUDENT order by ?";
        PreparedStatement pst=con.prepareStatement(query);
        pst.setString(1,"id");
        pst.executeUpdate();
    }
    if(str.equals("name"))
    {
        String query="select * from STUDENT order by ?";
        PreparedStatement pst=con.prepareStatement(query);
        pst.setString(1,"name");
        pst.executeUpdate();
    }
    if(str.equals("age"))
    {
        String query="select * from STUDENT order by ?";
        PreparedStatement pst=con.prepareStatement(query);
        pst.setString(1,"age");
        pst.executeUpdate();
    }
   }
   catch(Exception e)
   {  
        e.printStackTrace();
   }

This is no more urgent for us than any other question, as someone mentioned on your previous question. It's not really our problem if you've left an assignment until the last minute. It would help if you said what was wrong with your current code - what error or behaviour you get. — Alex Poole, Jun 01 '14 at 17:40

score 3 · Answer 1 · answered Jun 01 '14 at 17:14

This is not how prepared statements work: you are trying to build dynamic SQL, whereas a prepared statement issues an SQL statement together with bound variables, which cannot be used for column or table names.

So you can do this:

String query="select * from STUDENT where some_col = ?";
PreparedStatement pst=con.prepareStatement(query);
pst.setString(1,"some_value");
pst.executeUpdate();

but not this:

String query="select * from ?";
PreparedStatement pst=con.prepareStatement(query);
pst.setString(1,"STUDENT");
pst.executeUpdate();

as you cannot use variable "placeholders" for tables or other database objects.

Sorting in JDBC preparedStatement

1 Answers1