1 /* 2 * Copyright (C) 2007 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package tests.java.sql; 18 19 import java.math.BigDecimal; 20 import java.sql.Connection; 21 import java.sql.DatabaseMetaData; 22 import java.sql.ResultSet; 23 import java.sql.SQLException; 24 import java.sql.Statement; 25 import java.util.HashSet; 26 27 import tests.support.DatabaseCreator; 28 import tests.support.Support_SQL; 29 import junit.extensions.TestSetup; 30 import junit.framework.Test; 31 import junit.framework.TestCase; 32 import junit.framework.TestSuite; 33 34 public class UpdateFunctionalityTest extends TestCase { 35 36 private static final int numberOfRecords = 20; 37 38 private static Connection conn; 39 40 private static Statement statement; 41 42 public void setUp() throws Exception { 43 super.setUp(); 44 Support_SQL.loadDriver(); 45 try { 46 conn = Support_SQL.getConnection(); 47 statement = conn.createStatement(); 48 createTestTables(); 49 } catch (SQLException e) { 50 fail("Unexpected SQLException " + e.toString()); 51 } 52 DatabaseCreator.fillTestTable1(conn, numberOfRecords); 53 DatabaseCreator.fillTestTable2(conn, numberOfRecords); 54 } 55 56 public void tearDown() throws Exception { 57 deleteTestTables(); 58 statement.close(); 59 conn.close(); 60 61 super.tearDown(); 62 } 63 64 protected void createTestTables() { 65 try { 66 DatabaseMetaData meta = conn.getMetaData(); 67 ResultSet userTab = meta.getTables(null, null, null, null); 68 69 while (userTab.next()) { 70 String tableName = userTab.getString("TABLE_NAME"); 71 if (tableName.equals(DatabaseCreator.TEST_TABLE1)) { 72 statement.execute(DatabaseCreator.DROP_TABLE1); 73 } else if (tableName 74 .equals(DatabaseCreator.TEST_TABLE2)) { 75 statement.execute(DatabaseCreator.DROP_TABLE2); 76 } else if (tableName 77 .equals(DatabaseCreator.TEST_TABLE3)) { 78 statement.execute(DatabaseCreator.DROP_TABLE3); 79 } 80 } 81 userTab.close(); 82 83 statement.execute(DatabaseCreator.CREATE_TABLE3); 84 statement.execute(DatabaseCreator.CREATE_TABLE2); 85 statement.execute(DatabaseCreator.CREATE_TABLE1); 86 87 } catch (SQLException e) { 88 fail("Unexpected SQLException " + e.toString()); 89 } 90 } 91 92 protected void deleteTestTables() { 93 try { 94 statement.execute(DatabaseCreator.DROP_TABLE1); 95 statement.execute(DatabaseCreator.DROP_TABLE2); 96 statement.execute(DatabaseCreator.DROP_TABLE3); 97 } catch (SQLException e) { 98 fail("Unexpected SQLException " + e.toString()); 99 } 100 } 101 102 /** 103 * UpdateFunctionalityTest#testUpdate1(). Updates all values in one 104 * column in the table 105 */ 106 public void testUpdate1() { 107 String newValue = "newValue"; 108 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 109 + " SET field1='" + newValue + "'"; 110 try { 111 int num = statement.executeUpdate(updateQuery); 112 assertEquals("Not all records in the database were updated", 113 numberOfRecords, num); 114 String selectQuery = "SELECT field1 FROM " 115 + DatabaseCreator.TEST_TABLE1; 116 ResultSet result = statement.executeQuery(selectQuery); 117 while (result.next()) { 118 assertEquals("The field field1 was not updated", newValue, 119 result.getString("field1")); 120 } 121 result.close(); 122 } catch (SQLException e) { 123 fail("Unexpected exception" + e.getMessage()); 124 } 125 } 126 127 /** 128 * UpdateFunctionalityTest#testUpdate2(). Updates values in one 129 * column in the table using where condition in update command 130 */ 131 public void testUpdate2() { 132 String newValue = "newValue"; 133 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 134 + " SET field1='" + newValue + "' WHERE (id > 2) and (id < 10)"; 135 try { 136 int num = statement.executeUpdate(updateQuery); 137 int expectedUpdated = 7; 138 assertEquals("Not all records in the database were updated", 139 expectedUpdated, num); 140 String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1; 141 ResultSet result = statement.executeQuery(selectQuery); 142 while (result.next()) { 143 int id = result.getInt("id"); 144 String field1 = result.getString("field1"); 145 if ((id > 2) && (id < 10)) { 146 assertEquals("The field field1 was not updated", newValue, 147 field1); 148 } else { 149 assertEquals("The field field1 was not updated", 150 DatabaseCreator.defaultString + id, field1); 151 } 152 } 153 result.close(); 154 } catch (SQLException e) { 155 fail("Unexpected exception" + e.getMessage()); 156 } 157 } 158 159 /** 160 * UpdateFunctionalityTest#testUpdate3(). Updates values in a several 161 * columns in the table 162 */ 163 public void testUpdate3() { 164 int newValue1 = -1; 165 int newValue2 = -2; 166 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 167 + " SET field2=" + newValue1 + ", field3=" + newValue2; 168 try { 169 int num = statement.executeUpdate(updateQuery); 170 assertEquals("Not all records in the database were updated", 171 numberOfRecords, num); 172 String selectQuery = "SELECT field2, field3 FROM " 173 + DatabaseCreator.TEST_TABLE1; 174 ResultSet result = statement.executeQuery(selectQuery); 175 while (result.next()) { 176 // TODO getBigDecimal is not supported 177 // assertEquals("The field field2 was not updated", newValue1, 178 // result.getBigDecimal("field2").intValue()); 179 // assertEquals("The field field3 was not updated", newValue2, 180 // result.getBigDecimal("field3").intValue()); 181 } 182 result.close(); 183 } catch (SQLException e) { 184 fail("Unexpected exception" + e.getMessage()); 185 } 186 } 187 188 /** 189 * UpdateFunctionalityTest#testUpdate4(). Updates values in a several 190 * columns in the table using where condition in update command 191 */ 192 public void testUpdate4() { 193 int newValue1 = -1; 194 int newValue2 = -2; 195 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 196 + " SET field2=" + newValue1 + ", field3=" + newValue2 197 + " WHERE id > 10"; 198 try { 199 int num = statement.executeUpdate(updateQuery); 200 int expectedUpdated = 9; 201 assertEquals("Not all records in the database were updated", 202 expectedUpdated, num); 203 String selectQuery = "SELECT id, field2, field3 FROM " 204 + DatabaseCreator.TEST_TABLE1; 205 ResultSet result = statement.executeQuery(selectQuery); 206 while (result.next()) { 207 int id = result.getInt("id"); 208 // TODO getBigDecimal is not supported 209 // int value2 = result.getBigDecimal("field2").intValue(); 210 // int value3 = result.getBigDecimal("field3").intValue(); 211 // if (id > expectedUpdated + 1) { 212 // assertEquals("The field field2 was not updated", newValue1, 213 // value2); 214 // assertEquals("The field field3 was not updated", newValue2, 215 // value3); 216 // } else { 217 // assertEquals("The field field2 was not updated", id, value2); 218 // assertEquals("The field field3 was not updated", id, value3); 219 // } 220 } 221 result.close(); 222 } catch (SQLException e) { 223 fail("Unexpected exception" + e.getMessage()); 224 } 225 } 226 227 /** 228 * UpdateFunctionalityTest#testUpdate5(). Updates values in one 229 * columns in the table using condition 230 */ 231 public void testUpdate5() { 232 int factor = 3; 233 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 234 + " SET field2=field2 *" + factor; 235 try { 236 String selectQuery = "SELECT field2 FROM " 237 + DatabaseCreator.TEST_TABLE1; 238 ResultSet result = statement.executeQuery(selectQuery); 239 HashSet<BigDecimal> values = new HashSet<BigDecimal>(); 240 // TODO getBigDecimal is not supported 241 // while (result.next()) { 242 // values.add(BigDecimal.valueOf(result.getBigDecimal("field2") 243 // .intValue() 244 // * factor)); 245 // } 246 247 int num = statement.executeUpdate(updateQuery); 248 assertEquals("Not all records in the database were updated", 249 numberOfRecords, num); 250 result = statement.executeQuery(selectQuery); 251 // TODO getBigDecimal is not supported 252 // while (result.next()) { 253 // BigDecimal value = result.getBigDecimal("field2"); 254 // assertTrue("Wrong value of field2 after update" 255 // + value.intValue(), values.remove(value)); 256 // } 257 assertTrue("Not all records were updated", values.isEmpty()); 258 259 result.close(); 260 } catch (SQLException e) { 261 fail("Unexpected exception" + e.getMessage()); 262 } 263 } 264 265 /** 266 * UpdateFunctionalityTest#testUpdate6(). Sets value of field2 to 267 * default 268 */ 269 public void testUpdate6() { 270 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 271 + " SET field2='1'"; 272 try { 273 274 int num = statement.executeUpdate(updateQuery); 275 assertEquals("Not all records in the database were updated", 276 numberOfRecords, num); 277 String selectQuery = "SELECT field2 FROM " 278 + DatabaseCreator.TEST_TABLE1; 279 ResultSet result = statement.executeQuery(selectQuery); 280 // TODO getBigDecimal is not supported 281 // while (result.next()) { 282 // assertEquals("value of field2 should be default ", 283 // DatabaseCreator.defaultInt, result.getBigDecimal( 284 // "field2").intValue()); 285 // } 286 result.close(); 287 } catch (SQLException e) { 288 fail("Unexpected exception" + e.getMessage()); 289 } 290 } 291 292 /** 293 * UpdateFunctionalityTest#testUpdate7(). Updates records in the 294 * table using subquery in update command 295 */ 296 public void testUpdate7() { 297 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 298 + " SET field2='1' WHERE id < ( SELECT COUNT(*) FROM " 299 + DatabaseCreator.TEST_TABLE2 + " WHERE finteger > 15)"; 300 try { 301 int num = statement.executeUpdate(updateQuery); 302 int expectedUpdated = 4; 303 assertEquals("Not all records in the database were updated", 304 expectedUpdated, num); 305 String selectQuery = "SELECT id, field2 FROM " 306 + DatabaseCreator.TEST_TABLE1; 307 ResultSet result = statement.executeQuery(selectQuery); 308 while (result.next()) { 309 // TODO getBigDecimal is not supported 310 // int value = result.getBigDecimal("field2").intValue(); 311 // int id = result.getInt("id"); 312 // if (id < expectedUpdated) { 313 // assertEquals("value of field2 should be default ", 314 // DatabaseCreator.defaultInt, value); 315 // } else { 316 // assertEquals("wrong value of field2", id, value); 317 // } 318 } 319 result.close(); 320 } catch (SQLException e) { 321 fail("Unexpected exception" + e.getMessage()); 322 } 323 } 324 325 /** 326 * UpdateFunctionalityTest#testUpdate8(). Sets value of field2 to 327 * NULL 328 */ 329 public void testUpdate8() { 330 String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1 331 + " SET field2=NULL"; 332 try { 333 334 int num = statement.executeUpdate(updateQuery); 335 assertEquals("Not all records in the database were updated", 336 numberOfRecords, num); 337 String selectQuery = "SELECT field2 FROM " 338 + DatabaseCreator.TEST_TABLE1; 339 ResultSet result = statement.executeQuery(selectQuery); 340 while (result.next()) { 341 assertNull("value of field2 should be NULL", result 342 .getObject("field2")); 343 } 344 result.close(); 345 } catch (SQLException e) { 346 fail("Unexpected exception" + e.getMessage()); 347 } 348 } 349 } 350
