1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.student.common.dao.impl;
17
18 import java.text.ParseException;
19 import java.text.SimpleDateFormat;
20 import java.util.ArrayList;
21 import java.util.Collection;
22 import java.util.List;
23 import java.util.Map;
24 import java.util.StringTokenizer;
25
26 import javax.persistence.Query;
27
28 import org.apache.log4j.Logger;
29 import org.kuali.student.common.dao.SearchableDao;
30 import org.kuali.student.common.search.dto.QueryParamInfo;
31 import org.kuali.student.common.search.dto.ResultColumnInfo;
32 import org.kuali.student.common.search.dto.SearchParam;
33 import org.kuali.student.common.search.dto.SearchRequest;
34 import org.kuali.student.common.search.dto.SearchResult;
35 import org.kuali.student.common.search.dto.SearchResultCell;
36 import org.kuali.student.common.search.dto.SearchResultRow;
37 import org.kuali.student.common.search.dto.SearchTypeInfo;
38 import org.kuali.student.common.search.dto.SortDirection;
39
40 public class AbstractSearchableCrudDaoImpl extends AbstractCrudDaoImpl
41 implements SearchableDao {
42 final Logger LOG = Logger.getLogger(AbstractSearchableCrudDaoImpl.class);
43 private static SimpleDateFormat df = new SimpleDateFormat("EEE MMM dd hh:mm:ss zzz yyyy");
44
45 @Override
46 public SearchResult search(SearchRequest searchRequest, Map<String, String> queryMap, SearchTypeInfo searchTypeInfo) {
47 String searchKey = searchRequest.getSearchKey();
48
49 boolean isNative = false;
50
51
52 String queryString = queryMap.get(searchKey);
53 String optionalQueryString = "";
54 if(null == queryString){
55 LOG.error("No SQL query was found for searchKey:"+searchKey);
56 }
57
58 if(queryString.toUpperCase().startsWith("NATIVE:")){
59 queryString = queryString.substring("NATIVE:".length());
60 isNative = true;
61 }
62
63
64 List<SearchParam> searchParamsTemp = new ArrayList<SearchParam>(searchRequest.getParams());
65
66 List<SearchParam> internalQueryParms = new ArrayList<SearchParam>(searchRequest.getParams());
67 for(SearchParam searchParam : searchParamsTemp){
68 for(QueryParamInfo queryParam:searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams()){
69
70 if(queryParam.isOptional()&&queryParam.getKey().equals(searchParam.getKey())&&searchParam.getValue()!=null){
71 if(!optionalQueryString.isEmpty()){
72 optionalQueryString += " AND ";
73 }
74
75
76 String condition = queryMap.get(searchParam.getKey());
77 if (condition.trim().startsWith("!!")) {
78 String substitutionType = condition.trim().substring("!!".length());
79
80 if (condition.contains(" ")) {
81 substitutionType = condition.substring("!!".length(), condition.indexOf(" "));
82 }
83
84 if (substitutionType != null && substitutionType.equals("NUMBER_RANGE")) {
85 String realCondition = condition.substring("!!".length() + substitutionType.length()).trim();
86 String queryValue = (String)searchParam.getValue();
87
88 if (queryValue != null && queryValue.trim().contains("-")) {
89 StringTokenizer strTokenizer = new StringTokenizer(queryValue.trim(),"-");
90 if (strTokenizer.hasMoreElements()) {
91 String strNum1 = strTokenizer.nextToken().trim();
92 String strNum2 = strTokenizer.nextToken().trim();
93 optionalQueryString +=
94 realCondition +
95 " BETWEEN " + "'" + strNum1 + "'" + " AND " + "'" + strNum2 + "'";
96 internalQueryParms.remove(searchParam);
97 }
98 } else {
99
100 optionalQueryString += realCondition + " = '" + queryValue + "'";
101 internalQueryParms.remove(searchParam);
102 }
103 }
104 } else if (condition.trim().contains(":")) {
105
106 String dataType = queryParam.getFieldDescriptor().getDataType();
107 if ((dataType != null) && "boolean".equals(dataType)) {
108 optionalQueryString += queryMap.get(searchParam.getKey()).replace(":" + searchParam.getKey().replace(".", "_"), searchParam.getValue().toString());
109 internalQueryParms.remove(searchParam);
110 } else {
111 optionalQueryString += queryMap.get(searchParam.getKey());
112 }
113 } else {
114
115
116
117 optionalQueryString +=
118 "(LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE LOWER('" + searchParam.getValue() + "') || '%' OR " +
119 "LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE '% ' || LOWER('" + searchParam.getValue() + "') || '%')";
120 internalQueryParms.remove(searchParam);
121 }
122 }
123 }
124 }
125
126
127 if(!optionalQueryString.isEmpty()){
128 if(!queryString.toUpperCase().contains(" WHERE ")){
129 queryString += " WHERE ";
130 }
131 else {
132 queryString += " AND ";
133 }
134 }
135
136
137 String orderByClause = "";
138 if(!queryString.toUpperCase().contains("ORDER BY")&&searchRequest.getSortColumn()!=null){
139
140 int i = 0;
141
142
143 int selectIndex = queryString.toLowerCase().indexOf("select")+"select".length();
144 int fromIndex = queryString.toLowerCase().indexOf(" from ");
145
146 if (selectIndex >= 0 && fromIndex > selectIndex){
147 String[] jpqlResultColumns = queryString.substring(selectIndex, fromIndex).replaceAll("\\s", "").split(",");
148 for(ResultColumnInfo results : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()){
149 if(results.getKey().equals(searchRequest.getSortColumn())){
150 orderByClause = " ORDER BY "+jpqlResultColumns[i]+" ";
151 if(searchRequest.getSortDirection()!=null && searchRequest.getSortDirection()==SortDirection.DESC){
152 orderByClause += "DESC ";
153 }else{
154 orderByClause += "ASC ";
155 }
156 }
157 i++;
158 }
159 }
160 }
161
162
163 String finalQueryString = queryString + optionalQueryString + orderByClause;
164
165
166
167
168
169 Query query;
170 if(isNative){
171 LOG.info("Native Query:"+finalQueryString);
172 query = em.createNativeQuery(finalQueryString);
173 }else{
174 LOG.info("JPQL Query:"+finalQueryString);
175 query = em.createQuery(finalQueryString);
176 }
177
178
179 if(searchRequest.getStartAt()!=null){
180 query.setFirstResult(searchRequest.getStartAt().intValue());
181 }
182 if(searchRequest.getMaxResults()!=null){
183 query.setMaxResults(searchRequest.getMaxResults().intValue());
184 }
185
186
187 for (SearchParam searchParam : internalQueryParms) {
188
189 if (searchParam.getValue() != null) {
190 List<QueryParamInfo> queryParams = searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams();
191 String paramDataType = null;
192 if (queryParams != null) {
193 for (QueryParamInfo queryParam : queryParams) {
194 if (queryParam.getKey() != null && queryParam.getKey().equals(searchParam.getKey())) {
195 paramDataType = queryParam.getFieldDescriptor().getDataType();
196 }
197 }
198 }
199
200 Object queryParamValue = null;
201 if ("date".equals(paramDataType) && searchParam.getValue() instanceof String) {
202 try {
203 queryParamValue = df.parse((String)searchParam.getValue());
204 } catch (ParseException e) {
205 throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
206 }
207 } if ("long".equals(paramDataType) && searchParam.getValue() instanceof String) {
208 try{
209 queryParamValue = Long.valueOf((String)searchParam.getValue());
210 } catch (NumberFormatException e) {
211 throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
212 }
213 } else {
214 queryParamValue = searchParam.getValue();
215 }
216
217 if((queryParamValue==null||queryParamValue instanceof Collection && ((Collection<?>)queryParamValue).isEmpty())&&"list".equals(paramDataType)){
218 queryParamValue = "";
219 }
220 query.setParameter(searchParam.getKey().replace(".", "_"), queryParamValue);
221 }
222 }
223
224
225 List<SearchResultRow> results = convertToResults(query.getResultList(),searchTypeInfo);
226
227 SearchResult searchResult = new SearchResult();
228 searchResult.setRows(results);
229 searchResult.setSortColumn(searchRequest.getSortColumn());
230 searchResult.setSortDirection(searchRequest.getSortDirection());
231 searchResult.setStartAt(searchRequest.getStartAt());
232 if(searchRequest.getNeededTotalResults()!=null && searchRequest.getNeededTotalResults()){
233
234 String regex = "^\\s*[Ss][Ee][Ll][Ee][Cc][Tt]\\s+([^,\\s]+)(.|[\r\n])*?\\s+[Ff][Rr][Oo][Mm]\\s+";
235 String replacement = "SELECT COUNT($1) FROM ";
236 String countQueryString = (queryString + optionalQueryString).replaceFirst(regex, replacement);
237
238 LOG.info("Executing query: "+countQueryString);
239 Query countQuery;
240 if(isNative){
241 countQuery = em.createNativeQuery(countQueryString);
242 }else{
243 countQuery = em.createQuery(countQueryString);
244 }
245 for (SearchParam searchParam : internalQueryParms) {
246 countQuery.setParameter(searchParam.getKey().replace(".", "_"), searchParam.getValue());
247 }
248 Long totalResults = (Long) countQuery.getSingleResult();
249 searchResult.setTotalResults(totalResults.intValue());
250 }
251
252 return searchResult;
253 }
254
255 private List<SearchResultRow> convertToResults(List<?> queryResults,
256 SearchTypeInfo searchTypeInfo) {
257 List<SearchResultRow> results = new ArrayList<SearchResultRow>();
258
259 if(queryResults!=null){
260
261 for(Object queryResult:queryResults){
262 SearchResultRow result = new SearchResultRow();
263 int i=0;
264 for (ResultColumnInfo resultColumn : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()) {
265
266 SearchResultCell resultCell = new SearchResultCell();
267 resultCell.setKey(resultColumn.getKey());
268
269 try {
270 Object queryResultCell = null;
271 if(queryResult.getClass().isArray()){
272 queryResultCell = ((Object[])queryResult)[i];
273
274 }else{
275 queryResultCell = queryResult;
276 }
277
278 if (queryResultCell != null) {
279 resultCell.setValue(queryResultCell.toString());
280 }
281
282 } catch (Exception e) {
283 throw new RuntimeException("Error copying results from " + queryResult.toString(),e);
284 }
285
286 result.getCells().add(resultCell);
287 i++;
288 }
289 results.add(result);
290 }
291 }
292 return results;
293 }
294
295 }