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  ;
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.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   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   strMapTableName, 
136       Class    schema1, 
137       String   strTableName1, 
138       String   strColumnName1, 
139       Class    schema2, 
140       String   strTableName2, 
141       String   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   cntDBConnection,
155       String       strUserName
156    ) throws SQLException  , OSSException
157    {
158       Statement   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   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   dbConnection, 
208       MappedData data
209    ) throws OSSException
210    {
211       CallableStatement   insertStatement = null;
212       
213       try
214       {
215          StringBuffer   buffer = new StringBuffer  (); 
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   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   dbConnection, 
249       MappedData data
250    ) throws OSSException
251    {
252       CallableStatement   updateStatement = null;
253       
254       try
255       {
256          StringBuffer   buffer = new StringBuffer  (); 
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   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   constructInsertStoredProc(
297       String   strUserName,
298       String   strMapTableName
299    )
300    {
301       StringBuffer   buffer = new StringBuffer  ();
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   constructUpdateStoredProc(
347       String   strUserName,
348       String   strMapTableName
349    )
350    {
351       StringBuffer   buffer = new StringBuffer  ();
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
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags