|
1
17
18 package org.sape.carbon.services.sql.test;
19
20 import java.sql.CallableStatement  ;
21 import java.sql.Connection ;
22 import java.sql.DatabaseMetaData ;
23 import java.sql.PreparedStatement ;
24 import java.sql.ResultSet ;
25 import java.sql.SQLException ;
26 import java.util.Random ;
27
28 import org.sape.carbon.core.component.Lookup;
29 import org.sape.carbon.core.config.Config;
30 import org.sape.carbon.services.sql.StatementFactory;
31 import org.sape.carbon.services.sql.StatementFactoryConfiguration;
32 import org.sape.carbon.services.sql.StatementFactoryException;
33
34 import junit.framework.Test;
35 import junit.framework.TestCase;
36 import junit.framework.TestSuite;
37
38 import org.apache.commons.logging.Log;
39 import org.apache.commons.logging.LogFactory;
40
41
49 public class StatementFactoryTest extends TestCase {
50
51
54 private Log log = LogFactory.getLog(this.getClass());
55
56 public static final String STATEMENT_FACTORY
57 = "/sql/test/DefaultStatementFactoryTest";
58 public static final String CREATE_TABLE_ONE_QUERY = "CreateTableOne";
59 public static final String CREATE_TABLE_TWO_QUERY = "CreateTableTwo";
60 public static final String CREATE_TABLE_THREE_QUERY = "CreateTableThree";
61 public static final String CREATE_TABLE_FOUR_QUERY = "CreateTableFour";
62 public static final String POPULATE_DATA_ONE_QUERY = "PopulateDataOne";
63 public static final String POPULATE_DATA_TWO_QUERY = "PopulateDataTwo";
64 public static final String POPULATE_DATA_THREE_QUERY
65 = "PopulateDataThree";
66 public static final String POPULATE_DATA_FOUR_QUERY = "PopulateDataFour";
67 public static final String DROP_TABLE_ONE_QUERY = "DropTableOne";
68 public static final String DROP_TABLE_TWO_QUERY = "DropTableTwo";
69 public static final String DROP_TABLE_THREE_QUERY = "DropTableThree";
70 public static final String DROP_TABLE_FOUR_QUERY = "DropTableFour";
71 public static final String PREPARED_STATEMENT_ONE_ROW_QUERY = "PSOR";
72 public static final String PSOR_NAME_FIELD = "name";
73 public static final String PSOR_VALUE_FIELD = "value";
74 public static final String PSOR_NAME_EXPECTED = "testname";
75 public static final int PSOR_VALUE_EXPECTED = 12345;
76 public static final String PREPARED_STATEMENT_MULTI_ROWS_QUERY = "PSMR";
77 public static final int PSMR_ROWS_EXPECTED = 101;
78 public static final String FUNCTION_CALLABLE_QUERY = "FunctionCallable";
79 public static final String PROCEDURE_CALLABLE_QUERY = "ProcedureCallable";
80 public static final String NULL_READ_QUERY = "NullRead";
81 public static final String MAX_ROWS_QUERY= "MaxRows";
82 public static final int MR_ROWS_EXPECTED = 10;
83 public static final String FIELD_SIZE_QUERY = "FieldSize";
84 public static final int FS_EXPECTED = 20;
85 public static final String SCROLL_INSENSITIVE_QUERY= "ScrollInsensitive";
86 public static final String SCROLL_SENSITIVE_QUERY= "ScrollSensitive";
87 public static final String CONCUR_UPDATABLE_QUERY= "ConcurUpdatable";
88 public static final String CONCUR_READONLY_QUERY= "ConcurReadonly";
89 public static final String CREATE_PACKAGE_DEFINITION_QUERY
90 = "CreatePackageDefinition";
91 public static final String CREATE_PACKAGE_BODY_QUERY="CreatePackageBody";
92
93 public StatementFactoryTest(String name) {
94 super(name);
95 }
96
97
98
99
104 public void getDriverInfo() {
105 try {
106 StatementFactoryConfiguration sfConfig = (StatementFactoryConfiguration)
107 Config.getInstance().fetchConfiguration(STATEMENT_FACTORY);
108 Connection conn = (sfConfig.getConnectionFactory()).getConnection();
109 DatabaseMetaData dbmd = conn.getMetaData();
110
111 if (log.isInfoEnabled()) {
112 log.info("Database Metadata: ");
113 log.info("\tDatabase: " + dbmd.getDatabaseProductName());
114 log.info("Driver Metadata: ");
115 log.info("\tDriver: " + dbmd.getDriverName());
116 log.info("\tDriver Version: " + dbmd.getDriverVersion());
117 log.info("\tDefault Transaction Isolation: "
118 + conn.getTransactionIsolation());
119 log.info("\tTypes Map: " + conn.getTypeMap());
120 }
121 } catch (SQLException se) {
122 se.printStackTrace();
123 }
124 }
125
126
127
130 public void cleanUpBeforeStart() {
131 PreparedStatement preparedStatement=null;
132 try {
133 StatementFactory sf = (StatementFactory)
134 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
135
136 try {
137 preparedStatement =
138 sf.createPreparedStatement(DROP_TABLE_ONE_QUERY);
139 preparedStatement.executeUpdate();
140 } catch (SQLException se) {
141 }
143
144 try {
145 preparedStatement =
146 sf.createPreparedStatement(DROP_TABLE_TWO_QUERY);
147 preparedStatement.executeUpdate();
148 } catch (SQLException se) {
149 }
151 try {
152 preparedStatement =
153 sf.createPreparedStatement(DROP_TABLE_THREE_QUERY);
154 preparedStatement.executeUpdate();
155 } catch (SQLException se) {
156 }
158 try {
159 preparedStatement =
160 sf.createPreparedStatement(DROP_TABLE_FOUR_QUERY);
161 preparedStatement.executeUpdate();
162 } catch (SQLException se) {
163 }
165 } catch (StatementFactoryException sfe) {
166 fail("Clean up before start operation failed due to "+sfe);
167 }
168 }
169
170
171
174
175 public void createTestTables() {
176 PreparedStatement preparedStatement=null;
177
178 try {
179
180 StatementFactory sf = (StatementFactory)
181 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
182
183 preparedStatement =
184 sf.createPreparedStatement(CREATE_TABLE_ONE_QUERY);
185 preparedStatement.executeUpdate();
186 preparedStatement =
187 sf.createPreparedStatement(CREATE_TABLE_TWO_QUERY);
188 preparedStatement.executeUpdate();
189 preparedStatement =
190 sf.createPreparedStatement(CREATE_TABLE_THREE_QUERY);
191 preparedStatement.executeUpdate();
192 preparedStatement =
193 sf.createPreparedStatement(CREATE_TABLE_FOUR_QUERY);
194 preparedStatement.executeUpdate();
195
196 } catch (StatementFactoryException sfe) {
197 fail("Cannot create Test Tables:"+sfe);
198 } catch (SQLException se) {
199 fail("Cannot create Test Tables"+se);
200 }
201
202 }
203
204
207 public void createTestData() {
208
209 PreparedStatement preparedStatement=null;
210 int result;
211 Random rand = new Random ();
212
213 try {
214 StatementFactory sf = (StatementFactory)
215 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
216
217 preparedStatement =
218 sf.createPreparedStatement(POPULATE_DATA_FOUR_QUERY);
219 preparedStatement.setString(1,PSOR_NAME_EXPECTED);
220 preparedStatement.setInt(2,PSOR_VALUE_EXPECTED);
221 result = preparedStatement.executeUpdate();
222 for (int i=0;i<100;i++) {
223 preparedStatement =
224 sf.createPreparedStatement(POPULATE_DATA_FOUR_QUERY);
225 int id = rand.nextInt(1000);
226 preparedStatement.setString(1,"Statement ID:"+i);
227 preparedStatement.setInt(2,id);
228 result = preparedStatement.executeUpdate();
229 if (result!=1) {
230 fail("Insert operation Failed");
231 }
232 preparedStatement.close();
233 }
234
235 } catch (StatementFactoryException sfe) {
236 fail("Cannot insert Test Data:"+sfe);
237 } catch (SQLException se) {
238 fail("Cannot insert Test Data:"+se);
239 }
240
241 }
242
243
246 public void cleanUp() {
247 PreparedStatement preparedStatement=null;
248 try {
249 StatementFactory sf = (StatementFactory)
250 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
251
252 preparedStatement =
253 sf.createPreparedStatement(DROP_TABLE_ONE_QUERY);
254 preparedStatement.executeUpdate();
255 preparedStatement =
256 sf.createPreparedStatement(DROP_TABLE_TWO_QUERY);
257 preparedStatement.executeUpdate();
258 preparedStatement =
259 sf.createPreparedStatement(DROP_TABLE_THREE_QUERY);
260 preparedStatement.executeUpdate();
261 preparedStatement =
262 sf.createPreparedStatement(DROP_TABLE_FOUR_QUERY);
263 preparedStatement.executeUpdate();
264
265 } catch (StatementFactoryException sfe) {
266 fail("Clean up operation failed due to "+sfe);
267 } catch (SQLException se) {
268 fail("Clean up operation failed due to "+se);
269 }
270 }
271
272
273
276 public void testPreparedStatementOneRow() {
277 PreparedStatement preparedStatement=null;
278 ResultSet resultSet;
279 try {
280 StatementFactory sf = (StatementFactory)
281 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
282
283 preparedStatement =
284 sf.createPreparedStatement(PREPARED_STATEMENT_ONE_ROW_QUERY);
285
286 preparedStatement.setString(1,PSOR_NAME_EXPECTED);
287 resultSet = preparedStatement.executeQuery();
288
289 if (resultSet.next()) {
290 String name = resultSet.getString(PSOR_NAME_FIELD);
291 int value = resultSet.getInt(PSOR_VALUE_FIELD);
292 if (!name.equals(PSOR_NAME_EXPECTED)) {
293 fail("Prepared Statement test returned name='"+name+
294 "' but '"+PSOR_NAME_EXPECTED+"' was expected ");
295 }
296 if (value!=PSOR_VALUE_EXPECTED) {
297 fail("Prepared Statement test returned value='"+value+
298 "' but '"+PSOR_VALUE_EXPECTED+"' was expected ");
299 }
300 } else {
301 fail("Prepared Statement test :Query returned no rows");
302 }
303
304 } catch (StatementFactoryException sfe) {
305 fail("Failed due to "+sfe);
306 } catch (SQLException se) {
307 fail("Failed due to "+se);
308 }
309 }
310
311
314 public void testPreparedStatementMultiRow() {
315
316 try {
317 StatementFactory sf = (StatementFactory)
318 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
319
320 PreparedStatement preparedStatement =
321 sf.createPreparedStatement(PREPARED_STATEMENT_MULTI_ROWS_QUERY);
322
323 ResultSet resultSet = preparedStatement.executeQuery();
324
325 int numberofRows = 0;
326 while (resultSet.next()){
327 numberofRows ++;
328 }
329 if (numberofRows!=PSMR_ROWS_EXPECTED) {
330 fail("Prepared Statement multi row, number of rows ='"+
331 numberofRows+"' but '"+PSMR_ROWS_EXPECTED+
332 "' were expected ");
333 }
334
335 } catch (StatementFactoryException sfe) {
336 fail("Failed due to "+sfe);
337 } catch (SQLException se) {
338 fail("Failed due to "+se);
339 }
340 }
341
342
345 public void createTestPackage() throws StatementFactoryException, SQLException {
346 PreparedStatement preparedStatement=null;
347 int result;
348 try {
349 StatementFactory sf = (StatementFactory)
350 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
351 preparedStatement =
352 sf.createPreparedStatement(CREATE_PACKAGE_DEFINITION_QUERY);
353 preparedStatement.executeUpdate();
354 preparedStatement.close();
355 preparedStatement =
356 sf.createPreparedStatement(CREATE_PACKAGE_BODY_QUERY);
357 preparedStatement.executeUpdate();
358 } finally {
359 try {
360 if (preparedStatement!=null) {
361 preparedStatement.close();
362 }
363 } catch (SQLException se) {
364 }
366 }
367
368
369 }
370
371
374 public void testFunctionCallableStatement() {
375 CallableStatement callableStatement = null;
376 try {
377 StatementFactory sf = (StatementFactory)
378 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
379
380 callableStatement =
381 sf.createCallableStatement(FUNCTION_CALLABLE_QUERY);
382 callableStatement.registerOutParameter(1,java.sql.Types.VARCHAR);
383 String data = "Hello World!";
384 callableStatement.setString(2,data);
385 callableStatement.executeQuery();
386 String result = (String ) callableStatement.getObject(1);
387 if (!result.equals("prefix."+data)) {
388 fail ("testFunctionCallableStatement failed , expected 'prefix."
389 +data+"' got '"+result+"'");
390 }
391 } catch (StatementFactoryException sfe) {
392 fail("Clean up operation failed due to "+sfe);
393 } catch (SQLException se) {
394 fail("Clean up operation failed due to "+se);
395 } finally {
396 try {
397 if (callableStatement!=null) {
398 callableStatement.close();
399 }
400 } catch (SQLException se) {
401 }
403 }
404 }
405
406
409 public void testProcedureCallableStatement() {
410 CallableStatement callableStatement = null;
411 try {
412 StatementFactory sf = (StatementFactory)
413 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
414
415 callableStatement =
416 sf.createCallableStatement(PROCEDURE_CALLABLE_QUERY);
417
418 callableStatement.registerOutParameter(2,java.sql.Types.VARCHAR);
419 callableStatement.registerOutParameter(3,java.sql.Types.VARCHAR);
420 String data = "Hello World!";
421 callableStatement.setString(1,data);
422 callableStatement.executeQuery();
423 String resultA = (String ) callableStatement.getObject(2);
424 String resultB = (String ) callableStatement.getObject(3);
425 if (!(resultA.equals("H") && resultB.equals("e"))) {
426 fail("Got some error resultA='"+resultA+"' and resultB='"
427 +resultB+"'");
428 }
429
430 } catch (StatementFactoryException sfe) {
431 fail("Clean up operation failed due to "+sfe);
432 } catch (SQLException se) {
433 fail("Clean up operation failed due to "+se);
434 } finally {
435 try {
436 if (callableStatement!=null) {
437 callableStatement.close();
438 }
439
440 } catch (SQLException se) { }
442 }
443 }
444
445
448 public void testNullReads() {
449 ResultSet resultSet;
450 PreparedStatement preparedStatement=null;
451 try {
452
453 StatementFactory sf = (StatementFactory)
454 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
455
456 preparedStatement =
457 sf.createPreparedStatement(NULL_READ_QUERY);
458
459 resultSet = preparedStatement.executeQuery();
460
461 if (resultSet.next()) {
462 if (( resultSet.getInt(1) != 0 ) || (!resultSet.wasNull())) {
463 fail("Null read test failed.");
464 }
465 }
466
467 } catch (StatementFactoryException sfe) {
468 fail("Failed due to "+sfe);
469 } catch (SQLException se) {
470 fail("Failed due to "+se);
471 } finally {
472 try {
473 if (preparedStatement!=null) {
474 preparedStatement.close();
475 }
476 } catch (SQLException se) {
477 }
479 }
480
481 }
482
483
486 public void testMaxRows() {
487
488 PreparedStatement preparedStatement=null;
489 ResultSet resultSet;
490
491 try {
492 StatementFactory sf = (StatementFactory)
493 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
494
495 preparedStatement =
496 sf.createPreparedStatement(MAX_ROWS_QUERY);
497
498 resultSet = preparedStatement.executeQuery();
499
500 int numberofRows = 0;
501 while (resultSet.next()){
502 numberofRows ++;
503 }
504
505 if (numberofRows!=MR_ROWS_EXPECTED) {
506 fail("Result Set not limited");
507 }
508
509 } catch (StatementFactoryException sfe) {
510 fail("Failed due to "+sfe);
511 } catch (SQLException se) {
512 fail("Failed due to "+se);
513 } finally {
514 try {
515 if (preparedStatement!=null) {
516 preparedStatement.close();
517 }
518 } catch (SQLException se) { }
520 }
521
522 }
523
524
527 public void testFieldSize() {
528 PreparedStatement preparedStatement=null;
529 ResultSet resultSet;
530
531 try {
532 StatementFactory sf = (StatementFactory)
533 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
534
535 preparedStatement =
536 sf.createPreparedStatement(FIELD_SIZE_QUERY);
537
538 resultSet = preparedStatement.executeQuery();
539
540 while (resultSet.next()) {
541 String data = (String ) resultSet.getObject(1);
542 if (data.length()>FS_EXPECTED) {
543 fail("Field size limit not respected.");
544 }
545 }
546
547 } catch (StatementFactoryException sfe) {
548 fail("Failed due to "+sfe);
549 } catch (SQLException se) {
550 fail("Failed due to "+se);
551 } finally {
552 try {
553 if (preparedStatement!=null) {
554 preparedStatement.close();
555 }
556 } catch (SQLException se) { }
558 }
559
560 }
561
562
565 public void testScrollInsensitive() {
566 PreparedStatement preparedStatement=null;
567 ResultSet resultSet;
568
569 try {
570 StatementFactory sf = (StatementFactory)
571 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
572
573 preparedStatement =
574 sf.createPreparedStatement(SCROLL_INSENSITIVE_QUERY);
575
576 resultSet = preparedStatement.executeQuery();
577
578 if (!resultSet.next()) {
579 fail("Error reading data from scroll insensitive result set.");
580 }
581
582 } catch (StatementFactoryException sfe) {
583 fail("Failed due to "+sfe);
584 } catch (SQLException se) {
585 fail("Failed due to "+se);
586 } finally {
587 try {
588 if (preparedStatement!=null) {
589 preparedStatement.close();
590 }
591 } catch (SQLException se) { }
593 }
594
595 }
596
597
600 public void testScrollSensitive() {
601 PreparedStatement preparedStatement=null;
602 ResultSet resultSet;
603
604 try {
605 StatementFactory sf = (StatementFactory)
606 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
607
608 preparedStatement =
609 sf.createPreparedStatement(SCROLL_SENSITIVE_QUERY);
610
611 resultSet = preparedStatement.executeQuery();
612
613 if (!resultSet.next()) {
614 fail("Error reading data from scroll sensitive result set.");
615 } else {
616 int totalRecords = resultSet.getFetchSize();
617 boolean isScrollable = resultSet.absolute((int)(totalRecords/2));
618 assertTrue("Cannot scroll a scrollable result set", isScrollable);
619 if (!resultSet.previous()) {
620 fail("Error scrolling backward");
621 }
622 if (!resultSet.next()) {
623 fail("Error scrolling forward");
624 }
625 }
626
627 } catch (StatementFactoryException sfe) {
628 fail("Failed due to "+sfe);
629 } catch (SQLException se) {
630 fail("Failed due to "+se);
631 } finally {
632 try {
633 if (preparedStatement!=null) {
634 preparedStatement.close();
635 }
636 } catch (SQLException se) { }
638 }
639
640 }
641
642
645 public void testConcurUpdatable() {
646 PreparedStatement preparedStatement=null;
647 ResultSet resultSet;
648
649 try {
650 StatementFactory sf = (StatementFactory)
651 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
652
653 preparedStatement =
654 sf.createPreparedStatement(CONCUR_UPDATABLE_QUERY);
655
656 resultSet = preparedStatement.executeQuery();
657
658 if (!resultSet.next()) {
659 fail("Error reading data from concur_updatable result set.");
660 }
661
662 } catch (StatementFactoryException sfe) {
663 fail("Failed due to "+sfe);
664 } catch (SQLException se) {
665 fail("Failed due to "+se);
666 } finally {
667 try {
668 if (preparedStatement!=null) {
669 preparedStatement.close();
670 }
671 } catch (SQLException se) { }
673 }
674
675 }
676
677
680 public void testConcurReadOnly() {
681 PreparedStatement preparedStatement=null;
682 ResultSet resultSet;
683
684 try {
685 StatementFactory sf = (StatementFactory)
686 Lookup.getInstance().fetchComponent(STATEMENT_FACTORY);
687
688 preparedStatement =
689 sf.createPreparedStatement(CONCUR_READONLY_QUERY);
690
691 resultSet = preparedStatement.executeQuery();
692
693 if (!resultSet.next()) {
694 fail("Error reading data from concur_readonly result set.");
695 }
696
697 } catch (StatementFactoryException sfe) {
698 fail("Failed due to "+sfe);
699 } catch (SQLException se) {
700 fail("Failed due to "+se);
701 } finally {
702 try {
703 if (preparedStatement!=null) {
704 preparedStatement.close();
705 }
706 } catch (SQLException se) { }
708 }
709
710 }
711
712
716 public static Test suite() {
717 TestSuite test = new TestSuite();
718 test.addTest(new StatementFactoryTest("getDriverInfo"));
719 test.addTest(new StatementFactoryTest("cleanUpBeforeStart"));
720 test.addTest(new StatementFactoryTest("createTestTables"));
721 test.addTest(new StatementFactoryTest("createTestData"));
722 test.addTest(new StatementFactoryTest("testPreparedStatementOneRow"));
723 test.addTest(new StatementFactoryTest("testPreparedStatementMultiRow"));
724 test.addTest(new StatementFactoryTest("createTestPackage"));
725 test.addTest(new StatementFactoryTest("testFunctionCallableStatement"));
726 test.addTest(new StatementFactoryTest("testProcedureCallableStatement"));
727 test.addTest(new StatementFactoryTest("testNullReads"));
728 test.addTest(new StatementFactoryTest("testMaxRows"));
729 test.addTest(new StatementFactoryTest("testFieldSize"));
730 test.addTest(new StatementFactoryTest("testScrollInsensitive"));
731 test.addTest(new StatementFactoryTest("testScrollSensitive"));
732 test.addTest(new StatementFactoryTest("testConcurUpdatable"));
733 test.addTest(new StatementFactoryTest("testConcurReadOnly"));
734 test.addTest(new StatementFactoryTest("cleanUp"));
735 return test;
736
737 }
738
739
740 }
741
742 |
Popular Tags |
Java API By Example, From Geeks To Geeks.