001: /**
002: * Copyright (C) 2006, 2007 David Bulmore, Software Sensation Inc.
003: * All Rights Reserved.
004: *
005: * This file is part of JPersist.
006: *
007: * JPersist is free software; you can redistribute it and/or modify it under
008: * the terms of the GNU General Public License (Version 2) as published by
009: * the Free Software Foundation.
010: *
011: * JPersist is distributed in the hope that it will be useful, but WITHOUT
012: * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
013: * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
014: * for more details.
015: *
016: * You should have received a copy of the GNU General Public License
017: * along with JPersist; if not, write to the Free Software Foundation,
018: * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
019: */package jpersist.utils;
020:
021: import java.sql.PreparedStatement;
022: import java.sql.ResultSet;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025: import java.util.Collection;
026: import java.util.Iterator;
027: import java.util.Map;
028: import java.util.Vector;
029: import jpersist.Database;
030: import jpersist.JPersistException;
031: import jpersist.Result;
032:
033: /**
034: * This class provides several static utility methods for retrieving data from a database handler.
035: */
036:
037: @SuppressWarnings("unchecked")
038: // working to complete a Java 1.5 version
039: public class DatabaseUtils {
040: public static Map queryReturnMap(Database db, Map map, String sql)
041: throws JPersistException {
042: return queryReturnMap(db, map, sql, 1, 2);
043: }
044:
045: public static Map queryReturnMap(Database db, Map map, String sql,
046: int keyColumn, int valueColumn) throws JPersistException {
047: Result result = db.executeQuery(sql);
048:
049: try {
050: return ResultSetUtils.loadMap(result.getResultSet(), map,
051: keyColumn, valueColumn);
052: } catch (Exception e) {
053: throw new JPersistException(e);
054: } finally {
055: result.close();
056: }
057: }
058:
059: public static Map parameterizedQueryReturnMap(Database db, Map map,
060: String sql, Object[] objects) throws JPersistException {
061: return parameterizedQueryReturnMap(db, map, sql, objects, 1, 2);
062: }
063:
064: public static Map parameterizedQueryReturnMap(Database db, Map map,
065: String sql, Object[] objects, int keyColumn, int valueColumn)
066: throws JPersistException {
067: Result result = db.parameterizedQuery(sql, objects);
068:
069: try {
070: return ResultSetUtils.loadMap(result.getResultSet(), map,
071: keyColumn, valueColumn);
072: } catch (Exception e) {
073: throw new JPersistException(e);
074: } finally {
075: result.close();
076: }
077: }
078:
079: public static Collection queryReturnCollection(Database db,
080: Collection c, String sql) throws JPersistException {
081: return queryReturnCollection(db, c, sql, false);
082: }
083:
084: public static Collection queryReturnCollection(Database db,
085: Collection c, String sql, boolean singleObject)
086: throws JPersistException {
087: Result result = db.executeQuery(sql);
088:
089: try {
090: return ResultSetUtils.loadCollection(result.getResultSet(),
091: c, singleObject);
092: } catch (Exception e) {
093: throw new JPersistException(e);
094: } finally {
095: result.close();
096: }
097: }
098:
099: public static Collection parameterizedQueryReturnCollection(
100: Database db, Collection c, String sql, Object[] objects)
101: throws JPersistException {
102: return parameterizedQueryReturnCollection(db, c, sql, objects,
103: false);
104: }
105:
106: public static Collection parameterizedQueryReturnCollection(
107: Database db, Collection c, String sql, Object[] objects,
108: boolean singleObject) throws JPersistException {
109: Result result = db.parameterizedQuery(sql, objects);
110:
111: try {
112: return ResultSetUtils.loadCollection(result.getResultSet(),
113: c, singleObject);
114: } catch (Exception e) {
115: throw new JPersistException(e);
116: } finally {
117: result.close();
118: }
119: }
120:
121: public static Object queryReturnObject(Database db, String sql,
122: Object object) throws JPersistException {
123: Result result = db.executeQuery(sql);
124:
125: try {
126: if (result.hasNext())
127: return result.next();
128:
129: return null;
130: } finally {
131: result.close();
132: }
133: }
134:
135: public static Object parameterizedQueryReturnObject(Database db,
136: String sql, Object[] objects, Object object)
137: throws JPersistException {
138: Result result = db.parameterizedQuery(sql, objects);
139:
140: try {
141: if (result.hasNext())
142: return result.next();
143:
144: return null;
145: } finally {
146: result.close();
147: }
148: }
149:
150: public static Collection queryReturnCollectionOfObjects(
151: Database db, Collection c, String sql, Class objectClass)
152: throws JPersistException {
153: Result result = db.executeQuery(sql);
154:
155: try {
156: while (result.hasNext())
157: c.add(result.next());
158:
159: return c;
160: } catch (Exception e) {
161: throw new JPersistException(e);
162: } finally {
163: result.close();
164: }
165: }
166:
167: public static Collection parameterizedQueryReturnCollectionOfObjects(
168: Database db, Collection c, String sql, Object[] objects,
169: Class objectClass) throws JPersistException {
170: Result result = db.parameterizedQuery(sql, objects);
171:
172: try {
173: while (result.hasNext())
174: c.add(result.next());
175:
176: return c;
177: } catch (Exception e) {
178: throw new JPersistException(e);
179: } finally {
180: result.close();
181: }
182: }
183:
184: public static boolean rowExists(Database db, String sql,
185: Object[] objects) throws JPersistException {
186: Result result = db.parameterizedQuery(sql, objects);
187:
188: try {
189: return result.hasNext();
190: } finally {
191: result.close();
192: }
193: }
194:
195: public static void preparedQuery(Database db,
196: PreparedQuery handler, String sql) throws JPersistException {
197: try {
198: Statement statement = db.createPreparedStatement(sql);
199:
200: try {
201: while (handler.hasNextQuery()) {
202: handler.prepareQuery((PreparedStatement) statement);
203:
204: ResultSet result = ((PreparedStatement) statement)
205: .executeQuery();
206:
207: while (result.next())
208: handler.resultSet(result);
209: }
210: } finally {
211: statement.close();
212: }
213: } catch (Exception e) {
214: throw new JPersistException(e);
215: }
216: }
217:
218: public static int preparedUpdate(Database db,
219: PreparedUpdate handler, String sql)
220: throws JPersistException {
221: try {
222: int returnValue = 0;
223: String generatedKeys[] = handler.getGeneratedKeys();
224: Statement statement = null;
225:
226: if (generatedKeys != null && generatedKeys.length > 0)
227: statement = db.getConnection().prepareStatement(sql,
228: generatedKeys);
229: else if (generatedKeys != null)
230: statement = db.getConnection().prepareStatement(sql,
231: Statement.RETURN_GENERATED_KEYS);
232: else
233: statement = db.getConnection().prepareStatement(sql);
234:
235: try {
236: while (handler.hasNextUpdate()) {
237: handler
238: .prepareUpdate((PreparedStatement) statement);
239:
240: int rval = ((PreparedStatement) statement)
241: .executeUpdate();
242:
243: handler.setReturnValue(rval);
244:
245: returnValue += rval;
246:
247: if (generatedKeys != null) {
248: ResultSet keys = statement.getGeneratedKeys();
249:
250: while (keys.next())
251: handler.setGeneratedKey(keys.getLong(1));
252: }
253: }
254:
255: return returnValue;
256: } finally {
257: statement.close();
258: }
259: } catch (Exception e) {
260: throw new JPersistException(e);
261: }
262: }
263:
264: public static int multiRowPreparedUpdate(Database db, String sql,
265: Vector rows) throws JPersistException {
266: return multiRowPreparedUpdate(db, sql, rows, null);
267: }
268:
269: public static int multiRowPreparedUpdate(Database db, String sql,
270: Vector rows, Vector keys) throws JPersistException {
271: MultiRowPreparedUpdateHandler mr = new MultiRowPreparedUpdateHandler(
272: rows, keys);
273:
274: return preparedUpdate(db, mr, sql);
275: }
276:
277: static class MultiRowPreparedUpdateHandler extends
278: PreparedUpdateAdapter {
279: Vector keys;
280: Iterator i;
281:
282: MultiRowPreparedUpdateHandler(Vector rows, Vector keys) {
283: this .keys = keys;
284:
285: i = rows.iterator();
286: }
287:
288: public boolean hasNextUpdate() {
289: return i.hasNext();
290: }
291:
292: public void prepareUpdate(PreparedStatement ps)
293: throws JPersistException {
294: Object[] objects = (Object[]) i.next();
295:
296: try {
297: Database.setPreparedStatementObjects(ps, objects);
298: } catch (SQLException ex) {
299: throw new JPersistException(ex);
300: }
301: }
302:
303: public void setGeneratedKey(long key) {
304: if (keys != null)
305: keys.add(new Long(key));
306: }
307: }
308: }
|