Concatenation in prepared statement

Question

I have written universal DAO layer for mySQL (it can save\get any class object that extends Entity to\from table using Reflection and ResultSetMetaData). My implementation of it has little concatenation in sql query. Is it waste all advantages of prepared statement or I just loose little perfomance to concat String and nothing more?

For example piece of code for entity deletion:

PreparedStatement prepStatement = con.prepareStatement("DELETE FROM "
                                      + tableName + " WHERE id = ?");
prepStatement.setLong(1, id);

Is it waste all advantages of prepared statement? No its not. Read [When we use PreparedStatement instead of Statement?](http://stackoverflow.com/questions/2099425/when-we-use-preparedstatement-instead-of-statement) and Read [when to use StringBuilder in java](http://stackoverflow.com/questions/4645020/when-to-use-stringbuilder-in-java) — Braj, Jul 07 '14 at 20:34
I know about StringBuilder and about it advantages over concatenation of strings. In case of adding only table name I think StringBuilder is redundant. — Kirill Reznikov, Jul 07 '14 at 20:42
The answer of your question is in first link. Second one is only suggestion. — Braj, Jul 07 '14 at 20:44
Prepared statement preparation and one time execution is normally slower than a simple statement. But it's definitely the way to go. You should consider to prepare one statement for each operation on each entity class at startup and reuse them as often as needed. In very long running applications I would recommend to re-prepare the statements from time to time. — blafasel, Jul 07 '14 at 20:51
Your database will reuse the query for each tablename. So, you won't profit from reusing only 1 query at database level, but as many as tables you use. However, `DELETE FROM TABLE1 WHERE ID = 1` and `DELETE FROM TABLE1 WHERE ID = 2` will be handled as the same query with different parameters, there you will still have the benefit. `DELETE FROM TABLE1 WHERE ID = 1` and `DELETE FROM TABLE2 WHERE ID = 1` will however be treated as two different queries. — Martin, Jul 08 '14 at 14:32

score 3 · Answer 1 · answered Jul 07 '14 at 20:46

The main benefit of PreparedStatements is when you have code that behaves in a similar way to this pseudocode:

PreparedStatement ps = con.prepareStatement("blabalblabla");
for (int i = 0; i < a gazillion times; i++) {
    // Set parameters into ps
    ...
    // execute already prepared statement
    ps.execute();
}

That is, you prepare once and execute many times, each time with different sets of parameters. This allows the driver / database to perform potentially costly operations (such as parsing) only once and then reuse that work. Apart from that, using PreparedStatement may be interpreted as a hint to the driver that it should cache that statement resources or something because it is going to be used later, but I don't think it will have as much impact as the "prepare once execute many" approach.

Your use of concatenation to add the table names won't disable the optimizations that your JDBC driver does (if any). But anyway, if your code does more of "prepare once execute once" than it does "prepare once execute many", then PreparedStatement might only have a minor performance benefit.

Note that all of the above is highly database / driver dependent. For example, Oracle performs a lot better if you use PreparedStatements in the way I have described as "prepare once execute many". And as a last advice, don't forget that you should avoid concatenating parameter values unless you have no other option, for both performance AND SECURITY reasons.

So, my suggestions was right. JDBC driver after first call of the method with the new table name will cache new prepared statement and all next calls with this entity will use old prepared statement. — Kirill Reznikov, Jul 07 '14 at 21:03
No, you need to check in the documentation / source code what your JDBC driver & database combination really does regarding `PreparedStatement`s. What I have described is *what a reasonably smart JDBC driver / database combination might do*. — gpeche, Jul 07 '14 at 21:22

score 1 · Answer 2 · answered Jul 08 '14 at 14:03

It's recommended to use the prepared statements for the DB performance improvement. In theory the DB drivers cache the prepared statements (you might require to enable the caching on connection object). I would assume that concatenation is not as critical. Keep in mind that tableName might be case sensitive in the driver cache.

I would review your DB driver features, and you should be able to debug the driver, and monitor the database to see how your statements are handled/executed.

score 0 · Answer 3 · answered Jul 07 '14 at 20:56

The variable tableName in your example may introduce vulnerability for SQL injection but it may be alternative ways to protect against this. For instance,

Map<String,String> myTables; // key and value are the same.
tableName = myTables.get(tableName); // safe known value or null.

Generally, it is better just to use prepared statements consistently to stay away out of trouble. However sometimes building query (most often where query) "on the fly" can save many lines otherwise close to duplicate code so it is difficult to say "never do this".

Yes, I care about tableName with "final" keyword :-) – Kirill Reznikov Jul 07 '14 at 21:10 — Kirill Reznikov, Jul 07 '14 at 21:10

Concatenation in prepared statement

3 Answers3