test/jdbc/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","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             selStmt.setInt(1,2);
  47             selStmt.setShort(2,(short)3);
  48             selStmt.setByte(3,(byte)4);
  49             rs=selStmt.executeQuery();
  50             while(rs.next())
  51             {
  52                 System.out.println("Tuple value is " + rs.getInt("f1") + " "+
  53                                                         rs.getShort("f2") + " "+
  54                                                         rs.getByte("f3") + " "+
  55                                                         rs.getLong("f4") + " "+
  56                                                         rs.getFloat("f5") + " "+
  57                                                         rs.getString("f6") + " "+
  58                                                         rs.getDate("f7") + " "+
  59                                                         rs.getTime("f7") + " "+
  60                                                         rs.getTimestamp("f9") + " "
  61                                                         );
  62
  63             }
  64             rs.close();
  65             System.out.println("");
  66             selStmt=con.prepareStatement("SELECT * FROM T1 ;");
  67             rs=null;
  68             selStmt.setInt(1,2);
  69             selStmt.setShort(2,(short)3);
  70             selStmt.setByte(3,(byte)4);
  71             rs=selStmt.executeQuery();
  72             while(rs.next())
  73             {
  74                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  75                                                         rs.getShort(2) + " "+
  76                                                         rs.getByte(3) + " "+
  77                                                         rs.getLong(4) + " "+
  78                                                         rs.getFloat(5) + " "+
  79                                                         rs.getString(6) + " "+
  80                                                         rs.getDate(7) + " "+
  81                                                         rs.getTime(8) + " "+
  82                                                         rs.getTimestamp(9) + " "
  83                                                         );
  84
  85             }
  86             rs.close();
  87             con.commit();
  88             cStmt.executeUpdate("Drop table T1;");
  89             con.close();
  90        }catch(Exception e) {
  91             System.out.println("Exception in Test: "+e);
  92             e.printStackTrace();
  93         }
  94     }
  95
  96 }