The database update method that I'm working on in my java program, doesn't update the database. Only if I run the read method inside the same class, it will show the updated version of the database but it doesn't actually update the database. What I do is I run the unit_elimination(2) method inside my main class and then it doesn't update the database ( it should turn the status column of the row with ID=1 inside main_table to 2 but it stays the same in the database ) . Then I use the showname method to read the table and it shows the updated number but the database isn't actually getting updated.

package com.company;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.*;

public class Stdn implements AutoCloseable{


    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public Stdn() throws SQLException {
        connection = DriverManager
                .getConnection("jdbc:mysql://localhost:1522/juni_project" , "root", "password");
        connection.setAutoCommit(false);
    }

    public void showName(String A , String B) throws SQLException{
        preparedStatement = connection.prepareStatement("select "  A   " from "   B   ";" );
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
        }
    }
    public void update(String A , String B , int C , int D)  {
        try{
            PreparedStatement preparedStatement = connection.prepareStatement("UPDATE "  A   " SET "   B   " =? WHERE ID = ? ;");
            preparedStatement.setInt(1,C);
            preparedStatement.setInt(2, D);
            preparedStatement.executeUpdate();
        }
        catch (SQLException e){
            System.out.println( " Could not update data to the database "   e.getMessage());
        }}
        public void unit_elimination(int X) throws SQLException {
        update("main_table" ,"status", X , 1 );
        }
    @Override
    public void close() throws Exception {
        preparedStatement.close();
        connection.close();
    }}

Can you please help me find where the problem is I've been stuck on this for a few days and have little time to spare.

CodePudding user response：

You disable auto-commit after creating the connection. This means you have to explicitly commit (using connection.commit()) before changes are visible to other transactions. And, IIRC, MySQL or MySQL Connector/J will rollback the transaction if it is still active when you close the connection.

In other words, your changes are 1) never visible to other transactions and 2) once you close the connections, your changes are gone.

TL;DR: Add a connection.commit() where your unit-of-work is complete and you want the changes to become permanent. Or consider if for your program using auto-commit is good enough, and remove the connection.setAutoCommit(false); from your code.