SQL PreparedStatement & autocommit

jdbc prepared-statement autocommit
AliM picture AliM · May 30, 2012 · Viewed 21.9k times · Source

If I create a preparedstatement using a JDBC connection which has "autocommit off", do I need to call commit() to make the transaction permanent or only the prepare call is enough? Note that I only want to prepare a statement and keep it for later execution.

Many thanks!

Answer

a_horse_with_no_name picture a_horse_with_no_name · May 30, 2012

The answer is: yes you have to call commit.

The "prepare" step does not do anything that is relevant for transaction handling on the server.

You have to call executeUpdate() (or execute()) to start a transaction (assuming the used SQL does in fact start a transaction)

Using a PreparedStatement without auto-commit requires the following steps:

  1. Prepare the statement
  2. Set the parameter values
  3. call executeUpdate() (or execute() depending on the type of statement)
  4. call Connection.commit()

To make things clearer: calling prepareStatement() does not require a commit().

Related questions

PreparedStatement IN clause alternatives?

What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list …

Read More
Get query from java.sql.PreparedStatement

In my code I am using java.sql.PreparedStatement. I then execute the setString() method to populate the wildcards of the prepared statement. Is there a way for me to retrieve (and print out) the final query before the executeQuery() …

Read More
PreparedStatement with list of parameters in a IN clause

How to set value for in clause in a preparedStatement in JDBC while executing a query. Example: connection.prepareStatement("Select * from test where field in (?)"); If this in-clause can hold multiple values how can I do it. Sometimes I know …

Read More