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}