/*
* Copyright (C) 2007 The Android Open Source Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package tests.java.sql;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import tests.support.DatabaseCreator;
import tests.support.Support_SQL;
import junit.extensions.TestSetup;
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
public class UpdateFunctionalityTest extends TestCase {
private static final int numberOfRecords = 20;
private static Connection conn;
private static Statement statement;
public void setUp() throws Exception {
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 void tearDown() throws Exception {
deleteTestTables();
statement.close();
conn.close();
super.tearDown();
}
protected void createTestTables() {
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 void deleteTestTables() {
try {
statement.execute(DatabaseCreator.DROP_TABLE1);
statement.execute(DatabaseCreator.DROP_TABLE2);
statement.execute(DatabaseCreator.DROP_TABLE3);
} catch (SQLException e) {
fail("Unexpected SQLException " + e.toString());
}
}
/**
* UpdateFunctionalityTest#testUpdate1(). Updates all values in one
* column in the table
*/
public void testUpdate1() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate2(). Updates values in one
* column in the table using where condition in update command
*/
public void testUpdate2() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate3(). Updates values in a several
* columns in the table
*/
public void testUpdate3() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate4(). Updates values in a several
* columns in the table using where condition in update command
*/
public void testUpdate4() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate5(). Updates values in one
* columns in the table using condition
*/
public void testUpdate5() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate6(). Sets value of field2 to
* default
*/
public void testUpdate6() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate7(). Updates records in the
* table using subquery in update command
*/
public void testUpdate7() {
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());
}
}
/**
* UpdateFunctionalityTest#testUpdate8(). Sets value of field2 to
* NULL
*/
public void testUpdate8() {
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());
}
}
}