Java tutorial
/******************************************************************************* * Copyright (c) 2009 David Harrison. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v3.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/gpl-3.0.html * * Contributors: * David Harrison - initial API and implementation ******************************************************************************/ package com.sfs.whichdoctor.search; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Collection; import java.util.Map; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Required; import org.springframework.dao.DataAccessException; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.sfs.beans.BuilderBean; import com.sfs.beans.UserBean; import com.sfs.whichdoctor.beans.SearchBean; import com.sfs.whichdoctor.beans.SearchResultsBean; import com.sfs.whichdoctor.dao.BaseDAOImpl; import com.sfs.whichdoctor.search.sql.SqlHandler; /** * The Class SearchDAOImpl. */ public class SearchDAOImpl extends BaseDAOImpl implements SearchDAO { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(SearchDAOImpl.class); /** The sql handlers. */ private Map<String, SqlHandler> sqlHandlers; /** * Sets the sql handlers. * * @param sqlHandlersMap the sql handlers */ @Required public final void setSqlHandlers(final Map<String, SqlHandler> sqlHandlersMap) { this.sqlHandlers = sqlHandlersMap; } /** * Initiate. * * @param type the type * @param user the user * * @return the search bean */ public final SearchBean initiate(final String type, final UserBean user) { SearchBean search = null; if (type != null && this.sqlHandlers != null) { if (this.sqlHandlers.containsKey(type)) { SqlHandler handler = this.sqlHandlers.get(type); search = handler.initiate(user); } } return search; } /** * Search. * * @param search the search * * @return the search results bean * * @throws WhichDoctorSearchDaoException the which doctor search dao * exception */ public final SearchResultsBean search(final SearchBean search) throws WhichDoctorSearchDaoException { return search(search, new BuilderBean()); } /** * Search. * * @param search the search * @param loadDetails the load details * * @return the search results bean * * @throws WhichDoctorSearchDaoException the which doctor search dao * exception */ @SuppressWarnings("unchecked") public final SearchResultsBean search(final SearchBean search, final BuilderBean loadDetails) throws WhichDoctorSearchDaoException { if (this.sqlHandlers == null) { throw new WhichDoctorSearchDaoException("The search is not configured"); } if (search.getType() == null) { /* Incorrect type set in SearchBean */ dataLogger.error("No search type has been set"); throw new WhichDoctorSearchDaoException("No search type has been set"); } SqlHandler sqlHandler = null; if (this.sqlHandlers.containsKey(search.getType())) { sqlHandler = this.sqlHandlers.get(search.getType()); } if (sqlHandler == null) { throw new WhichDoctorSearchDaoException("No SQL handler found for this search"); } /* Build SQL WHERE statement */ if (search.getSearchArray() != null) { search.setSQLWhereComponents(search.getSQLArrayStatement(sqlHandler.getIdentifierColumn()), search.getAction(), search.getSearchArray(), search.getSearchArrayDescription()); } else { if (search.getSearchCriteria() != null && search.getSearchConstraints() != null) { Map<String[], Collection<Object>> fields = sqlHandler.construct(search.getSearchCriteria(), search.getSearchConstraints()); for (String[] key : fields.keySet()) { search.setSQLWhereComponents(key[0], search.getAction(), fields.get(key), key[1]); } } } final String sqlWHERE = search.getSearchSQL(); // System.out.println("- SQL where: " + sqlWHERE); final Collection<Object> parameters = search.getSearchParameters(); /* Set ordering system of returned results */ if (search.getOrderColumn() == null) { search.setOrderColumn(sqlHandler.getDefaultOrder()); } String sqlORDER = buildOrder(search); /* Set range of results */ if (search.getRequestedPage() == 0) { search.setRequestedPage(1); } String sqlLIMIT = buildLimit(search); /* Initalize SearchResultsBean to hold result */ SearchResultsBean searchResults = new SearchResultsBean(); // Fill with values from SearchBean searchResults.setOrderAscending(search.getOrderAscending()); searchResults.setOrderColumn(search.getOrderColumn()); searchResults.setRequestedPage(search.getRequestedPage()); searchResults.setType(search.getType()); searchResults.setLimit(search.getLimit()); searchResults.setSearchCriteria(search.getSearchCriteria()); searchResults.setSearchVectors(search.getSearchVectors()); if (loadDetails.getBoolean("RESULT_COUNT")) { /* Load the result count for this search */ StringBuffer countSQL = new StringBuffer(); countSQL.append(sqlHandler.getCountSql()); countSQL.append(sqlWHERE); dataLogger.info("SQL Query: " + countSQL.toString()); /* System.out.println("- SQL count: " + countSQL.toString()); */ try { JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader(); if (!sqlHandler.getDefaultConnection()) { jdbcTemplate = this.getIsbJdbcTemplate(); } final int totalResults = jdbcTemplate.queryForInt(countSQL.toString(), parameters.toArray()); searchResults.setTotalResults(totalResults); } catch (DataAccessException de) { dataLogger.error("Error getting search result count: " + de.getMessage()); } } // BUILD SQL Statement StringBuffer searchSQL = new StringBuffer(); searchSQL.append(sqlHandler.getSelectSql()); searchSQL.append(sqlWHERE); searchSQL.append(sqlHandler.getGroupBy()); searchSQL.append(sqlORDER); searchSQL.append(sqlLIMIT); dataLogger.info("SQL Query: " + searchSQL.toString()); /* System.out.println("- SQL search: " + searchSQL.toString()); */ Collection<Integer> objects = null; try { JdbcTemplate jdbcTemplate = this.getJdbcTemplateReader(); if (!sqlHandler.getDefaultConnection()) { jdbcTemplate = this.getIsbJdbcTemplate(); } objects = jdbcTemplate.query(searchSQL.toString(), parameters.toArray(), new RowMapper() { public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException { return rs.getInt(1); } }); } catch (IncorrectResultSizeDataAccessException ie) { dataLogger.debug("No results found for this search: " + ie.getMessage()); } if (objects != null) { // Pass the results to the handler and load the referenced objects searchResults.setSearchResults(sqlHandler.load(objects, loadDetails)); } return searchResults; } /** * Builds the order. * * @param search the search * * @return the string */ private String buildOrder(final SearchBean search) { StringBuffer order = new StringBuffer(); order.append(" ORDER BY "); order.append(search.getOrderColumn()); if (!search.getOrderAscending()) { order.append(" DESC"); } else { order.append(" ASC"); } if (search.getOrderColumn2() != null) { if (search.getOrderColumn2().compareTo("") != 0) { order.append(", " + search.getOrderColumn2()); if (!search.getOrderAscending()) { order.append(" DESC"); } else { order.append(" ASC"); } if (search.getOrderColumn3() != null) { if (search.getOrderColumn3().compareTo("") != 0) { order.append(", " + search.getOrderColumn3()); if (!search.getOrderAscending()) { order.append(" DESC"); } else { order.append(" ASC"); } } } } } return order.toString(); } /** * Builds the limit. * * @param search the search * * @return the string */ private String buildLimit(final SearchBean search) { StringBuffer limit = new StringBuffer(); if (search.getLimit() != 0) { // Determine first and last record requested final int record = (search.getRequestedPage() - 1) * search.getLimit(); limit.append(" LIMIT "); limit.append(record); limit.append(", "); limit.append(search.getLimit()); } return limit.toString(); } }