How to fix "java.sql.SQLException: Can't call commit when autocommit=true"

Question

I am working on java and mysql and I couldn't fix the errors. I am facing these errors when I insert new farmer to the table...

java.sql.SQLException: Can't call commit when autocommit=true

Errors are below

java.sql.SQLException: Can't call commit when autocommit=true

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:869)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:865)
at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1537)
at client.Main.addonething(Main.java:784) at client.Main.parse_commands(Main.java:123) at client.Main.main_loop(Main.java:202) at client.Main.main(Main.java:239) com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4560) at client.Main.addonething(Main.java:795) at client.Main.parse_commands(Main.java:123) at client.Main.main_loop(Main.java:202) at client.Main.main(Main.java:239)

This is the database connection code

 public static void addonething(String query, DBConnection dbConnection){
        Statement myStmt;
        try {
            myStmt =  dbConnection.getConn().createStatement();
            int swichA = 0;
            int swichB = 0;
            myStmt.execute(query);

    System.out.println("To commit the data enter 1");
    Scanner in = new Scanner(System.in);
    swichB = in.nextInt();
    if(swichA != -1 && swichB == 1){
        System.out.println();
        dbConnection.getConn().commit();
    }
    else{
        System.out.println("it came hear3");
        dbConnection.getConn().rollback();
    }

} catch (SQLException e) {
    System.out.println("it came hear4");
    e.printStackTrace();
    try {
        dbConnection.getConn().rollback();
    } catch (SQLException e1) {
        System.out.println("it came hear5");
        e1.printStackTrace();
    }
}}

Below is the insert method

public static String frQuery(String second_part){
    String data = get_data_from_commands(second_part);
    String[] tokens = data.split(",");
    String queryf = String.format("insert into farmers" + " 
            (Name, lastname, Address, zipcode, city, phones, mails)" + "values 
      ('%s','%s','%s','%s','%s','%s','%s')",tokens[0],tokens[1],tokens[2], 
       tokens[3],tokens[4],tokens[5], tokens[6]);

    return queryf;
}

Turn AutoCommit off. See docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html — PM 77-1
– PM 77-1, Commented Jun 7, 2019 at 17:06

TechFree · Accepted Answer · 2019-06-07 17:13:46Z

3

You can disable auto commit by using this code:

 //Note : This will implicitly commit the active transaction and create a new one
 connection.setAutoCommit(false);

Now the set of statements ( transaction ) will be committed on :

connection.commit();

answered Jun 7, 2019 at 17:13

TechFree

3,0141 gold badge24 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhteva · Accepted Answer · 2021-08-27 14:56:29Z

0

Basically when we write query and execute them defaultly they will commit it resist it we have to specify don't commit and commit only when I specify.

that is we can do it by like

Connection con = DriverManager.getConnection(url,user,password);
con.setAutoCommit(false);

This will set the autocommit not to commit automatically.

edited Aug 27, 2021 at 14:56

Muhteva

2,8402 gold badges12 silver badges23 bronze badges

answered Aug 27, 2021 at 12:05

chundi sai krishna

34 bronze badges

Comments

Yusuf BESTAS · Accepted Answer · 2023-07-20 09:49:22Z

Auto-commit mode means that when a statement is completed, the method commit is called on that statement automatically. When auto-commit is enabled, any changes made to the database will be committed "immediately" after the statement is executed.

In case of Transaction on a method, it will override the auto-commit setting for that method(if auto-commit is on default mode). This means Transaction will set auto-commit false and queries executed within the method will be part of the same transaction. Basically, the framework will take care to configure the necessary auto-commit value.

But When you explicitly set auto-commit to true, you are telling Hibernate that you want to control when transactions are committed. And in this case, if needs to use the @Transacional method then you have to set auto-commit=false manually before using the transaction otherwise cause an exception Caused by: java.sql.SQLException: Can't call commit when autocommit=true

Collectives™ on Stack Overflow

How to fix "java.sql.SQLException: Can't call commit when autocommit=true"

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related