KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > test > TestSubQueriesInPreparedStatements


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 /*
33  * TestSubQueriesInPreparedStatements.java
34  *
35  * Created on July 9, 2003, 4:03 PM
36  */
37 package org.hsqldb.test;
38
39 import java.sql.Connection JavaDoc;
40 import java.sql.Driver JavaDoc;
41 import java.sql.DriverManager JavaDoc;
42 import java.sql.PreparedStatement JavaDoc;
43 import java.sql.ResultSet JavaDoc;
44 import java.sql.Statement JavaDoc;
45
46 /**
47  *
48  * @author boucherb@users
49  */
50 public class TestSubQueriesInPreparedStatements {
51
52     public static void main(String JavaDoc[] args) throws Exception JavaDoc {
53         test();
54     }
55
56     public static void test() throws Exception JavaDoc {
57
58         Connection JavaDoc conn;
59         Statement JavaDoc stmnt;
60         PreparedStatement JavaDoc pstmnt;
61         Driver JavaDoc driver;
62
63         driver =
64             (Driver JavaDoc) Class.forName("org.hsqldb.jdbcDriver").newInstance();
65
66         DriverManager.registerDriver(driver);
67
68         conn = DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", "");
69         stmnt = conn.createStatement();
70         pstmnt = conn.prepareStatement("drop table t if exists");
71
72         boolean result = pstmnt.execute();
73
74         pstmnt = conn.prepareStatement("create table t(i decimal)");
75
76         int updatecount = pstmnt.executeUpdate();
77
78         pstmnt = conn.prepareStatement("insert into t values(?)");
79
80         for (int i = 0; i < 100; i++) {
81             pstmnt.setInt(1, i);
82             pstmnt.executeUpdate();
83         }
84
85         pstmnt = conn.prepareStatement(
86             "select * from (select * from t where i < ?)");
87
88         System.out.println("Expecting: 0..3");
89         pstmnt.setInt(1, 4);
90
91         ResultSet JavaDoc rs = pstmnt.executeQuery();
92
93         while (rs.next()) {
94             System.out.println(rs.getInt(1));
95         }
96
97         System.out.println("Expecting: 0..4");
98         pstmnt.setInt(1, 5);
99
100         rs = pstmnt.executeQuery();
101
102         while (rs.next()) {
103             System.out.println(rs.getInt(1));
104         }
105
106         pstmnt = conn.prepareStatement(
107             "select sum(i) from (select i from t where i between ? and ?)");
108
109         System.out.println("Expecting: 9");
110         pstmnt.setInt(1, 4);
111         pstmnt.setInt(2, 5);
112
113         rs = pstmnt.executeQuery();
114
115         while (rs.next()) {
116             System.out.println(rs.getInt(1));
117         }
118
119         System.out.println("Expecting: 15");
120         pstmnt.setInt(2, 6);
121
122         rs = pstmnt.executeQuery();
123
124         while (rs.next()) {
125             System.out.println(rs.getInt(1));
126         }
127
128         pstmnt = conn.prepareStatement(
129             "select * from (select i as c1 from t where i < ?) a, (select i as c2 from t where i < ?) b");
130
131         System.out.println("Expecting: (0,0)");
132         pstmnt.setInt(1, 1);
133         pstmnt.setInt(2, 1);
134
135         rs = pstmnt.executeQuery();
136
137         while (rs.next()) {
138             System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
139         }
140
141         System.out.println("Expecting: ((0,0), (0,1), (1,0), (1,1)");
142         pstmnt.setInt(1, 2);
143         pstmnt.setInt(2, 2);
144
145         rs = pstmnt.executeQuery();
146
147         while (rs.next()) {
148             System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
149         }
150
151         System.out.println("Expecting: ((0,0) .. (3,3)");
152         pstmnt.setInt(1, 4);
153         pstmnt.setInt(2, 4);
154
155         rs = pstmnt.executeQuery();
156
157         while (rs.next()) {
158             System.out.println("(" + rs.getInt(1) + "," + rs.getInt(2) + ")");
159         }
160     }
161 }
162
Popular Tags