001    /*
002     * The MIT License
003     *
004     * Copyright (c) 2012, Ninja Squad
005     *
006     * Permission is hereby granted, free of charge, to any person obtaining a copy
007     * of this software and associated documentation files (the "Software"), to deal
008     * in the Software without restriction, including without limitation the rights
009     * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
010     * copies of the Software, and to permit persons to whom the Software is
011     * furnished to do so, subject to the following conditions:
012     *
013     * The above copyright notice and this permission notice shall be included in
014     * all copies or substantial portions of the Software.
015     *
016     * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
017     * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
018     * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
019     * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
020     * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
021     * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
022     * THE SOFTWARE.
023     */
024    
025    package com.ninja_squad.dbsetup.operation;
026    
027    import java.sql.Connection;
028    import java.sql.ParameterMetaData;
029    import java.sql.PreparedStatement;
030    import java.sql.SQLException;
031    import java.util.ArrayList;
032    import java.util.Arrays;
033    import java.util.HashMap;
034    import java.util.Iterator;
035    import java.util.LinkedHashMap;
036    import java.util.List;
037    import java.util.Map;
038    
039    import javax.annotation.Nonnull;
040    import javax.annotation.concurrent.Immutable;
041    
042    import com.ninja_squad.dbsetup.bind.Binder;
043    import com.ninja_squad.dbsetup.bind.BinderConfiguration;
044    import com.ninja_squad.dbsetup.bind.Binders;
045    import com.ninja_squad.dbsetup.util.Preconditions;
046    
047    /**
048     * Operation which inserts one or several rows into a table. Example usage:
049     * <pre>
050     *   Insert insert =
051     *       Insert.into("CLIENT")
052     *             .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
053     *             .values(1L, "John", "Doe", "1975-07-19", ClientType.NORMAL)
054     *             .values(2L, "Jack", "Smith", "1969-08-22", ClientType.HIGH_PRIORITY)
055     *             .withDefaultValue("DELETED", false)
056     *             .withDefaultValue("VERSION", 1)
057     *             .withBinder(new ClientTypeBinder(), "CLIENT_TYPE")
058     *             .build();
059     * </pre>
060     *
061     * The above operation will insert two rows inside the CLIENT table. For each row, the column DELETED will be set to
062     * <code>false</code> and the column VERSION will be set to 1. For the column CLIENT_TYPE, instead of using the
063     * {@link Binder} associated to the type of the column found in the metadata of the table, a custom binder will be used.
064     *
065     * @author JB Nizet
066     */
067    @Immutable
068    public final class Insert implements Operation {
069        private final String table;
070        private final List<String> columnNames;
071        private final Map<String, Object> defaultValues;
072        private final List<List<?>> rows;
073        private final boolean metadataUsed;
074    
075        private final Map<String, Binder> binders;
076    
077        private Insert(Builder builder) {
078            this.table = builder.table;
079            this.columnNames = builder.columnNames;
080            this.defaultValues = builder.defaultValues;
081            this.rows = builder.rows;
082            this.binders = builder.binders;
083            this.metadataUsed = builder.metadataUsed;
084        }
085    
086        /**
087         * Inserts the values and default values in the table. Unless <code>useMetadata</code> has been set to
088         * <code>false</code>, the given configuration is used to get the appropriate binder. Nevertheless, if a binder
089         * has explicitely been associated to a given column, this binder will always be used for this column.
090         */
091        @edu.umd.cs.findbugs.annotations.SuppressWarnings(
092            value = "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING",
093            justification = "The point here is precisely to compose a SQL String from column names coming from the user")
094        @Override
095        public void execute(Connection connection, BinderConfiguration configuration) throws SQLException {
096            StringBuilder sql = new StringBuilder("insert into ").append(table).append(" (");
097    
098            List<String> allColumnNames = new ArrayList<String>(columnNames);
099            allColumnNames.addAll(defaultValues.keySet());
100    
101            for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
102                String columnName = it.next();
103                sql.append(columnName);
104                if (it.hasNext()) {
105                    sql.append(", ");
106                }
107            }
108            sql.append(") values (");
109            for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
110                it.next();
111                sql.append("?");
112                if (it.hasNext()) {
113                    sql.append(", ");
114                }
115            }
116            sql.append(")");
117    
118            PreparedStatement stmt = connection.prepareStatement(sql.toString());
119    
120            try {
121                Map<String, Binder> metadataBinders = new HashMap<String, Binder>();
122                if (metadataUsed) {
123                    initializeBinders(stmt, allColumnNames, configuration, metadataBinders);
124                }
125    
126                for (List<?> row : rows) {
127                    int i = 0;
128                    for (Object value : row) {
129                        String columnName = columnNames.get(i);
130                        Binder binder = getBinder(columnName, metadataBinders);
131                        binder.bind(stmt, i + 1, value);
132                        i++;
133                    }
134                    for (Map.Entry<String, Object> defaultValue : defaultValues.entrySet()) {
135                        String columnName = defaultValue.getKey();
136                        Binder binder = getBinder(columnName, metadataBinders);
137                        binder.bind(stmt, i + 1, defaultValue.getValue());
138                        i++;
139                    }
140    
141                    stmt.executeUpdate();
142                }
143            }
144            finally {
145                stmt.close();
146            }
147        }
148    
149        private void initializeBinders(PreparedStatement stmt,
150                                       List<String> allColumnNames,
151                                       BinderConfiguration configuration,
152                                       Map<String, Binder> metadataBinders) throws SQLException {
153            ParameterMetaData metadata = stmt.getParameterMetaData();
154            int i = 1;
155            for (String columnName : allColumnNames) {
156                if (!this.binders.containsKey(columnName)) {
157                    metadataBinders.put(columnName, configuration.getBinder(metadata, i));
158                }
159                i++;
160            }
161        }
162    
163        private Binder getBinder(String columnName, Map<String, Binder> metadataBinders) {
164            Binder result = binders.get(columnName);
165            if (result == null) {
166                result = metadataBinders.get(columnName);
167            }
168            if (result == null) {
169                result = Binders.defaultBinder();
170            }
171            return result;
172        }
173    
174        @Override
175        public String toString() {
176            return "insert into "
177                   + table
178                   + " [columns="
179                   + columnNames
180                   + ", defaultValues="
181                   + defaultValues
182                   + ", rows="
183                   + rows
184                   + ", metadataUsed="
185                   + metadataUsed
186                   + ", binders="
187                   + binders
188                   + "]";
189    
190        }
191    
192        @Override
193        public int hashCode() {
194            final int prime = 31;
195            int result = 1;
196            result = prime * result + binders.hashCode();
197            result = prime * result + columnNames.hashCode();
198            result = prime * result + defaultValues.hashCode();
199            result = prime * result + Boolean.valueOf(metadataUsed).hashCode();
200            result = prime * result + rows.hashCode();
201            result = prime * result + table.hashCode();
202            return result;
203        }
204    
205        @Override
206        public boolean equals(Object obj) {
207            if (this == obj) {
208                return true;
209            }
210            if (obj == null) {
211                return false;
212            }
213            if (getClass() != obj.getClass()) {
214                return false;
215            }
216            Insert other = (Insert) obj;
217    
218            return binders.equals(other.binders)
219                   && columnNames.equals(other.columnNames)
220                   && defaultValues.equals(other.defaultValues)
221                   && metadataUsed == other.metadataUsed
222                   && rows.equals(other.rows)
223                   && table.equals(other.table);
224        }
225    
226        /**
227         * Creates a new Builder instance, in order to build an Insert operation into the given table
228         * @param table the name of the table to insert into
229         * @return the created Builder
230         */
231        public static Builder into(@Nonnull String table) {
232            Preconditions.checkNotNull(table, "table may not be null");
233            return new Builder(table);
234        }
235    
236        /**
237         * A builder used to create an Insert operation. Such a builder may only be used once. Once it has built its Insert
238         * operation, all its methods throw an {@link IllegalStateException}.
239         * @see Insert#into(String)
240         * @author JB Nizet
241         */
242        public static final class Builder {
243            private final String table;
244            private final List<String> columnNames = new ArrayList<String>();
245            private final Map<String, Object> defaultValues = new LinkedHashMap<String, Object>();
246            private final List<List<?>> rows = new ArrayList<List<?>>();
247    
248            private boolean metadataUsed = true;
249            private final Map<String, Binder> binders = new HashMap<String, Binder>();
250    
251            private boolean built;
252    
253            private Builder(String table) {
254                this.table = table;
255            }
256    
257            /**
258             * Specifies the list of columns into which values wil be inserted. The values must the be specifed, after,
259             * using the {@link #values(Object...)} method.
260             * @param columns the names of the columns to insert into.
261             * @return this Builder instance, for chaining.
262             * @throws IllegalStateException if the Insert has already been built, or if this method has already been
263             * called, or if one of the given columns is also specified as one of the "default value" columns.
264             */
265            public Builder columns(@Nonnull String... columns) {
266                Preconditions.checkState(!built, "The insert has already been built");
267                Preconditions.checkState(columnNames.isEmpty(), "columns have already been specified");
268                for (String column : columns) {
269                    Preconditions.checkNotNull(column, "column may not be null");
270                    Preconditions.checkState(!defaultValues.containsKey(column),
271                                             "column "
272                                                 + column
273                                                 + " has already been specified as default value column");
274                }
275                columnNames.addAll(Arrays.asList(columns));
276                return this;
277            }
278    
279            /**
280             * Adds a row of values to insert.
281             * @param values the values to insert.
282             * @return this Builder instance, for chaining.
283             * @throws IllegalStateException if the Insert has already been built, or if the number of values doesn't match
284             * the number of columns.
285             */
286            public Builder values(@Nonnull Object... values) {
287                Preconditions.checkState(!built, "The insert has already been built");
288                Preconditions.checkArgument(values.length == columnNames.size(),
289                                            "The number of values doesn't match the number of columns");
290                rows.add(new ArrayList<Object>(Arrays.asList(values)));
291                return this;
292            }
293    
294            /**
295             * Associates a Binder to one or several columns.
296             * @param binder the binder to use, regardless of the metadata, for the given columns
297             * @param columns the name of the columns to associate with the given Binder
298             * @return this Builder instance, for chaining.
299             * @throws IllegalStateException if the Insert has already been built, or if any of the given columns is not
300             * part of the columns or "default value" columns.
301             */
302            public Builder withBinder(@Nonnull Binder binder, @Nonnull String... columns) {
303                Preconditions.checkState(!built, "The insert has already been built");
304                Preconditions.checkNotNull(binder, "binder may not be null");
305                for (String columnName : columns) {
306                    Preconditions.checkArgument(this.columnNames.contains(columnName)
307                                                || this.defaultValues.containsKey(columnName),
308                                                "column "
309                                                    + columnName
310                                                    + " is not one of the registered column names");
311                    binders.put(columnName, binder);
312                }
313                return this;
314            }
315    
316            /**
317             * Specifies a default value to be inserted in a column for all the rows inserted by the Insert operation.
318             * @param column the name of the column
319             * @param value the default value to insert into the column
320             * @return this Builder instance, for chaining.
321             * @throws IllegalStateException if the Insert has already been built, or if the given column is part
322             * of the columns to insert.
323             */
324            public Builder withDefaultValue(@Nonnull String column, Object value) {
325                Preconditions.checkState(!built, "The insert has already been built");
326                Preconditions.checkNotNull(column, "column may not be null");
327                Preconditions.checkArgument(!columnNames.contains(column),
328                                            "column "
329                                                + column
330                                                + " is already listed in the list of column names");
331                defaultValues.put(column, value);
332                return this;
333            }
334    
335            /**
336             * Determines if the metadata must be used to get the appropriate binder for each inserted column (except
337             * the ones which have been associated explicitely with a Binder). The default is <code>true</code>. The insert
338             * can be faster if set to <code>false</code>, but in this case, the {@link Binders#defaultBinder() default
339             * binder} will be used for all the columns (except the ones which have been associated explicitely with a
340             * Binder).
341             * @return this Builder instance, for chaining.
342             * @throws IllegalStateException if the Insert has already been built.
343             */
344            public Builder useMetadata(boolean useMetadata) {
345                Preconditions.checkState(!built, "The insert has already been built");
346                this.metadataUsed = useMetadata;
347                return this;
348            }
349    
350            /**
351             * Builds the Insert operation.
352             * @return the created Insert operation.
353             * @throws IllegalStateException if the Insert has already been built, or if no column and no default value
354             * column has been specified.
355             */
356            public Insert build() {
357                Preconditions.checkState(!built, "The insert has already been built");
358                Preconditions.checkState(!this.columnNames.isEmpty() || !this.defaultValues.isEmpty(),
359                                         "no column and no default value column has been specified");
360                built = true;
361                return new Insert(this);
362            }
363        }
364    }