1 //select tuple with WHERE clause having param for some fields in Join(SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=? AND T1.f2>=? AND T2.f3=?)
5 public static void main(String
[] arg
)
9 Class
.forName("csql.jdbc.JdbcSqlDriver");
10 Connection con
=DriverManager
.getConnection("jdbc:csql","root","manager");
11 Statement cStmt
=con
.createStatement();
12 cStmt
.execute("CREATE TABLE T1 (f1 integer, f2 smallint, f3 tinyint, f4 bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
13 cStmt
.execute("CREATE TABLE T2 (f1 integer, f2 smallint, f3 tinyint, f4 bigint, f5 float, f6 char(10), f7 date, f8 time, f9 timestamp);");
14 PreparedStatement stmt
=null,selStmt
=null;
15 stmt
=con
.prepareStatement("INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?);");
20 stmt
.setShort(2,(short)(i
+1));
21 stmt
.setByte(3,(byte)(i
+2));
22 stmt
.setLong(4,(long)i
);
23 stmt
.setFloat(5,(float)1000+i
);
24 stmt
.setString(6, String
.valueOf("Nihar"+i
));
25 stmt
.setDate(7,Date
.valueOf("2008-03-21"));
26 stmt
.setTime(8,Time
.valueOf("18:00:00"));
27 stmt
.setTimestamp(9,Timestamp
.valueOf("2008-03-21 18:00:00"));
28 ret
=stmt
.executeUpdate();
32 System
.out
.println("SELECT * FROM T1;");
33 selStmt
=con
.prepareStatement("SELECT * FROM T1;");
35 rs
=selStmt
.executeQuery();
38 System
.out
.println("Tuple value is " + rs
.getInt(1) + " "+
43 rs
.getString(6) + " "+
46 rs
.getTimestamp(9) + " "
52 stmt
=con
.prepareStatement("INSERT INTO T2 VALUES(?,?,?,?,?,?,?,?,?);");
54 for(int i
=0;i
<10;i
+=2)
57 stmt
.setShort(2,(short)(i
+1));
58 stmt
.setByte(3,(byte)(i
+2));
59 stmt
.setLong(4,(long)i
);
60 stmt
.setFloat(5,(float)1000+i
);
61 stmt
.setString(6, String
.valueOf("Nihar"+i
));
62 stmt
.setDate(7,Date
.valueOf("2008-03-21"));
63 stmt
.setTime(8,Time
.valueOf("18:00:00"));
64 stmt
.setTimestamp(9,Timestamp
.valueOf("2008-03-21 18:00:00"));
65 ret
=stmt
.executeUpdate();
70 System
.out
.println("SELECT * FROM T2;");
71 selStmt
=con
.prepareStatement("SELECT * FROM T2;");
73 rs
=selStmt
.executeQuery();
76 System
.out
.println("Tuple value is " + rs
.getInt(1) + " "+
81 rs
.getString(6) + " "+
84 rs
.getTimestamp(9) + " "
91 System
.out
.println("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2;");
92 selStmt
=con
.prepareStatement("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2");
94 rs
=selStmt
.executeQuery();
95 System
.out
.println("T1.f1\tT2.f1\tT1.f2\tT2.f2\tT1.f3\tT2.f3");
98 System
.out
.println( rs
.getInt(1) + "\t"+
100 rs
.getShort(3) + "\t"+
101 rs
.getShort(4) + "\t"+
102 rs
.getByte(5) + "\t"+
110 System
.out
.println("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=2 AND T1.f2>=4 AND T2.f3=6");
111 selStmt
=con
.prepareStatement("SELECT T1.f1,T2.f1,T1.f2,T2.f2,T1.f3,T2.f3 FROM T1,T2 WHERE T1.f1>=? AND T1.f2>=? AND T2.f3=?");
114 selStmt
.setShort(2,(short)4);
115 selStmt
.setByte(3,(byte)6);
116 rs
=selStmt
.executeQuery();
117 System
.out
.println("T1.f1\tT2.f1\tT1.f2\tT2.f2\tT1.f3\tT2.f3");
120 System
.out
.println( rs
.getInt(1) + "\t"+
122 rs
.getShort(3) + "\t"+
123 rs
.getShort(4) + "\t"+
124 rs
.getByte(5) + "\t"+
132 cStmt
.executeUpdate("Drop table T1;");
133 cStmt
.executeUpdate("Drop table T2;");
135 }catch(Exception e
) {
136 System
.out
.println("Exception in Test: "+e
);