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