1 package org.apache.ojb.broker.util.sequence;
2
3 /* Copyright 2003-2005 The Apache Software Foundation
4 *
5 * Licensed under the Apache License, Version 2.0 (the "License");
6 * you may not use this file except in compliance with the License.
7 * You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17
18 import org.apache.commons.lang.SystemUtils;
19 import org.apache.ojb.broker.PersistenceBroker;
20 import org.apache.ojb.broker.platforms.PlatformException;
21 import org.apache.ojb.broker.accesslayer.LookupException;
22 import org.apache.ojb.broker.metadata.ClassDescriptor;
23 import org.apache.ojb.broker.metadata.FieldDescriptor;
24 import org.apache.ojb.broker.query.Query;
25 import org.apache.ojb.broker.util.logging.Logger;
26 import org.apache.ojb.broker.util.logging.LoggerFactory;
27
28 import java.sql.CallableStatement;
29 import java.sql.SQLException;
30 import java.sql.Statement;
31 import java.sql.Connection;
32
33 /**
34 * This solution will give those seeking an oracle-style
35 * sequence generator a final answer (Identity columns really suck).
36 * <br/>
37 * The <code>SequenceManagerStoredProcedureImpl</code> implementation enabled database
38 * sequence key generation for all databases (e.g. MSSQL, MySQL, DB2, ...)
39 * with a <b>JDBC 2.0</b> compliant driver.
40 * <br/>
41 * First add a new table <code>OJB_NEXTVAL_SEQ</code> to
42 * your database.
43 * <pre>
44 * CREATE TABLE OJB_NEXTVAL_SEQ
45 * (
46 * SEQ_NAME VARCHAR(150) NOT NULL,
47 * MAX_KEY BIGINT,
48 * CONSTRAINT SYS_PK_OJB_NEXTVAL_SEQ PRIMARY KEY(SEQ_NAME)
49 * )
50 * </pre>
51 * You will also need the stored procedure OJB_NEXTVAL
52 * will will take care of giving you a guaranteed unique
53 * sequence number, in multi server environments.
54 * <br/>
55 * <pre>
56 * CREATE PROCEDURE ojb_nextval_proc @SEQ_NAME varchar(100)
57 * AS
58 * declare @MAX_KEY BIGINT
59 * -- return an error if sequence does not exist
60 * -- so we will know if someone truncates the table
61 * set @MAX_KEY = 0
62 *
63 * UPDATE OJB_NEXTVAL_SEQ
64 * SET @MAX_KEY = MAX_KEY = MAX_KEY + 1
65 * WHERE SEQ_NAME = @SEQ_NAME
66 *
67 * if @MAX_KEY = 0
68 * select 1/0
69 * else
70 * select @MAX_KEY
71 *
72 * RETURN @MAX_KEY
73 * </pre>
74 * <br/>
75 * It is possible to define a <code>sequence-name</code>
76 * field-descriptor attribute in the repository file. If
77 * such an attribute was not found, the implementation build
78 * an extent aware sequence name by its own.
79 * <br/>
80 * Keep in mind when define a sequence name, that you are responsible
81 * to be aware of extents, that is: if you ask for an uid for an
82 * interface with several
83 * implementor classes, or a baseclass with several subclasses the returned
84 * uid have to be unique accross all tables representing objects of the
85 * extent in question. Thus you have to use the same <code>sequence-name</code>
86 * for all extents.
87 *
88 * <p>
89 * Implementation configuration properties:
90 * </p>
91 *
92 * <table cellspacing="2" cellpadding="2" border="3" frame="box">
93 * <tr>
94 * <td><strong>Property Key</strong></td>
95 * <td><strong>Property Values</strong></td>
96 * </tr>
97 * <tr>
98 * <td>autoNaming</td>
99 * <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 }