KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > db2 > 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 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.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 JavaDoc 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 JavaDoc strMapTableName,
168       Class JavaDoc schema1,
169       String JavaDoc strTableName1,
170       String JavaDoc strColumnName1,
171       Class JavaDoc schema2,
172       String JavaDoc strTableName2,
173       String JavaDoc 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 JavaDoc cntDBConnection,
187       String JavaDoc strUserName
188    ) throws SQLException JavaDoc, OSSException
189    {
190       Statement JavaDoc 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 JavaDoc 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 JavaDoc dbConnection,
250       MappedData data
251    ) throws OSSException
252    {
253       CallableStatement JavaDoc insertStatement = null;
254       
255       try
256       {
257          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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 JavaDoc 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 JavaDoc dbConnection,
291       MappedData data
292    ) throws OSSException
293    {
294       CallableStatement JavaDoc updateStatement = null;
295       
296       try
297       {
298          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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 JavaDoc 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 JavaDoc constructInsertStoredProc(
339       String JavaDoc strUserName,
340       String JavaDoc strMapTableName
341    )
342    {
343       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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 JavaDoc constructUpdateStoredProc(
389       String JavaDoc strUserName,
390       String JavaDoc strMapTableName
391    )
392    {
393       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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
Popular Tags