API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 8th, 2016
86
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 34.89 KB | None | 0 0
raw download clone embed print report
  1. package jdbc.tests;
  2.  
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.sql.Connection;
  6. import java.sql.Driver;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. import java.util.Properties;
  11.  
  12. import org.junit.Assert;
  13. import 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. **/
  27. public 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. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!