代码之家 › 专栏 › 技术社区 › wandermonk

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

hive jdbc java

wandermonk · 技术社区 · 9 年前

下面的java方法将ResultSet数据设置为bean类,我正在获取数据。但是,方法runHiveQuery()只返回表中最后一条记录的一行。在调试代码时,我发现结果集被循环了两次,因为我们有两条记录。但是,在返回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);

    }

1 回复 | 直到 9 年前

Eran 9 年前

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

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;
}