java - MySQL - Java - 如何从多个查询中检索结果

Question

我关注了Multiple queries executed in java in single statement和其他一些帖子来解决如何运行多个查询。我现在纠结于如何存储结果以便返回它们。

我从上面的帖子中知道答案是:

您必须使用 execute( String sql ) 或其其他变体来获取查询执行的结果。

boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );要遍历和处理结果，您需要执行以下步骤:

READING_QUERY_RESULTS: // label  
    while ( hasMoreResultSets || stmt.getUpdateCount() != -1 ) {  
        if ( hasMoreResultSets ) {  
            Resultset rs = stmt.getResultSet();
            // handle your rs here
        } // if has rs
        else { // if ddl/dml/...
            int queryResult = stmt.getUpdateCount();  
            if ( queryResult == -1 ) { // no more queries processed  
                break READING_QUERY_RESULTS;  
            } // no more queries processed  
            // handle success, failure, generated keys, etc here
        } // if ddl/dml/...

        // check to continue in the loop  
        hasMoreResultSets = stmt.getMoreResults();  
    } // while results

但是，我不知道如何将此示例应用到我的代码中(以下代码未返回任何错误；我只是没有得到返回的查询结果):

String selectQry = ("SELECT COUNT(at_cub_awards.ca_id) " +
                "FROM at_cub_details, at_cub_awards, at_award " + 
                "WHERE at_cub_details.grp_id = ? " +
                "  AND at_cub_details.cd_id = at_cub_awards.cd_id " +
                "  AND at_cub_awards.aw_id = at_award.aw_id " +
                "  AND at_award.aw_award_name LIKE '%Bronze Boomerang%' " +
                "GROUP BY at_award.aw_award_type;" +
                "SELECT COUNT(at_cub_awards.ca_id) " +
                "FROM at_cub_details, at_cub_awards, at_award " + 
                "WHERE at_cub_details.grp_id = ? " +
                "  AND at_cub_details.cd_id = at_cub_awards.cd_id " +
                "  AND at_cub_awards.aw_id = at_award.aw_id " +
                "  AND at_award.aw_award_name LIKE '%Silver Boomerang%' " +
                "GROUP BY at_award.aw_award_type;");

        try {
            // Get Connection and Statement from DataSource
            c = ds.getConnection();
            ps = c.prepareStatement(selectQry);

            try {
                // Create a statement and execute the query on it               
                ps.setString(1, groupID);
                ps.setString(2, groupID);

                // Get result set
                ResultSet result = ps.executeQuery();

                while (result.next()) {
                    packSummary = new PackSummary(result.getInt(1), result.getInt(2), null, null, null, null, null, null, null, null);
                }

                // Clean up
                ps.close();
                c.close();

我尝试了以下方法:

//              ResultSet result = ps.executeQuery();
//
//              while (result.next()) {
//                  packSummary = new PackSummary(result.getInt(1), result.getInt(2), null, null, null, null, null, null, null, null);
//              }

                boolean hasMoreResultSets = ps.execute( selectQry );

                READING_QUERY_RESULTS: // label  
                    while ( hasMoreResultSets || ps.getUpdateCount() != -1 ) {  
                        if ( hasMoreResultSets ) {  
                            ResultSet rs = ps.getResultSet();
                            packSummary = new PackSummary(rs.getInt(1), rs.getInt(2), null, null, null, null, null, null, null, null);
                        } // if has rs
                        else { // if ddl/dml/...
                            int queryResult = ps.getUpdateCount();  
                            if ( queryResult == -1 ) { // no more queries processed  
                                break READING_QUERY_RESULTS;  
                            } // no more queries processed  
                            // handle success, failure, generated keys, etc here
                        } // if ddl/dml/...

                        // check to continue in the loop  
                        hasMoreResultSets = ps.getMoreResults();  
                    } // while results

但是，变量不再被传递(它们在之前的代码中并且没有返回 SQL 错误)并且我得到错误:

SQLException in getPackSummary: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server  version for the right syntax to use near '?   AND at_cub_details.cd_id = at_cub_awards.cd_id   AND at_cub_awards.aw_id = a' at line 1

非常感谢您的帮助。

问候，

格林

Answer 1

您将两个 select SQL 语句合并为一个，Java/SQL 不能那样工作。它可能适用于不选择任何内容的语句。

您的选择是进行两个不同的查询(一个用于青铜级，一个用于银级)，或者重写您的查询以返回所有结果，然后遍历 ResultSet。我提供了一个重新连接查询的示例，使其不会来回多次。

String selectQry = ("SELECT at_award.aw_award_name as award_name, COUNT(at_cub_awards.ca_id) as award_count" +
    "FROM at_cub_details, at_cub_awards, at_award " + 
    "WHERE at_cub_details.grp_id = ? " +
    "  AND at_cub_details.cd_id = at_cub_awards.cd_id " +
    "  AND at_cub_awards.aw_id = at_award.aw_id " +
    "  AND (at_award.aw_award_name LIKE '%Bronze Boomerang%' or " +
    "       at_award.aw_award_name LIKE '%Silver Boomerang%') " +
    "GROUP BY at_award.aw_award_type";

或者，如果您可以删除 LIKE 并进行精确匹配，这可能会更快:

String selectQry = ("SELECT at_award.aw_award_name as award_name, COUNT(at_cub_awards.ca_id) as award_count" +
    "FROM at_cub_details, at_cub_awards, at_award " + 
    "WHERE at_cub_details.grp_id = ? " +
    "  AND at_cub_details.cd_id = at_cub_awards.cd_id " +
    "  AND at_cub_awards.aw_id = at_award.aw_id " +
    "  AND (at_award.aw_award_name in ('Bronze Boomerang', 'Silver Boomerang') " +
    "GROUP BY at_award.aw_award_type";

然后，在 Java 中:

ResultSet result = ps.executeQuery();
while (result.next()) {
    System.out.println("Award: " + rs.getString("award_name"));
    System.out.println("Count: " + rs.getInt("award_count"));
}       

另一个建议是不要使用列号来获取查询结果，而是使用列名。