DB2MappingDatabaseSchema


1   /*
2    * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: DB2MappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:34 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.db2;
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.db2.DB2DataUtils;
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 IBM DB2 database.
41   *
42   * @version $Id: DB2MappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:34 bastafidli Exp $
43   * @author Julian Legeny
44   * @code.reviewer Miro Halas
45   * @code.reviewed 1.6 2006/08/15 22:14:06 jlegeny
46   */
47  public class DB2MappingDatabaseSchema extends MappingDatabaseSchema
48  {
49     /*
50        Use autogenerated numbers for IDs using sequence
51        Name all constraints to easily identify them later.
52        For stored procedures which provides UPDATE must be defined
53        also output parameter NUMBER OF AFFECTED ROWS. Because IBM DB2
54        driver does not support returning number of processed rows using 
55        [ RowCounter = insertStatement.executeUpdate() ]. 
56  
57        CREATE SEQUENCE MAP_TABLE_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
58        
59        create table BF_MAP_TABLE
60        (
61           ID INTEGER NOT NULL,
62           ID1 INTEGER NOT NULL,      
63           ID2 INTEGER NOT NULL,
64           MAPPING_TYPE INTEGER NOT NULL,
65           CUSTOM_DATA VARCHAR(3500) DEFAULT NULL,
66           CREATION_DATE TIMESTAMP NOT NULL,
67           MODIFICATION_DATE TIMESTAMP NOT NULL,
68           CONSTRAINT BF_MAP_TABLE_UQ UNIQUE (ID1, ID2, MAPPING_TYPE),
69           CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID),
70           CONSTRAINT BF_MAP_TABLE_FK1 FOREIGN KEY (ID1) 
71              REFERENCES BF_TABLE1 (COLUMN_ID1),
72           CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2) 
73              REFERENCES BF_TABLE2 (COLUMN_ID2)
74        )
75  
76        CREATE PROCEDURE INSERT_BF_MAP_TABLE
77        (
78           IN IN_ID1 INTEGER,
79           IN IN_ID2 INTEGER,
80           IN IN_MAPPING_TYPE INTEGER,
81           IN IN_CUSTOM_DATA VARCHAR(3500),
82           IN OUT_KEY INTEGER,
83           OUT OUT_TIMESTAMP TIMESTAMP
84        ) LANGUAGE SQL SPECIFIC INSERT_BF_MAP_TABLE
85        BEGIN
86           DECLARE new_out_key INTEGER DEFAULT -1;
87           DECLARE new_out_timestamp TIMESTAMP;
88           SET new_out_key = NEXT VALUE FOR BF_MAP_TABLE_SEQ;
89           SET new_out_timestamp = CURRENT TIMESTAMP;
90           SET OUT_KEY = new_out_key;
91           SET OUT_TIMESTAMP = new_out_timestamp;
92           INSERT INTO BF_MAP_TABLE (ID, ID1, ID2, MAPPING_TYPE, CUSTOM_DATA,  
93                  CREATION_DATE, MODIFICATION_DATE)
94              VALUES (OUT_KEY, IN_ID1, IN_ID2, IN_MAPPING_TYPE, IN_CUSTOM_DATA, 
95                      OUT_TIMESTAMP, OUT_TIMESTAMP);
96        END
97  
98        CREATE PROCEDURE UPDATE_BF_MAP_TABLE
99        (
100          IN IN_ID INTEGER,
101          IN IN_ID1 INTEGER,
102          IN IN_ID2 INTEGER,
103          IN IN_MAPPING_TYPE INTEGER,
104          IN IN_CUSTOM_DATA VARCHAR(3500),
105          IN IN_MODIFICATION_DATE TIMESTAMP,
106          OUT OUT_TIMESTAMP TIMESTAMP,
107          OUT OUT_ROW_COUNT INTEGER
108       ) LANGUAGE SQL SPECIFIC UPDATE_BF_MAP_TABLE
109       BEGIN
110          DECLARE new_out_timestamp TIMESTAMP;
111          DECLARE new_out_row_count INTEGER;
112          SET new_out_timestamp = CURRENT TIMESTAMP;
113          SET OUT_TIMESTAMP = new_out_timestamp;
114          UPDATE  + strUserName + BF_MAP_TABLE SET ID1 = IN_ID1, ID2 = IN_ID2  
115                 MAPPING_TYPE = IN_MAPPING_TYPE, CUSTOM_DATA = IN_CUSTOM_DATA, 
116                 MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ID 
117                 AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
118          GET DIAGNOSTICS new_out_row_count = ROW_COUNT;
119          SET OUT_ROW_COUNT = new_out_row_count;
120       END
121    */   
122 
123    // Constants ////////////////////////////////////////////////////////////////
124 
125    /**
126     * Maximal length of custom data.
127     * There is limitation for IBM DB2 for length of the VARCHAR column type.
128     * If we specify value = 4000 there is following error occured:
129     * ErrorCode:42727 - No default primary tablespace exists for the new table.
130     * Therefore tablespace has to be changed or if we want to use default 
131     * tablespace value we specify custom data maxlength to value 3500
132     * 
133     */
134    public static final int MAPPING_CUSTOM_DATA_MAXLENGTH_DB2 = 3500;
135 
136    // Cached values ////////////////////////////////////////////////////////////
137 
138    /**
139     * Logger for this class
140     */
141    private static Logger   s_logger = Log.getInstance(DB2MappingDatabaseSchema.class);
142 
143    // Constructors /////////////////////////////////////////////////////////////
144 
145    /**
146     * Static initializer
147     */
148    static
149    {
150       // Setup maximal length of individual fields for entities
151       MappedData.setCustomDataMaxLength(MAPPING_CUSTOM_DATA_MAXLENGTH_DB2);
152    }
153 
154    /**
155     * Full constructor.
156     * 
157     * @param strMapTableName - table name for mapping table
158     * @param schema1 - schema name the table 1 was defined in
159     * @param strTableName1 - name of the table 1
160     * @param strColumnName1 - name of the column 1
161     * @param schema2 - schema name the table 2 was defined in
162     * @param strTableName2 - name of the table 2
163     * @param strColumnName2 - name of the column 2
164     * @throws OSSException - an error has occured
165     */
166    public DB2MappingDatabaseSchema(
167       String   strMapTableName, 
168       Class    schema1, 
169       String   strTableName1, 
170       String   strColumnName1, 
171       Class    schema2, 
172       String   strTableName2, 
173       String   strColumnName2
174    ) throws OSSException
175    {
176       super(strMapTableName, schema1, strTableName1, strColumnName1, 
177             schema2, strTableName2, strColumnName2);
178    }
179 
180    // Public methods ///////////////////////////////////////////////////////////
181 
182    /**
183     * {@inheritDoc}
184     */
185    public void create(
186       Connection   cntDBConnection,
187       String       strUserName
188    ) throws SQLException  , OSSException
189    {
190       Statement   stmQuery = null;
191       try
192       {
193          stmQuery = cntDBConnection.createStatement();
194 
195          if (stmQuery.execute("CREATE SEQUENCE " + m_strMapTableName + 
196                               "_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE"))
197          {
198             // Close any results
199             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
200          }
201          s_logger.log(Level.FINEST, "Sequence " + m_strMapTableName + "_SEQ created.");
202 
203          ///////////////////////////////////////////////////////////////////////
204 
205          if (stmQuery.execute(constructSQL(
206                                 "ID INTEGER NOT NULL,", "INTEGER",
207                                 "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH_DB2 + ") DEFAULT" + NL,
208                                 "TIMESTAMP", m_strConstraintBody, true, true)))
209          {
210             // Close any results
211             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
212          }
213          s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() + 
214                                     m_strMapTableName + " created.");
215 
216          ///////////////////////////////////////////////////////////////////////
217 
218          if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName)))
219          {
220             // Close any results
221             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
222          }
223          s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created.");
224          
225          ///////////////////////////////////////////////////////////////////////  
226 
227          if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName)))
228          {
229             // Close any results
230             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
231          }
232          s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created.");
233       }
234       catch (SQLException   sqleExc)
235       {
236          s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc);
237          throw sqleExc;
238       }
239       finally
240       {
241          DatabaseUtils.closeStatement(stmQuery);
242       }
243    }   
244 
245    /**
246     * {@inheritDoc}
247     */
248    public MappedData insertMappedData(
249       Connection   dbConnection, 
250       MappedData data
251    ) throws OSSException
252    {
253       CallableStatement   insertStatement = null;
254       
255       try
256       {
257          StringBuffer   buffer = new StringBuffer  (); 
258          int iIndex = 1;
259       
260          buffer.append("call INSERT_");
261          buffer.append(getSchemaPrefix());
262          buffer.append(m_strMapTableName);
263          buffer.append(" (?, ?, ?, ?, ?, ?)");
264          
265          insertStatement = dbConnection.prepareCall(buffer.toString());
266 
267          insertStatement.setInt(iIndex++, data.getMappedId1());
268          insertStatement.setInt(iIndex++, data.getMappedId2());
269          insertStatement.setInt(iIndex++, data.getMappingType());
270          insertStatement.setString(iIndex++, data.getCustomData());
271 
272          DB2DataUtils.insertAndFetchGeneratedValues(insertStatement, iIndex, data);
273       }
274       catch (SQLException   eExc)
275       {
276          handleInsertMappedDataException(eExc);
277       }
278       finally
279       {
280          DatabaseUtils.closeStatement(insertStatement);
281       }
282 
283       return data;   
284    }
285 
286    /**
287     * {@inheritDoc}
288     */
289    public MappedData updateMappedData(
290       Connection   dbConnection, 
291       MappedData data
292    ) throws OSSException
293    {
294       CallableStatement   updateStatement = null;
295       
296       try
297       {
298          StringBuffer   buffer = new StringBuffer  (); 
299          int iIndex = 1;
300       
301          buffer.append("call UPDATE_");
302          buffer.append(getSchemaPrefix());
303          buffer.append(m_strMapTableName);
304          buffer.append(" (?, ?, ?, ?, ?, ?, ?, ?)");
305          
306          updateStatement = dbConnection.prepareCall(buffer.toString());
307 
308          updateStatement.setInt(iIndex++, data.getId());
309          updateStatement.setInt(iIndex++, data.getMappedId1());
310          updateStatement.setInt(iIndex++, data.getMappedId2());
311          updateStatement.setInt(iIndex++, data.getMappingType());
312          updateStatement.setString(iIndex++, data.getCustomData());
313          updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp());
314 
315          DB2DataUtils.updateAndFetchGeneratedValues(updateStatement, iIndex, data);
316       }   
317       catch (SQLException   eExc)
318       {
319          handleUpdateMappedDataException(eExc, dbConnection, data);
320       }
321       finally
322       {
323          DatabaseUtils.closeStatement(updateStatement);
324       }
325 
326       return data;
327    }
328 
329    // Helper methods ///////////////////////////////////////////////////////////
330 
331    /**
332     * Construt SQL command for stored procedure that process inserting data.
333     * 
334     * @param strUserName - user name
335     * @param strMapTableName - table name
336     * @return String - constructed command of insert stored procedure
337     */
338    public static String   constructInsertStoredProc(
339       String   strUserName,
340       String   strMapTableName
341    )
342    {
343       StringBuffer   buffer = new StringBuffer  ();
344       
345       buffer.append("CREATE PROCEDURE INSERT_");
346       buffer.append(getSchemaPrefix());
347       buffer.append(strMapTableName);
348       buffer.append(NL + "(" + NL +
349          "   IN IN_ID1 INTEGER, " + NL +
350          "   IN IN_ID2 INTEGER, " + NL +
351          "   IN IN_MAPPING_TYPE INTEGER, " + NL +
352          "   IN IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH_DB2 + "), " + NL +
353          "   OUT OUT_KEY INTEGER, " + NL +
354          "   OUT OUT_TIMESTAMP TIMESTAMP " + NL +
355          ") LANGUAGE SQL SPECIFIC INSERT_");
356       buffer.append(getSchemaPrefix());
357       buffer.append(strMapTableName);
358       buffer.append(NL + " BEGIN " + NL +
359          "   DECLARE new_out_key INTEGER DEFAULT -1; " + NL +
360          "   DECLARE new_out_timestamp TIMESTAMP; " + NL +
361          "   SET new_out_key = NEXT VALUE FOR ");
362       buffer.append(strMapTableName);
363       buffer.append("_SEQ;" + NL +
364          "   SET new_out_timestamp = CURRENT TIMESTAMP; " + NL +
365          "   SET OUT_KEY = new_out_key; " + NL +
366          "   SET OUT_TIMESTAMP = new_out_timestamp; " + NL +
367          "   INSERT INTO ");
368       buffer.append(strUserName);
369       buffer.append(".");
370       buffer.append(getSchemaPrefix());
371       buffer.append(strMapTableName);
372       buffer.append("(ID, ID1, ID2, " + NL +
373          "      MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + NL +
374          "   VALUES (OUT_KEY, IN_ID1, IN_ID2, IN_MAPPING_TYPE, IN_CUSTOM_DATA, " + NL +
375          "      OUT_TIMESTAMP, OUT_TIMESTAMP); " + NL +
376          "END");
377       
378       return buffer.toString();
379    }
380    
381    /**
382     * Construt SQL command for stored procedure that process inserting data.
383     * 
384     * @param strUserName - user name
385     * @param strMapTableName - table name
386     * @return String - constructed command of insert stored procedure
387     */
388    public static String   constructUpdateStoredProc(
389       String   strUserName,
390       String   strMapTableName
391    )
392    {
393       StringBuffer   buffer = new StringBuffer  ();
394       
395       buffer.append("CREATE PROCEDURE UPDATE_");
396       buffer.append(getSchemaPrefix());
397       buffer.append(strMapTableName);
398       buffer.append(NL + "(" + NL +
399          "   IN IN_ID INTEGER, " + NL +
400          "   IN IN_ID1 INTEGER, " + NL +
401          "   IN IN_ID2 INTEGER, " + NL +
402          "   IN IN_MAPPING_TYPE INTEGER, " + NL +
403          "   IN IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH_DB2 + "), " + NL +
404          "   IN IN_MODIFICATION_DATE TIMESTAMP, " + NL +
405          "   OUT OUT_TIMESTAMP TIMESTAMP, " + NL +
406          "   OUT OUT_ROW_COUNT INTEGER " + NL +
407          ") LANGUAGE SQL SPECIFIC UPDATE_"); 
408       buffer.append(getSchemaPrefix());
409       buffer.append(strMapTableName);
410       buffer.append(NL + " BEGIN " + NL +
411          "   DECLARE new_out_timestamp TIMESTAMP; " + NL +
412          "   DECLARE new_out_row_count INTEGER; " + NL +
413          "   SET new_out_timestamp = CURRENT TIMESTAMP; " + NL +
414          "   SET OUT_TIMESTAMP = new_out_timestamp; " + NL +
415          "   UPDATE ");
416       buffer.append(strUserName);
417       buffer.append(".");
418       buffer.append(getSchemaPrefix());
419       buffer.append(strMapTableName);
420       buffer.append(" SET ID1 = IN_ID1, ID2 = IN_ID2, " + NL +
421          "   MAPPING_TYPE = IN_MAPPING_TYPE, CUSTOM_DATA = IN_CUSTOM_DATA, " + NL +
422          "   MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ID " + NL +
423          "   AND MODIFICATION_DATE = IN_MODIFICATION_DATE; " + NL +
424          "   GET DIAGNOSTICS new_out_row_count = ROW_COUNT;" + NL +
425          "   SET OUT_ROW_COUNT = new_out_row_count;" + NL +
426          "END");
427       
428       return buffer.toString();
429    }
430 }
431
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags