test/jdbc/network/Gateway/GwTest15.java

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