Source Code Cross Referenced for TestSubselect.java in  » Database-DBMS » hsql » org » hsqldb » test » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Database DBMS » hsql » org.hsqldb.test 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


001:        /* Copyright (c) 2001-2005, The HSQL Development Group
002:         * All rights reserved.
003:         *
004:         * Redistribution and use in source and binary forms, with or without
005:         * modification, are permitted provided that the following conditions are met:
006:         *
007:         * Redistributions of source code must retain the above copyright notice, this
008:         * list of conditions and the following disclaimer.
009:         *
010:         * Redistributions in binary form must reproduce the above copyright notice,
011:         * this list of conditions and the following disclaimer in the documentation
012:         * and/or other materials provided with the distribution.
013:         *
014:         * Neither the name of the HSQL Development Group nor the names of its
015:         * contributors may be used to endorse or promote products derived from this
016:         * software without specific prior written permission.
017:         *
018:         * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019:         * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020:         * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021:         * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022:         * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023:         * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024:         * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025:         * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026:         * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027:         * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028:         * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029:         */
030:
031:        package org.hsqldb.test;
032:
033:        import java.io.IOException;
034:        import java.sql.Connection;
035:        import java.sql.DriverManager;
036:        import java.sql.ResultSet;
037:        import java.sql.SQLException;
038:        import java.sql.Statement;
039:
040:        import junit.framework.TestCase;
041:
042:        /**
043:         * Test cases for HSQL subselects.
044:         *
045:         * @author David Moles Apr 30, 2002
046:         */
047:
048:        // fredt@users - modified to remove dependecy on DBUnit
049:        public class TestSubselect extends TestCase {
050:
051:            //------------------------------------------------------------
052:            // Class variables
053:            //------------------------------------------------------------
054:            private static final String databaseDriver = "org.hsqldb.jdbcDriver";
055:            private static final String databaseURL = "jdbc:hsqldb:/hsql/test/subselect";
056:            private static final String databaseUser = "sa";
057:            private static final String databasePassword = "";
058:
059:            //------------------------------------------------------------
060:            // Instance variables
061:            //------------------------------------------------------------
062:            private Connection jdbcConnection;
063:
064:            //------------------------------------------------------------
065:            // Constructors
066:            //------------------------------------------------------------
067:
068:            /**
069:             * Constructs a new SubselectTest.
070:             */
071:            public TestSubselect(String s) {
072:                super (s);
073:            }
074:
075:            //------------------------------------------------------------
076:            // Class methods
077:            //------------------------------------------------------------
078:            protected static Connection getJDBCConnection() throws SQLException {
079:                return DriverManager.getConnection(databaseURL, databaseUser,
080:                        databasePassword);
081:            }
082:
083:            protected void setUp() throws Exception {
084:
085:                TestSelf.deleteDatabase("/hsql/test/subselect");
086:                Class.forName(databaseDriver);
087:
088:                jdbcConnection = getJDBCConnection();
089:
090:                createDataset();
091:            }
092:
093:            protected void tearDown() throws Exception {
094:
095:                super .tearDown();
096:                jdbcConnection.close();
097:
098:                jdbcConnection = null;
099:            }
100:
101:            void createDataset() throws SQLException {
102:
103:                Statement statement = jdbcConnection.createStatement();
104:
105:                statement.execute("drop table colors if exists; "
106:                        + "drop table sizes if exists; "
107:                        + "drop table fruits if exists; "
108:                        + "drop table trees if exists; ");
109:                statement
110:                        .execute("create table colors(id int, val char); "
111:                                + "insert into colors values(1,'red'); "
112:                                + "insert into colors values(2,'green'); "
113:                                + "insert into colors values(3,'orange'); "
114:                                + "insert into colors values(4,'indigo'); "
115:                                + "create table sizes(id int, val char); "
116:                                + "insert into sizes values(1,'small'); "
117:                                + "insert into sizes values(2,'medium'); "
118:                                + "insert into sizes values(3,'large'); "
119:                                + "insert into sizes values(4,'odd'); "
120:                                + "create table fruits(id int, name char, color_id int); "
121:                                + "insert into fruits values(1, 'golden delicious',2); "
122:                                + "insert into fruits values(2, 'macintosh',1); "
123:                                + "insert into fruits values(3, 'red delicious',1); "
124:                                + "insert into fruits values(4, 'granny smith',2); "
125:                                + "insert into fruits values(5, 'tangerine',4); "
126:                                + "create table trees(id int, name char, fruit_id int, size_id int); "
127:                                + "insert into trees values(1, 'small golden delicious tree',1,1); "
128:                                + "insert into trees values(2, 'large macintosh tree',2,3); "
129:                                + "insert into trees values(3, 'large red delicious tree',3,3); "
130:                                + "insert into trees values(4, 'small red delicious tree',3,1); "
131:                                + "insert into trees values(5, 'medium granny smith tree',4,2); ");
132:                statement.close();
133:            }
134:
135:            //------------------------------------------------------------
136:            // Helper methods
137:            //------------------------------------------------------------
138:            private static void compareResults(String sql, String[] expected,
139:                    Connection jdbcConnection) throws SQLException {
140:
141:                Statement statement = jdbcConnection.createStatement();
142:                ResultSet results = statement.executeQuery(sql);
143:                int rowCount = 0;
144:
145:                while (results.next()) {
146:                    assertTrue("Statement <" + sql
147:                            + "> returned too many rows.",
148:                            (rowCount < expected.length));
149:                    assertEquals("Statement <" + sql
150:                            + "> returned wrong value.", expected[rowCount],
151:                            results.getString(1));
152:
153:                    rowCount++;
154:                }
155:
156:                assertEquals("Statement <" + sql
157:                        + "> returned wrong number of rows.", expected.length,
158:                        rowCount);
159:            }
160:
161:            //------------------------------------------------------------
162:            // Test methods
163:            //------------------------------------------------------------
164:
165:            /**
166:             * This test is basically a sanity check of the data set.
167:             */
168:            public void testSimpleJoin() throws SQLException {
169:
170:                String sql = "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
171:                        + " from trees, sizes, fruits, colors"
172:                        + " where trees.size_id = sizes.id"
173:                        + " and trees.fruit_id = fruits.id"
174:                        + " and fruits.color_id = colors.id" + " order by 1";
175:                int expectedRows = 5;
176:                String[] expectedTrees = new String[] {
177:                        "small golden delicious tree", "large macintosh tree",
178:                        "large red delicious tree", "small red delicious tree",
179:                        "medium granny smith tree" };
180:                String[] expectedSizes = new String[] { "small", "large",
181:                        "large", "small", "medium" };
182:                String[] expectedFruits = new String[] { "golden delicious",
183:                        "macintosh", "red delicious", "red delicious",
184:                        "granny smith" };
185:                String[] expectedColors = new String[] { "green", "red", "red",
186:                        "red", "green" };
187:                Statement statement = jdbcConnection.createStatement();
188:                ResultSet results = statement.executeQuery(sql);
189:                String[] trees = new String[expectedRows];
190:                String[] fruits = new String[expectedRows];
191:                String[] sizes = new String[expectedRows];
192:                String[] colors = new String[expectedRows];
193:                int rowCount = 0;
194:
195:                while (results.next()) {
196:                    assertTrue("Statement <" + sql
197:                            + "> returned too many rows.",
198:                            (rowCount <= expectedRows));
199:                    assertEquals("Statement <" + sql
200:                            + "> returned rows in wrong order.",
201:                            (1 + rowCount), results.getInt(1));
202:                    assertEquals("Statement <" + sql
203:                            + "> returned wrong value.",
204:                            expectedTrees[rowCount], results.getString(2));
205:                    assertEquals("Statement <" + sql
206:                            + "> returned wrong value.",
207:                            expectedSizes[rowCount], results.getString(3));
208:                    assertEquals("Statement <" + sql
209:                            + "> returned wrong value.",
210:                            expectedFruits[rowCount], results.getString(4));
211:                    assertEquals("Statement <" + sql
212:                            + "> returned wrong value.",
213:                            expectedColors[rowCount], results.getString(5));
214:
215:                    rowCount++;
216:                }
217:
218:                assertEquals("Statement <" + sql
219:                        + "> returned wrong number of rows.", expectedRows,
220:                        rowCount);
221:            }
222:
223:            /**
224:             * Inner select with where clause in outer select having column with same name as where clause in inner select
225:             */
226:            public void testWhereClausesColliding() throws SQLException {
227:
228:                String sql = "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
229:                String[] expected = new String[] { "golden delicious",
230:                        "macintosh" };
231:
232:                compareResults(sql, expected, jdbcConnection);
233:            }
234:
235:            /**
236:             * As above, with table aliases.
237:             */
238:            public void testWhereClausesCollidingWithAliases()
239:                    throws SQLException {
240:
241:                String sql = "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
242:                String[] expected = new String[] { "golden delicious",
243:                        "macintosh" };
244:
245:                compareResults(sql, expected, jdbcConnection);
246:            }
247:
248:            /**
249:             * Inner select with two tables having columns with the same name, one of which is referred to in the
250:             * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
251:             * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
252:             */
253:            public void testHiddenCollision() throws SQLException {
254:
255:                String sql = "select name from fruits where id in (select fruit_id from trees) order by name";
256:                String[] expected = new String[] { "golden delicious",
257:                        "granny smith", "macintosh", "red delicious" };
258:
259:                compareResults(sql, expected, jdbcConnection);
260:            }
261:
262:            /**
263:             * As above, with table aliases.
264:             */
265:            public void testHiddenCollisionWithAliases() throws SQLException {
266:
267:                String sql = "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
268:                String[] expected = new String[] { "golden delicious",
269:                        "granny smith", "macintosh", "red delicious" };
270:
271:                compareResults(sql, expected, jdbcConnection);
272:            }
273:
274:            /**
275:             * Inner select with where clause in outer select having column with same name as select clause in inner select
276:             */
277:            public void testWhereSelectColliding() throws SQLException {
278:
279:                // Yes, this is a nonsensical query
280:                String sql = "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
281:                String[] expected = new String[] { "indigo", "orange" };
282:
283:                compareResults(sql, expected, jdbcConnection);
284:            }
285:
286:            /**
287:             * As above, with aliases.
288:             */
289:            public void testWhereSelectCollidingWithAliases()
290:                    throws SQLException {
291:
292:                // Yes, this is a nonsensical query
293:                String sql = "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";
294:                String[] expected = new String[] { "indigo", "orange" };
295:
296:                compareResults(sql, expected, jdbcConnection);
297:            }
298:
299:            /**
300:             * Inner select involving same table
301:             */
302:            public void testSameTable() throws SQLException {
303:
304:                String sql = "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
305:                String[] expected = new String[] { "large red delicious tree",
306:                        "small red delicious tree" };
307:
308:                compareResults(sql, expected, jdbcConnection);
309:            }
310:
311:            /**
312:             * As above with aliases.
313:             */
314:            public void testSameTableWithAliases() throws SQLException {
315:
316:                String sql = "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";
317:                String[] expected = new String[] { "large red delicious tree",
318:                        "small red delicious tree" };
319:
320:                compareResults(sql, expected, jdbcConnection);
321:            }
322:
323:            /**
324:             *     Inner select involving same table as one of two joined tables in outer select
325:             */
326:            public void testSameTableWithJoin() throws SQLException {
327:
328:                String sql = "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";
329:                String[] expected = new String[] { "large", "small" };
330:
331:                compareResults(sql, expected, jdbcConnection);
332:            }
333:
334:            /**
335:             * Tests two subselects, anded.
336:             */
337:            public void testAndedSubselects() throws SQLException {
338:
339:                String sql = "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";
340:                String[] expected = new String[] { "large macintosh tree",
341:                        "large red delicious tree" };
342:
343:                compareResults(sql, expected, jdbcConnection);
344:            }
345:
346:            /**
347:             * Test nested subselects.
348:             */
349:            public void testNestedSubselects() throws SQLException {
350:
351:                String sql = "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";
352:                String[] expected = new String[] { "large macintosh tree",
353:                        "large red delicious tree", "small red delicious tree" };
354:
355:                compareResults(sql, expected, jdbcConnection);
356:            }
357:
358:            /**
359:             * Inner select with "not in" in outer select where clause.
360:             */
361:            public void testNotIn() throws SQLException {
362:
363:                String sql = "select name from fruits where id not in (select fruit_id from trees) order by name";
364:                String[] expected = new String[] { "tangerine" };
365:
366:                compareResults(sql, expected, jdbcConnection);
367:            }
368:
369:            /**
370:             * Inner select with "not in" in outer select where clause and same table in inner select where clause.
371:             */
372:            public void testNotInSameTableAndColumn() throws SQLException {
373:
374:                String sql = "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
375:                String[] expected = new String[] { "macintosh", "red delicious" };
376:
377:                compareResults(sql, expected, jdbcConnection);
378:            }
379:
380:            /**
381:             * Inner select reusing alias names from outer select, but using them for different tables
382:             */
383:            public void testAliasScope() throws SQLException {
384:
385:                String sql = "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";
386:                String[] expectedSizes = new String[] { "large", "small" };
387:                String[] expectedTrees = new String[] {
388:                        "large red delicious tree", "small red delicious tree" };
389:
390:                assertEquals(
391:                        "Programmer error: expected arrays should be of equal length.",
392:                        expectedSizes.length, expectedTrees.length);
393:
394:                Statement statement = jdbcConnection.createStatement();
395:                ResultSet results = statement.executeQuery(sql);
396:                int rowCount = 0;
397:
398:                while (results.next()) {
399:                    assertTrue("Statement <" + sql
400:                            + "> returned too many rows.",
401:                            (rowCount < expectedSizes.length));
402:                    assertEquals("Statement <" + sql
403:                            + "> returned wrong value.",
404:                            expectedSizes[rowCount], results.getString(1));
405:                    assertEquals("Statement <" + sql
406:                            + "> returned wrong value.",
407:                            expectedTrees[rowCount], results.getString(2));
408:
409:                    rowCount++;
410:                }
411:
412:                assertEquals("Statement <" + sql
413:                        + "> returned wrong number of rows.",
414:                        expectedSizes.length, rowCount);
415:            }
416:
417:            //------------------------------------------------------------
418:            // Main program
419:            //------------------------------------------------------------
420:            public static void main(String[] args) throws IOException {
421:                junit.swingui.TestRunner.run(TestSubselect.class);
422:            }
423:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.