FileDocCategorySizeDatePackage
InsertFunctionalityTest.javaAPI DocAndroid 1.5 API11379Wed May 06 22:41:06 BST 2009tests.java.sql

InsertFunctionalityTest

public class InsertFunctionalityTest extends TestCase

Fields Summary
private static Connection
conn
private static Statement
statement
Constructors Summary
Methods Summary
public voidcreateTestTables()

        try {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet userTab = meta.getTables(null, null, null, null);

            while (userTab.next()) {
                String tableName = userTab.getString("TABLE_NAME");
                if (tableName.equals(DatabaseCreator.PARENT_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_PARENT);
                } else if (tableName
                        .equals(DatabaseCreator.FKCASCADE_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_FKCASCADE);
                } else if (tableName
                        .equals(DatabaseCreator.FKSTRICT_TABLE)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_FKSTRICT);
                } else if (tableName
                        .equals(DatabaseCreator.SIMPLE_TABLE1)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_SIMPLE1);
                } else if (tableName
                        .equals(DatabaseCreator.SIMPLE_TABLE2)) {
                    statement
                            .execute(DatabaseCreator.DROP_TABLE_SIMPLE2);
                } else if (tableName
                        .equals(DatabaseCreator.TEST_TABLE5)) {
                    statement.execute(DatabaseCreator.DROP_TABLE5);
                }
            }
            userTab.close();
            statement.execute(DatabaseCreator.CREATE_TABLE_PARENT);
            statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT);
            statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE);
            statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE2);
            statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE1);
            statement.execute(DatabaseCreator.CREATE_TABLE5);
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
public voiddeleteTestTables()

        try {
            statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE);
            statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT);
            statement.execute(DatabaseCreator.DROP_TABLE_PARENT);
            statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE2);
            statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE1);
            statement.execute(DatabaseCreator.DROP_TABLE5);
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
public voidsetUp()


         
        super.setUp();
        Support_SQL.loadDriver();
        conn = Support_SQL.getConnection();
        statement = conn.createStatement();
        createTestTables();
public voidtearDown()

        deleteTestTables();
        statement.close();
        conn.close();
        super.tearDown();
public voidtestInsert1()

tests
InsertFunctionalityTest#testInsert1(). Attempts to insert row into table with integrity checking

        DatabaseCreator.fillParentTable(conn);
        DatabaseCreator.fillFKStrictTable(conn);
        DatabaseCreator.fillFKCascadeTable(conn);
        statement.execute("INSERT INTO " + DatabaseCreator.FKSTRICT_TABLE
                + " VALUES(4, 1, 'testInsert')");
        statement.execute("INSERT INTO " + DatabaseCreator.FKCASCADE_TABLE
                + " VALUES(4, 1, 'testInsert')");
public voidtestInsert2()

tests
InsertFunctionalityTest#testInsert2(). Attempts to insert row into table with integrity checking when row has incorrect foreign key value - expecting SQLException

        DatabaseCreator.fillParentTable(conn);
        DatabaseCreator.fillFKStrictTable(conn);
        DatabaseCreator.fillFKCascadeTable(conn);
        try {
            statement.execute("INSERT INTO " + DatabaseCreator.FKSTRICT_TABLE
                    + " VALUES(4, 4, 'testInsert')");
           // TODO Foreign key functionality isn't supported 
           // fail("expecting SQLException");
        } catch (SQLException ex) {
            // expected
        }
        try {
            statement.execute("INSERT INTO " + DatabaseCreator.FKCASCADE_TABLE
                    + " VALUES(4, 4, 'testInsert')");
           // TODO Foreign key functionality isn't supported 
           // fail("expecting SQLException");
        } catch (SQLException ex) {
            // expected
        }
public voidtestInsert3()

tests
InsertFunctionalityTest#testInsert3(). Tests INSERT ... SELECT functionality

        DatabaseCreator.fillParentTable(conn);
        DatabaseCreator.fillFKStrictTable(conn);
        statement.execute("INSERT INTO " + DatabaseCreator.TEST_TABLE5
                + " SELECT id AS testId, value AS testValue " + "FROM "
                + DatabaseCreator.FKSTRICT_TABLE + " WHERE name_id = 1");
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.TEST_TABLE5);
        r.next();
        assertEquals("Should be 2 rows", 2, r.getInt(1));
        r.close();
public voidtestInsert4()

tests
InsertFunctionalityTest#testInsert4(). Tests INSERT ... SELECT with expressions in SELECT query

        DatabaseCreator.fillSimpleTable1(conn);
        statement.execute("INSERT INTO " + DatabaseCreator.SIMPLE_TABLE2
                + " SELECT id, speed*10 AS speed, size-1 AS size FROM "
                + DatabaseCreator.SIMPLE_TABLE1);
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.SIMPLE_TABLE2 + " AS a JOIN "
                + DatabaseCreator.SIMPLE_TABLE1
                + " AS b ON a.speed = 10*b.speed AND a.size = b.size-1");
        r.next();
        assertEquals("Should be 2 rows", 2, r.getInt(1));
        r.close();
public voidtestInsert5()

tests
InsertFunctionalityTest#testInsert5(). Inserts multiple rows using UNION ALL

        statement.execute("INSERT INTO " + DatabaseCreator.TEST_TABLE5
                + " SELECT 1 as testId, 2 as testValue "
                + "UNION SELECT 2 as testId, 3 as testValue "
                + "UNION SELECT 3 as testId, 4 as testValue");
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.TEST_TABLE5);
        r.next();
        assertEquals("Should be 3 rows", 3, r.getInt(1));
        r.close();
public voidtestInsertPrepared()

tests
InsertFunctionalityTest#testInsert6(). Tests INSERT with PreparedStatement

        PreparedStatement stat = conn.prepareStatement("INSERT INTO "
                + DatabaseCreator.TEST_TABLE5 + " VALUES(?, ?)");
        stat.setInt(1, 1);
        stat.setString(2, "1");
        stat.execute();
        stat.setInt(1, 2);
        stat.setString(2, "3");
        stat.execute();
        ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM "
                + DatabaseCreator.TEST_TABLE5
                + " WHERE (testId = 1 AND testValue = '1') "
                + "OR (testId = 2 AND testValue = '3')");
        r.next();
        assertEquals("Incorrect number of records", 2, r.getInt(1));
        r.close();
        stat.close();