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 * If you use any of the constructors specifying auto-generated keys, you can retrieve the keys
045 * after execution using {@link #getGeneratedKeys()}.
046 *
047 */
048@SuppressWarnings("unused")
049public final class NamedParameterStatement extends AbstractNamedParameterStatement<PreparedStatement> {
050
051        /**
052         * Creates a NamedParameterStatement. Wraps a call to {@link Connection#prepareStatement(java.lang.String)}.
053         * 
054         * @param connection the database connection
055         * @param query the parameterized query
056         * @throws SQLException if the statement could not be created
057         */
058        public NamedParameterStatement(Connection connection, String query) throws SQLException {
059                indexMap = new HashMap<>();
060                String parsedQuery = parse(query, indexMap);
061                this.connection = connection;
062                this.statement = connection.prepareStatement(parsedQuery);
063                this.allowMissingAttributes = false;
064        }
065
066        /**
067         * Creates a NamedParameterStatement, specifying whether to return auto-generated keys using
068         * one of the constants from {@link Statement}.
069         *
070         * @param connection the database connection
071         * @param query the parameterized query
072         * @param autoGeneratedKeys the flag indicating whether auto-generated keys should be returned
073         * @throws SQLException if the statement could not be created
074         * @see Connection#prepareStatement(java.lang.String, int)
075         * @see Statement#RETURN_GENERATED_KEYS
076         * @see Statement#NO_GENERATED_KEYS
077         */
078        public NamedParameterStatement(Connection connection, String query, int autoGeneratedKeys) throws SQLException {
079                indexMap = new HashMap<>();
080                String parsedQuery = parse(query, indexMap);
081                this.connection = connection;
082                this.statement = connection.prepareStatement(parsedQuery, autoGeneratedKeys);
083                this.allowMissingAttributes = false;
084        }
085
086        /**
087         * Creates a NamedParameterStatement, specifying which auto-generated keys to return as a result set.
088         *
089         * @param connection the database connection
090         * @param query the parameterized query
091         * @param columnNames the names of the columns that should be returned
092         * @throws SQLException if the statement could not be created
093         * @see Connection#prepareStatement(java.lang.String, java.lang.String[])
094         * @see NamedParameterStatement#NamedParameterStatement(Connection, String, String[])
095         */
096        public NamedParameterStatement(Connection connection, String query, List<String> columnNames) throws SQLException {
097                this(connection, query, columnNames.toArray(new String[0]));
098        }
099
100        /**
101         * Creates a NamedParameterStatement, specifying which auto-generated keys to return as a result set.
102         *
103         * @param connection the database connection
104         * @param query the parameterized query
105         * @param columnNames the names of the columns that should be returned
106         * @throws SQLException if the statement could not be created
107         */
108        public NamedParameterStatement(Connection connection, String query, String[] columnNames) throws SQLException {
109                indexMap = new HashMap<>();
110                String parsedQuery = parse(query, indexMap);
111                this.connection = connection;
112                this.statement = connection.prepareStatement(parsedQuery, columnNames);
113                this.allowMissingAttributes = false;
114        }
115
116        /**
117         * Creates a NamedParameterStatement with the given query, result set type, concurrency, and holdability.
118         *
119         * The int parameters correspond to constants from {@link ResultSet}.
120         *
121         * @param connection the database connection
122         * @param query the parameterized query
123         * @param resultSetType the type of the result set to return
124         * @param resultSetConcurrency the concurrency of the result set
125         * @param resultSetHoldability the holdability of the result set
126         * @throws SQLException if the statement could not be created
127         * @see Connection#prepareStatement(java.lang.String, int, int, int)
128         */
129        public NamedParameterStatement(Connection connection, String query, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
130                indexMap = new HashMap<>();
131                String parsedQuery = parse(query, indexMap);
132                this.connection = connection;
133                this.statement = connection.prepareStatement(parsedQuery, resultSetType, resultSetConcurrency, resultSetHoldability);
134                this.allowMissingAttributes = false;
135        }
136
137        /**
138         * Adds the current row to the batch
139         * @throws SQLException The batch
140         */
141        public void addBatch() throws SQLException {
142                this.statement.addBatch();
143        }
144
145        /**
146         * Executes the statement.
147         *
148         * @return true if the first result is a {@link ResultSet}
149         * @throws SQLException if an error occurred
150         * @see PreparedStatement#execute()
151         */
152        public boolean execute() throws SQLException {
153                return statement.execute();
154        }
155
156        /**
157         * Executes the statement and returns a result set
158         * @return The result set
159         * @throws SQLException on errors
160         */
161        public ResultSet executeQuery() throws SQLException {
162                return statement.executeQuery();
163        }
164
165        /**
166         * Executes the statement and returns the update count
167         * @return The update count
168         * @throws SQLException on errors
169         */
170        public int executeUpdate() throws SQLException {
171                return statement.executeUpdate();
172        }
173
174        /**
175         * Sets a parameter to an array. Note that this is not supported in all database engines (particularly, not in MySQL)
176         * @param name The name of the field
177         * @param value The list to change to an array
178         * @throws SQLException if a failure generating the array occurs
179         */
180        public void setArray(String name, List<String> value) throws SQLException {
181                if (value == null) {
182                        setNull(name, Types.ARRAY);
183                } else {
184                        int[] indexes = getIndexes(name);
185                        for (int idx : indexes) {
186                                Array array = connection.createArrayOf("VARCHAR", value.toArray());
187                                statement.setArray(idx, array);
188                        }
189                }
190        }
191
192        /**
193         * Sets the given byte array as a Blob input to the statement
194         * @param name The parameter name
195         * @param blob The blob
196         * @throws SQLException on failures
197         */
198        public void setBlob(String name, Blob blob) throws SQLException {
199                if (blob == null) {
200                        setNull(name, Types.BLOB);
201                } else {
202                        int[] indexes = getIndexes(name);
203                        for (int idx : indexes) {
204                                statement.setBlob(idx, blob);
205                        }
206                }
207        }
208
209        /**
210         * Sets the given byte array as a Blob input to the statement
211         * @param name The parameter name
212         * @param blob The blob
213         * @throws SQLException on failures setting the blob or reading the byte array
214         */
215        public void setBlob(String name, byte[] blob) throws SQLException {
216                if (blob == null) {
217                        setNull(name, Types.BLOB);
218                } else {
219                        int[] indexes = getIndexes(name);
220                        for (int idx : indexes) {
221                                try (ByteArrayInputStream bais = new ByteArrayInputStream(blob)) {
222                                        statement.setBlob(idx, bais);
223                                } catch (IOException e) {
224                                        throw new SQLException(e);
225                                }
226                        }
227                }
228        }
229
230        /**
231         * Sets a parameter to a clob
232         * @param name The named parameter
233         * @param clob The clob to set
234         * @throws SQLException on failures
235         */
236        public void setClob(String name, Reader clob) throws SQLException {
237                if (clob == null) {
238                        setNull(name, Types.CLOB);
239                } else {
240                        int[] indexes = getIndexes(name);
241                        for (int idx : indexes) {
242                                // What does this do if there is more than one? Readers
243                                // can't usually be read twice. Do we want to copy the Reader?
244                                statement.setClob(idx, clob);
245                        }
246                }
247        }
248
249        /**
250         * Sets a parameter to a clob
251         * @param name The named parameter
252         * @param clob The clob to set
253         * @throws SQLException on failures
254         */
255        public void setClob(String name, String clob) throws SQLException {
256                if (clob == null) {
257                        setNull(name, Types.CLOB);
258                } else {
259                        int[] indexes = getIndexes(name);
260                        for (int idx : indexes) {
261                                if (JdbcUtil.isOracle(statement)) {
262                                        JdbcUtil.setOracleCLOBParameter(statement, idx, clob);
263                                } else {
264                                        JdbcUtil.setClobParameter(statement, idx, clob);
265                                }
266                        }
267                }
268        }
269
270
271        /**
272         * Sets a parameter to a clob
273         * @param name The named parameter
274         * @param clob The clob to set
275         * @throws SQLException on failures
276         */
277        public void setClob(String name, Clob clob) throws SQLException {
278                if (clob == null) {
279                        setNull(name, Types.CLOB);
280                } else {
281                        int[] indexes = getIndexes(name);
282                        for (int idx : indexes) {
283                                statement.setClob(idx, clob);
284                        }
285                }
286        }
287
288        /**
289         * Sets a parameter to a Java time instant.
290         *
291         * @param name parameter name
292         * @param value parameter value
293         * @throws SQLException if an error occurred
294         * @throws IllegalArgumentException if the parameter does not exist
295         * @see PreparedStatement#setDate(int, Date)
296         */
297        public void setDate(String name, LocalDateTime value) throws SQLException {
298                if (value == null) {
299                        setNull(name, Types.DATE);
300                } else {
301                        Date localDate = new Date(value.toEpochSecond(OffsetDateTime.now().getOffset()));
302                        setDate(name, localDate);
303                }
304        }
305
306
307        /**
308         * Sets a parameter to a Java time instant.
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#setDate(int, Date)
315         */
316        public void setDate(String name, Instant value) throws SQLException {
317                if (value == null) {
318                        setNull(name, Types.DATE);
319                } else {
320                        setDate(name, new Date(value.toEpochMilli()));
321                }
322        }
323
324        /**
325         * Sets a parameter to a Java date.
326         *
327         * @param name parameter name
328         * @param value parameter value
329         * @throws SQLException if an error occurred
330         * @throws IllegalArgumentException if the parameter does not exist
331         * @see PreparedStatement#setDate(int, Date)
332         */
333        public void setDate(String name, java.util.Date value) throws SQLException {
334                if (value == null) {
335                        setNull(name, Types.DATE);
336                } else {
337                        java.sql.Date sqlDate = new java.sql.Date(value.getTime());
338                        int[] indexes = getIndexes(name);
339                        for (int idx : indexes) {
340                                statement.setDate(idx, sqlDate);
341                        }
342                }
343        }
344
345        /**
346         * Sets a parameter.
347         *
348         * @param name parameter name
349         * @param value parameter value
350         * @throws SQLException if an error occurred
351         * @throws IllegalArgumentException if the parameter does not exist
352         * @see PreparedStatement#setDate(int, Date)
353         */
354        public void setDate(String name, Date value) throws SQLException {
355                if (value == null) {
356                        setNull(name, Types.DATE);
357                } else {
358                        int[] indexes = getIndexes(name);
359                        for (int idx : indexes) {
360                                statement.setDate(idx, value);
361                        }
362                }
363        }
364
365        /**
366         * Sets a parameter.
367         *
368         * @param name parameter name
369         * @param value parameter value
370         * @throws SQLException if an error occurred
371         * @throws IllegalArgumentException if the parameter does not exist
372         * @see PreparedStatement#setDouble(int, double)
373         */
374        public void setDouble(String name, double value) throws SQLException {
375                int[] indexes = getIndexes(name);
376                for (int idx : indexes) {
377                        statement.setDouble(idx, value);
378                }
379        }
380
381        /**
382         * Sets a parameter.
383         *
384         * @param name parameter name
385         * @param value parameter value
386         * @throws SQLException if an error occurred
387         * @throws IllegalArgumentException if the parameter does not exist
388         * @see PreparedStatement#setInt(int, int)
389         */
390        public void setInt(String name, int value) throws SQLException {
391                int[] indexes = getIndexes(name);
392                for (int idx : indexes) {
393                        statement.setInt(idx, value);
394                }
395        }
396
397        /**
398         * Sets a parameter.
399         *
400         * @param name parameter name
401         * @param value parameter value
402         * @throws SQLException if an error occurred
403         * @throws IllegalArgumentException if the parameter does not exist
404         * @see PreparedStatement#setLong(int, long)
405         */
406        public void setLong(String name, long value) throws SQLException {
407                int[] indexes = getIndexes(name);
408                for (int idx : indexes) {
409                        statement.setLong(idx, value);
410                }
411        }
412
413        /**
414         * Sets a parameter to null, assuming the type to be VARCHAR.
415         *
416         * @param name parameter name
417         * @throws SQLException if an error occurred
418         * @throws IllegalArgumentException if the parameter does not exist
419         * @see PreparedStatement#setNull(int, int)
420         */
421        public void setNull(String name) throws SQLException {
422                setNull(name, Types.VARCHAR);
423        }
424
425        /**
426         * Sets a typed parameter to null
427         *
428         * @param name parameter name
429         * @param type The SQL type of the argument to set
430         * @throws SQLException if an error occurred
431         * @throws IllegalArgumentException if the parameter does not exist
432         * @see PreparedStatement#setNull(int, int)
433         */
434        public void setNull(String name, int type) throws SQLException {
435                int[] indexes = getIndexes(name);
436                for (int idx : indexes) {
437                        statement.setNull(idx, type);
438                }
439        }
440
441        /**
442         * Sets a parameter to the given object. If the object is a SailPointObject,
443         * the parameter will be set as to its 'id' as a string instead.
444         *
445         * @param name parameter name
446         * @param value parameter value
447         * @throws SQLException if an error occurred
448         * @throws IllegalArgumentException if the parameter does not exist
449         * @see PreparedStatement#setObject(int, java.lang.Object)
450         */
451        public void setObject(String name, Object value) throws SQLException {
452                if (value == null) {
453                        setNull(name, Types.JAVA_OBJECT);
454                } else if (value instanceof Attributes) {
455                        XMLObjectFactory f = XMLObjectFactory.getInstance();
456                        String xml = f.toXmlNoIndent(value);
457                        setClob(name, xml);
458                } else if (value instanceof SailPointObject) {
459                        setString(name, ((SailPointObject) value).getId());
460                } else if (value instanceof String) {
461                        setString(name, (String) value);
462                } else if (value instanceof Timestamp) {
463                        setTimestamp(name, (Timestamp)value);
464                } else if (value instanceof java.util.Date) {
465                        setDate(name, (java.util.Date) value);
466                } else if (value instanceof Integer) {
467                        setInt(name, (Integer)value);
468                } else if (value instanceof Long) {
469                        setLong(name, (Long)value);
470                } else {
471                        int[] indexes = getIndexes(name);
472                        for (int idx : indexes) {
473                                statement.setObject(idx, value);
474                        }
475                }
476        }
477
478        /**
479         * Sets a parameter.
480         *
481         * @param name parameter name
482         * @param value parameter value
483         * @throws SQLException if an error occurred
484         * @throws IllegalArgumentException if the parameter does not exist
485         * @see PreparedStatement#setString(int, java.lang.String)
486         */
487        public void setString(String name, String value) throws SQLException {
488                if (value == null) {
489                        setNull(name);
490                } else {
491                        int[] indexes = getIndexes(name);
492                        for (int idx : indexes) {
493                                statement.setString(idx, value);
494                        }
495                }
496        }
497
498        /**
499         * Sets a parameter.
500         *
501         * @param name parameter name
502         * @param value parameter value
503         * @throws SQLException if an error occurred
504         * @throws IllegalArgumentException if the parameter does not exist
505         * @see PreparedStatement#setTimestamp(int, java.sql.Timestamp)
506         */
507        public void setTimestamp(String name, Timestamp value) throws SQLException {
508                if (value == null) {
509                        setNull(name, Types.TIMESTAMP);
510                } else {
511                        int[] indexes = getIndexes(name);
512                        for (int idx : indexes) {
513                                statement.setTimestamp(idx, value);
514                        }
515                }
516        }
517
518        /**
519         * Serializes the input object into XML and then adds it to the query
520         * as a CLOB.
521         *
522         * @param name The field name
523         * @param xmlObject The XML object
524         * @throws SQLException if any failures occur
525         */
526        public void setXml(String name, Object xmlObject) throws SQLException {
527                XMLObjectFactory f = XMLObjectFactory.getInstance();
528                String xml = f.toXmlNoIndent(xmlObject);
529
530                setClob(name, xml);
531        }
532}