javaprogrammer javaprogrammer - 1 year ago 50
SQL Question

How to check which range a float variable is within, from database values

I have the following sql table "tax_info" with three columns:

enter image description here

I have a variable called "Salary" which is a floating point. How can I check which tax bracket Salary lies within? Please note there could be as many tax brackets as necessary, but currently there are only 3. So far I've started off with the following, but unsure how to query.

PreparedStatement ps4 = con.prepareStatement("SELECT FromSalary, ToSalary FROM payroll_system.tax_info");
ResultSet rt = ps4.executeQuery();

Answer Source

Java naming convention is for variables to start with lowercase letter, so you variable should be named salary, not Salary.

Float values only have about 7 digits of precision (6 to 9), so it is a very bad datatype to use for salary. Since your tax brackets are defined as 0-6000, 6001-20000, ..., a salary of 6000.42 will fall between brackets. This means that your salary value is likely an integer value, so you should use int, not float to store the value.

When using JDBC, you must remember to close the statements and result sets, otherwise you have a resource leak. Best way to do this is using try-with-resources.

Here is code to find the tax bracket for a given salary value.

// input: con, salary
int taxBracket = 0, fromSalary = 0, toSalary = 0;
String sql = "SELECT TaxBracket, FromSalary, ToSalary" +
              " FROM payroll_system.tax_info" +
             " WHERE ? BETWEEN FromSalary AND ToSalary";
try (PreparedStatement stmt = con.prepareStatement(sql)) {
    stmt.setInt(1, salary);
    try (ResultSet rs = stmt.executeQuery()) {
        if ( {
            taxBracket = rs.getInt("TaxBracket");
            fromSalary = rs.getInt("FromSalary");
            toSalary   = rs.getInt("ToSalary");