The column name ... was not found in this ResultSet

Question

We are using java jdk 1.7.0_45, postgresql jdbc connector postgresql-9.3-1100.jdbc41.jar.

Here is a synopsis of our problem, as much as possible of code pasted below.

This code:

        ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
                while (rs.next()){
                    System.out.println(rs.getInt("d.deptId"));
    

Produces the error:

    org.postgresql.util.PSQLException: The column name d.deptId was not found in this ResultSet.
    

This code:

                        ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
                while (rs.next()){
                    System.out.println(rs.getInt("deptId"));
    

Produces no error.

Is there a way, besides removing the "d." from the first query, to make the first code snippet not throw the error message?

Here is the source code:

public class JoinTest {
    @Test
    public void test(){
        boolean pass = false;
        try {
            ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
            String label = rs.getMetaData().getColumnLabel(1);  // What do you get?
            System.out.println("label = " + label);
            while (rs.next()){
                System.out.println(rs.getInt("d.deptId"));
                pass = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            pass=false;
        }
        assertTrue(pass);
    }
    @Test
    public void test2(){
        boolean pass = false;
        try {
            ResultSet rs = DbConn.getInstance().doQuery("Select d.deptId from Depts d");
            while (rs.next()){
                System.out.println(rs.getInt("deptId"));
                pass = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            pass=false;
        }
        assertTrue(pass);
    }
}

    public class DbConn {


    private static String url = "jdbc:postgresql://server:port/schema";
        private static Properties props = new Properties(); {
            props.setProperty("user","userid");
            props.setProperty("password","passwprd");
        }
        private  Connection conn;

        private DbConn(){}
        private static DbConn instance;
        public static DbConn getInstance() throws SQLException{
            if (instance == null){
                instance = new DbConn();
                instance.conn = DriverManager.getConnection(url, props);
            }
            return instance;
        }
        public ResultSet doQuery(String query) throws SQLException{
            Logger.log("DbConn.doQuery: " + query);
            Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(query);
                return rs;
        }
        }

}

Answer 1

The query:

 select d.deptId from Depts d

produces a single-column resultset with the result-alias "deptId". There is no "d.deptId" column. If you want one, you can request that as the column alias instead:

 select d.deptId AS "d.deptId" from Depts d

PgJDBC can't do anything about this because it has no idea that the resultset column "deptId" is related to the "d.deptId" in the select-list. Teaching it about that would force it to understand way more about the SQL it processes than would be desirable, and lead to maintenance and performance challenges.

Answer 2

The second one works - why isn't that acceptable?

You can also do this:

 System.out.println(rs.getInt(1));

If you change the query you have to change the code, too.