Coverage Report - org.kuali.rice.core.framework.persistence.jdbc.sql.SQLUtils
 
Classes in this File Line Coverage Branch Coverage Complexity
SQLUtils
0%
0/129
0%
0/58
3.929
SQLUtils$DateComponent
0%
0/8
N/A
N/A
3.929
 
 1   
 /*
 2   
  * Copyright 2007 The Kuali Foundation
 3   
  *
 4   
  * Licensed under the Educational Community License, Version 1.0 (the "License");
 5   
  * you may not use this file except in compliance with the License.
 6   
  * You may obtain a copy of the License at
 7   
  *
 8   
  * http://www.opensource.org/licenses/ecl1.php
 9   
  *
 10   
  * Unless required by applicable law or agreed to in writing, software
 11   
  * distributed under the License is distributed on an "AS IS" BASIS,
 12   
  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 13   
  * See the License for the specific language governing permissions and
 14   
  * limitations under the License.
 15   
  */
 16   
 package org.kuali.rice.core.framework.persistence.jdbc.sql;
 17   
 
 18   
 import java.sql.Timestamp;
 19   
 import java.util.Arrays;
 20   
 import java.util.Calendar;
 21   
 import java.util.Collection;
 22   
 import java.util.Collections;
 23   
 import java.util.HashMap;
 24   
 import java.util.Map;
 25   
 import java.util.regex.Matcher;
 26   
 import java.util.regex.Pattern;
 27   
 
 28   
 import org.apache.commons.lang.StringUtils;
 29   
 import org.kuali.rice.core.LogicalOperator;
 30   
 
 31   
 
 32   
 /**
 33   
  * Utility class for working with SQL.
 34   
  *
 35   
  * @author Kuali Rice Team (rice.collab@kuali.org)
 36   
  */
 37   
 public final class SQLUtils {
 38   
 
 39  0 
         private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SQLUtils.class);
 40   
 
 41   
         private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{2}$|^\\d{1,2}-\\d{1,2}-\\d{2}$"; // matches M/d/yy or MM/dd/yy or M-d-yy or MM-dd-yy
 42   
         private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{2})";
 43   
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{4}$|^\\d{1,2}-\\d{1,2}-\\d{4}$"; // matches M/d/yyyy or MM/dd/yyyy or M-d-yyyy or MM-dd-yyyy
 44   
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{4})";
 45   
 
 46   
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST = "^\\d{4}/\\d{1,2}/\\d{1,2}$|^\\d{4}-\\d{1,2}-\\d{1,2}$"; // matches yyyy/M/d or yyyy/MM/dd or yyyy-M-d or yyyy-MM-dd
 47   
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT = "(\\d{4})[/,-](\\d{1,2})[/,-](\\d{1,2})";
 48   
 
 49   
         private static final String DATE_REGEX_WHOLENUM_SMALL = "^\\d{6}$"; // matches MMddyy
 50   
         private static final String DATE_REGEX_WHOLENUM_SMALL_SPLIT = "(\\d{2})(\\d{2})(\\d{2})";
 51   
         private static final String DATE_REGEX_WHOLENUM_LARGE = "^\\d{8}$"; // matches MMddyyyy
 52   
         private static final String DATE_REGEX_WHOLENUM_LARGE_SPLIT = "(\\d{2})(\\d{2})(\\d{4})";
 53   
 
 54   
         private static final String TIME_REGEX = "([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])";
 55   
 
 56   
 
 57  0 
         private static final Collection<String> DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS = Collections.unmodifiableCollection(Arrays.asList(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST));
 58   
 
 59   
 
 60   
         private static final Map<String, String> REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION;
 61   
         static {
 62  0 
                 final Map<String, String> map = new HashMap<String, String>();
 63   
 
 64  0 
                 map.put(DATE_REGEX_SMALL_TWO_DIGIT_YEAR, DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT);
 65  0 
                 map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT);
 66  0 
                 map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT);
 67  0 
                 map.put(DATE_REGEX_WHOLENUM_SMALL, DATE_REGEX_WHOLENUM_SMALL_SPLIT);
 68  0 
                 map.put(DATE_REGEX_WHOLENUM_LARGE,DATE_REGEX_WHOLENUM_LARGE_SPLIT);
 69  0 
                 REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION = Collections.unmodifiableMap(map);
 70  0 
         }
 71   
 
 72  0 
         private SQLUtils() {
 73  0 
                 throw new UnsupportedOperationException("do not call");
 74   
         }
 75   
 
 76   
     /**
 77   
      * A method to format any variety of date strings into a common format
 78   
      *
 79   
      * @param date
 80   
      *            A string date in one of a few different formats
 81   
      * @return A string representing a date in the format yyyy/MM/dd or null if date is invalid
 82   
      */
 83   
     public static String getSqlFormattedDate(String date) {
 84  0 
         DateComponent dc = formatDateToDateComponent(date, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
 85  0 
         if (dc == null) {
 86  0 
             return null;
 87   
         }
 88  0 
         return dc.getYear() + "/" + dc.getMonth() + "/" + dc.getDate();
 89   
     }
 90   
 
 91   
     public static Timestamp convertStringDateToTimestamp(String dateWithoutTime) {
 92  0 
         Pattern p = Pattern.compile(TIME_REGEX);
 93  0 
         Matcher util = p.matcher(dateWithoutTime);
 94  0 
         if (util.find()) {
 95  0 
             dateWithoutTime = StringUtils.substringBeforeLast(dateWithoutTime, " ");
 96   
         }
 97  0 
         DateComponent formattedDate = formatDateToDateComponent(dateWithoutTime, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
 98  0 
         if (formattedDate == null) {
 99  0 
             return null;
 100   
         }
 101  0 
         Calendar c = Calendar.getInstance();
 102  0 
         c.clear();
 103  0 
         c.set(Calendar.MONTH, Integer.valueOf(formattedDate.getMonth()).intValue() - 1);
 104  0 
         c.set(Calendar.DATE, Integer.valueOf(formattedDate.getDate()).intValue());
 105  0 
         c.set(Calendar.YEAR, Integer.valueOf(formattedDate.getYear()).intValue());
 106  0 
         return convertCalendar(c);
 107   
     }
 108   
 
 109   
     private static class DateComponent {
 110   
         protected String month;
 111   
         protected String date;
 112   
         protected String year;
 113   
 
 114  0 
         public DateComponent(String year, String month, String date) {
 115  0 
             this.month = month;
 116  0 
             this.date = date;
 117  0 
             this.year = year;
 118  0 
         }
 119   
 
 120   
         public String getDate() {
 121  0 
             return date;
 122   
         }
 123   
 
 124   
         public String getMonth() {
 125  0 
             return month;
 126   
         }
 127   
 
 128   
         public String getYear() {
 129  0 
             return year;
 130   
         }
 131   
     }
 132   
 
 133   
     /**
 134   
      * A method to format any variety of date strings into a common format
 135   
      *
 136   
      * @param date
 137   
      *            A string date in one of a few different formats
 138   
      * @return A string representing a date in the format MM/dd/yyyy or null if date is invalid
 139   
      */
 140   
     public static String getEntryFormattedDate(String date) {
 141  0 
         Pattern p = Pattern.compile(TIME_REGEX);
 142  0 
         Matcher util = p.matcher(date);
 143  0 
         if (util.find()) {
 144  0 
             date = StringUtils.substringBeforeLast(date, " ");
 145   
         }
 146  0 
         DateComponent dc = formatDateToDateComponent(date, DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS);
 147  0 
         if (dc == null) {
 148  0 
             return null;
 149   
         }
 150  0 
         return dc.getMonth() + "/" + dc.getDate() + "/" + dc.getYear();
 151   
     }
 152   
 
 153   
     private static DateComponent formatDateToDateComponent(String date, Collection<String> regularExpressionList) {
 154  0 
         String matchingRegexExpression = null;
 155  0 
         for (String string : regularExpressionList) {
 156  0 
             String matchRegex = string;
 157  0 
             if (!REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.containsKey(matchRegex)) {
 158  0 
                 String errorMsg = "";
 159  0 
                 LOG.error("formatDateToDateComponent(String,List) " + errorMsg);
 160   
 
 161   
             }
 162  0 
             Pattern p = Pattern.compile(matchRegex);
 163  0 
             if ((p.matcher(date)).matches()) {
 164  0 
                 matchingRegexExpression = matchRegex;
 165  0 
                 break;
 166   
             }
 167  0 
         }
 168   
 
 169  0 
         if (matchingRegexExpression == null) {
 170  0 
             String errorMsg = "formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults.  Returning null value.";
 171  0 
             if (StringUtils.isNotBlank(date)) {
 172  0 
                 LOG.warn(errorMsg);
 173   
             } else {
 174  0 
                 LOG.debug(errorMsg);
 175   
             }
 176  0 
             return null;
 177   
         }
 178  0 
         String regexSplitExpression = REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.get(matchingRegexExpression);
 179   
 
 180   
         // Check date formats and reformat to yyyy/MM/dd
 181   
         // well formed MM/dd/yyyy
 182  0 
         Pattern p = Pattern.compile(regexSplitExpression);
 183  0 
         Matcher util = p.matcher(date);
 184  0 
         util.matches();
 185  0 
         if (regexSplitExpression.equals(DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT)) {
 186  0 
             StringBuffer yearBuf = new StringBuffer();
 187  0 
             StringBuffer monthBuf = new StringBuffer();
 188  0 
             StringBuffer dateBuf = new StringBuffer();
 189  0 
             Integer year = Integer.valueOf(util.group(3));
 190   
 
 191  0 
             if (year.intValue() <= 50) {
 192  0 
                 yearBuf.append("20").append(util.group(3));
 193  0 
             } else if (util.group(3).length() < 3) {
 194  0 
                 yearBuf.append("19").append(util.group(3));
 195   
             } else {
 196  0 
                 yearBuf.append(util.group(3));
 197   
             }
 198   
 
 199  0 
             if (util.group(1).length() < 2) {
 200  0 
                 monthBuf.append("0").append(util.group(1));
 201   
             } else {
 202  0 
                 monthBuf.append(util.group(1));
 203   
             }
 204   
 
 205  0 
             if (util.group(2).length() < 2) {
 206  0 
                 dateBuf.append("0").append(util.group(2));
 207   
             } else {
 208  0 
                 dateBuf.append(util.group(2));
 209   
             }
 210   
 
 211  0 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 212   
 
 213   
             // small date format M/d/yyyy | MM/dd/yyyy | M-d-yyyy | MM-dd-yyyy
 214  0 
         } else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT)) {
 215  0 
             StringBuffer yearBuf = new StringBuffer(util.group(3));
 216  0 
             StringBuffer monthBuf = new StringBuffer();
 217  0 
             StringBuffer dateBuf = new StringBuffer();
 218   
 
 219  0 
             if (util.group(1).length() < 2) {
 220  0 
                 monthBuf.append("0").append(util.group(1));
 221   
             } else {
 222  0 
                 monthBuf.append(util.group(1));
 223   
             }
 224   
 
 225  0 
             if (util.group(2).length() < 2) {
 226  0 
                 dateBuf.append("0").append(util.group(2));
 227   
             } else {
 228  0 
                 dateBuf.append(util.group(2));
 229   
             }
 230   
 
 231  0 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 232   
 
 233   
             // small date format yyyy/M/d | yyyy/MM/dd | yyyy-M-d | yyyy-MM-dd
 234  0 
         } else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT)) {
 235  0 
             StringBuffer yearBuf = new StringBuffer(util.group(1));
 236  0 
             StringBuffer monthBuf = new StringBuffer();
 237  0 
             StringBuffer dateBuf = new StringBuffer();
 238   
 
 239  0 
             if (util.group(2).length() < 2) {
 240  0 
                 monthBuf.append("0").append(util.group(2));
 241   
             } else {
 242  0 
                 monthBuf.append(util.group(2));
 243   
             }
 244   
 
 245  0 
             if (util.group(3).length() < 2) {
 246  0 
                 dateBuf.append("0").append(util.group(3));
 247   
             } else {
 248  0 
                 dateBuf.append(util.group(3));
 249   
             }
 250   
 
 251  0 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 252   
 
 253   
             // large number MMddyyyy
 254  0 
         } else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_LARGE_SPLIT)) {
 255  0 
             return new DateComponent(util.group(3), util.group(1), util.group(2));
 256   
 
 257   
             // small number MMddyy
 258  0 
         } else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_SMALL_SPLIT)) {
 259  0 
             StringBuffer yearBuf = new StringBuffer();
 260  0 
             Integer year = Integer.valueOf(util.group(3));
 261   
 
 262  0 
             if (year.intValue() < 50) {
 263  0 
                 yearBuf.append("20");
 264   
             } else {
 265  0 
                 yearBuf.append("19");
 266   
             }
 267  0 
             yearBuf.append(util.group(3));
 268  0 
             return new DateComponent(yearBuf.toString(), util.group(1), util.group(2));
 269   
         } else {
 270  0 
             LOG.warn("formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults.  Returning null value.");
 271  0 
             return null;
 272   
         }
 273   
     }
 274   
 
 275   
     public static Calendar convertTimestamp(Timestamp timestamp) {
 276  0 
         if (timestamp == null) {
 277  0 
             return null;
 278   
         }
 279  0 
         Calendar calendar = Calendar.getInstance();
 280  0 
         calendar.setTime(timestamp);
 281  0 
         return calendar;
 282   
     }
 283   
 
 284   
     public static Timestamp convertCalendar(Calendar calendar) {
 285  0 
         if (calendar == null) {
 286  0 
             return null;
 287   
         }
 288  0 
         return new Timestamp(calendar.getTimeInMillis());
 289   
     }
 290   
 
 291   
         public static String cleanDate(String string) {
 292  0 
         for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
 293  0 
             string = StringUtils.replace(string, op.op(), "");
 294   
         }
 295  0 
         return string;
 296   
     }
 297   
 
 298   
         public static String cleanNumericOfValidOperators(String string){
 299  0 
                 for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
 300  0 
             string = StringUtils.replace(string, op.op(), "");
 301   
         }
 302  0 
                 string = StringUtils.replace(string, LogicalOperator.OR.op(), "");
 303  0 
                 string = StringUtils.replace(string, LogicalOperator.AND.op(), "");
 304  0 
                 string = StringUtils.replace(string, LogicalOperator.NOT.op(), "");
 305   
 
 306  0 
                 return string;
 307   
         }
 308   
 
 309   
     /**
 310   
      * Removes all query characters from a string.
 311   
      *
 312   
      * @param string
 313   
      * @return Cleaned string
 314   
      */
 315   
     public static String cleanString(String string) {
 316  0 
         for (LogicalOperator op : LogicalOperator.QUERY_CHARACTERS) {
 317  0 
             string = StringUtils.replace(string, op.op(), "");
 318   
         }
 319  0 
         return string;
 320   
     }
 321   
 }
Report generated by Cobertura 1.9.4.1 on 4/13/11 3:57 AM.