001 package biz.wedoit4u.databeans;
002
003 import java.util.ArrayList;
004 import java.util.Collection;
005 import java.sql.Connection;
006 import java.sql.PreparedStatement;
007 import java.sql.ResultSet;
008 import java.sql.SQLException;
009
010 import javax.naming.NamingException;
011
012 import biz.wedoit4u.Logger;
013
014 /**
015 * A helper class that provides convenient methods to interact with
016 * the <code>customer_types</code> table. This class is meant to
017 * be used from the {@link DatabaseHelper} class, or other classes
018 * in this package only.
019 *
020 * <p>Copyright 2003, Rakesh Vidyadharan and wedoit4u.biz</p>
021 *
022 * @author Rakesh Vidyadharan 21<sup><small>st</small></sup> October 2003
023 *
024 * @version $Id: CustomerTypeHelper.java,v 1.4 2004/05/26 11:42:32 rakesh Exp $
025 */
026 final class CustomerTypeHelper extends Object
027 {
028 /**
029 * The <code>sequence</code> set in the database for the <code>
030 * customer_type_id primary key</code> column.
031 */
032 static final String CUSTOMER_TYPE_ID_SEQUENCE = "sequence_customer_type_id";
033
034 /**
035 * The statement that is executed to fetch all the records from the
036 * <code>customer_types</code> table.
037 */
038 static final String ALL_CUSTOMER_TYPES = "select * from customer_types";
039
040 /**
041 * The statement that is executed to fetch all the records from the
042 * <code>customer_types</code> table for the specified <code>
043 * partner_id</code>.
044 */
045 static final String SELECT_BY_PARTNER = "select * from customer_types where partner_id = ?";
046
047 /**
048 * The statement that is executed to insert a new record into the
049 * <code>customer_types</code> table.
050 */
051 static final String INSERT_CUSTOMER_TYPES = "insert into customer_types cols( customer_type_id, type, description, partner_id ) values ( ?, ?, ?, ? )";
052
053 /**
054 * The statement that is executed to fetch a specific record from the
055 * <code>customer_types</code> table.
056 */
057 static final String SELECT_CUSTOMER_TYPES = "select * from customer_types where customer_type_id = ?";
058
059 /**
060 * The update statement to be executed to update a record in the
061 * <code>customer_types</code> table.
062 */
063 static final String UPDATE_CUSTOMER_TYPES = "update customer_types set type = ?, description = ? where customer_type_id = ?";
064
065 /**
066 * The delete statement to be executed to delete a record in the
067 * <code>customer_types</code> table.
068 */
069 static final String DELETE_CUSTOMER_TYPES = "delete from customer_types where customer_type_id = ?";
070
071 /**
072 * Return a <code>Collection</code> of <code>Integer</code> values
073 * that represent all the primary key values in the
074 * <code>customer_types</code> table.
075 *
076 * @return Collection - The collection of primary key values.
077 * @throws SQLException - If errors are encountered while fetching
078 * the records from the database.
079 * @throws NamingException - If errors are encountered while getting
080 * a connection from the connection pool.
081 */
082 static final Collection findAll() throws SQLException, NamingException
083 {
084 Collection list = new ArrayList();
085 Connection connection = null;
086 PreparedStatement statement = null;
087 ResultSet resultSet = null;
088
089 try
090 {
091 connection = DatabaseHelper.getConnection();
092 statement = connection.prepareStatement( CustomerTypeHelper.ALL_CUSTOMER_TYPES );
093 resultSet = statement.executeQuery();
094
095 while ( resultSet.next() )
096 {
097 list.add( new Integer( resultSet.getInt( "customer_type_id" ) ) );
098 }
099 }
100 finally
101 {
102 try
103 {
104 if ( resultSet != null ) resultSet.close();
105 }
106 catch ( SQLException sex )
107 {
108 Logger.error( "Error closing resultSet in CustomerTypeHelper.findAll.", sex );
109 }
110
111 try
112 {
113 if ( statement != null ) statement.close();
114 }
115 catch ( SQLException sex )
116 {
117 Logger.error( "Error closing statement in CustomerTypeHelper.findAll.", sex );
118 }
119
120 try
121 {
122 if ( connection != null ) connection.close();
123 }
124 catch ( SQLException sex )
125 {
126 Logger.error( "Error closing connection in CustomerTypeHelper.findAll.", sex );
127 }
128 }
129
130 return list;
131 }
132
133 /**
134 * Return a <code>Collection</code> of <code>Integer</code> values
135 * that represent all the primary key values in the
136 * <code>customer_types</code> table that belong to the specified
137 * <code>partner</code>.
138 *
139 * @return Collection - The collection of primary key values.
140 * @throws SQLException - If errors are encountered while fetching
141 * the records from the database.
142 * @throws NamingException - If errors are encountered while getting
143 * a connection from the connection pool.
144 */
145 static final Collection findByPartner( int partnerId )
146 throws SQLException, NamingException
147 {
148 Collection list = new ArrayList();
149 Connection connection = null;
150 PreparedStatement statement = null;
151 ResultSet resultSet = null;
152
153 try
154 {
155 connection = DatabaseHelper.getConnection();
156 statement = connection.prepareStatement( CustomerTypeHelper.SELECT_BY_PARTNER );
157 statement.setInt( 1, partnerId );
158 resultSet = statement.executeQuery();
159
160 while ( resultSet.next() )
161 {
162 list.add( new Integer( resultSet.getInt( "customer_type_id" ) ) );
163 }
164 }
165 finally
166 {
167 try
168 {
169 if ( resultSet != null ) resultSet.close();
170 }
171 catch ( SQLException sex )
172 {
173 Logger.error( "Error closing resultSet in CustomerTypeHelper.findByPartner.", sex );
174 }
175
176 try
177 {
178 if ( statement != null ) statement.close();
179 }
180 catch ( SQLException sex )
181 {
182 Logger.error( "Error closing statement in CustomerTypeHelper.findByPartner.", sex );
183 }
184
185 try
186 {
187 if ( connection != null ) connection.close();
188 }
189 catch ( SQLException sex )
190 {
191 Logger.error( "Error closing connection in CustomerTypeHelper.findByPartner.", sex );
192 }
193 }
194
195 return list;
196 }
197
198 /**
199 * Select the specified record from the <code>customer_types</code>
200 * table. The values selected from the database are used to set the
201 * values of the bean fields.
202 *
203 * @param ctb - The java bean instance that
204 * represents the record. The primary key value to use to look
205 * up the record is obtained from this bean.
206 * @throws SQLException - If errors are encountered while fetching
207 * the record from the database.
208 * @throws NamingException - If errors are encountered while getting
209 * a connection from the connection pool.
210 */
211 static final void select( CustomerTypeBean ctb )
212 throws SQLException, NamingException
213 {
214 Connection connection = null;
215 PreparedStatement statement = null;
216 ResultSet resultSet = null;
217
218 try
219 {
220 connection = DatabaseHelper.getConnection();
221 statement = connection.prepareStatement( CustomerTypeHelper.SELECT_CUSTOMER_TYPES );
222 statement.setInt( 1, ctb.getCustomerTypeId() );
223 resultSet = statement.executeQuery();
224 resultSet.next();
225 ctb.type = resultSet.getString( "type" );
226 ctb.description = resultSet.getString( "description" );
227 ctb.setPartnerId( resultSet.getInt( "partner_id" ) );
228 }
229 finally
230 {
231 try
232 {
233 if ( resultSet != null ) resultSet.close();
234 }
235 catch ( SQLException sex )
236 {
237 Logger.error( "Error closing resultSet in CustomerTypeHelper.select for customer_type_id " + ctb.getCustomerTypeId() + ".", sex );
238 }
239
240 try
241 {
242 if ( statement != null ) statement.close();
243 }
244 catch ( SQLException sex )
245 {
246 Logger.error( "Error closing statement in CustomerTypeHelper.select for customer_type_id " + ctb.getCustomerTypeId() + ".", sex );
247 }
248
249 try
250 {
251 if ( connection != null ) connection.close();
252 }
253 catch ( SQLException sex )
254 {
255 Logger.error( "Error closing connection in CustomerTypeHelper.select for customer_type_id " + ctb.getCustomerTypeId() + ".", sex );
256 }
257 }
258 }
259
260 /**
261 * Insert a new record into the <code>customer_types</code> table
262 * with the values specified.
263 *
264 * @param customerTypeId - The primary key values to use. If this
265 * is <code>0</code>, then the next value from the {@link
266 * #CUSTOMER_TYPE_ID_SEQUENCE} is used.
267 * @param type - The <code>type</code> value.
268 * @param description - The <code>description</code> value.
269 * @param partnerId - The <code>partner_id</code> value.
270 * @throws SQLException - If database errors are encountered while
271 * writing to the database.
272 * @throws NamingException - If errors are encountered while getting
273 * a connection from the connection pool.
274 */
275 static final int insert( int customerTypeId, String type,
276 String description, int partnerId )
277 throws SQLException, NamingException
278 {
279 Connection connection = null;
280 PreparedStatement statement = null;
281
282 try
283 {
284 connection = DatabaseHelper.getConnection();
285
286 if ( customerTypeId == 0 )
287 {
288 customerTypeId = DatabaseHelper.getNextSequenceValue( CustomerTypeHelper.CUSTOMER_TYPE_ID_SEQUENCE );
289 }
290
291 statement = connection.prepareStatement( CustomerTypeHelper.INSERT_CUSTOMER_TYPES );
292 statement.setInt( 1, customerTypeId );
293 statement.setString( 2, type );
294 statement.setString( 3, description );
295 statement.setInt( 4, partnerId );
296 statement.executeUpdate();
297 }
298 finally
299 {
300 try
301 {
302 if ( statement != null ) statement.close();
303 }
304 catch ( SQLException sex )
305 {
306 Logger.error( "Error closing statement in CustomerTypeHelper.insert for customer_type_id " + customerTypeId + ".", sex );
307 }
308
309 try
310 {
311 if ( connection != null ) connection.close();
312 }
313 catch ( SQLException sex )
314 {
315 Logger.error( "Error closing connection in CustomerTypeHelper.insert for customer_type_id " + customerTypeId + ".", sex );
316 }
317 }
318
319 return customerTypeId;
320 }
321
322 /**
323 * Update the values in the <code>customer_types</code> table with
324 * the specified values.
325 *
326 * @param ctb - The java bean that represents a
327 * record in the database.
328 * @throws SQLException - If database errors are encountered while
329 * writing to the database.
330 * @throws NamingException - If errors are encountered while getting
331 * a connection from the connection pool.
332 */
333 static final void update( CustomerTypeBean ctb )
334 throws SQLException, NamingException
335 {
336 Connection connection = null;
337 PreparedStatement statement = null;
338
339 try
340 {
341 connection = DatabaseHelper.getConnection();
342 statement = connection.prepareStatement( CustomerTypeHelper.UPDATE_CUSTOMER_TYPES );
343 statement.setString( 1, ctb.type );
344 statement.setString( 2, ctb.description );
345 statement.setInt( 3, ctb.getCustomerTypeId() );
346 statement.executeUpdate();
347 }
348 finally
349 {
350 try
351 {
352 if ( statement != null ) statement.close();
353 }
354 catch ( SQLException sex )
355 {
356 Logger.error( "Error closing statement in CustomerTypeHelper.delete for customer_type_id " + ctb.getCustomerTypeId() + ".", sex );
357 }
358
359 try
360 {
361 if ( connection != null ) connection.close();
362 }
363 catch ( SQLException sex )
364 {
365 Logger.error( "Error closing connection in CustomerTypeHelper.delete for customer_type_id " + ctb.getCustomerTypeId() + ".", sex );
366 }
367 }
368 }
369
370 /**
371 * Delete the record from the database identified by the primary key
372 * value specified.
373 *
374 * @param customerTypeId - The <code>customer_type_id</code>
375 * primary key value.
376 * @throws SQLException - If database errors are encountered while
377 * writing to the database.
378 * @throws NamingException - If errors are encountered while getting
379 * a connection from the connection pool.
380 */
381 static final void delete( int customerTypeId )
382 throws SQLException, NamingException
383 {
384 Connection connection = null;
385 PreparedStatement statement = null;
386
387 try
388 {
389 connection = DatabaseHelper.getConnection();
390 statement = connection.prepareStatement( CustomerTypeHelper.DELETE_CUSTOMER_TYPES );
391 statement.setInt( 1, customerTypeId );
392 statement.executeUpdate();
393 }
394 finally
395 {
396 try
397 {
398 if ( statement != null ) statement.close();
399 }
400 catch ( SQLException sex )
401 {
402 Logger.error( "Error closing statement in CustomerTypeHelper.delete for customer_type_id " + customerTypeId + ".", sex );
403 }
404
405 try
406 {
407 if ( connection != null ) connection.close();
408 }
409 catch ( SQLException sex )
410 {
411 Logger.error( "Error closing connection in CustomerTypeHelper.delete for customer_type_id " + customerTypeId + ".", sex );
412 }
413 }
414 }
415
416 /**
417 * Default constructor. Cannot be instantiated.
418 */
419 private CustomerTypeHelper() {}
420 }