test/jdbc/Gateway/GwStmt1.java

   1 //1. Table t1 is cached to csql.
   2 //Then do (INSERT/UPDATE/DELETE/SELECT) with params statement using gateway statement
   3
   4 import java.sql.Connection;
   5 import java.sql.DriverManager;
   6 import java.sql.PreparedStatement;
   7 import java.sql.ResultSet;
   8 import java.sql.Statement;
   9
  10
  11 /**
  12  *
  13  * @author bijaya
  14  */
  15 public class GwStmt1 {
  16      public static void main(String[] args)
  17     {
  18        try
  19        {
  20            Class.forName("csql.jdbc.JdbcSqlDriver");
  21            Connection con = DriverManager.getConnection("jdbc:gateway", "root", "manager");
  22            Statement cStmt = con.createStatement();
  23            PreparedStatement stmt = null, selStmt= null;
  24            stmt = con.prepareStatement("INSERT INTO t1 VALUES (?, ?);");
  25            for (int i =0 ; i< 10 ; i++) {
  26              stmt.setInt(1, i);
  27              stmt.setString(2, String.valueOf(i+100));
  28              stmt.executeUpdate();
  29            }
  30            stmt.close();
  31            con.commit();
  32            selStmt = con.prepareStatement("SELECT * from t1 where f1 = ?;");
  33            ResultSet rs = null;
  34            for (int i =0 ; i< 10 ; i++) {
  35              selStmt.setInt(1, i);
  36              rs = selStmt.executeQuery();
  37              while (rs.next())
  38              {
  39                  System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  40              }
  41              rs.close();
  42            }
  43            int ret=0;
  44            stmt = con.prepareStatement("UPDATE t1 SET f2 = ? WHERE f1 = ?;");
  45            for (int i =0 ; i< 10 ; i +=2) {
  46              stmt.setString(1, String.valueOf(i+200));
  47              stmt.setInt(2, i);
  48              ret = stmt.executeUpdate();
  49              if (ret != 1) break;
  50            }
  51            stmt.close();
  52            con.commit();
  53            System.out.println("After update, listing tuples:");
  54            for (int i =0 ; i< 10 ; i++) {
  55              selStmt.setInt(1, i);
  56              rs = selStmt.executeQuery();
  57              while (rs.next())
  58              {
  59                  System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  60              }
  61              rs.close();
  62            }
  63            con.commit();
  64            stmt = con.prepareStatement("DELETE FROM t1 WHERE f1 = ?;");
  65            for (int i =0 ; i< 10 ; i +=3) {
  66              stmt.setInt(1, i);
  67              ret = stmt.executeUpdate();
  68              if (ret !=1) break;
  69            }
  70            stmt.close();
  71            con.commit();
  72
  73            System.out.println("After delete, listing tuples:");
  74            for (int i =0 ; i< 10 ; i++) {
  75              selStmt.setInt(1, i);
  76              rs = selStmt.executeQuery();
  77              while (rs.next())
  78              {
  79                  System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  80              }
  81              rs.close();
  82            }
  83            con.commit();
  84            con.close();
  85         }catch(Exception e) {
  86              System.out.println("Exception in Test: "+e);
  87              e.printStackTrace();
  88         }
  89     }
  90
  91 }