27

I'm working with JDBC to connect to Oracle. I tested connection.setAutoCommit(false) vs connection.setAutoCommit(true) and the results were as expected.

While by default connection is supposed to work as if autoCommit(true) [correct me if I'm wrong], but none of the records are being inserted till connection.commit() was called. Any advice regarding default behaviour?

String insert = "INSERT INTO MONITOR (number, name,value) VALUES (?,?,?)";

conn = connection; //connection  details avoided
preparedStmtInsert = conn.prepareStatement(insert);
preparedStmtInsert.execute();

conn.commit();
informatik01
  • 16,038
  • 10
  • 74
  • 104
stackex
  • 299
  • 1
  • 3
  • 7
  • 1
    Can you show the code you actually used where it didn't work. Autocommit only occurs after the statement has been executed. – Ben Jun 13 '12 at 19:25
  • Are you doing anything in the `connection details avoided` section that does anything to affect the autocommit settings? – Justin Cave Jun 13 '12 at 19:51
  • You didn't post the most interesting part - the code that creates a connection ;) – npe Jun 13 '12 at 19:52
  • No. It just has url,passwords details thats it.(other database setting are not open to me) – stackex Jun 13 '12 at 19:53

1 Answers1

37

From Oracle JDBC documentation:

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)

The other thing is - you ommitted connection creation details, so I'm just guessing - if you are using some frameworks, or acquiring a connection from a datasource or connection pool, the autocommit may be turned off by those frameworks/pools/datasources - the solution is to never trust in default settings ;-)

npe
  • 15,395
  • 1
  • 56
  • 55
  • so should i take it as default may not be same as autocommit true in all cases. [something like default vs public in java]? – stackex Jun 13 '12 at 20:11
  • Defaults should be the same (autocommit=true) because that is what JDBC spec says. However, JDBC spec does not apply to frameworks, connection pools and datasources. For example, when defining a datasource in JBoss you can explicitely set autocommit to false in the datasource XML file, and the connections you will obtain from a pool will have it turned off by default. – npe Jun 13 '12 at 20:15