KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > sybase > SybaseMappingDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: SybaseMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:11 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.sybase;
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.sybase.SybaseDataUtils;
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 /**
40  * Database specific operations related to persistence of mapping tables
41  * for Sybase ASE database.
42  *
43  * @version $Id: SybaseMappingDatabaseSchema.java,v 1.8 2007/01/07 06:15:11 bastafidli Exp $
44  * @author Julian Legeny
45  * @code.reviewer Miro Halas
46  * @code.reviewed 1.6 2006/08/14 22:42:37 jlegeny
47  */

48 public class SybaseMappingDatabaseSchema extends MappingDatabaseSchema
49 {
50    /*
51       Use autogenerated numbers for IDs using sequence
52       Name all constraints to easily identify them later.
53
54       create table BF_MAP_TABLE
55       (
56          ID NUMERIC(10, 0) IDENTITY NOT NULL,
57          ID1 NUMERIC(10, 0) NOT NULL,
58          ID2 NUMERIC(10, 0) NOT NULL,
59          MAPPING_TYPE INTEGER NOT NULL,
60          CUSTOM_DATA VARCHAR(4000) DEFAULT NULL,
61          CREATION_DATE DATETIME NOT NULL,
62          MODIFICATION_DATE DATETIME NOT NULL,
63          CONSTRAINT BF_MAP_TABLE_UQ UNIQUE (ID1, ID2, MAPPING_TYPE),
64          CONSTRAINT BF_MAP_TABLE_FK1 FOREIGN KEY (ID1)
65             REFERENCES BF_TABLE1 (COLUMN_ID1),
66          CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2)
67             REFERENCES BF_TABLE2 (COLUMN_ID2),
68          CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID)
69       )
70
71       CREATE PROCEDURE INSERT_BF_MAP_TABLE
72          @IN_ID1 INTEGER,
73          @IN_ID2 INTEGER,
74          @IN_MAPPING_TYPE INTEGER,
75          @IN_CUSTOM_DATA VARCHAR(4000),
76          @OUT_KEY INTEGER OUTPUT,
77          @OUT_TIMESTAMP DATETIME OUTPUT
78       AS
79          SELECT @OUT_TIMESTAMP = GETDATE()
80          INSERT INTO " + strUserName + ".BF_MAP_TABLE (ID1, ID2, MAPPING_TYPE,
81             CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE)
82             VALUES (@IN_ID1, @IN_ID2, @IN_MAPPING_TYPE, @IN_CUSTOM_DATA,
83             @OUT_TIMESTAMP, @OUT_TIMESTAMP)
84          SET @OUT_KEY = @@IDENTITY
85       RETURN
86
87       CREATE PROCEDURE UPDATE_BF_MAP_TABLE
88          @IN_ID INTEGER,
89          @IN_ID1 INTEGER,
90          @IN_ID2 INTEGER,
91          @IN_MAPPING_TYPE INTEGER,
92          @IN_CUSTOM_DATA VARCHAR(4000),
93          @IN_MODIFICATION_DATE DATETIME,
94          @OUT_TIMESTAMP DATETIME OUTPUT
95       AS
96          SET @OUT_TIMESTAMP = GETDATE()
97          UPDATE " + strUserName + ".BF_MAP_TABLE SET ID1 = @IN_ID1, ID2 = @IN_ID2,
98             MAPPING_TYPE = @IN_MAPPING_TYPE, CUSTOM_DATA = @IN_CUSTOM_DATA,
99             MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_ID
100             AND MODIFICATION_DATE = @IN_MODIFICATION_DATE
101       RETURN
102    */

103
104    // Cached values ////////////////////////////////////////////////////////////
105

106    /**
107     * Logger for this class
108     */

109    private static Logger JavaDoc s_logger = Log.getInstance(SybaseMappingDatabaseSchema.class);
110
111    // Constructors /////////////////////////////////////////////////////////////
112

113    /**
114     * Full constructor.
115     *
116     * @param strMapTableName - table name for mapping table
117     * @param schema1 - schema name the table 1 was defined in
118     * @param strTableName1 - name of the table 1
119     * @param strColumnName1 - name of the column 1
120     * @param schema2 - schema name the table 2 was defined in
121     * @param strTableName2 - name of the table 2
122     * @param strColumnName2 - name of the column 2
123     * @throws OSSException - an error has occured
124     */

125    public SybaseMappingDatabaseSchema(
126       String JavaDoc strMapTableName,
127       Class JavaDoc schema1,
128       String JavaDoc strTableName1,
129       String JavaDoc strColumnName1,
130       Class JavaDoc schema2,
131       String JavaDoc strTableName2,
132       String JavaDoc strColumnName2
133    ) throws OSSException
134    {
135       super(strMapTableName, schema1, strTableName1, strColumnName1,
136             schema2, strTableName2, strColumnName2);
137    }
138
139    // Public methods ///////////////////////////////////////////////////////////
140

141    /**
142     * {@inheritDoc}
143     */

144    public void create(
145       Connection JavaDoc cntDBConnection,
146       String JavaDoc strUserName
147    ) throws SQLException JavaDoc, OSSException
148    {
149       Statement JavaDoc stmQuery = null;
150       try
151       {
152          stmQuery = cntDBConnection.createStatement();
153
154          if (stmQuery.execute(constructSQL(
155                                  "ID NUMERIC(10, 0) IDENTITY NOT NULL,", "NUMERIC(10, 0)",
156                                  "VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + ")" + NL,
157                                  "DATETIME", m_strConstraintBody, false, true)))
158          {
159             // Close any results
160
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
161          }
162          s_logger.log(Level.FINEST, "Table " + getSchemaPrefix() +
163                                     m_strMapTableName + " created.");
164
165          ///////////////////////////////////////////////////////////////////////
166

167          if (stmQuery.execute(constructInsertStoredProc(strUserName, m_strMapTableName)))
168          {
169             // Close any results
170
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
171          }
172          s_logger.log(Level.FINEST, "Procedure INSERT_" + m_strMapTableName + " created.");
173          
174          ///////////////////////////////////////////////////////////////////////
175

176          if (stmQuery.execute(constructUpdateStoredProc(strUserName, m_strMapTableName)))
177          {
178             // Close any results
179
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
180          }
181          s_logger.log(Level.FINEST, "Procedure UPDATE_" + m_strMapTableName + " created.");
182       }
183       catch (SQLException JavaDoc sqleExc)
184       {
185          s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc);
186          throw sqleExc;
187       }
188       finally
189       {
190          DatabaseUtils.closeStatement(stmQuery);
191       }
192    }
193
194    /**
195     * {@inheritDoc}
196     */

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

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

280    /**
281     * Construt SQL command for stored procedure that process inserting data.
282     *
283     * @param strMapTableName - table name
284     * @param strUserName - user name
285     * @return String - constructed command of insert stored procedure
286     */

287    public String JavaDoc constructInsertStoredProc(
288       String JavaDoc strUserName,
289       String JavaDoc strMapTableName
290    )
291    {
292       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
293
294       buffer.append("CREATE PROCEDURE INSERT_");
295       buffer.append(getSchemaPrefix());
296       buffer.append(strMapTableName);
297       buffer.append(NL +
298          " @IN_ID1 INTEGER, " + NL +
299          " @IN_ID2 INTEGER, " + NL +
300          " @IN_MAPPING_TYPE INTEGER, " + NL +
301          " @IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL +
302          " @OUT_KEY INTEGER OUTPUT, " + NL +
303          " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
304          "AS " + NL +
305          " SELECT @OUT_TIMESTAMP = GETDATE() " + NL +
306          " INSERT INTO ");
307       buffer.append(strUserName);
308       buffer.append(".");
309       buffer.append(getSchemaPrefix());
310       buffer.append(strMapTableName);
311       buffer.append(" (ID1, ID2, " + NL +
312          " MAPPING_TYPE, CUSTOM_DATA, CREATION_DATE, MODIFICATION_DATE) " + NL +
313          " VALUES (@IN_ID1, @IN_ID2, @IN_MAPPING_TYPE, @IN_CUSTOM_DATA, " + NL +
314          " @OUT_TIMESTAMP, @OUT_TIMESTAMP) " + NL +
315          " SET @OUT_KEY = @@IDENTITY " + NL +
316          "RETURN");
317       
318       return buffer.toString();
319    }
320    
321    /**
322     * Construt SQL command for stored procedure that process inserting data.
323     *
324     * @param strMapTableName - table name
325     * @param strUserName - user name
326     * @return String - constructed command of insert stored procedure
327     */

328    public static String JavaDoc constructUpdateStoredProc(
329       String JavaDoc strUserName,
330       String JavaDoc strMapTableName
331    )
332    {
333       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
334
335       buffer.append("CREATE PROCEDURE UPDATE_");
336       buffer.append(getSchemaPrefix());
337       buffer.append(strMapTableName);
338       buffer.append(NL +
339          " @IN_ID INTEGER, " + NL +
340          " @IN_ID1 INTEGER, " + NL +
341          " @IN_ID2 INTEGER, " + NL +
342          " @IN_MAPPING_TYPE INTEGER, " + NL +
343          " @IN_CUSTOM_DATA VARCHAR(" + MAPPING_CUSTOM_DATA_MAXLENGTH + "), " + NL +
344          " @IN_MODIFICATION_DATE DATETIME, " + NL +
345          " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
346          "AS " + NL +
347          " SET @OUT_TIMESTAMP = GETDATE() " + NL +
348          " UPDATE ");
349       buffer.append(strUserName);
350       buffer.append(".");
351       buffer.append(getSchemaPrefix());
352       buffer.append(strMapTableName);
353       buffer.append(" SET ID1 = @IN_ID1, ID2 = @IN_ID2, " + NL +
354          " MAPPING_TYPE = @IN_MAPPING_TYPE, CUSTOM_DATA = @IN_CUSTOM_DATA, " + NL +
355          " MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_ID " + NL +
356          " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE " + NL +
357       "RETURN");
358       
359       return buffer.toString();
360    }
361 }
362
Popular Tags