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

当列数据类型为String时,如何在Resultset的输出值中添加双引号?

jdbc postgresql mysql java

-1

Metadata · 技术社区 · 5 年前

我正在尝试运行以下查询并将其输出插入另一个表(postgres db):

    String ssnQuery = "SELECT period_year, period_name, period_num, NULL as count_of_issues,
    ledger_id,
    balancing_segment,
    Count(*) AS count_of_account_segments,
    Sum(accounted_period_net_dr) AS balance_accounted_period_net_dr,
    Sum(accounted_period_net_cr) AS balance_accounted_period_net_cr,
    Round(Sum(accounted_period_net_dr_cr)) AS balance_accounted_period_net_dr_cr_diff,
    Sum(entered_period_net_dr) AS balance_entered_period_net_dr,
    Sum(entered_period_net_cr) AS balance_entered_period_net_cr,
    Round(Sum(entered_period_net_dr_cr)) AS balance_entered_period_net_dr_cr_diff,
    Sum(begin_balance_dr) AS begin_balance_dr,
    Sum(begin_balance_cr) AS begin_balance_cr,
    Round(Sum(net_beginning_balance)) AS net_beginning_balance,
    Round(Sum(net_closing_balance)) AS net_closing_balance
    FROM schema.tablename";

try {
    pstmnt       = financeConnection.prepareStatement(ssnQuery);
    rs           = pstmnt.executeQuery();
    rsmd         = rs.getMetaData();
    for(int i=1; i<=rsmd.getColumnCount(); i++) {
        columnNames.add(rsmd.getColumnName(i));
        if(i == 1) {
            queryColumns = rsmd.getColumnName(i);
        } else if(i<7) {
            queryColumns += ", " + rsmd.getColumnName(i);
        } else {
            queryColumns += ", value_" + (i-7);
        }
    }
    while (rs.next()) {
        queryValues = " ";
        for(String colname: columnNames) {
            if(queryValues.isEmpty()) {
                queryValues = rs.getString(colname);
            } else {
                queryValues += rs.getString(colname) + ", ";
            }
        }
        remCommas = queryValues.replaceAll(", $", "");
        insertQuery = "INSERT INTO bdmerge.gen_audit_func_hive_results (run_id, run_date, run_date_ist" + queryColumns + ") VALUES (" + runid +"," + utcTimeStamp + "," + istTimeStamp + "," + remCommas + ")";
        System.out.println("Final Insert query: " + insertQuery);
    }
} catch (SQLException e) {
    e.printStackTrace();
} catch(Exception e) {
    e.printStackTrace();
}

为了插入上述查询的输出,我根据目的表中的列名形成了插入查询,如下所示:

INSERT INTO schema.destinationTable (run_id, run_date, run_date_ist, source_system_type, source_system, module, source_table_name, period_year, period_name, period_num, count_of_issues, count_of_accounted_issues, count_of_entered_issues, value_0, value_1, value_2, value_3, value_4, value_5, value_6, value_7, value_8, value_9, value_10, value_11, value_12) VALUES (781,2018-11-12 08:15:32.0,2018-11-12 13:45:32.0, 2018, OCT-18, 10, null, 1, 1, 2073, ATRS, 6135, 6.2778220466107E11, 6.277946274560101E11, -1.2422795E7, 5.929031383587201E11, 5.9291556115366E11, -1.2422795E7, 3.931397937759116E13, 3.9313979377591164E13, 0.0)

run_id, count_of_issues, count_of_accounted_issues, count_of_entered_issues

是数字格式的(在postgres数据库上工作),其他的都是varchar(1000)。

为了插入varchar数据,我需要将 价值观 直到 价值12 双引号。

有什么方法可以将resultSet中的varchar列值用双引号括起来并插入到目标表中吗?

1 回复 | 直到 5 年前

Shariq 5 年前

需要在字符串值中添加双引号;请使用转义字符,如下所示:

String quotedStr = "Non Quoted," + " \"Quoted\".";

修改代码以解决问题:

   while (rs.next()) {
        queryValues = " ";
        int i = 0; 
        for(String colname: columnNames) {

            if(queryValues.isEmpty()) {
                queryValues = rs.getString(colname);
            } else {
            if (i >= 7)
                    queryValues += "\"" + rs.getString(colname) +"\"" + ", ";
            }
            else
                queryValues += rs.getString(colname) + ", ";
            i++;
        }