Coverage Report

Coverage Report - org.kuali.student.common.dao.impl.AbstractSearchableCrudDaoImpl

Classes in this File

Line Coverage

Branch Coverage

Complexity

AbstractSearchableCrudDaoImpl

0/160

0/124

24.667

AbstractSearchableCrudDaoImpl$1

0/2

N/A

24.667

 /**
  * Copyright 2010 The Kuali Foundation Licensed under the
  * Educational Community License, Version 2.0 (the "License"); you may
  * not use this file except in compliance with the License. You may
  * obtain a copy of the License at
  *
  * http://www.osedu.org/licenses/ECL-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an "AS IS"
  * BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
  * or implied. See the License for the specific language governing
  * permissions and limitations under the License.
  */
 
 package org.kuali.student.common.dao.impl;
 
 import java.math.BigDecimal;
 import java.text.DateFormat;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.List;
 import java.util.Map;
 import java.util.StringTokenizer;
 
 import javax.persistence.Query;
 
 import org.apache.log4j.Logger;
 import org.kuali.student.common.dao.SearchableDao;
 import org.kuali.student.common.search.dto.QueryParamInfo;
 import org.kuali.student.common.search.dto.ResultColumnInfo;
 import org.kuali.student.common.search.dto.SearchParam;
 import org.kuali.student.common.search.dto.SearchRequest;
 import org.kuali.student.common.search.dto.SearchResult;
 import org.kuali.student.common.search.dto.SearchResultCell;
 import org.kuali.student.common.search.dto.SearchResultRow;
 import org.kuali.student.common.search.dto.SearchTypeInfo;
 import org.kuali.student.common.search.dto.SortDirection;
 
 public class AbstractSearchableCrudDaoImpl extends AbstractCrudDaoImpl
                 implements SearchableDao {
         final Logger LOG = Logger.getLogger(AbstractSearchableCrudDaoImpl.class);
         
         private static ThreadLocal<DateFormat> df = new ThreadLocal<DateFormat>() {
                 protected DateFormat initialValue() {
                         return new SimpleDateFormat("EEE MMM dd hh:mm:ss zzz yyyy");
                 }
         };
 
         @Override
         public SearchResult search(SearchRequest searchRequest,        Map<String, String> queryMap, SearchTypeInfo searchTypeInfo) {
                 String searchKey = searchRequest.getSearchKey();
                 
                 boolean isNative = false;
                 
                 //retrieve the SELECT statement from search type definition
                 String queryString = queryMap.get(searchKey);
                 String optionalQueryString = "";
                 if(null == queryString){
                         LOG.error("No SQL query was found for searchKey:"+searchKey);
                 }
                 
                 if(queryString.toUpperCase().startsWith("NATIVE:")){
                         queryString = queryString.substring("NATIVE:".length());
                         isNative = true;
                 }
                 
                 //add in optional
                 List<SearchParam> searchParamsTemp = new ArrayList<SearchParam>(searchRequest.getParams());
                 // internalQueryParms is used only internally to know which parameters have to be set in the query
                 List<SearchParam> internalQueryParms = new ArrayList<SearchParam>(searchRequest.getParams());
                 for(SearchParam searchParam : searchParamsTemp){
                         for(QueryParamInfo queryParam:searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams()){
                                 // check to see if optional param has any values set.
                                 if(queryParam.isOptional()&&queryParam.getKey().equals(searchParam.getKey())&&searchParam.getValue()!=null){
                                         if(!optionalQueryString.isEmpty()){
                                                 optionalQueryString += " AND ";
                                         }
                                         
                                         //if optional query parameter has only a column name then create proper search expression
                                         String condition = queryMap.get(searchParam.getKey());
                                         if(condition==null){
                                                 throw new RuntimeException("Optional Param "+searchParam.getKey()+" must have a queryMap definition");
                                         }
                                         if (condition.trim().startsWith("!!")) {
                                             String substitutionType = condition.trim().substring("!!".length());
                                             // to detect queryMap value in the form of !!____ ___
                                             if (condition.contains(" ")) {
                                                 substitutionType = condition.substring("!!".length(), condition.indexOf(" "));
                                             }
                                             
                                             if (substitutionType != null && substitutionType.equals("NUMBER_RANGE")) {
                                                 String realCondition = condition.substring("!!".length() + substitutionType.length()).trim();
                                                 String queryValue = (String)searchParam.getValue();
                                                 // if the query value is of the form n1 - n2
                                                 if (queryValue != null && queryValue.trim().contains("-")) {
                                                     StringTokenizer strTokenizer = new StringTokenizer(queryValue.trim(),"-");
                                                     if (strTokenizer.hasMoreElements()) {
                                                         String strNum1 = strTokenizer.nextToken().trim();
                                                         String strNum2 = strTokenizer.nextToken().trim();
                                                         optionalQueryString += 
                                                             realCondition +
                                                             " BETWEEN " + "'" + strNum1 + "'" + " AND " + "'" + strNum2 + "'";
                                                         internalQueryParms.remove(searchParam);
                                                     }
                                                 } else {
                                                     // the value is just one number
                                                     optionalQueryString += realCondition + " = '" + queryValue + "'";
                                                     internalQueryParms.remove(searchParam);
                                                 }
                                             }
                                         } else if (condition.trim().contains(":")) {
                                             //this parameter is not entered by end user but rather it is set with a default context value        
                                             String dataType = queryParam.getFieldDescriptor().getDataType();
                                             if ((dataType != null) && "boolean".equals(dataType)) {
                                                 optionalQueryString += queryMap.get(searchParam.getKey()).replace(":" + searchParam.getKey().replace(".", "_"), searchParam.getValue().toString());
                                                 internalQueryParms.remove(searchParam);
                                             } else {                                            
                                                 optionalQueryString += queryMap.get(searchParam.getKey());
                                             }                                                
                                         } else {
                                                 //comparison should be case insensitive and should include wild card such that we match beginning of a text 
                                                 //and each word within text
                                                 //FIXME SQL injection can occur here - or NOT if we need to assemble SQL to cover various ways one can compare criteria to a text
                                                 optionalQueryString += 
                                 "(LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE LOWER(:"
                                         + searchParam.getKey().replace(".", "_") + ") || '%' OR " +
                                         "LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE '% ' || LOWER(:"
                                         + searchParam.getKey().replace(".", "_") + ") || '%')";
                                         }
                                 }
                         }
                 }
                 
                 //Add in the where clause or And clause if needed for the optional criteria
                 if(!optionalQueryString.isEmpty()){
                         if(!queryString.toUpperCase().contains(" WHERE ")){
                                 queryString += " WHERE ";
                         }
                         else {
                                 queryString += " AND ";
                         }
                 }
                 
                 //Do ordering
                 String orderByClause = "";                
                 if(!queryString.toUpperCase().contains("ORDER BY")&&searchRequest.getSortColumn()!=null){
                         //make sure the sort column is a real result column
                         int i = 0;
                         
                         //Get an array of the jpql results
                         int selectIndex = queryString.toLowerCase().indexOf("select")+"select".length();
                         int fromIndex = queryString.toLowerCase().indexOf(" from ");
                         
                         if (selectIndex >= 0 && fromIndex > selectIndex){
                                 String[] jpqlResultColumns = queryString.substring(selectIndex, fromIndex).replaceAll("\\s", "").split(",");
                                 for(ResultColumnInfo results : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()){
                                         if(results.getKey().equals(searchRequest.getSortColumn())){
                         if(results.getDataType()!=null && "string".equals(results.getDataType().toLowerCase())){
                                 orderByClause = " ORDER BY LOWER(" + jpqlResultColumns[i] + ") ";
                         }else{
                                 //Don't sort dates or numbers in alphabetic order or weirdness happens
                                 orderByClause = " ORDER BY " + jpqlResultColumns[i] + " ";
                         }
                                                 if(searchRequest.getSortDirection()!=null && searchRequest.getSortDirection()==SortDirection.DESC){
                                                         orderByClause += "DESC ";
                                                 }else{
                                                         orderByClause += "ASC ";
                                                 }
                                         }
                                         i++;
                                 }
                         }                        
                 }
                 
                 //Create the query
                 String finalQueryString = queryString + optionalQueryString + orderByClause;
                 
                 //remove special characters and extra spaces
                 //finalQueryString = finalQueryString.replaceAll("[\n\r\t]", " ");
                 //finalQueryString = finalQueryString.replaceAll("\\s+", " ");
                 
                 Query query;
                 if(isNative){
                         LOG.info("Native Query:"+finalQueryString);
                         query = em.createNativeQuery(finalQueryString);
                 }else{
                         LOG.info("JPQL Query:"+finalQueryString);
                         query = em.createQuery(finalQueryString);
                 }
                 
                 //Set the pagination information (eg. only return 25 rows starting at row 100)
                 if(searchRequest.getStartAt()!=null){
                         query.setFirstResult(searchRequest.getStartAt().intValue());
                 }
                 if(searchRequest.getMaxResults()!=null){
                         query.setMaxResults(searchRequest.getMaxResults().intValue());
                 }
                 
                 //replace all the "." notation with "_" since the "."s in the ids of the queries will cause problems with the jpql  
                 for (SearchParam searchParam : internalQueryParms) {
                         // check to see if optional param has any values set.
                         if (searchParam.getValue() != null) {
                             List<QueryParamInfo> queryParams = searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams();
                             String paramDataType = null;
                             if (queryParams != null) {
                                 for (QueryParamInfo queryParam : queryParams) {
                                     if (queryParam.getKey() != null && queryParam.getKey().equals(searchParam.getKey())) {
                                         paramDataType = queryParam.getFieldDescriptor().getDataType();
                                     }
                                 }
                             }
                             
                 Object queryParamValue = null;
                             if ("date".equals(paramDataType) && searchParam.getValue() instanceof String) {
                                 try {
                         queryParamValue = df.get().parse((String)searchParam.getValue());
                     } catch (ParseException e) {
                         throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
                     }
                             } if ("long".equals(paramDataType)){
                                     if(searchParam.getValue() instanceof String) {
                                     try{
                                                 queryParamValue = Long.valueOf((String)searchParam.getValue());
                                 } catch (NumberFormatException e) {
                                     throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
                                 }
                                     }else if(searchParam.getValue() instanceof Collection){
                                             try{
                                                     List<Long> longList = new ArrayList<Long>();
                                                     if(searchParam.getValue()!=null){
                                                             for(String value:(Collection<String>)searchParam.getValue()){
                                                                     longList.add(Long.parseLong(value));
                                                             }
                                                     }
                                                 queryParamValue = longList;
                                 } catch (NumberFormatException e) {
                                     throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
                                 }
                                     }
                             } else {
                                 queryParamValue = searchParam.getValue();
                             }
                             //Needed to get around Hibernate not supporting IN(:var) where var is null or an empty collection
                             if((queryParamValue==null||queryParamValue instanceof Collection && ((Collection<?>)queryParamValue).isEmpty())&&"list".equals(paramDataType)){
                                     queryParamValue = "";
                             }
                             query.setParameter(searchParam.getKey().replace(".", "_"), queryParamValue);
                         }
                 }
 
                 // Turn into results
                 List<SearchResultRow> results = convertToResults(query.getResultList(),searchTypeInfo);
 
                 SearchResult searchResult = new SearchResult();
                 searchResult.setRows(results);
                 searchResult.setSortColumn(searchRequest.getSortColumn());
                 searchResult.setSortDirection(searchRequest.getSortDirection());
                 searchResult.setStartAt(searchRequest.getStartAt());
                 if(searchRequest.getNeededTotalResults()!=null && searchRequest.getNeededTotalResults()){
                         //Get count of total rows if needed
             String regex = "^\\s*[Ss][Ee][Ll][Ee][Cc][Tt]\\s+([^,\\s]+)(.|[\r\n])*?\\s+[Ff][Rr][Oo][Mm]\\s+";
             String replacement = "SELECT COUNT(DISTINCT $1) FROM ";
             queryString = queryString.replaceAll("([Dd][Ii][Ss][Tt][Ii][Nn][Cc][Tt])", "");
                         String countQueryString = (queryString + optionalQueryString).replaceFirst(regex, replacement);
 
                         LOG.info("Executing query: "+countQueryString);
                         Query countQuery;
                         if(isNative){
                                 countQuery = em.createNativeQuery(countQueryString);
                         }else{
                                 countQuery = em.createQuery(countQueryString);
                         }
                         for (SearchParam searchParam : internalQueryParms) {
                                 countQuery.setParameter(searchParam.getKey().replace(".", "_"), searchParam.getValue());
                         }
             Integer totalRecords = 0;
             Object resultObject = countQuery.getSingleResult();
             if (resultObject instanceof BigDecimal) {
                 totalRecords = ((BigDecimal) resultObject).intValue();
             } else if (resultObject instanceof Long) {
                 totalRecords = ((Long) resultObject).intValue();
             }
             searchResult.setTotalResults(totalRecords);
                 }
 
                 return searchResult;
         }
         
         private List<SearchResultRow> convertToResults(List<?> queryResults,
                         SearchTypeInfo searchTypeInfo) {
                 List<SearchResultRow> results = new ArrayList<SearchResultRow>();
 
                 if(queryResults!=null){
                         //Copy the query results to a Result object
                         for(Object queryResult:queryResults){
                                 SearchResultRow result = new SearchResultRow();
                                 int i=0;
                                 for (ResultColumnInfo resultColumn : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()) {
                         
                                         SearchResultCell resultCell = new SearchResultCell();
                                         resultCell.setKey(resultColumn.getKey());
                                         
                                         try {
                                                 Object queryResultCell = null;
                                                 if(queryResult.getClass().isArray()){
                                                         queryResultCell = ((Object[])queryResult)[i];
                                                         
                                                 }else{
                                                         queryResultCell = queryResult;
                                                 }
                                                 
                                                 if (queryResultCell != null) {
                                                         resultCell.setValue(queryResultCell.toString());                                                        
                                                 }
                                         
                                         } catch (Exception e) {
                                                 throw new RuntimeException("Error copying results from " + queryResult.toString(),e);
                                         }
                                         
                                         result.getCells().add(resultCell);
                                         i++;
                                 }
                                 results.add(result);
                         }
                 }
                 return results;
         }
 
 }

1		/**
2		* Copyright 2010 The Kuali Foundation Licensed under the
3		* Educational Community License, Version 2.0 (the "License"); you may
4		* not use this file except in compliance with the License. You may
5		* obtain a copy of the License at
6		*
7		* http://www.osedu.org/licenses/ECL-2.0
8		*
9		* Unless required by applicable law or agreed to in writing,
10		* software distributed under the License is distributed on an "AS IS"
11		* BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
12		* or implied. See the License for the specific language governing
13		* permissions and limitations under the License.
14		*/
15
16		package org.kuali.student.common.dao.impl;
17
18		import java.math.BigDecimal;
19		import java.text.DateFormat;
20		import java.text.ParseException;
21		import java.text.SimpleDateFormat;
22		import java.util.ArrayList;
23		import java.util.Collection;
24		import java.util.List;
25		import java.util.Map;
26		import java.util.StringTokenizer;
27
28		import javax.persistence.Query;
29
30		import org.apache.log4j.Logger;
31		import org.kuali.student.common.dao.SearchableDao;
32		import org.kuali.student.common.search.dto.QueryParamInfo;
33		import org.kuali.student.common.search.dto.ResultColumnInfo;
34		import org.kuali.student.common.search.dto.SearchParam;
35		import org.kuali.student.common.search.dto.SearchRequest;
36		import org.kuali.student.common.search.dto.SearchResult;
37		import org.kuali.student.common.search.dto.SearchResultCell;
38		import org.kuali.student.common.search.dto.SearchResultRow;
39		import org.kuali.student.common.search.dto.SearchTypeInfo;
40		import org.kuali.student.common.search.dto.SortDirection;
41
42	0	public class AbstractSearchableCrudDaoImpl extends AbstractCrudDaoImpl
43		implements SearchableDao {
44	0	final Logger LOG = Logger.getLogger(AbstractSearchableCrudDaoImpl.class);
45
46	0	private static ThreadLocal<DateFormat> df = new ThreadLocal<DateFormat>() {
47		protected DateFormat initialValue() {
48	0	return new SimpleDateFormat("EEE MMM dd hh:mm:ss zzz yyyy");
49		}
50		};
51
52		@Override
53		public SearchResult search(SearchRequest searchRequest, Map<String, String> queryMap, SearchTypeInfo searchTypeInfo) {
54	0	String searchKey = searchRequest.getSearchKey();
55
56	0	boolean isNative = false;
57
58		//retrieve the SELECT statement from search type definition
59	0	String queryString = queryMap.get(searchKey);
60	0	String optionalQueryString = "";
61	0	if(null == queryString){
62	0	LOG.error("No SQL query was found for searchKey:"+searchKey);
63		}
64
65	0	if(queryString.toUpperCase().startsWith("NATIVE:")){
66	0	queryString = queryString.substring("NATIVE:".length());
67	0	isNative = true;
68		}
69
70		//add in optional
71	0	List<SearchParam> searchParamsTemp = new ArrayList<SearchParam>(searchRequest.getParams());
72		// internalQueryParms is used only internally to know which parameters have to be set in the query
73	0	List<SearchParam> internalQueryParms = new ArrayList<SearchParam>(searchRequest.getParams());
74	0	for(SearchParam searchParam : searchParamsTemp){
75	0	for(QueryParamInfo queryParam:searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams()){
76		// check to see if optional param has any values set.
77	0	if(queryParam.isOptional()&&queryParam.getKey().equals(searchParam.getKey())&&searchParam.getValue()!=null){
78	0	if(!optionalQueryString.isEmpty()){
79	0	optionalQueryString += " AND ";
80		}
81
82		//if optional query parameter has only a column name then create proper search expression
83	0	String condition = queryMap.get(searchParam.getKey());
84	0	if(condition==null){
85	0	throw new RuntimeException("Optional Param "+searchParam.getKey()+" must have a queryMap definition");
86		}
87	0	if (condition.trim().startsWith("!!")) {
88	0	String substitutionType = condition.trim().substring("!!".length());
89		// to detect queryMap value in the form of !!____ ___
90	0	if (condition.contains(" ")) {
91	0	substitutionType = condition.substring("!!".length(), condition.indexOf(" "));
92		}
93
94	0	if (substitutionType != null && substitutionType.equals("NUMBER_RANGE")) {
95	0	String realCondition = condition.substring("!!".length() + substitutionType.length()).trim();
96	0	String queryValue = (String)searchParam.getValue();
97		// if the query value is of the form n1 - n2
98	0	if (queryValue != null && queryValue.trim().contains("-")) {
99	0	StringTokenizer strTokenizer = new StringTokenizer(queryValue.trim(),"-");
100	0	if (strTokenizer.hasMoreElements()) {
101	0	String strNum1 = strTokenizer.nextToken().trim();
102	0	String strNum2 = strTokenizer.nextToken().trim();
103	0	optionalQueryString +=
104		realCondition +
105		" BETWEEN " + "'" + strNum1 + "'" + " AND " + "'" + strNum2 + "'";
106	0	internalQueryParms.remove(searchParam);
107		}
108	0	} else {
109		// the value is just one number
110	0	optionalQueryString += realCondition + " = '" + queryValue + "'";
111	0	internalQueryParms.remove(searchParam);
112		}
113		}
114	0	} else if (condition.trim().contains(":")) {
115		//this parameter is not entered by end user but rather it is set with a default context value
116	0	String dataType = queryParam.getFieldDescriptor().getDataType();
117	0	if ((dataType != null) && "boolean".equals(dataType)) {
118	0	optionalQueryString += queryMap.get(searchParam.getKey()).replace(":" + searchParam.getKey().replace(".", "_"), searchParam.getValue().toString());
119	0	internalQueryParms.remove(searchParam);
120		} else {
121	0	optionalQueryString += queryMap.get(searchParam.getKey());
122		}
123	0	} else {
124		//comparison should be case insensitive and should include wild card such that we match beginning of a text
125		//and each word within text
126		//FIXME SQL injection can occur here - or NOT if we need to assemble SQL to cover various ways one can compare criteria to a text
127	0	optionalQueryString +=
128		"(LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE LOWER(:"
129		+ searchParam.getKey().replace(".", "_") + ") \|\| '%' OR " +
130		"LOWER(" + queryMap.get(searchParam.getKey()) + ") LIKE '% ' \|\| LOWER(:"
131		+ searchParam.getKey().replace(".", "_") + ") \|\| '%')";
132		}
133	0	}
134		}
135		}
136
137		//Add in the where clause or And clause if needed for the optional criteria
138	0	if(!optionalQueryString.isEmpty()){
139	0	if(!queryString.toUpperCase().contains(" WHERE ")){
140	0	queryString += " WHERE ";
141		}
142		else {
143	0	queryString += " AND ";
144		}
145		}
146
147		//Do ordering
148	0	String orderByClause = "";
149	0	if(!queryString.toUpperCase().contains("ORDER BY")&&searchRequest.getSortColumn()!=null){
150		//make sure the sort column is a real result column
151	0	int i = 0;
152
153		//Get an array of the jpql results
154	0	int selectIndex = queryString.toLowerCase().indexOf("select")+"select".length();
155	0	int fromIndex = queryString.toLowerCase().indexOf(" from ");
156
157	0	if (selectIndex >= 0 && fromIndex > selectIndex){
158	0	String[] jpqlResultColumns = queryString.substring(selectIndex, fromIndex).replaceAll("\\s", "").split(",");
159	0	for(ResultColumnInfo results : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()){
160	0	if(results.getKey().equals(searchRequest.getSortColumn())){
161	0	if(results.getDataType()!=null && "string".equals(results.getDataType().toLowerCase())){
162	0	orderByClause = " ORDER BY LOWER(" + jpqlResultColumns[i] + ") ";
163		}else{
164		//Don't sort dates or numbers in alphabetic order or weirdness happens
165	0	orderByClause = " ORDER BY " + jpqlResultColumns[i] + " ";
166		}
167	0	if(searchRequest.getSortDirection()!=null && searchRequest.getSortDirection()==SortDirection.DESC){
168	0	orderByClause += "DESC ";
169		}else{
170	0	orderByClause += "ASC ";
171		}
172		}
173	0	i++;
174		}
175		}
176		}
177
178		//Create the query
179	0	String finalQueryString = queryString + optionalQueryString + orderByClause;
180
181		//remove special characters and extra spaces
182		//finalQueryString = finalQueryString.replaceAll("[\n\r\t]", " ");
183		//finalQueryString = finalQueryString.replaceAll("\\s+", " ");
184
185		Query query;
186	0	if(isNative){
187	0	LOG.info("Native Query:"+finalQueryString);
188	0	query = em.createNativeQuery(finalQueryString);
189		}else{
190	0	LOG.info("JPQL Query:"+finalQueryString);
191	0	query = em.createQuery(finalQueryString);
192		}
193
194		//Set the pagination information (eg. only return 25 rows starting at row 100)
195	0	if(searchRequest.getStartAt()!=null){
196	0	query.setFirstResult(searchRequest.getStartAt().intValue());
197		}
198	0	if(searchRequest.getMaxResults()!=null){
199	0	query.setMaxResults(searchRequest.getMaxResults().intValue());
200		}
201
202		//replace all the "." notation with "_" since the "."s in the ids of the queries will cause problems with the jpql
203	0	for (SearchParam searchParam : internalQueryParms) {
204		// check to see if optional param has any values set.
205	0	if (searchParam.getValue() != null) {
206	0	List<QueryParamInfo> queryParams = searchTypeInfo.getSearchCriteriaTypeInfo().getQueryParams();
207	0	String paramDataType = null;
208	0	if (queryParams != null) {
209	0	for (QueryParamInfo queryParam : queryParams) {
210	0	if (queryParam.getKey() != null && queryParam.getKey().equals(searchParam.getKey())) {
211	0	paramDataType = queryParam.getFieldDescriptor().getDataType();
212		}
213		}
214		}
215
216	0	Object queryParamValue = null;
217	0	if ("date".equals(paramDataType) && searchParam.getValue() instanceof String) {
218		try {
219	0	queryParamValue = df.get().parse((String)searchParam.getValue());
220	0	} catch (ParseException e) {
221	0	throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
222	0	}
223	0	} if ("long".equals(paramDataType)){
224	0	if(searchParam.getValue() instanceof String) {
225		try{
226	0	queryParamValue = Long.valueOf((String)searchParam.getValue());
227	0	} catch (NumberFormatException e) {
228	0	throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
229	0	}
230	0	}else if(searchParam.getValue() instanceof Collection){
231		try{
232	0	List<Long> longList = new ArrayList<Long>();
233	0	if(searchParam.getValue()!=null){
234	0	for(String value:(Collection<String>)searchParam.getValue()){
235	0	longList.add(Long.parseLong(value));
236		}
237		}
238	0	queryParamValue = longList;
239	0	} catch (NumberFormatException e) {
240	0	throw new RuntimeException("Failed to parse date value " + searchParam.getValue(),e);
241	0	}
242		}
243		} else {
244	0	queryParamValue = searchParam.getValue();
245		}
246		//Needed to get around Hibernate not supporting IN(:var) where var is null or an empty collection
247	0	if((queryParamValue==null\|\|queryParamValue instanceof Collection && ((Collection<?>)queryParamValue).isEmpty())&&"list".equals(paramDataType)){
248	0	queryParamValue = "";
249		}
250	0	query.setParameter(searchParam.getKey().replace(".", "_"), queryParamValue);
251	0	}
252		}
253
254		// Turn into results
255	0	List<SearchResultRow> results = convertToResults(query.getResultList(),searchTypeInfo);
256
257	0	SearchResult searchResult = new SearchResult();
258	0	searchResult.setRows(results);
259	0	searchResult.setSortColumn(searchRequest.getSortColumn());
260	0	searchResult.setSortDirection(searchRequest.getSortDirection());
261	0	searchResult.setStartAt(searchRequest.getStartAt());
262	0	if(searchRequest.getNeededTotalResults()!=null && searchRequest.getNeededTotalResults()){
263		//Get count of total rows if needed
264	0	String regex = "^\\s[Ss][Ee][Ll][Ee][Cc][Tt]\\s+([^,\\s]+)(.\|[\r\n])?\\s+[Ff][Rr][Oo][Mm]\\s+";
265	0	String replacement = "SELECT COUNT(DISTINCT $1) FROM ";
266	0	queryString = queryString.replaceAll("([Dd][Ii][Ss][Tt][Ii][Nn][Cc][Tt])", "");
267	0	String countQueryString = (queryString + optionalQueryString).replaceFirst(regex, replacement);
268
269	0	LOG.info("Executing query: "+countQueryString);
270		Query countQuery;
271	0	if(isNative){
272	0	countQuery = em.createNativeQuery(countQueryString);
273		}else{
274	0	countQuery = em.createQuery(countQueryString);
275		}
276	0	for (SearchParam searchParam : internalQueryParms) {
277	0	countQuery.setParameter(searchParam.getKey().replace(".", "_"), searchParam.getValue());
278		}
279	0	Integer totalRecords = 0;
280	0	Object resultObject = countQuery.getSingleResult();
281	0	if (resultObject instanceof BigDecimal) {
282	0	totalRecords = ((BigDecimal) resultObject).intValue();
283	0	} else if (resultObject instanceof Long) {
284	0	totalRecords = ((Long) resultObject).intValue();
285		}
286	0	searchResult.setTotalResults(totalRecords);
287		}
288
289	0	return searchResult;
290		}
291
292		private List<SearchResultRow> convertToResults(List<?> queryResults,
293		SearchTypeInfo searchTypeInfo) {
294	0	List<SearchResultRow> results = new ArrayList<SearchResultRow>();
295
296	0	if(queryResults!=null){
297		//Copy the query results to a Result object
298	0	for(Object queryResult:queryResults){
299	0	SearchResultRow result = new SearchResultRow();
300	0	int i=0;
301	0	for (ResultColumnInfo resultColumn : searchTypeInfo.getSearchResultTypeInfo().getResultColumns()) {
302
303	0	SearchResultCell resultCell = new SearchResultCell();
304	0	resultCell.setKey(resultColumn.getKey());
305
306		try {
307	0	Object queryResultCell = null;
308	0	if(queryResult.getClass().isArray()){
309	0	queryResultCell = ((Object[])queryResult)[i];
310
311		}else{
312	0	queryResultCell = queryResult;
313		}
314
315	0	if (queryResultCell != null) {
316	0	resultCell.setValue(queryResultCell.toString());
317		}
318
319	0	} catch (Exception e) {
320	0	throw new RuntimeException("Error copying results from " + queryResult.toString(),e);
321	0	}
322
323	0	result.getCells().add(resultCell);
324	0	i++;
325	0	}
326	0	results.add(result);
327	0	}
328		}
329	0	return results;
330		}
331
332		}