001 package org.apache.ojb.broker.util.sequence;
002
003 /* Copyright 2003-2005 The Apache Software Foundation
004 *
005 * Licensed under the Apache License, Version 2.0 (the "License");
006 * you may not use this file except in compliance with the License.
007 * You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017
018 import org.apache.commons.lang.SystemUtils;
019 import org.apache.ojb.broker.PersistenceBroker;
020 import org.apache.ojb.broker.platforms.PlatformException;
021 import org.apache.ojb.broker.accesslayer.LookupException;
022 import org.apache.ojb.broker.metadata.ClassDescriptor;
023 import org.apache.ojb.broker.metadata.FieldDescriptor;
024 import org.apache.ojb.broker.query.Query;
025 import org.apache.ojb.broker.util.logging.Logger;
026 import org.apache.ojb.broker.util.logging.LoggerFactory;
027
028 import java.sql.CallableStatement;
029 import java.sql.SQLException;
030 import java.sql.Statement;
031 import java.sql.Connection;
032
033 /**
034 * This solution will give those seeking an oracle-style
035 * sequence generator a final answer (Identity columns really suck).
036 * <br/>
037 * The <code>SequenceManagerStoredProcedureImpl</code> implementation enabled database
038 * sequence key generation for all databases (e.g. MSSQL, MySQL, DB2, ...)
039 * with a <b>JDBC 2.0</b> compliant driver.
040 * <br/>
041 * First add a new table <code>OJB_NEXTVAL_SEQ</code> to
042 * your database.
043 * <pre>
044 * CREATE TABLE OJB_NEXTVAL_SEQ
045 * (
046 * SEQ_NAME VARCHAR(150) NOT NULL,
047 * MAX_KEY BIGINT,
048 * CONSTRAINT SYS_PK_OJB_NEXTVAL_SEQ PRIMARY KEY(SEQ_NAME)
049 * )
050 * </pre>
051 * You will also need the stored procedure OJB_NEXTVAL
052 * will will take care of giving you a guaranteed unique
053 * sequence number, in multi server environments.
054 * <br/>
055 * <pre>
056 * CREATE PROCEDURE ojb_nextval_proc @SEQ_NAME varchar(100)
057 * AS
058 * declare @MAX_KEY BIGINT
059 * -- return an error if sequence does not exist
060 * -- so we will know if someone truncates the table
061 * set @MAX_KEY = 0
062 *
063 * UPDATE OJB_NEXTVAL_SEQ
064 * SET @MAX_KEY = MAX_KEY = MAX_KEY + 1
065 * WHERE SEQ_NAME = @SEQ_NAME
066 *
067 * if @MAX_KEY = 0
068 * select 1/0
069 * else
070 * select @MAX_KEY
071 *
072 * RETURN @MAX_KEY
073 * </pre>
074 * <br/>
075 * It is possible to define a <code>sequence-name</code>
076 * field-descriptor attribute in the repository file. If
077 * such an attribute was not found, the implementation build
078 * an extent aware sequence name by its own.
079 * <br/>
080 * Keep in mind when define a sequence name, that you are responsible
081 * to be aware of extents, that is: if you ask for an uid for an
082 * interface with several
083 * implementor classes, or a baseclass with several subclasses the returned
084 * uid have to be unique accross all tables representing objects of the
085 * extent in question. Thus you have to use the same <code>sequence-name</code>
086 * for all extents.
087 *
088 * <p>
089 * Implementation configuration properties:
090 * </p>
091 *
092 * <table cellspacing="2" cellpadding="2" border="3" frame="box">
093 * <tr>
094 * <td><strong>Property Key</strong></td>
095 * <td><strong>Property Values</strong></td>
096 * </tr>
097 * <tr>
098 * <td>autoNaming</td>
099 * <td>
100 * Default was 'true'. If set 'true' OJB try to build a
101 * sequence name automatic if none found in field-descriptor
102 * and set this generated name as <code>sequence-name</code>
103 * in field-descriptor. If set 'false' OJB throws an exception
104 * if none sequence name was found in field-descriptor.
105 * </td>
106 * </tr>
107 * </table>
108 *
109 * <p>
110 * <b>Limitations:</b>
111 * <ul>
112 * <li>do not use when other application use the native key generation ditto</li>
113 * </ul>
114 * </p>
115 * <br/>
116 * <br/>
117 *
118 * @author Ryan Vanderwerf
119 * @author Edson Carlos Ericksson Richter
120 * @author Rajeev Kaul
121 * @author Thomas Mahler
122 * @author Armin Waibel
123 * @version $Id: SequenceManagerStoredProcedureImpl.java,v 1.1 2007-08-24 22:17:29 ewestfal Exp $
124 */
125 public class SequenceManagerStoredProcedureImpl extends AbstractSequenceManager
126 {
127 private Logger log = LoggerFactory.getLogger(SequenceManagerStoredProcedureImpl.class);
128 protected static final String PROCEDURE_NAME = "ojb_nextval_proc";
129 protected static final String SEQ_NAME_STRING = "SEQ_NAME";
130 protected static final String SEQ_ID_STRING = "MAX_KEY";
131 protected static final String SEQ_TABLE_NAME = "OJB_NEXTVAL_SEQ";
132
133 /**
134 * Constructor
135 * @param broker
136 */
137 public SequenceManagerStoredProcedureImpl(PersistenceBroker broker)
138 {
139 super(broker);
140 }
141
142 /**
143 * Insert syntax for our special table
144 * @param sequenceName
145 * @param maxKey
146 * @return sequence insert statement
147 */
148 protected String sp_createSequenceQuery(String sequenceName, long maxKey)
149 {
150 return "insert into " + SEQ_TABLE_NAME + " ("
151 + SEQ_NAME_STRING + "," + SEQ_ID_STRING +
152 ") values ('" + sequenceName + "'," + maxKey + ")";
153 }
154
155 /**
156 * Gets the actual key - will create a new row with the max key of table if it
157 * does not exist.
158 * @param field
159 * @return
160 * @throws SequenceManagerException
161 */
162 protected long getUniqueLong(FieldDescriptor field) throws SequenceManagerException
163 {
164 boolean needsCommit = false;
165 long result = 0;
166 /*
167 arminw:
168 use the associated broker instance, check if broker was in tx or
169 we need to commit used connection.
170 */
171 PersistenceBroker targetBroker = getBrokerForClass();
172 if(!targetBroker.isInTransaction())
173 {
174 targetBroker.beginTransaction();
175 needsCommit = true;
176 }
177 try
178 {
179 // lookup sequence name
180 String sequenceName = calculateSequenceName(field);
181 try
182 {
183 result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);
184 /*
185 if 0 was returned we assume that the stored procedure
186 did not work properly.
187 */
188 if (result == 0)
189 {
190 throw new SequenceManagerException("No incremented value retrieved");
191 }
192 }
193 catch (Exception e)
194 {
195 // maybe the sequence was not created
196 log.info("Could not grab next key, message was " + e.getMessage() +
197 " - try to write a new sequence entry to database");
198 try
199 {
200 // on create, make sure to get the max key for the table first
201 long maxKey = SequenceManagerHelper.getMaxForExtent(targetBroker, field);
202 createSequence(targetBroker, field, sequenceName, maxKey);
203 }
204 catch (Exception e1)
205 {
206 String eol = SystemUtils.LINE_SEPARATOR;
207 throw new SequenceManagerException(eol + "Could not grab next id, failed with " + eol +
208 e.getMessage() + eol + "Creation of new sequence failed with " +
209 eol + e1.getMessage() + eol, e1);
210 }
211 try
212 {
213 result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);
214 }
215 catch (Exception e1)
216 {
217 throw new SequenceManagerException("Could not grab next id although a sequence seems to exist", e);
218 }
219 }
220 }
221 finally
222 {
223 if(targetBroker != null && needsCommit)
224 {
225 targetBroker.commitTransaction();
226 }
227 }
228 return result;
229 }
230
231 /**
232 * Calls the stored procedure stored procedure throws an
233 * error if it doesn't exist.
234 * @param broker
235 * @param cld
236 * @param sequenceName
237 * @return
238 * @throws LookupException
239 * @throws SQLException
240 */
241 protected long buildNextSequence(PersistenceBroker broker, ClassDescriptor cld, String sequenceName)
242 throws LookupException, SQLException, PlatformException
243 {
244 CallableStatement cs = null;
245 try
246 {
247 Connection con = broker.serviceConnectionManager().getConnection();
248 cs = getPlatform().prepareNextValProcedureStatement(con, PROCEDURE_NAME, sequenceName);
249 cs.executeUpdate();
250 return cs.getLong(1);
251 }
252 finally
253 {
254 try
255 {
256 if (cs != null)
257 cs.close();
258 }
259 catch (SQLException ignore)
260 {
261 // ignore it
262 }
263 }
264 }
265
266 /**
267 * Creates new row in table
268 * @param broker
269 * @param field
270 * @param sequenceName
271 * @param maxKey
272 * @throws Exception
273 */
274 protected void createSequence(PersistenceBroker broker, FieldDescriptor field,
275 String sequenceName, long maxKey) throws Exception
276 {
277 Statement stmt = null;
278 try
279 {
280 stmt = broker.serviceStatementManager().getGenericStatement(field.getClassDescriptor(), Query.NOT_SCROLLABLE);
281 stmt.execute(sp_createSequenceQuery(sequenceName, maxKey));
282 }
283 catch (Exception e)
284 {
285 log.error(e);
286 throw new SequenceManagerException("Could not create new row in "+SEQ_TABLE_NAME+" table - TABLENAME=" +
287 sequenceName + " field=" + field.getColumnName(), e);
288 }
289 finally
290 {
291 try
292 {
293 if (stmt != null) stmt.close();
294 }
295 catch (SQLException sqle)
296 {
297 if(log.isDebugEnabled())
298 log.debug("Threw SQLException while in createSequence and closing stmt", sqle);
299 // ignore it
300 }
301 }
302 }
303 }