Prepared Statement in Java/SQL Server not returning any results-CodePudding

List<Guest> guestList = new ArrayList<>();
String query = "select * from Guests where ? like ?";
System.out.println("select * from Guests where "   property   " like '%"   value   "%'");
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, property);
preparedStatement.setString(2, "'%"   value   "%'");
ResultSet resultSet = preparedStatement.executeQuery();
guestList = getGuestListFromResultSet(resultSet);
return guestList;

As you can see above, I created a Prepared Statement, which is later populated with 2 values: property and value. Running the above query should give me some results in SQL Server. I also tried these variations for setting the second parameter(value):

preparedStatement.setString(2, "%"   value   "%");
preparedStatement.setString(2, value);

None of these seem to work. What does work is simply building the query from string concatenation:

PreparedStatement preparedStatement = connection.prepareStatement("select * from Guests where "   property   " like '"   value   "'");

However, I want to use a Prepared Statement.

CodePudding user response：

You can't use a variable as a column name. Instead, you can use dynamic SQL

String query = """
DECLARE @sql nvarchar(max) = '
select *
from Guests
where '   QUOTENAME(?)   ' like @value;
';

EXEC sp_executesql @sql,
  N'@value nvarchar(100)',
  @value = ?;
""";

Note the use of QUOTENAME to correctly escape the column name.

Note also the use of sp_executesql to pass the value all the way through.

I'm not sure about the JDBC driver, but ideally you should use proper named parameters, rather than ?