Execution of multiple select statements

[p9878]

I want to execute multiple select statements in a single query to find frequency of values in a database
I have tried this:

import java.sql.*;
import java.io.*;    
class t1
{
  public static void main(String a[])throws Exception
 {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection con=DriverManager.getConnection("jdbc:odbc:mydsn");
      Statement s=con.createStatement();
      ResultSet rs=s.executeQuery("select Platform,count(*) from Table11 group by Platform;select Job,count(*) from Table11 group by Job");
      ResultSetMetaData r=rs.getMetaData();
      for(int i=1;i<=r.getColumnCount();i++)
       {
         String c=r.getColumnLabel(i);
         System.out.print(c+ " ");
 
       }
      while(rs.next())
     {
        System.out.println("\n"+rs.getString(1)+ " " +rs.getString(2));
     }
 
 }
}

[Rabbit]

Why can't you run different queries? Any other workaround would require more work than just running separate queries.

[p9878]

I think it becomes lenghthy.I want to find frequency of 10 attributes in a database.
can't we write multiple queries in a single resultset?

[chaarmann]

You can combine results of multiple queries with "union".
If you need to differentiate later on from which query the result came from:

select value1 as result1, null as result2 from table1 where ...
union
select null as result1, value2 as result2 from table2 where ...

or use an additinal field:

select "1" as queryNumber, value1 as result from table1 where ...
union
select "2" as queryNumber, value2 as result from table2 where ...

But as Rabbit statet, most time it's better to run 2 queries. You must weight the connection costs, response time and memory usage dependent of the expected results. If you expect several hundred records, use 2 queries. if you run over several tables but only expect a single record back, use combined query.

[Sherin]

Try This Code

import java.sql.*;
 
public class jdbcConn {
   public static void main(String[] args) throws Exception{
      Class.forName("org.apache.derby.jdbc.ClientDriver");
      Connection con = DriverManager.getConnection
      ("jdbc:derby://localhost:1527/testDb","name","pass");
 
      Statement stmt = con.createStatement
      (ResultSet.TYPE_SCROLL_SENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
      String insertEmp1 = "insert into emp values
      (10,'jay','trainee')";
      String insertEmp2 = "insert into emp values
      (11,'jayes','trainee')";
      String insertEmp3 = "insert into emp values
      (12,'shail','trainee')";
      con.setAutoCommit(false);
      stmt.addBatch(insertEmp1);//inserting Query in stmt
      stmt.addBatch(insertEmp2);
      stmt.addBatch(insertEmp3);
      ResultSet rs = stmt.executeQuery("select * from emp");
      rs.last();
      System.out.println("rows before batch execution= "
      + rs.getRow());
      stmt.executeBatch();
      con.commit();
      System.out.println("Batch executed");
      rs = stmt.executeQuery("select * from emp");
      rs.last();
      System.out.println("rows after batch execution= "
      + rs.getRow());
   }
}