MySQL - Java - How to retrieve the results from multiple queries

Question

I have followed Multiple queries executed in java in single statement and a number of other posts to work out how to run multiple queries. I am now stuck on how to store the results so I can return them.

I know the answer from the above post is:

You have to use execute( String sql ) or its other variants to fetch results of the query execution.

boolean hasMoreResultSets = stmt.execute( multiQuerySqlString ); To iterate through and process results you require following steps:

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

However, I can not work out how to apply this example to my code (the following code returns no errors; I just do not get the results of the queries returned):

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();

I have tried the following:

//              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

However, the variables are no longer being passed (they were in the previous code and no SQL error was returned) and I get the error:

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

Your help is greatly appreciated.

Regards,

Glyn

Answer 1

You are combining two select SQL statements in one, Java/SQL doesn't work that way. It might work for statements that don't select anything.

Your options are either to make two distinct queries (one for Bronze and one for Silver), or rewrite your query to return all the results and then iterate through the ResultSet. I've provided an example of rewiring the query to not go back and forth so many times.

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

Or, if you can drop the LIKE and do an exact match, this might be quicker:

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

And then, in Java:

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

One more recommendation would be to not use column numbers to get query results, but to use the column names.