|
1
30
31
32 package org.hsqldb.test;
33
34 import java.io.IOException  ;
35 import java.sql.Connection ;
36 import java.sql.DriverManager ;
37 import java.sql.ResultSet ;
38 import java.sql.SQLException ;
39 import java.sql.Statement ;
40
41 import junit.framework.TestCase;
42
43
48
49 public class TestSubselect extends TestCase {
51
52 private static final String databaseDriver = "org.hsqldb.jdbcDriver";
56 private static final String databaseURL =
57 "jdbc:hsqldb:/hsql/test/subselect";
58 private static final String databaseUser = "sa";
59 private static final String databasePassword = "";
60
61 private Connection jdbcConnection;
65
66
70
73 public TestSubselect(String s) {
74 super(s);
75 }
76
77 protected static Connection getJDBCConnection() throws SQLException {
81 return DriverManager.getConnection(databaseURL, databaseUser,
82 databasePassword);
83 }
84
85 protected void setUp() throws Exception {
86
87 TestSelf.deleteDatabase("/hsql/test/subselect");
88 Class.forName(databaseDriver);
89
90 jdbcConnection = getJDBCConnection();
91
92 createDataset();
93 }
94
95 protected void tearDown() throws Exception {
96
97 super.tearDown();
98 jdbcConnection.close();
99
100 jdbcConnection = null;
101 }
102
103 void createDataset() throws SQLException {
104
105 Statement statement = jdbcConnection.createStatement();
106
107 statement.execute("drop table colors if exists; "
108 + "drop table sizes if exists; "
109 + "drop table fruits if exists; "
110 + "drop table trees if exists; ");
111 statement.execute(
112 "create table colors(id int, val char); "
113 + "insert into colors values(1,'red'); "
114 + "insert into colors values(2,'green'); "
115 + "insert into colors values(3,'orange'); "
116 + "insert into colors values(4,'indigo'); "
117 + "create table sizes(id int, val char); "
118 + "insert into sizes values(1,'small'); "
119 + "insert into sizes values(2,'medium'); "
120 + "insert into sizes values(3,'large'); "
121 + "insert into sizes values(4,'odd'); "
122 + "create table fruits(id int, name char, color_id int); "
123 + "insert into fruits values(1, 'golden delicious',2); "
124 + "insert into fruits values(2, 'macintosh',1); "
125 + "insert into fruits values(3, 'red delicious',1); "
126 + "insert into fruits values(4, 'granny smith',2); "
127 + "insert into fruits values(5, 'tangerine',4); "
128 + "create table trees(id int, name char, fruit_id int, size_id int); "
129 + "insert into trees values(1, 'small golden delicious tree',1,1); "
130 + "insert into trees values(2, 'large macintosh tree',2,3); "
131 + "insert into trees values(3, 'large red delicious tree',3,3); "
132 + "insert into trees values(4, 'small red delicious tree',3,1); "
133 + "insert into trees values(5, 'medium granny smith tree',4,2); ");
134 statement.close();
135 }
136
137 private static void compareResults(String sql, String [] expected,
141 Connection jdbcConnection)
142 throws SQLException {
143
144 Statement statement = jdbcConnection.createStatement();
145 ResultSet results = statement.executeQuery(sql);
146 int rowCount = 0;
147
148 while (results.next()) {
149 assertTrue("Statement <" + sql + "> returned too many rows.",
150 (rowCount < expected.length));
151 assertEquals("Statement <" + sql + "> returned wrong value.",
152 expected[rowCount], results.getString(1));
153
154 rowCount++;
155 }
156
157 assertEquals("Statement <" + sql
158 + "> returned wrong number of rows.", expected.length,
159 rowCount);
160 }
161
162
166
169 public void testSimpleJoin() throws SQLException {
170
171 String sql =
172 "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
173 + " from trees, sizes, fruits, colors"
174 + " where trees.size_id = sizes.id"
175 + " and trees.fruit_id = fruits.id"
176 + " and fruits.color_id = colors.id" + " order by 1";
177 int expectedRows = 5;
178 String [] expectedTrees = new String [] {
179 "small golden delicious tree", "large macintosh tree",
180 "large red delicious tree", "small red delicious tree",
181 "medium granny smith tree"
182 };
183 String [] expectedSizes = new String [] {
184 "small", "large", "large", "small", "medium"
185 };
186 String [] expectedFruits = new String [] {
187 "golden delicious", "macintosh", "red delicious", "red delicious",
188 "granny smith"
189 };
190 String [] expectedColors = new String [] {
191 "green", "red", "red", "red", "green"
192 };
193 Statement statement = jdbcConnection.createStatement();
194 ResultSet results = statement.executeQuery(sql);
195 String [] trees = new String [expectedRows];
196 String [] fruits = new String [expectedRows];
197 String [] sizes = new String [expectedRows];
198 String [] colors = new String [expectedRows];
199 int rowCount = 0;
200
201 while (results.next()) {
202 assertTrue("Statement <" + sql + "> returned too many rows.",
203 (rowCount <= expectedRows));
204 assertEquals("Statement <" + sql
205 + "> returned rows in wrong order.", (1 + rowCount),
206 results.getInt(1));
207 assertEquals("Statement <" + sql + "> returned wrong value.",
208 expectedTrees[rowCount], results.getString(2));
209 assertEquals("Statement <" + sql + "> returned wrong value.",
210 expectedSizes[rowCount], results.getString(3));
211 assertEquals("Statement <" + sql + "> returned wrong value.",
212 expectedFruits[rowCount], results.getString(4));
213 assertEquals("Statement <" + sql + "> returned wrong value.",
214 expectedColors[rowCount], results.getString(5));
215
216 rowCount++;
217 }
218
219 assertEquals("Statement <" + sql
220 + "> returned wrong number of rows.", expectedRows,
221 rowCount);
222 }
223
224
227 public void testWhereClausesColliding() throws SQLException {
228
229 String sql =
230 "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
231 String [] expected = new String [] {
232 "golden delicious", "macintosh"
233 };
234
235 compareResults(sql, expected, jdbcConnection);
236 }
237
238
241 public void testWhereClausesCollidingWithAliases() throws SQLException {
242
243 String sql =
244 "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
245 String [] expected = new String [] {
246 "golden delicious", "macintosh"
247 };
248
249 compareResults(sql, expected, jdbcConnection);
250 }
251
252
257 public void testHiddenCollision() throws SQLException {
258
259 String sql =
260 "select name from fruits where id in (select fruit_id from trees) order by name";
261 String [] expected = new String [] {
262 "golden delicious", "granny smith", "macintosh", "red delicious"
263 };
264
265 compareResults(sql, expected, jdbcConnection);
266 }
267
268
271 public void testHiddenCollisionWithAliases() throws SQLException {
272
273 String sql =
274 "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
275 String [] expected = new String [] {
276 "golden delicious", "granny smith", "macintosh", "red delicious"
277 };
278
279 compareResults(sql, expected, jdbcConnection);
280 }
281
282
285 public void testWhereSelectColliding() throws SQLException {
286
287 String sql =
289 "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
290 String [] expected = new String [] {
291 "indigo", "orange"
292 };
293
294 compareResults(sql, expected, jdbcConnection);
295 }
296
297
300 public void testWhereSelectCollidingWithAliases() throws SQLException {
301
302 String sql =
304 "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";
305 String [] expected = new String [] {
306 "indigo", "orange"
307 };
308
309 compareResults(sql, expected, jdbcConnection);
310 }
311
312
315 public void testSameTable() throws SQLException {
316
317 String sql =
318 "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
319 String [] expected = new String [] {
320 "large red delicious tree", "small red delicious tree"
321 };
322
323 compareResults(sql, expected, jdbcConnection);
324 }
325
326
329 public void testSameTableWithAliases() throws SQLException {
330
331 String sql =
332 "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";
333 String [] expected = new String [] {
334 "large red delicious tree", "small red delicious tree"
335 };
336
337 compareResults(sql, expected, jdbcConnection);
338 }
339
340
343 public void testSameTableWithJoin() throws SQLException {
344
345 String sql =
346 "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";
347 String [] expected = new String [] {
348 "large", "small"
349 };
350
351 compareResults(sql, expected, jdbcConnection);
352 }
353
354
357 public void testAndedSubselects() throws SQLException {
358
359 String sql =
360 "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";
361 String [] expected = new String [] {
362 "large macintosh tree", "large red delicious tree"
363 };
364
365 compareResults(sql, expected, jdbcConnection);
366 }
367
368
371 public void testNestedSubselects() throws SQLException {
372
373 String sql =
374 "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";
375 String [] expected = new String [] {
376 "large macintosh tree", "large red delicious tree",
377 "small red delicious tree"
378 };
379
380 compareResults(sql, expected, jdbcConnection);
381 }
382
383
386 public void testNotIn() throws SQLException {
387
388 String sql =
389 "select name from fruits where id not in (select fruit_id from trees) order by name";
390 String [] expected = new String []{ "tangerine" };
391
392 compareResults(sql, expected, jdbcConnection);
393 }
394
395
398 public void testNotInSameTableAndColumn() throws SQLException {
399
400 String sql =
401 "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
402 String [] expected = new String [] {
403 "macintosh", "red delicious"
404 };
405
406 compareResults(sql, expected, jdbcConnection);
407 }
408
409
412 public void testAliasScope() throws SQLException {
413
414 String sql =
415 "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";
416 String [] expectedSizes = new String [] {
417 "large", "small"
418 };
419 String [] expectedTrees = new String [] {
420 "large red delicious tree", "small red delicious tree"
421 };
422
423 assertEquals(
424 "Programmer error: expected arrays should be of equal length.",
425 expectedSizes.length, expectedTrees.length);
426
427 Statement statement = jdbcConnection.createStatement();
428 ResultSet results = statement.executeQuery(sql);
429 int rowCount = 0;
430
431 while (results.next()) {
432 assertTrue("Statement <" + sql + "> returned too many rows.",
433 (rowCount < expectedSizes.length));
434 assertEquals("Statement <" + sql + "> returned wrong value.",
435 expectedSizes[rowCount], results.getString(1));
436 assertEquals("Statement <" + sql + "> returned wrong value.",
437 expectedTrees[rowCount], results.getString(2));
438
439 rowCount++;
440 }
441
442 assertEquals(
443 "Statement <" + sql + "> returned wrong number of rows.",
444 expectedSizes.length, rowCount);
445 }
446
447 public static void main(String [] args) throws IOException {
451 junit.swingui.TestRunner.run(TestSubselect.class);
452 }
453 }
454 |
Popular Tags |
Java API By Example, From Geeks To Geeks.