0001: /*
0002: * (C) Copyright 2001 Nabh Information Systems, Inc.
0003: *
0004: * All copyright notices regarding Nabh's products MUST remain
0005: * intact in the scripts and in the outputted HTML.
0006: * This program is free software; you can redistribute it and/or
0007: * modify it under the terms of the GNU Lesser General Public License
0008: * as published by the Free Software Foundation; either version 2.1
0009: * of the License, or (at your option) any later version.
0010: *
0011: * This program is distributed in the hope that it will be useful,
0012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0014: * GNU Lesser General Public License for more details.
0015: *
0016: * You should have received a copy of the GNU Lesser General Public License
0017: * along with this program; if not, write to the Free Software
0018: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
0019: *
0020: */
0021:
0022: package com.nabhinc.util.db;
0023:
0024: import java.sql.Connection;
0025: import java.sql.DriverManager;
0026: import java.sql.PreparedStatement;
0027: import java.sql.ResultSet;
0028: import java.sql.SQLException;
0029: import java.text.DecimalFormat;
0030: import java.text.SimpleDateFormat;
0031: import java.util.HashMap;
0032: import java.util.Hashtable;
0033: import java.util.Locale;
0034: import java.util.Properties;
0035: import java.util.Vector;
0036:
0037: import javax.naming.InitialContext;
0038: import javax.naming.NamingException;
0039: import javax.sql.DataSource;
0040: import javax.transaction.TransactionManager;
0041:
0042: import org.apache.commons.dbcp.BasicDataSource;
0043:
0044: import com.nabhinc.core.Defaults;
0045: import com.nabhinc.util.StringUtil;
0046: import com.nabhinc.util.i18n.DateTimeFormatUtil;
0047:
0048: /**
0049: * Provides utility methods to get a database connection. Depending on
0050: * availability of JTA, the connection is either a pooled connection from
0051: * a data source, or a direct connection to a database.
0052: *
0053: * @author Padmanabh Dabke
0054: * (c) 2001 Nabh Information Systems, Inc. All Rights Reserved.
0055: */
0056: public class DBUtil {
0057:
0058: public static boolean dbuUseJNDI = true;
0059:
0060: /**
0061: * Hashtable used to keep the mapping between datasource names and
0062: * datasource implementations.
0063: */
0064: public static Hashtable<String, DataSource> dbuLocalDSTable = new Hashtable<String, DataSource>();
0065:
0066: /**
0067: * Maintains mapping between datasource names and flavor strings.
0068: */
0069: protected static Hashtable<String, String> dbuDSFlavorsTable = new Hashtable<String, String>();
0070:
0071: /**
0072: * Initialization properties
0073: */
0074: private static Properties dbuProps = null;
0075:
0076: /**
0077: * Reference to the transaction manager.
0078: */
0079: private static TransactionManager dbuTM = null;
0080:
0081: /**
0082: * Do nothing constructor.
0083: */
0084: public DBUtil() {
0085: super ();
0086: }
0087:
0088: /**
0089: * Starts a transaction only if it has not been started.
0090: * @return true if a new transaction was started
0091: * @exception java.sql.SQLException Database exception.
0092: */
0093: public static boolean beginTransaction() throws Exception {
0094: if (dbuTM.getStatus() == javax.transaction.Status.STATUS_ACTIVE)
0095: return false;
0096: else {
0097: dbuTM.begin();
0098: return true;
0099: }
0100: }
0101:
0102: /**
0103: * Invokes commit on transaction manager.
0104: * @exception java.sql.SQLException Database exception.
0105: */
0106: public static void commitTransaction() throws Exception {
0107: //System.out.println(Thread.currentThread() + " : Committing transaction");
0108: dbuTM.commit();
0109: }
0110:
0111: /**
0112: * Gets a datbase connection.
0113: * @return Database connection
0114: * @exception java.sql.SQLException
0115: */
0116: public static java.sql.Connection getConnection()
0117: throws java.sql.SQLException, NamingException {
0118:
0119: //return dbuDefaultDS.getConnection();
0120: return getConnection(Defaults.getDataSourceName());
0121:
0122: }
0123:
0124: /**
0125: * Gets a datbase connection from a datasource that is looked up
0126: * using the given JNDI. Note that this will not work in the non-j2ee
0127: * configuration
0128: * @param dsName JNDI name for the datasource
0129: * @return Database connection
0130: * @exception java.sql.SQLException
0131: */
0132: public static Connection getConnection(String dsName)
0133: throws java.sql.SQLException, javax.naming.NamingException {
0134:
0135: if (StringUtil.isNullOrEmpty(dsName)) {
0136: dsName = Defaults.getDataSourceName();
0137:
0138: }
0139:
0140: if (dbuUseJNDI) {
0141: DataSource ds = (DataSource) new InitialContext(dbuProps)
0142: .lookup(dsName);
0143: return ds.getConnection();
0144: } else {
0145: DataSource ds = (DataSource) dbuLocalDSTable.get(dsName);
0146: if (ds != null)
0147: return ds.getConnection();
0148: }
0149: return null;
0150:
0151: }
0152:
0153: /**
0154: * Gets a datbase connection from a datasource that is looked up
0155: * using the given JNDI. Note that this will not work in the non-j2ee
0156: * configuration
0157: * @param dsName JNDI name for the datasource
0158: * @return Database connection
0159: * @exception java.sql.SQLException
0160: */
0161: public static DataSource getDataSource(String dsName)
0162: throws javax.naming.NamingException {
0163:
0164: if (StringUtil.isNullOrEmpty(dsName)) {
0165: dsName = Defaults.getDataSourceName();
0166:
0167: }
0168:
0169: if (dbuUseJNDI)
0170: return (DataSource) new InitialContext(dbuProps)
0171: .lookup(dsName);
0172: else
0173: return (DataSource) dbuLocalDSTable.get(dsName);
0174:
0175: }
0176:
0177: /**
0178: *
0179: * @return
0180: * @throws javax.transaction.SystemException
0181: */
0182: public static int getStatus()
0183: throws javax.transaction.SystemException {
0184: return dbuTM.getStatus();
0185: }
0186:
0187: /**
0188: * Rolls back current transaction.
0189: * @exception java.sql.SQLException The exception description.
0190: */
0191: public static void rollbackTransaction() throws Exception {
0192: dbuTM.rollback();
0193: }
0194:
0195: /**
0196: * Mark current transaction for rollback.
0197: *
0198: * @exception java.sql.SQLException The exception description.
0199: */
0200: public static void setRollbackOnly() throws Exception {
0201: dbuTM.setRollbackOnly();
0202: }
0203:
0204: /**
0205: *
0206: * @param rs
0207: * @param colIndex
0208: * @param colType
0209: * @param format
0210: * @return
0211: * @throws SQLException
0212: */
0213: public static String getFormattedValue(ResultSet rs, int colIndex,
0214: int colType, String format) throws SQLException {
0215:
0216: switch (colType) {
0217: case java.sql.Types.INTEGER:
0218: case java.sql.Types.DECIMAL:
0219: if (format == null)
0220: return Integer.toString(rs.getInt(colIndex));
0221: else
0222: return new DecimalFormat(format).format(rs
0223: .getInt(colIndex));
0224: case java.sql.Types.FLOAT:
0225: if (format == null)
0226: return Float.toString(rs.getFloat(colIndex));
0227: else
0228: return new DecimalFormat(format).format(rs
0229: .getFloat(colIndex));
0230: case java.sql.Types.DOUBLE:
0231: if (format == null)
0232: return Double.toString(rs.getDouble(colIndex));
0233: else
0234: return new DecimalFormat(format).format(rs
0235: .getDouble(colIndex));
0236: case java.sql.Types.TIMESTAMP:
0237: java.sql.Timestamp ts = rs.getTimestamp(colIndex);
0238: if (ts == null)
0239: return "N/A";
0240: if (format == null)
0241: return DateTimeFormatUtil.getDateTimeFormat(null)
0242: .format(ts);
0243: else
0244: return new SimpleDateFormat(format).format(ts);
0245: case java.sql.Types.DATE:
0246: java.sql.Date date = rs.getDate(colIndex);
0247: if (date == null)
0248: return "N/A";
0249: if (format == null)
0250: return DateTimeFormatUtil.getDateFormat(null).format(
0251: date);
0252: else
0253: return new SimpleDateFormat(format).format(date);
0254:
0255: case java.sql.Types.BOOLEAN:
0256: return Boolean.toString(rs.getBoolean(colIndex));
0257:
0258: default:
0259: return rs.getString(colIndex);
0260: }
0261: }
0262:
0263: public static String getFormattedValue(ResultSet rs, int colIndex,
0264: int colType, String format, Locale locale)
0265: throws SQLException {
0266:
0267: switch (colType) {
0268: case java.sql.Types.INTEGER:
0269: case java.sql.Types.DECIMAL:
0270: if (format == null)
0271: return Integer.toString(rs.getInt(colIndex));
0272: else
0273: return new DecimalFormat(format).format(rs
0274: .getInt(colIndex));
0275: case java.sql.Types.FLOAT:
0276: if (format == null)
0277: return Float.toString(rs.getFloat(colIndex));
0278: else
0279: return new DecimalFormat(format).format(rs
0280: .getFloat(colIndex));
0281: case java.sql.Types.DOUBLE:
0282: if (format == null)
0283: return Double.toString(rs.getDouble(colIndex));
0284: else
0285: return new DecimalFormat(format).format(rs
0286: .getDouble(colIndex));
0287: case java.sql.Types.TIMESTAMP:
0288: if (format == null)
0289: return DateTimeFormatUtil.getDateTimeFormat(locale)
0290: .format(rs.getTimestamp(colIndex));
0291: else if (locale != null)
0292: return new SimpleDateFormat(format, locale).format(rs
0293: .getTimestamp(colIndex));
0294: else
0295: return new SimpleDateFormat(format).format(rs
0296: .getTimestamp(colIndex));
0297:
0298: case java.sql.Types.DATE:
0299: java.sql.Date date = rs.getDate(colIndex);
0300: if (date == null)
0301: return "N/A";
0302: if (format == null)
0303: return DateTimeFormatUtil.getDateFormat(locale).format(
0304: date);
0305: else if (locale != null)
0306: return new SimpleDateFormat(format, locale)
0307: .format(date);
0308: else
0309: return new SimpleDateFormat(format).format(date);
0310:
0311: case java.sql.Types.BOOLEAN:
0312: return Boolean.toString(rs.getBoolean(colIndex));
0313:
0314: default:
0315: return rs.getString(colIndex);
0316:
0317: }
0318: }
0319:
0320: /**
0321: *
0322: * @param dsName
0323: * @param sql
0324: * @param sqlTypes
0325: * @param formats
0326: * @return
0327: * @throws SQLException
0328: * @throws NamingException
0329: */
0330: public static String[] getRecord(String dsName, String sql,
0331: int[] sqlTypes, String[] formats) throws SQLException,
0332: NamingException {
0333: return getRecord(dsName, sql, sqlTypes, formats, null);
0334: }
0335:
0336: /**
0337: *
0338: * @param dsName
0339: * @param sql
0340: * @param sqlTypes
0341: * @param formats
0342: * @param params
0343: * @return
0344: * @throws SQLException
0345: * @throws NamingException
0346: */
0347: public static String[] getRecord(String dsName, String sql,
0348: int[] sqlTypes, String[] formats, Object[] params)
0349: throws SQLException, NamingException {
0350:
0351: Connection conn = null;
0352: try {
0353: conn = getConnection(dsName);
0354: return getRecordHelper(conn, sql, sqlTypes, formats, params);
0355: } finally {
0356: DBUtil.close(conn);
0357: }
0358: }
0359:
0360: /**
0361: *
0362: * @param dsName
0363: * @param sql
0364: * @param sqlTypes
0365: * @param formats
0366: * @param params
0367: * @return
0368: * @throws SQLException
0369: * @throws NamingException
0370: */
0371: public static String[] getRecord(DataSource ds, String sql,
0372: int[] sqlTypes, String[] formats, Object[] params)
0373: throws SQLException, NamingException {
0374:
0375: Connection conn = null;
0376: try {
0377: conn = ds.getConnection();
0378: return getRecordHelper(conn, sql, sqlTypes, formats, params);
0379: } finally {
0380: DBUtil.close(conn);
0381: }
0382: }
0383:
0384: private static String[] getRecordHelper(Connection conn,
0385: String sql, int[] sqlTypes, String[] formats,
0386: Object[] params) throws SQLException {
0387:
0388: PreparedStatement st = null;
0389: ResultSet results = null;
0390: try {
0391: st = conn.prepareStatement(sql);
0392:
0393: if (params != null) {
0394: for (int i = 0; i < params.length; i++) {
0395: st.setObject(i + 1, params[i]);
0396: }
0397: }
0398:
0399: results = st.executeQuery();
0400:
0401: if (!results.next()) {
0402: return null;
0403: }
0404: String[] resultStrings = new String[sqlTypes.length];
0405: int numFields = resultStrings.length;
0406: if (formats == null) {
0407: for (int i = 0; i < numFields; i++) {
0408: resultStrings[i] = getFormattedValue(results,
0409: i + 1, sqlTypes[i], null);
0410: }
0411: } else {
0412: for (int i = 0; i < numFields; i++) {
0413: resultStrings[i] = getFormattedValue(results,
0414: i + 1, sqlTypes[i], formats[i]);
0415: }
0416: }
0417: return resultStrings;
0418: } catch (java.sql.SQLException ex) {
0419: ex.printStackTrace();
0420: throw ex;
0421: } finally {
0422: close(results);
0423: close(st);
0424: }
0425: }
0426:
0427: /**
0428: * @param dsName
0429: * @param sql
0430: * @param sqlTypes
0431: * @param formats
0432: * @return
0433: * @throws SQLException
0434: * @throws NamingException
0435: */
0436: public static Vector getRecords(String dsName, String sql,
0437: int[] sqlTypes, String[] formats) throws SQLException,
0438: NamingException {
0439:
0440: Connection conn = null;
0441: try {
0442: conn = getConnection(dsName);
0443: return getRecordsHelper(conn, sql, sqlTypes, formats);
0444: } finally {
0445: DBUtil.close(conn);
0446: }
0447: }
0448:
0449: /**
0450: * @param dsName
0451: * @param sql
0452: * @param sqlTypes
0453: * @param formats
0454: * @return
0455: * @throws SQLException
0456: * @throws NamingException
0457: */
0458: public static Vector getRecords(DataSource ds, String sql,
0459: int[] sqlTypes, String[] formats) throws SQLException {
0460:
0461: Connection conn = null;
0462: try {
0463: conn = ds.getConnection();
0464: return getRecordsHelper(conn, sql, sqlTypes, formats);
0465: } finally {
0466: DBUtil.close(conn);
0467: }
0468: }
0469:
0470: @SuppressWarnings("unchecked")
0471: public static Vector getRecordsHelper(Connection conn, String sql,
0472: int[] sqlTypes, String[] formats) throws SQLException {
0473:
0474: PreparedStatement st = null;
0475: ResultSet results = null;
0476: try {
0477:
0478: st = conn.prepareStatement(sql);
0479: results = st.executeQuery();
0480:
0481: Vector records = new Vector();
0482: while (results.next()) {
0483: String[] resultStrings = new String[sqlTypes.length];
0484: int numFields = resultStrings.length;
0485: if (formats == null) {
0486: for (int i = 0; i < numFields; i++) {
0487: resultStrings[i] = getFormattedValue(results,
0488: i + 1, sqlTypes[i], null);
0489: }
0490: } else {
0491: for (int i = 0; i < numFields; i++) {
0492: resultStrings[i] = getFormattedValue(results,
0493: i + 1, sqlTypes[i], formats[i]);
0494: }
0495: }
0496: records.addElement(resultStrings);
0497: }
0498: return records;
0499: } finally {
0500: close(results);
0501: close(st);
0502: }
0503: }
0504:
0505: /**
0506: *
0507: * @param dsName
0508: * @param sql
0509: * @param params
0510: * @return
0511: * @throws SQLException
0512: * @throws NamingException
0513: */
0514: public static boolean execute(String dsName, String sql,
0515: Object[] params) throws SQLException, NamingException {
0516:
0517: Connection conn = null;
0518: PreparedStatement st = null;
0519:
0520: try {
0521: conn = getConnection(dsName);
0522: st = conn.prepareStatement(sql);
0523:
0524: if (params != null) {
0525:
0526: int numParams = params.length;
0527: for (int i = 0; i < numParams; i++) {
0528: st.setObject(i + 1, params[i]);
0529: }
0530: }
0531: boolean result = st.execute();
0532: return result;
0533: } catch (java.sql.SQLException ex) {
0534: ex.printStackTrace();
0535: throw ex;
0536: } finally {
0537: close(st);
0538: close(conn);
0539: }
0540: }
0541:
0542: /**
0543: *
0544: * @param dsName
0545: * @param sql
0546: * @param params
0547: * @return
0548: * @throws SQLException
0549: * @throws NamingException
0550: */
0551: public static boolean execute(DataSource ds, String sql,
0552: Object[] params) throws SQLException {
0553:
0554: Connection conn = null;
0555: PreparedStatement st = null;
0556:
0557: try {
0558: conn = ds.getConnection();
0559: st = conn.prepareStatement(sql);
0560:
0561: if (params != null) {
0562:
0563: int numParams = params.length;
0564: for (int i = 0; i < numParams; i++) {
0565: st.setObject(i + 1, params[i]);
0566: }
0567: }
0568: boolean result = st.execute();
0569: return result;
0570: } catch (java.sql.SQLException ex) {
0571: ex.printStackTrace();
0572: throw ex;
0573: } finally {
0574: close(st);
0575: close(conn);
0576: }
0577: }
0578:
0579: /**
0580: *
0581: * @param dsName
0582: * @param sql
0583: * @return
0584: * @throws SQLException
0585: * @throws NamingException
0586: */
0587: public static boolean execute(String dsName, String sql)
0588: throws SQLException, NamingException {
0589:
0590: Connection conn = null;
0591: PreparedStatement st = null;
0592:
0593: try {
0594: conn = getConnection(dsName);
0595: st = conn.prepareStatement(sql);
0596: boolean result = st.execute();
0597: return result;
0598: } catch (java.sql.SQLException ex) {
0599: ex.printStackTrace();
0600: throw ex;
0601: } finally {
0602: close(st);
0603: close(conn);
0604: }
0605: }
0606:
0607: /**
0608: *
0609: * @param ds
0610: * @param sql
0611: * @return
0612: * @throws SQLException
0613: * @throws NamingException
0614: */
0615: public static boolean execute(DataSource ds, String sql)
0616: throws SQLException {
0617:
0618: Connection conn = null;
0619: PreparedStatement st = null;
0620:
0621: try {
0622: conn = ds.getConnection();
0623: st = conn.prepareStatement(sql);
0624: boolean result = st.execute();
0625: return result;
0626: } catch (java.sql.SQLException ex) {
0627: ex.printStackTrace();
0628: throw ex;
0629: } finally {
0630: close(st);
0631: close(conn);
0632: }
0633: }
0634:
0635: /**
0636: * @param dsName
0637: * @param sql
0638: * @param sqlType
0639: * @param format
0640: * @return
0641: * @throws SQLException
0642: * @throws NamingException
0643: */
0644: public static String getField(String dsName, String sql,
0645: int sqlType, String format) throws SQLException,
0646: NamingException {
0647: return getField(dsName, sql, sqlType, format, null);
0648: }
0649:
0650: /**
0651: * @param ds
0652: * @param sql
0653: * @param sqlType
0654: * @param format
0655: * @return
0656: * @throws SQLException
0657: * @throws NamingException
0658: */
0659: public static String getField(DataSource ds, String sql,
0660: int sqlType, String format) throws SQLException {
0661: return getField(ds, sql, sqlType, format, null);
0662: }
0663:
0664: /**
0665: * Date and Timestamp are formatted based on Locale provided (optional)
0666: *
0667: * @param dsName
0668: * @param sql
0669: * @param sqlType
0670: * @param format
0671: * @param locale
0672: * @return
0673: * @throws SQLException
0674: * @throws NamingException
0675: */
0676: public static String getField(String dsName, String sql,
0677: int sqlType, String format, Locale locale)
0678: throws SQLException, NamingException {
0679:
0680: Connection conn = null;
0681: PreparedStatement st = null;
0682: ResultSet results = null;
0683: try {
0684: conn = getConnection(dsName);
0685: st = conn.prepareStatement(sql);
0686: results = st.executeQuery();
0687:
0688: if (!results.next()) {
0689: return null;
0690: }
0691:
0692: return getFormattedValue(results, 1, sqlType, format,
0693: locale);
0694:
0695: } catch (java.sql.SQLException ex) {
0696: ex.printStackTrace();
0697: throw ex;
0698: } finally {
0699: close(results);
0700: close(st);
0701: close(conn);
0702: }
0703: }
0704:
0705: /**
0706: * Date and Timestamp are formatted based on Locale provided (optional)
0707: *
0708: * @param ds
0709: * @param sql
0710: * @param sqlType
0711: * @param format
0712: * @param locale
0713: * @return
0714: * @throws SQLException
0715: * @throws NamingException
0716: */
0717: public static String getField(DataSource ds, String sql,
0718: int sqlType, String format, Locale locale)
0719: throws SQLException {
0720:
0721: Connection conn = null;
0722: PreparedStatement st = null;
0723: ResultSet results = null;
0724: try {
0725: conn = ds.getConnection();
0726: st = conn.prepareStatement(sql);
0727: results = st.executeQuery();
0728:
0729: if (!results.next()) {
0730: return null;
0731: }
0732:
0733: return getFormattedValue(results, 1, sqlType, format,
0734: locale);
0735:
0736: } catch (java.sql.SQLException ex) {
0737: ex.printStackTrace();
0738: throw ex;
0739: } finally {
0740: close(results);
0741: close(st);
0742: close(conn);
0743: }
0744: }
0745:
0746: public static java.sql.Date getDateField(String dsName, String sql)
0747: throws SQLException, NamingException {
0748:
0749: Connection conn = null;
0750: PreparedStatement st = null;
0751: ResultSet results = null;
0752: try {
0753: conn = getConnection(dsName);
0754: st = conn.prepareStatement(sql);
0755: results = st.executeQuery();
0756:
0757: if (!results.next()) {
0758: return null;
0759: }
0760:
0761: return results.getDate(1);
0762:
0763: } catch (java.sql.SQLException ex) {
0764: ex.printStackTrace();
0765: throw ex;
0766: } finally {
0767: close(results);
0768: close(st);
0769: close(conn);
0770: }
0771: }
0772:
0773: public static java.sql.Date getDateField(DataSource ds, String sql)
0774: throws SQLException {
0775:
0776: Connection conn = null;
0777: PreparedStatement st = null;
0778: ResultSet results = null;
0779: try {
0780: conn = ds.getConnection();
0781: st = conn.prepareStatement(sql);
0782: results = st.executeQuery();
0783:
0784: if (!results.next()) {
0785: return null;
0786: }
0787:
0788: return results.getDate(1);
0789:
0790: } catch (java.sql.SQLException ex) {
0791: ex.printStackTrace();
0792: throw ex;
0793: } finally {
0794: close(results);
0795: close(st);
0796: close(conn);
0797: }
0798: }
0799:
0800: /**
0801: * @param dsName
0802: * @param sql
0803: * @param sqlType
0804: * @param format
0805: * @return
0806: * @throws SQLException
0807: * @throws NamingException
0808: */
0809: public static Vector getFields(String dsName, String sql,
0810: int sqlType, String format) throws SQLException,
0811: NamingException {
0812:
0813: Connection conn = null;
0814: try {
0815: conn = getConnection(dsName);
0816: return getFields(conn, sql, sqlType, format);
0817: } finally {
0818: DBUtil.close(conn);
0819: }
0820: }
0821:
0822: /**
0823: * @param ds
0824: * @param sql
0825: * @param sqlType
0826: * @param format
0827: * @return
0828: * @throws SQLException
0829: * @throws NamingException
0830: */
0831: public static Vector getFields(DataSource ds, String sql,
0832: int sqlType, String format) throws SQLException {
0833:
0834: Connection conn = null;
0835: try {
0836: conn = ds.getConnection();
0837: return getFields(conn, sql, sqlType, format);
0838: } finally {
0839: DBUtil.close(conn);
0840: }
0841: }
0842:
0843: @SuppressWarnings("unchecked")
0844: private static Vector getFields(Connection conn, String sql,
0845: int sqlType, String format) throws SQLException {
0846:
0847: PreparedStatement st = null;
0848: ResultSet results = null;
0849:
0850: try {
0851:
0852: st = conn.prepareStatement(sql);
0853: results = st.executeQuery();
0854: Vector fieldVec = new Vector();
0855: while (results.next()) {
0856: fieldVec.addElement(getFormattedValue(results, 1,
0857: sqlType, format));
0858: }
0859:
0860: return fieldVec;
0861:
0862: } catch (java.sql.SQLException ex) {
0863: ex.printStackTrace();
0864: throw ex;
0865: } finally {
0866: close(results);
0867: close(st);
0868: }
0869: }
0870:
0871: /**
0872: * @param dsName
0873: * @param sql
0874: * @return
0875: * @throws SQLException
0876: * @throws NamingException
0877: */
0878: public static boolean checkRelation(String dsName, String sql)
0879: throws SQLException, NamingException {
0880:
0881: Connection conn = null;
0882: PreparedStatement st = null;
0883: ResultSet results = null;
0884:
0885: try {
0886: conn = getConnection(dsName);
0887: st = conn.prepareStatement(sql);
0888: results = st.executeQuery();
0889:
0890: if (results.next()) {
0891: return true;
0892: } else {
0893: return false;
0894: }
0895: } catch (java.sql.SQLException ex) {
0896: ex.printStackTrace();
0897: throw ex;
0898: } finally {
0899: close(results);
0900: close(st);
0901: close(conn);
0902: }
0903: }
0904:
0905: /**
0906: * @param ds
0907: * @param sql
0908: * @return
0909: * @throws SQLException
0910: * @throws NamingException
0911: */
0912: public static boolean checkRelation(DataSource ds, String sql)
0913: throws SQLException {
0914:
0915: Connection conn = null;
0916: PreparedStatement st = null;
0917: ResultSet results = null;
0918:
0919: try {
0920: conn = ds.getConnection();
0921: st = conn.prepareStatement(sql);
0922: results = st.executeQuery();
0923:
0924: if (results.next()) {
0925: return true;
0926: } else {
0927: return false;
0928: }
0929: } catch (java.sql.SQLException ex) {
0930: ex.printStackTrace();
0931: throw ex;
0932: } finally {
0933: close(results);
0934: close(st);
0935: close(conn);
0936: }
0937: }
0938:
0939: /**
0940: * Assumes that the specified SQL query returns a number. This will happen
0941: * if you have statements like "SELECT COUNT(*) ...". It extracts and
0942: * returns the number.
0943: *
0944: * @param dsName Data source name.
0945: * @param sql SQL query statement that return int as the result, e.g.
0946: * SELECT SUM(column_name)....
0947: * @param Optional subtitutes parameter used in prepare statement.
0948: */
0949: public static int getCount(String dsName, String sql,
0950: Object[] params) throws SQLException, NamingException {
0951:
0952: Connection con = null;
0953: PreparedStatement st = null;
0954: ResultSet set = null;
0955: int result = -1;
0956:
0957: try {
0958:
0959: con = DBUtil.getConnection(dsName);
0960: st = con.prepareStatement(sql);
0961: if (params != null)
0962: for (int i = 0; i < params.length; i++) {
0963: st.setObject(i + 1, params[i]);
0964: }
0965: set = st.executeQuery();
0966: if (!set.next())
0967: throw new SQLException(
0968: "SQL query did not return any results.");
0969: result = set.getInt(1);
0970:
0971: } finally {
0972:
0973: close(set);
0974: close(st);
0975: close(con);
0976: }
0977: return result;
0978:
0979: }
0980:
0981: /**
0982: * Assumes that the specified SQL query returns a number. This will happen
0983: * if you have statements like "SELECT COUNT(*) ...". It extracts and
0984: * returns the number.
0985: *
0986: * @param dsName Data source name.
0987: * @param sql SQL query statement that return int as the result, e.g.
0988: * SELECT SUM(column_name)....
0989: * @param Optional subtitutes parameter used in prepare statement.
0990: */
0991: public static int getCount(DataSource ds, String sql,
0992: Object[] params) throws SQLException {
0993:
0994: Connection con = null;
0995: PreparedStatement st = null;
0996: ResultSet set = null;
0997: int result = -1;
0998:
0999: try {
1000:
1001: con = ds.getConnection();
1002: st = con.prepareStatement(sql);
1003: if (params != null)
1004: for (int i = 0; i < params.length; i++) {
1005: st.setObject(i + 1, params[i]);
1006: }
1007: set = st.executeQuery();
1008: if (!set.next())
1009: throw new SQLException(
1010: "SQL query did not return any results.");
1011: result = set.getInt(1);
1012:
1013: } finally {
1014:
1015: close(set);
1016: close(st);
1017: close(con);
1018: }
1019: return result;
1020:
1021: }
1022:
1023: public static String getField(String dsName, String sql)
1024: throws SQLException, NamingException {
1025:
1026: Connection conn = null;
1027: PreparedStatement st = null;
1028: ResultSet results = null;
1029: try {
1030: conn = getConnection(dsName);
1031: st = conn.prepareStatement(sql);
1032: results = st.executeQuery();
1033:
1034: if (!results.next()) {
1035: return null;
1036: }
1037:
1038: return results.getString(1);
1039:
1040: } catch (java.sql.SQLException ex) {
1041: ex.printStackTrace();
1042: throw ex;
1043: } finally {
1044: close(results);
1045: close(st);
1046: close(conn);
1047: }
1048: }
1049:
1050: public static String getField(DataSource ds, String sql)
1051: throws SQLException {
1052:
1053: Connection conn = null;
1054: PreparedStatement st = null;
1055: ResultSet results = null;
1056: try {
1057: conn = ds.getConnection();
1058: st = conn.prepareStatement(sql);
1059: results = st.executeQuery();
1060:
1061: if (!results.next()) {
1062: return null;
1063: }
1064:
1065: return results.getString(1);
1066:
1067: } catch (java.sql.SQLException ex) {
1068: ex.printStackTrace();
1069: throw ex;
1070: } finally {
1071: close(results);
1072: close(st);
1073: close(conn);
1074: }
1075: }
1076:
1077: public static String[] getRecord(String dsName, String sql)
1078: throws SQLException, NamingException {
1079:
1080: Connection conn = null;
1081: PreparedStatement st = null;
1082: ResultSet results = null;
1083: try {
1084: conn = getConnection(dsName);
1085: st = conn.prepareStatement(sql);
1086:
1087: results = st.executeQuery();
1088:
1089: if (!results.next()) {
1090: return null;
1091: }
1092: int numFields = results.getMetaData().getColumnCount();
1093: String[] resultStrings = new String[numFields];
1094: for (int i = 0; i < numFields; i++) {
1095: resultStrings[i] = results.getString(i + 1);
1096: }
1097: return resultStrings;
1098: } catch (java.sql.SQLException ex) {
1099: ex.printStackTrace();
1100: throw ex;
1101: } finally {
1102: close(results);
1103: close(st);
1104: close(conn);
1105: }
1106: }
1107:
1108: public static String[] getRecord(DataSource ds, String sql)
1109: throws SQLException {
1110:
1111: Connection conn = null;
1112: PreparedStatement st = null;
1113: ResultSet results = null;
1114: try {
1115: conn = ds.getConnection();
1116: st = conn.prepareStatement(sql);
1117:
1118: results = st.executeQuery();
1119:
1120: if (!results.next()) {
1121: return null;
1122: }
1123: int numFields = results.getMetaData().getColumnCount();
1124: String[] resultStrings = new String[numFields];
1125: for (int i = 0; i < numFields; i++) {
1126: resultStrings[i] = results.getString(i + 1);
1127: }
1128: return resultStrings;
1129: } catch (java.sql.SQLException ex) {
1130: ex.printStackTrace();
1131: throw ex;
1132: } finally {
1133: close(results);
1134: close(st);
1135: close(conn);
1136: }
1137: }
1138:
1139: @SuppressWarnings("unchecked")
1140: public static Vector getRecords(String dsName, String sql)
1141: throws SQLException, NamingException {
1142:
1143: Connection conn = null;
1144: PreparedStatement st = null;
1145: ResultSet results = null;
1146: try {
1147: conn = getConnection(dsName);
1148: st = conn.prepareStatement(sql);
1149: results = st.executeQuery();
1150:
1151: Vector records = new Vector();
1152: int numFields = results.getMetaData().getColumnCount();
1153: while (results.next()) {
1154: String[] resultStrings = new String[numFields];
1155: for (int i = 0; i < numFields; i++) {
1156: resultStrings[i] = results.getString(i + 1);
1157: }
1158: records.addElement(resultStrings);
1159: }
1160: return records;
1161: } finally {
1162: close(results);
1163: close(st);
1164: close(conn);
1165: }
1166: }
1167:
1168: @SuppressWarnings("unchecked")
1169: public static Vector getRecords(DataSource ds, String sql)
1170: throws SQLException {
1171:
1172: Connection conn = null;
1173: PreparedStatement st = null;
1174: ResultSet results = null;
1175: try {
1176: conn = ds.getConnection();
1177: st = conn.prepareStatement(sql);
1178: results = st.executeQuery();
1179:
1180: Vector records = new Vector();
1181: int numFields = results.getMetaData().getColumnCount();
1182: while (results.next()) {
1183: String[] resultStrings = new String[numFields];
1184: for (int i = 0; i < numFields; i++) {
1185: resultStrings[i] = results.getString(i + 1);
1186: }
1187: records.addElement(resultStrings);
1188: }
1189: return records;
1190: } finally {
1191: close(results);
1192: close(st);
1193: close(conn);
1194: }
1195: }
1196:
1197: @SuppressWarnings("unchecked")
1198: public static HashMap constructEnumMap(DataSource ds, String sql)
1199: throws SQLException {
1200:
1201: Connection conn = null;
1202: PreparedStatement st = null;
1203: ResultSet results = null;
1204:
1205: try {
1206: conn = ds.getConnection();
1207: st = conn.prepareStatement(sql);
1208: results = st.executeQuery();
1209: HashMap map = new HashMap();
1210: while (results.next()) {
1211: map.put(results.getString(1), results.getString(2));
1212: }
1213:
1214: return map;
1215:
1216: } finally {
1217: close(results);
1218: close(st);
1219: close(conn);
1220: }
1221: }
1222:
1223: public static void close(Connection con) {
1224: if (con != null) {
1225: try {
1226: con.close();
1227: } catch (SQLException e) {
1228: }
1229: }
1230: }
1231:
1232: public static void close(ResultSet rs) {
1233: if (rs != null) {
1234: try {
1235: rs.close();
1236: } catch (SQLException e) {
1237: }
1238: }
1239: }
1240:
1241: public static void close(java.sql.Statement st) {
1242: if (st != null) {
1243: try {
1244: st.close();
1245: } catch (SQLException e) {
1246: }
1247: }
1248: }
1249:
1250: public static String getDataSourceFlavor(String dsName) {
1251: if (StringUtil.isNullOrEmpty(dsName))
1252: return (String) dbuDSFlavorsTable.get(Defaults
1253: .getDataSourceName());
1254: return (String) dbuDSFlavorsTable.get(dsName);
1255: }
1256:
1257: public static Connection getJDBCConnection(String dsName)
1258: throws SQLException, NamingException {
1259: BasicDataSource ds = null;
1260: try {
1261: ds = (BasicDataSource) DBUtil.getDataSource(dsName);
1262: if (ds == null)
1263: return null;
1264:
1265: // setup jdbc connection
1266: Class.forName(ds.getDriverClassName());
1267: return DriverManager.getConnection(ds.getUrl(), ds
1268: .getUsername(), ds.getPassword());
1269: } catch (ClassCastException e) {
1270: throw new SQLException(
1271: "Cannot cast the object to BasicDataSource. It is possible that you are using app server configured datasource.");
1272: } catch (ClassNotFoundException e) {
1273: throw new SQLException("Driver class not found:"
1274: + ds.getDriverClassName());
1275: }
1276: }
1277:
1278: }
|