Update data to SQL Server with While java

So i’m trying to input data into my sql server with condition, i printout the value and nothing is wrong with the method, but the prepare statement only add one same data to the table, here is my code

public static void tunjanganKaryawan(){
    try {
        Staff  staff = new Staff();
        Statement stmt=getConn().createStatement();
        ResultSet rs=stmt.executeQuery("select * from list_karyawan where Status ='Tetap'");
        while(rs.next()){
            staff.setCuti(rs.getInt("Cuti") + rs.getInt("Tanpa_Kabar"));
            staff.setjmlhAbsensi(rs.getInt("Absensi"));
            int makan1 = (int) staff.gethitungTunjanganMakan();
            int Trans = (int) staff.gethitungTunjanganTrans();
            System.out.println(makan1+" "+Trans);

            String query = "update list_karyawan set Tunjangan_Makan = ? ,Tunjangan_Trans = ? where Status = 'Tetap'";
            ps =  getConn().prepareStatement(query);
            ps.setInt(1,makan1);
            ps.setInt(2,Trans);

            ps.executeUpdate();
        }
    }catch (Exception e){
        System.out.println(e);
    }

}

Here is the result when i print out the makan and trans

200000 400000
170000 340000
170000 340000
130000 260000
170000 340000

and here is the data in the table

which the prepared statement only exceute the same value….

Answer

Your sql query

update list_karyawan set Tunjangan_Makan = ? ,Tunjangan_Trans = ? where Status = 'Tetap'

will update all records where Status = 'Tetap' instead of a single record at a time. Moreover, select * is pulling more data from your database, i.e. using more resources than your desired use case. I would recommend selecting fields that are required instead eg

select ID,Cuti,Tanpa_Kabar,Absensi  from list_karyawan where Status ='Tetap'

In order to update separate records, using a unique identifier such as your ID column in the where condition in your sql statement will solve your issue. The recommended modifications have been included below:

public static void tunjanganKaryawan(){
    try {
        Staff  staff = new Staff();
        Statement stmt=getConn().createStatement();
        ResultSet rs=stmt.executeQuery("select ID,Cuti,Tanpa_Kabar,Absensi  from list_karyawan where Status ='Tetap'");
        while(rs.next()){
            staff.setCuti(rs.getInt("Cuti") + rs.getInt("Tanpa_Kabar"));
            staff.setjmlhAbsensi(rs.getInt("Absensi"));
            int makan1 = (int) staff.gethitungTunjanganMakan();
            int Trans = (int) staff.gethitungTunjanganTrans();
            System.out.println(makan1+" "+Trans);

            String query = "update list_karyawan set Tunjangan_Makan = ? ,Tunjangan_Trans = ? where ID=?";
            ps =  getConn().prepareStatement(query);
            ps.setInt(1,makan1);
            ps.setInt(2,Trans);
            ps.setInt(3,rs.getInt("ID")); //use id from previous query
            ps.executeUpdate();
        }
    }catch (Exception e){
        System.out.println(e);
    }

}