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>partner_styles</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 2004, Rakesh Vidyadharan and wedoit4u.biz</p>
021 *
022 * @author Rakesh Vidyadharan 03<sup><small>rd</small></sup> March 2004
023 *
024 * @version $Id: PartnerStyleHelper.java,v 1.2 2004/05/26 11:42:32 rakesh Exp $
025 */
026 final class PartnerStyleHelper extends Object
027 {
028 /**
029 * The statement that is executed to fetch all the records from the
030 * <code>partner_styles</code> table.
031 */
032 static final String ALL_PARTNER_STYLES = "select style_class_id, partner_id from partner_styles";
033
034 /**
035 * The statement that is executed to fetch all the records from the
036 * <code>partner_styles</code> table for the specified <code>
037 * partner_id</code>.
038 */
039 static final String FIND_BY_CUSTOMER = "select style_class_id, partner_id from partner_styles where partner_id = ?";
040
041 /**
042 * The statement that is executed to insert a new record into the
043 * <code>partner_styles</code> table.
044 */
045 static final String INSERT_PARTNER_STYLES = "insert into partner_styles cols( style_class_id, partner_id, color, font_family, font_weight, font_size, background_color, text_align, vertical_align, width, height, border_style, border_width, padding_top, padding_left, padding_right, padding_bottom, text_decoration ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
046
047 /**
048 * The statement that is executed to fetch a specific record from the
049 * <code>partner_styles</code> table.
050 */
051 static final String SELECT_PARTNER_STYLES = "select * from partner_styles where style_class_id = ? and partner_id = ?";
052
053 /**
054 * The update statement to be executed to update a record in the
055 * <code>partner_styles</code> table.
056 */
057 static final String UPDATE_PARTNER_STYLES = "update partner_styles set color = ?, font_family = ?, font_weight = ?, font_size = ?, background_color = ?, text_align = ?, vertical_align = ?, width = ?, height = ?, border_style = ?, border_width = ?, padding_top = ?, padding_left = ?, padding_right = ?, padding_bottom = ?, text_decoration = ? where style_class_id = ? and partner_id = ?";
058
059 /**
060 * The delete statement to be executed to delete a record in the
061 * <code>partner_styles</code> table.
062 */
063 static final String DELETE_PARTNER_STYLES = "delete from partner_styles where style_class_id = ? and partner_id = ?";
064
065 /**
066 * Return a <code>Collection</code> of {@link PartnerStylesPK} objects
067 * that represent all the records in the <code>partner_styles</code>
068 * table.
069 *
070 * @return Collection - The collection of primary key values.
071 * @throws SQLException - If errors are encountered while fetching
072 * the records from the database.
073 * @throws NamingException - If errors are encountered while getting
074 * a connection from the connection pool.
075 */
076 static final Collection findAll() throws SQLException, NamingException
077 {
078 Collection list = new ArrayList();
079 Connection connection = null;
080 PreparedStatement statement = null;
081 ResultSet resultSet = null;
082
083 try
084 {
085 connection = DatabaseHelper.getConnection();
086 statement = connection.prepareStatement( PartnerStyleHelper.ALL_PARTNER_STYLES );
087 resultSet = statement.executeQuery();
088
089 while ( resultSet.next() )
090 {
091 list.add( new PartnerStylesPK( resultSet.getInt( "style_class_id" ), resultSet.getInt( "partner_id" ) ) );
092 }
093 }
094 finally
095 {
096 try
097 {
098 if ( resultSet != null ) resultSet.close();
099 }
100 catch ( SQLException sex )
101 {
102 Logger.error( "Error closing resultSet in PartnerStyleHelper.findAll.", sex );
103 }
104
105 try
106 {
107 if ( statement != null ) statement.close();
108 }
109 catch ( SQLException sex )
110 {
111 Logger.error( "Error closing statement in PartnerStyleHelper.findAll.", sex );
112 }
113
114 try
115 {
116 if ( connection != null ) connection.close();
117 }
118 catch ( SQLException sex )
119 {
120 Logger.error( "Error closing connection in PartnerStyleHelper.findAll.", sex );
121 }
122 }
123
124 return list;
125 }
126
127 /**
128 * Return a <code>Collection</code> of {@link PartnerStylesPK} objects
129 * that represent all the records in the <code>partner_styles</code>
130 * table that are associated with the specified
131 * <code>partner_id</code>.
132 *
133 * @param partnerId - The <code>partner_id</code> value for
134 * which the matching primary key values are to be fetched.
135 * @return Collection - The collection of primary key values.
136 * @throws SQLException - If errors are encountered while fetching
137 * the records from the database.
138 * @throws NamingException - If errors are encountered while getting
139 * a connection from the connection pool.
140 */
141 static final Collection findByPartner( int partnerId )
142 throws SQLException, NamingException
143 {
144 Collection list = new ArrayList();
145 Connection connection = null;
146 PreparedStatement statement = null;
147 ResultSet resultSet = null;
148
149 try
150 {
151 connection = DatabaseHelper.getConnection();
152 statement = connection.prepareStatement( PartnerStyleHelper.FIND_BY_CUSTOMER );
153 statement.setInt( 1, partnerId );
154 resultSet = statement.executeQuery();
155
156 while ( resultSet.next() )
157 {
158 list.add( new PartnerStylesPK( resultSet.getInt( "style_class_id" ), resultSet.getInt( "partner_id" ) ) );
159 }
160 }
161 finally
162 {
163 try
164 {
165 if ( resultSet != null ) resultSet.close();
166 }
167 catch ( SQLException sex )
168 {
169 Logger.error( "Error closing resultSet in PartnerStyleHelper.findByPartner.", sex );
170 }
171
172 try
173 {
174 if ( statement != null ) statement.close();
175 }
176 catch ( SQLException sex )
177 {
178 Logger.error( "Error closing statement in PartnerStyleHelper.findByPartner.", sex );
179 }
180
181 try
182 {
183 if ( connection != null ) connection.close();
184 }
185 catch ( SQLException sex )
186 {
187 Logger.error( "Error closing connection in PartnerStyleHelper.findByPartner.", sex );
188 }
189 }
190
191 return list;
192 }
193
194 /**
195 * Select the specified record from the <code>partner_styles</code>
196 * table. The values selected from the database are used to set the
197 * values of the bean fields.
198 *
199 * @param sab - The java bean instance that
200 * represents the record. The primary key value to use to look
201 * up the record is obtained from this bean.
202 * @throws SQLException - If errors are encountered while fetching
203 * the record from the database.
204 * @throws NamingException - If errors are encountered while getting
205 * a connection from the connection pool.
206 */
207 static final void select( PartnerStyleBean sab )
208 throws SQLException, NamingException
209 {
210 Connection connection = null;
211 PreparedStatement statement = null;
212 ResultSet resultSet = null;
213
214 try
215 {
216 connection = DatabaseHelper.getConnection();
217 statement = connection.prepareStatement( PartnerStyleHelper.SELECT_PARTNER_STYLES );
218 statement.setInt( 1, sab.getStyleClassId() );
219 statement.setInt( 2, sab.getPartnerId() );
220 resultSet = statement.executeQuery();
221 resultSet.next();
222 sab.color = resultSet.getString( "color" );
223 sab.fontFamily = resultSet.getString( "font_family" );
224 sab.fontWeight = resultSet.getString( "font_weight" );
225 sab.fontSize = resultSet.getString( "font_size" );
226 sab.backgroundColor = resultSet.getString( "background_color" );
227 sab.textAlign = resultSet.getString( "text_align" );
228 sab.verticalAlign = resultSet.getString( "vertical_align" );
229 sab.width = resultSet.getString( "width" );
230 sab.height = resultSet.getString( "height" );
231 sab.borderStyle = resultSet.getString( "border_style" );
232 sab.borderWidth = resultSet.getString( "border_width" );
233 sab.paddingTop = resultSet.getInt( "padding_top" );
234 sab.paddingLeft = resultSet.getInt( "padding_left" );
235 sab.paddingRight = resultSet.getInt( "padding_right" );
236 sab.paddingBottom = resultSet.getInt( "padding_bottom" );
237 sab.textDecoration = resultSet.getString( "text_decoration" );
238 }
239 finally
240 {
241 try
242 {
243 if ( resultSet != null ) resultSet.close();
244 }
245 catch ( SQLException sex )
246 {
247 Logger.error( "Error closing resultSet in PartnerStyleHelper.select for partner_id " + sab.getStyleClassId() + ".", sex );
248 }
249
250 try
251 {
252 if ( statement != null ) statement.close();
253 }
254 catch ( SQLException sex )
255 {
256 Logger.error( "Error closing statement in PartnerStyleHelper.select for partner_id " + sab.getStyleClassId() + ".", sex );
257 }
258
259 try
260 {
261 if ( connection != null ) connection.close();
262 }
263 catch ( SQLException sex )
264 {
265 Logger.error( "Error closing connection in PartnerStyleHelper.select for partner_id " + sab.getStyleClassId() + ".", sex );
266 }
267 }
268 }
269
270 /**
271 * Insert a new record into the <code>partner_styles</code> table
272 * with the values specified.
273 *
274 * @param styleClassId - The primary key value to use.
275 * @param partnerId - The primary key value to use.
276 * @param color - The <code>color</code> style attribute.
277 * @param fontFamily - The <code>font-family</code> style
278 * attribute.
279 * @param fontWeight - The <code>font-weight</code> style
280 * attribute.
281 * @param fontSize - The <code>font-size</code> style
282 * attribute.
283 * @param backgroundColor - The <code>background-color</code>
284 * style attribute.
285 * @param textAlign - The <code>text-align</code> style
286 * attribute.
287 * @param verticalAlign - The <code>vertical-align</code> style
288 * attribute.
289 * @param width - The <code>width</code> style attribute.
290 * @param height - The <code>height</code> style attribute.
291 * @param borderStyle - The <code>border-style</code> style
292 * attribute.
293 * @param borderWidth - The <code>border-width</code> style
294 * attribute.
295 * @param paddingTop - The <code>padding-top</code> style
296 * attribute.
297 * @param paddingLeft - The <code>padding-left</code> style
298 * attribute.
299 * @param paddingRight - The <code>padding-right</code> style
300 * attribute.
301 * @param paddingBottom - The <code>padding-bottom</code> style
302 * attribute.
303 * @param textDecoration - The <code>text-decoration</code>
304 * style attribute.
305 * @throws SQLException - If database errors are encountered while
306 * writing to the database.
307 * @throws NamingException - If errors are encountered while getting
308 * a connection from the connection pool.
309 */
310 static final void insert( int styleClassId, int partnerId,
311 String color, String fontFamily, String fontWeight,
312 String fontSize, String backgroundColor, String textAlign,
313 String verticalAlign, String width, String height,
314 String borderStyle, String borderWidth, int paddingTop,
315 int paddingLeft, int paddingRight, int paddingBottom,
316 String textDecoration ) throws SQLException, NamingException
317 {
318 Connection connection = null;
319 PreparedStatement statement = null;
320
321 try
322 {
323 connection = DatabaseHelper.getConnection();
324
325 statement = connection.prepareStatement( PartnerStyleHelper.INSERT_PARTNER_STYLES );
326 statement.setInt( 1, styleClassId );
327 statement.setInt( 2, partnerId );
328 statement.setString( 3, color );
329 statement.setString( 4, fontFamily );
330 statement.setString( 5, fontWeight );
331 statement.setString( 6, fontSize );
332 statement.setString( 7, backgroundColor );
333 statement.setString( 8, textAlign );
334 statement.setString( 9, verticalAlign );
335 statement.setString( 10, width );
336 statement.setString( 11, height );
337 statement.setString( 12, borderStyle );
338 statement.setString( 13, borderWidth );
339 statement.setInt( 14, paddingTop );
340 statement.setInt( 15, paddingLeft );
341 statement.setInt( 16, paddingRight );
342 statement.setInt( 17, paddingBottom );
343 statement.setString( 18, textDecoration );
344 statement.executeUpdate();
345 }
346 finally
347 {
348 try
349 {
350 if ( statement != null ) statement.close();
351 }
352 catch ( SQLException sex )
353 {
354 Logger.error( "Error closing statement in PartnerStyleHelper.insert for style_class_id " + styleClassId + " and partner_id " + partnerId + ".", sex );
355 }
356
357 try
358 {
359 if ( connection != null ) connection.close();
360 }
361 catch ( SQLException sex )
362 {
363 Logger.error( "Error closing connection in PartnerStyleHelper.insert for style_class_id " + styleClassId + " and partner_id " + partnerId + ".", sex );
364 }
365 }
366 }
367
368 /**
369 * Update the values in the <code>partner_styles</code> table with
370 * the specified values.
371 *
372 * @param sab - The java bean that represents a
373 * record in the database.
374 * @throws SQLException - If database errors are encountered while
375 * writing to the database.
376 * @throws NamingException - If errors are encountered while getting
377 * a connection from the connection pool.
378 */
379 static final void update( PartnerStyleBean sab )
380 throws SQLException, NamingException
381 {
382 Connection connection = null;
383 PreparedStatement statement = null;
384
385 try
386 {
387 connection = DatabaseHelper.getConnection();
388 statement = connection.prepareStatement( PartnerStyleHelper.UPDATE_PARTNER_STYLES );
389 statement.setString( 1, sab.color );
390 statement.setString( 2, sab.fontFamily );
391 statement.setString( 3, sab.fontWeight );
392 statement.setString( 4, sab.fontSize );
393 statement.setString( 5, sab.backgroundColor );
394 statement.setString( 6, sab.textAlign );
395 statement.setString( 7, sab.verticalAlign );
396 statement.setString( 8, sab.width );
397 statement.setString( 9, sab.height );
398 statement.setString( 10, sab.borderStyle );
399 statement.setString( 11, sab.borderWidth );
400 statement.setInt( 12, sab.paddingTop );
401 statement.setInt( 13, sab.paddingLeft );
402 statement.setInt( 14, sab.paddingRight );
403 statement.setInt( 15, sab.paddingBottom );
404 statement.setString( 16, sab.textDecoration );
405 statement.setInt( 17, sab.getStyleClassId() );
406 statement.setInt( 18, sab.getPartnerId() );
407 statement.executeUpdate();
408 }
409 finally
410 {
411 try
412 {
413 if ( statement != null ) statement.close();
414 }
415 catch ( SQLException sex )
416 {
417 Logger.error( "Error closing statement in PartnerStyleHelper.delete for style_class_id " + sab.getStyleClassId() + " and partner_id " + sab.getPartnerId() + ".", sex );
418 }
419
420 try
421 {
422 if ( connection != null ) connection.close();
423 }
424 catch ( SQLException sex )
425 {
426 Logger.error( "Error closing connection in PartnerStyleHelper.delete for style_class_id " + sab.getStyleClassId() + " and partner_id " + sab.getPartnerId() + ".", sex );
427 }
428 }
429 }
430
431 /**
432 * Delete the record from the database identified by the primary key
433 * value specified.
434 *
435 * @param styleClassId - The <code>style_class_id</code>
436 * primary key value.
437 * @param partnerId - The <code>partner_id</code>
438 * primary key value.
439 * @throws SQLException - If database errors are encountered while
440 * writing to the database.
441 * @throws NamingException - If errors are encountered while getting
442 * a connection from the connection pool.
443 */
444 static final void delete( int styleClassId, int partnerId )
445 throws SQLException, NamingException
446 {
447 Connection connection = null;
448 PreparedStatement statement = null;
449
450 try
451 {
452 connection = DatabaseHelper.getConnection();
453 statement = connection.prepareStatement( PartnerStyleHelper.DELETE_PARTNER_STYLES );
454 statement.setInt( 1, styleClassId );
455 statement.setInt( 2, partnerId );
456 statement.executeUpdate();
457 }
458 finally
459 {
460 try
461 {
462 if ( statement != null ) statement.close();
463 }
464 catch ( SQLException sex )
465 {
466 Logger.error( "Error closing statement in PartnerStyleHelper.delete for style_class_id " + styleClassId + " and partner_id " + partnerId + ".", sex );
467 }
468
469 try
470 {
471 if ( connection != null ) connection.close();
472 }
473 catch ( SQLException sex )
474 {
475 Logger.error( "Error closing connection in PartnerStyleHelper.delete for style_class_id " + styleClassId + " and partner_id " + partnerId + ".", sex );
476 }
477 }
478 }
479
480 /**
481 * Default constructor. Cannot be instantiated.
482 */
483 private PartnerStyleHelper() {}
484 }