KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > lateralnz > common > util > DAOUtils


1 /* ====================================================================
2  * The LateralNZ Software License, Version 1.0
3  *
4  * Copyright (c) 2003 LateralNZ. All rights reserved.
5  *
6  * Redistribution and use in source and binary forms, with or without
7  * modification, are permitted provided that the following conditions
8  * are met:
9  *
10  * 1. Redistributions of source code must retain the above copyright
11  * notice, this list of conditions and the following disclaimer.
12  *
13  * 2. Redistributions in binary form must reproduce the above copyright
14  * notice, this list of conditions and the following disclaimer in
15  * the documentation and/or other materials provided with the
16  * distribution.
17  *
18  * 3. The end-user documentation included with the redistribution,
19  * if any, must include the following acknowledgment:
20  * "This product includes software developed by
21  * LateralNZ (http://www.lateralnz.org/) and other third parties."
22  * Alternately, this acknowledgment may appear in the software itself,
23  * if and wherever such third-party acknowledgments normally appear.
24  *
25  * 4. The names "LateralNZ" must not be used to endorse or promote
26  * products derived from this software without prior written
27  * permission. For written permission, please
28  * contact oss@lateralnz.org.
29  *
30  * 5. Products derived from this software may not be called "Panther",
31  * or "Lateral" or "LateralNZ", nor may "PANTHER" or "LATERAL" or
32  * "LATERALNZ" appear in their name, without prior written
33  * permission of LateralNZ.
34  *
35  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
36  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
37  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
38  * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
39  * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
40  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
41  * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
42  * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
43  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
44  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
45  * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
46  * SUCH DAMAGE.
47  * ====================================================================
48  *
49  * This software consists of voluntary contributions made by many
50  * individuals on behalf of LateralNZ. For more
51  * information on Lateral, please see http://www.lateralnz.com/ or
52  * http://www.lateralnz.org
53  *
54  */

55 package org.lateralnz.common.util;
56
57 import java.sql.Connection JavaDoc;
58 import java.sql.DatabaseMetaData JavaDoc;
59 import java.sql.Driver JavaDoc;
60 import java.sql.DriverManager JavaDoc;
61 import java.sql.Statement JavaDoc;
62 import java.sql.PreparedStatement JavaDoc;
63 import java.sql.ResultSet JavaDoc;
64 import java.sql.ResultSetMetaData JavaDoc;
65 import java.sql.SQLException JavaDoc;
66 import java.sql.Timestamp JavaDoc;
67 import java.sql.Types JavaDoc;
68 import java.util.Date JavaDoc;
69 import java.util.HashMap JavaDoc;
70 import java.util.ResourceBundle JavaDoc;
71 import java.util.MissingResourceException JavaDoc;
72 import javax.naming.NamingException JavaDoc;
73 import javax.sql.DataSource JavaDoc;
74
75 import org.apache.log4j.Logger;
76
77 /**
78  * utility functions for use with Data Access Objects and for more general
79  * JDBC stuff
80  *
81  * @author J R Briggs
82  */

83 public class DAOUtils implements Constants {
84   private static final Logger log = Logger.getLogger(DAOUtils.class.getName());
85   private static final ResourceBundle JavaDoc resources = ResourceUtils.getStaticBundle(DAOUtils.class.getName(), DAOUtils.class);
86   
87   private static HashMap JavaDoc columnInfoMap = new HashMap JavaDoc();
88   private static HashMap JavaDoc placeholders = new HashMap JavaDoc(); // hashmap of placeholder strings (so we don't need to create them over and over again
89

90   private static final String JavaDoc DUPLICATE_KEY = "duplicate key";
91
92   private static String JavaDoc DEFAULT_DATASOURCE;
93   
94   static {
95     try {
96       DEFAULT_DATASOURCE = resources.getString("default_datasource");
97     }
98     catch (Exception JavaDoc e) {
99       e.printStackTrace();
100     }
101   }
102   
103   public static final String JavaDoc buildSQL(ResourceBundle JavaDoc sqlstatements, String JavaDoc[] keys) {
104     StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
105     
106     try {
107       for (int i = 0; i < keys.length; i++) {
108         if (i > 0) {
109           sb.append(SPACE);
110         }
111         sb.append(sqlstatements.getString(keys[i]));
112       }
113      
114       return sb.toString();
115     }
116     catch (MissingResourceException JavaDoc mre) {
117       throw new RuntimeException JavaDoc("missing sql statement resource " + mre.getKey());
118     }
119   }
120
121   public static boolean commit(Connection JavaDoc conn) {
122     try {
123       if (conn != null) {
124         conn.commit();
125       }
126       
127       return true;
128     }
129     catch (SQLException JavaDoc se) {
130       se.printStackTrace();
131       return false;
132     }
133   }
134
135   public static boolean close(Statement JavaDoc st) {
136     if (st != null) {
137       try {
138         st.clearWarnings();
139       }
140       catch (SQLException JavaDoc se) {
141         if (log.isDebugEnabled()) {
142           log.debug("statement clear warnings failed " + se.getMessage());
143         }
144         return false;
145       }
146       
147       try {
148         st.close();
149       }
150       catch (SQLException JavaDoc se) {
151         se.printStackTrace();
152         return false;
153       }
154     }
155       
156     return true;
157   }
158
159   public static boolean close(ResultSet JavaDoc rs) {
160     if (rs != null) {
161       try {
162         rs.clearWarnings();
163       }
164       catch (SQLException JavaDoc se) {
165         if (log.isDebugEnabled()) {
166           log.debug("resultset clear warnings failed " + se.getMessage());
167         }
168         return false;
169       }
170       
171       try {
172         rs.close();
173       }
174       catch (SQLException JavaDoc se) {
175         se.printStackTrace();
176         return false;
177       }
178     }
179       
180     return true;
181   }
182   
183   public static boolean close(Connection JavaDoc conn) {
184     if (conn != null) {
185       try {
186         conn.clearWarnings();
187       }
188       catch (SQLException JavaDoc se) {
189         if (log.isDebugEnabled()) {
190           log.debug("connection clear warnings failed " + se.getMessage());
191         }
192         return false;
193       }
194       
195       try {
196         conn.close();
197       }
198       catch (SQLException JavaDoc se) {
199         se.printStackTrace();
200         return false;
201       }
202     }
203       
204     return true;
205   }
206   
207   public static final String JavaDoc createParameterPlaceholders(int count) {
208     String JavaDoc key = Integer.toString(count);
209     if (!placeholders.containsKey(key)) {
210       synchronized (placeholders) {
211         if (!placeholders.containsKey(key)) {
212           StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
213           int end = count - 1;
214           for (int i = 0; i < count; i++) {
215             sb.append(QUESTION_MARK);
216             if (i < end) {
217               sb.append(COMMA);
218             }
219           }
220           placeholders.put(key, sb.toString());
221         }
222       }
223     }
224     return (String JavaDoc)placeholders.get(key);
225   }
226
227   public static final String JavaDoc createSQLArrayString(String JavaDoc[] vals, int start, int len) {
228     StringBuffer JavaDoc sb = new StringBuffer JavaDoc(LEFT_BRACE);
229     int stopcomma = len - 1;
230     for (int i = start; i < len; i++) {
231       sb.append(QUOTE).append(vals[i]).append(QUOTE);
232       if (i < stopcomma) {
233         sb.append(COMMA);
234       }
235     }
236     sb.append(RIGHT_BRACE);
237     return sb.toString();
238   }
239   
240   public static String JavaDoc dump(ResultSet JavaDoc rs) {
241     try {
242       StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
243       ResultSetMetaData JavaDoc meta = rs.getMetaData();
244       for (int i = 1; i <= meta.getColumnCount(); i++) {
245         sb.append(meta.getColumnName(i)).append(COMMA).append(meta.getColumnLabel(i)).append(NEWLINE);
246       }
247       return sb.toString();
248     }
249     catch (SQLException JavaDoc se) {
250       return se.getMessage();
251     }
252   }
253   
254   public static String JavaDoc dumpData(ResultSet JavaDoc rs) {
255     try {
256       StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
257       ResultSetMetaData JavaDoc meta = rs.getMetaData();
258       while (rs.next()) {
259         for (int i = 1; i <= meta.getColumnCount(); i++) {
260           sb.append(rs.getString(i)).append(COMMA);
261         }
262         sb.append(NEWLINE);
263       }
264       return sb.toString();
265     }
266     catch (SQLException JavaDoc se) {
267       return se.getMessage();
268     }
269   }
270   
271  /**
272   * get a char(1) as a boolean value. e.g. Y = true, N = false, an empty char = defaultvalue
273   */

274   public static boolean getCharAsBoolean(ResultSet JavaDoc rs, String JavaDoc column, boolean defaultValue) throws SQLException JavaDoc {
275     String JavaDoc s = rs.getString(column);
276     if (StringUtils.isEmpty(s)) {
277       return defaultValue;
278     }
279     else if (s.equalsIgnoreCase(Y)) {
280       return true;
281     }
282     else {
283       return false;
284     }
285   }
286   
287  /**
288   * given a resultset and column name of a timestamp column,
289   * return the column as a java.util.Date
290   */

291   public static Date JavaDoc getDate(ResultSet JavaDoc rs, String JavaDoc columnName) throws SQLException JavaDoc {
292     return new Date JavaDoc(rs.getTimestamp(columnName).getTime());
293   }
294
295
296  /**
297   * a method for checking if column meta data has been cached for the specified schema, table
298   * and column name.
299   * BEWARE: this method may need to change from database to database
300   */

301   public static boolean hasColumnMetaData(String JavaDoc schema, String JavaDoc table, String JavaDoc column) {
302     //String key = schema + DOT + table + DOT + column;
303
String JavaDoc key = table + DOT + column;
304     return columnInfoMap.containsKey(key);
305   }
306   
307  /**
308   * return column meta data for a particular column using the specified connection. If the data
309   * is present in the cache, then the cached metadata is returned, otherwise the connection is used
310   * and the response is cached.
311   */

312   public static ColumnMetaData getColumnMetaData(Connection JavaDoc conn, String JavaDoc schema, String JavaDoc table, String JavaDoc column) throws SQLException JavaDoc {
313     String JavaDoc key = table + DOT + column;
314     ColumnMetaData cmd = null;
315     if (columnInfoMap.containsKey(key)) {
316       cmd = (ColumnMetaData)columnInfoMap.get(key);
317     }
318     else {
319       ResultSet JavaDoc rs = null;
320       try {
321         DatabaseMetaData JavaDoc meta = conn.getMetaData();
322         //rs = meta.getColumns(null, (schema != null ? schema.toUpperCase() : null), table.toUpperCase(), column.toUpperCase());
323
rs = meta.getColumns(null, null, table, column);
324         if (rs.next()) {
325           cmd = new ColumnMetaData(rs.getShort(5), rs.getInt(7), (rs.getInt(11) == DatabaseMetaData.columnNoNulls ? true : false));
326           columnInfoMap.put(key, cmd);
327         }
328         else {
329           throw new SQLException JavaDoc("invalid metadata for " + key);
330         }
331       }
332       finally {
333         close(rs);
334       }
335     }
336     
337     return cmd;
338   }
339
340   
341  /**
342   * get a connection from a named datasource using JNDI
343   * @param datasource the name of the data source to retrieve
344   */

345   public static Connection JavaDoc getConnection(String JavaDoc datasource) throws SQLException JavaDoc {
346     DataSource JavaDoc ds = null;
347     try {
348       if (StringUtils.isEmpty(datasource)) {
349         datasource = DEFAULT_DATASOURCE;
350       }
351       
352       ds = (DataSource JavaDoc)JNDIUtils.get(JNDIUtils.DAO_CONTEXT, datasource);
353     
354       Connection JavaDoc con = ds.getConnection();
355
356       if (con == null) {
357         throw new SQLException JavaDoc("unable to get connection for datasource: '" + datasource + "'");
358       }
359
360       return con;
361     }
362     catch (NamingException JavaDoc ne) {
363       throw new SQLException JavaDoc("unable to find datasource: '" + datasource + "'");
364     }
365   }
366   
367   public static boolean isDuplicateKeyException(Throwable JavaDoc t) {
368     if (t == null || !(t instanceof SQLException JavaDoc)) {
369       return false;
370     }
371     else {
372       return isDuplicateKeyException(t);
373     }
374   }
375   
376   public static boolean isDuplicateKeyException(SQLException JavaDoc se) {
377     if (se == null) {
378       return false;
379     }
380     else {
381       String JavaDoc msg = se.getMessage();
382       return (msg != null && msg.indexOf(DUPLICATE_KEY) != -1);
383     }
384   }
385   
386   public static final String JavaDoc parseInClause(String JavaDoc sql, int numInList) {
387     String JavaDoc inclause = createParameterPlaceholders(numInList);
388     
389     return StringUtils.replace(sql, "@@@", inclause);
390   }
391   
392   public static final void registerDriver(String JavaDoc driverClass) throws Exception JavaDoc {
393     Class JavaDoc c = Class.forName(driverClass);
394     Driver JavaDoc d = (Driver JavaDoc)c.newInstance();
395     DriverManager.registerDriver(d);
396   }
397
398  /**
399   * set a String parameter on a prepared statement according to its index.
400   * Note: if the value is null, this will call setNull
401   * @param ps the PreparedStatement
402   * @param col the index of the parameter
403   * @param value the string to set
404   */

405   public static void setParam(PreparedStatement JavaDoc ps, int col, String JavaDoc value) throws SQLException JavaDoc {
406     if (value == null) {
407       ps.setNull(col, Types.VARCHAR);
408     }
409     else {
410       ps.setString(col, value);
411     }
412   }
413
414  /**
415   * set an int parameter on a prepared statement according to its index.
416   * Note: if the value is less than 0, setNull will be called. If you
417   * want to set a negative integer, then you must call setParam with
418   * preserveNegative set to true.
419   * @param ps the PreparedStatement
420   * @param col the index of the parameter
421   * @param value the int value to set
422   */

423   public static void setParam(PreparedStatement JavaDoc ps, int col, int value) throws SQLException JavaDoc {
424     setParam(ps, col, value, false);
425   }
426
427  /**
428   * set an int parameter on a prepared statement according to its index.
429   * Note: if the value is less than 0, setNull will be called unless
430   * preserve is set to true
431   * @param ps the PreparedStatement
432   * @param col the index of the parameter
433   * @param value the int value to set
434   * @param preserveNegative do not call setNull if this is true
435   */

436   public static void setParam(PreparedStatement JavaDoc ps, int col, int value, boolean preserve) throws SQLException JavaDoc {
437     if (preserve || value != Integer.MIN_VALUE) {
438       ps.setInt(col, value);
439     }
440     else {
441       ps.setNull(col, Types.INTEGER);
442     }
443   }
444
445  /**
446   * set a float parameter on a prepared statement according to its index.
447   * Note: if the value is Float.NEGATIVE_INFINITY, setNull will be called
448   * @param ps the PreparedStatement
449   * @param col the index of the parameter
450   * @param value the float value to set
451   * @param preserveNegative do not call setNull if this is true
452   */

453   public static void setParam(PreparedStatement JavaDoc ps, int col, float value) throws SQLException JavaDoc {
454     if (value != Float.NEGATIVE_INFINITY) {
455       ps.setFloat(col, value);
456     }
457     else {
458       ps.setNull(col, Types.FLOAT);
459     }
460   }
461
462  /**
463   * set a Date parameter on a prepared statement according to its index.
464   * Note: if the value is null, this will call setNull
465   * @param ps the PreparedStatement
466   * @param col the index of the parameter
467   * @param value the date to set
468   */

469   public static void setParam(PreparedStatement JavaDoc ps, int col, Date JavaDoc value) throws SQLException JavaDoc {
470     if (value == null) {
471       ps.setNull(col, Types.DATE);
472     }
473     else {
474       ps.setTimestamp(col, new Timestamp JavaDoc(value.getTime()));
475     }
476   }
477   
478   public static void setParam(PreparedStatement JavaDoc ps, int col, boolean value) throws SQLException JavaDoc {
479     if (value) {
480       ps.setString(col, Y);
481     }
482     else {
483       ps.setString(col, N);
484     }
485   }
486   
487 }
Popular Tags