test/jdbc/Statement/AggPara.java

   1 //select tuple with WHERE clause having param for some fields in Agggegate
   2 //SELECT f1,AVG(f5) FROM T1 GROUP BY f1 HAVING AVG(f5)>?;
   3 //SELECT f1,AVG(f5) FROM T1 WHERE f1>? GROUP BY f1 ;
   4 //SELECT f1,AVG(f5) FROM T1 WHERE f1>? GROUP BY f1 HAVING AVG(f5)>?;
   5
   6 import java.sql.*;
   7 public class AggPara
   8 {
   9     public static void main(String[] arg)
  10     {
  11         try
  12         {
  13             Class.forName("csql.jdbc.JdbcSqlDriver");
  14             Connection con=DriverManager.getConnection("jdbc:csql","root","manager");
  15             Statement cStmt=con.createStatement();
  16             PreparedStatement selStmt=null;
  17
  18             System.out.println("SELECT T1.f1,AVG(T1.f5) FROM T1 GROUP BY T1.f1;");
  19             selStmt=con.prepareStatement("SELECT T1.f1,AVG(T1.f5) FROM T1 GROUP BY T1.f1;");
  20             ResultSet rs=null;
  21             rs=selStmt.executeQuery();
  22             System.out.println("T1.f1\tAVG(T1.f5)");
  23             while(rs.next())
  24             {
  25                 System.out.println( rs.getInt(1) + "\t"+
  26                                     rs.getDouble(2)
  27                                    );
  28
  29             }
  30             rs.close();
  31             con.commit();
  32
  33             System.out.println("SELECT f1,AVG(f5) FROM T1 GROUP BY f1 HAVING AVG(f5)>5000;");
  34             selStmt=con.prepareStatement("SELECT f1,AVG(f5) FROM T1 GROUP BY f1 HAVING AVG(f5)>?; ");
  35             rs=null;
  36             selStmt.setDouble(1,(float)5000);
  37             rs=selStmt.executeQuery();
  38             System.out.println("T1.f1\tAVG(T1.f5)");
  39             while(rs.next())
  40             {
  41                 System.out.println( rs.getInt(1) + "\t"+
  42                                     rs.getDouble(2)
  43                                    );
  44
  45             }
  46             rs.close();
  47             con.commit();
  48
  49             System.out.println("SELECT f1,AVG(f5) FROM T1 WHERE f1>200 GROUP BY f1;");
  50             selStmt=con.prepareStatement("SELECT f1,AVG(f5) FROM T1 WHERE f1>? GROUP BY f1; ");
  51             rs=null;
  52             selStmt.setInt(1,(int)200);
  53             rs=selStmt.executeQuery();
  54             System.out.println("T1.f1\tAVG(T1.f5)");
  55             while(rs.next())
  56             {
  57                 System.out.println( rs.getInt(1) + "\t"+
  58                                     rs.getDouble(2)
  59                                    );
  60
  61             }
  62             rs.close();
  63             con.commit();
  64
  65             System.out.println("SELECT f1,AVG(f5) FROM T1 WHERE f1> 100 GROUP BY f1 HAVING AVG(f5)>5000;");
  66             selStmt=con.prepareStatement("SELECT f1,AVG(f5) FROM T1 WHERE f1>? GROUP BY f1 HAVING AVG(f5)>?;");
  67             rs=null;
  68             selStmt.setInt(1,100);
  69             selStmt.setDouble(2,(float)5000);
  70             rs=selStmt.executeQuery();
  71             System.out.println("T1.f1\tT1.f2\tAVG(T1.f5)");
  72             while(rs.next())
  73             {
  74                 System.out.println( rs.getInt(1) + "\t"+
  75                                     rs.getDouble(2)
  76                                    );
  77
  78             }
  79             rs.close();
  80             con.commit();
  81
  82             cStmt.executeUpdate("Drop table T1;");
  83             con.close();
  84        }catch(Exception e) {
  85             System.out.println("Exception in Test: "+e);
  86             e.printStackTrace();
  87         }
  88     }
  89 }
  90