test/sqlnetwork/Jdbcnetwork/StmtTest1.java

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