Should I use Prepared Statement for inserting one row?

Question

I just found that there are places in our code that use Prepared Statement even though we always deal with inserting one row to the table.

I'm wondering if using Prepared Statement when only inserting one row has some overhead that worth modifying this code to use Statement.

Answer 1

On the security side, Prepared Statements are used especially to prevent SQL Injection attacks. About efficiency, it very much depends on the nature of the statement you are dealing with. You may also find interesting this other answer:

Why PreparedStatement is preferable over Statement

Answer 2

When you use PreparedStement, not just your query execution is faster there are other advantages too.

• You queries execute fast as PreparedStatement results into the query being precompiled on the database and reused.
• PreparedStatement, your queries are dynamic. Meaning, you define the query only once, and reuse the same again with different parameters. String concatenation also achieves it but its crude way doing this. Quoting this link

The important thing to remember is to never construct SQL statements using string concatenation of unchecked input values. Creating of dynamic queries via the java.sql.Statement class leads to SQL Injection.

• When you use PreparedStatement you prevent the SQL injection attacks. In Prepared statement, you do not use string concatenation for adding the runtime parameters but instead set the parameter explicitly in the compiled query and the parameters passed are escaped automatically by JDBC Driver for PreparedStatement.

Answer 3

A PreparedStatement is prefrable to a simple Statement as it offes your some security against SQL injection.

In a PreparedStatement every parameter is checked for its type and automatically escaped. This means that inserting String with an ' is save with PreparedStatement whereas you have to escape special characters yourself when not using a PreparedStatement.

Also you cannot insert some String where a Number is expected.

Answer 4

If even your code inserts only one row it may be called many times in which case PreparedStatement is supposed to be faster. As for Statement it also needs to be compiled before execution so it's hardly be any faster