I am developing a server that does all types of stuff with a database. The only real option of creating queries is by prepared statements. I need to be able to select, update, delete and insert to the database. What would be the best approach as far as readability and reusability with creating prepared statements? I have more than one table so their will be ay least 2 to 3 parameters if I only create four prepared statements, one for each type of action (select, update, delete and insert). Also where should I declare the prepared statements? Constructor or as field variables? Just trying to learn how the professionals would do it. Any references would be greatly appreciated.
It's not possible to create a universally reusable prepared statement for all select (insert/update/delete) queries. First of all, only literals (actual "values") inside SQL statement can be parametrized. Table and column names, as well as all other keywords, cannot be parametrized. For example, the following will not work:because table name is not a literal (a "value"). There are fundamental reasons why this cannot work, including query processing and security issues.
What does work, on the other hand, is
I'd say that typical approach is to create each prepared statement as needed for the task at hand. If I'm inserting into a table, I'll create a prepared statement. If I'm updating column A and B in that table, I'll create another prepared statement. If I'm updating columns A, B and C in the same table, that's going to be yet another prepared statement.
Prepared statements are typically cached by the JDBC connection or connection pool and/or by the database. I'd only care about reusability of the prepared statements if the commands were generated dynamically. Say that I have a table with a primary key and ten other columns, and a user interface which allows to update any subset of these ten columns. If I dynamically create a prepared statement updating just the subset of columns the user has edited, I can theoretically get over 1000 (precisely 1023, 2^10-1) different statements, and if there was a possibility that the users in real world might be using the application in a way which leads to creating many many prepared statements, it might create performance problems (depending on the database).
However, under "all types of stuff" I imagine something much more dynamic. If you're planning to generally execute SQL statements that the users enter to the system, you need to be much more careful, because it might lead to serious security issues.