alternative to jdbc resultset rs.next()

Question

I have a first resultset within which I have to iterate through userids and for each userid, I have to perform several select count(*)'s all of which return single valued outputs. If you haven't understood what I said, please follow the pseudo code below:

ResultSet rs = stmt.executeQuery("select userid from tablename");
while(rs.next()){
    String userid = rs.getString("userId");
    ResultSet rs1 = stmt.executeQuery("select count(*) as cnt1 from xxx.... where userId = "+userId);
    if(rs1.next())
        String count1 = rs1.getString("cnt1");
    rs1.close();
    ResultSet rs2 = stmt.executeQuery("select count(*) as cnt2...");
    if(rs2.next())
        String count2 = rs2.getString("cnt2");
    rs2.close();
    ....
    rs10.close();

Since this is inefficient, I was hoping to get past Resultset each time by writing some sort of direct query to retrieve each different count like

String  cnt1 = stmt.executeQuery("select count(*) as noE from useractiontable where curr_action='edit'"  + " and userId = " + userId).getString("noE");

I know something like this cannot be done without using rs.next() each time. Is prepare statement the way to go? Is there another way? Appreciate any pointers in this regard.

Answer 1

You can use group by to retrieve all the user with count(*)

ResultSet rs = stmt.executeQuery("select userid,count(*) from tablename group by userid");