在此ResultSet中找不到列名。..

Question

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

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

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

除了去掉“D”之外，有没有办法。从第一个查询开始，使第一个代码段不抛出错误消息？

以下是源代码：

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

查询：

 select d.deptId from Depts d

生成一个结果别名为“DEPTID”的单列resultset。没有“.deptid”列。如果您想要一个，您可以请求它作为列别名：

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

PgJDBC对此无能为力，因为它不知道resultset列“deptid”与select-list中的“.deptid”相关。对it进行这方面的教学将迫使it对SQL it过程的理解远远超出预期，并导致维护和性能方面的挑战。