/**
* Licensed to Apereo under one or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information regarding copyright ownership. Apereo
* licenses this file to you under the Apache 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 the
* following location:
*
* <p>http://www.apache.org/licenses/LICENSE-2.0
*
* <p>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.apereo.portal.portlets.sqlquery;
import java.util.List;
import java.util.Map;
import javax.portlet.PortletPreferences;
import javax.portlet.PortletRequest;
import javax.portlet.RenderRequest;
import javax.portlet.RenderResponse;
import javax.sql.DataSource;
import net.sf.ehcache.Cache;
import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Element;
import org.apache.commons.lang.StringUtils;
import org.apereo.portal.jpa.BasePortalJpaDao;
import org.apereo.portal.portlet.IPortletSpELService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.portlet.ModelAndView;
import org.springframework.web.portlet.mvc.AbstractController;
/**
* This portlet executes a (configurable) SQL query against a (configurable) DataSource accessed via
* the Spring application context, translates the ResultSet into a collection of row Map objects,
* and feeds that object to a (configurable) JSP page.
*
* <p>The SQL Query can substitute attributes from the request, user attributes, or spring beans by
* using Spring Expression Language (SpEL) patterns against the request object, user attributes, or
* a bean (@MyBeanName). It's recommended to provide a default value using the Elvis operator (?:)
* in case the attribute is undefined which results in no value. Some example queries using SpEL
* are:
*
* <pre>
* select * from EB_CONTACT_TABLE where pidm = ${userInfo['pidm']?:0} and standard_priority<>0 order by standard_priority
* select * from UP_USER where user_name='${userInfo['user.login.id']?:''}'
* select '${@PortalDb.class.toString()}' as className from up_user where user_name='admin';
* </pre>
*
* This portlet is useful for exposing results of a simple DB query as a single page for users.
*
*/
public class SqlQueryPortletController extends AbstractController {
/**
* The name of the cache to use for sql results. Defaults to DEFAULT_CACHE_NAME. User should set
* to empty string to disable query results caching. See portlet.xml.
*/
public static final String PREF_CACHE_NAME = "cacheName";
public static final String DEFAULT_CACHE_NAME =
"org.apereo.portal.portlets.sqlquery.SqlQueryPortletController.queryResults";
/**
* The bean name of the DataSource against which this portlet will execute the SQL query is
* specified as a portlet preference parameter named "dataSource". This parameter is optional,
* defaulting to the uPortal DataSource (PortalDb).
*/
public static final String DATASOURCE_BEAN_NAME_PARAM_NAME = "dataSource";
/**
* The SQL query this portlet will execute is specified as a portlet preference parameter named
* "sql". This parameter is required.
*/
public static final String SQL_QUERY_PARAM_NAME = "sql";
public static final String VIEW_PARAM_NAME = "view";
private final Logger log = LoggerFactory.getLogger(this.getClass());
private IPortletSpELService portletSpELService;
@Autowired
public void setPortletSpELService(IPortletSpELService portletSpELService) {
this.portletSpELService = portletSpELService;
}
public String getPrefCacheName() {
return PREF_CACHE_NAME;
}
@Override
public ModelAndView handleRenderRequest(RenderRequest request, RenderResponse response)
throws Exception {
// find the configured SQL statement
PortletPreferences preferences = request.getPreferences();
String sqlQuery = preferences.getValue(SQL_QUERY_PARAM_NAME, null);
String dsName =
preferences.getValue(
DATASOURCE_BEAN_NAME_PARAM_NAME, BasePortalJpaDao.PERSISTENCE_UNIT_NAME);
String viewName = preferences.getValue(VIEW_PARAM_NAME, "jsp/SqlQuery/results");
// Allow substituting attributes from the request and userInfo objects using the SPEL ${} notation..
String spelSqlQuery = evaluateSpelExpression(sqlQuery, request);
List<Map<String, Object>> results = null;
String cacheKey = createCacheKey(spelSqlQuery, dsName);
Cache cache = getCache(request);
if (cache != null) {
Element cachedElement = cache.get(cacheKey);
if (cachedElement != null) {
log.debug(
"Cache hit. Returning item for query: {}, substituted query: {}, from cache {} for key {}",
sqlQuery,
spelSqlQuery,
cache.getName(),
cacheKey);
results = (List<Map<String, Object>>) cachedElement.getObjectValue();
}
}
if (results == null) {
// generate a JDBC template for the requested data source
DataSource ds = (DataSource) getApplicationContext().getBean(dsName);
JdbcTemplate template = new JdbcTemplate(ds);
// Execute the SQL query and build a results object. This result will consist of one
// rowname -> rowvalue map for each row in the result set
results = template.query(spelSqlQuery, new ColumnMapRowMapper());
log.debug("found {} results for query {}", results.size(), spelSqlQuery);
if (cache != null) {
log.debug(
"Adding SQL results to cache {}, query: {}, substituted query: {}",
cache.getName(),
sqlQuery,
spelSqlQuery);
Element cachedElement = new Element(cacheKey, results);
cache.put(cachedElement);
}
}
// build the model
ModelAndView modelandview = new ModelAndView(viewName);
modelandview.addObject("results", results);
return modelandview;
}
/**
* Substitute any SpEL expressions with values from the PortletRequest and other attributes
* added to the SpEL context.
*
* @param value SQL Query String with optional SpEL expressions in it
* @param request Portlet request
* @return SQL Query string with SpEL substitutions
*/
protected String evaluateSpelExpression(String value, PortletRequest request) {
if (StringUtils.isNotBlank(value)) {
String result = portletSpELService.parseString(value, request);
return result;
}
throw new IllegalArgumentException("SQL Query expression required");
}
/**
* Obtain the cache configured for this portlet instance.
*
* @param req Portlet request
* @return Cache configured for this portlet instance.
*/
private Cache getCache(PortletRequest req) {
String cacheName = req.getPreferences().getValue(PREF_CACHE_NAME, DEFAULT_CACHE_NAME);
if (StringUtils.isNotBlank(cacheName)) {
log.debug("Looking up cache '{}'", cacheName);
Cache cache = CacheManager.getInstance().getCache(cacheName);
if (cache == null) {
throw new RuntimeException(
"Unable to find cache named "
+ cacheName
+ ". Check portlet preference value "
+ PREF_CACHE_NAME
+ " and configuration in ehcache.xml");
}
return cache;
} else {
log.debug(
"Portlet preference {} set to empty string; disabling caching for this portlet instance",
PREF_CACHE_NAME);
return null;
}
}
/**
* Create a cache key that includes SQL query and datasource bean name.
*
* @param sqlQuery SQL Query (fully substituted)
* @param dsName datasource bean Name
* @return Generated Cache key
*/
private String createCacheKey(String sqlQuery, String dsName) {
return dsName + "-" + sqlQuery;
}
}