Executing multiple COUNT queries in Java

Question

I have a MySQL database (ver. 5.2 CE) and I have a table which I want to filter into a another table based on the WHERE conditions given by the user (this comes from an array list). I want to perform count query on this new table for a split chosen by the user. For example, COUNT(*) from TableName WHERE [userConditions like gender=male and gender=female, etc]. The user can give more than one WHERE conditions but the COUNT query will only take one condition at a time. Hence, I made my method (which performs this query) an array to return multiple queries based on the number of conditions chosen by the user and then execute each query in a for loop. However, this seems to give me compilation errors in 2 ways: i) the way I return a built string in a String[] method and ii) the way I execute the COUNT query. The code for these problems:

private String countQuery;

public SetupSubsamplePopulation(UserSelectedSplit split) {

    this.split = split;
    // connect to the database
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull","root", "sharadha_1992");

        String buildSelectQuery = buildSelectQueryForCode();
        String getRowsFromTable = getNumberOfRows();
        stmt = connection.createStatement();
        rows = stmt.executeUpdate(buildSelectQuery);
        ResultSet rs = stmt.executeQuery(getRowsFromTable);

        for (int i=0; i<getCountOfWhereCondition().length; i++){
            //where I get the executeQuery error
            ResultSet rs1= stmt.execute(getCountOfWhereCondition());
            while (rs1.next()){
                rowsCount= rs.getRow();
                rows_count++;
            }
        }
        while (rs.next()) {
            rows = rs.getRow();
            rows_inserted++;
        }

        System.out.println(rows_inserted);
        System.out.println(rows_count);

    } catch (Exception e) {
        e.printStackTrace();
    }

}

The method which returns the array of COUNT queries:

public String[] getCountOfWhereCondition() {
    countQuery="SELECT COUNT (*) FROM (SELECT * from mygrist_samples.subsample_population WHERE ";

    for (int i = 0; i < split.getSplitConditions().size(); i++) {

        String getCorrespondingCodeFromDatabase = split.getSplitConditions().get(i).getCode();
        getCorrespondingCodeFromDatabase = getCorrespondingCodeFromDatabase.replaceAll("-", "_");

        Enumerations enum1 = new Enumerations();

        String getCorrespondingRelationshipOperator = enum1.getOperator(split.getSplitConditions().get(i).getRelationship());

        countQuery+=getCorrespondingCodeFromDatabase + " " + getCorrespondingRelationshipOperator + " '" + split.getSplitConditions().get(i).getAnswer() + "'";
    }
    countQuery+=")";

    System.out.println(countQuery);
    //error which doesn't allow me to return a string
    return countQuery;


}

Can someone please tell me how to implement this sensibly? Thank you very much.

Gordon Linoff · Accepted Answer · 2013-04-04 19:44:30Z

1

I think you want conditional aggregation rather than a where:

select count(*)
from  mygrist_samples.subsample_population
WHERE XXX

Is the same as:

select sum(case when XXX then 1 else 0 end) as cnt1
from mygrist_samples.subsample_population

Now you can add multiple conditions on one call:

select sum(case when XXX then 1 else 0 end) as cnt1,
       sum(case when yyy then 1 else 0 end) as cnt1
from  mygrist_samples.subsample_population

answered Apr 4, 2013 at 19:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sharadha Jayaraman Over a year ago

Can you please show me an instance of this in code? I mean, how could I take in the sum parameters from an arrayList i.e. split.getSplitConditions() here?

Collectives™ on Stack Overflow

Executing multiple COUNT queries in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related