javaprogrammer javaprogrammer - 4 months ago 9
SQL Question

How to go through all rows in employeeID column in database and retrieve hourly pay rate one at a time?

I have the following sql table called employee_info with two important columns, "employeeID" and "HourlyRate". What I want to do is get the HourlyRate from each employeeID one at a time, do calculations on that and then store it in another database with the same primary key employeeID, and then repeat the whole process for the next employeeID and so on until all are completed.

enter image description here

So far I have tried this very basic incomplete code to try and retrieve the information, but I am unsure how to do the above, help is appreciated, thanks

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");

Answer

You need to iterate trough the result-set. See: 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
    }
}