001: /*
002: Copyright (C) 2003-2006 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.dataobjs;
034:
035: import java.sql.Connection;
036: import java.sql.Statement;
037: import java.sql.ResultSet;
038: import java.sql.SQLException;
039: import java.sql.Timestamp;
040:
041: import java.util.Date;
042:
043: import java.math.BigDecimal;
044:
045: import com.knowgate.debug.DebugFile;
046:
047: /**
048: * A wrapper for some common SQL command sequences
049: * @author Sergio Montoro Ten
050: * @version 3.0
051: */
052: public class DBCommand {
053:
054: private DBCommand() {
055: }
056:
057: // ---------------------------------------------------------------------------
058:
059: /**
060: * Execute a SQL query and get an Integer value as result
061: * @param oCon Connection Open JDBC database connection
062: * @param sSQL String Command to be executed
063: * @return Integer Value of the first column selected by the query or <b>null</b>
064: * if no row was found or selected row was <b>null</b>
065: * @throws SQLException
066: * @throws NumberFormatException
067: * @throws ClassCastException
068: */
069: public static Integer queryInt(Connection oCon, String sSQL)
070: throws SQLException, NumberFormatException,
071: ClassCastException {
072:
073: Statement oStm = null;
074: ResultSet oRst = null;
075: Object oObj = null;
076: Integer oInt;
077:
078: if (DebugFile.trace) {
079: DebugFile.writeln("Begin DBCommand.queryInt(" + sSQL + ")");
080: DebugFile.incIdent();
081: }
082:
083: try {
084: oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY,
085: ResultSet.CONCUR_READ_ONLY);
086: oRst = oStm.executeQuery(sSQL);
087: if (oRst.next()) {
088: oObj = oRst.getObject(1);
089: if (oRst.wasNull())
090: oObj = null;
091: }
092: oRst.close();
093: oRst = null;
094: oStm.close();
095: oStm = null;
096: } catch (Exception xcpt) {
097: if (DebugFile.trace) {
098: DebugFile.writeln(xcpt.getClass().getName() + " "
099: + xcpt.getMessage());
100: DebugFile.decIdent();
101: }
102: if (oRst != null) {
103: try {
104: oRst.close();
105: } catch (Exception ignore) {
106: }
107: }
108: if (oStm != null) {
109: try {
110: oStm.close();
111: } catch (Exception ignore) {
112: }
113: }
114: throw new SQLException(xcpt.getMessage());
115: }
116:
117: if (null == oObj)
118: oInt = null;
119: else
120: oInt = new Integer(oObj.toString());
121:
122: if (DebugFile.trace) {
123: DebugFile.decIdent();
124: if (null == oInt)
125: DebugFile.writeln("End DBCommand.queryInt() : null");
126: else
127: DebugFile.writeln("End DBCommand.queryInt() : "
128: + oInt.toString());
129: }
130:
131: return oInt;
132: } // queryInt
133:
134: // ---------------------------------------------------------------------------
135:
136: /**
137: * Execute a SQL query and get a String value as result
138: * @param oCon Connection Open JDBC database connection
139: * @param sSQL String Command to be executed
140: * @return String Value of the first column selected by the query or <b>null</b>
141: * if no row was found or selected row was <b>null</b>
142: * @throws SQLException
143: * @throws NumberFormatException
144: * @throws ClassCastException
145: */
146:
147: public static String queryStr(Connection oCon, String sSQL)
148: throws SQLException {
149:
150: Statement oStm = null;
151: ResultSet oRst = null;
152: String sStr = null;
153:
154: if (DebugFile.trace) {
155: DebugFile.writeln("Begin DBCommand.queryStr(" + sSQL + ")");
156: DebugFile.incIdent();
157: }
158:
159: try {
160: oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY,
161: ResultSet.CONCUR_READ_ONLY);
162: oRst = oStm.executeQuery(sSQL);
163: if (oRst.next()) {
164: sStr = oRst.getString(1);
165: if (oRst.wasNull())
166: sStr = null;
167: }
168: oRst.close();
169: oRst = null;
170: oStm.close();
171: oStm = null;
172: } catch (Exception xcpt) {
173: if (DebugFile.trace) {
174: DebugFile.writeln(xcpt.getClass().getName() + " "
175: + xcpt.getMessage());
176: DebugFile.decIdent();
177: }
178: if (oRst != null) {
179: try {
180: oRst.close();
181: } catch (Exception ignore) {
182: }
183: }
184: if (oStm != null) {
185: try {
186: oStm.close();
187: } catch (Exception ignore) {
188: }
189: }
190: throw new SQLException(xcpt.getMessage());
191: }
192:
193: if (DebugFile.trace) {
194: DebugFile.decIdent();
195: DebugFile.writeln("End DBCommand.queryInt() : " + sStr);
196: }
197:
198: return sStr;
199: } // queryStr
200:
201: // ---------------------------------------------------------------------------
202:
203: /**
204: * Execute a SQL query and get a BigDecimal value as result
205: * @param oCon Connection Open JDBC database connection
206: * @param sSQL String Command to be executed
207: * @return String Value of the first column selected by the query or <b>null</b>
208: * if no row was found or selected row was <b>null</b>
209: * @throws SQLException
210: * @throws NumberFormatException
211: * @throws ClassCastException
212: */
213:
214: public static BigDecimal queryBigDecimal(Connection oCon,
215: String sSQL) throws SQLException {
216:
217: Statement oStm = null;
218: ResultSet oRst = null;
219: BigDecimal oDec = null;
220:
221: if (DebugFile.trace) {
222: DebugFile.writeln("Begin DBCommand.queryBigDecimal(" + sSQL
223: + ")");
224: DebugFile.incIdent();
225: }
226:
227: try {
228: oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY,
229: ResultSet.CONCUR_READ_ONLY);
230: oRst = oStm.executeQuery(sSQL);
231: if (oRst.next()) {
232: oDec = oRst.getBigDecimal(1);
233: if (oRst.wasNull())
234: oDec = null;
235: }
236: oRst.close();
237: oRst = null;
238: oStm.close();
239: oStm = null;
240: } catch (Exception xcpt) {
241: if (DebugFile.trace) {
242: DebugFile.writeln(xcpt.getClass().getName() + " "
243: + xcpt.getMessage());
244: DebugFile.decIdent();
245: }
246: if (oRst != null) {
247: try {
248: oRst.close();
249: } catch (Exception ignore) {
250: }
251: }
252: if (oStm != null) {
253: try {
254: oStm.close();
255: } catch (Exception ignore) {
256: }
257: }
258: throw new SQLException(xcpt.getMessage());
259: }
260:
261: if (DebugFile.trace) {
262: DebugFile.decIdent();
263: if (null == oDec)
264: DebugFile
265: .writeln("End DBCommand.queryBigDecimal() : null ");
266: else
267: DebugFile.writeln("End DBCommand.queryBigDecimal() : "
268: + oDec.toString());
269: }
270: return oDec;
271: } // queryBigDecimal
272:
273: // ---------------------------------------------------------------------------
274:
275: /**
276: * Execute a SQL query and get a BigDecimal value as result
277: * @param oCon Connection Open JDBC database connection
278: * @param sSQL String Command to be executed
279: * @return String Value of the first column selected by the query or <b>null</b>
280: * if no row was found or selected row was <b>null</b>
281: * @throws SQLException
282: * @throws NumberFormatException
283: * @throws ClassCastException
284: */
285:
286: public static Date queryDateTime(Connection oCon, String sSQL)
287: throws SQLException {
288:
289: Statement oStm = null;
290: ResultSet oRst = null;
291: Timestamp oTs = null;
292: ;
293: Date oDt = null;
294:
295: if (DebugFile.trace) {
296: DebugFile.writeln("Begin DBCommand.queryDateTime(" + sSQL
297: + ")");
298: DebugFile.incIdent();
299: }
300:
301: try {
302: oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY,
303: ResultSet.CONCUR_READ_ONLY);
304: oRst = oStm.executeQuery(sSQL);
305: if (oRst.next()) {
306: oTs = oRst.getTimestamp(1);
307: if (oRst.wasNull())
308: oDt = null;
309: else
310: oDt = new Date(oTs.getTime());
311: }
312: oRst.close();
313: oRst = null;
314: oStm.close();
315: oStm = null;
316: } catch (Exception xcpt) {
317: if (DebugFile.trace) {
318: DebugFile.writeln(xcpt.getClass().getName() + " "
319: + xcpt.getMessage());
320: DebugFile.decIdent();
321: }
322: if (oRst != null) {
323: try {
324: oRst.close();
325: } catch (Exception ignore) {
326: }
327: }
328: if (oStm != null) {
329: try {
330: oStm.close();
331: } catch (Exception ignore) {
332: }
333: }
334: throw new SQLException(xcpt.getMessage());
335: }
336:
337: if (DebugFile.trace) {
338: DebugFile.decIdent();
339: if (null == oDt)
340: DebugFile
341: .writeln("End DBCommand.queryDateTime() : null ");
342: else
343: DebugFile.writeln("End DBCommand.queryDateTime() : "
344: + oDt.toString());
345: }
346: return oDt;
347: } // queryDateTime
348:
349: // ---------------------------------------------------------------------------
350:
351: /**
352: * Execute an INSERT or UPDATE statement
353: * @param oCon Connection Open JDBC database connection
354: * @param sSQL String Command to be executed
355: * @return int Count of affected rows
356: * @throws SQLException
357: */
358: public static int executeUpdate(Connection oCon, String sSQL)
359: throws SQLException {
360: if (DebugFile.trace) {
361: DebugFile.writeln("Begin DBCommand.executeUpdate(" + sSQL
362: + ")");
363: DebugFile.incIdent();
364: }
365:
366: Statement oStm = null;
367: int iAffected = 0;
368: try {
369: oStm = oCon.createStatement();
370: iAffected = oStm.executeUpdate(sSQL);
371: oStm.close();
372: oStm = null;
373: } catch (SQLException sqle) {
374: if (DebugFile.trace) {
375: DebugFile.writeln("SQLException " + sqle.getMessage());
376: DebugFile.decIdent();
377: }
378: if (null != oStm) {
379: try {
380: oStm.close();
381: } catch (Exception ignore) {
382: }
383: }
384: }
385:
386: if (DebugFile.trace) {
387: DebugFile.decIdent();
388: DebugFile.writeln("End DBCommand.executeUpdate() : "
389: + String.valueOf(iAffected));
390: }
391:
392: return iAffected;
393: } // executeUpdate
394: }
|