001 /*
002 * The MIT License
003 *
004 * Copyright (c) 2012-2013, 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 com.ninja_squad.dbsetup.bind.Binder;
028 import com.ninja_squad.dbsetup.bind.BinderConfiguration;
029 import com.ninja_squad.dbsetup.bind.Binders;
030 import com.ninja_squad.dbsetup.generator.ValueGenerator;
031 import com.ninja_squad.dbsetup.generator.ValueGenerators;
032 import com.ninja_squad.dbsetup.util.Preconditions;
033
034 import javax.annotation.Nonnull;
035 import javax.annotation.concurrent.Immutable;
036 import java.sql.Connection;
037 import java.sql.ParameterMetaData;
038 import java.sql.PreparedStatement;
039 import java.sql.SQLException;
040 import java.util.ArrayList;
041 import java.util.Arrays;
042 import java.util.HashMap;
043 import java.util.Iterator;
044 import java.util.LinkedHashMap;
045 import java.util.List;
046 import java.util.Map;
047
048 /**
049 * Operation which inserts one or several rows into a table. Example usage:
050 * <pre>
051 * Insert insert =
052 * Insert.into("CLIENT")
053 * .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
054 * .values(1L, "John", "Doe", "1975-07-19", ClientType.NORMAL)
055 * .values(2L, "Jack", "Smith", "1969-08-22", ClientType.HIGH_PRIORITY)
056 * .withDefaultValue("DELETED", false)
057 * .withDefaultValue("VERSION", 1)
058 * .withBinder(new ClientTypeBinder(), "CLIENT_TYPE")
059 * .build();
060 * </pre>
061 *
062 * The above operation will insert two rows inside the CLIENT table. For each row, the column DELETED will be set to
063 * <code>false</code> and the column VERSION will be set to 1. For the column CLIENT_TYPE, instead of using the
064 * {@link Binder} associated to the type of the column found in the metadata of the table, a custom binder will be used.
065 *
066 * @author JB Nizet
067 */
068 @Immutable
069 public final class Insert implements Operation {
070 private final String table;
071 private final List<String> columnNames;
072 private final Map<String, List<Object>> generatedValues;
073 private final List<List<?>> rows;
074 private final boolean metadataUsed;
075
076 private final Map<String, Binder> binders;
077
078 private Insert(Builder builder) {
079 this.table = builder.table;
080 this.columnNames = builder.columnNames;
081 this.rows = builder.rows;
082 this.generatedValues = generateValues(builder.valueGenerators, rows.size());
083 this.binders = builder.binders;
084 this.metadataUsed = builder.metadataUsed;
085 }
086
087 private Map<String, List<Object>> generateValues(Map<String, ValueGenerator<?>> valueGenerators,
088 int count) {
089 Map<String, List<Object>> result = new LinkedHashMap<String, List<Object>>();
090 for (Map.Entry<String, ValueGenerator<?>> entry : valueGenerators.entrySet()) {
091 result.put(entry.getKey(), generateValues(entry.getValue(), count));
092 }
093 return result;
094 }
095
096 private List<Object> generateValues(ValueGenerator<?> valueGenerator, int count) {
097 List<Object> result = new ArrayList<Object>(count);
098 for (int i = 0; i < count; i++) {
099 result.add(valueGenerator.nextValue());
100 }
101 return result;
102 }
103
104 /**
105 * Inserts the values and generated values in the table. Unless <code>useMetadata</code> has been set to
106 * <code>false</code>, the given configuration is used to get the appropriate binder. Nevertheless, if a binder
107 * has explicitely been associated to a given column, this binder will always be used for this column.
108 */
109 @edu.umd.cs.findbugs.annotations.SuppressWarnings(
110 value = "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING",
111 justification = "The point here is precisely to compose a SQL String from column names coming from the user")
112 @Override
113 public void execute(Connection connection, BinderConfiguration configuration) throws SQLException {
114 StringBuilder sql = new StringBuilder("insert into ").append(table).append(" (");
115
116 List<String> allColumnNames = new ArrayList<String>(columnNames);
117 allColumnNames.addAll(generatedValues.keySet());
118
119 for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
120 String columnName = it.next();
121 sql.append(columnName);
122 if (it.hasNext()) {
123 sql.append(", ");
124 }
125 }
126 sql.append(") values (");
127 for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
128 it.next();
129 sql.append("?");
130 if (it.hasNext()) {
131 sql.append(", ");
132 }
133 }
134 sql.append(")");
135
136 PreparedStatement stmt = connection.prepareStatement(sql.toString());
137
138 try {
139 Map<String, Binder> metadataBinders = new HashMap<String, Binder>();
140 if (metadataUsed) {
141 initializeBinders(stmt, allColumnNames, configuration, metadataBinders);
142 }
143
144 int rowIndex = 0;
145 for (List<?> row : rows) {
146 int i = 0;
147 for (Object value : row) {
148 String columnName = columnNames.get(i);
149 Binder binder = getBinder(columnName, metadataBinders);
150 binder.bind(stmt, i + 1, value);
151 i++;
152 }
153 for (Map.Entry<String, List<Object>> entry : generatedValues.entrySet()) {
154 String columnName = entry.getKey();
155 List<Object> rowValues = entry.getValue();
156 Binder binder = getBinder(columnName, metadataBinders);
157 binder.bind(stmt, i + 1, rowValues.get(rowIndex));
158 i++;
159 }
160
161 stmt.executeUpdate();
162 rowIndex++;
163 }
164 }
165 finally {
166 stmt.close();
167 }
168 }
169
170 private void initializeBinders(PreparedStatement stmt,
171 List<String> allColumnNames,
172 BinderConfiguration configuration,
173 Map<String, Binder> metadataBinders) throws SQLException {
174 ParameterMetaData metadata = stmt.getParameterMetaData();
175 int i = 1;
176 for (String columnName : allColumnNames) {
177 if (!this.binders.containsKey(columnName)) {
178 metadataBinders.put(columnName, configuration.getBinder(metadata, i));
179 }
180 i++;
181 }
182 }
183
184 private Binder getBinder(String columnName, Map<String, Binder> metadataBinders) {
185 Binder result = binders.get(columnName);
186 if (result == null) {
187 result = metadataBinders.get(columnName);
188 }
189 if (result == null) {
190 result = Binders.defaultBinder();
191 }
192 return result;
193 }
194
195 @Override
196 public String toString() {
197 return "insert into "
198 + table
199 + " [columns="
200 + columnNames
201 + ", generatedValues="
202 + generatedValues
203 + ", rows="
204 + rows
205 + ", metadataUsed="
206 + metadataUsed
207 + ", binders="
208 + binders
209 + "]";
210
211 }
212
213 @Override
214 public int hashCode() {
215 final int prime = 31;
216 int result = 1;
217 result = prime * result + binders.hashCode();
218 result = prime * result + columnNames.hashCode();
219 result = prime * result + generatedValues.hashCode();
220 result = prime * result + Boolean.valueOf(metadataUsed).hashCode();
221 result = prime * result + rows.hashCode();
222 result = prime * result + table.hashCode();
223 return result;
224 }
225
226 @Override
227 public boolean equals(Object obj) {
228 if (this == obj) {
229 return true;
230 }
231 if (obj == null) {
232 return false;
233 }
234 if (getClass() != obj.getClass()) {
235 return false;
236 }
237 Insert other = (Insert) obj;
238
239 return binders.equals(other.binders)
240 && columnNames.equals(other.columnNames)
241 && generatedValues.equals(other.generatedValues)
242 && metadataUsed == other.metadataUsed
243 && rows.equals(other.rows)
244 && table.equals(other.table);
245 }
246
247 /**
248 * Creates a new Builder instance, in order to build an Insert operation into the given table
249 * @param table the name of the table to insert into
250 * @return the created Builder
251 */
252 public static Builder into(@Nonnull String table) {
253 Preconditions.checkNotNull(table, "table may not be null");
254 return new Builder(table);
255 }
256
257 /**
258 * A builder used to create an Insert operation. Such a builder may only be used once. Once it has built its Insert
259 * operation, all its methods throw an {@link IllegalStateException}.
260 * @see Insert#into(String)
261 * @author JB Nizet
262 */
263 public static final class Builder {
264 private final String table;
265 private final List<String> columnNames = new ArrayList<String>();
266 private final Map<String, ValueGenerator<?>> valueGenerators = new LinkedHashMap<String, ValueGenerator<?>>();
267 private final List<List<?>> rows = new ArrayList<List<?>>();
268
269 private boolean metadataUsed = true;
270 private final Map<String, Binder> binders = new HashMap<String, Binder>();
271
272 private boolean built;
273
274 private Builder(String table) {
275 this.table = table;
276 }
277
278 /**
279 * Specifies the list of columns into which values wil be inserted. The values must the be specified, after,
280 * using the {@link #values(Object...)} method.
281 * @param columns the names of the columns to insert into.
282 * @return this Builder instance, for chaining.
283 * @throws IllegalStateException if the Insert has already been built, or if this method has already been
284 * called, or if one of the given columns is also specified as one of the generated value columns.
285 */
286 public Builder columns(@Nonnull String... columns) {
287 Preconditions.checkState(!built, "The insert has already been built");
288 Preconditions.checkState(columnNames.isEmpty(), "columns have already been specified");
289 for (String column : columns) {
290 Preconditions.checkNotNull(column, "column may not be null");
291 Preconditions.checkState(!valueGenerators.containsKey(column),
292 "column "
293 + column
294 + " has already been specified as generated value column");
295 }
296 columnNames.addAll(Arrays.asList(columns));
297 return this;
298 }
299
300 /**
301 * Adds a row of values to insert.
302 * @param values the values to insert.
303 * @return this Builder instance, for chaining.
304 * @throws IllegalStateException if the Insert has already been built, or if the number of values doesn't match
305 * the number of columns.
306 */
307 public Builder values(@Nonnull Object... values) {
308 Preconditions.checkState(!built, "The insert has already been built");
309 Preconditions.checkArgument(values.length == columnNames.size(),
310 "The number of values doesn't match the number of columns");
311 rows.add(new ArrayList<Object>(Arrays.asList(values)));
312 return this;
313 }
314
315 /**
316 * Associates a Binder to one or several columns.
317 * @param binder the binder to use, regardless of the metadata, for the given columns
318 * @param columns the name of the columns to associate with the given Binder
319 * @return this Builder instance, for chaining.
320 * @throws IllegalStateException if the Insert has already been built, or if any of the given columns is not
321 * part of the columns or "generated value" columns.
322 */
323 public Builder withBinder(@Nonnull Binder binder, @Nonnull String... columns) {
324 Preconditions.checkState(!built, "The insert has already been built");
325 Preconditions.checkNotNull(binder, "binder may not be null");
326 for (String columnName : columns) {
327 Preconditions.checkArgument(this.columnNames.contains(columnName)
328 || this.valueGenerators.containsKey(columnName),
329 "column "
330 + columnName
331 + " is not one of the registered column names");
332 binders.put(columnName, binder);
333 }
334 return this;
335 }
336
337 /**
338 * Specifies a default value to be inserted in a column for all the rows inserted by the Insert operation.
339 * Calling this method is equivalent to calling
340 * <code>withGeneratedValue(column, ValueGenerators.constant(value))</code>
341 * @param column the name of the column
342 * @param value the default value to insert into the column
343 * @return this Builder instance, for chaining.
344 * @throws IllegalStateException if the Insert has already been built, or if the given column is part
345 * of the columns to insert.
346 */
347 public Builder withDefaultValue(@Nonnull String column, Object value) {
348 return withGeneratedValue(column, ValueGenerators.constant(value));
349 }
350
351 /**
352 * Allows the given column to be populated by a value generator, which will be called for every row of the
353 * Insert operation being built.
354 * @param column the name of the column
355 * @param valueGenerator the generator generating values for the given column of every row
356 * @return this Builder instance, for chaining.
357 * @throws IllegalStateException if the Insert has already been built, or if the given column is part
358 * of the columns to insert.
359 */
360 public Builder withGeneratedValue(@Nonnull String column, @Nonnull ValueGenerator<?> valueGenerator) {
361 Preconditions.checkState(!built, "The insert has already been built");
362 Preconditions.checkNotNull(column, "column may not be null");
363 Preconditions.checkNotNull(valueGenerator, "valueGenerator may not be null");
364 Preconditions.checkArgument(!columnNames.contains(column),
365 "column "
366 + column
367 + " is already listed in the list of column names");
368 valueGenerators.put(column, valueGenerator);
369 return this;
370 }
371
372 /**
373 * Determines if the metadata must be used to get the appropriate binder for each inserted column (except
374 * the ones which have been associated explicitely with a Binder). The default is <code>true</code>. The insert
375 * can be faster if set to <code>false</code>, but in this case, the {@link Binders#defaultBinder() default
376 * binder} will be used for all the columns (except the ones which have been associated explicitely with a
377 * Binder).
378 * @return this Builder instance, for chaining.
379 * @throws IllegalStateException if the Insert has already been built.
380 */
381 public Builder useMetadata(boolean useMetadata) {
382 Preconditions.checkState(!built, "The insert has already been built");
383 this.metadataUsed = useMetadata;
384 return this;
385 }
386
387 /**
388 * Builds the Insert operation.
389 * @return the created Insert operation.
390 * @throws IllegalStateException if the Insert has already been built, or if no column and no generated value
391 * column has been specified.
392 */
393 public Insert build() {
394 Preconditions.checkState(!built, "The insert has already been built");
395 Preconditions.checkState(!this.columnNames.isEmpty() || !this.valueGenerators.isEmpty(),
396 "no column and no generated value column has been specified");
397 built = true;
398 return new Insert(this);
399 }
400 }
401 }