FileDocCategorySizeDatePackage
UpdateFunctionalityTest.javaAPI DocAndroid 1.5 API18464Wed May 06 22:41:06 BST 2009tests.java.sql

UpdateFunctionalityTest

public class UpdateFunctionalityTest extends TestCase

Fields Summary
private static final int
numberOfRecords
private static Connection
conn
private static Statement
statement
Constructors Summary
Methods Summary
protected 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.TEST_TABLE1)) {
                    statement.execute(DatabaseCreator.DROP_TABLE1);
                } else if (tableName
                        .equals(DatabaseCreator.TEST_TABLE2)) {
                    statement.execute(DatabaseCreator.DROP_TABLE2);
                } else if (tableName
                        .equals(DatabaseCreator.TEST_TABLE3)) {
                    statement.execute(DatabaseCreator.DROP_TABLE3);
                }
            }
            userTab.close();

            statement.execute(DatabaseCreator.CREATE_TABLE3);
            statement.execute(DatabaseCreator.CREATE_TABLE2);
            statement.execute(DatabaseCreator.CREATE_TABLE1);

        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
protected voiddeleteTestTables()

        try {
            statement.execute(DatabaseCreator.DROP_TABLE1);
            statement.execute(DatabaseCreator.DROP_TABLE2);
            statement.execute(DatabaseCreator.DROP_TABLE3);
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
public voidsetUp()


         
        super.setUp();
        Support_SQL.loadDriver();
        try {
            conn = Support_SQL.getConnection();
            statement = conn.createStatement();
            createTestTables();
        } catch (SQLException e) {
            fail("Unexpected SQLException " + e.toString());
        }
        DatabaseCreator.fillTestTable1(conn, numberOfRecords);
        DatabaseCreator.fillTestTable2(conn, numberOfRecords);
public voidtearDown()

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

tests
UpdateFunctionalityTest#testUpdate1(). Updates all values in one column in the table

        String newValue = "newValue";
        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field1='" + newValue + "'";
        try {
            int num = statement.executeUpdate(updateQuery);
            assertEquals("Not all records in the database were updated",
                    numberOfRecords, num);
            String selectQuery = "SELECT field1 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
                assertEquals("The field field1 was not updated", newValue,
                        result.getString("field1"));
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate2()

tests
UpdateFunctionalityTest#testUpdate2(). Updates values in one column in the table using where condition in update command

        String newValue = "newValue";
        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field1='" + newValue + "' WHERE (id > 2) and (id < 10)";
        try {
            int num = statement.executeUpdate(updateQuery);
            int expectedUpdated = 7;
            assertEquals("Not all records in the database were updated",
                    expectedUpdated, num);
            String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
                int id = result.getInt("id");
                String field1 = result.getString("field1");
                if ((id > 2) && (id < 10)) {
                    assertEquals("The field field1 was not updated", newValue,
                            field1);
                } else {
                    assertEquals("The field field1 was not updated",
                            DatabaseCreator.defaultString + id, field1);
                }
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate3()

tests
UpdateFunctionalityTest#testUpdate3(). Updates values in a several columns in the table

        int newValue1 = -1;
        int newValue2 = -2;
        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2=" + newValue1 + ", field3=" + newValue2;
        try {
            int num = statement.executeUpdate(updateQuery);
            assertEquals("Not all records in the database were updated",
                    numberOfRecords, num);
            String selectQuery = "SELECT field2, field3 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
           // TODO getBigDecimal is not supported
//                assertEquals("The field field2 was not updated", newValue1,
//                        result.getBigDecimal("field2").intValue());
//                assertEquals("The field field3 was not updated", newValue2,
//                        result.getBigDecimal("field3").intValue());
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate4()

tests
UpdateFunctionalityTest#testUpdate4(). Updates values in a several columns in the table using where condition in update command

        int newValue1 = -1;
        int newValue2 = -2;
        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2=" + newValue1 + ", field3=" + newValue2
                + " WHERE id > 10";
        try {
            int num = statement.executeUpdate(updateQuery);
            int expectedUpdated = 9;
            assertEquals("Not all records in the database were updated",
                    expectedUpdated, num);
            String selectQuery = "SELECT id, field2, field3 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
                int id = result.getInt("id");
                // TODO getBigDecimal is not supported
//                int value2 = result.getBigDecimal("field2").intValue();
//                int value3 = result.getBigDecimal("field3").intValue();
//                if (id > expectedUpdated + 1) {
//                    assertEquals("The field field2 was not updated", newValue1,
//                            value2);
//                    assertEquals("The field field3 was not updated", newValue2,
//                            value3);
//                } else {
//                    assertEquals("The field field2 was not updated", id, value2);
//                    assertEquals("The field field3 was not updated", id, value3);
//                }
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate5()

tests
UpdateFunctionalityTest#testUpdate5(). Updates values in one columns in the table using condition

        int factor = 3;
        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2=field2 *" + factor;
        try {
            String selectQuery = "SELECT field2 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            HashSet<BigDecimal> values = new HashSet<BigDecimal>();
            // TODO getBigDecimal is not supported
//            while (result.next()) {
//                values.add(BigDecimal.valueOf(result.getBigDecimal("field2")
//                        .intValue()
//                        * factor));
//            }

            int num = statement.executeUpdate(updateQuery);
            assertEquals("Not all records in the database were updated",
                    numberOfRecords, num);
            result = statement.executeQuery(selectQuery);
            // TODO getBigDecimal is not supported
//            while (result.next()) {
//                BigDecimal value = result.getBigDecimal("field2");
//                assertTrue("Wrong value of field2 after update"
//                        + value.intValue(), values.remove(value));
//            }
            assertTrue("Not all records were updated", values.isEmpty());

            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate6()

tests
UpdateFunctionalityTest#testUpdate6(). Sets value of field2 to default

        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2='1'";
        try {

            int num = statement.executeUpdate(updateQuery);
            assertEquals("Not all records in the database were updated",
                    numberOfRecords, num);
            String selectQuery = "SELECT field2 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            // TODO getBigDecimal is not supported
//            while (result.next()) {
//                assertEquals("value of field2 should be default ",
//                        DatabaseCreator.defaultInt, result.getBigDecimal(
//                                "field2").intValue());
//            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate7()

tests
UpdateFunctionalityTest#testUpdate7(). Updates records in the table using subquery in update command

        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2='1' WHERE id < ( SELECT COUNT(*) FROM "
                + DatabaseCreator.TEST_TABLE2 + " WHERE finteger > 15)";
        try {
            int num = statement.executeUpdate(updateQuery);
            int expectedUpdated = 4;
            assertEquals("Not all records in the database were updated",
                    expectedUpdated, num);
            String selectQuery = "SELECT id, field2 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
           // TODO getBigDecimal is not supported
//                int value = result.getBigDecimal("field2").intValue();
//                int id = result.getInt("id");
//                if (id < expectedUpdated) {
//                    assertEquals("value of field2 should be default ",
//                            DatabaseCreator.defaultInt, value);
//                } else {
//                    assertEquals("wrong value of field2", id, value);
//                }
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }
public voidtestUpdate8()

tests
UpdateFunctionalityTest#testUpdate8(). Sets value of field2 to NULL

        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
                + " SET field2=NULL";
        try {

            int num = statement.executeUpdate(updateQuery);
            assertEquals("Not all records in the database were updated",
                    numberOfRecords, num);
            String selectQuery = "SELECT field2 FROM "
                    + DatabaseCreator.TEST_TABLE1;
            ResultSet result = statement.executeQuery(selectQuery);
            while (result.next()) {
                assertNull("value of field2 should be NULL", result
                        .getObject("field2"));
            }
            result.close();
        } catch (SQLException e) {
            fail("Unexpected exception" + e.getMessage());
        }