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.ResultSet; 22 import java.sql.SQLException; 23 import java.sql.Statement; 24 25 import tests.support.DatabaseCreator; 26 import tests.support.Support_SQL; 27 28 import junit.extensions.TestSetup; 29 import junit.framework.Test; 30 import junit.framework.TestCase; 31 import junit.framework.TestSuite; 32 33 import tests.support.ThreadPool; 34 35 public class MultiThreadAccessTest extends TestCase { 36 37 private static Connection conn; 38 39 private static Statement statement; 40 41 private static final int numThreads = 10; 42 43 private static final int numOfRecords = 20; 44 45 private ThreadPool threadPool; 46 47 public void setUp() throws Exception { 48 super.setUp(); 49 Support_SQL.loadDriver(); 50 try { 51 conn = Support_SQL.getConnection(); 52 statement = conn.createStatement(); 53 createTestTables(); 54 } catch (SQLException e) { 55 fail("Unexpected SQLException " + e.toString()); 56 } 57 threadPool = new ThreadPool(numThreads); 58 } 59 60 public void tearDown() throws Exception { 61 threadPool.join(); 62 deleteTestTables(); 63 conn.close(); 64 statement.close(); 65 super.tearDown(); 66 } 67 68 public void createTestTables() { 69 try { 70 ResultSet userTab = conn.getMetaData().getTables(null, 71 null, null, null); 72 73 while (userTab.next()) { 74 String tableName = userTab.getString("TABLE_NAME"); 75 if (tableName.equals(DatabaseCreator.TEST_TABLE1)) { 76 statement.execute(DatabaseCreator.DROP_TABLE1); 77 } else if (tableName 78 .equals(DatabaseCreator.TEST_TABLE2)) { 79 statement.execute(DatabaseCreator.DROP_TABLE2); 80 } else if (tableName 81 .equals(DatabaseCreator.TEST_TABLE4)) { 82 statement.execute(DatabaseCreator.DROP_TABLE4); 83 } else if (tableName 84 .equals(DatabaseCreator.TEST_TABLE3)) { 85 statement.execute(DatabaseCreator.DROP_TABLE3); 86 } 87 } 88 89 userTab.close(); 90 statement.execute(DatabaseCreator.CREATE_TABLE3); 91 statement.execute(DatabaseCreator.CREATE_TABLE4); 92 statement.execute(DatabaseCreator.CREATE_TABLE1); 93 statement.execute(DatabaseCreator.CREATE_TABLE2); 94 95 DatabaseCreator.fillTestTable1(conn, numOfRecords); 96 DatabaseCreator.fillTestTable2(conn, numOfRecords); 97 DatabaseCreator.fillTestTable4(conn, numOfRecords); 98 } catch (SQLException e) { 99 fail("Unexpected SQLException " + e.toString()); 100 } 101 } 102 103 public void deleteTestTables() { 104 try { 105 statement.execute(DatabaseCreator.DROP_TABLE1); 106 statement.execute(DatabaseCreator.DROP_TABLE2); 107 statement.execute(DatabaseCreator.DROP_TABLE3); 108 statement.execute(DatabaseCreator.DROP_TABLE4); 109 } catch (SQLException e) { 110 fail("Unexpected SQLException " + e.toString()); 111 } 112 } 113 114 /** 115 * A few threads execute select operation in the same time for one table in 116 * the database. Number of threads is defined by numThreads variable 117 * 118 * @throws SQLException 119 */ 120 public void test_MultipleAccessToOneTable() throws SQLException { 121 for (int i = 0; i < numThreads; i++) { 122 threadPool.runTask(createTask1(i)); 123 } 124 } 125 126 /** 127 * A few threads execute select operation in the same time for different 128 * tables in the database. Number of threads is defined by numThreads 129 * variable 130 * 131 * @throws SQLException 132 */ 133 public void test_MultipleAccessToSeveralTables() throws SQLException { 134 threadPool.runTask(createTask1(1)); 135 threadPool.runTask(createTask2(2)); 136 threadPool.runTask(createTask3(3)); 137 } 138 139 /** 140 * A few threads execute update, insert and delete operations in the same 141 * time for one table in the database. Number of threads is defined by 142 * numThreads variable 143 * 144 * @throws SQLException 145 */ 146 public void test_MultipleOperationsInSeveralTables() throws SQLException { 147 int id1 = numOfRecords - 1; 148 threadPool.runTask(createTask4(id1)); 149 150 int id2 = numOfRecords + 1; 151 threadPool.runTask(createTask5(id2)); 152 153 int oldID = 5; 154 int newID = 100; 155 threadPool.runTask(createTask6(oldID, newID)); 156 157 threadPool.join(); 158 159 Statement statement = conn.createStatement(); 160 String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1 161 + " WHERE id="; 162 163 ResultSet result = statement.executeQuery(selectQuery + id1); 164 assertFalse("The record was not deleted", result.next()); 165 166 result = statement.executeQuery(selectQuery + id2); 167 assertTrue("The record was not inserted", result.next()); 168 169 assertEquals("Wrong value of field1", DatabaseCreator.defaultString 170 + id2, result.getString("field1")); 171 // TODO getBigDecimal is not supported 172 assertEquals("Wrong value of field2", Integer.valueOf(id2).intValue(), result 173 .getInt("field2")); 174 assertEquals("Wrong value of field3", Integer.valueOf(id2).intValue(), result 175 .getInt("field3")); 176 result.close(); 177 178 result = statement.executeQuery(selectQuery + oldID); 179 assertFalse("The record was not deleted", result.next()); 180 result.close(); 181 182 result = statement.executeQuery(selectQuery + newID); 183 assertTrue("The record was not updated", result.next()); 184 185 assertEquals("Wrong value of field1", DatabaseCreator.defaultString 186 + newID, result.getString("field1")); 187 // TODO getBigDecimal is not supported 188 assertEquals("Wrong value of field2", Integer.valueOf(newID).intValue(), result 189 .getInt("field2")); 190 assertEquals("Wrong value of field3", Integer.valueOf(newID).intValue(), result 191 .getInt("field3")); 192 result.close(); 193 } 194 195 /** 196 * A few threads execute update operation in the same time for one tables in 197 * the database. Number of threads is defined by numThreads variable 198 * 199 * @throws SQLException 200 */ 201 public void test_MultipleUpdatesInOneTables() throws SQLException { 202 int id = 1; 203 String field = "field3"; 204 205 String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1 206 + " WHERE id=" + id; 207 Statement statement = conn.createStatement(); 208 209 ResultSet result = statement.executeQuery(selectQuery); 210 assertTrue("There is no records with id = " + id, result.next()); 211 // TODO getBigDecimal is not supported 212 // assertEquals("Wrong value of field " + field, BigDecimal.valueOf(id), 213 // result.getBigDecimal(field)); 214 result.close(); 215 216 for (int i = 0; i < numThreads; i++) { 217 threadPool.runTask(createTask7(id, field)); 218 } 219 220 threadPool.join(); 221 222 double expectedVal = id + numThreads; 223 result = statement.executeQuery(selectQuery); 224 assertTrue("There is no records with id = " + id, result.next()); 225 // TODO getBigDecimal is not supported -> 226 // assertEquals("Wrong value of field " + field, expectedVal, result 227 // .getBigDecimal(field).doubleValue()); 228 result.close(); 229 } 230 231 /** 232 * This method creates a Runnable that executes select operation for the 233 * first table 234 * 235 * @param taskID 236 * @return 237 */ 238 private static Runnable createTask1(final int taskID) { 239 return new Runnable() { 240 public void run() { 241 try { 242 Statement statement = conn.createStatement(); 243 ResultSet result = statement.executeQuery("SELECT * FROM " 244 + DatabaseCreator.TEST_TABLE1); 245 246 while (result.next()) { 247 assertEquals("Wrong value of id ", 248 DatabaseCreator.defaultString 249 + result.getInt("id"), result 250 .getString("field1")); 251 assertEquals("Wrong value of field2 ", result.getInt("id"), result 252 .getInt("field2")); 253 assertEquals("Wrong value of field3 ",result.getInt("id"), result 254 .getInt("field3")); 255 } 256 result.close(); 257 } catch (Exception e) { 258 System.err.println("Task 1 "+e.getMessage()); 259 } 260 } 261 }; 262 } 263 264 /** 265 * This method creates a Runnable that execute select operation for the 266 * second table 267 * 268 * @param taskID 269 */ 270 private static Runnable createTask2(final int taskID) { 271 return new Runnable() { 272 public void run() { 273 try { 274 Statement statement = conn.createStatement(); 275 ResultSet result = statement.executeQuery("SELECT * FROM " 276 + DatabaseCreator.TEST_TABLE2); 277 278 while (result.next()) { 279 while (result.next()) { 280 int id = result.getInt("finteger"); 281 assertEquals("Wrong value of ftext", 282 DatabaseCreator.defaultString + id, result 283 .getString("ftext")); 284 assertEquals("Wrong value of fcharacter", 285 DatabaseCreator.defaultCharacter + id, 286 result.getString("fcharacter")); 287 assertEquals("Wrong value of fdecimal", 288 DatabaseCreator.defaultDouble + id, result 289 .getDouble("fdecimal")); 290 assertEquals("Wrong value of fnumeric", 291 DatabaseCreator.defaultDouble + id, result 292 .getDouble("fnumeric")); 293 assertEquals("Wrong value of fsmallint", result 294 .getInt("finteger"), result 295 .getShort("fsmallint")); 296 assertEquals("Wrong value of ffloat", 297 (float) DatabaseCreator.defaultDouble + id, 298 result.getFloat("ffloat")); 299 assertEquals("Wrong value of freal", 300 (float) DatabaseCreator.defaultDouble + id, 301 result.getFloat("freal")); 302 assertEquals("Wrong value of fdouble", 303 DatabaseCreator.defaultDouble + id, result 304 .getDouble("fdouble")); 305 } 306 } 307 result.close(); 308 } catch (Exception e) { 309 System.err.println("Task2 "+e.getMessage()); 310 } 311 } 312 }; 313 } 314 315 /** 316 * This method creates a Runnable that execute select operation for the 317 * third table 318 * 319 * @param taskID 320 */ 321 private static Runnable createTask3(final int taskID) { 322 return new Runnable() { 323 public void run() { 324 try { 325 Statement statement = conn.createStatement(); 326 ResultSet result = statement.executeQuery("SELECT * FROM " 327 + DatabaseCreator.TEST_TABLE4); 328 329 while (result.next()) { 330 assertEquals("Wrong value of field1", 331 DatabaseCreator.defaultString 332 + result.getInt("fk"), result 333 .getString("field1")); 334 } 335 result.close(); 336 } catch (Exception e) { 337 System.err.println("Task 3 "+e.getMessage()); 338 } 339 } 340 }; 341 } 342 343 /** 344 * This method creates a Runnable that executes delete operation for the 345 * first table 346 * 347 * @param taskID 348 */ 349 private static Runnable createTask4(final int id) { 350 return new Runnable() { 351 public void run() { 352 try { 353 Statement statement = conn.createStatement(); 354 statement.execute("DELETE FROM " 355 + DatabaseCreator.TEST_TABLE1 + " WHERE id=" + id); 356 } catch (Exception e) { 357 System.err.println("Task 4 "+e.getMessage()); 358 } 359 } 360 }; 361 } 362 363 /** 364 * This method creates a Runnable that executes insert operation for the 365 * first table 366 * 367 * @param taskID 368 */ 369 private static Runnable createTask5(final int id) { 370 return new Runnable() { 371 public void run() { 372 try { 373 Statement statement = conn.createStatement(); 374 String value = DatabaseCreator.defaultString + id; 375 376 String insertQuery = "INSERT INTO " 377 + DatabaseCreator.TEST_TABLE1 378 + " (id, field1, field2, field3) VALUES(" + id 379 + ", '" + value + "', " + id + ", " + id + ")"; 380 statement.execute(insertQuery); 381 } catch (Exception e) { 382 System.err.println("Task 5 "+e.getMessage()); 383 } 384 } 385 }; 386 } 387 388 /** 389 * This method creates a Runnable that executes update operation for the one 390 * record of the first table 391 * 392 * @param taskID 393 */ 394 private static Runnable createTask6(final int oldID, final int newID) { 395 return new Runnable() { 396 public void run() { 397 try { 398 Statement statement = conn.createStatement(); 399 String value = DatabaseCreator.defaultString + newID; 400 String updateQuery = "UPDATE " 401 + DatabaseCreator.TEST_TABLE1 + " SET id=" + newID 402 + ", field1='" + value + "', field2=" + newID 403 + ", field3=" + newID + " WHERE id=" + oldID; 404 statement.execute(updateQuery); 405 } catch (Exception e) { 406 System.err.println("Task 6 "+e.getMessage()); 407 } 408 } 409 }; 410 } 411 412 /** 413 * This method creates a Runnable that executes update operation for the one 414 * field of one record with identifier id in the first table 415 * 416 * @param taskID 417 */ 418 private static Runnable createTask7(final int id, final String field) { 419 return new Runnable() { 420 public void run() { 421 try { 422 Statement statement = conn.createStatement(); 423 String updateQuery = "UPDATE " 424 + DatabaseCreator.TEST_TABLE1 + " SET " + field 425 + "= " + field + "+ 1 WHERE id=" + id; 426 statement.execute(updateQuery); 427 } catch (Exception e) { 428 System.err.println("Task 7 "+e.getMessage()); 429 } 430 } 431 }; 432 } 433 } 434
