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

javaProgrammer 发表于 Dev

Java程序

我有名为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(); 




            }

抬头

您需要遍历结果集。参见：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
    }
}