1 21 22 package org.opensubsystems.patterns.mappeddata.persist.db.oracle; 23 24 import java.sql.CallableStatement ; 25 import java.sql.Connection ; 26 import java.sql.SQLException ; 27 import java.sql.Statement ; 28 import java.util.logging.Level ; 29 import java.util.logging.Logger ; 30 31 import org.opensubsystems.core.error.OSSException; 32 import org.opensubsystems.core.persist.db.oracle.OracleDataUtils; 33 import org.opensubsystems.core.util.DatabaseUtils; 34 import org.opensubsystems.core.util.Log; 35 import org.opensubsystems.patterns.mappeddata.data.MappedData; 36 import org.opensubsystems.patterns.mappeddata.persist.db.MappingDatabaseSchema; 37 38 47 public class OracleMappingDatabaseSchema extends MappingDatabaseSchema 48 { 49 116 117 119 122 private static Logger s_logger = Log.getInstance(OracleMappingDatabaseSchema.class); 123 124 126 138 public OracleMappingDatabaseSchema( 139 String strMapTableName, 140 Class schema1, 141 String strTableName1, 142 String strColumnName1, 143 Class schema2, 144 String strTableName2, 145 String strColumnName2 146 ) throws OSSException 147 { 148 super(strMapTableName, schema1, strTableName1, strColumnName1, 149 schema2, strTableName2, strColumnName2); 150 } 151 152 154 157 public void create( 158 Connection cntDBConnection, 159 String strUserName 160 ) throws SQLException , OSSException 161 { 162 Statement stmQuery = null; 163 try 164 { 165 stmQuery = cntDBConnection.createStatement(); 166 167 if (stmQuery.execute("CREATE SEQUENCE " + m_strMapTableName + 168 "_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE")) 169 { 170 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 172 } 173 s_logger.log(Level.FINEST, "Sequence " + m_strMapTableName + "_SEQ created."); 174 175 177 if (stmQuery.execute(constructSQL( 178 "ID INTEGER NOT NULL,", "INTEGER", 179 "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ")" + NL, 180 "TIMESTAMP(9)", m_strConstraintBody, true, true))) 181 { 182 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 184 } 185 s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() + 186 m_strMapTableName + " created."); 187 188 190 if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName))) 191 { 192 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 194 } 195 s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created."); 196 197 199 if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName))) 200 { 201 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 203 } 204 s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created."); 205 } 206 catch (SQLException sqleExc) 207 { 208 s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc); 209 throw sqleExc; 210 } 211 finally 212 { 213 DatabaseUtils.closeStatement(stmQuery); 214 } 215 } 216 217 220 public MappedData insertMappedData( 221 Connection dbConnection, 222 MappedData data 223 ) throws OSSException 224 { 225 CallableStatement insertStatement = null; 226 227 try 228 { 229 StringBuffer buffer = new StringBuffer (); 230 int iIndex = 1; 231 232 buffer.append("{call INSERT_"); 233 buffer.append(getSchemaPrefix()); 234 buffer.append(m_strMapTableName); 235 buffer.append(" (?, ?, ?, ?, ?, ?)}"); 236 237 insertStatement = dbConnection.prepareCall(buffer.toString()); 238 239 insertStatement.setInt(iIndex++, data.getMappedId1()); 240 insertStatement.setInt(iIndex++, data.getMappedId2()); 241 insertStatement.setInt(iIndex++, data.getMappingType()); 242 insertStatement.setString(iIndex++, data.getCustomData()); 243 244 OracleDataUtils.insertAndFetchGeneratedValues(insertStatement, iIndex, data); 245 } 246 catch (SQLException eExc) 247 { 248 handleInsertMappedDataException(eExc); 249 } 250 finally 251 { 252 DatabaseUtils.closeStatement(insertStatement); 253 } 254 255 return data; 256 } 257 258 261 public MappedData updateMappedData( 262 Connection dbConnection, 263 MappedData data 264 ) throws OSSException 265 { 266 CallableStatement updateStatement = null; 267 268 try 269 { 270 StringBuffer buffer = new StringBuffer (); 271 int iIndex = 1; 272 273 buffer.append("{call UPDATE_"); 274 buffer.append(getSchemaPrefix()); 275 buffer.append(m_strMapTableName); 276 buffer.append(" (?, ?, ?, ?, ?, ?, ?)}"); 277 278 updateStatement = dbConnection.prepareCall(buffer.toString()); 279 280 updateStatement.setInt(iIndex++, data.getId()); 281 updateStatement.setInt(iIndex++, data.getMappedId1()); 282 updateStatement.setInt(iIndex++, data.getMappedId2()); 283 updateStatement.setInt(iIndex++, data.getMappingType()); 284 updateStatement.setString(iIndex++, data.getCustomData()); 285 updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp()); 286 287 OracleDataUtils.updateAndFetchGeneratedValues(updateStatement, iIndex, data); 288 } 289 catch (SQLException eExc) 290 { 291 handleUpdateMappedDataException(eExc, dbConnection, data); 292 } 293 finally 294 { 295 DatabaseUtils.closeStatement(updateStatement); 296 } 297 298 return data; 299 } 300 301 303 310 public String constructInsertStoredProc( 311 String strUserName, 312 String strMapTableName 313 ) 314 { 315 StringBuffer buffer = new StringBuffer (); 316 317 buffer.append("CREATE OR REPLACE PROCEDURE INSERT_"); 318 buffer.append(getSchemaPrefix()); 319 buffer.append(strMapTableName); 320 buffer.append(NL + "(" + NL + 321 " IN_ID1 IN INTEGER, " + NL + 322 " IN_ID2 IN INTEGER, " + NL + 323 " IN_MAPPING_TYPE IN INTEGER, " + NL + 324 " IN_CUSTOM_DATA IN VARCHAR, " + NL + 325 " OUT_KEY OUT INTEGER, " + NL + 326 " OUT_TIMESTAMP OUT TIMESTAMP " + NL + 327 ") " + NL + 328 "IS " + NL + 329 " NEW_OUT_KEY INTEGER; " + NL + 330 " NEW_OUT_TIMESTAMP TIMESTAMP(9); " + NL + 331 "BEGIN " + NL + 332 " SELECT "); 333 buffer.append(strMapTableName); 334 buffer.append("_SEQ.NEXTVAL INTO NEW_OUT_KEY FROM DUAL;" + NL + 335 " OUT_KEY := NEW_OUT_KEY; " + NL + 336 " SELECT SYSDATE INTO NEW_OUT_TIMESTAMP FROM DUAL; " + NL + 337 " OUT_TIMESTAMP := NEW_OUT_TIMESTAMP; " + NL + 338 " INSERT INTO "); 339 buffer.append(strUserName); 340 buffer.append("."); 341 buffer.append(getSchemaPrefix()); 342 buffer.append(strMapTableName); 343 buffer.append("(ID, ID1, ID2, " + NL + 344 " MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + NL + 345 " VALUES (OUT_KEY, IN_ID1, IN_ID2, IN_MAPPING_TYPE, IN_CUSTOM_DATA, " + NL + 346 " OUT_TIMESTAMP, OUT_TIMESTAMP); " + NL + 347 "END;"); 348 349 return buffer.toString(); 350 } 351 352 353 360 public static String constructUpdateStoredProc( 361 String strUserName, 362 String strMapTableName 363 ) 364 { 365 StringBuffer buffer = new StringBuffer (); 366 367 buffer.append("CREATE OR REPLACE PROCEDURE UPDATE_"); 368 buffer.append(getSchemaPrefix()); 369 buffer.append(strMapTableName); 370 buffer.append(NL + "(" + NL + 371 " IN_ID IN INTEGER, " + NL + 372 " IN_ID1 IN INTEGER, " + NL + 373 " IN_ID2 IN INTEGER, " + NL + 374 " IN_MAPPING_TYPE IN INTEGER, " + NL + 375 " IN_CUSTOM_DATA IN VARCHAR, " + NL + 376 " IN_MODIFICATION_DATE IN TIMESTAMP, " + NL + 377 " OUT_TIMESTAMP OUT TIMESTAMP " + NL + 378 ") " + NL + 379 "IS " + NL + 380 " NEW_OUT_TIMESTAMP TIMESTAMP(9); " + NL + 381 "BEGIN " + NL + 382 " SELECT SYSDATE INTO NEW_OUT_TIMESTAMP FROM DUAL; " + NL + 383 " OUT_TIMESTAMP := NEW_OUT_TIMESTAMP; " + NL + 384 "UPDATE "); 385 buffer.append(strUserName); 386 buffer.append("."); 387 buffer.append(getSchemaPrefix()); 388 buffer.append(strMapTableName); 389 buffer.append(" SET ID1 = IN_ID1, ID2 = IN_ID2, " + NL + 390 " MAPPING_TYPE = IN_MAPPING_TYPE, CUSTOM_DATA = IN_CUSTOM_DATA, " + NL + 391 " MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ID " + NL + 392 " AND MODIFICATION_DATE = IN_MODIFICATION_DATE; " + NL + 393 "END;"); 394 395 return buffer.toString(); 396 } 397 } 398 | Popular Tags |