1 21 22 package org.opensubsystems.patterns.mappeddata.persist.db.sybase; 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.sybase.SybaseDataUtils; 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 39 48 public class SybaseMappingDatabaseSchema extends MappingDatabaseSchema 49 { 50 103 104 106 109 private static Logger s_logger = Log.getInstance(SybaseMappingDatabaseSchema.class); 110 111 113 125 public SybaseMappingDatabaseSchema( 126 String strMapTableName, 127 Class schema1, 128 String strTableName1, 129 String strColumnName1, 130 Class schema2, 131 String strTableName2, 132 String strColumnName2 133 ) throws OSSException 134 { 135 super(strMapTableName, schema1, strTableName1, strColumnName1, 136 schema2, strTableName2, strColumnName2); 137 } 138 139 141 144 public void create( 145 Connection cntDBConnection, 146 String strUserName 147 ) throws SQLException , OSSException 148 { 149 Statement stmQuery = null; 150 try 151 { 152 stmQuery = cntDBConnection.createStatement(); 153 154 if (stmQuery.execute(constructSQL( 155 "ID NUMERIC(10, 0) IDENTITY NOT NULL,", "NUMERIC(10, 0)", 156 "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ")" + NL, 157 "DATETIME", m_strConstraintBody, false, true))) 158 { 159 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 161 } 162 s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() + 163 m_strMapTableName + " created."); 164 165 167 if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName))) 168 { 169 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 171 } 172 s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created."); 173 174 176 if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName))) 177 { 178 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 180 } 181 s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created."); 182 } 183 catch (SQLException sqleExc) 184 { 185 s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc); 186 throw sqleExc; 187 } 188 finally 189 { 190 DatabaseUtils.closeStatement(stmQuery); 191 } 192 } 193 194 197 public MappedData insertMappedData( 198 Connection dbConnection, 199 MappedData data 200 ) throws OSSException 201 { 202 CallableStatement insertStatement = null; 203 204 try 205 { 206 StringBuffer buffer = new StringBuffer (); 207 int iIndex = 1; 208 209 buffer.append("{call INSERT_"); 210 buffer.append(getSchemaPrefix()); 211 buffer.append(m_strMapTableName); 212 buffer.append(" (?, ?, ?, ?, ?, ?)}"); 213 214 insertStatement = dbConnection.prepareCall(buffer.toString()); 215 216 insertStatement.setInt(iIndex++, data.getMappedId1()); 217 insertStatement.setInt(iIndex++, data.getMappedId2()); 218 insertStatement.setInt(iIndex++, data.getMappingType()); 219 insertStatement.setString(iIndex++, data.getCustomData()); 220 221 SybaseDataUtils.insertAndFetchGeneratedValues(insertStatement, iIndex, data); 222 } 223 catch (SQLException eExc) 224 { 225 handleInsertMappedDataException(eExc); 226 } 227 finally 228 { 229 DatabaseUtils.closeStatement(insertStatement); 230 } 231 232 return data; 233 } 234 235 238 public MappedData updateMappedData( 239 Connection dbConnection, 240 MappedData data 241 ) throws OSSException 242 { 243 CallableStatement updateStatement = null; 244 245 try 246 { 247 StringBuffer buffer = new StringBuffer (); 248 int iIndex = 1; 249 250 buffer.append("{call UPDATE_"); 251 buffer.append(getSchemaPrefix()); 252 buffer.append(m_strMapTableName); 253 buffer.append(" (?, ?, ?, ?, ?, ?, ?)}"); 254 255 updateStatement = dbConnection.prepareCall(buffer.toString()); 256 257 updateStatement.setInt(iIndex++, data.getId()); 258 updateStatement.setInt(iIndex++, data.getMappedId1()); 259 updateStatement.setInt(iIndex++, data.getMappedId2()); 260 updateStatement.setInt(iIndex++, data.getMappingType()); 261 updateStatement.setString(iIndex++, data.getCustomData()); 262 updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp()); 263 264 SybaseDataUtils.updateAndFetchGeneratedValues(updateStatement, iIndex, data); 265 } 266 catch (SQLException eExc) 267 { 268 handleUpdateMappedDataException(eExc, dbConnection, data); 269 } 270 finally 271 { 272 DatabaseUtils.closeStatement(updateStatement); 273 } 274 275 return data; 276 } 277 278 280 287 public String constructInsertStoredProc( 288 String strUserName, 289 String strMapTableName 290 ) 291 { 292 StringBuffer buffer = new StringBuffer (); 293 294 buffer.append("CREATE PROCEDURE INSERT_"); 295 buffer.append(getSchemaPrefix()); 296 buffer.append(strMapTableName); 297 buffer.append(NL + 298 " @IN_ID1 INTEGER, " + NL + 299 " @IN_ID2 INTEGER, " + NL + 300 " @IN_MAPPING_TYPE INTEGER, " + NL + 301 " @IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL + 302 " @OUT_KEY INTEGER OUTPUT, " + NL + 303 " @OUT_TIMESTAMP DATETIME OUTPUT " + NL + 304 "AS " + NL + 305 " SELECT @OUT_TIMESTAMP = GETDATE() " + NL + 306 " INSERT INTO "); 307 buffer.append(strUserName); 308 buffer.append("."); 309 buffer.append(getSchemaPrefix()); 310 buffer.append(strMapTableName); 311 buffer.append(" (ID1, ID2, " + NL + 312 " MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + NL + 313 " VALUES (@IN_ID1, @IN_ID2, @IN_MAPPING_TYPE, @IN_CUSTOM_DATA, " + NL + 314 " @OUT_TIMESTAMP, @OUT_TIMESTAMP) " + NL + 315 " SET @OUT_KEY = @@IDENTITY " + NL + 316 "RETURN"); 317 318 return buffer.toString(); 319 } 320 321 328 public static String constructUpdateStoredProc( 329 String strUserName, 330 String strMapTableName 331 ) 332 { 333 StringBuffer buffer = new StringBuffer (); 334 335 buffer.append("CREATE PROCEDURE UPDATE_"); 336 buffer.append(getSchemaPrefix()); 337 buffer.append(strMapTableName); 338 buffer.append(NL + 339 " @IN_ID INTEGER, " + NL + 340 " @IN_ID1 INTEGER, " + NL + 341 " @IN_ID2 INTEGER, " + NL + 342 " @IN_MAPPING_TYPE INTEGER, " + NL + 343 " @IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL + 344 " @IN_MODIFICATION_DATE DATETIME, " + NL + 345 " @OUT_TIMESTAMP DATETIME OUTPUT " + NL + 346 "AS " + NL + 347 " SET @OUT_TIMESTAMP = GETDATE() " + NL + 348 " UPDATE "); 349 buffer.append(strUserName); 350 buffer.append("."); 351 buffer.append(getSchemaPrefix()); 352 buffer.append(strMapTableName); 353 buffer.append(" SET ID1 = @IN_ID1, ID2 = @IN_ID2, " + NL + 354 " MAPPING_TYPE = @IN_MAPPING_TYPE, CUSTOM_DATA = @IN_CUSTOM_DATA, " + NL + 355 " MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_ID " + NL + 356 " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE " + NL + 357 "RETURN"); 358 359 return buffer.toString(); 360 } 361 } 362 | Popular Tags |