test/jdbc/Parameters/ParaTest1Date.java

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