test/jdbc/Statement/JoinPara.java

   1 //select tuple with WHERE clause having param for some fields in Join(SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=? AND T1.f2>=? AND T2.f3=?)
   2 import java.sql.*;
   3 public class JoinPara
   4 {
   5     public static void main(String[] arg)
   6     {
   7         try
   8         {
   9             Class.forName("csql.jdbc.JdbcSqlDriver");
  10             Connection con=DriverManager.getConnection("jdbc:csql","root","manager");
  11             Statement cStmt=con.createStatement();
  12             cStmt.execute("CREATE TABLE T1 (f1 integer, f2 smallint, f3 tinyint, f4 bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
  13             cStmt.execute("CREATE TABLE T2 (f1 integer, f2 smallint, f3 tinyint, f4 bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
  14             PreparedStatement stmt=null,selStmt=null;
  15             stmt=con.prepareStatement("INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?);");
  16             int ret=0;
  17             for(int i=0;i<10;i++)
  18             {
  19                 stmt.setInt(1,i);
  20                 stmt.setShort(2,(short)(i+1));
  21                 stmt.setByte(3,(byte)(i+2));
  22                 stmt.setLong(4,(long)i);
  23                 stmt.setFloat(5,(float)1000+i);
  24                 stmt.setString(6, String.valueOf("Nihar"+i));
  25                 stmt.setDate(7,Date.valueOf("2008-03-21"));
  26                 stmt.setTime(8,Time.valueOf("18:00:00"));
  27                 stmt.setTimestamp(9,Timestamp.valueOf("2008-03-21 18:00:00"));
  28                 ret=stmt.executeUpdate();
  29                 if(ret!=1) break;
  30             }
  31             stmt.close();
  32             System.out.println("SELECT * FROM T1;");
  33             selStmt=con.prepareStatement("SELECT * FROM T1;");
  34             ResultSet rs=null;
  35             rs=selStmt.executeQuery();
  36             while(rs.next())
  37             {
  38                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  39                                                         rs.getShort(2) + " "+
  40                                                         rs.getByte(3) + " "+
  41                                                         rs.getLong(4) + " "+
  42                                                         rs.getFloat(5) + " "+
  43                                                         rs.getString(6) + " "+
  44                                                         rs.getDate(7) + " "+
  45                                                         rs.getTime(8) + " "+
  46                                                         rs.getTimestamp(9) + " "
  47                                                         );
  48
  49             }
  50             rs.close();
  51             con.commit();
  52             stmt=con.prepareStatement("INSERT INTO T2 VALUES(?,?,?,?,?,?,?,?,?);");
  53             ret=0;
  54             for(int i=0;i<10;i+=2)
  55             {
  56                 stmt.setInt(1,i);
  57                 stmt.setShort(2,(short)(i+1));
  58                 stmt.setByte(3,(byte)(i+2));
  59                 stmt.setLong(4,(long)i);
  60                 stmt.setFloat(5,(float)1000+i);
  61                 stmt.setString(6, String.valueOf("Nihar"+i));
  62                 stmt.setDate(7,Date.valueOf("2008-03-21"));
  63                 stmt.setTime(8,Time.valueOf("18:00:00"));
  64                 stmt.setTimestamp(9,Timestamp.valueOf("2008-03-21 18:00:00"));
  65                 ret=stmt.executeUpdate();
  66                 if(ret!=1) break;
  67             }
  68             stmt.close();
  69
  70             System.out.println("SELECT * FROM T2;");
  71             selStmt=con.prepareStatement("SELECT * FROM T2;");
  72             rs=null;
  73             rs=selStmt.executeQuery();
  74             while(rs.next())
  75             {
  76                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  77                                                         rs.getShort(2) + " "+
  78                                                         rs.getByte(3) + " "+
  79                                                         rs.getLong(4) + " "+
  80                                                         rs.getFloat(5) + " "+
  81                                                         rs.getString(6) + " "+
  82                                                         rs.getDate(7) + " "+
  83                                                         rs.getTime(8) + " "+
  84                                                         rs.getTimestamp(9) + " "
  85                                                         );
  86
  87             }
  88             rs.close();
  89             con.commit();
  90
  91             System.out.println("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2;");
  92             selStmt=con.prepareStatement("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2");
  93             rs=null;
  94             rs=selStmt.executeQuery();
  95             System.out.println("T1.f1\tT2.f1\tT1.f2\tT2.f2\tT1.f3\tT2.f3");
  96             while(rs.next())
  97             {
  98                 System.out.println( rs.getInt(1) + "\t"+
  99                                     rs.getInt(2) + "\t"+
 100                                     rs.getShort(3) + "\t"+
 101                                     rs.getShort(4) + "\t"+
 102                                     rs.getByte(5) + "\t"+
 103                                     rs.getByte(6)
 104                                    );
 105
 106             }
 107             rs.close();
 108             con.commit();
 109
 110             System.out.println("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=2 AND T1.f2>=4 AND T2.f3=6");
 111             selStmt=con.prepareStatement("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=? AND T1.f2>=? AND T2.f3=?");
 112             rs=null;
 113             selStmt.setInt(1,2);
 114             selStmt.setShort(2,(short)4);
 115             selStmt.setByte(3,(byte)6);
 116             rs=selStmt.executeQuery();
 117             System.out.println("T1.f1\tT2.f1\tT1.f2\tT2.f2\tT1.f3\tT2.f3");
 118             while(rs.next())
 119             {
 120                 System.out.println( rs.getInt(1) + "\t"+
 121                                     rs.getInt(2) + "\t"+
 122                                     rs.getShort(3) + "\t"+
 123                                     rs.getShort(4) + "\t"+
 124                                     rs.getByte(5) + "\t"+
 125                                     rs.getByte(6)
 126                                    );
 127
 128             }
 129             rs.close();
 130             con.commit();
 131
 132             cStmt.executeUpdate("Drop table T1;");
 133             cStmt.executeUpdate("Drop table T2;");
 134             con.close();
 135        }catch(Exception e) {
 136             System.out.println("Exception in Test: "+e);
 137             e.printStackTrace();
 138         }
 139     }
 140 }
 141