HSQLDB example source code file (TestSubselect.java)

This example HSQLDB source code file (TestSubselect.java) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

connection, connection, exception, jdbc, programmer, resultset, sql, sqlexception, sqlexception, statement, statement, string, string, testcase, testsubselect

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);
    }
}