KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > sapdb > SapDBMappingDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: SapDBMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:32 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.sapdb;
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.sapdb.SapDBDataUtils;
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 SAP DB database.
41  *
42  * @version $Id: SapDBMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:32 bastafidli Exp $
43  * @author Julian Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.6 2006/08/14 22:42:38 jlegeny
46  */

47 public class SapDBMappingDatabaseSchema extends MappingDatabaseSchema
48 {
49    /*
50       Use autogenerated numbers for IDs using sequence
51       Name all constraints to easily identify them later.
52
53       create table BF_MAP_TABLE
54       (
55          ID SERIAL,
56          ID1 INTEGER NOT NULL,
57          ID2 INTEGER NOT NULL,
58          MAPPING_TYPE INTEGER NOT NULL,
59          CUSTOM_DATA VARCHAR(4000) DEFAULT NULL,
60          CREATION_DATE TIMESTAMP NOT NULL,
61          MODIFICATION_DATE TIMESTAMP NOT NULL,
62          CONSTRAINT BF_MAP_TABLE_UQ UNIQUE (ID1, ID2, MAPPING_TYPE),
63          CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID),
64          CONSTRAINT BF_MAP_TABLE_FK1 FOREIGN KEY (ID1)
65             REFERENCES BF_TABLE1 (COLUMN_ID1) ON DELETE CASCADE,
66          CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2)
67             REFERENCES BF_TABLE2 (COLUMN_ID2) ON DELETE CASCADE
68          
69       )
70
71       CREATE DBPROCEDURE INSERT_BF_MAP_TABLE
72       (
73          IN IN_ID1 INTEGER,
74          IN IN_ID2 INTEGER,
75          IN IN_MAPPING_TYPE INTEGER,
76          IN IN_CUSTOM_DATA VARCHAR(4000),
77          OUT OUT_KEY INTEGER,
78          OUT OUT_TIMESTAMP TIMESTAMP
79       )
80       AS
81       TRY
82          SET OUT_TIMESTAMP = now();
83          INSERT INTO + strUserName + .BF_MAP_TABLE (ID1, ID2, MAPPING_TYPE, CUSTOM_DATA,
84                 CREATION_DATE, MODIFICATION_DATE)
85             VALUES (:IN_ID1, :IN_ID2, :IN_MAPPING_TYPE, :IN_CUSTOM_DATA,
86                     :OUT_TIMESTAMP, :OUT_TIMESTAMP);
87          SET OUT_KEY = BASTA.MAPPING_TABLE.CURRVAL;
88       CATCH
89          IF $RC <> 0 THEN STOP ($RC, $ERRMSG);
90
91
92       CREATE DBPROCEDURE UPDATE_BF_MAP_TABLE
93       (
94          IN IN_ID INTEGER,
95          IN IN_ID1 INTEGER,
96          IN IN_ID2 INTEGER,
97          IN IN_MAPPING_TYPE INTEGER,
98          IN IN_CUSTOM_DATA VARCHAR(4000),
99          IN IN_MODIFICATION_DATE TIMESTAMP,
100          OUT OUT_TIMESTAMP TIMESTAMP
101       )
102       AS
103       TRY
104          SET OUT_TIMESTAMP = now();
105          UPDATE + strUserName + BF_MAP_TABLE SET ID1 = :IN_ID1, ID2 = :IN_ID2
106             MAPPING_TYPE = :IN_MAPPING_TYPE, CUSTOM_DATA = :IN_CUSTOM_DATA,
107             MODIFICATION_DATE = :OUT_TIMESTAMP WHERE ID = :IN_ID
108             AND MODIFICATION_DATE = :IN_MODIFICATION_DATE;
109       CATCH
110          IF $RC <> 0 THEN STOP ($RC, $ERRMSG);
111    */

112
113    // Cached values ////////////////////////////////////////////////////////////
114

115    /**
116     * Logger for this class
117     */

118    private static Logger JavaDoc s_logger = Log.getInstance(SapDBMappingDatabaseSchema.class);
119
120    // Constructors /////////////////////////////////////////////////////////////
121

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

134    public SapDBMappingDatabaseSchema(
135       String JavaDoc strMapTableName,
136       Class JavaDoc schema1,
137       String JavaDoc strTableName1,
138       String JavaDoc strColumnName1,
139       Class JavaDoc schema2,
140       String JavaDoc strTableName2,
141       String JavaDoc strColumnName2
142    ) throws OSSException
143    {
144       super(strMapTableName, schema1, strTableName1, strColumnName1,
145             schema2, strTableName2, strColumnName2);
146    }
147
148    // Public methods ///////////////////////////////////////////////////////////
149

150    /**
151     * {@inheritDoc}
152     */

153    public void create(
154       Connection JavaDoc cntDBConnection,
155       String JavaDoc strUserName
156    ) throws SQLException JavaDoc, OSSException
157    {
158       Statement JavaDoc stmQuery = null;
159       try
160       {
161          stmQuery = cntDBConnection.createStatement();
162
163          if (stmQuery.execute(constructSQL(
164                                 "ID SERIAL,", "INTEGER",
165                                 "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ")" + NL,
166                                 "TIMESTAMP", m_strConstraintBody, true, true)))
167          {
168             // Close any results
169
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
170          }
171          s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() +
172                                     m_strMapTableName + " created.");
173
174          ///////////////////////////////////////////////////////////////////////
175

176          if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName)))
177          {
178             // Close any results
179
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
180          }
181          s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created.");
182          
183          ///////////////////////////////////////////////////////////////////////
184

185          if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName)))
186          {
187             // Close any results
188
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
189          }
190          s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created.");
191       }
192       catch (SQLException JavaDoc sqleExc)
193       {
194          s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc);
195          throw sqleExc;
196       }
197       finally
198       {
199          DatabaseUtils.closeStatement(stmQuery);
200       }
201    }
202
203    /**
204     * {@inheritDoc}
205     */

206    public MappedData insertMappedData(
207       Connection JavaDoc dbConnection,
208       MappedData data
209    ) throws OSSException
210    {
211       CallableStatement JavaDoc insertStatement = null;
212       
213       try
214       {
215          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
216          int iIndex = 1;
217       
218          buffer.append("call INSERT_");
219          buffer.append(getSchemaPrefix());
220          buffer.append(m_strMapTableName);
221          buffer.append(" (?, ?, ?, ?, ?, ?)");
222       
223          insertStatement = dbConnection.prepareCall(buffer.toString());
224
225          insertStatement.setInt(iIndex++, data.getMappedId1());
226          insertStatement.setInt(iIndex++, data.getMappedId2());
227          insertStatement.setInt(iIndex++, data.getMappingType());
228          insertStatement.setString(iIndex++, data.getCustomData());
229
230          SapDBDataUtils.insertAndFetchGeneratedValues(insertStatement, iIndex, data);
231       }
232       catch (SQLException JavaDoc eExc)
233       {
234          handleInsertMappedDataException(eExc);
235       }
236       finally
237       {
238          DatabaseUtils.closeStatement(insertStatement);
239       }
240
241       return data;
242    }
243
244    /**
245     * {@inheritDoc}
246     */

247    public MappedData updateMappedData(
248       Connection JavaDoc dbConnection,
249       MappedData data
250    ) throws OSSException
251    {
252       CallableStatement JavaDoc updateStatement = null;
253       
254       try
255       {
256          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
257          int iIndex = 1;
258       
259          buffer.append("call UPDATE_");
260          buffer.append(getSchemaPrefix());
261          buffer.append(m_strMapTableName);
262          buffer.append(" (?, ?, ?, ?, ?, ?, ?)");
263          
264          updateStatement = dbConnection.prepareCall(buffer.toString());
265
266          updateStatement.setInt(iIndex++, data.getId());
267          updateStatement.setInt(iIndex++, data.getMappedId1());
268          updateStatement.setInt(iIndex++, data.getMappedId2());
269          updateStatement.setInt(iIndex++, data.getMappingType());
270          updateStatement.setString(iIndex++, data.getCustomData());
271          updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp());
272
273          SapDBDataUtils.updateAndFetchGeneratedValues(updateStatement, iIndex, data);
274       }
275       catch (SQLException JavaDoc eExc)
276       {
277          handleUpdateMappedDataException(eExc, dbConnection, data);
278       }
279       finally
280       {
281          DatabaseUtils.closeStatement(updateStatement);
282       }
283
284       return data;
285    }
286
287    // Helper methods ///////////////////////////////////////////////////////////
288

289    /**
290     * Construt SQL command for stored procedure that process inserting data.
291     *
292     * @param strMapTableName - table name
293     * @param strUserName - user name
294     * @return String - constructed command of insert stored procedure
295     */

296    public static String JavaDoc constructInsertStoredProc(
297       String JavaDoc strUserName,
298       String JavaDoc strMapTableName
299    )
300    {
301       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
302
303       buffer.append("CREATE DBPROCEDURE INSERT_");
304       buffer.append(getSchemaPrefix());
305       buffer.append(strMapTableName);
306       buffer.append(NL + "(" + NL +
307          " IN IN_ID1 INTEGER, " + NL +
308          " IN IN_ID2 INTEGER, " + NL +
309          " IN IN_MAPPING_TYPE INTEGER, " + NL +
310          " IN IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL +
311          " OUT OUT_KEY INTEGER, " + NL +
312          " OUT OUT_TIMESTAMP TIMESTAMP " + NL +
313          ")" + NL +
314          "AS " + NL +
315          "TRY " + NL +
316          " SET OUT_TIMESTAMP = now(); " + NL +
317          " INSERT INTO ");
318       buffer.append(strUserName);
319       buffer.append(".");
320       buffer.append(getSchemaPrefix());
321       buffer.append(strMapTableName);
322       buffer.append("(ID1, ID2, MAPPING_TYPE, CUSTOM_DATA, " + NL +
323          " CREATION_DATE, MODIFICATION_DATE) " + NL +
324          " VALUES (:IN_ID1, :IN_ID2, :IN_MAPPING_TYPE, :IN_CUSTOM_DATA, " + NL +
325          " :OUT_TIMESTAMP, :OUT_TIMESTAMP); " + NL +
326          "SET OUT_KEY = ");
327       buffer.append(strUserName);
328       buffer.append(".");
329       buffer.append(getSchemaPrefix());
330       buffer.append(strMapTableName);
331       buffer.append(".CURRVAL;" + NL +
332          "CATCH " + NL +
333          "IF $RC <> 0 THEN STOP ($RC, $ERRMSG);");
334       
335       return buffer.toString();
336    }
337    
338    
339    /**
340     * Construt SQL command for stored procedure that process inserting data.
341     *
342     * @param strMapTableName - table name
343     * @param strUserName - user name
344     * @return String - constructed command of insert stored procedure
345     */

346    public static String JavaDoc constructUpdateStoredProc(
347       String JavaDoc strUserName,
348       String JavaDoc strMapTableName
349    )
350    {
351       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
352       
353       buffer.append("CREATE DBPROCEDURE UPDATE_");
354       buffer.append(getSchemaPrefix());
355       buffer.append(strMapTableName);
356       buffer.append(NL + "(" + NL +
357          " IN IN_ID INTEGER, " + NL +
358          " IN IN_ID1 INTEGER, " + NL +
359          " IN IN_ID2 INTEGER, " + NL +
360          " IN IN_MAPPING_TYPE INTEGER, " + NL +
361          " IN IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL +
362          " IN IN_MODIFICATION_DATE TIMESTAMP, " + NL +
363          " OUT OUT_TIMESTAMP TIMESTAMP " + NL +
364          ")" + NL +
365          "AS " + NL +
366          "TRY " + NL +
367          " SET OUT_TIMESTAMP = now(); " + NL +
368       " UPDATE ");
369       buffer.append(strUserName);
370       buffer.append(".");
371       buffer.append(getSchemaPrefix());
372       buffer.append(strMapTableName);
373       buffer.append(" SET ID1 = :IN_ID1, ID2 = :IN_ID2, " + NL +
374          " MAPPING_TYPE = :IN_MAPPING_TYPE, CUSTOM_DATA = :IN_CUSTOM_DATA, " + NL +
375          " MODIFICATION_DATE = :OUT_TIMESTAMP WHERE ID = :IN_ID " + NL +
376          " AND MODIFICATION_DATE = :IN_MODIFICATION_DATE; " + NL +
377          "CATCH " + NL +
378          "IF $RC <> 0 THEN STOP ($RC, $ERRMSG);");
379       
380       return buffer.toString();
381    }
382 }
383
Popular Tags