test/jdbc/Gateway/GwStmt6.java

   1 //update tuple with WHERE clause having param for all fields(UPDATE t1 SET f2=100 WHERE f1=?AND f3=?....)
   2 import java.sql.*;
   3 /**
   4  *
   5  * @author bijaya
   6  */
   7 public class GwStmt6 {
   8      public static void main(String[] arg)
   9     {
  10         try
  11         {
  12             Class.forName("csql.jdbc.JdbcSqlDriver");
  13             Connection con=DriverManager.getConnection("jdbc:gateway","root","manager");
  14             Statement cStmt=con.createStatement();
  15             PreparedStatement stmt=null,selStmt=null;
  16             stmt=con.prepareStatement("INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?);");
  17             int ret=0;
  18             for(int i=0;i<10;i++)
  19             {
  20                 stmt.setInt(1,i);
  21                 stmt.setShort(2,(short)(i+1));
  22 //                stmt.setByte(3,(byte)(i+2));
  23                 stmt.setLong(3,(long)i);
  24                 stmt.setFloat(4,(float)1000+i);
  25                 stmt.setString(5, String.valueOf("Cache"+i));
  26                 stmt.setDate(6,Date.valueOf("2008-03-21"));
  27                 if(System.getenv("DSN").equals("oracle"))
  28                   stmt.setDate(7,Date.valueOf("2009-04-24"));
  29                 else
  30                   stmt.setTime(7,Time.valueOf("18:00:00"));
  31                 stmt.setTimestamp(8,Timestamp.valueOf("2008-03-21 18:00:00"));
  32                 ret=stmt.executeUpdate();
  33                 if(ret!=1) break;
  34             }
  35             stmt.close();
  36             con.commit();
  37             selStmt=con.prepareStatement("Select * from t1");
  38             ResultSet rs=null;
  39             rs=selStmt.executeQuery();
  40             while(rs.next())
  41             {
  42                 if(System.getenv("DSN").equals("oracle")){
  43                    System.out.println("Tuple value is " + rs.getInt(1) + " "+
  44                                                         rs.getShort(2) + " "+
  45                                                         rs.getLong(3) + " "+
  46                                                         rs.getFloat(4) + " "+
  47                                                         rs.getString(5) + " "+
  48                                                         rs.getDate(6) + " "+
  49                                                         rs.getDate(7) + " "+
  50                                                         rs.getTimestamp(8) + " "
  51                                                         );
  52                 }
  53                 else{
  54                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  55                                                         rs.getShort(2) + " "+
  56                                                         rs.getLong(3) + " "+
  57                                                         rs.getFloat(4) + " "+
  58                                                         rs.getString(5) + " "+
  59                                                         rs.getDate(6) + " "+
  60                                                         rs.getTime(7) + " "+
  61                                                         rs.getTimestamp(8) + " "
  62                                                         );
  63                 }
  64             }
  65             rs.close();
  66             con.commit();
  67             ret=0;
  68             stmt=con.prepareStatement("UPDATE t1 SET f2=?, f8=? WHERE f1=?AND f9=?;");
  69             for(int i=0;i<10;i++)
  70             {
  71                 stmt.setShort(1,(short)100);
  72                 if(System.getenv("DSN").equals("oracle"))
  73                   stmt.setDate(2,Date.valueOf("2009-09-25"));
  74                 else
  75                   stmt.setTime(2,Time.valueOf("19:38:25"));
  76                 stmt.setInt(3,3);
  77                 stmt.setTimestamp(4,Timestamp.valueOf("2008-03-21 18:00:00"));
  78                 ret=stmt.executeUpdate();
  79                 if(ret!=1) break;
  80             }
  81             stmt.close();
  82             con.commit();
  83             selStmt=con.prepareStatement("Select * from t1");
  84             rs=null;
  85             rs=selStmt.executeQuery();
  86             System.out.println();
  87             while(rs.next())
  88             {
  89                 if(System.getenv("DSN").equals("oracle")){
  90                    System.out.println("Tuple value is " + rs.getInt(1) + " "+
  91                                                         rs.getShort(2) + " "+
  92                                                         rs.getLong(3) + " "+
  93                                                         rs.getFloat(4) + " "+
  94                                                         rs.getString(5) + " "+
  95                                                         rs.getDate(6) + " "+
  96                                                         rs.getDate(7) + " "+
  97                                                         rs.getTimestamp(8) + " "
  98                                                         );
  99                 }
 100                 else{
 101                    System.out.println("Tuple value is " + rs.getInt(1) + " "+
 102                                                         rs.getShort(2) + " "+
 103                                                         rs.getLong(3) + " "+
 104                                                         rs.getFloat(4) + " "+
 105                                                         rs.getString(5) + " "+
 106                                                         rs.getDate(6) + " "+
 107                                                         rs.getTime(7) + " "+
 108                                                         rs.getTimestamp(8) + " "
 109                                                         );
 110                 }
 111             }
 112             rs.close();
 113             con.commit();
 114             con.close();
 115        }catch(Exception e) {
 116             System.out.println("Exception in Test: "+e);
 117             e.printStackTrace();
 118         }
 119     }
 120
 121
 122 }