KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > patterns > mappeddata > persist > db > mysql > MySQLMappingDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: MySQLMappingDatabaseSchema.java,v 1.8 2007/01/07 06:14:56 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.mysql;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.PreparedStatement 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.mysql.MySQLDataUtils;
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 MySQL database.
41  *
42  * @version $Id: MySQLMappingDatabaseSchema.java,v 1.8 2007/01/07 06:14:56 bastafidli Exp $
43  * @author Julian Legeny
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.6 2006/08/14 22:42:40 jlegeny
46  */
47 public class MySQLMappingDatabaseSchema extends MappingDatabaseSchema
48 {
49    /*
50       Use autogenerated numbers for IDs using IDENTITY column.
51       Identity automatically defines primary key
52       Name all constraints to easily identify them later.
53       For all unique constraint we need to define unique indexes instead of
54       unique constrant otherwise we won't be able to indentify the violation of
55       this constraint by name.
56       DESCRIPTION column must be type of TEXT because VARCHAR type
57       can be max. 255 characters length.
58       
59       create table BF_MAP_TABLE
60       (
61          ID INTEGER NOT NULL AUTO_INCREMENT,
62          ID1 INTEGER NOT NULL,
63          ID2 INTEGER NOT NULL,
64          MAPPING_TYPE INTEGER NOT NULL,
65          CUSTOM_DATA TEXT 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_FK1 FOREIGN KEY (ID1)
70             REFERENCES BF_TABLE1 (COLUMN_ID1) ON DELETE CASCADE,
71          CONSTRAINT BF_MAP_TABLE_FK2 FOREIGN KEY (ID2)
72             REFERENCES BF_TABLE2 (COLUMN_ID2) ON DELETE CASCADE,
73          CONSTRAINT BF_MAP_TABLE_PK PRIMARY KEY (ID),
74          INDEX IND_MAP_COL_ID2 (ID2),
75          INDEX IND_MAP_COL_ID1 (ID1),
76       ) TYPE=INNODB
77
78    */
79
80    // Cached values ////////////////////////////////////////////////////////////
81
82    /**
83     * Logger for this class
84     */
85    private static Logger JavaDoc s_logger = Log.getInstance(MySQLMappingDatabaseSchema.class);
86
87    // Constructors /////////////////////////////////////////////////////////////
88
89    /**
90     * Full constructor.
91     *
92     * @param strMapTableName - table name for mapping table
93     * @param schema1 - schema name the table 1 was defined in
94     * @param strTableName1 - name of the table 1
95     * @param strColumnName1 - name of the column 1
96     * @param schema2 - schema name the table 2 was defined in
97     * @param strTableName2 - name of the table 2
98     * @param strColumnName2 - name of the column 2
99     * @throws OSSException - an error has occured
100     */
101    public MySQLMappingDatabaseSchema(
102       String JavaDoc strMapTableName,
103       Class JavaDoc schema1,
104       String JavaDoc strTableName1,
105       String JavaDoc strColumnName1,
106       Class JavaDoc schema2,
107       String JavaDoc strTableName2,
108       String JavaDoc strColumnName2
109    ) throws OSSException
110    {
111       super(strMapTableName, schema1, strTableName1, strColumnName1,
112             schema2, strTableName2, strColumnName2);
113    }
114
115    // Public methods ///////////////////////////////////////////////////////////
116
117    /**
118     * {@inheritDoc}
119     */
120    public void create(
121       Connection JavaDoc cntDBConnection,
122       String JavaDoc strUserName
123    ) throws SQLException JavaDoc, OSSException
124    {
125       Statement JavaDoc stmQuery = null;
126       try
127       {
128          StringBuffer JavaDoc bufferTable = new StringBuffer JavaDoc();
129          stmQuery = cntDBConnection.createStatement();
130
131
132          bufferTable.append(constructSQL("ID INTEGER NOT NULL AUTO_INCREMENT,",
133                   "INTEGER", "TEXT", "TIMESTAMP",
134                   m_strConstraintBody, true, true));
135          // remove last char (right bracket) append indexes and
136 // mysql innodb table type instead
137 bufferTable.deleteCharAt(bufferTable.length() - 1);
138          // construct index for column 1
139 bufferTable.append(",INDEX IND_");
140          bufferTable.append(m_strConstraintBody);
141          bufferTable.append("_");
142          bufferTable.append(MAPPING_SCHEMA_COLUMN1);
143          bufferTable.append(" (");
144          bufferTable.append(MAPPING_SCHEMA_COLUMN1);
145          bufferTable.append("),");
146          bufferTable.append(NL);
147          // construct index for column 2
148 bufferTable.append("INDEX IND_");
149          bufferTable.append(m_strConstraintBody);
150          bufferTable.append("_");
151          bufferTable.append(MAPPING_SCHEMA_COLUMN2);
152          bufferTable.append(" (");
153          bufferTable.append(MAPPING_SCHEMA_COLUMN2);
154          bufferTable.append(")");
155          bufferTable.append(") TYPE=INNODB");
156          
157          if (stmQuery.execute(bufferTable.toString()))
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          if (stmQuery.execute("grant all on " + getSchemaPrefix() +
166                               strMapTableName + " to " + strUserName))
167          {
168             // Close any results
169             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
170          }
171          s_logger.log(Level.FINEST, "Access for table " + getSchemaPrefix() +
172                                     strMapTableName + " set for user " +
173                                     strUserName);
174 */
175       }
176       catch (SQLException JavaDoc sqleExc)
177       {
178          s_logger.log(Level.WARNING, "Failed to create schema " + MAPPING_SCHEMA_NAME, sqleExc);
179          throw sqleExc;
180       }
181       finally
182       {
183          DatabaseUtils.closeStatement(stmQuery);
184       }
185    }
186
187    /**
188     * {@inheritDoc}
189     */
190    public MappedData insertMappedData(
191       Connection JavaDoc dbConnection,
192       MappedData data
193    ) throws OSSException
194    {
195       PreparedStatement JavaDoc insertStatement = null;
196       
197       try
198       {
199          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
200          int iIndex = 1;
201       
202          buffer.append("insert into ");
203          buffer.append(getSchemaPrefix());
204          buffer.append(m_strMapTableName);
205          buffer.append(" (");
206          getColumns(false, MappedData.ALL_MAPPEDDATA_COLUMNS, null, null, buffer);
207          buffer.append(") values (null, ?, ?, ?, ?, now(), now())");
208
209          insertStatement = dbConnection.prepareStatement(buffer.toString());
210          insertStatement.setInt(iIndex++, data.getMappedId1());
211          insertStatement.setInt(iIndex++, data.getMappedId2());
212          insertStatement.setInt(iIndex++, data.getMappingType());
213          insertStatement.setString(iIndex++, data.getCustomData());
214
215          MySQLDataUtils.insertAndFetchGeneratedValues(dbConnection,
216             insertStatement, isInDomain(), getSchemaPrefix() + m_strMapTableName, data);
217       }
218       catch (SQLException JavaDoc eExc)
219       {
220          handleInsertMappedDataException(eExc);
221       }
222       finally
223       {
224          DatabaseUtils.closeStatement(insertStatement);
225       }
226
227       return data;
228    }
229
230    /**
231     * {@inheritDoc}
232     */
233    public MappedData updateMappedData(
234       Connection JavaDoc dbConnection,
235       MappedData data
236    ) throws OSSException
237    {
238       PreparedStatement JavaDoc updateStatement = null;
239       
240       try
241       {
242          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
243          int iIndex = 1;
244          
245          buffer.append("update ");
246          buffer.append(getSchemaPrefix());
247          buffer.append(m_strMapTableName);
248          buffer.append(" set ID1 = ?, ID2 = ?, MAPPING_TYPE = ?, " +
249                        "CUSTOM_DATA = ?, MODIFICATION_DATE = now() where ID = ? " +
250                        "and MODIFICATION_DATE = ?");
251          
252          updateStatement = dbConnection.prepareStatement(buffer.toString());
253          updateStatement.setInt(iIndex++, data.getMappedId1());
254          updateStatement.setInt(iIndex++, data.getMappedId2());
255          updateStatement.setInt(iIndex++, data.getMappingType());
256          updateStatement.setString(iIndex++, data.getCustomData());
257          updateStatement.setInt(iIndex++, data.getId());
258          updateStatement.setTimestamp(iIndex++, data.getModificationTimestamp());
259          
260          MySQLDataUtils.updatedAndFetchGeneratedValues("Mapped Data", dbConnection,
261             updateStatement, isInDomain(), getSchemaPrefix() + m_strMapTableName, data);
262       }
263       catch (SQLException JavaDoc eExc)
264       {
265          handleUpdateMappedDataException(eExc, dbConnection, data);
266       }
267       finally
268       {
269          DatabaseUtils.closeStatement(updateStatement);
270       }
271
272       return data;
273    }
274 }
275
Popular Tags