001: /*
002: * (C) Copyright 2005 Nabh Information Systems, Inc.
003: *
004: * All copyright notices regarding Nabh's products MUST remain
005: * intact in the scripts and in the outputted HTML.
006: * This program is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public License
008: * as published by the Free Software Foundation; either version 2.1
009: * of the License, or (at your option) any later version.
010: *
011: * This program is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: * GNU Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public License
017: * along with this program; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
019: *
020: */
021: package com.nabhinc.util.db;
022:
023: import java.sql.Date;
024: import java.sql.PreparedStatement;
025: import java.sql.ResultSet;
026: import java.sql.SQLException;
027: import java.sql.Timestamp;
028: import java.sql.Types;
029: import java.text.DateFormat;
030: import java.text.ParseException;
031: import java.util.Locale;
032: import java.util.Map;
033: import java.util.Vector;
034:
035: import javax.portlet.ActionRequest;
036: import javax.portlet.ActionResponse;
037: import javax.portlet.PortletRequest;
038: import javax.servlet.http.HttpServletRequest;
039:
040: import com.nabhinc.core.WebServiceRequest;
041: import com.nabhinc.util.i18n.DateTimeFormatUtil;
042:
043: /**
044: *
045: *
046: * @author Padmanabh Dabke
047: * (c) 2005 Nabh Information Systems, Inc. All Rights Reserved.
048: */
049: public class DBParamUtil {
050: public static final String OLD_VALUE_ATTRIBUTE_PREFIX = "nabh.old_value.";
051:
052: public static void setSQLParam(PreparedStatement st, int pos,
053: String paramName, int paramType, PortletRequest request,
054: int id) throws SQLException, ParseException {
055: String paramValue = null;
056: if (paramName.startsWith("$")) {
057: String prefName = paramName.substring(1);
058: paramValue = request.getPreferences().getValue(prefName,
059: null);
060: } else {
061: paramValue = request.getParameter(paramName);
062: }
063: setSQLParamHelper(st, pos, paramName, paramType, paramValue,
064: (Map) request.getAttribute(PortletRequest.USER_INFO),
065: request.getLocale(), request.getRemoteUser(), id);
066: }
067:
068: public static void setSQLParam(PreparedStatement st, int pos,
069: String paramName, int paramType,
070: HttpServletRequest request, int id) throws SQLException,
071: ParseException {
072: setSQLParamHelper(st, pos, paramName, paramType, request
073: .getParameter(paramName), (Map) request
074: .getAttribute(PortletRequest.USER_INFO), request
075: .getLocale(), request.getRemoteUser(), id);
076: }
077:
078: // For a Web service we do not have a notion of a request parameter, so we
079: // just send in null until we figure out if there is a way of find something
080: // with equivalent semantics.
081: public static void setSQLParam(PreparedStatement st, int pos,
082: String paramName, int paramType, WebServiceRequest request,
083: int id) throws SQLException, ParseException {
084: setSQLParamHelper(st, pos, paramName, paramType, null, /* request.getParameter(paramName) */
085: (Map) request.getAttribute(WebServiceRequest.USER_INFO),
086: request.getLocale(), request.getRemoteUser(), id);
087: }
088:
089: private static void setSQLParamHelper(PreparedStatement st,
090: int pos, String paramName, int paramType,
091: String paramValue, Map userInfo, Locale locale,
092: String userName, int id) throws SQLException,
093: ParseException {
094: // SQL indexes start from 1
095: pos++;
096:
097: // First check for special variables: remote user name, current date,
098: // current timestamp
099: if (paramName.startsWith("$")) {
100: if (paramName.equalsIgnoreCase("$userName")) {
101: st.setString(pos, userName);
102: return;
103: } else if (paramName.equalsIgnoreCase("$userId")) {
104: if (userInfo != null) {
105: String userIdStr = (String) userInfo.get("user.id");
106: if (userIdStr != null) {
107: st.setInt(pos, Integer.parseInt(userIdStr));
108: return;
109: }
110: }
111: st.setString(pos, null);
112:
113: } else if (paramName.equalsIgnoreCase("$currentDate")) {
114: st.setDate(pos, new Date(System.currentTimeMillis()));
115: return;
116: } else if (paramName.equalsIgnoreCase("$currentTimestamp")) {
117: st.setTimestamp(pos, new Timestamp(System
118: .currentTimeMillis()));
119: return;
120: } else if (paramName.equalsIgnoreCase("$id")) {
121: st.setInt(pos, id);
122: return;
123: } //else {
124: //throw new IllegalArgumentException("Invalid param variable: " + paramName + ".");
125: //}
126: }
127:
128: // Now look for a request parameter
129: if ("".equals(paramValue))
130: paramValue = null;
131: /*
132: if (paramValue == null)
133: paramValue = defaul;
134: */
135: switch (paramType) {
136: case Types.INTEGER:
137: case Types.DECIMAL:
138: if (paramValue == null) {
139: st.setString(pos, null);
140: } else {
141: st.setInt(pos, Integer.parseInt(paramValue));
142: }
143: break;
144: case Types.VARCHAR:
145: st.setString(pos, paramValue);
146: break;
147: case Types.BOOLEAN:
148: if (paramValue == null) {
149: st.setBoolean(pos, false);
150: } else {
151: st.setBoolean(pos, true);
152: }
153: break;
154: case Types.DATE:
155: if (paramValue == null || paramValue.trim().equals("")) {
156: st.setDate(pos, null);
157: } else {
158: DateFormat formatter = DateTimeFormatUtil
159: .getDateFormat(locale);
160: Date date = new Date(formatter.parse(paramValue)
161: .getTime());
162: st.setDate(pos, date);
163: }
164: break;
165: case Types.TIMESTAMP:
166: if (paramValue == null || paramValue.trim().equals("")) {
167: st.setTimestamp(pos, null);
168: } else {
169: DateFormat formatter = DateTimeFormatUtil
170: .getDateTimeFormat(locale);
171: formatter.setLenient(true);
172: Timestamp tm = new Timestamp(formatter
173: .parse(paramValue).getTime());
174: st.setTimestamp(pos, tm);
175: }
176: break;
177: default:
178: st.setString(pos, paramValue);
179:
180: }
181:
182: }
183:
184: @SuppressWarnings("unchecked")
185: public static void setRenderParam(ResultSet results, int pos,
186: String paramName, int paramType, ActionRequest request,
187: ActionResponse response, boolean isMultiple)
188: throws SQLException {
189:
190: // SQL indexes start from 1
191: pos++;
192:
193: switch (paramType) {
194: case Types.BOOLEAN:
195: boolean val = results.getBoolean(pos);
196: if (val) {
197: response.setRenderParameter(paramName, "true");
198: }
199: break;
200: case Types.DATE:
201: Date d = results.getDate(pos);
202: if (d != null) {
203: Locale locale = request.getLocale();
204: DateFormat formatter = DateTimeFormatUtil
205: .getDateFormat(locale);
206: if (isMultiple) {
207: Vector paramValues = new Vector();
208: paramValues.addElement(formatter.format(d));
209: while (results.next()) {
210: d = results.getDate(pos);
211: paramValues.addElement(formatter.format(d));
212: }
213: String[] valueArray = new String[paramValues.size()];
214: paramValues.copyInto(valueArray);
215: response.setRenderParameter(paramName, valueArray);
216: } else {
217: response.setRenderParameter(paramName, formatter
218: .format(d));
219: }
220: }
221: break;
222: case Types.TIMESTAMP:
223: Timestamp tm = results.getTimestamp(pos);
224: if (tm != null) {
225: Locale locale = request.getLocale();
226: DateFormat formatter = DateTimeFormatUtil
227: .getDateTimeFormat(locale);
228: if (isMultiple) {
229: Vector paramValues = new Vector();
230: paramValues.addElement(formatter.format(tm));
231: while (results.next()) {
232: tm = results.getTimestamp(pos);
233: paramValues.addElement(formatter.format(tm));
234: }
235: String[] valueArray = new String[paramValues.size()];
236: paramValues.copyInto(valueArray);
237: response.setRenderParameter(paramName, valueArray);
238: } else {
239: response.setRenderParameter(paramName, formatter
240: .format(tm));
241: }
242: }
243: break;
244: default:
245: if (isMultiple) {
246: Vector paramValues = new Vector();
247: paramValues.addElement(results.getString(pos));
248: while (results.next()) {
249: paramValues.addElement(results.getString(pos));
250: }
251: String[] valueArray = new String[paramValues.size()];
252: paramValues.copyInto(valueArray);
253: response.setRenderParameter(paramName, valueArray);
254: } else {
255: response.setRenderParameter(paramName, results
256: .getString(pos));
257: }
258:
259: }
260:
261: }
262:
263: @SuppressWarnings("unchecked")
264: public static void setAndMarkRenderParam(ResultSet results,
265: int pos, String paramName, int paramType,
266: ActionRequest request, ActionResponse response,
267: boolean isMultiple) throws SQLException {
268:
269: // SQL indexes start from 1
270: pos++;
271:
272: switch (paramType) {
273: case Types.BOOLEAN:
274: boolean val = results.getBoolean(pos);
275: if (val) {
276: setAndMarkRenderParameter(request, response, paramName,
277: "true");
278: }
279: break;
280: case Types.DATE:
281: Date d = results.getDate(pos);
282: if (d != null) {
283: Locale locale = request.getLocale();
284: DateFormat formatter = DateTimeFormatUtil
285: .getDateFormat(locale);
286: if (isMultiple) {
287: Vector paramValues = new Vector();
288: paramValues.addElement(formatter.format(d));
289: while (results.next()) {
290: d = results.getDate(pos);
291: paramValues.addElement(formatter.format(d));
292: }
293: String[] valueArray = new String[paramValues.size()];
294: paramValues.copyInto(valueArray);
295: setAndMarkRenderParameter(request, response,
296: paramName, valueArray);
297: } else {
298: setAndMarkRenderParameter(request, response,
299: paramName, formatter.format(d));
300: }
301: }
302: break;
303: case Types.TIMESTAMP:
304: Timestamp tm = results.getTimestamp(pos);
305: if (tm != null) {
306: Locale locale = request.getLocale();
307: DateFormat formatter = DateTimeFormatUtil
308: .getDateTimeFormat(locale);
309: if (isMultiple) {
310: Vector paramValues = new Vector();
311: paramValues.addElement(formatter.format(tm));
312: while (results.next()) {
313: tm = results.getTimestamp(pos);
314: paramValues.addElement(formatter.format(tm));
315: }
316: String[] valueArray = new String[paramValues.size()];
317: paramValues.copyInto(valueArray);
318: setAndMarkRenderParameter(request, response,
319: paramName, valueArray);
320: } else {
321: setAndMarkRenderParameter(request, response,
322: paramName, formatter.format(tm));
323: }
324: }
325: break;
326: default:
327: if (isMultiple) {
328: Vector paramValues = new Vector();
329: paramValues.addElement(results.getString(pos));
330: while (results.next()) {
331: paramValues.addElement(results.getString(pos));
332: }
333: String[] valueArray = new String[paramValues.size()];
334: paramValues.copyInto(valueArray);
335: setAndMarkRenderParameter(request, response, paramName,
336: valueArray);
337: } else {
338: setAndMarkRenderParameter(request, response, paramName,
339: results.getString(pos));
340: }
341:
342: }
343:
344: }
345:
346: public static SQLParameterSetter createParameterSetter(
347: String paramName, int paramType) {
348:
349: if (paramName.startsWith("$")) {
350: if (paramName.equalsIgnoreCase("$userName")) {
351: return UserNameParameterSetter.getInstance();
352: } else if (paramName.equalsIgnoreCase("$userId")) {
353: return UserIDParameterSetter.getInstance();
354: } else if (paramName.equalsIgnoreCase("$currentDate")) {
355: return CurrentDateParameterSetter.getInstance();
356: } else if (paramName.equalsIgnoreCase("$currentTimestamp")) {
357: return CurrentTimestampParameterSetter.getInstance();
358: } else if (paramName.equalsIgnoreCase("$id")) {
359: return IDParameterSetter.getInstance();
360: } else {
361: return new PreferenceParameterSetter(paramName
362: .substring(1), paramType);
363: }
364: }
365:
366: switch (paramType) {
367: case Types.INTEGER:
368: case Types.DECIMAL:
369: return IntegerParameterSetter.getInstance();
370: case Types.DOUBLE:
371: return DoubleParameterSetter.getInstance();
372: case Types.FLOAT:
373: return FloatParameterSetter.getInstance();
374: case Types.VARCHAR:
375: return VarcharParameterSetter.getInstance();
376: case Types.BOOLEAN:
377: return BooleanParameterSetter.getInstance();
378: case Types.DATE:
379: return DateParameterSetter.getInstance();
380: case Types.TIMESTAMP:
381: return TimestampParameterSetter.getInstance();
382: default:
383: return VarcharParameterSetter.getInstance();
384:
385: }
386:
387: }
388:
389: public static void setSQLTimestamp(String paramValue,
390: PreparedStatement st, int pos, Locale locale)
391: throws SQLException {
392: if (paramValue == null || paramValue.trim().equals("")) {
393: st.setTimestamp(pos, null);
394: } else {
395: DateFormat formatter = DateTimeFormatUtil
396: .getDateTimeFormat(locale);
397: Timestamp tm;
398: try {
399: tm = new Timestamp(formatter.parse(paramValue)
400: .getTime());
401: } catch (ParseException e) {
402: throw new IllegalArgumentException(
403: "Invalid date format.");
404: }
405: st.setTimestamp(pos, tm);
406: }
407:
408: }
409:
410: public static void setSQLBoolean(String paramValue,
411: PreparedStatement st, int pos) throws SQLException {
412: if (paramValue == null) {
413: st.setBoolean(pos, false);
414: } else {
415: st.setBoolean(pos, true);
416: }
417: }
418:
419: public static void setSQLDouble(String paramValue,
420: PreparedStatement st, int pos) throws SQLException {
421: if (paramValue == null || paramValue.trim().equals("")) {
422: st.setString(pos, null);
423: } else {
424: st.setDouble(pos, Double.parseDouble(paramValue));
425: }
426: }
427:
428: public static void setSQLFloat(String paramValue,
429: PreparedStatement st, int pos) throws SQLException {
430: if (paramValue == null || paramValue.trim().equals("")) {
431: st.setString(pos, null);
432: } else {
433: st.setFloat(pos, Float.parseFloat(paramValue));
434: }
435: }
436:
437: public static void setSQLInt(String paramValue,
438: PreparedStatement st, int pos) throws SQLException {
439: if (paramValue == null || paramValue.trim().equals("")) {
440: st.setString(pos, null);
441: } else {
442: st.setInt(pos, Integer.parseInt(paramValue));
443: }
444: }
445:
446: public static void setSQLDate(String paramValue,
447: PreparedStatement st, int pos, Locale locale)
448: throws SQLException {
449:
450: if (paramValue == null || paramValue.trim().equals("")) {
451: st.setDate(pos, null);
452: } else {
453: DateFormat formatter = DateTimeFormatUtil
454: .getDateFormat(locale);
455: Date date;
456: try {
457: date = new Date(formatter.parse(paramValue).getTime());
458: } catch (ParseException e) {
459: throw new IllegalArgumentException(
460: "Invalid date format.");
461: }
462: st.setDate(pos, date);
463: }
464: }
465:
466: private static void setAndMarkRenderParameter(
467: ActionRequest request, ActionResponse response,
468: String paramName, String[] values) {
469:
470: response.setRenderParameter(paramName, values);
471: if (values != null && values.length > 0 && values[0] != null
472: && values[0].length() != 0)
473: request.getPortletSession().setAttribute(
474: OLD_VALUE_ATTRIBUTE_PREFIX + paramName, values);
475: }
476:
477: private static void setAndMarkRenderParameter(
478: ActionRequest request, ActionResponse response,
479: String paramName, String value) {
480:
481: response.setRenderParameter(paramName, value);
482: if (value != null && value.length() != 0)
483: request.getPortletSession().setAttribute(
484: OLD_VALUE_ATTRIBUTE_PREFIX + paramName, value);
485: }
486:
487: }
|