JDBC PreparedStatement Seems to Ignore Placeholder

Given the following code for a PreparedStatement, I use a placeholder (?) to be filled with a String:

String sql = "SELECT SUM(Premium), SUM(AgentComm)\n" +
                "FROM \"?_KLCommissions\" \n" +
                "WHERE AgentCode = \"?\";";

    PreparedStatement preparedStatement = conn.prepareStatement(sql);
    preparedStatement.setString(1, year);
    preparedStatement.setString(2, agent.getAgentNumber());

    ResultSet resultSet = preparedStatement.executeQuery();

When executing the query, however, I get a SQL error: no such table: ?_KLCommissions.

Why is the ? placeholder not being replaced when processing the query?

Jon Skeet
people
quotationmark

You've got it within double-quotes, which means it isn't a placeholder - it's just part of the value.

Beyond that, most SQL databases don't let you use parameters for things like table names and column names - only values can be parameterised. So you may well need to build the table name part directly, but then use

WHERE AgentCode = ?

for the agent code - so you'll only have a single parameter to set. So something like:

String tableName = year + "_KLCommissions";
// TODO: Put validation here unless you already have *complete confidence*
// that the value of "year" can't be messed around with.
String sql = "SELECT SUM(Premium), SUM(AgentComm) " +
             "FROM " + tableName + " " +
             "WHERE AgentCode = ?";

PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, agent.getAgentNumber());
ResultSet resultSet = preparedStatement.executeQuery();

Note the TODO there - you really don't want users to be able to provide an arbitrary value for year, as then they could put arbitrary SQL in your query.

people

See more on this question at Stackoverflow