javaprogrammer javaprogrammer - 4 months ago 11
SQL Question

How to use select data from a table, make calculations and then updating new data into another table with same primary key as first?

I am using java servlet to do the following:

I have table called Table1 with two columns, "EmployeeID(Primary key)" and "Gross salary"

I have another table called Table2 with two columns, "EmployeeID(Primary key)" and "Net salary"

There are many employee IDs currently in the database.

How can I retrieve the Gross salary from Table1 for all employees, do calculations to calculate the net salary, and then insert the new value (after calculations) into Net salary in Table2, in the corresponding row for each employee?

This is what I have so far, may not even be on the right track, help is appreciated, thanks.

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", "");
PreparedStatement ps = con.prepareStatement("SELECT Gross salary from payroll_system.Table1");
ps.executeUpdate();

Answer

First of all you need to execute the query, not an update, then save your data in a HashMap, finally perform the calculations and insert the results:

    ResultSet rs = ps.executeQuery(selectSQL );
    Map<Integer, Float> grossSalaries = new HashMap<>();
    Map<Integer, Float> netSalaries = new HashMap<>();
    while (rs.next()) {
        grossSalaries.put(rs.getInt(1), rs.getFloat(2));    
    }
    //Do the calculations for each one of the elements in the map
    //Save them into the netSalaries Map.

    //Then insert the rows in the Table2
    for(Integer key : netSalaries.keySet){
         String insertSt = "insert into Table2 values (?,?)";
         PreparedStatement preparedStatement = dbConnection.prepareStatement(insertSt);
         preparedStatement.setInt(1, key);
         preparedStatement.setFloat(2, netSalaries.get(key));
    }