KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: DeleteWithSubqueryTest.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.PreparedStatement JavaDoc;
25 import java.sql.SQLException JavaDoc;
26
27 import junit.extensions.TestSetup;
28 import junit.framework.Test;
29 import junit.framework.TestSuite;
30
31 import org.opensubsystems.core.error.OSSException;
32 import org.opensubsystems.core.persist.db.Database;
33 import org.opensubsystems.core.persist.db.DatabaseImpl;
34 import org.opensubsystems.core.persist.db.DatabaseTest;
35 import org.opensubsystems.core.persist.db.DatabaseTestSetup;
36 import org.opensubsystems.core.persist.db.DatabaseTestSuite;
37 import org.opensubsystems.core.util.DatabaseUtils;
38
39 /**
40  * Test for delete query using subquery.
41  *
42  * @version $Id: DeleteWithSubqueryTest.java,v 1.12 2007/01/07 06:14:51 bastafidli Exp $
43  * @author Julo Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.9 2005/09/09 06:50:44 bastafidli
46  */

47 public final class DeleteWithSubqueryTest
48 {
49    // Constructors /////////////////////////////////////////////////////////////
50

51    /**
52     * Private constructor since this class cannot be instantiated
53     */

54    private DeleteWithSubqueryTest(
55    )
56    {
57       // Do nothing
58
}
59    
60    // Public methods ///////////////////////////////////////////////////////////
61

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

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

83    public static class DeleteWithSubqueryTestInternal extends DatabaseTest
84    {
85       /**
86        * Static initializer
87        */

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

110       public DeleteWithSubqueryTestInternal(
111          String JavaDoc strTestName
112       )
113       {
114          super(strTestName);
115       }
116       
117       /**
118        * Test if the DB supports delete using subquery in the SQL command.
119        *
120        * @throws Throwable - an error has occured during test
121        */

122       public void testDeleteWithSubquery(
123       ) throws Throwable JavaDoc
124       {
125          final String JavaDoc INSERT_VALUE_1
126                        = "insert into DELETE_TEST (TEST_ID,TEST_VALUE) values (?,?)";
127          final String JavaDoc INSERT_VALUE_2
128                        = "insert into DELETE_RELATED_TEST (TEST_REL_ID,TEST_ID,TEST_VALUE) " +
129                          "values (?,?,?)";
130          final String JavaDoc DELETE_VALUE
131                        = "delete from DELETE_TEST where DELETE_TEST.TEST_ID " +
132                          " in (select DELETE_TEST.TEST_ID from DELETE_TEST" +
133                          " left join DELETE_RELATED_TEST on " +
134                          " DELETE_TEST.TEST_ID = DELETE_RELATED_TEST.TEST_ID" +
135                          " where DELETE_RELATED_TEST.TEST_ID is null)";
136    
137          final String JavaDoc DELETE_VALUE_1 = "delete from DELETE_TEST where TEST_ID in (?, ?)";
138          final String JavaDoc DELETE_VALUE_2 = "delete from DELETE_RELATED_TEST" +
139                                        " where TEST_REL_ID in (?, ?)";
140    
141          PreparedStatement JavaDoc insertStatement = null;
142          PreparedStatement JavaDoc deleteStatement = null;
143          int iDeletedCount = 0;
144    
145          try
146          {
147             // insert value
148
m_transaction.begin();
149             try
150             {
151                try
152                {
153                   // insert values to the table TRANSACTION_TEST
154
insertStatement = m_connection.prepareStatement(INSERT_VALUE_1);
155                   insertStatement.setInt(1, 100);
156                   insertStatement.setString(2, "dt_value_1");
157                   insertStatement.executeUpdate();
158    
159                   // insert values to the table TRANSACTION_TEST
160
insertStatement = m_connection.prepareStatement(INSERT_VALUE_1);
161                   insertStatement.setInt(1, 200);
162                   insertStatement.setString(2, "dt_value_2");
163                   insertStatement.executeUpdate();
164    
165                   // insert values to the table TRANSACTION_RELATED_TEST
166
insertStatement = m_connection.prepareStatement(INSERT_VALUE_2);
167                   insertStatement.setInt(1, 100);
168                   insertStatement.setInt(2, 100);
169                   insertStatement.setString(3, "drt_value_1");
170                   insertStatement.executeUpdate();
171    
172                   // insert values to the table TRANSACTION_RELATED_TEST
173
insertStatement = m_connection.prepareStatement(INSERT_VALUE_2);
174                   insertStatement.setInt(1, 200);
175                   insertStatement.setInt(2, 100);
176                   insertStatement.setString(3, "drt_value_2");
177                   insertStatement.executeUpdate();
178                }
179                finally
180                {
181                   DatabaseUtils.closeStatement(insertStatement);
182                }
183                m_transaction.commit();
184             }
185             catch (Throwable JavaDoc throwable)
186             {
187                m_transaction.rollback();
188                throw throwable;
189             }
190    
191             // try to delete records from TRANSACTION_TABLE that does not have belonging records
192
// in the TRANSACTION_RELATED_TEST table
193
m_transaction.begin();
194             try
195             {
196                deleteStatement = m_connection.prepareStatement(DELETE_VALUE);
197                iDeletedCount = DatabaseUtils.executeUpdateAndClose(deleteStatement);
198                m_transaction.commit();
199             }
200             catch (SQLException JavaDoc sqleExc)
201             {
202                m_transaction.rollback();
203                fail("It seems like database doesn't support delete with subquery: " +
204                     sqleExc.getMessage());
205                throw sqleExc;
206             }
207             catch (Throwable JavaDoc throwable)
208             {
209                m_transaction.rollback();
210                throw throwable;
211             }
212             assertEquals("Exactly one record should be deleted.",
213                                 1, iDeletedCount);
214    
215          }
216          finally
217          {
218             // delete inserted data
219
m_transaction.begin();
220             try
221             {
222                // delete from TRANSACTION_RELATED_TEST table
223
deleteStatement = m_connection.prepareStatement(DELETE_VALUE_2);
224                deleteStatement.setInt(1, 100);
225                deleteStatement.setInt(2, 200);
226                deleteStatement.execute();
227                m_transaction.commit();
228             }
229             catch (Throwable JavaDoc thr)
230             {
231                m_transaction.rollback();
232                throw new Exception JavaDoc(thr);
233             }
234             finally
235             {
236                DatabaseUtils.closeStatement(deleteStatement);
237             }
238             
239             m_transaction.begin();
240             try
241             {
242                // delete from TRANSACTION_RELATED_TEST table
243
deleteStatement = m_connection.prepareStatement(DELETE_VALUE_1);
244                deleteStatement.setInt(1, 100);
245                deleteStatement.setInt(2, 200);
246                deleteStatement.execute();
247                m_transaction.commit();
248             }
249             catch (Throwable JavaDoc thr)
250             {
251                m_transaction.rollback();
252                throw new Exception JavaDoc(thr);
253             }
254             finally
255             {
256                DatabaseUtils.closeStatement(deleteStatement);
257             }
258          }
259       }
260    }
261 }
262
Popular Tags