Source code: info/crossbar/util/db/DbUtils.java
1 /*
2 * @(#)DbUtils.java $Revision: 1.2 $ $Date: 2003/06/04 04:55:32 $
3 *
4 * Copyright 2002 by Daniel Kehoe <kehoe@fortuity.com>
5 * All Rights Reserved
6 *
7 * Redistribution and use in source and binary forms, with or without
8 * modification, are permitted provided that the following conditions
9 * are met:
10 * 1. Redistributions of source code must retain the above copyright
11 * notice, this list of conditions and the following disclaimer.
12 * 2. Redistributions in binary form must reproduce the above copyright
13 * notice, this list of conditions and the following disclaimer in the
14 * documentation and/or other materials provided with the distribution.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
17 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
18 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
19 * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
20 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
21 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
22 * OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
23 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
24 * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
25 * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
26 * SUCH DAMAGE.
27 */
28 package info.crossbar.util.db;
29
30 import java.util.*;
31 import java.util.logging.Logger;
32
33 import java.io.*;
34
35 import javax.servlet.ServletException;
36
37 import java.sql.Connection;
38 import java.sql.Statement;
39 import java.sql.ResultSet;
40 import java.sql.SQLException;
41
42 import javax.sql.DataSource;
43
44 import javax.naming.Context;
45 import javax.naming.InitialContext;
46 import javax.naming.NameClassPair;
47 import javax.naming.NamingEnumeration;
48 import javax.naming.NamingException;
49
50 import info.crossbar.util.db.Pool;
51
52
53 /**
54 * DbUtils class for use by <a href="http://www.crossbar.info/">Crossbar</a>
55 *
56 * @author Daniel Kehoe, <a href="http://www.fortuity.com/">Fortuity Consulting</a>
57 * @version <a href="http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/crossbar/Crossbar/src/java/info/crossbar/util/db/DbUtils.java">View source, revision history</a>
58 * $Revision: 1.2 $ $Date: 2003/06/04 04:55:32 $
59 * <p>
60 * DESCRIPTION:
61 * This class is a database access object or a "doorway" to a database. This class does
62 * not decouple or isolate the database schema from the web application. Objects that use the
63 * DbUtils object to interact with the database need to know about the database's tables.
64 * Consequently, this DbUtils object is useful for manipulation of database tables that are
65 * tightly coupled to the application, such as tables that define the structure of the
66 * application. If you are building an application that should be decoupled from possible changes
67 * to an underlying database schema, you should not use this object to access the database. You
68 * should build Models that interact with business data through EJB or another object-relational
69 * mapping technology.
70 */
71 public class DbUtils {
72
73 /**
74 * Set up logging.
75 */
76 private static Logger log = Logger.getLogger(DbUtils.class.getName());
77
78
79 /**
80 * Constructor.
81 *
82 * @exception ServletException thrown by any error
83 */
84 public DbUtils()
85 throws ServletException {
86 log.entering(getClass().getName(), "DbUtils");
87 log.exiting(getClass().getName(), "DbUtils");
88 }
89
90 /**
91 * Convenience method creates an SQL statement and executes a query.
92 *
93 * @param conn java.sql.Connection
94 * @param query String database SQL statement for a query
95 * @return JDBC ResultSet
96 * @exception SQLException for null statement or error on execute
97 */
98 public static ResultSet execQuery(Statement stmt, String query)
99 throws SQLException {
100 log.finest("ENTER making query \""
101 + query + "\"");
102 ResultSet rs = null;
103 if (stmt == null) {
104 String error = "The statement is null";
105 log.fine(error);
106 throw new SQLException(error);
107 } else {
108 rs = stmt.executeQuery(query);
109 }
110 log.finest("RETURN");
111 return rs;
112 }
113
114 /**
115 * Convenience method executes an update of a database.
116 *
117 * @param conn java.sql.Connection
118 * @param update Description of Parameter
119 * @return boolean true if successful
120 * @exception SQLException for null statement or error on update
121 */
122 public static boolean execUpdate(Connection conn, String update)
123 throws SQLException {
124 log.finest("ENTER making update \""
125 + update + "\"");
126 int updateCount = -1;
127 Statement stmt = null;
128 ResultSet rs = null;
129 stmt = conn.createStatement();
130 if (stmt == null) {
131 String error = "The statement is null";
132 log.fine(error);
133 throw new SQLException(error);
134 } else {
135 // execute the update:
136 updateCount = stmt.executeUpdate(update);
137 // close the Statement object:
138 stmt.close();
139 }
140 boolean success = false;
141 if (updateCount > 0) {
142 success = true;
143 }
144 log.finest("RETURN");
145 return success;
146 }
147
148 /**
149 * Tests if a data table exists
150 *
151 * @param sourcename String specifying a name of a datasource
152 * @param tablename String specifying a name of a data table
153 * @return a boolean true if the table exists
154 * @exception ServletException Description of Exception
155 * @throws SQLException which can be caught in the calling method
156 */
157 public static boolean tableExists(String sourcename, String tablename)
158 throws ServletException {
159 log.finest("ENTER checking for table \"" + tablename + "\"");
160 boolean exists = false;
161 Connection conn = null;
162 try {
163 // Connect to the database:
164 conn = Pool.getConnection(sourcename);
165 } catch (SQLException e) {
166 log.warning("SQLException on attempt to connect to \""
167 + sourcename + "\" database: " + e);
168 throw new ServletException("SQLException on attempt to connect to \""
169 + sourcename + "\" database: " + e);
170 } catch (NullPointerException npe) {
171 log.warning("NullPointerException on attempt to connect to \""
172 + sourcename + "\" database: " + npe);
173 throw new ServletException("NullPointerException on attempt to connect to \""
174 + sourcename + "\" database: " + npe);
175 }
176 try {
177 // try to access a table:
178 Statement stmt = conn.createStatement();
179 ResultSet rs = execQuery(stmt, "SELECT 1 FROM " + tablename);
180 stmt.close();
181 exists = true;
182 } catch (SQLException e) {
183 // hsqldb: "Table not found"
184 // mySQL: "General error: Table 'x' doesn't exist"
185 if (e.getMessage().startsWith("Table not found")
186 || e.getMessage().startsWith("General error: Table")) {
187 log.finest("SQLException: " + e);
188 exists = false;
189 } else {
190 throw new ServletException(e.getMessage());
191 }
192 } finally {
193 if (conn != null) {
194 try {
195 conn.close();
196 } catch (SQLException e) {
197 log.warning("Could not close connection: " + e);
198 throw new ServletException("Could not close connection: " + e);
199 }
200 }
201 }
202 log.finest("RETURN");
203 return exists;
204 }
205
206 /**
207 * Establish a new data table.
208 * This method can be called by an object
209 * after testing and determining a table is missing.
210 *
211 * @param sourcename String specifying a name of a datasource
212 * @param tablename String specifying a name of a data table
213 * @param statement String used to create a table
214 * @throws ServletException after catching any other exception
215 */
216 public static void createTable(String sourcename, String tablename, String statement)
217 throws ServletException {
218 log.finest("ENTER creating table \"" + tablename + "\"");
219 Connection conn = null;
220 try {
221 // Connect to the database:
222 conn = Pool.getConnection(sourcename);
223 } catch (SQLException e) {
224 log.warning("SQLException on attempt to connect to \""
225 + sourcename + "\" database: " + e);
226 throw new ServletException("SQLException on attempt to connect to \""
227 + sourcename + "\" database: " + e);
228 }
229 Statement stat = null;
230 try {
231 // create a statement object:
232 stat = conn.createStatement();
233 try {
234 // Try to drop the table
235 stat.executeUpdate("DROP TABLE " + tablename);
236 } catch (SQLException e) {
237 // (ignore Exception, because the table may not yet exist)
238 }
239 stat.close();
240 // create a statement object:
241 stat = conn.createStatement();
242 stat.execute("CREATE TABLE " + tablename + " " + statement);
243 // close the Statement object, it is no longer used:
244 stat.close();
245 log.fine("table \"" + tablename + "\" created");
246 } catch (SQLException e) {
247 log.warning("SQLException: " + e);
248 } finally {
249 if (conn != null) {
250 try {
251 conn.close();
252 } catch (SQLException e) {
253 log.warning("Could not close connection: " + e);
254 throw new ServletException("Could not close connection: " + e);
255 }
256 }
257 }
258 log.finest("RETURN");
259 }
260
261 /**
262 * Adds data obtained from a Map object to a specified data table.
263 *
264 * @param sourcename String specifying a name of a datasource
265 * @param tablename String specifying a name of a data table
266 * @param collection Map collection of keys and values
267 * @throws ServletException after catching any other exception
268 */
269 public static void populateTable(String sourcename, String tablename, Map collection)
270 throws ServletException {
271 log.finest("ENTER populating table \"" + tablename + "\"");
272 Connection conn = null;
273 try {
274 // Connect to the database:
275 conn = Pool.getConnection(sourcename);
276 } catch (SQLException e) {
277 log.warning("SQLException on attempt to connect to \""
278 + sourcename + "\" database: " + e);
279 throw new ServletException("SQLException on attempt to connect to \""
280 + sourcename + "\" database: " + e);
281 }
282 try {
283 String key = null;
284 String value = null;
285 String query = null;
286 Iterator i = collection.keySet().iterator();
287 while (i.hasNext()) {
288 key = (String) i.next();
289 value = (String) collection.get(key);
290 query = "INSERT INTO " + tablename + " (key,value) VALUES ('"
291 + key + "','" + value + "')";
292 execUpdate(conn, query);
293 log.finest("key/value pair \"" + key + ", " + value + "\" has been added successfully ");
294 }
295 } catch (SQLException e) {
296 log.warning("Could not add key/value pair: " + e);
297 throw new ServletException("Could not add key/value pair: " + e);
298 } finally {
299 if (conn != null) {
300 try {
301 conn.close();
302 } catch (SQLException e) {
303 log.warning("Could not close connection: " + e);
304 throw new ServletException("Could not close connection: " + e);
305 }
306 }
307 }
308 log.finest("RETURN");
309 return;
310 }
311
312 /**
313 * Do a simple database lookup, returning a single string, specifying a datasource,
314 * table, column, and "where clause."
315 *
316 * @param sourcename String specifying a name of a datasource
317 * @param tablename String specifying a name of a data table
318 * @param column String specifying a name of a column in the table
319 * @param where String "where clause"
320 * @return null or a String value found in the field
321 * @exception ServletException Description of Exception
322 * @throws SQLException which can be caught in the calling method
323 */
324 public static String getFieldValue(String sourcename, String tablename, String column , String where)
325 throws ServletException {
326 log.finest("ENTER getting value for \"" + column + "\" from \"" + tablename + "\"");
327 String value = null;
328 Connection conn = null;
329 try {
330 // Connect to the database:
331 conn = Pool.getConnection(sourcename);
332 } catch (SQLException e) {
333 log.warning("SQLException on attempt to connect to \""
334 + sourcename + "\" database: " + e);
335 throw new ServletException("SQLException on attempt to connect to \""
336 + sourcename + "\" database: " + e);
337 } catch (NullPointerException npe) {
338 log.warning("NullPointerException on attempt to connect to \""
339 + sourcename + "\" database: " + npe);
340 throw new ServletException("NullPointerException on attempt to connect to \""
341 + sourcename + "\" database: " + npe);
342 }
343 try {
344 // try to access a table:
345 Statement stmt = conn.createStatement();
346 ResultSet rs = execQuery(stmt, "SELECT "
347 + column + " FROM " + tablename + " WHERE " + where);
348 // if more than one record is returned, use the last one:
349 while (rs.next()) {
350 value = rs.getString(1);
351 }
352 rs.close();
353 stmt.close();
354 } catch (SQLException e) {
355 throw new ServletException(e.getMessage());
356 } finally {
357 if (conn != null) {
358 try {
359 conn.close();
360 } catch (SQLException e) {
361 log.warning("Could not close connection: " + e);
362 throw new ServletException("Could not close connection: " + e);
363 }
364 }
365 }
366 log.finest("RETURN");
367 return value;
368 }
369 }
370