001/* 002 * The contents of this file are subject to the terms of the Common Development and 003 * Distribution License (the License). You may not use this file except in compliance with the 004 * License. 005 * 006 * You can obtain a copy of the License at legal/CDDLv1.0.txt. See the License for the 007 * specific language governing permission and limitations under the License. 008 * 009 * When distributing Covered Software, include this CDDL Header Notice in each file and include 010 * the License file at legal/CDDLv1.0.txt. If applicable, add the following below the CDDL 011 * Header, with the fields enclosed by brackets [] replaced by your own identifying 012 * information: "Portions Copyright [year] [name of copyright owner]". 013 * 014 * Copyright 2010-2011 ApexIdentity Inc. 015 * Portions Copyright 2011-2015 ForgeRock AS. 016 */ 017 018package org.forgerock.openig.filter; 019 020import static java.lang.String.format; 021import static org.forgerock.openig.el.Bindings.bindings; 022import static org.forgerock.openig.log.LogLevel.DEBUG; 023import static org.forgerock.openig.util.JsonValues.asExpression; 024import static org.forgerock.openig.util.JsonValues.ofExpression; 025 026import java.sql.Connection; 027import java.sql.PreparedStatement; 028import java.sql.ResultSet; 029import java.sql.ResultSetMetaData; 030import java.sql.SQLException; 031import java.util.ArrayList; 032import java.util.HashMap; 033import java.util.Iterator; 034import java.util.List; 035import java.util.Map; 036 037import javax.naming.InitialContext; 038import javax.naming.NamingException; 039import javax.sql.DataSource; 040 041import org.forgerock.http.Filter; 042import org.forgerock.http.Handler; 043import org.forgerock.http.protocol.Request; 044import org.forgerock.http.protocol.Response; 045import org.forgerock.json.JsonValue; 046import org.forgerock.json.JsonValueException; 047import org.forgerock.openig.el.Bindings; 048import org.forgerock.openig.el.Expression; 049import org.forgerock.openig.heap.GenericHeapObject; 050import org.forgerock.openig.heap.GenericHeaplet; 051import org.forgerock.openig.heap.HeapException; 052import org.forgerock.services.context.Context; 053import org.forgerock.util.Factory; 054import org.forgerock.util.LazyMap; 055import org.forgerock.util.promise.NeverThrowsException; 056import org.forgerock.util.promise.Promise; 057 058/** 059 * Executes a SQL query through a prepared statement and exposes its first result. Parameters 060 * in the prepared statement are derived from expressions. The query result is 061 * exposed in a {@link Map} object, whose location is specified by the {@code target} 062 * expression. If the query yields no result, then the resulting map will be empty. 063 * <p> 064 * The execution of the query is performed lazily; it does not occur until the first attempt 065 * to access a value in the target. This defers the overhead of connection pool, network 066 * and database query processing until a value is first required. This also means that the 067 * {@code parameters} expressions will not be evaluated until the map is first accessed. 068 * 069 * @see PreparedStatement 070 */ 071public class SqlAttributesFilter extends GenericHeapObject implements Filter { 072 073 /** Expression that yields the target object that will contain the mapped results. */ 074 @SuppressWarnings("rawtypes") 075 private final Expression<Map> target; 076 077 /** The factory for connections to the physical data source. */ 078 private final DataSource dataSource; 079 080 /** The parameterized SQL query to execute, with ? parameter placeholders. */ 081 private final String preparedStatement; 082 083 /** The list of parameters to evaluate and include in the execution of the prepared statement. */ 084 private final List<Expression<?>> parameters = new ArrayList<>(); 085 086 /** 087 * Builds a new SqlAttributesFilter that will execute the given SQL statement on the given {@link DataSource}, 088 * placing the results in a {@link Map} in the specified target. 089 * 090 * @param dataSource 091 * JDBC data source 092 * @param target 093 * Expression that yields the target object that will contain the mapped results 094 * @param preparedStatement 095 * The parameterized SQL query to execute, with ? parameter placeholders 096 */ 097 public SqlAttributesFilter(final DataSource dataSource, 098 @SuppressWarnings("rawtypes") final Expression<Map> target, 099 final String preparedStatement) { 100 this.dataSource = dataSource; 101 this.target = target; 102 this.preparedStatement = preparedStatement; 103 } 104 105 /** 106 * Returns the list of parameters to evaluate and include in the execution of the prepared statement. 107 * @return the list of parameters to evaluate and include in the execution of the prepared statement. 108 */ 109 public List<Expression<?>> getParameters() { 110 return parameters; 111 } 112 113 @Override 114 public Promise<Response, NeverThrowsException> filter(final Context context, 115 final Request request, 116 final Handler next) { 117 118 final Bindings bindings = bindings(context, request); 119 120 target.set(bindings, new LazyMap<>(new Factory<Map<String, Object>>() { 121 @Override 122 public Map<String, Object> newInstance() { 123 HashMap<String, Object> result = new HashMap<>(); 124 Connection c = null; 125 try { 126 c = dataSource.getConnection(); 127 128 129 PreparedStatement ps = createPreparedStatement(c); 130 131 ResultSet rs = ps.executeQuery(); 132 if (rs.next()) { 133 ResultSetMetaData rsmd = rs.getMetaData(); 134 int columns = rsmd.getColumnCount(); 135 for (int n = 1; n <= columns; n++) { 136 result.put(rsmd.getColumnLabel(n), rs.getObject(n)); 137 } 138 } 139 if (logger.isLoggable(DEBUG)) { 140 logger.debug("Result: " + result); 141 } 142 rs.close(); 143 ps.close(); 144 } catch (SQLException sqle) { 145 // probably a config issue 146 logger.error(sqle); 147 } finally { 148 if (c != null) { 149 try { 150 c.close(); 151 } catch (SQLException sqle) { 152 // probably a network issue 153 logger.error(sqle); 154 } 155 } 156 } 157 return result; 158 } 159 160 private PreparedStatement createPreparedStatement(final Connection connection) throws SQLException { 161 logger.debug(format("PreparedStatement %s", preparedStatement)); 162 163 // probably cached in connection pool 164 PreparedStatement ps = connection.prepareStatement(preparedStatement); 165 166 // probably unnecessary but a safety precaution 167 ps.clearParameters(); 168 169 // Inject evaluated expression values into statement's placeholders 170 Iterator<Expression<?>> expressions = parameters.iterator(); 171 int count = ps.getParameterMetaData().getParameterCount(); 172 for (int i = 0; i < count; i++) { 173 if (!expressions.hasNext()) { 174 // Got a statement parameter, but no expression to evaluate 175 logger.warning(format(" Placeholder %d has no provided value as parameter", i + 1)); 176 continue; 177 } 178 Object eval = expressions.next().eval(bindings); 179 ps.setObject(i + 1, eval); 180 logger.debug(format(" Placeholder #%d -> %s", i + 1, eval)); 181 } 182 183 // Output a warning if there are too many expressions compared to the number 184 // of parameters/placeholders in the prepared statement 185 if (expressions.hasNext()) { 186 logger.warning(format(" All parameters with index >= %d are ignored because there are " 187 + "no placeholders for them in the configured prepared statement (%s)", 188 count, 189 preparedStatement)); 190 } 191 return ps; 192 } 193 })); 194 return next.handle(context, request); 195 } 196 197 /** Creates and initializes a static attribute provider in a heap environment. */ 198 public static class Heaplet extends GenericHeaplet { 199 @Override 200 public Object create() throws HeapException { 201 InitialContext ctx; 202 try { 203 ctx = new InitialContext(); 204 } catch (NamingException ne) { 205 throw new HeapException(ne); 206 } 207 DataSource source; 208 JsonValue dataSource = config.get("dataSource").required(); 209 try { 210 source = (DataSource) ctx.lookup(dataSource.asString()); 211 } catch (NamingException ne) { 212 throw new JsonValueException(dataSource, ne); 213 } catch (ClassCastException ne) { 214 throw new JsonValueException(dataSource, "expecting " + DataSource.class.getName() + " type"); 215 } 216 217 @SuppressWarnings("rawtypes") 218 Expression<Map> targetExpr = asExpression(config.get("target").required(), Map.class); 219 SqlAttributesFilter filter = new SqlAttributesFilter(source, 220 targetExpr, 221 config.get("preparedStatement").required().asString()); 222 223 if (config.isDefined("parameters")) { 224 filter.parameters.addAll(config.get("parameters").asList(ofExpression())); 225 } 226 return filter; 227 } 228 } 229}