KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > knowgate > dataobjs > DBCommand


1 /*
2   Copyright (C) 2003-2006 Know Gate S.L. All rights reserved.
3                            C/Oņa, 107 1š2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */
32
33 package com.knowgate.dataobjs;
34
35 import java.sql.Connection JavaDoc;
36 import java.sql.Statement JavaDoc;
37 import java.sql.ResultSet JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.Timestamp JavaDoc;
40
41 import java.util.Date JavaDoc;
42
43 import java.math.BigDecimal JavaDoc;
44
45 import com.knowgate.debug.DebugFile;
46
47 /**
48  * A wrapper for some common SQL command sequences
49  * @author Sergio Montoro Ten
50  * @version 3.0
51  */
52 public class DBCommand {
53
54   private DBCommand() { }
55
56   // ---------------------------------------------------------------------------
57
58   /**
59    * Execute a SQL query and get an Integer value as result
60    * @param oCon Connection Open JDBC database connection
61    * @param sSQL String Command to be executed
62    * @return Integer Value of the first column selected by the query or <b>null</b>
63    * if no row was found or selected row was <b>null</b>
64    * @throws SQLException
65    * @throws NumberFormatException
66    * @throws ClassCastException
67    */
68   public static Integer JavaDoc queryInt(Connection JavaDoc oCon, String JavaDoc sSQL)
69     throws SQLException JavaDoc,NumberFormatException JavaDoc,ClassCastException JavaDoc {
70
71     Statement JavaDoc oStm = null;
72     ResultSet JavaDoc oRst = null;
73     Object JavaDoc oObj = null;
74     Integer JavaDoc oInt;
75
76     if (DebugFile.trace) {
77       DebugFile.writeln("Begin DBCommand.queryInt("+sSQL+")");
78       DebugFile.incIdent();
79     }
80
81     try {
82       oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
83       oRst = oStm.executeQuery(sSQL);
84       if (oRst.next()) {
85         oObj = oRst.getObject(1);
86         if (oRst.wasNull()) oObj = null;
87       }
88       oRst.close();
89       oRst=null;
90       oStm.close();
91       oStm=null;
92     } catch (Exception JavaDoc xcpt) {
93       if (DebugFile.trace) {
94         DebugFile.writeln(xcpt.getClass().getName()+" "+xcpt.getMessage());
95         DebugFile.decIdent();
96       }
97       if (oRst!=null) { try {oRst.close(); } catch (Exception JavaDoc ignore) {} }
98       if (oStm!=null) { try {oStm.close(); } catch (Exception JavaDoc ignore) {} }
99       throw new SQLException JavaDoc(xcpt.getMessage());
100     }
101
102     if (null==oObj)
103       oInt = null;
104     else
105       oInt = new Integer JavaDoc(oObj.toString());
106
107     if (DebugFile.trace) {
108       DebugFile.decIdent();
109       if (null==oInt)
110         DebugFile.writeln("End DBCommand.queryInt() : null");
111       else
112         DebugFile.writeln("End DBCommand.queryInt() : "+oInt.toString());
113     }
114
115     return oInt;
116   } // queryInt
117
118   // ---------------------------------------------------------------------------
119
120   /**
121    * Execute a SQL query and get a String value as result
122    * @param oCon Connection Open JDBC database connection
123    * @param sSQL String Command to be executed
124    * @return String Value of the first column selected by the query or <b>null</b>
125    * if no row was found or selected row was <b>null</b>
126    * @throws SQLException
127    * @throws NumberFormatException
128    * @throws ClassCastException
129    */
130
131   public static String JavaDoc queryStr(Connection JavaDoc oCon, String JavaDoc sSQL)
132     throws SQLException JavaDoc {
133
134     Statement JavaDoc oStm = null;
135     ResultSet JavaDoc oRst = null;
136     String JavaDoc sStr = null;
137
138     if (DebugFile.trace) {
139       DebugFile.writeln("Begin DBCommand.queryStr("+sSQL+")");
140       DebugFile.incIdent();
141     }
142
143     try {
144       oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
145       oRst = oStm.executeQuery(sSQL);
146       if (oRst.next()) {
147         sStr = oRst.getString(1);
148         if (oRst.wasNull()) sStr = null;
149       }
150       oRst.close();
151       oRst=null;
152       oStm.close();
153       oStm=null;
154     } catch (Exception JavaDoc xcpt) {
155       if (DebugFile.trace) {
156         DebugFile.writeln(xcpt.getClass().getName()+" "+xcpt.getMessage());
157         DebugFile.decIdent();
158       }
159       if (oRst!=null) { try {oRst.close(); } catch (Exception JavaDoc ignore) {} }
160       if (oStm!=null) { try {oStm.close(); } catch (Exception JavaDoc ignore) {} }
161       throw new SQLException JavaDoc(xcpt.getMessage());
162     }
163
164     if (DebugFile.trace) {
165       DebugFile.decIdent();
166       DebugFile.writeln("End DBCommand.queryInt() : "+sStr);
167     }
168
169     return sStr;
170   } // queryStr
171
172   // ---------------------------------------------------------------------------
173
174   /**
175    * Execute a SQL query and get a BigDecimal value as result
176    * @param oCon Connection Open JDBC database connection
177    * @param sSQL String Command to be executed
178    * @return String Value of the first column selected by the query or <b>null</b>
179    * if no row was found or selected row was <b>null</b>
180    * @throws SQLException
181    * @throws NumberFormatException
182    * @throws ClassCastException
183    */
184
185   public static BigDecimal JavaDoc queryBigDecimal(Connection JavaDoc oCon, String JavaDoc sSQL)
186     throws SQLException JavaDoc {
187
188     Statement JavaDoc oStm = null;
189     ResultSet JavaDoc oRst = null;
190     BigDecimal JavaDoc oDec = null;
191
192     if (DebugFile.trace) {
193       DebugFile.writeln("Begin DBCommand.queryBigDecimal("+sSQL+")");
194       DebugFile.incIdent();
195     }
196
197     try {
198       oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
199       oRst = oStm.executeQuery(sSQL);
200       if (oRst.next()) {
201         oDec = oRst.getBigDecimal(1);
202         if (oRst.wasNull()) oDec = null;
203       }
204       oRst.close();
205       oRst=null;
206       oStm.close();
207       oStm=null;
208     } catch (Exception JavaDoc xcpt) {
209       if (DebugFile.trace) {
210         DebugFile.writeln(xcpt.getClass().getName()+" "+xcpt.getMessage());
211         DebugFile.decIdent();
212       }
213       if (oRst!=null) { try {oRst.close(); } catch (Exception JavaDoc ignore) {} }
214       if (oStm!=null) { try {oStm.close(); } catch (Exception JavaDoc ignore) {} }
215       throw new SQLException JavaDoc(xcpt.getMessage());
216     }
217
218     if (DebugFile.trace) {
219       DebugFile.decIdent();
220       if (null==oDec)
221         DebugFile.writeln("End DBCommand.queryBigDecimal() : null ");
222       else
223         DebugFile.writeln("End DBCommand.queryBigDecimal() : "+oDec.toString());
224     }
225     return oDec;
226   } // queryBigDecimal
227
228   // ---------------------------------------------------------------------------
229
230   /**
231    * Execute a SQL query and get a BigDecimal value as result
232    * @param oCon Connection Open JDBC database connection
233    * @param sSQL String Command to be executed
234    * @return String Value of the first column selected by the query or <b>null</b>
235    * if no row was found or selected row was <b>null</b>
236    * @throws SQLException
237    * @throws NumberFormatException
238    * @throws ClassCastException
239    */
240
241   public static Date JavaDoc queryDateTime(Connection JavaDoc oCon, String JavaDoc sSQL)
242     throws SQLException JavaDoc {
243
244     Statement JavaDoc oStm = null;
245     ResultSet JavaDoc oRst = null;
246     Timestamp JavaDoc oTs = null;;
247     Date JavaDoc oDt = null;
248
249     if (DebugFile.trace) {
250       DebugFile.writeln("Begin DBCommand.queryDateTime("+sSQL+")");
251       DebugFile.incIdent();
252     }
253
254     try {
255       oStm = oCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
256       oRst = oStm.executeQuery(sSQL);
257       if (oRst.next()) {
258         oTs = oRst.getTimestamp(1);
259         if (oRst.wasNull())
260           oDt = null;
261         else
262           oDt = new Date JavaDoc(oTs.getTime());
263       }
264       oRst.close();
265       oRst=null;
266       oStm.close();
267       oStm=null;
268     } catch (Exception JavaDoc xcpt) {
269       if (DebugFile.trace) {
270         DebugFile.writeln(xcpt.getClass().getName()+" "+xcpt.getMessage());
271         DebugFile.decIdent();
272       }
273       if (oRst!=null) { try {oRst.close(); } catch (Exception JavaDoc ignore) {} }
274       if (oStm!=null) { try {oStm.close(); } catch (Exception JavaDoc ignore) {} }
275       throw new SQLException JavaDoc(xcpt.getMessage());
276     }
277
278     if (DebugFile.trace) {
279       DebugFile.decIdent();
280       if (null==oDt)
281         DebugFile.writeln("End DBCommand.queryDateTime() : null ");
282       else
283         DebugFile.writeln("End DBCommand.queryDateTime() : "+oDt.toString());
284     }
285     return oDt;
286   } // queryDateTime
287
288   // ---------------------------------------------------------------------------
289
290   /**
291    * Execute an INSERT or UPDATE statement
292    * @param oCon Connection Open JDBC database connection
293    * @param sSQL String Command to be executed
294    * @return int Count of affected rows
295    * @throws SQLException
296    */
297   public static int executeUpdate(Connection JavaDoc oCon, String JavaDoc sSQL) throws SQLException JavaDoc {
298     if (DebugFile.trace) {
299       DebugFile.writeln("Begin DBCommand.executeUpdate("+sSQL+")");
300       DebugFile.incIdent();
301     }
302
303     Statement JavaDoc oStm = null;
304     int iAffected = 0;
305     try {
306       oStm = oCon.createStatement();
307       iAffected = oStm.executeUpdate(sSQL);
308       oStm.close();
309       oStm=null;
310     } catch (SQLException JavaDoc sqle) {
311       if (DebugFile.trace) {
312         DebugFile.writeln("SQLException "+sqle.getMessage());
313         DebugFile.decIdent();
314       }
315       if (null!=oStm) { try { oStm.close(); } catch (Exception JavaDoc ignore) {} }
316     }
317
318     if (DebugFile.trace) {
319       DebugFile.decIdent();
320       DebugFile.writeln("End DBCommand.executeUpdate() : " + String.valueOf(iAffected));
321     }
322
323     return iAffected;
324   } // executeUpdate
325 }
326
Popular Tags