· 9 years ago · Dec 08, 2016, 09:14 PM
1package jdbc.tests;
2
3import java.io.File;
4import java.io.IOException;
5import java.sql.Connection;
6import java.sql.Driver;
7import java.sql.ResultSet;
8import java.sql.SQLException;
9import java.sql.Statement;
10import java.util.Properties;
11
12import org.junit.Assert;
13import org.junit.Test;
14
15
16/**
17 * Notes:
18 1- Any test with "xmldb" can be replaced with "jsondb".
19
20 2- You can manipulate this file as much as you can, add more tests,
21 change these tests ... etc.
22
23 3- This file does not cover all the required test cases but
24 that does not mean that these test cases will be forgotten, the SanityTest
25 will have many of them.
26 **/
27public class SmokeTest {
28
29 private static String protocol = "xmldb";
30 private static String tmp = System.getProperty("java.io.tmpdir");
31
32 public static Class<?> getSpecifications() {
33 return Driver.class;
34 }
35
36 private Connection createUseDatabase(String databaseName) throws SQLException {
37 Driver driver = (Driver) TestRunner.getImplementationInstance();
38 Properties info = new Properties();
39
40 File dbDir = new File(tmp + File.separator + "jdbc" +
41 File.separator +
42 Math.round((((float) Math.random()) * 100000)));
43 System.out.print(dbDir.getAbsoluteFile());
44 info.put("path", dbDir.getAbsoluteFile());
45 Connection connection = driver.connect(
46 "jdbc:" + protocol + "://localhost", info);
47 // Establish
48 // connection
49 // (really,
50 // just
51 // make
52 // sure
53 // that the dbDir exists, and create it if it
54 // doesn't), and just record the protocol.
55 Statement statement = connection.createStatement();
56 // create a
57 // statement object
58 // to execute next
59 // statements.
60 statement.execute("DROP DATABASE " + databaseName);
61 // delete
62 // "databaseName" it
63 // if it exists.
64 statement.execute("CREATE DATABASE " + databaseName);
65 // you should now
66 // create a
67 // folder for
68 // that database
69 // within dbDir.
70 statement.execute("USE " + databaseName);
71 // Set the state of your
72 // connection to use
73 // "databaseName", all next
74 // created statements
75 // (like selects and inserts) should be applied to this database.
76 statement.close();
77 return connection;
78 }
79
80 @Test
81 public void testCreateAndOpenAndDropDatabase() throws SQLException {
82 File dummy = null;
83 Driver driver = (Driver) TestRunner.getImplementationInstance();
84 Properties info = new Properties();
85 File dbDir = new File(
86 tmp + File.separator + "jdbc" +
87 File.separator + Math.round((((float) Math.random()) * 100000)));
88 info.put("path", dbDir.getAbsoluteFile());
89 Connection connection = driver.connect(
90 "jdbc:" + protocol + "://localhost", info);
91
92 {
93 Statement statement = connection.createStatement();
94 statement.execute("DROP DATABASE SaMpLe");
95 statement.execute("CREATE DATABASE SaMpLe");
96 statement.execute("USE SaMpLe");
97 String files[] = dbDir.list();
98 Assert.assertFalse("Databases directory is empty!",
99 files == null || files.length == 0);
100 dummy = new File(dbDir, "dummy");
101 try {
102 boolean created = dummy.createNewFile();
103 Assert.assertTrue("Failed to create file into DB",
104 created && dummy.exists());
105 } catch (IOException e) {
106 TestRunner.fail("Failed to create file into DB", e);
107 }
108 statement.close();
109 }
110
111 {
112 Statement statement = connection.createStatement();
113 statement.execute("CREATE DATABASE sAmPlE");
114 statement.execute("USE SaMpLe");
115 String files[] = dbDir.list();
116 Assert.assertTrue("Database directory is empty after opening!",
117 files.length > 0);
118 Assert.assertTrue("Failed to create find"
119 + " a previously created file into DB",
120 dummy.exists());
121 statement.close();
122 }
123 connection.close();
124 }
125
126 @Test
127 public void testCreateTable() throws SQLException {
128 Connection connection = createUseDatabase("TestDB_Create");
129 try {
130 Statement statement = connection.createStatement();
131 statement.execute("CREATE TABLE table_name1("
132 + "column_name1 varchar, column_name2 int,"
133 + " column_name3 date)");
134 statement.close();
135 } catch (Throwable e) {
136 TestRunner.fail("Failed to create table", e);
137 }
138 try {
139 Statement statement = connection.createStatement();
140 boolean created = statement
141 .execute("CREATE TABLE table_name1(column_name1"
142 + " varchar, column_name2 int, column_name3 date)");
143 Assert.assertFalse("Create table succeed when table already exists",
144 created);
145 } catch (Throwable e) {
146 TestRunner.fail("Failed to create existing table", e);
147 }
148 try {
149 Statement statement = connection.createStatement();
150 statement.execute("CREATE TABLE incomplete_table_name1");
151 Assert.fail("Create invalid table succeed");
152 } catch (SQLException e) {
153 } catch (Throwable e) {
154 TestRunner.fail("Unknown Exception thrown", e);
155 }
156 connection.close();
157 }
158
159 @Test
160 public void testInsertWithoutColumnNames() throws SQLException {
161 Connection connection = createUseDatabase("TestDB_Create");
162 try {
163 Statement statement = connection.createStatement();
164 statement.execute("CREATE TABLE table_name3("
165 + "column_name1 varchar, column_name2 int,"
166 + " column_name3 float)");
167 int count = statement.executeUpdate("INSERT INTO table_name3"
168 + " VALUES ('value1', 3, 1.3)");
169 Assert.assertNotEquals("Insert returned zero rows", 0, count);
170 statement.close();
171 } catch (Throwable e) {
172 TestRunner.fail("Failed to insert into table", e);
173 }
174 connection.close();
175 }
176
177 @Test
178 public void testInsertWithColumnNames() throws SQLException {
179 Connection connection = createUseDatabase("TestDB_Create");
180 try {
181 Statement statement = connection.createStatement();
182 statement.execute("CREATE TABLE table_name4(column_name1 varchar, column_name2 int, column_name3 date)");
183 int count = statement.executeUpdate(
184 "INSERT INTO table_name4(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', '2011-01-25', 4)");
185 Assert.assertNotEquals("Insert returned zero rows", 0, count);
186 statement.close();
187 } catch (Throwable e) {
188 TestRunner.fail("Failed to insert into table", e);
189 }
190 connection.close();
191 }
192
193 @Test
194 public void testInsertWithWrongColumnNames() throws SQLException {
195 Connection connection = createUseDatabase("TestDB_Create");
196 try {
197 Statement statement = connection.createStatement();
198 statement.execute("CREATE TABLE table_name5(column_name1 varchar, column_name2 int, column_name3 varchar)");
199 statement.executeUpdate(
200 "INSERT INTO table_name5(invalid_column_name1, column_name3, column_name2) VALUES ('value1', 'value3', 4)");
201 Assert.fail("Inserted with invalid column name!!");
202 statement.close();
203 } catch (Throwable e) {
204 }
205 connection.close();
206 }
207
208 @Test
209 public void testInsertWithWrongColumnCount() throws SQLException {
210 Connection connection = createUseDatabase("TestDB_Create");
211 try {
212 Statement statement = connection.createStatement();
213 statement.execute("CREATE TABLE table_name6(column_name1 varchar, column_name2 int, column_name3 varchar)");
214 statement.executeUpdate("INSERT INTO table_name6(column_name1, column_name2) VALUES ('value1', 4)");
215 Assert.fail("Inserted with invalid column count!!");
216 statement.close();
217 } catch (Throwable e) {
218 }
219 connection.close();
220 }
221
222 @Test
223 public void testUpdate() throws SQLException {
224 Connection connection = createUseDatabase("TestDB_Create");
225 try {
226 Statement statement = connection.createStatement();
227 statement.execute("CREATE TABLE table_name7(column_name1 varchar, column_name2 int, column_name3 varchar)");
228 int count1 = statement.executeUpdate(
229 "INSERT INTO table_name7(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
230 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
231 int count2 = statement.executeUpdate(
232 "INSERT INTO table_name7(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
233 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
234 int count3 = statement.executeUpdate(
235 "INSERT INTO table_name7(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
236 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
237 int count4 = statement.executeUpdate(
238 "UPDATE table_name7 SET column_name1='1111111111', COLUMN_NAME2=2222222, column_name3='333333333'");
239 Assert.assertEquals("Updated returned wrong number", count1 + count2 + count3, count4);
240 statement.close();
241 } catch (Throwable e) {
242 TestRunner.fail("Failed to update table", e);
243 }
244 connection.close();
245 }
246
247 @Test
248 public void testConditionalUpdate() throws SQLException {
249 Connection connection = createUseDatabase("TestDB_Create");
250 try {
251 Statement statement = connection.createStatement();
252 statement.execute(
253 "CREATE TABLE table_name8(column_name1 varchar,"
254 + " column_name2 int, column_name3 date, column_name4 float)");
255
256 int count1 = statement.executeUpdate(
257 "INSERT INTO table_name8(column_NAME1,"
258 + " COLUMN_name3, column_name2, column_name4)"
259 + " VALUES ('value1', '2011-01-25', 3, 1.3)");
260 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
261
262 int count2 = statement.executeUpdate(
263 "INSERT INTO table_name8(column_NAME1, COLUMN_name3,"
264 + " column_name2, column_name4) VALUES"
265 + " ('value1', '2011-01-28', 3456, 1.01)");
266 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
267
268 int count3 = statement.executeUpdate(
269 "INSERT INTO table_name8(column_NAME1, COLUMN_name3,"
270 + " column_name2, column_name4) VALUES"
271 + " ('value2', '2011-02-11', -123, 3.14159)");
272 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
273
274 int count4 = statement.executeUpdate(
275 "UPDATE table_name8 SET COLUMN_NAME2=222222,"
276 + " column_name3='1993-10-03' WHERE"
277 + " coLUmn_NAME1='value1'");
278 Assert.assertEquals("Updated returned wrong number", count1 + count2, count4);
279
280 statement.close();
281 } catch (Throwable e) {
282 TestRunner.fail("Failed to update table", e);
283 }
284 connection.close();
285 }
286
287 @Test
288 public void testUpdateEmptyOrInvalidTable() throws SQLException {
289 Connection connection = createUseDatabase("TestDB_Create");
290 try {
291 Statement statement = connection.createStatement();
292 statement.execute("CREATE TABLE table_name9(column_name1 varchar, column_name2 int, column_name3 varchar)");
293 int count = statement.executeUpdate(
294 "UPDATE table_name9 SET column_name1='value1', column_name2=15, column_name3='value2'");
295 Assert.assertEquals("Updated empty table retruned non-zero count!", 0, count);
296 statement.close();
297 } catch (Throwable e) {
298 TestRunner.fail("Failed to update table", e);
299 }
300 try {
301 Statement statement = connection.createStatement();
302 statement.executeUpdate(
303 "UPDATE wrong_table_name9 SET column_name1='value1', column_name2=15, column_name3='value2'");
304 Assert.fail("Updated empty table retruned non-zero count!");
305 statement.close();
306 } catch (SQLException e) {
307 } catch (Throwable e) {
308 TestRunner.fail("Invalid exception was thrown", e);
309 }
310 connection.close();
311 }
312
313 @Test
314 public void testDelete() throws SQLException {
315 Connection connection = createUseDatabase("TestDB_Create");
316 try {
317 Statement statement = connection.createStatement();
318 statement.execute("CREATE TABLE table_name10(column_name1 varchar, column_name2 int, column_name3 date)");
319 int count1 = statement.executeUpdate(
320 "INSERT INTO table_name10(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', '2011-01-25', 4)");
321 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
322 int count2 = statement.executeUpdate(
323 "INSERT INTO table_name10(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', '2011-01-28', 4)");
324 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
325 int count3 = statement.executeUpdate(
326 "INSERT INTO table_name10(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', '2011-02-11', 5)");
327 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
328 int count4 = statement.executeUpdate("DELETE From table_name10");
329 Assert.assertEquals("Delete returned wrong number", 3, count4);
330 statement.close();
331 } catch (Throwable e) {
332 TestRunner.fail("Failed to delete from table", e);
333 }
334 connection.close();
335 }
336
337 @Test
338 public void testConditionalDelete() throws SQLException {
339 Connection connection = createUseDatabase("TestDB_Create");
340 try {
341 Statement statement = connection.createStatement();
342 statement.execute("CREATE TABLE table_name11(column_name1 varchar, column_name2 int, column_name3 DATE)");
343 int count1 = statement.executeUpdate(
344 "INSERT INTO table_name11(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', '2011-01-25', 4)");
345 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
346 int count2 = statement.executeUpdate(
347 "INSERT INTO table_name11(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', '2013-06-30', 4)");
348 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
349 int count3 = statement.executeUpdate(
350 "INSERT INTO table_name11(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', '2013-07-03', 5)");
351 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
352 int count4 = statement.executeUpdate("DELETE From table_name11 WHERE coLUmn_NAME3>'2011-01-25'");
353 Assert.assertEquals("Delete returned wrong number", 2, count4);
354 statement.close();
355 } catch (Throwable e) {
356 TestRunner.fail("Failed to delete from table", e);
357 }
358 connection.close();
359 }
360
361 @Test
362 public void testSelect() throws SQLException {
363 Connection connection = createUseDatabase("TestDB_Create");
364 try {
365 Statement statement = connection.createStatement();
366 statement
367 .execute("CREATE TABLE table_name12(column_name1 varchar, column_name2 int, column_name3 varchar)");
368 int count1 = statement.executeUpdate(
369 "INSERT INTO table_name12(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
370 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
371 int count2 = statement.executeUpdate(
372 "INSERT INTO table_name12(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
373 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
374 int count3 = statement.executeUpdate(
375 "INSERT INTO table_name12(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
376 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
377 int count4 = statement.executeUpdate(
378 "INSERT INTO table_name12(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
379 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
380 ResultSet result = statement.executeQuery("SELECT * From table_name12");
381 int rows = 0;
382 while (result.next())
383 rows++;
384 Assert.assertNotNull("Null result retruned", result);
385 Assert.assertEquals("Wrong number of rows", 4, rows);
386 Assert.assertEquals("Wrong number of columns", 3, result.getMetaData().getColumnCount());
387 statement.close();
388 } catch (Throwable e) {
389 TestRunner.fail("Failed to select from table", e);
390 }
391 connection.close();
392 }
393
394 @Test
395 public void testConditionalSelect() throws SQLException {
396 Connection connection = createUseDatabase("TestDB_Create");
397 try {
398 Statement statement = connection.createStatement();
399 statement
400 .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
401 int count1 = statement.executeUpdate(
402 "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
403 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
404 int count2 = statement.executeUpdate(
405 "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value3')");
406 Assert.assertNotEquals("Insert returned zero rows", 0, count2);
407 int count3 = statement.executeUpdate(
408 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
409 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
410 int count4 = statement.executeUpdate(
411 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
412 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
413 ResultSet result = statement.executeQuery("SELECT column_name1 FROM table_name13 WHERE coluMN_NAME2 < 5");
414 int rows = 0;
415 while (result.next())
416 rows++;
417 Assert.assertNotNull("Null result retruned", result);
418 Assert.assertEquals("Wrong number of rows", 2, rows);
419 Assert.assertEquals("Wrong number of columns", 1, result.getMetaData().getColumnCount());
420 statement.close();
421 } catch (Throwable e) {
422 TestRunner.fail("Failed to select from table", e);
423 }
424 connection.close();
425 }
426
427 @Test
428 public void testExecute() throws SQLException {
429 Connection connection = createUseDatabase("TestDB_Create");
430 try {
431 Statement statement = connection.createStatement();
432 statement
433 .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
434 int count1 = statement.executeUpdate(
435 "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
436 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
437 boolean result1 = statement.execute(
438 "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 8, 'value3')");
439
440 //Assert.assertTrue("Wrong return for insert record", result1);
441// Assert.assertTrue("Wrong return for insert record", result1);
442
443 int count3 = statement.executeUpdate(
444 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
445 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
446 int count4 = statement.executeUpdate(
447 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
448 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
449 boolean result2 = statement.execute("SELECT column_name1 FROM table_name13 WHERE coluMN_NAME2 = 8");
450 Assert.assertTrue("Wrong return for select existing records", result2);
451 boolean result3 = statement.execute("SELECT column_name1 FROM table_name13 WHERE coluMN_NAME2 > 100");
452 //Assert.assertFalse("Wrong return for select non existing records", result3);
453// Assert.assertFalse("Wrong return for select non existing records", result3);
454
455 statement.close();
456 } catch (Throwable e) {
457 TestRunner.fail("Failed to select from table", e);
458 }
459 connection.close();
460 }
461
462
463 @Test
464 public void testDistinct() throws SQLException {
465 Connection connection = createUseDatabase("TestDB_Create");
466 try {
467 Statement statement = connection.createStatement();
468 statement
469 .execute("CREATE TABLE table_name13(column_name1 varchar,"
470 + " column_name2 int, column_name3 varchar)");
471 int count1 = statement.executeUpdate(
472 "INSERT INTO table_name13(column_NAME1,"
473 + " COLUMN_name3, column_name2) VALUES"
474 + " ('value1', 'value3', 4)");
475 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
476 boolean result1 = statement.execute(
477 "INSERT INTO table_name13(column_NAME1,"
478 + " column_name2, COLUMN_name3) VALUES"
479 + " ('value1', 4, 'value5')");
480 //Assert.assertTrue("Wrong return for insert record", result1);
481 int count3 = statement.executeUpdate(
482 "INSERT INTO table_name13(column_name1,"
483 + " COLUMN_NAME3, column_NAME2) VALUES"
484 + " ('value2', 'value4', 5)");
485 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
486 int count4 = statement.executeUpdate(
487 "INSERT INTO table_name13(column_name1,"
488 + " COLUMN_NAME3, column_NAME2) VALUES"
489 + " ('value5', 'value6', 6)");
490 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
491
492 boolean result2 = statement.execute("SELECT DISTINCT"
493 + " column_name2 FROM table_name13");
494 Assert.assertTrue("Wrong return for select existing records", result2);
495 ResultSet res1 = statement.getResultSet();
496
497 int rows = 0;
498 while (res1.next())
499 rows++;
500 Assert.assertEquals("Wrong number of rows", 3, rows);
501
502 boolean result3 = statement
503 .execute("SELECT DISTINCT column_name2,"
504 + " column_name3 FROM table_name13"
505 + " WHERE coluMN_NAME2 < 5");
506 Assert.assertTrue("Wrong return for select existing records", result3);
507 ResultSet res2 = statement.getResultSet();
508
509 int rows2 = 0;
510 while (res2.next())
511 rows2++;
512 Assert.assertEquals("Wrong number of rows", 2, rows2);
513
514 statement.close();
515 } catch (Throwable e) {
516 TestRunner.fail("Failed to select distinct from table", e);
517 }
518 connection.close();
519 }
520
521 @Test
522 public void testAlterTable() throws SQLException {
523 Connection connection = createUseDatabase("TestDB_Create");
524 try {
525 Statement statement = connection.createStatement();
526 statement
527 .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
528 int count1 = statement.executeUpdate(
529 "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
530 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
531 boolean result1 = statement.execute(
532 "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
533 //Assert.assertTrue("Wrong return for insert record", result1);
534 int count3 = statement.executeUpdate(
535 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
536 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
537 int count4 = statement.executeUpdate(
538 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
539 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
540
541 boolean result2 = statement.execute("ALTER TABLE table_name13 ADD COLUMN column_name4 date");
542 Assert.assertFalse("Wrong return for ALTER TABLE", result2);
543 boolean result3 = statement.execute("SELECT column_name4 FROM table_name13 WHERE coluMN_NAME2 = 5");
544 Assert.assertTrue("Wrong return for select existing records", result3);
545 ResultSet res2 = statement.getResultSet();
546 int rows2 = 0;
547 while (res2.next())
548 rows2++;
549
550 Assert.assertEquals("Wrong number of rows", 1, rows2);
551
552 while (res2.previous())
553 ;
554 res2.next();
555
556 Assert.assertNull("Retrieved date is not null", res2.getDate("column_name4"));
557
558 statement.close();
559 } catch (Throwable e) {
560 TestRunner.fail("Failed to test ALTER TABLE from table", e);
561 }
562 connection.close();
563 }
564
565 @Test
566 public void testUnion() throws SQLException {
567 Connection connection = createUseDatabase("TestDB_Create");
568 try {
569 Statement statement = connection.createStatement();
570 statement
571 .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
572 int count1 = statement.executeUpdate(
573 "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
574 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
575 boolean result1 = statement.execute(
576 "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
577 //Assert.assertTrue("Wrong return for insert record", result1);
578 int count3 = statement.executeUpdate(
579 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
580 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
581 int count4 = statement.executeUpdate(
582 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
583 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
584
585 boolean result3 = statement.execute(
586 "SELECT * FROM table_name13 WHERE coluMN_NAME2 = 4 UNION SELECT * FROM table_name13 WHERE coluMN_NAME3 < 'value6'");
587 Assert.assertTrue("Wrong return for select UNION existing records", result3);
588 ResultSet res2 = statement.getResultSet();
589 int rows2 = 0;
590 while (res2.next())
591 rows2++;
592 Assert.assertEquals("Wrong number of rows", 3, rows2);
593
594 statement.close();
595 } catch (Throwable e) {
596 TestRunner.fail("Failed to test SELECT from table UNION", e);
597 }
598
599 connection.close();
600 }
601
602 @Test
603 public void testOrderBy() throws SQLException {
604 Connection connection = createUseDatabase("TestDB_Create");
605 try {
606 Statement statement = connection.createStatement();
607 statement
608 .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
609 int count1 = statement.executeUpdate(
610 "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
611 Assert.assertNotEquals("Insert returned zero rows", 0, count1);
612 boolean result1 = statement.execute(
613 "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
614 //Assert.assertTrue("Wrong return for insert record", result1);
615 int count3 = statement.executeUpdate(
616 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
617 Assert.assertNotEquals("Insert returned zero rows", 0, count3);
618 int count4 = statement.executeUpdate(
619 "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
620 Assert.assertNotEquals("Insert returned zero rows", 0, count4);
621
622 boolean result3 = statement
623 .execute("SELECT * FROM table_name13 ORDER BY column_name2 ASC, COLUMN_name3 DESC");
624 Assert.assertTrue("Wrong return for select UNION existing records", result3);
625 ResultSet res2 = statement.getResultSet();
626 int rows2 = 0;
627 while (res2.next())
628 rows2++;
629 Assert.assertEquals("Wrong number of rows", 4, rows2);
630
631 while (res2.previous());
632
633 res2.next();
634 Assert.assertEquals("Wrong order of rows", 4, res2.getInt("column_name2"));
635 Assert.assertEquals("Wrong order of rows", "value5", res2.getString("column_name3"));
636
637 res2.next();
638 Assert.assertEquals("Wrong order of rows", 4, res2.getInt("column_name2"));
639 Assert.assertEquals("Wrong order of rows", "value3", res2.getString("column_name3"));
640
641 res2.next();
642 Assert.assertEquals("Wrong order of rows", 5, res2.getInt("column_name2"));
643
644 statement.close();
645 } catch (Throwable e) {
646 TestRunner.fail("Failed to test ORDER By", e);
647 }
648 connection.close();
649 }
650
651// @Test
652// public void testDistinct() throws SQLException {
653// Connection connection = createUseDatabase("TestDB_Create");
654// try {
655// Statement statement = connection.createStatement();
656// statement
657// .execute("CREATE TABLE table_name13(column_name1 varchar,"
658// + " column_name2 int, column_name3 varchar)");
659// int count1 = statement.executeUpdate(
660// "INSERT INTO table_name13(column_NAME1,"
661// + " COLUMN_name3, column_name2) VALUES"
662// + " ('value1', 'value3', 4)");
663// Assert.assertNotEquals("Insert returned zero rows", 0, count1);
664// boolean result1 = statement.execute(
665// "INSERT INTO table_name13(column_NAME1,"
666// + " column_name2, COLUMN_name3) VALUES"
667// + " ('value1', 4, 'value5')");
668// Assert.assertTrue("Wrong return for insert record", result1);
669// int count3 = statement.executeUpdate(
670// "INSERT INTO table_name13(column_name1,"
671// + " COLUMN_NAME3, column_NAME2) VALUES"
672// + " ('value2', 'value4', 5)");
673// Assert.assertNotEquals("Insert returned zero rows", 0, count3);
674// int count4 = statement.executeUpdate(
675// "INSERT INTO table_name13(column_name1,"
676// + " COLUMN_NAME3, column_NAME2) VALUES"
677// + " ('value5', 'value6', 6)");
678// Assert.assertNotEquals("Insert returned zero rows", 0, count4);
679//
680// boolean result2 = statement.execute("SELECT DISTINCT"
681// + " column_name2 FROM table_name13");
682// Assert.assertTrue("Wrong return for select existing records", result2);
683// ResultSet res1 = statement.getResultSet();
684//
685// int rows = 0;
686// while (res1.next())
687// rows++;
688// Assert.assertEquals("Wrong number of rows", 3, rows);
689//
690// boolean result3 = statement
691// .execute("SELECT DISTINCT column_name2,"
692// + " column_name3 FROM table_name13"
693// + " WHERE coluMN_NAME2 < 5");
694// Assert.assertTrue("Wrong return for select existing records", result3);
695// ResultSet res2 = statement.getResultSet();
696//
697// int rows2 = 0;
698// while (res2.next())
699// rows2++;
700// Assert.assertEquals("Wrong number of rows", 2, rows2);
701//
702// statement.close();
703// } catch (Throwable e) {
704// TestRunner.fail("Failed to select distinct from table", e);
705// }
706// connection.close();
707// }
708//
709// @Test
710// public void testAlterTable() throws SQLException {
711// Connection connection = createUseDatabase("TestDB_Create");
712// try {
713// Statement statement = connection.createStatement();
714// statement
715// .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
716// int count1 = statement.executeUpdate(
717// "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
718// Assert.assertNotEquals("Insert returned zero rows", 0, count1);
719// boolean result1 = statement.execute(
720// "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
721// Assert.assertTrue("Wrong return for insert record", result1);
722// int count3 = statement.executeUpdate(
723// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
724// Assert.assertNotEquals("Insert returned zero rows", 0, count3);
725// int count4 = statement.executeUpdate(
726// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
727// Assert.assertNotEquals("Insert returned zero rows", 0, count4);
728//
729// boolean result2 = statement.execute("ALTER TABLE table_name13 ADD COLUMN column_name4 date");
730// Assert.assertFalse("Wrong return for ALTER TABLE", result2);
731//
732// boolean result3 = statement.execute("SELECT column_name4 FROM table_name13 WHERE coluMN_NAME2 = 5");
733// Assert.assertTrue("Wrong return for select existing records", result3);
734// ResultSet res2 = statement.getResultSet();
735// int rows2 = 0;
736// while (res2.next())
737// rows2++;
738// Assert.assertEquals("Wrong number of rows", 1, rows2);
739//
740// while (res2.previous())
741// ;
742// res2.next();
743//
744// Assert.assertNull("Retrieved date is not null", res2.getDate("column_name4"));
745//
746// statement.close();
747// } catch (Throwable e) {
748// TestRunner.fail("Failed to test ALTER TABLE from table", e);
749// }
750// connection.close();
751// }
752//
753// @Test
754// public void testUnion() throws SQLException {
755// Connection connection = createUseDatabase("TestDB_Create");
756// try {
757// Statement statement = connection.createStatement();
758// statement
759// .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
760// int count1 = statement.executeUpdate(
761// "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
762// Assert.assertNotEquals("Insert returned zero rows", 0, count1);
763// boolean result1 = statement.execute(
764// "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
765// Assert.assertTrue("Wrong return for insert record", result1);
766// int count3 = statement.executeUpdate(
767// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
768// Assert.assertNotEquals("Insert returned zero rows", 0, count3);
769// int count4 = statement.executeUpdate(
770// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
771// Assert.assertNotEquals("Insert returned zero rows", 0, count4);
772//
773// boolean result3 = statement.execute(
774// "SELECT * FROM table_name13 WHERE coluMN_NAME2 = 4 UNION SELECT * FROM table_name13 WHERE coluMN_NAME3 < 'value6'");
775// Assert.assertTrue("Wrong return for select UNION existing records", result3);
776// ResultSet res2 = statement.getResultSet();
777// int rows2 = 0;
778// while (res2.next())
779// rows2++;
780// Assert.assertEquals("Wrong number of rows", 3, rows2);
781//
782// statement.close();
783// } catch (Throwable e) {
784// TestRunner.fail("Failed to test SELECT from table UNION", e);
785// }
786// connection.close();
787// }
788//
789// @Test
790// public void testOrderBy() throws SQLException {
791// Connection connection = createUseDatabase("TestDB_Create");
792// try {
793// Statement statement = connection.createStatement();
794// statement
795// .execute("CREATE TABLE table_name13(column_name1 varchar, column_name2 int, column_name3 varchar)");
796// int count1 = statement.executeUpdate(
797// "INSERT INTO table_name13(column_NAME1, COLUMN_name3, column_name2) VALUES ('value1', 'value3', 4)");
798// Assert.assertNotEquals("Insert returned zero rows", 0, count1);
799// boolean result1 = statement.execute(
800// "INSERT INTO table_name13(column_NAME1, column_name2, COLUMN_name3) VALUES ('value1', 4, 'value5')");
801// Assert.assertTrue("Wrong return for insert record", result1);
802// int count3 = statement.executeUpdate(
803// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value2', 'value4', 5)");
804// Assert.assertNotEquals("Insert returned zero rows", 0, count3);
805// int count4 = statement.executeUpdate(
806// "INSERT INTO table_name13(column_name1, COLUMN_NAME3, column_NAME2) VALUES ('value5', 'value6', 6)");
807// Assert.assertNotEquals("Insert returned zero rows", 0, count4);
808//
809// boolean result3 = statement
810// .execute("SELECT * FROM table_name13 ORDER BY column_name2 ASC, COLUMN_name3 DESC");
811// Assert.assertTrue("Wrong return for select UNION existing records", result3);
812// ResultSet res2 = statement.getResultSet();
813// int rows2 = 0;
814// while (res2.next())
815// rows2++;
816// Assert.assertEquals("Wrong number of rows", 4, rows2);
817//
818// while (res2.previous())
819// ;
820//
821// res2.next();
822// Assert.assertEquals("Wrong order of rows", 4, res2.getInt("column_name2"));
823// Assert.assertEquals("Wrong order of rows", "value5", res2.getString("column_name3"));
824//
825// res2.next();
826// Assert.assertEquals("Wrong order of rows", 4, res2.getInt("column_name2"));
827// Assert.assertEquals("Wrong order of rows", "value3", res2.getString("column_name3"));
828//
829// res2.next();
830// Assert.assertEquals("Wrong order of rows", 5, res2.getInt("column_name2"));
831//
832// statement.close();
833// } catch (Throwable e) {
834// TestRunner.fail("Failed to test ORDER By", e);
835// }
836// connection.close();
837// }
838
839}