test/jdbc/network/Statement/TestScript6.java

   1 //Select Data with Field name instead of position
   2
   3 import java.sql.Connection;
   4 import java.sql.Date;
   5 import java.sql.DriverManager;
   6 import java.sql.PreparedStatement;
   7 import java.sql.ResultSet;
   8 import java.sql.Statement;
   9 import java.sql.Time;
  10 import java.sql.Timestamp;
  11 /**
  12  *
  13  * @author bijaya
  14  */
  15 public class TestScript6 {
  16   public static void main(String[] arg)
  17     {
  18         try
  19         {
  20             Class.forName("csql.jdbc.JdbcSqlDriver");
  21             Connection con = DriverManager.getConnection("jdbc:csql://localhost:5678", "root", "manager");
  22             Statement cStmt=con.createStatement();
  23             cStmt.execute("CREATE TABLE T1 (f1 integer, f2 smallint, f3 tinyint, f4 bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
  24             con.commit();
  25             PreparedStatement stmt=null,selStmt=null;
  26             stmt=con.prepareStatement("INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?);");
  27             int ret=0;
  28             for(int i=0;i<10;i++)
  29             {
  30                 stmt.setInt(1,i);
  31                 stmt.setShort(2,(short)(i+1));
  32                 stmt.setByte(3,(byte)(i+2));
  33                 stmt.setLong(4,(long)i);
  34                 stmt.setFloat(5,(float)1000+i);
  35                 stmt.setString(6, String.valueOf("Bijaya"+i));
  36                 stmt.setDate(7,Date.valueOf("2008-03-21"));
  37                 stmt.setTime(8,Time.valueOf("18:00:00"));
  38                 stmt.setTimestamp(9,Timestamp.valueOf("2008-03-21 18:00:00"));
  39                 ret=stmt.executeUpdate();
  40                 if(ret!=1) break;
  41             }
  42             stmt.close();
  43             con.commit();
  44             selStmt=con.prepareStatement("SELECT * FROM T1 ;");
  45             ResultSet rs=null;
  46             rs=selStmt.executeQuery();
  47             while(rs.next())
  48             {
  49                 System.out.println("Tuple value is " + rs.getInt("f1") + " "+
  50                                                         rs.getShort("f2") + " "+
  51                                                         rs.getByte("f3") + " "+
  52                                                         rs.getLong("f4") + " "+
  53                                                         rs.getFloat("f5") + " "+
  54                                                         rs.getString("f6") + " "+
  55                                                         rs.getDate("f7") + " "+
  56                                                         rs.getTime("f7") + " "+
  57                                                         rs.getTimestamp("f9") + " "
  58                                                         );
  59
  60             }
  61             rs.close();
  62             System.out.println("");
  63             selStmt=con.prepareStatement("SELECT * FROM T1 ;");
  64             rs=null;
  65             selStmt.setInt(1,2);
  66             selStmt.setShort(2,(short)3);
  67             selStmt.setByte(3,(byte)4);
  68             rs=selStmt.executeQuery();
  69             while(rs.next())
  70             {
  71                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  72                                                         rs.getShort(2) + " "+
  73                                                         rs.getByte(3) + " "+
  74                                                         rs.getLong(4) + " "+
  75                                                         rs.getFloat(5) + " "+
  76                                                         rs.getString(6) + " "+
  77                                                         rs.getDate(7) + " "+
  78                                                         rs.getTime(8) + " "+
  79                                                         rs.getTimestamp(9) + " "
  80                                                         );
  81
  82             }
  83             rs.close();
  84             con.commit();
  85             cStmt.executeUpdate("Drop table T1;");
  86             con.close();
  87        }catch(Exception e) {
  88             System.out.println("Exception in Test: "+e);
  89             e.printStackTrace();
  90         }
  91     }
  92
  93 }