Source code

001package com.identityworksllc.iiq.common.query;
002
003import sailpoint.object.Attributes;
004import sailpoint.object.SailPointObject;
005import sailpoint.tools.JdbcUtil;
006import sailpoint.tools.xml.AbstractXmlObject;
007import sailpoint.tools.xml.XMLObjectFactory;
008
009import java.io.ByteArrayInputStream;
010import java.io.IOException;
011import java.io.Reader;
012import java.sql.*;
013import java.time.Instant;
014import java.time.LocalDateTime;
015import java.time.OffsetDateTime;
016import java.util.HashMap;
017import java.util.List;
018
019/**
020 * Named parameter prepared statement wrapper, derived from various examples
021 * around the Internet. Rather than setting arguments by position, you can
022 * set them by name, the way Hibernate or certain JDBC drivers permit.
023 *
024 * Tokens have the form `:name` and must be alphanumeric.
025 *
026 * The same tokens can be re-used more than once in your query.
027 *
028 * ```
029 * NamedParameterStatement stmt = new NamedParameterStatement(
030 *        connection,
031 *        "select id from spt_identity where (extended3 = :username or name = :username) and extended4 = :department"
032 * );
033 *
034 * stmt.setString("username", someUsername);
035 * stmt.setString("department", someDepartment);
036 *
037 * // Use the statement like any other
038 * ```
039 *
040 * If you attempt to set a token that does not exist, such as `stmt.setString("xyz", str)` in the
041 * above example, the default behavior is to throw an {@link IllegalArgumentException}. You can use
042 * {@link #setAllowMissingAttributes(boolean)} to disable this behavior.
043 *
044 */
045@SuppressWarnings("unused")
046public final class NamedParameterStatement extends AbstractNamedParameterStatement<PreparedStatement> {
047
048        /**
049         * Creates a NamedParameterStatement. Wraps a call to {@link Connection#prepareStatement(java.lang.String)}.
050         * 
051         * @param connection the database connection
052         * @param query the parameterized query
053         * @throws SQLException if the statement could not be created
054         */
055        public NamedParameterStatement(Connection connection, String query) throws SQLException {
056                indexMap = new HashMap<>();
057                String parsedQuery = parse(query, indexMap);
058                this.connection = connection;
059                this.statement = connection.prepareStatement(parsedQuery);
060                this.allowMissingAttributes = false;
061        }
062
063        /**
064         * Adds the current row to the batch
065         * @throws SQLException The batch
066         */
067        public void addBatch() throws SQLException {
068                this.statement.addBatch();
069        }
070
071        /**
072         * Executes the statement.
073         *
074         * @return true if the first result is a {@link ResultSet}
075         * @throws SQLException if an error occurred
076         * @see PreparedStatement#execute()
077         */
078        public boolean execute() throws SQLException {
079                return statement.execute();
080        }
081
082        /**
083         * Executes the statement and returns a result set
084         * @return The result set
085         * @throws SQLException on errors
086         */
087        public ResultSet executeQuery() throws SQLException {
088                return statement.executeQuery();
089        }
090
091        /**
092         * Executes the statement and returns the update count
093         * @return The update count
094         * @throws SQLException on errors
095         */
096        public int executeUpdate() throws SQLException {
097                return statement.executeUpdate();
098        }
099
100        /**
101         * Sets a parameter to an array. Note that this is not supported in all database engines (particularly, not in MySQL)
102         * @param name The name of the field
103         * @param value The list to change to an array
104         * @throws SQLException if a failure generating the array occurs
105         */
106        public void setArray(String name, List<String> value) throws SQLException {
107                if (value == null) {
108                        setNull(name, Types.ARRAY);
109                } else {
110                        int[] indexes = getIndexes(name);
111                        for (int idx : indexes) {
112                                Array array = connection.createArrayOf("VARCHAR", value.toArray());
113                                statement.setArray(idx, array);
114                        }
115                }
116        }
117
118        /**
119         * Sets the given byte array as a Blob input to the statement
120         * @param name The parameter name
121         * @param blob The blob
122         * @throws SQLException on failures
123         */
124        public void setBlob(String name, Blob blob) throws SQLException {
125                if (blob == null) {
126                        setNull(name, Types.BLOB);
127                } else {
128                        int[] indexes = getIndexes(name);
129                        for (int idx : indexes) {
130                                statement.setBlob(idx, blob);
131                        }
132                }
133        }
134
135        /**
136         * Sets the given byte array as a Blob input to the statement
137         * @param name The parameter name
138         * @param blob The blob
139         * @throws SQLException on failures setting the blob or reading the byte array
140         */
141        public void setBlob(String name, byte[] blob) throws SQLException {
142                if (blob == null) {
143                        setNull(name, Types.BLOB);
144                } else {
145                        int[] indexes = getIndexes(name);
146                        for (int idx : indexes) {
147                                try (ByteArrayInputStream bais = new ByteArrayInputStream(blob)) {
148                                        statement.setBlob(idx, bais);
149                                } catch (IOException e) {
150                                        throw new SQLException(e);
151                                }
152                        }
153                }
154        }
155
156        /**
157         * Sets a parameter to a clob
158         * @param name The named parameter
159         * @param clob The clob to set
160         * @throws SQLException on failures
161         */
162        public void setClob(String name, Reader clob) throws SQLException {
163                if (clob == null) {
164                        setNull(name, Types.CLOB);
165                } else {
166                        int[] indexes = getIndexes(name);
167                        for (int idx : indexes) {
168                                // What does this do if there is more than one? Readers
169                                // can't usually be read twice. Do we want to copy the Reader?
170                                statement.setClob(idx, clob);
171                        }
172                }
173        }
174
175        /**
176         * Sets a parameter to a clob
177         * @param name The named parameter
178         * @param clob The clob to set
179         * @throws SQLException on failures
180         */
181        public void setClob(String name, String clob) throws SQLException {
182                if (clob == null) {
183                        setNull(name, Types.CLOB);
184                } else {
185                        int[] indexes = getIndexes(name);
186                        for (int idx : indexes) {
187                                if (JdbcUtil.isOracle(statement)) {
188                                        JdbcUtil.setOracleCLOBParameter(statement, idx, clob);
189                                } else {
190                                        JdbcUtil.setClobParameter(statement, idx, clob);
191                                }
192                        }
193                }
194        }
195
196
197        /**
198         * Sets a parameter to a clob
199         * @param name The named parameter
200         * @param clob The clob to set
201         * @throws SQLException on failures
202         */
203        public void setClob(String name, Clob clob) throws SQLException {
204                if (clob == null) {
205                        setNull(name, Types.CLOB);
206                } else {
207                        int[] indexes = getIndexes(name);
208                        for (int idx : indexes) {
209                                statement.setClob(idx, clob);
210                        }
211                }
212        }
213
214        /**
215         * Sets a parameter to a Java time instant.
216         *
217         * @param name parameter name
218         * @param value parameter value
219         * @throws SQLException if an error occurred
220         * @throws IllegalArgumentException if the parameter does not exist
221         * @see PreparedStatement#setDate(int, Date)
222         */
223        public void setDate(String name, LocalDateTime value) throws SQLException {
224                if (value == null) {
225                        setNull(name, Types.DATE);
226                } else {
227                        Date localDate = new Date(value.toEpochSecond(OffsetDateTime.now().getOffset()));
228                        setDate(name, localDate);
229                }
230        }
231
232
233        /**
234         * Sets a parameter to a Java time instant.
235         *
236         * @param name parameter name
237         * @param value parameter value
238         * @throws SQLException if an error occurred
239         * @throws IllegalArgumentException if the parameter does not exist
240         * @see PreparedStatement#setDate(int, Date)
241         */
242        public void setDate(String name, Instant value) throws SQLException {
243                if (value == null) {
244                        setNull(name, Types.DATE);
245                } else {
246                        setDate(name, new Date(value.toEpochMilli()));
247                }
248        }
249
250        /**
251         * Sets a parameter to a Java date.
252         *
253         * @param name parameter name
254         * @param value parameter value
255         * @throws SQLException if an error occurred
256         * @throws IllegalArgumentException if the parameter does not exist
257         * @see PreparedStatement#setDate(int, Date)
258         */
259        public void setDate(String name, java.util.Date value) throws SQLException {
260                if (value == null) {
261                        setNull(name, Types.DATE);
262                } else {
263                        java.sql.Date sqlDate = new java.sql.Date(value.getTime());
264                        int[] indexes = getIndexes(name);
265                        for (int idx : indexes) {
266                                statement.setDate(idx, sqlDate);
267                        }
268                }
269        }
270
271        /**
272         * Sets a parameter.
273         *
274         * @param name parameter name
275         * @param value parameter value
276         * @throws SQLException if an error occurred
277         * @throws IllegalArgumentException if the parameter does not exist
278         * @see PreparedStatement#setDate(int, Date)
279         */
280        public void setDate(String name, Date value) throws SQLException {
281                if (value == null) {
282                        setNull(name, Types.DATE);
283                } else {
284                        int[] indexes = getIndexes(name);
285                        for (int idx : indexes) {
286                                statement.setDate(idx, value);
287                        }
288                }
289        }
290
291        /**
292         * Sets a parameter.
293         *
294         * @param name parameter name
295         * @param value parameter value
296         * @throws SQLException if an error occurred
297         * @throws IllegalArgumentException if the parameter does not exist
298         * @see PreparedStatement#setDouble(int, double)
299         */
300        public void setDouble(String name, double value) throws SQLException {
301                int[] indexes = getIndexes(name);
302                for (int idx : indexes) {
303                        statement.setDouble(idx, value);
304                }
305        }
306
307        /**
308         * Sets a parameter.
309         *
310         * @param name parameter name
311         * @param value parameter value
312         * @throws SQLException if an error occurred
313         * @throws IllegalArgumentException if the parameter does not exist
314         * @see PreparedStatement#setInt(int, int)
315         */
316        public void setInt(String name, int value) throws SQLException {
317                int[] indexes = getIndexes(name);
318                for (int idx : indexes) {
319                        statement.setInt(idx, value);
320                }
321        }
322
323        /**
324         * Sets a parameter.
325         *
326         * @param name parameter name
327         * @param value parameter value
328         * @throws SQLException if an error occurred
329         * @throws IllegalArgumentException if the parameter does not exist
330         * @see PreparedStatement#setLong(int, long)
331         */
332        public void setLong(String name, long value) throws SQLException {
333                int[] indexes = getIndexes(name);
334                for (int idx : indexes) {
335                        statement.setLong(idx, value);
336                }
337        }
338
339        /**
340         * Sets a parameter to null, assuming the type to be VARCHAR.
341         *
342         * @param name parameter name
343         * @throws SQLException if an error occurred
344         * @throws IllegalArgumentException if the parameter does not exist
345         * @see PreparedStatement#setNull(int, int)
346         */
347        public void setNull(String name) throws SQLException {
348                setNull(name, Types.VARCHAR);
349        }
350
351        /**
352         * Sets a typed parameter to null
353         *
354         * @param name parameter name
355         * @param type The SQL type of the argument to set
356         * @throws SQLException if an error occurred
357         * @throws IllegalArgumentException if the parameter does not exist
358         * @see PreparedStatement#setNull(int, int)
359         */
360        public void setNull(String name, int type) throws SQLException {
361                int[] indexes = getIndexes(name);
362                for (int idx : indexes) {
363                        statement.setNull(idx, type);
364                }
365        }
366
367        /**
368         * Sets a parameter to the given object. If the object is a SailPointObject,
369         * the parameter will be set as to its 'id' as a string instead.
370         *
371         * @param name parameter name
372         * @param value parameter value
373         * @throws SQLException if an error occurred
374         * @throws IllegalArgumentException if the parameter does not exist
375         * @see PreparedStatement#setObject(int, java.lang.Object)
376         */
377        public void setObject(String name, Object value) throws SQLException {
378                if (value == null) {
379                        setNull(name, Types.JAVA_OBJECT);
380                } else if (value instanceof Attributes) {
381                        XMLObjectFactory f = XMLObjectFactory.getInstance();
382                        String xml = f.toXmlNoIndent(value);
383                        setClob(name, xml);
384                } else if (value instanceof SailPointObject) {
385                        setString(name, ((SailPointObject) value).getId());
386                } else if (value instanceof String) {
387                        setString(name, (String) value);
388                } else if (value instanceof Timestamp) {
389                        setTimestamp(name, (Timestamp)value);
390                } else if (value instanceof java.util.Date) {
391                        setDate(name, (java.util.Date) value);
392                } else if (value instanceof Integer) {
393                        setInt(name, (Integer)value);
394                } else if (value instanceof Long) {
395                        setLong(name, (Long)value);
396                } else {
397                        int[] indexes = getIndexes(name);
398                        for (int idx : indexes) {
399                                statement.setObject(idx, value);
400                        }
401                }
402        }
403
404        /**
405         * Sets a parameter.
406         *
407         * @param name parameter name
408         * @param value parameter value
409         * @throws SQLException if an error occurred
410         * @throws IllegalArgumentException if the parameter does not exist
411         * @see PreparedStatement#setString(int, java.lang.String)
412         */
413        public void setString(String name, String value) throws SQLException {
414                if (value == null) {
415                        setNull(name);
416                } else {
417                        int[] indexes = getIndexes(name);
418                        for (int idx : indexes) {
419                                statement.setString(idx, value);
420                        }
421                }
422        }
423
424        /**
425         * Sets a parameter.
426         *
427         * @param name parameter name
428         * @param value parameter value
429         * @throws SQLException if an error occurred
430         * @throws IllegalArgumentException if the parameter does not exist
431         * @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
432         */
433        public void setTimestamp(String name, Timestamp value) throws SQLException {
434                if (value == null) {
435                        setNull(name, Types.TIMESTAMP);
436                } else {
437                        int[] indexes = getIndexes(name);
438                        for (int idx : indexes) {
439                                statement.setTimestamp(idx, value);
440                        }
441                }
442        }
443
444        /**
445         * Serializes the input object into XML and then adds it to the query
446         * as a CLOB.
447         *
448         * @param name The field name
449         * @param xmlObject The XML object
450         * @throws SQLException if any failures occur
451         */
452        public void setXml(String name, Object xmlObject) throws SQLException {
453                XMLObjectFactory f = XMLObjectFactory.getInstance();
454                String xml = f.toXmlNoIndent(xmlObject);
455
456                setClob(name, xml);
457        }
458}