001: /* ====================================================================
002: * The LateralNZ Software License, Version 1.0
003: *
004: * Copyright (c) 2003 LateralNZ. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above copyright
011: * notice, this list of conditions and the following disclaimer.
012: *
013: * 2. Redistributions in binary form must reproduce the above copyright
014: * notice, this list of conditions and the following disclaimer in
015: * the documentation and/or other materials provided with the
016: * distribution.
017: *
018: * 3. The end-user documentation included with the redistribution,
019: * if any, must include the following acknowledgment:
020: * "This product includes software developed by
021: * LateralNZ (http://www.lateralnz.org/) and other third parties."
022: * Alternately, this acknowledgment may appear in the software itself,
023: * if and wherever such third-party acknowledgments normally appear.
024: *
025: * 4. The names "LateralNZ" must not be used to endorse or promote
026: * products derived from this software without prior written
027: * permission. For written permission, please
028: * contact oss@lateralnz.org.
029: *
030: * 5. Products derived from this software may not be called "Panther",
031: * or "Lateral" or "LateralNZ", nor may "PANTHER" or "LATERAL" or
032: * "LATERALNZ" appear in their name, without prior written
033: * permission of LateralNZ.
034: *
035: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
036: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
037: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
038: * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
039: * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
040: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
041: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
042: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
043: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
044: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
045: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
046: * SUCH DAMAGE.
047: * ====================================================================
048: *
049: * This software consists of voluntary contributions made by many
050: * individuals on behalf of LateralNZ. For more
051: * information on Lateral, please see http://www.lateralnz.com/ or
052: * http://www.lateralnz.org
053: *
054: */
055: package org.lateralnz.common.util;
056:
057: import java.sql.Connection;
058: import java.sql.DatabaseMetaData;
059: import java.sql.Driver;
060: import java.sql.DriverManager;
061: import java.sql.Statement;
062: import java.sql.PreparedStatement;
063: import java.sql.ResultSet;
064: import java.sql.ResultSetMetaData;
065: import java.sql.SQLException;
066: import java.sql.Timestamp;
067: import java.sql.Types;
068: import java.util.Date;
069: import java.util.HashMap;
070: import java.util.ResourceBundle;
071: import java.util.MissingResourceException;
072: import javax.naming.NamingException;
073: import javax.sql.DataSource;
074:
075: import org.apache.log4j.Logger;
076:
077: /**
078: * utility functions for use with Data Access Objects and for more general
079: * JDBC stuff
080: *
081: * @author J R Briggs
082: */
083: public class DAOUtils implements Constants {
084: private static final Logger log = Logger.getLogger(DAOUtils.class
085: .getName());
086: private static final ResourceBundle resources = ResourceUtils
087: .getStaticBundle(DAOUtils.class.getName(), DAOUtils.class);
088:
089: private static HashMap columnInfoMap = new HashMap();
090: private static HashMap placeholders = new HashMap(); // hashmap of placeholder strings (so we don't need to create them over and over again
091:
092: private static final String DUPLICATE_KEY = "duplicate key";
093:
094: private static String DEFAULT_DATASOURCE;
095:
096: static {
097: try {
098: DEFAULT_DATASOURCE = resources
099: .getString("default_datasource");
100: } catch (Exception e) {
101: e.printStackTrace();
102: }
103: }
104:
105: public static final String buildSQL(ResourceBundle sqlstatements,
106: String[] keys) {
107: StringBuffer sb = new StringBuffer();
108:
109: try {
110: for (int i = 0; i < keys.length; i++) {
111: if (i > 0) {
112: sb.append(SPACE);
113: }
114: sb.append(sqlstatements.getString(keys[i]));
115: }
116:
117: return sb.toString();
118: } catch (MissingResourceException mre) {
119: throw new RuntimeException(
120: "missing sql statement resource " + mre.getKey());
121: }
122: }
123:
124: public static boolean commit(Connection conn) {
125: try {
126: if (conn != null) {
127: conn.commit();
128: }
129:
130: return true;
131: } catch (SQLException se) {
132: se.printStackTrace();
133: return false;
134: }
135: }
136:
137: public static boolean close(Statement st) {
138: if (st != null) {
139: try {
140: st.clearWarnings();
141: } catch (SQLException se) {
142: if (log.isDebugEnabled()) {
143: log.debug("statement clear warnings failed "
144: + se.getMessage());
145: }
146: return false;
147: }
148:
149: try {
150: st.close();
151: } catch (SQLException se) {
152: se.printStackTrace();
153: return false;
154: }
155: }
156:
157: return true;
158: }
159:
160: public static boolean close(ResultSet rs) {
161: if (rs != null) {
162: try {
163: rs.clearWarnings();
164: } catch (SQLException se) {
165: if (log.isDebugEnabled()) {
166: log.debug("resultset clear warnings failed "
167: + se.getMessage());
168: }
169: return false;
170: }
171:
172: try {
173: rs.close();
174: } catch (SQLException se) {
175: se.printStackTrace();
176: return false;
177: }
178: }
179:
180: return true;
181: }
182:
183: public static boolean close(Connection conn) {
184: if (conn != null) {
185: try {
186: conn.clearWarnings();
187: } catch (SQLException se) {
188: if (log.isDebugEnabled()) {
189: log.debug("connection clear warnings failed "
190: + se.getMessage());
191: }
192: return false;
193: }
194:
195: try {
196: conn.close();
197: } catch (SQLException se) {
198: se.printStackTrace();
199: return false;
200: }
201: }
202:
203: return true;
204: }
205:
206: public static final String createParameterPlaceholders(int count) {
207: String key = Integer.toString(count);
208: if (!placeholders.containsKey(key)) {
209: synchronized (placeholders) {
210: if (!placeholders.containsKey(key)) {
211: StringBuffer sb = new StringBuffer();
212: int end = count - 1;
213: for (int i = 0; i < count; i++) {
214: sb.append(QUESTION_MARK);
215: if (i < end) {
216: sb.append(COMMA);
217: }
218: }
219: placeholders.put(key, sb.toString());
220: }
221: }
222: }
223: return (String) placeholders.get(key);
224: }
225:
226: public static final String createSQLArrayString(String[] vals,
227: int start, int len) {
228: StringBuffer sb = new StringBuffer(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 dump(ResultSet rs) {
241: try {
242: StringBuffer sb = new StringBuffer();
243: ResultSetMetaData meta = rs.getMetaData();
244: for (int i = 1; i <= meta.getColumnCount(); i++) {
245: sb.append(meta.getColumnName(i)).append(COMMA).append(
246: meta.getColumnLabel(i)).append(NEWLINE);
247: }
248: return sb.toString();
249: } catch (SQLException se) {
250: return se.getMessage();
251: }
252: }
253:
254: public static String dumpData(ResultSet rs) {
255: try {
256: StringBuffer sb = new StringBuffer();
257: ResultSetMetaData 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: } catch (SQLException se) {
266: return se.getMessage();
267: }
268: }
269:
270: /**
271: * get a char(1) as a boolean value. e.g. Y = true, N = false, an empty char = defaultvalue
272: */
273: public static boolean getCharAsBoolean(ResultSet rs, String column,
274: boolean defaultValue) throws SQLException {
275: String s = rs.getString(column);
276: if (StringUtils.isEmpty(s)) {
277: return defaultValue;
278: } else if (s.equalsIgnoreCase(Y)) {
279: return true;
280: } else {
281: return false;
282: }
283: }
284:
285: /**
286: * given a resultset and column name of a timestamp column,
287: * return the column as a java.util.Date
288: */
289: public static Date getDate(ResultSet rs, String columnName)
290: throws SQLException {
291: return new Date(rs.getTimestamp(columnName).getTime());
292: }
293:
294: /**
295: * a method for checking if column meta data has been cached for the specified schema, table
296: * and column name.
297: * BEWARE: this method may need to change from database to database
298: */
299: public static boolean hasColumnMetaData(String schema,
300: String table, String column) {
301: //String key = schema + DOT + table + DOT + column;
302: String key = table + DOT + column;
303: return columnInfoMap.containsKey(key);
304: }
305:
306: /**
307: * return column meta data for a particular column using the specified connection. If the data
308: * is present in the cache, then the cached metadata is returned, otherwise the connection is used
309: * and the response is cached.
310: */
311: public static ColumnMetaData getColumnMetaData(Connection conn,
312: String schema, String table, String column)
313: throws SQLException {
314: String key = table + DOT + column;
315: ColumnMetaData cmd = null;
316: if (columnInfoMap.containsKey(key)) {
317: cmd = (ColumnMetaData) columnInfoMap.get(key);
318: } else {
319: ResultSet rs = null;
320: try {
321: DatabaseMetaData 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(
326: rs.getShort(5),
327: rs.getInt(7),
328: (rs.getInt(11) == DatabaseMetaData.columnNoNulls ? true
329: : false));
330: columnInfoMap.put(key, cmd);
331: } else {
332: throw new SQLException("invalid metadata for "
333: + key);
334: }
335: } finally {
336: close(rs);
337: }
338: }
339:
340: return cmd;
341: }
342:
343: /**
344: * get a connection from a named datasource using JNDI
345: * @param datasource the name of the data source to retrieve
346: */
347: public static Connection getConnection(String datasource)
348: throws SQLException {
349: DataSource ds = null;
350: try {
351: if (StringUtils.isEmpty(datasource)) {
352: datasource = DEFAULT_DATASOURCE;
353: }
354:
355: ds = (DataSource) JNDIUtils.get(JNDIUtils.DAO_CONTEXT,
356: datasource);
357:
358: Connection con = ds.getConnection();
359:
360: if (con == null) {
361: throw new SQLException(
362: "unable to get connection for datasource: '"
363: + datasource + "'");
364: }
365:
366: return con;
367: } catch (NamingException ne) {
368: throw new SQLException("unable to find datasource: '"
369: + datasource + "'");
370: }
371: }
372:
373: public static boolean isDuplicateKeyException(Throwable t) {
374: if (t == null || !(t instanceof SQLException)) {
375: return false;
376: } else {
377: return isDuplicateKeyException(t);
378: }
379: }
380:
381: public static boolean isDuplicateKeyException(SQLException se) {
382: if (se == null) {
383: return false;
384: } else {
385: String msg = se.getMessage();
386: return (msg != null && msg.indexOf(DUPLICATE_KEY) != -1);
387: }
388: }
389:
390: public static final String parseInClause(String sql, int numInList) {
391: String inclause = createParameterPlaceholders(numInList);
392:
393: return StringUtils.replace(sql, "@@@", inclause);
394: }
395:
396: public static final void registerDriver(String driverClass)
397: throws Exception {
398: Class c = Class.forName(driverClass);
399: Driver d = (Driver) c.newInstance();
400: DriverManager.registerDriver(d);
401: }
402:
403: /**
404: * set a String parameter on a prepared statement according to its index.
405: * Note: if the value is null, this will call setNull
406: * @param ps the PreparedStatement
407: * @param col the index of the parameter
408: * @param value the string to set
409: */
410: public static void setParam(PreparedStatement ps, int col,
411: String value) throws SQLException {
412: if (value == null) {
413: ps.setNull(col, Types.VARCHAR);
414: } else {
415: ps.setString(col, value);
416: }
417: }
418:
419: /**
420: * set an int parameter on a prepared statement according to its index.
421: * Note: if the value is less than 0, setNull will be called. If you
422: * want to set a negative integer, then you must call setParam with
423: * preserveNegative set to true.
424: * @param ps the PreparedStatement
425: * @param col the index of the parameter
426: * @param value the int value to set
427: */
428: public static void setParam(PreparedStatement ps, int col, int value)
429: throws SQLException {
430: setParam(ps, col, value, false);
431: }
432:
433: /**
434: * set an int parameter on a prepared statement according to its index.
435: * Note: if the value is less than 0, setNull will be called unless
436: * preserve is set to true
437: * @param ps the PreparedStatement
438: * @param col the index of the parameter
439: * @param value the int value to set
440: * @param preserveNegative do not call setNull if this is true
441: */
442: public static void setParam(PreparedStatement ps, int col,
443: int value, boolean preserve) throws SQLException {
444: if (preserve || value != Integer.MIN_VALUE) {
445: ps.setInt(col, value);
446: } else {
447: ps.setNull(col, Types.INTEGER);
448: }
449: }
450:
451: /**
452: * set a float parameter on a prepared statement according to its index.
453: * Note: if the value is Float.NEGATIVE_INFINITY, setNull will be called
454: * @param ps the PreparedStatement
455: * @param col the index of the parameter
456: * @param value the float value to set
457: * @param preserveNegative do not call setNull if this is true
458: */
459: public static void setParam(PreparedStatement ps, int col,
460: float value) throws SQLException {
461: if (value != Float.NEGATIVE_INFINITY) {
462: ps.setFloat(col, value);
463: } else {
464: ps.setNull(col, Types.FLOAT);
465: }
466: }
467:
468: /**
469: * set a Date parameter on a prepared statement according to its index.
470: * Note: if the value is null, this will call setNull
471: * @param ps the PreparedStatement
472: * @param col the index of the parameter
473: * @param value the date to set
474: */
475: public static void setParam(PreparedStatement ps, int col,
476: Date value) throws SQLException {
477: if (value == null) {
478: ps.setNull(col, Types.DATE);
479: } else {
480: ps.setTimestamp(col, new Timestamp(value.getTime()));
481: }
482: }
483:
484: public static void setParam(PreparedStatement ps, int col,
485: boolean value) throws SQLException {
486: if (value) {
487: ps.setString(col, Y);
488: } else {
489: ps.setString(col, N);
490: }
491: }
492:
493: }
|