examples/jdbc/jdbcexample.java

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