Java SQL PreparedStatement is not working

Question

I have written this code with Statement and it is working well:

 protected ResultSet tabQuery(String query) throws SQLException {

String tabQuery = "SELECT * FROM " + query;

Statement statement = connection.createStatement();

return statement.executeQuery(tabQuery);

}

But when I trying to convert it with PreparedStatement but it is not working with SQL syntax error.

protected ResultSet tabQuery(String query) throws SQLException {

String tabQuery = "SELECT * FROM ?";

PreparedStatement statement = connection.prepareStatement(tabQuery);

statement.setString(1, query);

System.out.println(statement);

return statement.executeQuery();

}

You cannot pass table names as bind variables. Please look at the answer for following question to understand more: https://stackoverflow.com/questions/9723931/passing-table-and-column-name-dynamically-using-bind-variables — Shivam Puri, Dec 09 '19 at 12:58

dan1st · Accepted Answer · 2019-12-09T14:03:36.920

0

You cannot use the ? operator on tables.

You can do something like "SELECT ? FROM mytable;" but it does not work the other way.

I recommand using stored procedures, not using prepared statements, changing your db structure or create prepared statements for every query and cache them:

Map<String,PreparedStatement> cache=new HashMap<>();

and

PreparedStatement stmt;
if(cache.contains(query)){
    stmt= cache.get(query)
}else{
     stmt=new PreparedStatement("SELECT * FROM `"+query.replace("`","\\`")+"`");
    cache.put(query,stmt);
}
return stmt.executeQuery();

edited Dec 09 '19 at 14:03

answered Dec 09 '19 at 12:47

dan1st

12,568
8
34
67

1

Not only should you always use prepared statements, creating your own prepared statement caching is most likely completely useless, as the drivers and servers tend to handle those a lot more efficiently themselves. Consult your database and driver documentation for prepared statement caching. Main problem in the question is really about design. You just don't create a method that returns **everything** from any table you pass as a parameter... – Kayaman Dec 09 '19 at 13:02

score 0 · Answer 2 · edited Dec 09 '19 at 21:20

0

Giving table names as variables for SQL queries is a bad practice.

It's better to write different queries for different tables.

getCustomers for the Customers table
getAccounts for Accounts table

etc.

edited Dec 09 '19 at 21:20

marc_s

732,580
175
1,330
1,459

answered Dec 09 '19 at 13:08

Pramodya Mendis

686
8
24

Java SQL PreparedStatement is not working

2 Answers2