KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > cayenne > dba > sybase > SybasePkGenerator


1 /*****************************************************************
2  * Licensed to the Apache Software Foundation (ASF) under one
3  * or more contributor license agreements. See the NOTICE file
4  * distributed with this work for additional information
5  * regarding copyright ownership. The ASF licenses this file
6  * to you under the Apache License, Version 2.0 (the
7  * "License"); you may not use this file except in compliance
8  * with the License. You may obtain a copy of the License at
9  *
10  * http://www.apache.org/licenses/LICENSE-2.0
11  *
12  * Unless required by applicable law or agreed to in writing,
13  * software distributed under the License is distributed on an
14  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15  * KIND, either express or implied. See the License for the
16  * specific language governing permissions and limitations
17  * under the License.
18  ****************************************************************/
19
20
21 package org.apache.cayenne.dba.sybase;
22
23 import java.sql.CallableStatement JavaDoc;
24 import java.sql.Connection JavaDoc;
25 import java.sql.ResultSet JavaDoc;
26 import java.util.ArrayList JavaDoc;
27 import java.util.List JavaDoc;
28
29 import org.apache.cayenne.CayenneRuntimeException;
30 import org.apache.cayenne.access.DataNode;
31 import org.apache.cayenne.access.Transaction;
32 import org.apache.cayenne.dba.JdbcPkGenerator;
33 import org.apache.cayenne.map.DbEntity;
34
35 /**
36  * Primary key generator implementation for Sybase. Uses a lookup table named
37  * "AUTO_PK_SUPPORT" and a stored procedure "auto_pk_for_table"
38  * to search and increment primary keys for tables.
39  *
40  * @author Andrus Adamchik
41  */
42 public class SybasePkGenerator extends JdbcPkGenerator {
43
44     /** Generates database objects to provide
45      * automatic primary key support. Method will execute the following
46      * SQL statements:
47      *
48      * <p>1. Executed only if a corresponding table does not exist in the
49      * database.</p>
50      *
51      * <pre>
52      * CREATE TABLE AUTO_PK_SUPPORT (
53      * TABLE_NAME VARCHAR(32) NOT NULL,
54      * NEXT_ID INTEGER NOT NULL
55      * )
56      * </pre>
57      *
58      * <p>2. Executed under any circumstances. </p>
59      *
60      * <pre>
61      * if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')
62      * BEGIN
63      * DROP PROCEDURE auto_pk_for_table
64      * END
65      * </pre>
66      *
67      * <p>3. Executed under any circumstances. </p>
68      * CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS
69      * BEGIN
70      * BEGIN TRANSACTION
71      * UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID + @pkbatchsize
72      * WHERE TABLE_NAME = @tname
73      *
74      * SELECT NEXT_ID from AUTO_PK_SUPPORT where NEXT_ID = @tname
75      * COMMIT
76      * END
77      * </pre>
78      *
79      * @param node node that provides access to a DataSource.
80      */
81     public void createAutoPk(DataNode node, List JavaDoc dbEntities) throws Exception JavaDoc {
82         super.createAutoPk(node, dbEntities);
83         super.runUpdate(node, safePkProcDrop());
84         super.runUpdate(node, unsafePkProcCreate());
85     }
86     
87     
88     public List JavaDoc createAutoPkStatements(List JavaDoc dbEntities) {
89         List JavaDoc list = super.createAutoPkStatements(dbEntities);
90         
91         // add stored procedure drop code
92 list.add(safePkProcDrop());
93         
94         // add stored procedure creation code
95 list.add(unsafePkProcCreate());
96         
97         return list;
98     }
99
100
101     /**
102      * Drops database objects related to automatic primary
103      * key support. Method will execute the following SQL
104      * statements:
105      *
106      * <pre>
107      * if exists (SELECT * FROM sysobjects WHERE name = 'AUTO_PK_SUPPORT')
108      * BEGIN
109      * DROP TABLE AUTO_PK_SUPPORT
110      * END
111      *
112      *
113      * if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')
114      * BEGIN
115      * DROP PROCEDURE auto_pk_for_table
116      * END
117      * </pre>
118      *
119      * @param node node that provides access to a DataSource.
120      */
121     public void dropAutoPk(DataNode node, List JavaDoc dbEntities) throws Exception JavaDoc {
122         super.runUpdate(node, safePkProcDrop());
123         super.runUpdate(node, safePkTableDrop());
124     }
125     
126     public List JavaDoc dropAutoPkStatements(List JavaDoc dbEntities) {
127         List JavaDoc list = new ArrayList JavaDoc();
128         list.add(safePkProcDrop());
129         list.add(safePkTableDrop());
130         return list;
131     }
132
133     protected int pkFromDatabase(DataNode node, DbEntity ent) throws Exception JavaDoc {
134         // handle CAY-588 - get connection that is separate from the connection in the
135 // current transaction.
136
137         // TODO (andrus, 7/6/2006) Note that this will still work in a pool with a single
138 // connection, as PK generator is invoked early in the transaction, before the
139 // connection is grabbed for commit... So maybe promote this to other adapters in
140 // 3.0?
141
142         Transaction transaction = Transaction.getThreadTransaction();
143         Transaction.bindThreadTransaction(null);
144
145         try {
146
147             Connection JavaDoc connection = node.getDataSource().getConnection();
148             try {
149                 CallableStatement JavaDoc statement = connection
150                         .prepareCall("{call auto_pk_for_table(?, ?)}");
151                 try {
152                     statement.setString(1, ent.getName());
153                     statement.setInt(2, super.getPkCacheSize());
154
155                     // can't use "executeQuery"
156 // per http://jtds.sourceforge.net/faq.html#expectingResultSet
157 statement.execute();
158                     if (statement.getMoreResults()) {
159                         ResultSet JavaDoc rs = statement.getResultSet();
160
161                         try {
162                             if (rs.next()) {
163                                 return rs.getInt(1);
164                             }
165                             else {
166                                 throw new CayenneRuntimeException(
167                                         "Error generating pk for DbEntity "
168                                                 + ent.getName());
169                             }
170                         }
171                         finally {
172                             rs.close();
173                         }
174                     }
175                     else {
176                         throw new CayenneRuntimeException(
177                                 "Error generating pk for DbEntity "
178                                         + ent.getName()
179                                         + ", no result set from stored procedure.");
180                     }
181                 }
182                 finally {
183                     statement.close();
184                 }
185             }
186             finally {
187                 connection.close();
188             }
189         }
190         finally {
191             Transaction.bindThreadTransaction(transaction);
192         }
193     }
194
195
196     private String JavaDoc safePkTableDrop() {
197         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
198         buf
199             .append("if exists (SELECT * FROM sysobjects WHERE name = 'AUTO_PK_SUPPORT')")
200             .append(" BEGIN ")
201             .append(" DROP TABLE AUTO_PK_SUPPORT")
202             .append(" END");
203
204         return buf.toString();
205     }
206
207     private String JavaDoc unsafePkProcCreate() {
208         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
209         buf
210             .append(" CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS")
211             .append(" BEGIN")
212             .append(" BEGIN TRANSACTION")
213             .append(" UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID + @pkbatchsize")
214             .append(" WHERE TABLE_NAME = @tname")
215             .append(" SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = @tname")
216             .append(" COMMIT")
217             .append(" END");
218         return buf.toString();
219     }
220
221     private String JavaDoc safePkProcDrop() {
222         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
223         buf
224             .append("if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')")
225             .append(" BEGIN")
226             .append(" DROP PROCEDURE auto_pk_for_table")
227             .append(" END");
228         return buf.toString();
229     }
230
231 }
232
Popular Tags