|
HSQLDB example source code file (TestSubselect.java)
The HSQLDB TestSubselect.java source code/* Copyright (c) 2001-2008, The HSQL Development Group * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * Redistributions of source code must retain the above copyright notice, this * list of conditions and the following disclaimer. * * Redistributions in binary form must reproduce the above copyright notice, * this list of conditions and the following disclaimer in the documentation * and/or other materials provided with the distribution. * * Neither the name of the HSQL Development Group nor the names of its * contributors may be used to endorse or promote products derived from this * software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ package org.hsqldb.test; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.hsqldb.jdbc.jdbcDataSource; import junit.framework.TestCase; /** * Test cases for HSQL subselects. * * @author David Moles Apr 30, 2002 */ // fredt@users - modified to remove dependecy on DBUnit public class TestSubselect extends TestCase { //------------------------------------------------------------ // Class variables //------------------------------------------------------------ private static final String databaseDriver = "org.hsqldb.jdbcDriver"; private static final String databaseURL = "jdbc:hsqldb:/hsql/test/subselect"; private static final String databaseUser = "sa"; private static final String databasePassword = ""; //------------------------------------------------------------ // Instance variables //------------------------------------------------------------ private Connection jdbcConnection; //------------------------------------------------------------ // Constructors //------------------------------------------------------------ /** * Constructs a new SubselectTest. */ public TestSubselect(String s) { super(s); } //------------------------------------------------------------ // Class methods //------------------------------------------------------------ protected static Connection getJDBCConnection() throws SQLException { jdbcDataSource dataSource = new jdbcDataSource(); dataSource.setDatabase(databaseURL); return dataSource.getConnection(databaseUser, databasePassword); } protected void setUp() throws Exception { TestSelf.deleteDatabase("/hsql/test/subselect"); jdbcConnection = getJDBCConnection(); createDataset(); } protected void tearDown() throws Exception { jdbcConnection.close(); jdbcConnection = null; super.tearDown(); } void createDataset() throws SQLException { Statement statement = jdbcConnection.createStatement(); statement.execute("drop table colors if exists; " + "drop table sizes if exists; " + "drop table fruits if exists; " + "drop table trees if exists; "); statement.execute( "create table colors(id int, val char); " + "insert into colors values(1,'red'); " + "insert into colors values(2,'green'); " + "insert into colors values(3,'orange'); " + "insert into colors values(4,'indigo'); " + "create table sizes(id int, val char); " + "insert into sizes values(1,'small'); " + "insert into sizes values(2,'medium'); " + "insert into sizes values(3,'large'); " + "insert into sizes values(4,'odd'); " + "create table fruits(id int, name char, color_id int); " + "insert into fruits values(1, 'golden delicious',2); " + "insert into fruits values(2, 'macintosh',1); " + "insert into fruits values(3, 'red delicious',1); " + "insert into fruits values(4, 'granny smith',2); " + "insert into fruits values(5, 'tangerine',4); " + "create table trees(id int, name char, fruit_id int, size_id int); " + "insert into trees values(1, 'small golden delicious tree',1,1); " + "insert into trees values(2, 'large macintosh tree',2,3); " + "insert into trees values(3, 'large red delicious tree',3,3); " + "insert into trees values(4, 'small red delicious tree',3,1); " + "insert into trees values(5, 'medium granny smith tree',4,2); "); statement.close(); } //------------------------------------------------------------ // Helper methods //------------------------------------------------------------ private static void compareResults(String sql, String[] expected, Connection jdbcConnection) throws SQLException { Statement statement = jdbcConnection.createStatement(); ResultSet results = statement.executeQuery(sql); int rowCount = 0; while (results.next()) { assertTrue("Statement <" + sql + "> returned too many rows.", (rowCount < expected.length)); assertEquals("Statement <" + sql + "> returned wrong value.", expected[rowCount], results.getString(1)); rowCount++; } assertEquals("Statement <" + sql + "> returned wrong number of rows.", expected.length, rowCount); } //------------------------------------------------------------ // Test methods //------------------------------------------------------------ /** * This test is basically a sanity check of the data set. */ public void testSimpleJoin() throws SQLException { String sql = "select trees.id, trees.name, sizes.val, fruits.name, colors.val" + " from trees, sizes, fruits, colors" + " where trees.size_id = sizes.id" + " and trees.fruit_id = fruits.id" + " and fruits.color_id = colors.id" + " order by 1"; int expectedRows = 5; String[] expectedTrees = new String[] { "small golden delicious tree", "large macintosh tree", "large red delicious tree", "small red delicious tree", "medium granny smith tree" }; String[] expectedSizes = new String[] { "small", "large", "large", "small", "medium" }; String[] expectedFruits = new String[] { "golden delicious", "macintosh", "red delicious", "red delicious", "granny smith" }; String[] expectedColors = new String[] { "green", "red", "red", "red", "green" }; Statement statement = jdbcConnection.createStatement(); ResultSet results = statement.executeQuery(sql); String[] trees = new String[expectedRows]; String[] fruits = new String[expectedRows]; String[] sizes = new String[expectedRows]; String[] colors = new String[expectedRows]; int rowCount = 0; while (results.next()) { assertTrue("Statement <" + sql + "> returned too many rows.", (rowCount <= expectedRows)); assertEquals("Statement <" + sql + "> returned rows in wrong order.", (1 + rowCount), results.getInt(1)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedTrees[rowCount], results.getString(2)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedSizes[rowCount], results.getString(3)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedFruits[rowCount], results.getString(4)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedColors[rowCount], results.getString(5)); rowCount++; } assertEquals("Statement <" + sql + "> returned wrong number of rows.", expectedRows, rowCount); } /** * Inner select with where clause in outer select having column with same name as where clause in inner select */ public void testWhereClausesColliding() throws SQLException { String sql = "select name from fruits where id in (select fruit_id from trees where id < 3) order by name"; String[] expected = new String[] { "golden delicious", "macintosh" }; compareResults(sql, expected, jdbcConnection); } /** * As above, with table aliases. */ public void testWhereClausesCollidingWithAliases() throws SQLException { 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"; String[] expected = new String[] { "golden delicious", "macintosh" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select with two tables having columns with the same name, one of which is referred to in the * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column, * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all). */ public void testHiddenCollision() throws SQLException { String sql = "select name from fruits where id in (select fruit_id from trees) order by name"; String[] expected = new String[] { "golden delicious", "granny smith", "macintosh", "red delicious" }; compareResults(sql, expected, jdbcConnection); } /** * As above, with table aliases. */ public void testHiddenCollisionWithAliases() throws SQLException { String sql = "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name"; String[] expected = new String[] { "golden delicious", "granny smith", "macintosh", "red delicious" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select with where clause in outer select having column with same name as select clause in inner select */ public void testWhereSelectColliding() throws SQLException { // Yes, this is a nonsensical query String sql = "select val from colors where id in (select id from trees where fruit_id = 3) order by val"; String[] expected = new String[] { "indigo", "orange" }; compareResults(sql, expected, jdbcConnection); } /** * As above, with aliases. */ public void testWhereSelectCollidingWithAliases() throws SQLException { // Yes, this is a nonsensical query 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"; String[] expected = new String[] { "indigo", "orange" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select involving same table */ public void testSameTable() throws SQLException { String sql = "select name from trees where id in (select id from trees where fruit_id = 3) order by name"; String[] expected = new String[] { "large red delicious tree", "small red delicious tree" }; compareResults(sql, expected, jdbcConnection); } /** * As above with aliases. */ public void testSameTableWithAliases() throws SQLException { 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"; String[] expected = new String[] { "large red delicious tree", "small red delicious tree" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select involving same table as one of two joined tables in outer select */ public void testSameTableWithJoin() throws SQLException { 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"; String[] expected = new String[] { "large", "small" }; compareResults(sql, expected, jdbcConnection); } /** * Tests two subselects, anded. */ public void testAndedSubselects() throws SQLException { 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"; String[] expected = new String[] { "large macintosh tree", "large red delicious tree" }; compareResults(sql, expected, jdbcConnection); } /** * Test nested subselects. */ public void testNestedSubselects() throws SQLException { 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"; String[] expected = new String[] { "large macintosh tree", "large red delicious tree", "small red delicious tree" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select with "not in" in outer select where clause. */ public void testNotIn() throws SQLException { String sql = "select name from fruits where id not in (select fruit_id from trees) order by name"; String[] expected = new String[]{ "tangerine" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select with "not in" in outer select where clause and same table in inner select where clause. */ public void testNotInSameTableAndColumn() throws SQLException { String sql = "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name"; String[] expected = new String[] { "macintosh", "red delicious" }; compareResults(sql, expected, jdbcConnection); } /** * Inner select reusing alias names from outer select, but using them for different tables */ public void testAliasScope() throws SQLException { 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"; String[] expectedSizes = new String[] { "large", "small" }; String[] expectedTrees = new String[] { "large red delicious tree", "small red delicious tree" }; assertEquals( "Programmer error: expected arrays should be of equal length.", expectedSizes.length, expectedTrees.length); Statement statement = jdbcConnection.createStatement(); ResultSet results = statement.executeQuery(sql); int rowCount = 0; while (results.next()) { assertTrue("Statement <" + sql + "> returned too many rows.", (rowCount < expectedSizes.length)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedSizes[rowCount], results.getString(1)); assertEquals("Statement <" + sql + "> returned wrong value.", expectedTrees[rowCount], results.getString(2)); rowCount++; } assertEquals( "Statement <" + sql + "> returned wrong number of rows.", expectedSizes.length, rowCount); } } Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSubselect.java source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2024 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.