test/jdbc/Parameters/ParaTest2Now.java

   1 //select tuple with WHERE clause having param for some fields(SELECT * FROM T1 WHERE f1='now' AND f2=? AND f3=?)
   2 import java.sql.*;
   3 public class ParaTest2Now
   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 T1 (f1 date, f2 int, f3 char(10));");
  14             con.commit();
  15             PreparedStatement stmt=null,selStmt=null;
  16             stmt=con.prepareStatement("INSERT INTO T1 values('2008-03-21',0,'Nihar0');");
  17             stmt.executeUpdate();
  18             stmt=con.prepareStatement("INSERT INTO T1 values('now',1,'Nihar1');");
  19             stmt.executeUpdate();
  20             stmt=con.prepareStatement("INSERT INTO T1 values('2008-03-24',2,'Nihar2');");
  21             stmt.executeUpdate();
  22             stmt=con.prepareStatement("INSERT INTO T1 values('now',3,'Nihar3');");
  23             stmt.executeUpdate();
  24             stmt=con.prepareStatement("INSERT INTO T1 values('2008-03-21',4,'Nihar4');");
  25             stmt.executeUpdate();
  26             stmt=con.prepareStatement("INSERT INTO T1 values('now',5,'Nihar5');");
  27             stmt.executeUpdate();
  28
  29             stmt.close();
  30             con.commit();
  31
  32             System.out.println("SELECT * FROM T1;\n");
  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.getDate(1) + " "+
  39                                                         rs.getInt(2) + " "+
  40                                                         rs.getString(3)
  41                                                         );
  42
  43             }
  44
  45             System.out.println("\nSELECT * FROM T1 where f1 = 'now';\n");
  46             selStmt=con.prepareStatement("SELECT * FROM T1 where f1 = 'now';");
  47             rs=null;
  48             rs=selStmt.executeQuery();
  49             while(rs.next())
  50             {
  51                 System.out.println("Tuple value is " + rs.getDate(1) + " "+
  52                                                         rs.getInt(2) + " "+
  53                                                         rs.getString(3)
  54                                                         );
  55
  56             }
  57
  58             System.out.println("\nSELECT * FROM T1 where f2 >= ? and f3 <= ?;\n");
  59             selStmt=con.prepareStatement("SELECT * FROM T1 where f2 >= ? and f3 <= ?");
  60             rs=null;
  61             selStmt.setInt(1,1);
  62             selStmt.setString(2,String.valueOf("Nihar3"));
  63             rs=selStmt.executeQuery();
  64             while(rs.next())
  65             {
  66                 System.out.println("Tuple value is " + rs.getDate(1) + " "+
  67                                                         rs.getInt(2) + " "+
  68                                                         rs.getString(3)
  69                                                         );
  70
  71             }
  72
  73             System.out.println("\nSELECT * FROM T1 where f1='now' and f2 >= ? and f3 <= ?;\n");
  74             selStmt=con.prepareStatement("SELECT * FROM T1 where f1='now' and f2 >= ? and f3 <= ?");
  75             rs=null;
  76             selStmt.setInt(1,1);
  77             selStmt.setString(2,String.valueOf("Nihar3"));
  78             rs=selStmt.executeQuery();
  79             while(rs.next())
  80             {
  81                 System.out.println("Tuple value is " + rs.getDate(1) + " "+
  82                                                         rs.getInt(2) + " "+
  83                                                         rs.getString(3)
  84                                                         );
  85
  86             }
  87
  88             rs.close();
  89             con.commit();
  90             cStmt.executeUpdate("Drop table T1;");
  91             con.close();
  92        }catch(Exception e) {
  93             System.out.println("Exception in Test: "+e);
  94             e.printStackTrace();
  95         }
  96     }
  97 }
  98