KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > ojb > broker > util > sequence > SequenceManagerStoredProcedureImpl


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 JavaDoc;
29 import java.sql.SQLException JavaDoc;
30 import java.sql.Statement JavaDoc;
31 import java.sql.Connection JavaDoc;
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.11.2.2 2005/12/21 22:28:41 tomdz Exp $
124  */
125 public class SequenceManagerStoredProcedureImpl extends AbstractSequenceManager
126 {
127     private Logger log = LoggerFactory.getLogger(SequenceManagerStoredProcedureImpl.class);
128     protected static final String JavaDoc PROCEDURE_NAME = "ojb_nextval_proc";
129     protected static final String JavaDoc SEQ_NAME_STRING = "SEQ_NAME";
130     protected static final String JavaDoc SEQ_ID_STRING = "MAX_KEY";
131     protected static final String JavaDoc 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 JavaDoc sp_createSequenceQuery(String JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc e1)
205                 {
206                     String JavaDoc 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 JavaDoc 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 JavaDoc sequenceName)
242             throws LookupException, SQLException JavaDoc, PlatformException
243     {
244         CallableStatement JavaDoc cs = null;
245         try
246         {
247             Connection JavaDoc 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 JavaDoc 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 JavaDoc sequenceName, long maxKey) throws Exception JavaDoc
276     {
277         Statement JavaDoc 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 JavaDoc 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 JavaDoc 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 }
304
Popular Tags