KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > QueryTest


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: QueryTest.java,v 1.14 2007/01/07 06:14:51 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */

21
22 package org.opensubsystems.core.persist.db.driver;
23
24 import java.sql.PreparedStatement JavaDoc;
25 import java.sql.ResultSet JavaDoc;
26 import java.sql.SQLException JavaDoc;
27
28 import junit.extensions.TestSetup;
29 import junit.framework.Test;
30 import junit.framework.TestSuite;
31
32 import org.opensubsystems.core.error.OSSException;
33 import org.opensubsystems.core.persist.db.Database;
34 import org.opensubsystems.core.persist.db.DatabaseImpl;
35 import org.opensubsystems.core.persist.db.DatabaseSchemaManager;
36 import org.opensubsystems.core.persist.db.DatabaseTest;
37 import org.opensubsystems.core.persist.db.DatabaseTestSetup;
38 import org.opensubsystems.core.persist.db.DatabaseTestSuite;
39 import org.opensubsystems.core.util.DatabaseUtils;
40
41 /**
42  * All tests related to sql queries problems.
43  *
44  * @version $Id: QueryTest.java,v 1.14 2007/01/07 06:14:51 bastafidli Exp $
45  * @author Peter Satury
46  * @code.reviewer Miro Halas
47  * @code.reviewed 1.12 2005/09/09 06:50:44 bastafidli
48  */

49 public final class QueryTest
50 {
51    // Constructors /////////////////////////////////////////////////////////////
52

53    /**
54     * Private constructor since this class cannot be instantiated
55     */

56    private QueryTest(
57    )
58    {
59       // Do nothing
60
}
61    
62    // Public methods ///////////////////////////////////////////////////////////
63

64    /**
65     * Create the suite for this test since this is the only way how to create
66     * test setup which can initialize and shutdown the database for us
67     *
68     * @return Test - suite of tests to run for this database
69     */

70    public static Test suite(
71    )
72    {
73       TestSuite suite = new DatabaseTestSuite("QueryTest");
74       suite.addTestSuite(QueryTestInternal.class);
75       TestSetup wrapper = new DatabaseTestSetup(suite);
76
77       return wrapper;
78    }
79
80    /**
81     * Internal class which can be included in other test suites directly without
82     * including the above suite. This allows us to group multiple tests
83     * together and the execute the DatabaseTestSetup only once
84     */

85    public static class QueryTestInternal extends DatabaseTest
86    {
87       /**
88        * Static initializer
89        */

90       static
91       {
92          // This test use special database schema so make the database aware of it
93
Database dbDatabase;
94    
95          try
96          {
97             dbDatabase = DatabaseImpl.getInstance();
98             // Add schema database tests needs to the database
99
dbDatabase.add(DatabaseTestSchema.class);
100          }
101          catch (OSSException bfeExc)
102          {
103             throw new RuntimeException JavaDoc("Unexpected exception.", bfeExc);
104          }
105       }
106       
107       /**
108        * Create new test.
109        *
110        * @param strTestName - name of the test
111        */

112       public QueryTestInternal(
113          String JavaDoc strTestName
114       )
115       {
116          super(strTestName);
117       }
118       
119       /**
120        * Test if the join with also other conditions in ON part will go through
121        * In case that no join data there also should be result row
122        *
123        * @throws Throwable - an error has occured during test
124        */

125       public void testJoinQuery(
126       ) throws Throwable JavaDoc
127       {
128          final String JavaDoc INSERT_BASE = "insert into GROUP_BASE_TEST values (?,?)";
129          final String JavaDoc DELETE_BASE = "delete from GROUP_BASE_TEST";
130          final String JavaDoc INSERT_CHILD = "insert into GROUP_CHILD_TEST values (?,?,?)";
131          final String JavaDoc DELETE_CHILD = "delete from GROUP_CHILD_TEST";
132          
133          final String JavaDoc SELECT = "select GROUP_BASE_TEST.TEST_BASE_ID, " +
134                                "GROUP_CHILD_TEST.TEST_CHILD_VALUE " +
135                                "from GROUP_BASE_TEST " +
136                                "left join GROUP_CHILD_TEST " +
137                                "on GROUP_CHILD_TEST.TEST_BASE_FK_ID=GROUP_BASE_TEST.TEST_BASE_ID " +
138                                "and GROUP_CHILD_TEST.TEST_CHILD_VALUE=? " +
139                                "where GROUP_BASE_TEST.TEST_BASE_ID=?";
140                                        
141          PreparedStatement JavaDoc insertStatement = null;
142          PreparedStatement JavaDoc deleteStatement = null;
143          PreparedStatement JavaDoc selectStatement = null;
144          ResultSet JavaDoc rsResults = null;
145          
146          try
147          {
148             m_transaction.begin();
149             try
150             {
151                insertStatement = m_connection.prepareStatement(INSERT_BASE);
152                insertStatement.setInt(1, 1);
153                insertStatement.setInt(2, 1);
154                insertStatement.execute();
155                
156                insertStatement.setInt(1, 2);
157                insertStatement.setInt(2, 2);
158                insertStatement.execute();
159    
160                insertStatement = m_connection.prepareStatement(INSERT_CHILD);
161                insertStatement.setInt(1, 1);
162                insertStatement.setInt(2, 1);
163                insertStatement.setInt(3, 1);
164                insertStatement.execute();
165    
166                insertStatement.setInt(1, 2);
167                insertStatement.setInt(2, 1);
168                insertStatement.setInt(3, 2);
169                insertStatement.execute();
170    
171                insertStatement.setInt(1, 3);
172                insertStatement.setInt(2, 2);
173                insertStatement.setInt(3, 3);
174                insertStatement.execute();
175    
176                m_transaction.commit();
177             }
178             catch (Throwable JavaDoc throwable)
179             {
180                m_transaction.rollback();
181                throw throwable;
182             }
183             finally
184             {
185                DatabaseUtils.closeStatement(insertStatement);
186             }
187             
188             try
189             {
190                selectStatement = m_connection.prepareStatement(SELECT);
191                selectStatement.setInt(1, 3);
192                selectStatement.setInt(2, 1);
193                rsResults = selectStatement.executeQuery();
194                
195                assertTrue("There have to be rows in result", rsResults.next());
196             }
197             finally
198             {
199                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
200             }
201             
202          }
203          finally
204          {
205             m_transaction.begin();
206             try
207             {
208                deleteStatement = m_connection.prepareStatement(DELETE_CHILD);
209                deleteStatement.execute();
210                deleteStatement = m_connection.prepareStatement(DELETE_BASE);
211                deleteStatement.execute();
212                m_transaction.commit();
213             }
214             catch (Throwable JavaDoc thr)
215             {
216                m_transaction.rollback();
217                throw new Exception JavaDoc(thr);
218             }
219             finally
220             {
221                DatabaseUtils.closeStatement(deleteStatement);
222             }
223          }
224       }
225       
226       /**
227        * Test if the group will be correct if no specified data in DB
228        * In that case there should be no row in result
229        *
230        * @throws Throwable - an error has occured during test
231        */

232       public void testGroupQuery(
233       ) throws Throwable JavaDoc
234       {
235          final String JavaDoc INSERT_BASE = "insert into GROUP_BASE_TEST values (?,?)";
236          final String JavaDoc DELETE_BASE = "delete from GROUP_BASE_TEST";
237          final String JavaDoc INSERT_CHILD = "insert into GROUP_CHILD_TEST values (?,?,?)";
238          final String JavaDoc DELETE_CHILD = "delete from GROUP_CHILD_TEST";
239          
240          final String JavaDoc SELECT = "select GROUP_BASE_TEST.TEST_BASE_ID," +
241                           "GROUP_BASE_TEST.TEST_BASE_VALUE," +
242                           "sum(GROUP_CHILD_TEST.TEST_CHILD_VALUE) " +
243                           "from GROUP_BASE_TEST, GROUP_CHILD_TEST " +
244                           "where GROUP_CHILD_TEST.TEST_BASE_FK_ID=GROUP_BASE_TEST.TEST_BASE_ID " +
245                           "and GROUP_CHILD_TEST.TEST_CHILD_VALUE=? " +
246                           "group by GROUP_BASE_TEST.TEST_BASE_ID,GROUP_BASE_TEST.TEST_BASE_VALUE";
247                                       
248          PreparedStatement JavaDoc insertStatement = null;
249          PreparedStatement JavaDoc deleteStatement = null;
250          PreparedStatement JavaDoc selectStatement = null;
251          ResultSet JavaDoc rsResults = null;
252          
253          try
254          {
255             m_transaction.begin();
256             try
257             {
258                insertStatement = m_connection.prepareStatement(INSERT_BASE);
259                insertStatement.setInt(1, 1);
260                insertStatement.setInt(2, 1);
261                insertStatement.execute();
262                
263                insertStatement = m_connection.prepareStatement(INSERT_CHILD);
264                insertStatement.setInt(1, 1);
265                insertStatement.setInt(2, 1);
266                insertStatement.setInt(3, 1);
267                insertStatement.execute();
268    
269                m_transaction.commit();
270             }
271             catch (Throwable JavaDoc throwable)
272             {
273                m_transaction.rollback();
274                throw throwable;
275             }
276             finally
277             {
278                DatabaseUtils.closeStatement(insertStatement);
279             }
280             
281             try
282             {
283                selectStatement = m_connection.prepareStatement(SELECT);
284                selectStatement.setInt(1, 3);
285                rsResults = selectStatement.executeQuery();
286                
287                assertFalse("There should be no rows in result", rsResults.next());
288             }
289             finally
290             {
291                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
292             }
293             
294          }
295          finally
296          {
297             m_transaction.begin();
298             try
299             {
300                deleteStatement = m_connection.prepareStatement(DELETE_CHILD);
301                deleteStatement.execute();
302                deleteStatement = m_connection.prepareStatement(DELETE_BASE);
303                deleteStatement.execute();
304                m_transaction.commit();
305             }
306             catch (Throwable JavaDoc thr)
307             {
308                m_transaction.rollback();
309                throw new Exception JavaDoc(thr);
310             }
311             finally
312             {
313                DatabaseUtils.closeStatement(deleteStatement);
314             }
315          }
316       }
317    
318       /**
319        * Test if the union is working
320        *
321        * @throws Throwable - an error has occured during test
322        */

323       public void testUnionQuery(
324       ) throws Throwable JavaDoc
325       {
326          final String JavaDoc INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)";
327          final String JavaDoc DELETE = "delete from QUERY_TEST";
328    
329          final String JavaDoc SELECT = "select VALUE_1 from QUERY_TEST where VALUE_2 in " +
330             "(select VALUE_2 from QUERY_TEST union select VALUE_1 from QUERY_TEST)";
331                                        
332          PreparedStatement JavaDoc insertStatement = null;
333          PreparedStatement JavaDoc deleteStatement = null;
334          PreparedStatement JavaDoc selectStatement = null;
335          ResultSet JavaDoc rsResults = null;
336          
337          try
338          {
339             m_transaction.begin();
340             try
341             {
342                insertStatement = m_connection.prepareStatement(INSERT);
343                insertStatement.setInt(1, 1);
344                insertStatement.setInt(2, 11);
345                insertStatement.execute();
346                
347                insertStatement = m_connection.prepareStatement(INSERT);
348                insertStatement.setInt(1, 2);
349                insertStatement.setInt(2, 12);
350                insertStatement.execute();
351    
352                m_transaction.commit();
353             }
354             catch (Throwable JavaDoc throwable)
355             {
356                m_transaction.rollback();
357                throw throwable;
358             }
359             finally
360             {
361                DatabaseUtils.closeStatement(insertStatement);
362             }
363             
364             try
365             {
366                selectStatement = m_connection.prepareStatement(SELECT);
367                rsResults = selectStatement.executeQuery();
368                
369                assertTrue("There should be rows in result", rsResults.next());
370                assertTrue("There should be rows in result", rsResults.next());
371                assertFalse("There should be no more rows in result", rsResults.next());
372             }
373             catch (SQLException JavaDoc sqleExc)
374             {
375                assertTrue("It seems like the database doesn't support UNION: "
376                           + sqleExc.getMessage(), false);
377             }
378             finally
379             {
380                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
381             }
382          }
383          finally
384          {
385             m_transaction.begin();
386             try
387             {
388                deleteStatement = m_connection.prepareStatement(DELETE);
389                deleteStatement.execute();
390                m_transaction.commit();
391             }
392             catch (Throwable JavaDoc thr)
393             {
394                m_transaction.rollback();
395                throw new Exception JavaDoc(thr);
396             }
397             finally
398             {
399                DatabaseUtils.closeStatement(deleteStatement);
400             }
401          }
402       }
403       
404       /**
405        * Test if the intersect is working
406        *
407        * @throws Throwable - an error has occured during test
408        */

409       public void testIntersectQuery(
410       ) throws Throwable JavaDoc
411       {
412          final String JavaDoc INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)";
413          final String JavaDoc DELETE = "delete from QUERY_TEST";
414    
415          final String JavaDoc SELECT = "select VALUE_1 from QUERY_TEST where VALUE_2 in " +
416             "(select VALUE_2 from QUERY_TEST intersect select VALUE_1 from QUERY_TEST)";
417                                        
418          PreparedStatement JavaDoc insertStatement = null;
419          PreparedStatement JavaDoc deleteStatement = null;
420          PreparedStatement JavaDoc selectStatement = null;
421          ResultSet JavaDoc rsResults = null;
422          
423          try
424          {
425             m_transaction.begin();
426             try
427             {
428                insertStatement = m_connection.prepareStatement(INSERT);
429                insertStatement.setInt(1, 1);
430                insertStatement.setInt(2, 11);
431                insertStatement.execute();
432                
433                insertStatement = m_connection.prepareStatement(INSERT);
434                insertStatement.setInt(1, 11);
435                insertStatement.setInt(2, 12);
436                insertStatement.execute();
437    
438                m_transaction.commit();
439             }
440             catch (Throwable JavaDoc throwable)
441             {
442                m_transaction.rollback();
443                throw throwable;
444             }
445             finally
446             {
447                DatabaseUtils.closeStatement(insertStatement);
448             }
449             
450             try
451             {
452                selectStatement = m_connection.prepareStatement(SELECT);
453                rsResults = selectStatement.executeQuery();
454                
455                assertTrue("There should be rows in result", rsResults.next());
456                assertFalse("There should be no more rows in result", rsResults.next());
457             }
458             catch (SQLException JavaDoc sqleExc)
459             {
460                // MySQL 4.x doesn't support Intersect
461
fail("It seems like the database doen't support INTERSECT: "
462                     + sqleExc.getMessage());
463             }
464             finally
465             {
466                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
467             }
468          }
469          finally
470          {
471             m_transaction.begin();
472             try
473             {
474                deleteStatement = m_connection.prepareStatement(DELETE);
475                deleteStatement.execute();
476                m_transaction.commit();
477             }
478             catch (Throwable JavaDoc thr)
479             {
480                m_transaction.rollback();
481                throw new Exception JavaDoc(thr);
482             }
483             finally
484             {
485                DatabaseUtils.closeStatement(deleteStatement);
486             }
487          }
488       }
489       
490       /**
491        * Test if the insert-select with parameter is working
492        *
493        * @throws Throwable - an error has occured during test
494        */

495       public void testInsertSelectQuery(
496       ) throws Throwable JavaDoc
497       {
498          final String JavaDoc INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)";
499          final String JavaDoc DELETE = "delete from QUERY_TEST";
500    
501          final String JavaDoc INSERT_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance(
502                               DatabaseTestSchema.class)).getInsertSelectQuery();
503    
504          final String JavaDoc SELECT = "select VALUE_1, VALUE_2 from QUERY_TEST where VALUE_1=?";
505                                        
506          PreparedStatement JavaDoc insertStatement = null;
507          PreparedStatement JavaDoc deleteStatement = null;
508          PreparedStatement JavaDoc insertSelectStatement = null;
509          PreparedStatement JavaDoc selectStatement = null;
510          ResultSet JavaDoc rsResults = null;
511          
512          try
513          {
514             m_transaction.begin();
515             try
516             {
517                insertStatement = m_connection.prepareStatement(INSERT);
518                insertStatement.setInt(1, 1);
519                insertStatement.setInt(2, 1);
520                insertStatement.execute();
521                
522                insertStatement = m_connection.prepareStatement(INSERT);
523                insertStatement.setInt(1, 2);
524                insertStatement.setInt(2, 2);
525                insertStatement.execute();
526    
527                m_transaction.commit();
528             }
529             catch (Throwable JavaDoc throwable)
530             {
531                m_transaction.rollback();
532                throw throwable;
533             }
534             finally
535             {
536                DatabaseUtils.closeStatement(insertStatement);
537             }
538             
539             m_transaction.begin();
540             try
541             {
542                insertSelectStatement = m_connection.prepareStatement(INSERT_SELECT);
543                insertSelectStatement.setInt(1, 3);
544                insertSelectStatement.execute();
545    
546                m_transaction.commit();
547             }
548             catch (SQLException JavaDoc sqleExc)
549             {
550                m_transaction.rollback();
551                throw sqleExc;
552             }
553             finally
554             {
555                DatabaseUtils.closeResultSetAndStatement(rsResults, insertSelectStatement);
556             }
557             
558             selectStatement = m_connection.prepareStatement(SELECT);
559             selectStatement.setInt(1, 3);
560             rsResults = selectStatement.executeQuery();
561             
562             assertTrue("The inserted data not in DB", rsResults.next());
563             assertFalse("More data in DB", rsResults.next());
564          }
565          finally
566          {
567             m_transaction.begin();
568             try
569             {
570                deleteStatement = m_connection.prepareStatement(DELETE);
571                deleteStatement.execute();
572                m_transaction.commit();
573             }
574             catch (Throwable JavaDoc thr)
575             {
576                m_transaction.rollback();
577                throw new Exception JavaDoc(thr);
578             }
579             finally
580             {
581                DatabaseUtils.closeStatement(deleteStatement);
582             }
583          }
584       }
585       
586       /**
587        * Test if select except except without parenthesis work
588        *
589        * @throws Throwable - an error has occured during test
590        */

591       public void testExceptExcept(
592       ) throws Throwable JavaDoc
593       {
594          final String JavaDoc INSERT = "insert into QUERY_TEST_EXCEPT (VALUE_1) values (?)";
595          
596          final String JavaDoc DELETE = "delete from QUERY_TEST_EXCEPT";
597          
598          final String JavaDoc EXCEPT_EXCEPT_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance(
599                               DatabaseTestSchema.class)).getSelectExceptQuery();
600    
601          PreparedStatement JavaDoc insertStatement = null;
602          PreparedStatement JavaDoc deleteStatement = null;
603          PreparedStatement JavaDoc selectStatement = null;
604          ResultSet JavaDoc rsResults = null;
605          
606          try
607          {
608             m_transaction.begin();
609             try
610             {
611                insertStatement = m_connection.prepareStatement(INSERT);
612                insertStatement.setInt(1, 1);
613                insertStatement.execute();
614    
615                insertStatement.setInt(1, 2);
616                insertStatement.execute();
617    
618                insertStatement.setInt(1, 3);
619                insertStatement.execute();
620    
621                m_transaction.commit();
622             }
623             catch (Throwable JavaDoc throwable)
624             {
625                m_transaction.rollback();
626                throw throwable;
627             }
628             finally
629             {
630                DatabaseUtils.closeStatement(insertStatement);
631             }
632             
633             // Here we are doing 1,2,3 except 1,2 except 2,3
634
// Based on the parenthesis this can have two different interpretations
635
// ((1,2,3 except 1,2) except 2,3) is nothing what we expect
636
// 1,2,3 except (1,2 except 2,3) is 2,3 what we don't want
637
selectStatement = m_connection.prepareStatement(EXCEPT_EXCEPT_SELECT);
638             selectStatement.setInt(1, 1);
639             selectStatement.setInt(2, 2);
640             selectStatement.setInt(3, 3);
641             selectStatement.setInt(4, 1);
642             selectStatement.setInt(5, 2);
643             selectStatement.setInt(6, 2);
644             selectStatement.setInt(7, 3);
645             
646             try
647             {
648                rsResults = selectStatement.executeQuery();
649             }
650             catch (SQLException JavaDoc sqleExc)
651             {
652                fail("It seems like database doesn't support queries with EXCEPT: " +
653                     sqleExc.getMessage());
654                throw sqleExc;
655             }
656    
657             assertFalse("Select should not return any data", rsResults.next());
658          }
659          finally
660          {
661             m_transaction.begin();
662             try
663             {
664                deleteStatement = m_connection.prepareStatement(DELETE);
665                
666                deleteStatement.execute();
667                m_transaction.commit();
668             }
669             catch (Throwable JavaDoc thr)
670             {
671                m_transaction.rollback();
672                throw new Exception JavaDoc(thr);
673             }
674             finally
675             {
676                DatabaseUtils.closeStatement(deleteStatement);
677             }
678          }
679       }
680    
681       /**
682        * Test if select except union without parenthesis work
683        *
684        * @throws Throwable - an error has occured during test
685        */

686       public void testExceptUnion(
687       ) throws Throwable JavaDoc
688       {
689          final String JavaDoc INSERT = "insert into QUERY_TEST_EXCEPT (VALUE_1) values (?)";
690          
691          final String JavaDoc DELETE = "delete from QUERY_TEST_EXCEPT";
692          
693          final String JavaDoc EXCEPT_UNION_SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance(
694                               DatabaseTestSchema.class)).getSelectExceptUnionQuery();
695    
696          PreparedStatement JavaDoc insertStatement = null;
697          PreparedStatement JavaDoc deleteStatement = null;
698          PreparedStatement JavaDoc selectStatement = null;
699          ResultSet JavaDoc rsResults = null;
700          
701          try
702          {
703             m_transaction.begin();
704             try
705             {
706                insertStatement = m_connection.prepareStatement(INSERT);
707                insertStatement.setInt(1, 1);
708                insertStatement.execute();
709                
710                insertStatement.setInt(1, 2);
711                insertStatement.execute();
712    
713                insertStatement.setInt(1, 3);
714                insertStatement.execute();
715    
716                m_transaction.commit();
717             }
718             catch (Throwable JavaDoc throwable)
719             {
720                m_transaction.rollback();
721                throw throwable;
722             }
723             finally
724             {
725                DatabaseUtils.closeStatement(insertStatement);
726             }
727             
728             // Here we are doing 1,2,3 except 1,2 union 2,3
729
// Based on the parenthesis this can have two different interpretations
730
// ((1,2,3 except 1,2) union 2,3) is 2,3
731
// 1,2,3 except (1,2 union 2,3) is nothing
732
selectStatement = m_connection.prepareStatement(EXCEPT_UNION_SELECT);
733             selectStatement.setInt(1, 1);
734             selectStatement.setInt(2, 2);
735             selectStatement.setInt(3, 3);
736             selectStatement.setInt(4, 1);
737             selectStatement.setInt(5, 2);
738             selectStatement.setInt(6, 2);
739             selectStatement.setInt(7, 3);
740             
741             try
742             {
743                rsResults = selectStatement.executeQuery();
744             }
745             catch (SQLException JavaDoc sqleExc)
746             {
747                fail("It seems like database doesn't support queries with EXCEPT" +
748                     " or with UNION: " + sqleExc.getMessage());
749                throw sqleExc;
750             }
751    
752             assertTrue("Select should return first value", rsResults.next());
753             assertEquals("First value should be 2", 2, rsResults.getInt(1));
754             assertTrue("Select should return second value", rsResults.next());
755             assertEquals("First value should be 3", 3, rsResults.getInt(1));
756          }
757          finally
758          {
759             m_transaction.begin();
760             try
761             {
762                deleteStatement = m_connection.prepareStatement(DELETE);
763                
764                deleteStatement.execute();
765                m_transaction.commit();
766             }
767             catch (Throwable JavaDoc thr)
768             {
769                m_transaction.rollback();
770                throw new Exception JavaDoc(thr);
771             }
772             finally
773             {
774                DatabaseUtils.closeStatement(deleteStatement);
775             }
776          }
777       }
778    
779       /**
780        * Test if the exists is working
781        *
782        * @throws Throwable - an error has occured during test
783        */

784       public void testExistsQuery(
785       ) throws Throwable JavaDoc
786       {
787          final String JavaDoc INSERT = "insert into QUERY_TEST (VALUE_1, VALUE_2) values (?,?)";
788          final String JavaDoc DELETE = "delete from QUERY_TEST";
789    
790          final String JavaDoc SELECT = ((DatabaseTestSchema)DatabaseSchemaManager.getInstance(
791                               DatabaseTestSchema.class)).getSelectExistQuery();
792                                        
793          PreparedStatement JavaDoc insertStatement = null;
794          PreparedStatement JavaDoc deleteStatement = null;
795          PreparedStatement JavaDoc selectStatement = null;
796          ResultSet JavaDoc rsResults = null;
797          
798          try
799          {
800             m_transaction.begin();
801             try
802             {
803                insertStatement = m_connection.prepareStatement(INSERT);
804                insertStatement.setInt(1, 1);
805                insertStatement.setInt(2, 11);
806                insertStatement.execute();
807                
808                insertStatement = m_connection.prepareStatement(INSERT);
809                insertStatement.setInt(1, 11);
810                insertStatement.setInt(2, 12);
811                insertStatement.execute();
812    
813                m_transaction.commit();
814             }
815             catch (Throwable JavaDoc throwable)
816             {
817                m_transaction.rollback();
818                throw throwable;
819             }
820             finally
821             {
822                DatabaseUtils.closeStatement(insertStatement);
823             }
824             
825             try
826             {
827                selectStatement = m_connection.prepareStatement(SELECT);
828                rsResults = selectStatement.executeQuery();
829                
830                assertTrue("There should be rows in result", rsResults.next());
831                assertFalse("There should be no more rows in result", rsResults.next());
832             }
833             catch (SQLException JavaDoc sqleExc)
834             {
835                assertTrue(sqleExc.getMessage(), false);
836             }
837             finally
838             {
839                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
840             }
841          }
842          finally
843          {
844             m_transaction.begin();
845             try
846             {
847                deleteStatement = m_connection.prepareStatement(DELETE);
848                deleteStatement.execute();
849                m_transaction.commit();
850             }
851             catch (Throwable JavaDoc thr)
852             {
853                m_transaction.rollback();
854                throw new Exception JavaDoc(thr);
855             }
856             finally
857             {
858                DatabaseUtils.closeStatement(deleteStatement);
859             }
860          }
861       }
862    }
863 }
864
Popular Tags