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


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: TransactionTest.java,v 1.12 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.Connection ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.util.logging.Level ;
28 import java.util.logging.Logger ;
29
30 import junit.extensions.TestSetup;
31 import junit.framework.Test;
32 import junit.framework.TestSuite;
33
34 import org.opensubsystems.core.error.OSSException;
35 import org.opensubsystems.core.persist.db.Database;
36 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
37 import org.opensubsystems.core.persist.db.DatabaseImpl;
38 import org.opensubsystems.core.persist.db.DatabaseTest;
39 import org.opensubsystems.core.persist.db.DatabaseTestSetup;
40 import org.opensubsystems.core.persist.db.DatabaseTestSuite;
41 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl;
42 import org.opensubsystems.core.util.DatabaseUtils;
43 import org.opensubsystems.core.util.Log;
44
45 /**
46  * Test of transaction (update, insert, delete ans select in one transaction).
47  *
48  * @version $Id: TransactionTest.java,v 1.12 2007/01/07 06:14:51 bastafidli Exp $
49  * @author Julo Legeny
50  * @code.reviewer Miro Halas
51  * @code.reviewed Initial revision
52  */
53 public final class TransactionTest
54 {
55    // Constructors /////////////////////////////////////////////////////////////
56
57    /**
58     * Private constructor since this class cannot be instantiated
59     */
60    private TransactionTest(
61    )
62    {
63       // Do nothing
64 }
65    
66    // Public methods ///////////////////////////////////////////////////////////
67
68    /**
69     * Create the suite for this test since this is the only way how to create
70     * test setup which can initialize and shutdown the database for us
71     *
72     * @return Test - suite of tests to run for this database
73     */
74    public static Test suite(
75    )
76    {
77       TestSuite suite = new DatabaseTestSuite("TransactionTest");
78       suite.addTestSuite(TransactionTestInternal.class);
79       TestSetup wrapper = new DatabaseTestSetup(suite);
80
81       return wrapper;
82    }
83
84    /**
85     * Internal class which can be included in other test suites directly without
86     * including the above suite. This allows us to group multiple tests
87     * together and the execute the DatabaseTestSetup only once
88     */
89    public static class TransactionTestInternal extends DatabaseTest
90    {
91       // Cached values ////////////////////////////////////////////////////////////
92
93       /**
94        * Logger for this class
95        */
96       private static Logger s_logger = Log.getInstance(TransactionTestInternal.class);
97    
98       /**
99        * Static initializer
100        */
101       static
102       { // This test use special database schema so make the database aware of it
103 Database dbDatabase;
104          try
105          { // Add schema database tests needs to the database
106 dbDatabase = DatabaseImpl.getInstance();
107             dbDatabase.add(DatabaseTestSchema.class);
108          }
109          catch (OSSException bfeExc)
110          {
111             throw new RuntimeException ("Unexpected exception.", bfeExc);
112          }
113       }
114       /**
115        * Create new test.
116        *
117        * @param strTestName - name of the test
118        */
119       public TransactionTestInternal(
120          String strTestName
121       )
122       {
123          super(strTestName);
124       }
125    
126       /**
127        * Test if the database driver support rollback.
128        *
129        * Uses the already setup connection and transaction.
130        * No need to close the connection since base class is doing it for us.
131        *
132        * @throws Throwable - an error has occured during test
133        */
134       public void testRollbackUniqueWithJTA(
135       ) throws Throwable
136       {
137          final String INSERT_VALUE = "insert into ROLLBACK_TEST (TEST_COLUMN) values (?)";
138          final String DELETE_ALL = "delete from ROLLBACK_TEST";
139          final String UPDATE_VALUE
140                           = "update ROLLBACK_TEST set TEST_COLUMN = ? where TEST_COLUMN = ?";
141          final String VALUE_TEST_1 = "value one 1";
142          final String VALUE_TEST_2 = "value two 2";
143        
144          PreparedStatement insertStatement = null;
145          int iUpdateCount;
146          try
147          {
148             m_transaction.begin();
149       
150             try
151             {
152                insertStatement = m_connection.prepareStatement(INSERT_VALUE);
153                insertStatement.setString(1, VALUE_TEST_1);
154       
155                iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
156       
157                m_transaction.commit();
158             }
159             catch (Throwable throwable)
160             {
161                m_transaction.rollback();
162                throw throwable;
163             }
164             finally
165             {
166                DatabaseUtils.closeStatement(insertStatement);
167                insertStatement = null;
168             }
169          
170             assertEquals("Exactly one record have been inserted.",
171                                 iUpdateCount, 1);
172       
173             m_transaction.begin();
174       
175             try
176             {
177                insertStatement = m_connection.prepareStatement(INSERT_VALUE);
178                insertStatement.setString(1, VALUE_TEST_2);
179       
180                iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
181       
182                m_transaction.commit();
183             }
184             catch (Throwable throwable)
185             {
186                m_transaction.rollback();
187                throw throwable;
188             }
189             finally
190             {
191                DatabaseUtils.closeStatement(insertStatement);
192                insertStatement = null;
193             }
194                
195             // Now select it back to be sure it is there so we cna delete it
196 PreparedStatement updateStatement = null;
197       
198             m_transaction.begin();
199             try
200             {
201                updateStatement = m_connection.prepareStatement(UPDATE_VALUE);
202                updateStatement.setString(1, VALUE_TEST_1);
203                updateStatement.setString(2, VALUE_TEST_2);
204                iUpdateCount = updateStatement.executeUpdate();
205                
206                m_transaction.commit();
207                
208                fail("Updating the unique column with the same value" +
209                     " didn't generated exception.");
210             }
211             catch (Throwable throwable)
212             {
213                s_logger.log(Level.FINEST,
214                             "Update of unique column with the same value" +
215                             " generated exception as expected.",
216                             throwable);
217                m_transaction.rollback();
218                // Don't throw the exception since it was expected
219 }
220             finally
221             {
222                DatabaseUtils.closeStatement(updateStatement);
223                updateStatement = null;
224             }
225          }
226          finally
227          {
228             PreparedStatement deleteStatement = null;
229             
230             m_transaction.begin();
231             try
232             {
233                deleteStatement = m_connection.prepareStatement(DELETE_ALL);
234    
235                DatabaseUtils.executeUpdateAndClose(deleteStatement);
236    
237                m_transaction.commit();
238             }
239             catch (Throwable throwable)
240             {
241                m_transaction.rollback();
242                throw throwable;
243             }
244             finally
245             {
246                DatabaseUtils.closeStatement(deleteStatement);
247                deleteStatement = null;
248             }
249          }
250       }
251    
252       /**
253        * Test if the database driver support rollback.
254        *
255        * Uses the already setup connection and transaction.
256        * No need to close the connection since base class is doing it for us.
257        *
258        * @throws Throwable - an error has occured during test
259        */
260       public void testRollbackUniqueWithJDBC(
261       ) throws Throwable
262       {
263          final String INSERT_VALUE = "insert into ROLLBACK_TEST (TEST_COLUMN) values (?)";
264          final String DELETE_ALL = "delete from ROLLBACK_TEST";
265          // See OracleTests class why we need to select tablename.*
266 final String UPDATE_VALUE
267                           = "update ROLLBACK_TEST set TEST_COLUMN = ? where TEST_COLUMN = ?";
268          final String VALUE_TEST_1 = "value one 1";
269          final String VALUE_TEST_2 = "value two 2";
270        
271          PreparedStatement insertStatement = null;
272          int iUpdateCount;
273          try
274          {
275             m_transaction.begin();
276             try
277             {
278                insertStatement = m_connection.prepareStatement(INSERT_VALUE);
279                insertStatement.setString(1, VALUE_TEST_1);
280       
281                iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
282       
283                m_transaction.commit();
284             }
285             catch (Throwable throwable)
286             {
287                m_transaction.rollback();
288                throw throwable;
289             }
290             finally
291             {
292                DatabaseUtils.closeStatement(insertStatement);
293                insertStatement = null;
294             }
295          
296             assertEquals("Exactly one record have been inserted.",
297                                 iUpdateCount, 1);
298       
299             m_transaction.begin();
300             try
301             {
302                insertStatement = m_connection.prepareStatement(INSERT_VALUE);
303                insertStatement.setString(1, VALUE_TEST_2);
304       
305                iUpdateCount = DatabaseUtils.executeUpdateAndClose(insertStatement);
306       
307                m_transaction.commit();
308             }
309             catch (Throwable throwable)
310             {
311                m_transaction.rollback();
312                throw throwable;
313             }
314             finally
315             {
316                DatabaseUtils.closeStatement(insertStatement);
317                insertStatement = null;
318             }
319                
320             // Now select it back to be sure it is there so we cna delete it
321 PreparedStatement updateStatement = null;
322       
323             // Instead of JTA we will use JDBC to commit
324 // m_transaction.begin();
325 boolean bOriginalAutoCommit = m_connection.getAutoCommit();
326             try
327             {
328                if (bOriginalAutoCommit)
329                {
330                   m_connection.setAutoCommit(false);
331                }
332                try
333                {
334                   updateStatement = m_connection.prepareStatement(UPDATE_VALUE);
335                   updateStatement.setString(1, VALUE_TEST_1);
336                   updateStatement.setString(2, VALUE_TEST_2);
337                   iUpdateCount = updateStatement.executeUpdate();
338                   
339                   DatabaseTransactionFactoryImpl.getInstance().commitTransaction(m_connection);
340                   
341                   fail("Updating the unique column with the same value" +
342                        " didn't generated exception.");
343                }
344                catch (Throwable throwable)
345                {
346                   s_logger.log(Level.FINEST,
347                                "Update of unique column with the same value" +
348                                " generated exception as expected.",
349                                         throwable);
350                   // Instead of JTA we will use JDBC to commit
351 // m_transaction.rollback();
352 DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(m_connection);
353                   // Don't throw the exception since it was expected
354 }
355                finally
356                {
357                   DatabaseUtils.closeStatement(updateStatement);
358                   updateStatement = null;
359                }
360             }
361             finally
362             {
363                if (bOriginalAutoCommit)
364                {
365                   m_connection.setAutoCommit(bOriginalAutoCommit);
366                }
367             }
368          }
369          finally
370          {
371             PreparedStatement deleteStatement = null;
372             
373             m_transaction.begin();
374             try
375             {
376                deleteStatement = m_connection.prepareStatement(DELETE_ALL);
377    
378                DatabaseUtils.executeUpdateAndClose(deleteStatement);
379    
380                m_transaction.commit();
381             }
382             catch (Throwable throwable)
383             {
384                m_transaction.rollback();
385                throw throwable;
386             }
387             finally
388             {
389                DatabaseUtils.closeStatement(deleteStatement);
390                deleteStatement = null;
391             }
392          }
393       }
394    
395       /**
396        * Update, delete, insert and select record in the same transaction
397        *
398        * @throws Throwable - an error has occured during test
399        */
400       public void testCommitAfterUpdateSelect(
401       ) throws Throwable
402       {
403          final String DELETE_ALL = "delete from TRANSACTION_TEST";
404          final String UPDATE_VALUE = "update TRANSACTION_TEST set TEST_VALUE = ? where TEST_ID = ?";
405          final String INSERT_VALUE = "insert into TRANSACTION_TEST values (?, ?)";
406          final String SELECT_VALUE = "select TEST_VALUE from TRANSACTION_TEST where TEST_ID = 1";
407          final String VALUE_TEST = "test_value";
408          final String VALUE_TEST1 = "test_value_updated_1";
409          
410          Connection connection = null;
411          PreparedStatement updateStatement = null;
412          PreparedStatement deleteStatement = null;
413          PreparedStatement selectStatement = null;
414          PreparedStatement insertStatement = null;
415          ResultSet rsResults = null;
416          int iUpdatedCount = 0;
417          int iDeletedCount = 0;
418          int iSelectedCount = 0;
419          int iInsertCount = 0;
420          String strUpdatedValue = "";
421    
422          try
423          {
424             //******************************************************************
425 // Try to select original record to verify that the database is in OK state
426 try
427             {
428                // Request autocommit false since we are modifying database
429 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
430       
431                m_transaction.begin();
432                try
433                {
434                   deleteStatement = connection.prepareStatement(DELETE_ALL);
435       
436                   iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
437       
438                   try
439                   {
440                      insertStatement = connection.prepareStatement(INSERT_VALUE);
441                      insertStatement.setInt(1, 1); // ID
442 insertStatement.setString(2, VALUE_TEST);
443                      iInsertCount = insertStatement.executeUpdate();
444                   }
445                   finally
446                   {
447                      DatabaseUtils.closeStatement(insertStatement);
448                   }
449    
450                   m_transaction.commit();
451                }
452                catch (Throwable throwable)
453                {
454                   m_transaction.rollback();
455                   throw throwable;
456                }
457             }
458             finally
459             {
460                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
461             }
462             
463             assertEquals("No records should be initially in the database.",
464                                 0, iDeletedCount);
465             assertEquals("Exactly one record should be inserted.", 1, iInsertCount);
466                                 
467             //******************************************************************
468 // Try to select original record to verify that the database is in OK state
469 try
470             {
471                // Request autocommit true since we are just reading data from the database
472 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
473                try
474                {
475                   selectStatement = connection.prepareStatement(SELECT_VALUE,
476                                                                 ResultSet.TYPE_SCROLL_INSENSITIVE,
477                                                                 ResultSet.CONCUR_READ_ONLY);
478                   rsResults = selectStatement.executeQuery();
479                   if (rsResults.last())
480                   { // The last row number will tell us the row count
481 iSelectedCount = rsResults.getRow();
482                      rsResults.beforeFirst();
483                   }
484                   rsResults.beforeFirst();
485                   if (rsResults.next())
486                   {
487                      strUpdatedValue = rsResults.getString(1);
488                   }
489                }
490                finally
491                {
492                   DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
493                }
494             }
495             finally
496             {
497                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
498             }
499             assertEquals("Exactly one record have been selected.", 1, iSelectedCount);
500             assertEquals("Selected items should not have postfix '_updated_1'.",
501                                 VALUE_TEST, strUpdatedValue);
502    
503             // Now in transaction access the database and try to confuse JOTM/XAPool
504 m_transaction.begin();
505             try
506             {
507                //******************************************************************
508 try // try to update record
509 {
510                   // Request autocommit false since we are modifying database
511 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
512                   try
513                   {
514                      updateStatement = connection.prepareStatement(UPDATE_VALUE);
515                      updateStatement.setString(1, VALUE_TEST1);
516                      updateStatement.setInt(2, 1);
517                      iUpdatedCount = DatabaseUtils.executeUpdateAndClose(updateStatement);
518                   }
519                   finally
520                   {
521                      DatabaseUtils.closeStatement(updateStatement);
522                   }
523                }
524                finally
525                {
526                   DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
527                }
528                assertEquals("Exactly one record should have been updated.", 1, iUpdatedCount);
529                //******************************************************************
530 try // try to select some record
531 {
532                   // Request autocommit true since we are just reading data from the database
533 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
534                   try
535                   {
536                      // HERE IS BUG IN JOTM 1.4.2 / XAPOOL 1.2.2
537 // Connection which is used to execute this statement
538 // will never become part of the transaction and for whatever reason
539 // what was done before is rollbacked
540 selectStatement = connection.prepareStatement(
541                                                        SELECT_VALUE,
542                                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
543                                                        ResultSet.CONCUR_READ_ONLY);
544                      rsResults = selectStatement.executeQuery();
545                      if (rsResults.last())
546                      { // The last row number will tell us the row count
547 iSelectedCount = rsResults.getRow();
548                         rsResults.beforeFirst();
549                      }
550                      rsResults.first();
551                      strUpdatedValue = rsResults.getString(1);
552                   }
553                   finally
554                   {
555                      DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
556                   }
557                }
558                finally
559                {
560                   DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
561                }
562                assertEquals("Exactly one record should have been selected.", 1, iSelectedCount);
563                assertEquals("Selected items should have updated value.",
564                                    VALUE_TEST1, strUpdatedValue);
565                m_transaction.commit();
566             }
567             catch (Throwable throwable)
568             {
569                m_transaction.rollback();
570                throw throwable;
571             }
572                
573             // Now try to verify if the result is in there
574 //******************************************************************
575 try // try to select updated record
576 {
577                // Request autocommit true since we are just reading data from the database
578 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
579                try
580                {
581                   selectStatement = connection.prepareStatement(SELECT_VALUE,
582                                                                 ResultSet.TYPE_SCROLL_INSENSITIVE,
583                                                                 ResultSet.CONCUR_READ_ONLY);
584                   rsResults = selectStatement.executeQuery();
585                   if (rsResults.last())
586                   { // The last row number will tell us the row count
587 iSelectedCount = rsResults.getRow();
588                      rsResults.beforeFirst();
589                   }
590                   rsResults.first();
591                   strUpdatedValue = rsResults.getString(1);
592                }
593                finally
594                {
595                   DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
596                }
597             }
598             finally
599             {
600                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
601             }
602             // In JOTM 1.4.2 with XAPool 1.2.2 this will fail since the select
603 // before rollbacked the update. In JOTM 1.4.3 this is passing
604 assertEquals("Exactly one record should have been selected.",
605                                 1, iSelectedCount);
606             assertEquals("Selected items should have updated value.",
607                                 VALUE_TEST1, strUpdatedValue);
608                                 
609          }
610          finally
611          {
612             m_transaction.begin();
613             try
614             {
615                deleteStatement = m_connection.prepareStatement(DELETE_ALL);
616    
617                DatabaseUtils.executeUpdateAndClose(deleteStatement);
618    
619                m_transaction.commit();
620             }
621             catch (Throwable throwable)
622             {
623                m_transaction.rollback();
624                throw throwable;
625             }
626          }
627       }
628    
629       /**
630        * Test implemented to try to detect bug in JOTM which turned up to be
631        * bug in testCommitAfterUpdateSelect. Now the testCommitAfterUpdateSelect
632        * and it still detects the bug in JOTM 1.4.2 and passing in JOTM 1.4.3
633        *
634        * This test case also detects defect in WebLogic 9.1 which doesn't enroll
635        * connections acquired before the transaction is started in the transaction.
636        *
637        * @throws Throwable - an error has occured during test
638        */
639       public void testCommitAfterUpdateDelete(
640       ) throws Throwable
641       {
642          final String DELETE_ALL = "delete from TRANSACTION_TEST";
643          final String UPDATE_VALUE = "update TRANSACTION_TEST set TEST_VALUE = ? where TEST_ID = ?";
644          final String INSERT_VALUE = "insert into TRANSACTION_TEST values (?, ?)";
645          final String SELECT_VALUE = "select TEST_VALUE from TRANSACTION_TEST where TEST_ID = 1";
646          final String VALUE_TEST = "test_value";
647          final String VALUE_TEST1 = "test_value_updated_1";
648          
649          Connection connection = null;
650          PreparedStatement updateStatement = null;
651          PreparedStatement deleteStatement = null;
652          PreparedStatement selectStatement = null;
653          PreparedStatement insertStatement = null;
654          ResultSet rsResults = null;
655          int iUpdatedCount = 0;
656          int iDeletedCount = 0;
657          int iSelectedCount = 0;
658          int iInsertCount = 0;
659    
660          try
661          {
662             //******************************************************************
663 // Insert record to prepare it for the test
664 try
665             {
666                // Request autocommit false since we are modifying database
667 // Notice that we are requesting the connection before we begin
668 // the transaction. The transaction manager should handle this
669 // and once the connection is used in the transaction it should
670 // enroll it in the transaction
671 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
672       
673                m_transaction.begin();
674                try
675                {
676                   deleteStatement = connection.prepareStatement(DELETE_ALL);
677                   iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
678                   try
679                   {
680                      insertStatement = connection.prepareStatement(INSERT_VALUE);
681                      insertStatement.setInt(1, 1); // ID
682 insertStatement.setString(2, VALUE_TEST1);
683                      iInsertCount = insertStatement.executeUpdate();
684                   }
685                   finally
686                   {
687                      DatabaseUtils.closeStatement(insertStatement);
688                   }
689       
690                   m_transaction.commit();
691                }
692                catch (Throwable throwable)
693                {
694                   m_transaction.rollback();
695                   throw throwable;
696                }
697             }
698             finally
699             {
700                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
701                connection = null;
702             }
703             
704             assertEquals("No records should be initially in the database.",
705                                 0, iDeletedCount);
706             assertEquals("Exactly one record should be inserted.", 1, iInsertCount);
707                                 
708             m_transaction.begin();
709             try
710             {
711                try // try to update the record back to it's original form
712 {
713                   // Notice that we are requesting the connection withing the
714 // transaction. This connection should be enrolled in the
715 // transaction automatically
716
717                   // Request autocommit false since we are modifying database
718 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
719                   try
720                   {
721                      updateStatement = connection.prepareStatement(UPDATE_VALUE);
722                      updateStatement.setString(1, VALUE_TEST);
723                      updateStatement.setInt(2, 1);
724                      iUpdatedCount = DatabaseUtils.executeUpdateAndClose(updateStatement);
725                   }
726                   finally
727                   {
728                      DatabaseUtils.closeStatement(updateStatement);
729                   }
730                }
731                finally
732                {
733                   DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
734                   connection = null;
735                }
736                assertEquals("Exactly one record should have been updated.", 1, iUpdatedCount);
737                m_transaction.commit();
738             }
739             catch (Throwable throwable)
740             {
741                m_transaction.rollback();
742                throw throwable;
743             }
744          }
745          catch (Throwable throwable)
746          {
747             // Print the error message here since if some error occurs
748 // below in finally we will never see this one
749 s_logger.log(Level.SEVERE,
750                                   "Unexpected error has occured during test.",
751                                   throwable);
752          }
753          finally
754          {
755             m_transaction.begin();
756             try
757             {
758                // Notice here that we are using connection which we requested
759 // before the transaction has started. This connection should
760 // be automatically enrolled in the transaction
761 deleteStatement = m_connection.prepareStatement(DELETE_ALL);
762    
763                DatabaseUtils.executeUpdateAndClose(deleteStatement);
764    
765                m_transaction.commit();
766             }
767             catch (Throwable throwable)
768             {
769                m_transaction.rollback();
770                throw throwable;
771             }
772    
773             //******************************************************************
774 // Now use new connection to see if all records were deleted
775 iSelectedCount = 0;
776             try
777             {
778                // Request autocommit true since we are just reading data from the database
779 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
780                try
781                {
782                   selectStatement = connection.prepareStatement(SELECT_VALUE);
783                   // TODO: Bug: WebLogic 9.1: The thread blocks here because it didn't
784 // enroll the m_connection to execute previous delete
785 // in the transaction and therefore it didn't commited
786 // that delete and therefore this select will be blocked
787 rsResults = selectStatement.executeQuery();
788                   if (rsResults.next())
789                   {
790                      iSelectedCount = 1;
791                   }
792                }
793                finally
794                {
795                   DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
796                }
797             }
798             finally
799             {
800                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
801             }
802             assertEquals("No record should exists in the database but there is a record",
803                          0, iSelectedCount);
804    
805             //******************************************************************
806 // And now use the same connection to see if all records were delected
807 iSelectedCount = 0;
808             try
809             {
810                selectStatement = m_connection.prepareStatement(SELECT_VALUE);
811                rsResults = selectStatement.executeQuery();
812                if (rsResults.next())
813                {
814                   iSelectedCount = 1;
815                }
816             }
817             finally
818             {
819                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
820             }
821             assertEquals("No record should exists in the database but there is a record",
822                          0, iSelectedCount);
823          }
824       }
825    
826       /**
827        * Test implemented to try to detect bug in JOTM which turned up to be
828        * bug in testCommitAfterUpdateSelect. Now the testCommitAfterUpdateSelect
829        * and it still detects the bug in JOTM 1.4.2 and passing in JOTM 1.4.3
830        *
831        * @throws Throwable - an error has occured during test
832        */
833       public void testCommitAfterUpdateDeleteWithAutoCommit(
834       ) throws Throwable
835       {
836          final String DELETE_ALL = "delete from TRANSACTION_TEST";
837          final String UPDATE_VALUE = "update TRANSACTION_TEST set TEST_VALUE = ? where TEST_ID = ?";
838          final String INSERT_VALUE = "insert into TRANSACTION_TEST values (?, ?)";
839          final String SELECT_VALUE = "select TEST_VALUE from TRANSACTION_TEST where TEST_ID = 1";
840          final String VALUE_TEST = "test_value";
841          final String VALUE_TEST1 = "test_value_updated_1";
842          
843          Connection connection = null;
844          PreparedStatement updateStatement = null;
845          PreparedStatement deleteStatement = null;
846          PreparedStatement selectStatement = null;
847          PreparedStatement insertStatement = null;
848          ResultSet rsResults = null;
849          int iUpdatedCount = 0;
850          int iDeletedCount = 0;
851          int iSelectedCount = 0;
852          int iInsertCount = 0;
853          String strUpdatedValue = "";
854    
855          try
856          {
857             //******************************************************************
858 // Try to select original record to verify that the database is in OK state
859 try
860             {
861                // This sequence is correct, don't change it.
862 // 1. First get the connection from the pool
863 // Request autocommit false since we are modifying database
864 // Notice that we are requesting the connection before we begin
865 // the transaction. The transaction manager should handle this
866 // and once the connection is used in the transaction it should
867 // enroll it in the transaction
868 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
869       
870                // 2. Now start transaction
871 m_transaction.begin();
872                try
873                {
874                   // 3. Not prepare statement with the global connection
875 // It should become part of the transaction
876 deleteStatement = connection.prepareStatement(DELETE_ALL);
877                   iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
878       
879                   // 4. Now commit or rollback
880 m_transaction.commit();
881                }
882                catch (Throwable throwable)
883                {
884                   m_transaction.rollback();
885                   throw throwable;
886                }
887    
888                // 5. And now try to execute insert outside of transaction
889 // The connection has autocommit as true and therefore it should
890 // commit immidiately. In Weblogic 9.1 this commits the previous
891 // transaction since it wasn't commited because Weblogic due to bug
892 // #1491821 doesn't enroll that connection in transaction
893 connection.setAutoCommit(true);
894                try
895                {
896                   insertStatement = connection.prepareStatement(INSERT_VALUE);
897                   insertStatement.setInt(1, 1); // ID
898 insertStatement.setString(2, VALUE_TEST1);
899                   iInsertCount = insertStatement.executeUpdate();
900                }
901                finally
902                {
903                   DatabaseUtils.closeStatement(insertStatement);
904                }
905             }
906             finally
907             {
908                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
909                connection = null;
910             }
911             
912             assertEquals("No records should be initially in the database.",
913                          0, iDeletedCount);
914                                 
915             assertEquals("Exactly one record should be inserted.", 1, iInsertCount);
916                                 
917             m_transaction.begin();
918             try
919             {
920                // try to update the record back to it's original form
921 // Request autocommit false since we are modifying database
922 // Notice that we are requesting the connection withing the
923 // transaction. This connection should be enrolled in the
924 // transaction automatically
925 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
926                try
927                {
928                   updateStatement = connection.prepareStatement(UPDATE_VALUE);
929                   updateStatement.setString(1, VALUE_TEST1);
930                   updateStatement.setInt(2, 1);
931                   iUpdatedCount = DatabaseUtils.executeUpdateAndClose(updateStatement);
932                }
933                finally
934                {
935                   DatabaseUtils.closeStatement(updateStatement);
936                   DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
937                   connection = null;
938                }
939                m_transaction.commit();
940             }
941             catch (Throwable throwable)
942             {
943                m_transaction.rollback();
944                throw throwable;
945             }
946    
947             assertEquals("Exactly one record should have been updated.",
948                                 1, iUpdatedCount);
949    
950             // Now try to verify if the result is in there using different connection
951 //******************************************************************
952 // try to select updated record
953 iSelectedCount = 0;
954             strUpdatedValue = "";
955             try
956             {
957                // Set autocommit true since we are just reading data from the database
958 m_connection.setAutoCommit(true);
959                selectStatement = m_connection.prepareStatement(SELECT_VALUE,
960                                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
961                                                                ResultSet.CONCUR_READ_ONLY);
962                rsResults = selectStatement.executeQuery();
963                if (rsResults.last())
964                { // The last row number will tell us the row count
965 iSelectedCount = rsResults.getRow();
966                   rsResults.beforeFirst();
967                }
968                rsResults.first();
969                strUpdatedValue = rsResults.getString(1);
970             }
971             finally
972             {
973                DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
974                selectStatement = null;
975                DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
976                connection = null;
977             }
978             assertEquals("Exactly one record should have been selected.",
979                                 1, iSelectedCount);
980             assertEquals("Selected items should have updated value.",
981                                 VALUE_TEST1, strUpdatedValue);
982                                 
983             // Now try to update it back but WITHOUT transaction to see if autocommit works
984 m_connection.setAutoCommit(true);
985             try
986             {
987                // Use the global connection. It was part of transaction above
988 // I want to see if it will become able to act standalone
989 // and autocommit
990 updateStatement = m_connection.prepareStatement(UPDATE_VALUE);
991                updateStatement.setString(1, VALUE_TEST);
992                updateStatement.setInt(2, 1);
993                iUpdatedCount = DatabaseUtils.executeUpdateAndClose(updateStatement);
994             }
995             finally
996             {
997                DatabaseUtils.closeStatement(updateStatement);
998             }
999             
1000            // Now try to verify if the result is in there using different connection
1001 //******************************************************************
1002 // try to select updated record but use a different connection than
1003 // the one above
1004 iSelectedCount = 0;
1005            strUpdatedValue = "";
1006            try
1007            {
1008               // Request autocommit true since we are just reading data from the database
1009 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
1010               selectStatement = connection.prepareStatement(SELECT_VALUE,
1011                                                             ResultSet.TYPE_SCROLL_INSENSITIVE,
1012                                                             ResultSet.CONCUR_READ_ONLY);
1013               rsResults = selectStatement.executeQuery();
1014               if (rsResults.last())
1015               { // The last row number will tell us the row count
1016 iSelectedCount = rsResults.getRow();
1017                  rsResults.beforeFirst();
1018               }
1019               rsResults.first();
1020               strUpdatedValue = rsResults.getString(1);
1021            }
1022            finally
1023            {
1024               DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
1025               selectStatement = null;
1026               DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
1027               connection = null;
1028            }
1029            assertEquals("Exactly one record should have been selected.",
1030                                1, iSelectedCount);
1031            assertEquals("Selected items should have updated value.",
1032                                VALUE_TEST, strUpdatedValue);
1033         }
1034         catch (Throwable throwable)
1035         {
1036            // Print the error message here since if some error occurs
1037 // below in finally we will never see this one
1038 s_logger.log(Level.SEVERE,
1039                                  "Unexpected error has occured during test.",
1040                                  throwable);
1041         }
1042         finally
1043         {
1044            try
1045            {
1046               // Get the connection before transaction
1047 // It should be automatically enrolled in the transaction
1048 // Request autocommit false since we are modifying data
1049 connection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(false);
1050               
1051               // Now delete all the records
1052 m_transaction.begin();
1053               try
1054               {
1055                  deleteStatement = connection.prepareStatement(DELETE_ALL);
1056                  DatabaseUtils.executeUpdateAndClose(deleteStatement);
1057      
1058                  m_transaction.commit();
1059               }
1060               catch (Throwable throwable2)
1061               {
1062                  m_transaction.rollback();
1063                  throw throwable2;
1064               }
1065      
1066               //******************************************************************
1067 // Now use new connection to see if all records were delected
1068 iSelectedCount = 0;
1069               // Request autocommit true since we are just reading data from the database
1070 m_connection.setAutoCommit(true);
1071               try
1072               {
1073                  selectStatement = m_connection.prepareStatement(SELECT_VALUE);
1074                  rsResults = selectStatement.executeQuery();
1075                  if (rsResults.next())
1076                  {
1077                     iSelectedCount = 1;
1078                  }
1079               }
1080               finally
1081               {
1082                  DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
1083                  selectStatement = null;
1084               }
1085               assertEquals("No record should exists in the database but there is a record",
1086                             0, iSelectedCount);
1087      
1088               //******************************************************************
1089 // And now use the same connection to see if all records were delected
1090 iSelectedCount = 0;
1091               try
1092               {
1093                  connection.setAutoCommit(true);
1094                  selectStatement = connection.prepareStatement(SELECT_VALUE);
1095                  rsResults = selectStatement.executeQuery();
1096                  if (rsResults.next())
1097                  {
1098                     iSelectedCount = 1;
1099                  }
1100               }
1101               finally
1102               {
1103                  DatabaseUtils.closeResultSetAndStatement(rsResults, selectStatement);
1104                  selectStatement = null;
1105               }
1106               assertEquals("No record should exists in the database but there is a record",
1107                            0, iSelectedCount);
1108            }
1109            finally
1110            {
1111               DatabaseConnectionFactoryImpl.getInstance().returnConnection(connection);
1112               connection = null;
1113            }
1114         }
1115      }
1116   
1117      /**
1118       * Test implemented to try to detect bug in JDBC Transaction manager which
1119       * was causing the transaction to stay active if nothing was done during
1120       * the transaction
1121       *
1122       * @throws Throwable - an error has occured during test
1123       */
1124      public void testEmptyCommit(
1125      ) throws Throwable
1126      {
1127         // The bug discovered by this test was visible only if the first transaction
1128 // was empty so reset the transaction factory so that we do not depend
1129 // on what the other tests did. Return the globally allocated connection
1130 // and reset it to null so that is is not returned again as well otherwise
1131 // we will try to return the connection about which the DatabaseTransactionFactory
1132 // doesn't know since it was reset
1133 DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
1134         m_connection = null;
1135         m_iRequestedConnectionCount--;
1136         DatabaseTransactionFactoryImpl.getInstance().reset();
1137         
1138         m_transaction.begin();
1139         try
1140         {
1141            // Do nothing here
1142 m_transaction.commit();
1143         }
1144         catch (Throwable throwable)
1145         {
1146            m_transaction.rollback();
1147            throw throwable;
1148         }
1149         // Now do it again and it should still work (it wasn't)
1150 m_transaction.begin();
1151         try
1152         {
1153            // Do nothing here
1154 m_transaction.commit();
1155         }
1156         catch (Throwable throwable)
1157         {
1158            m_transaction.rollback();
1159            throw throwable;
1160         }
1161      }
1162   
1163      /**
1164       * Test implemented to try to detect bug in JDBC Transaction manager which
1165       * was causing the transaction to stay active if nothing was done during
1166       * the transaction
1167       *
1168       * @throws Throwable - an error has occured during test
1169       */
1170      public void testEmptyRollback(
1171      ) throws Throwable
1172      {
1173         // The bug discovered by this test was visible only if the first transaction
1174 // was empty so reset the transaction factory so that we do not depend
1175 // on what the other tests did. Return the globally allocated connection
1176 // and reset it to null so that is is not returned again as well otherwise
1177 // we will try to return the connection about which the DatabaseTransactionFactory
1178 // doesn't know since it was reset
1179 DatabaseConnectionFactoryImpl.getInstance().returnConnection(m_connection);
1180         m_connection = null;
1181         m_iRequestedConnectionCount--;
1182         DatabaseTransactionFactoryImpl.getInstance().reset();
1183         
1184         m_transaction.begin();
1185         // Do nothing here
1186 m_transaction.rollback();
1187         // Now do it again and it should still work (it wasn't)
1188 m_transaction.begin();
1189         // Do nothing here
1190 m_transaction.rollback();
1191      }
1192   }
1193}
1194

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags