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-2014 ForgeRock AS. 016 */ 017 018package org.forgerock.openig.filter; 019 020import static java.lang.String.*; 021import static org.forgerock.openig.log.LogLevel.*; 022import static org.forgerock.openig.util.Json.*; 023 024import java.io.IOException; 025import java.sql.Connection; 026import java.sql.PreparedStatement; 027import java.sql.ResultSet; 028import java.sql.ResultSetMetaData; 029import java.sql.SQLException; 030import java.util.ArrayList; 031import java.util.HashMap; 032import java.util.Iterator; 033import java.util.List; 034import java.util.Map; 035 036import javax.naming.InitialContext; 037import javax.naming.NamingException; 038import javax.sql.DataSource; 039 040import org.forgerock.json.fluent.JsonValue; 041import org.forgerock.json.fluent.JsonValueException; 042import org.forgerock.openig.el.Expression; 043import org.forgerock.openig.handler.Handler; 044import org.forgerock.openig.handler.HandlerException; 045import org.forgerock.openig.heap.GenericHeaplet; 046import org.forgerock.openig.heap.HeapException; 047import org.forgerock.openig.http.Exchange; 048import org.forgerock.util.Factory; 049import org.forgerock.util.LazyMap; 050 051/** 052 * Executes a SQL query through a prepared statement and exposes its first result. Parameters 053 * in the prepared statement are derived from exchange-scoped expressions. The query result is 054 * exposed in a {@link Map} object, whose location is specified by the {@code target} 055 * expression. If the query yields no result, then the resulting map will be empty. 056 * <p> 057 * The execution of the query is performed lazily; it does not occur until the first attempt 058 * to access a value in the target. This defers the overhead of connection pool, network 059 * and database query processing until a value is first required. This also means that the 060 * {@code parameters} expressions will not be evaluated until the map is first accessed. 061 * 062 * @see PreparedStatement 063 */ 064public class SqlAttributesFilter extends GenericFilter { 065 066 /** Expression that yields the target object that will contain the mapped results. */ 067 private final Expression target; 068 069 /** The factory for connections to the physical data source. */ 070 private final DataSource dataSource; 071 072 /** The parametrized SQL query to execute, with ? parameter placeholders. */ 073 private final String preparedStatement; 074 075 /** The list of parameters to evaluate and include in the execution of the prepared statement. */ 076 private final List<Expression> parameters = new ArrayList<Expression>(); 077 078 /** 079 * Builds a new SqlAttributesFilter that will execute the given SQL statement on the given {@link DataSource}, 080 * placing the results in a {@link Map} in the specified target. 081 * 082 * @param dataSource 083 * JDBC data source 084 * @param target 085 * Expression that yields the target object that will contain the mapped results 086 * @param preparedStatement 087 * The parametrized SQL query to execute, with ? parameter placeholders 088 */ 089 public SqlAttributesFilter(final DataSource dataSource, final Expression target, final String preparedStatement) { 090 this.dataSource = dataSource; 091 this.target = target; 092 this.preparedStatement = preparedStatement; 093 } 094 095 /** 096 * Returns the list of parameters to evaluate and include in the execution of the prepared statement. 097 * @return the list of parameters to evaluate and include in the execution of the prepared statement. 098 */ 099 public List<Expression> getParameters() { 100 return parameters; 101 } 102 103 @Override 104 public void filter(final Exchange exchange, Handler next) throws HandlerException, IOException { 105 target.set(exchange, new LazyMap<String, Object>(new Factory<Map<String, Object>>() { 106 @Override 107 public Map<String, Object> newInstance() { 108 HashMap<String, Object> result = new HashMap<String, Object>(); 109 Connection c = null; 110 try { 111 c = dataSource.getConnection(); 112 113 114 PreparedStatement ps = createPreparedStatement(c); 115 116 ResultSet rs = ps.executeQuery(); 117 if (rs.next()) { 118 ResultSetMetaData rsmd = rs.getMetaData(); 119 int columns = rsmd.getColumnCount(); 120 for (int n = 1; n <= columns; n++) { 121 result.put(rsmd.getColumnLabel(n), rs.getObject(n)); 122 } 123 } 124 if (logger.isLoggable(DEBUG)) { 125 logger.debug("Result: " + result); 126 } 127 rs.close(); 128 ps.close(); 129 } catch (SQLException sqle) { 130 // probably a config issue 131 logger.error(sqle); 132 } finally { 133 if (c != null) { 134 try { 135 c.close(); 136 } catch (SQLException sqle) { 137 // probably a network issue 138 logger.error(sqle); 139 } 140 } 141 } 142 return result; 143 } 144 145 private PreparedStatement createPreparedStatement(final Connection connection) throws SQLException { 146 logger.debug(format("PreparedStatement %s", preparedStatement)); 147 148 // probably cached in connection pool 149 PreparedStatement ps = connection.prepareStatement(preparedStatement); 150 151 // probably unnecessary but a safety precaution 152 ps.clearParameters(); 153 154 // Inject evaluated expression values into statement's placeholders 155 Iterator<Expression> expressions = parameters.iterator(); 156 int count = ps.getParameterMetaData().getParameterCount(); 157 for (int i = 0; i < count; i++) { 158 if (!expressions.hasNext()) { 159 // Got a statement parameter, but no expression to evaluate 160 logger.warning(format(" Placeholder %d has no provided value as parameter", i + 1)); 161 continue; 162 } 163 Object eval = expressions.next().eval(exchange); 164 ps.setObject(i + 1, eval); 165 logger.debug(format(" Placeholder #%d -> %s", i + 1, eval)); 166 } 167 168 // Output a warning if there are too many expressions compared to the number 169 // of parameters/placeholders in the prepared statement 170 if (expressions.hasNext()) { 171 logger.warning(format(" All parameters with index >= %d are ignored because there are " 172 + "no placeholders for them in the configured prepared statement (%s)", 173 count, 174 preparedStatement)); 175 } 176 return ps; 177 } 178 })); 179 next.handle(exchange); 180 } 181 182 /** Creates and initializes a static attribute provider in a heap environment. */ 183 public static class Heaplet extends GenericHeaplet { 184 @Override 185 public Object create() throws HeapException { 186 InitialContext ctx; 187 try { 188 ctx = new InitialContext(); 189 } catch (NamingException ne) { 190 throw new HeapException(ne); 191 } 192 DataSource source; 193 JsonValue dataSource = config.get("dataSource").required(); 194 try { 195 source = (DataSource) ctx.lookup(dataSource.asString()); 196 } catch (NamingException ne) { 197 throw new JsonValueException(dataSource, ne); 198 } catch (ClassCastException ne) { 199 throw new JsonValueException(dataSource, "expecting " + DataSource.class.getName() + " type"); 200 } 201 202 SqlAttributesFilter filter = new SqlAttributesFilter(source, 203 asExpression(config.get("target").required()), 204 config.get("preparedStatement").required().asString()); 205 206 if (config.isDefined("parameters")) { 207 filter.parameters.addAll(config.get("parameters").asList(ofExpression())); 208 } 209 return filter; 210 } 211 } 212}