JDBC PreparedStatement strange behaviour

Question

I have the following simple code to connect to and query a DB with a prepared statement (it's only for learning purposes).

import java.sql.*;

public class Prepared {

  public static void main(String[] args) throws SQLException,Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb","myuser","luca01123581321");
    String query="SELECT id,? FROM person WHERE id>=?";
    PreparedStatement prep=conn.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
    prep.setString(1,"name");
    prep.setInt(2,2);
    prep.executeQuery();
    ResultSet res=prep.getResultSet();
    ResultSetMetaData rsmd=res.getMetaData();
    System.out.println("number of columns: "+rsmd.getColumnCount());
    while (res.next()) {
      int id=res.getInt(1);
      String name=res.getString(2);
      System.out.println(name);
    }
  }

}

When I execute the query and try to print the results I get:

number of columns:2
name
name
name
name

in other words I would like to retrieve the values in the second column (which is "name") that is of SQL type VARCHAR. but instead of getting the values I get "name" for all records...how's that?

score 4 · Answer 1 · answered Oct 21 '15 at 12:00

You can't parameterize the name of a result column. Say your id is 2 then you are literally executing this statement:

SELECT id,'name' FROM person WHERE id>=2

This would work:

String column = "name";
...
String query="SELECT id," + column+ " FROM person WHERE id>=?";
...
prep.setInt(1,2);  // only one parameter

JDBC PreparedStatement strange behaviour

1 Answers1