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 17package tests.java.sql; 18 19import dalvik.annotation.KnownFailure; 20 21import java.io.CharArrayReader; 22import java.math.BigDecimal; 23import java.sql.Connection; 24import java.sql.Date; 25import java.sql.PreparedStatement; 26import java.sql.ResultSet; 27import java.sql.SQLException; 28import java.sql.Statement; 29import java.sql.Time; 30import java.util.ArrayList; 31import java.util.Calendar; 32import java.util.HashMap; 33import java.util.HashSet; 34 35import tests.support.DatabaseCreator; 36import tests.support.Support_SQL; 37import junit.extensions.TestSetup; 38import junit.framework.Test; 39import junit.framework.TestCase; 40import junit.framework.TestSuite; 41 42public class SelectFunctionalityTest extends TestCase { 43 44 private static Connection conn; 45 46 private static Statement statement; 47 48 private static Date date; 49 50 private static Time time; 51 52 public void setUp() throws Exception { 53 super.setUp(); 54 Support_SQL.loadDriver(); 55 try { 56 conn = Support_SQL.getConnection(); 57 statement = conn.createStatement(); 58 createTestTables(); 59 } catch (SQLException e) { 60 fail("Unexpected SQLException " + e.toString()); 61 } 62 } 63 64 public void tearDown() throws Exception { 65 deleteTestTables(); 66 conn.close(); 67 statement.close(); 68 super.tearDown(); 69 } 70 71 private void createTestTables() { 72 try { 73 ResultSet userTab = conn.getMetaData().getTables(null, 74 null, null, null); 75 76 while (userTab.next()) { 77 String tableName = userTab.getString("TABLE_NAME"); 78 if (tableName.equals(DatabaseCreator.TEST_TABLE2)) { 79 statement.execute(DatabaseCreator.DROP_TABLE2); 80 } else if (tableName 81 .equals(DatabaseCreator.SALESPEOPLE_TABLE)) { 82 statement 83 .execute(DatabaseCreator.DROP_TABLE_SALESPEOPLE); 84 } else if (tableName 85 .equals(DatabaseCreator.CUSTOMERS_TABLE)) { 86 statement 87 .execute(DatabaseCreator.DROP_TABLE_CUSTOMERS); 88 } else if (tableName 89 .equals(DatabaseCreator.ORDERS_TABLE)) { 90 statement 91 .execute(DatabaseCreator.DROP_TABLE_ORDERS); 92 } 93 } 94 userTab.close(); 95 96 statement.execute(DatabaseCreator.CREATE_TABLE2); 97 statement.execute(DatabaseCreator.CREATE_TABLE_SALESPEOPLE); 98 statement.execute(DatabaseCreator.CREATE_TABLE_CUSTOMERS); 99 statement.execute(DatabaseCreator.CREATE_TABLE_ORDERS); 100 101 long currentTime = Calendar.getInstance().getTimeInMillis(); 102 date = new Date(currentTime); 103 time = new Time(currentTime); 104 105 DatabaseCreator.fillTestTable2(conn, 1, 5, currentTime); 106 DatabaseCreator.fillCustomersTable(conn); 107 DatabaseCreator.fillOrdersTable(conn); 108 DatabaseCreator.fillSalesPeopleTable(conn); 109 110 } catch (SQLException e) { 111 fail("Unexpected SQLException " + e.toString()); 112 } 113 } 114 115 private void deleteTestTables() { 116 try { 117 statement.execute(DatabaseCreator.DROP_TABLE2); 118 statement.execute(DatabaseCreator.DROP_TABLE_SALESPEOPLE); 119 statement.execute(DatabaseCreator.DROP_TABLE_CUSTOMERS); 120 statement.execute(DatabaseCreator.DROP_TABLE_ORDERS); 121 } catch (SQLException e) { 122 fail("Unexpected SQLException " + e.toString()); 123 } 124 } 125 126 /** 127 * SelectFunctionalityTest#test_SelectSimple(). Selects all records 128 * from the table 129 */ 130 public void test_SelectSimple() throws SQLException { 131 String sql = "SELECT * FROM " + DatabaseCreator.TEST_TABLE2; 132 ResultSet result = statement.executeQuery(sql); 133 int counter = 0; 134 135 while (result.next()) { 136 int id = result.getInt("finteger"); 137 assertEquals("expected value doesn't equal actual", 138 DatabaseCreator.defaultString + id, result 139 .getString("ftext")); 140 assertEquals("expected value doesn't equal actual", 141 DatabaseCreator.defaultCharacter + id, result 142 .getString("fcharacter")); 143 144 // TODO getBigDecimal is not supported 145// assertEquals("expected value doesn't equal actual", BigDecimal 146// .valueOf(id + 0.1), result.getBigDecimal("fdecimal")); 147// assertEquals("expected value doesn't equal actual", BigDecimal 148// .valueOf(id + 0.1), result.getBigDecimal("fnumeric")); 149// assertEquals("expected value doesn't equal actual", id, result 150// .getInt("fsmallint")); 151 assertEquals("expected value doesn't equal actual", BigDecimal 152 .valueOf(id + 0.1).floatValue(), result.getFloat("ffloat")); 153 assertEquals("expected value doesn't equal actual", BigDecimal 154 .valueOf(id + 0.1).doubleValue(), result.getDouble("freal")); 155 assertEquals("expected value doesn't equal actual", BigDecimal 156 .valueOf(id + 0.1).doubleValue(), result 157 .getDouble("fdouble")); 158 assertEquals("expected value doesn't equal actual", 159 date.toString(), result.getDate("fdate").toString()); 160 assertEquals("expected value doesn't equal actual", 161 time.toString(), result.getTime("ftime").toString()); 162 counter++; 163 } 164 165 assertEquals("number of rows in ResultSet is wrong", 5, counter); 166 result.close(); 167 } 168 169 /** 170 * SelectFunctionalityTest#test_SelectPrepared(). Selects all records 171 * from the table using parametric query 172 */ 173 public void test_SelectPrepared() throws SQLException { 174 String sql = "SELECT finteger, ftext, fcharacter, fdecimal, fnumeric," 175 + " fsmallint, ffloat, freal, fdouble, fdate, ftime" + " FROM " 176 + DatabaseCreator.TEST_TABLE2 177 + " WHERE finteger = ? AND ftext = ? AND fcharacter = ? AND" 178 + " fdecimal = ? AND fnumeric = ? AND fsmallint = ? AND" 179 + " freal = ? AND fdouble = ? AND fdate = ?" + " AND ftime = ?"; 180 PreparedStatement prepStatement = conn.prepareStatement(sql); 181 182 CharArrayReader reader = new CharArrayReader(new String( 183 DatabaseCreator.defaultCharacter + "1").toCharArray()); 184 prepStatement.setInt(1, 1); 185 prepStatement.setString(2, DatabaseCreator.defaultString + "1"); 186// TODO setCharacterStream and setBigDecimal are not supported 187// prepStatement.setCharacterStream(3, reader, 4); 188// prepStatement.setBigDecimal(4, BigDecimal.valueOf(1.1)); 189// prepStatement.setBigDecimal(5, BigDecimal.valueOf(1.1)); 190 prepStatement.setInt(6, 1); 191 prepStatement.setDouble(7, 1.1); 192 prepStatement.setDouble(8, 1.1); 193 prepStatement.setDate(9, date); 194 prepStatement.setTime(10, time); 195 196 int counter = 0; 197 ResultSet result = prepStatement.executeQuery(); 198 while (result.next()) { 199 int id = result.getInt("finteger"); 200 assertEquals("expected value doesn't equal actual", 201 DatabaseCreator.defaultString + id, result 202 .getString("ftext")); 203 assertEquals("expected value doesn't equal actual", 204 DatabaseCreator.defaultCharacter + id, result 205 .getString("fcharacter")); 206// TODO getBigDecimal is not supported 207// assertEquals("expected value doesn't equal actual", BigDecimal 208// .valueOf(1.1), result.getBigDecimal("fdecimal")); 209// assertEquals("expected value doesn't equal actual", BigDecimal 210// .valueOf(1.1), result.getBigDecimal("fnumeric")); 211 assertEquals("expected value doesn't equal actual", id, result 212 .getInt("fsmallint")); 213 assertEquals("expected value doesn't equal actual", 214 (float) (id + 0.1), result.getFloat("ffloat")); 215 assertEquals("expected value doesn't equal actual", 216 (double) (id + 0.1), result.getDouble("freal")); 217 assertEquals("expected value doesn't equal actual", 218 (double) (id + 0.1), result.getDouble("fdouble")); 219 assertEquals("expected value doesn't equal actual", 220 date.toString(), result.getDate("fdate").toString()); 221 assertEquals("expected value doesn't equal actual", 222 time.toString(), result.getTime("ftime").toString()); 223 counter++; 224 } 225// TODO query wasn't executed due to "not supported" methods 226// assertEquals("number of rows in ResultSet is wrong", 1, counter); 227 prepStatement.close(); 228 result.close(); 229 } 230 231 /** 232 * SelectFunctionalityTest#test_SubSelect(). Selects records from the 233 * table using subselect 234 */ 235 public void test_SubSelect() throws SQLException { 236 String sql = "SELECT finteger," + " (SELECT ftext FROM " 237 + DatabaseCreator.TEST_TABLE2 + " WHERE finteger = 1) as ftext" 238 + " FROM " + DatabaseCreator.TEST_TABLE2; 239 ResultSet result = statement.executeQuery(sql); 240 241 HashMap<Integer, String> value = new HashMap<Integer, String>(); 242 value.put(1, DatabaseCreator.defaultString + "1"); 243 value.put(2, DatabaseCreator.defaultString + "1"); 244 value.put(3, DatabaseCreator.defaultString + "1"); 245 value.put(4, DatabaseCreator.defaultString + "1"); 246 value.put(5, DatabaseCreator.defaultString + "1"); 247 248 while (result.next()) { 249 int key = result.getInt("finteger"); 250 String val = result.getString("ftext"); 251 assertTrue("wrong value of finteger field", value.containsKey(key)); 252 assertEquals("wrong value of ftext field", value.get(key), val); 253 value.remove(key); 254 } 255 assertTrue("expected rows number doesn't equal actual rows number", 256 value.isEmpty()); 257 result.close(); 258 } 259 260 /** 261 * SelectFunctionalityTest#test_SelectThreeTables(). Selects records 262 * from a few tables 263 */ 264 public void test_SelectThreeTables() throws SQLException { 265 String sql = "SELECT onum, " + DatabaseCreator.ORDERS_TABLE + ".cnum" 266 + " FROM " + DatabaseCreator.SALESPEOPLE_TABLE + ", " 267 + DatabaseCreator.CUSTOMERS_TABLE + ", " 268 + DatabaseCreator.ORDERS_TABLE + " WHERE " 269 + DatabaseCreator.CUSTOMERS_TABLE + ".city <> " 270 + DatabaseCreator.SALESPEOPLE_TABLE + ".city" + " AND " 271 + DatabaseCreator.ORDERS_TABLE + ".cnum = " 272 + DatabaseCreator.CUSTOMERS_TABLE + ".cnum" + " AND " 273 + DatabaseCreator.ORDERS_TABLE + ".snum = " 274 + DatabaseCreator.SALESPEOPLE_TABLE + ".snum"; 275 ResultSet result = statement.executeQuery(sql); 276 277 HashMap<Integer, Integer> value = new HashMap<Integer, Integer>(); 278 value.put(3001, 2008); 279 value.put(3002, 2007); 280 value.put(3006, 2008); 281 value.put(3009, 2002); 282 value.put(3007, 2004); 283 value.put(3010, 2004); 284 285 while (result.next()) { 286 int key = result.getInt("onum"); 287 int val = result.getInt("cnum"); 288 assertTrue("wrong value of onum field", value.containsKey(key)); 289 assertEquals("wrong value of cnum field", value.get(key), 290 (Integer) val); 291 value.remove(key); 292 } 293 assertTrue("expected rows number doesn't equal actual rows number", 294 value.isEmpty()); 295 result.close(); 296 } 297 298 /** 299 * SelectFunctionalityTest#test_SelectThreeTables(). Selects records 300 * from a table using union 301 */ 302 public void test_SelectUnionItself() throws SQLException { 303 String sql = "SELECT b.cnum, b.cname" + " FROM " 304 + DatabaseCreator.CUSTOMERS_TABLE + " a, " 305 + DatabaseCreator.CUSTOMERS_TABLE + " b" 306 + " WHERE a.snum = 1002" + " AND b.city = a.city"; 307 ResultSet result = statement.executeQuery(sql); 308 309 HashMap<Integer, String> value = new HashMap<Integer, String>(); 310 value.put(2003, "Liu"); 311 value.put(2004, "Grass"); 312 value.put(2008, "Cisneros"); 313 314 while (result.next()) { 315 int key = result.getInt("cnum"); 316 String val = result.getString("cname"); 317 assertTrue("wrong value of cnum field", value.containsKey(key)); 318 assertEquals("wrong value of cname field", value.get(key), val); 319 value.remove(key); 320 } 321 assertTrue("expected rows number doesn't equal actual rows number", 322 value.isEmpty()); 323 result.close(); 324 } 325 326 /** 327 * SelectFunctionalityTest#test_SelectLeftOuterJoin(). Selects 328 * records from a table using left join 329 */ 330 public void test_SelectLeftOuterJoin() throws SQLException { 331 String sql = "SELECT distinct s.snum as ssnum, c.snum as ccnum FROM " 332 + DatabaseCreator.CUSTOMERS_TABLE + " c left outer join " 333 + DatabaseCreator.SALESPEOPLE_TABLE + " s on s.snum=c.snum"; 334 ResultSet result = statement.executeQuery(sql); 335 336 HashMap<Integer, Integer> value = new HashMap<Integer, Integer>(); 337 value.put(1001, 1001); 338 value.put(1002, 1002); 339 value.put(1003, 1003); 340 value.put(1004, 1004); 341 value.put(1007, 1007); 342 343 while (result.next()) { 344 int key = result.getInt("ssnum"); 345 Object val = result.getObject("ccnum"); 346 assertTrue("wrong value of ssnum field", value.containsKey(key)); 347 assertEquals("wrong value of ccnum field", value.get(key), 348 (Integer) val); 349 value.remove(key); 350 } 351 assertTrue("expected rows number doesn't equal actual rows number", 352 value.isEmpty()); 353 result.close(); 354 } 355 356 /** 357 * SelectFunctionalityTest#test_SelectRightOuterJoin(). Selects 358 * records from a table using right join 359 * 360 * TODO RIGHT and FULL OUTER JOINs are not supported 361 */ 362 @KnownFailure("not supported") 363 public void test_SelectRightOuterJoin() throws SQLException { 364 String sql = "SELECT distinct s.snum as ssnum, c.snum as ccnum FROM " 365 + DatabaseCreator.CUSTOMERS_TABLE + " c right outer join " 366 + DatabaseCreator.SALESPEOPLE_TABLE + " s on s.snum=c.snum"; 367 ResultSet result = statement.executeQuery(sql); 368 369 HashMap<Integer, Integer> value = new HashMap<Integer, Integer>(); 370 value.put(1001, 1001); 371 value.put(1002, 1002); 372 value.put(1003, 1003); 373 value.put(1004, 1004); 374 value.put(1007, 1007); 375 value.put(1013, null); 376 377 while (result.next()) { 378 int key = result.getInt("ssnum"); 379 Object val = result.getObject("ccnum"); 380 assertTrue("wrong value of ssnum field", value.containsKey(key)); 381 assertEquals("wrong value of ccnum field", value.get(key), 382 (Integer) val); 383 value.remove(key); 384 } 385 assertTrue("expected rows number doesn't equal actual rows number", 386 value.isEmpty()); 387 result.close(); 388 } 389 390 /** 391 * SelectFunctionalityTest#test_SelectGroupBy(). Selects records from 392 * a table using group by 393 */ 394 public void test_SelectGroupBy() throws SQLException { 395 String selectQuery = "SELECT rating, SUM(snum) AS sum FROM " 396 + DatabaseCreator.CUSTOMERS_TABLE + " GROUP BY rating"; 397 ResultSet result = statement.executeQuery(selectQuery); 398 399 HashMap<Integer, Integer> values = new HashMap<Integer, Integer>(); 400 values.put(100, 3006); 401 values.put(200, 2005); 402 values.put(300, 2009); 403 404 while (result.next()) { 405 int rating = result.getInt("rating"); 406 int sum = result.getInt("sum"); 407 assertTrue("Wrong value of rating field", values 408 .containsKey(rating)); 409 assertEquals("Wrong value of sum field", values.get(rating), 410 new Integer(sum)); 411 assertEquals(new Integer(sum), values.remove(rating)); 412 } 413 result.close(); 414 assertTrue("Result set has wrong size", values.isEmpty()); 415 } 416 417 /** 418 * SelectFunctionalityTest#test_SelectOrderBy(). Selects records from 419 * a table using order by 420 */ 421 public void test_SelectOrderBy() throws SQLException { 422 String selectQuery = "SELECT onum FROM " + DatabaseCreator.ORDERS_TABLE 423 + " ORDER BY onum"; 424 ResultSet result = statement.executeQuery(selectQuery); 425 426 ArrayList<Integer> values = new ArrayList<Integer>(); 427 values.add(Integer.valueOf(3001)); 428 values.add(Integer.valueOf(3002)); 429 values.add(Integer.valueOf(3003)); 430 values.add(Integer.valueOf(3005)); 431 values.add(Integer.valueOf(3006)); 432 values.add(Integer.valueOf(3007)); 433 values.add(Integer.valueOf(3008)); 434 values.add(Integer.valueOf(3009)); 435 values.add(Integer.valueOf(3010)); 436 values.add(Integer.valueOf(3011)); 437 438 int index = 0; 439 while (result.next()) { 440 Integer onum = result.getInt("onum"); 441 assertTrue("result set doesn't contain value", values 442 .contains(onum)); 443 assertEquals("result set is not sorted", index, values 444 .indexOf(onum)); 445 index++; 446 } 447 result.close(); 448 } 449 450 /** 451 * SelectFunctionalityTest#test_SelectDistinct(). Selects records 452 * from a table using distinct 453 */ 454 public void test_SelectDistinct() throws SQLException { 455 String selectQuery = "SELECT DISTINCT rating FROM " 456 + DatabaseCreator.CUSTOMERS_TABLE; 457 ResultSet result = statement.executeQuery(selectQuery); 458 459 HashSet<Integer> values = new HashSet<Integer>(); 460 values.add(Integer.valueOf(100)); 461 values.add(Integer.valueOf(200)); 462 values.add(Integer.valueOf(300)); 463 464 while (result.next()) { 465 Integer rating = result.getInt("rating"); 466 assertTrue("result set doesn't contain value", values 467 .contains(rating)); 468 assertTrue("wrong value in the result set", values.remove(rating)); 469 } 470 result.close(); 471 assertTrue("Result set has wrong size", values.isEmpty()); 472 } 473 474 /** 475 * SelectFunctionalityTest#test_SelectAgregateFunctions(). Selects 476 * records from a table using agregate functions 477 */ 478 public void test_SelectAgregateFunctions() throws SQLException { 479 String selectCount = "SELECT count(onum) as count FROM " 480 + DatabaseCreator.ORDERS_TABLE; 481 String selectSum = "SELECT sum(onum) as sum FROM " 482 + DatabaseCreator.ORDERS_TABLE; 483 String selectAvg = "SELECT avg(onum) as avg FROM " 484 + DatabaseCreator.ORDERS_TABLE; 485 String selectMax = "SELECT max(onum) as max FROM " 486 + DatabaseCreator.ORDERS_TABLE; 487 String selectMin = "SELECT min(onum) as min FROM " 488 + DatabaseCreator.ORDERS_TABLE; 489 490 func("count", selectCount, 10); 491 func("sum", selectSum, 30062); 492 func("avg", selectAvg, 3006); 493 func("max", selectMax, 3011); 494 func("min", selectMin, 3001); 495 } 496 497 private void func(String name, String query, int expected) { 498 int res = 0; 499 double resDouble = 0.0; 500 try { 501 ResultSet result = statement.executeQuery(query); 502 while (result.next()) { 503 res = result.getInt(name); 504 if (res != 0 ) { 505 assertEquals(expected,res); 506 break; 507 } 508 // for Double: getInt not supported yet 509 resDouble = Double.parseDouble(result.getString(name)); 510 res = (int) Math.rint(resDouble); 511 assertEquals(expected,res); 512 513 } 514 assertFalse("wrong size of result set", result.next()); 515 result.close(); 516 } catch (SQLException e) { 517 fail(e.getMessage()); 518 } 519 } 520 521 /** 522 * SelectFunctionalityTest#test_SelectHaving(). Selects records from 523 * a table using having 524 */ 525 public void test_SelectHaving() throws SQLException { 526 String selectQuery = "SELECT snum, max(amt) AS max FROM " 527 + DatabaseCreator.ORDERS_TABLE 528 + " GROUP BY snum HAVING max(amt) > 3000"; 529 ResultSet result = statement.executeQuery(selectQuery); 530 531 HashSet<Double> values = new HashSet<Double>(); 532 values.add(Double.valueOf(9891.88)); 533 values.add(Double.valueOf(5160.45)); 534 535 while (result.next()) { 536 Double max = result.getDouble("max"); 537 assertTrue("result set doesn't contain value", values.contains(max)); 538 assertTrue("wrong value in the result set", values.remove(max)); 539 } 540 result.close(); 541 assertTrue("Result set has wrong size", values.isEmpty()); 542 } 543} 544