Why can't I get results from my Inner Join JDBC Query?-CodePudding

This JDBC query does not return anything despite it working on my SQLite Database Browser, no matter what I tried. The snippet is pretty self-explanatory of the results I'm looking for.

    public void getCountryIdLocationIdDepartmentIdParEmploye(Employe employe) {
        String query = "SELECT countries.country_id AS idc, locations.location_id AS idl, departments.department_id AS idd 
FROM countries 
INNER JOIN locations ON countries.country_id = locations.country_id 
INNER JOIN departments ON locations.location_id = departments.location_id 
INNER JOIN employees ON departments.department_id = employees.department_id 
AND employees.employee_id = ?";

        try {
            PreparedStatement ps = maConnexion.prepareStatement(query);
            ResultSet rs = ps.executeQuery();

            ps.setInt(1, employe.getId());
            setCountry_id(rs.getString("idc"));
            setLocation_id(rs.getInt("idl"));
            setDepartment_id(rs.getInt("idd"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

I've tried to replace setCountry_id(rs.getString("idc")); with setCountry_id(rs.getString(1)); etc. already but no good, my attributes stay unchanged. Regards,

PS: country_id is indeed a string

CodePudding user response：

In a compliant JDBC driver this should throw a SQLException because you set the parameter after executing the query (which means it should not be possible to execute the statement). It sounds like the SQLite JDBC driver has a bug in that regard.

You need to set the parameter before executing:

try (PreparedStatement ps = maConnexion.prepareStatement(query)) {
    ps.setInt(1, employe.getId());

    try (ResultSet rs = ps.executeQuery()) {
        setCountry_id(rs.getString("idc"));
        setLocation_id(rs.getInt("idl"));
        setDepartment_id(rs.getInt("idd"));
    }
} catch (SQLException throwables) {
    throwables.printStackTrace();
}

Also observe the use of try-with-resources, which ensures you don't leak the prepared statement and result set.