Where should ? be placed in a PreparedStatement?

Question

I am using PreparedStatement to select records from a table:

public static String getMemberInfo(String columnName, Integer memberId) {       
    String memberInfo = "";
    String sql = "SELECT ? FROM member WHERE member_id = ?";
    DatabaseConnector.setConn();

    try(Connection conn = DatabaseConnector.getConn();
        PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setString(1, columnName);
        ps.setInt(2, memberId);

        try(ResultSet rs = ps.executeQuery()) {
            if(rs.next()) {
                memberInfo = rs.getString(columnName);
            }
        }

    } catch(SQLException se) {
        se.printStackTrace();
    }

    return memberInfo;
}

When I use SELECT " + columnName + " FROM member WHERE member_id = ?, it works.

But when I use SELECT ? FROM member WHERE member_id = ?, it does not.

Where should ? be placed in prepared statements?

Related, http://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements — Masudul, Jun 08 '15 at 06:24

score 4 · Accepted Answer · answered Jun 08 '15 at 06:29

4

? is for input values (typically in the WHERE clause conditions).

? is not for selected columns.

answered Jun 08 '15 at 06:29

jarlh

42,561
8
45
63

Sachin Gupta · Answer 2 · 2015-06-08T06:34:07.103

2

Column name must be hard-coded, Only column values can be set using ?.

but you can set dynamic column name by doing something like this :

String sql = "SELECT "+ columnName +" FROM member WHERE member_id = ?";

edited Jun 08 '15 at 06:34

answered Jun 08 '15 at 06:29

Sachin Gupta

7,805
4
30
45

2

The restriction doesn't come from JDBC. It's the database that does not allow parameterised column names. Mostly because it cannot prepare a query plan without knowing the column and table names. – Andomar Jun 08 '15 at 06:32
Oh ok thanks for the info @Andomar I have removed that line from my answer. – Sachin Gupta Jun 08 '15 at 06:33

Where should ? be placed in a PreparedStatement?

2 Answers2