KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > oracle > OracleMappingDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: OracleMappingDatabaseSchema.java,v 1.8 2007/01/07 06:14:41 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.patterns.mappeddata.persist.db.oracle;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.SQLException JavaDoc;
27 import java.sql.Statement JavaDoc;
28 import java.util.logging.Level JavaDoc;
29 import java.util.logging.Logger JavaDoc;
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 /**
39  * Database specific operations related to persistence of mapping tables
40  * for Oracle database.
41  *
42  * @version $Id: OracleMappingDatabaseSchema.java,v 1.8 2007/01/07 06:14:41 bastafidli Exp $
43  * @author Julian Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.6 2006/08/14 22:42:40 jlegeny
46  */

47 public class OracleMappingDatabaseSchema extends MappingDatabaseSchema
48 {
49    /*
50       Use sequence for ID
51       Name all constraints to easily identify them later.
52
53       CREATE SEQUENCE MAP_TABLE_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE
54       
55       create table BF_MAP_TABLE
56       (
57          ID INTEGER NOT NULL,
58          ID1 INTEGER NOT NULL,
59          ID2 INTEGER NOT NULL,
60          MAPPING_TYPE INTEGER NOT NULL,
61          CUSTOM_DATA VARCHAR(4000) DEFAULT NULL,
62          CREATION_DATE TIMESTAMP(9) NOT NULL,
63          MODIFICATION_DATE TIMESTAMP(9) NOT NULL,
64          CONSTRAINT BF_MAP_TABLE_UQ UNIQUE (ID1, ID2, MAPPING_TYPE),
65          CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID),
66          CONSTRAINT BF_MAP_TABLE_FK1 FOREIGN KEY (ID1)
67             REFERENCES BF_TABLE1 (COLUMN_ID1) ON DELETE CASCADE,
68          CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2)
69             REFERENCES BF_TABLE2 (COLUMN_ID2) ON DELETE CASCADE
70       )
71
72       CREATE OR REPLACE PROCEDURE INSERT_BF_MAP_TABLE
73       (
74          IN_ID1 IN INTEGER,
75          IN_ID2 IN INTEGER,
76          IN_MAPPING_TYPE IN INTEGER,
77          IN_CUSTOM_DATA IN VARCHAR,
78          OUT_KEY OUT INTEGER,
79          OUT_TIMESTAMP OUT TIMESTAMP
80       )
81       IS
82          NEW_OUT_KEY INTEGER;
83          NEW_OUT_TIMESTAMP TIMESTAMP(9);
84       BEGIN
85          SELECT MAPPING_TABLE_SEQ.NEXTVAL INTO NEW_OUT_KEY FROM DUAL;
86          OUT_KEY := NEW_OUT_KEY;
87          SELECT SYSDATE INTO NEW_OUT_TIMESTAMP FROM DUAL;
88          OUT_TIMESTAMP := NEW_OUT_TIMESTAMP;
89          INSERT INTO BF_MAP_TABLE (ID, ID1, ID2, MAPPING_TYPE, CUSTOM_DATA,
90                 CREATION_DATE, MODIFICATION_DATE)
91             VALUES (OUT_KEY, IN_ID1, IN_ID2, IN_MAPPING_TYPE, IN_CUSTOM_DATA,
92                     OUT_TIMESTAMP, OUT_TIMESTAMP);
93       END;
94
95       CREATE OR REPLACE PROCEDURE UPDATE_BF_MAP_TABLE
96       (
97          IN_ID IN INTEGER,
98          IN_ID1 IN INTEGER,
99          IN_ID2 IN INTEGER,
100          IN_MAPPING_TYPE IN INTEGER,
101          IN_CUSTOM_DATA IN VARCHAR,
102          IN_MODIFICATION_DATE IN TIMESTAMP,
103          OUT_TIMESTAMP OUT TIMESTAMP
104       )
105       IS
106          NEW_OUT_TIMESTAMP TIMESTAMP(9);
107       BEGIN
108          SELECT SYSDATE INTO NEW_OUT_TIMESTAMP FROM DUAL;
109          OUT_TIMESTAMP := NEW_OUT_TIMESTAMP;
110          UPDATE + strUserName + BF_MAP_TABLE SET ID1 = IN_ID1, ID2 = IN_ID2
111             MAPPING_TYPE = IN_MAPPING_TYPE, CUSTOM_DATA = IN_CUSTOM_DATA,
112             MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ID
113             AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
114       END;
115    */

116
117    // Cached values ////////////////////////////////////////////////////////////
118

119    /**
120     * Logger for this class
121     */

122    private static Logger JavaDoc s_logger = Log.getInstance(OracleMappingDatabaseSchema.class);
123
124    // Constructors /////////////////////////////////////////////////////////////
125

126    /**
127     * Full constructor.
128     *
129     * @param strMapTableName - table name for mapping table
130     * @param schema1 - schema name the table 1 was defined in
131     * @param strTableName1 - name of the table 1
132     * @param strColumnName1 - name of the column 1
133     * @param schema2 - schema name the table 2 was defined in
134     * @param strTableName2 - name of the table 2
135     * @param strColumnName2 - name of the column 2
136     * @throws OSSException - an error has occured
137     */

138    public OracleMappingDatabaseSchema(
139       String JavaDoc strMapTableName,
140       Class JavaDoc schema1,
141       String JavaDoc strTableName1,
142       String JavaDoc strColumnName1,
143       Class JavaDoc schema2,
144       String JavaDoc strTableName2,
145       String JavaDoc strColumnName2
146    ) throws OSSException
147    {
148       super(strMapTableName, schema1, strTableName1, strColumnName1,
149             schema2, strTableName2, strColumnName2);
150    }
151
152    // Public methods ///////////////////////////////////////////////////////////
153

154    /**
155     * {@inheritDoc}
156     */

157    public void create(
158       Connection JavaDoc cntDBConnection,
159       String JavaDoc strUserName
160    ) throws SQLException JavaDoc, OSSException
161    {
162       Statement JavaDoc 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             // Close any results
171
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
172          }
173          s_logger.log(Level.FINEST, "Sequence " + m_strMapTableName + "_SEQ created.");
174
175          ///////////////////////////////////////////////////////////////////////
176

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             // Close any results
183
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
184          }
185          s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() +
186                                     m_strMapTableName + " created.");
187
188          ///////////////////////////////////////////////////////////////////////
189

190          if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName)))
191          {
192             // Close any results
193
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
194          }
195          s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created.");
196          
197          ///////////////////////////////////////////////////////////////////////
198

199          if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName)))
200          {
201             // Close any results
202
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
203          }
204          s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created.");
205       }
206       catch (SQLException JavaDoc 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    /**
218     * {@inheritDoc}
219     */

220    public MappedData insertMappedData(
221       Connection JavaDoc dbConnection,
222       MappedData data
223    ) throws OSSException
224    {
225       CallableStatement JavaDoc insertStatement = null;
226       
227       try
228       {
229          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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 JavaDoc eExc)
247       {
248          handleInsertMappedDataException(eExc);
249       }
250       finally
251       {
252          DatabaseUtils.closeStatement(insertStatement);
253       }
254
255       return data;
256    }
257
258    /**
259     * {@inheritDoc}
260     */

261    public MappedData updateMappedData(
262       Connection JavaDoc dbConnection,
263       MappedData data
264    ) throws OSSException
265    {
266       CallableStatement JavaDoc updateStatement = null;
267       
268       try
269       {
270          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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 JavaDoc eExc)
290       {
291          handleUpdateMappedDataException(eExc, dbConnection, data);
292       }
293       finally
294       {
295          DatabaseUtils.closeStatement(updateStatement);
296       }
297
298       return data;
299    }
300
301    // Helper methods ///////////////////////////////////////////////////////////
302

303    /**
304     * Construt SQL command for stored procedure that process inserting data.
305     *
306     * @param strMapTableName - table name
307     * @param strUserName - user name
308     * @return String - constructed command of insert stored procedure
309     */

310    public String JavaDoc constructInsertStoredProc(
311       String JavaDoc strUserName,
312       String JavaDoc strMapTableName
313    )
314    {
315       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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    /**
354     * Construt SQL command for stored procedure that process inserting data.
355     *
356     * @param strMapTableName - table name
357     * @param strUserName - user name
358     * @return String - constructed command of insert stored procedure
359     */

360    public static String JavaDoc constructUpdateStoredProc(
361       String JavaDoc strUserName,
362       String JavaDoc strMapTableName
363    )
364    {
365       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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