Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 159 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 471 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
do we have to say connection.commit explicitly after prepared statement execution

Hi i am inserting a record using database connection. I am obtaining connection from datasource. After inserting a record using preparedStatement.executeUpdate() do i have to explicitly write connection.commit() ?
Best Answer
-
ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason? is commit mandatory for prepared statement?
I already answered that question and provided a link to the documentation that discusses the ENTIRE TOPIC of commit.
RTFM - it explains ALL of the places that the autocommit property can be set and what the default value is.
If you get a rollback then it is because autocommit is set to false and you are NOT issuing a COMMIT.
That is the reason.
The code that creates the 'datasource' and the connections might be setting autocommit to FALSE.
Answers
-
Yes - if you have autocommit set to FALSE.
No - if you have autocommit set to TRUE.
See the JDBC Developer Guide
http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxtips.htm#sthref980
Disabling Auto-Commit Mode
Auto-commit mode indicates to the database whether to issue an automatic
COMMIT
operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables. By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with thesetAutoCommit
method of the connection object, eitherjava.sql.Conection
ororacle.jdbc.OracleConnection
. In auto-commit mode, theCOMMIT
operation occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning aResultSet
object, the statement completes when the last row of the Result Set has been retrieved or when the Result Set has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, theCOMMIT
occurs when all results and output parameter values have been retrieved. If you disable auto-commit mode with asetAutoCommit(false)
call, then you must manually commit or roll back groups of operations using thecommit
orrollback
method of the connection object. -
-
ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason? is commit mandatory for prepared statement?
-
Hello murray,
The Oracle JDBC driver, by default enables "AUTO COMMIT" on a connection. This might create problems, especially when calling a database store procedure in oracle which leverages global temporary tables. Having set the AUTO COMMIT by default, the data in temporary tables will be deleted after any data manipulation statement(INSERT, DELETE, UPDATE). To avoid this kind of scenario its better to disable AUTO COMMIT the JDBC connection explicitly.
-
My database is not oracle. my database is mysql. I have replied explaining my problem in response to "rp0428". Plase read that once.
-
Its a preferred approach to disable auto commit at first and making it auto-commit after the transaction is successful.
-
ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason? is commit mandatory for prepared statement?
I already answered that question and provided a link to the documentation that discusses the ENTIRE TOPIC of commit.
RTFM - it explains ALL of the places that the autocommit property can be set and what the default value is.
If you get a rollback then it is because autocommit is set to false and you are NOT issuing a COMMIT.
That is the reason.
The code that creates the 'datasource' and the connections might be setting autocommit to FALSE.