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}