java - 如何遍历数据库中 employeeID 列中的所有行并一次检索一个小时工资率？

Question

我有以下名为 employee_info 的 sql 表，其中包含两个重要的列，“employeeID”和“HourlyRate”。我想要做的是一次从每个 employeeID 获取一个 HourlyRate，对其进行计算，然后将其存储在另一个具有相同主键 employeeID 的数据库中，然后对下一个 employeeID 重复整个过程，依此类推，直到所有完成。

到目前为止，我已经尝试了这个非常基本的不完整代码来尝试检索信息，但我不确定如何执行上述操作，感谢帮助，谢谢

        boolean st = false;
        PreparedStatement ps3 = con.prepareStatement("SELECT HourlyRate FROM payroll_system.employee_info");
        ResultSet rs = ps3.executeQuery(); 
        st = rs.next();
        float hourlyRate = rs.getFloat("HourlyRate");

测试逻辑:

                PreparedStatement ps3 = con.prepareStatement("SELECT HourlyRate FROM payroll_system.employee_info");
            ResultSet rs = ps3.executeQuery(); 
            while(rs.next()) {
                float hourlyRate = rs.getFloat("HourlyRate");
                String employee_id = rs.getString("employeeID"); 

                PreparedStatement ps4 = con.prepareStatement("UPDATE payroll_system.payslip SET basic = ? WHERE employeeID=?"); 
                ps4.setFloat(1, hourlyRate);
                ps4.setString(2, employee_id);
                ps4.executeUpdate(); 




            }

Answer 1

您需要遍历结果集。请参阅:https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

rs.first();
while( rs.next() ){
    float hourlyRate = rs.getFloat("HourlyRate");
    int employeeID   = rs.getInt("employeeID ");
    //Do your code to calculate and update other database....
    //You need to check for INSERT or UPDATE in the other database.
    //Make one select on the current tupel

    //e.g.
    PreparedStatement checkUpdateOrInsert = conOnOtherDb.prepareStatement("SELECT employeeID FROM otherSchema.otherTable");
    ResultSet rsCheckUpdateOrInsert = checkUpdateOrInsert .executeQuery();
    if( !rsCheckUpdateOrInsert.first() ){
        //On onther DB you must insert
    }//untested, (bool) first should return false on no row?
    else{
        //On onther DB you must update
    }
}

谢谢大家的帮助!