在Java中执行多个COUNT查询

Question

我有一个MySQL数据库（版本5.2 CE），我有一张表，我想根据用户给出的WHERE条件过滤到另一个表中（这来自一个数组列表）。我想对这个新表执行计数查询，以查看用户选择的分割。例如，COUNT（*）来自TableName WHERE [userConditions，如gender = male和gender = female等]。用户可以提供多个WHERE条件，但COUNT查询一次只能处理一个条件。因此，我让我的方法（执行此查询）一个数组，以根据用户选择的条件数返回多个查询，然后在for循环中执行每个查询。然而，这似乎给了我两种编译错误：i）我在String []方法中返回构建的字符串的方式，以及ii）我执行COUNT查询的方式。对于这些问题的代码：

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

} 

返回COUNT查询的阵列的方法：

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; 


} 

可有人请告诉我如何理智地实现这一点？非常感谢你。

Answer 1

我想你想有条件的聚集，而不是where：

select count(*) 
from mygrist_samples.subsample_population 
WHERE XXX 

是一样的：

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