java ResultSet只返回表中的最后一条记录

Question

下面的java方法将ResultSet数据设置为bean类，我正在获取数据。但是，runHiveQuery（）方法只返回表中最后一条记录的一行。在调试代码时，我发现resultset被循环了两次，因为我们有两条记录。但是，在返回bean类对象时存在一些问题，因为它只检索一条记录

找不到哪里出了问题

public CSPData getCSPData() throws SQLException {
        try {
            String drivername = "org.apache.hive.jdbc.HiveDriver";
            Class.forName(drivername);
            connection = DriverManager.getConnection("jdbc:hive2://hddev-c01-edge-01:20000/");
            statement = connection.createStatement();
            resultset = statement.executeQuery(
                    "select distinct db_name as db_name,db_server_name as db_server_name,lower(db_name) as l_db_name,lower(db_server_name) as l_server_name,regexp_replace(lower(db_server_name), '-', '_') as server_name,db_server_name_secondary as db_server_name_secondary from csp.curated_input");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }


        while (resultset.next()) {
            cspdata.setDbName(resultset.getString("db_name"));
            cspdata.setDbServerName(resultset.getString("db_server_name"));
            cspdata.setDbServerNameSecondary(resultset.getString("db_server_name_secondary"));
            cspdata.setlDbName(resultset.getString("l_db_name"));
            cspdata.setlServerName(resultset.getString("l_server_name"));
            cspdata.setServerName(resultset.getString("server_name"));
        }

        return cspdata;
    }

public void runHiveQuery() throws SQLException {

        CSPData cspdata = hivedao.getCSPData();
        String hive_db = "csp";
        String dbname = cspdata.getDbName();
        String dbservername = cspdata.getDbServerName();
        String servername = cspdata.getlServerName();

        String drop = "Drop table if exists " + hive_db + "." + "IB_C3_" + dbname + "_" + dbservername;
        String insert = "insert into table " + hive_db + "." + "IB_export_log select " + "\'ib_c3_" + dbname + "_"
                + servername + "\' from " + hive_db + "." + "dual limit 1";

        System.out.println(drop);
        System.out.println(insert);

    }

Answer 1

代码返回最后一条记录，因为它只返回一条记录。您应该返回一个列表：

public List<CSPData> getCSPData() throws SQLException {
    List<CSPData> result = new ArrayList<>();
    try {
        String drivername = "org.apache.hive.jdbc.HiveDriver";
        Class.forName(drivername);
        connection = DriverManager.getConnection("jdbc:hive2://hddev-c01-edge-01:20000/");
        statement = connection.createStatement();
        resultset = statement.executeQuery(
                "select distinct db_name as db_name,db_server_name as db_server_name,lower(db_name) as l_db_name,lower(db_server_name) as l_server_name,regexp_replace(lower(db_server_name), '-', '_') as server_name,db_server_name_secondary as db_server_name_secondary from csp.curated_input");

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        System.exit(1);
    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println(e.getMessage());
    }


    while (resultset.next()) {
        CSPData cspdata = new CSPData ();
        cspdata.setDbName(resultset.getString("db_name"));
        cspdata.setDbServerName(resultset.getString("db_server_name"));
        cspdata.setDbServerNameSecondary(resultset.getString("db_server_name_secondary"));
        cspdata.setlDbName(resultset.getString("l_db_name"));
        cspdata.setlServerName(resultset.getString("l_server_name"));
        cspdata.setServerName(resultset.getString("server_name"));
        result.add(cspdata);
    }

    return result;
}