1 21 22 package org.opensubsystems.core.persist.db.driver; 23 24 import java.sql.PreparedStatement ; 25 import java.sql.SQLException ; 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 47 public final class DeleteWithSubqueryTest 48 { 49 51 54 private DeleteWithSubqueryTest( 55 ) 56 { 57 } 59 60 62 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 83 public static class DeleteWithSubqueryTestInternal extends DatabaseTest 84 { 85 88 static 89 { 90 Database dbDatabase; 92 93 try 94 { 95 dbDatabase = DatabaseImpl.getInstance(); 96 dbDatabase.add(DatabaseTestSchema.class); 98 } 99 catch (OSSException bfeExc) 100 { 101 throw new RuntimeException ("Unexpected exception.", bfeExc); 102 } 103 } 104 105 110 public DeleteWithSubqueryTestInternal( 111 String strTestName 112 ) 113 { 114 super(strTestName); 115 } 116 117 122 public void testDeleteWithSubquery( 123 ) throws Throwable 124 { 125 final String INSERT_VALUE_1 126 = "insert into DELETE_TEST (TEST_ID,TEST_VALUE) values (?,?)"; 127 final String INSERT_VALUE_2 128 = "insert into DELETE_RELATED_TEST (TEST_REL_ID,TEST_ID,TEST_VALUE) " + 129 "values (?,?,?)"; 130 final String 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 DELETE_VALUE_1 = "delete from DELETE_TEST where TEST_ID in (?, ?)"; 138 final String DELETE_VALUE_2 = "delete from DELETE_RELATED_TEST" + 139 " where TEST_REL_ID in (?, ?)"; 140 141 PreparedStatement insertStatement = null; 142 PreparedStatement deleteStatement = null; 143 int iDeletedCount = 0; 144 145 try 146 { 147 m_transaction.begin(); 149 try 150 { 151 try 152 { 153 insertStatement = m_connection.prepareStatement(INSERT_VALUE_1); 155 insertStatement.setInt(1, 100); 156 insertStatement.setString(2, "dt_value_1"); 157 insertStatement.executeUpdate(); 158 159 insertStatement = m_connection.prepareStatement(INSERT_VALUE_1); 161 insertStatement.setInt(1, 200); 162 insertStatement.setString(2, "dt_value_2"); 163 insertStatement.executeUpdate(); 164 165 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 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 throwable) 186 { 187 m_transaction.rollback(); 188 throw throwable; 189 } 190 191 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 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 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 m_transaction.begin(); 220 try 221 { 222 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 thr) 230 { 231 m_transaction.rollback(); 232 throw new Exception (thr); 233 } 234 finally 235 { 236 DatabaseUtils.closeStatement(deleteStatement); 237 } 238 239 m_transaction.begin(); 240 try 241 { 242 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 thr) 250 { 251 m_transaction.rollback(); 252 throw new Exception (thr); 253 } 254 finally 255 { 256 DatabaseUtils.closeStatement(deleteStatement); 257 } 258 } 259 } 260 } 261 } 262 | Popular Tags |