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.HashSet;
044 import java.util.Iterator;
045 import java.util.LinkedHashMap;
046 import java.util.List;
047 import java.util.Map;
048 import java.util.Set;
049
050 /**
051 * Operation which inserts one or several rows into a table. Example usage:
052 * <pre>
053 * Insert insert =
054 * Insert.into("CLIENT")
055 * .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
056 * .values(1L, "John", "Doe", "1975-07-19", ClientType.NORMAL)
057 * .values(2L, "Jack", "Smith", "1969-08-22", ClientType.HIGH_PRIORITY)
058 * .withDefaultValue("DELETED", false)
059 * .withDefaultValue("VERSION", 1)
060 * .withBinder(new ClientTypeBinder(), "CLIENT_TYPE")
061 * .build();
062 * </pre>
063 *
064 * The above operation will insert two rows inside the CLIENT table. For each row, the column DELETED will be set to
065 * <code>false</code> and the column VERSION will be set to 1. For the column CLIENT_TYPE, instead of using the
066 * {@link Binder} associated to the type of the column found in the metadata of the table, a custom binder will be used.
067 * <p>
068 * Instead of specifying values as an ordered sequence which must match the sequence of column names, some might prefer
069 * passing a map of column/value associations. This makes things more verbose, but can be more readable in some cases,
070 * when the number of columns is high. This also allows not specifying any value for columns that must stay null.
071 * The map can be constructed like any other map and passed to the builder, or it can be added using a fluent builder.
072 * The following snippet:
073 *
074 * <pre>
075 * Insert insert =
076 * Insert.into("CLIENT")
077 * .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
078 * .row().column("CLIENT_ID", 1L)
079 * .column("FIRST_NAME", "John")
080 * .column("LAST_NAME", "Doe")
081 * .column("DATE_OF_BIRTH", "1975-07-19")
082 * .end()
083 * .row().column("CLIENT_ID", 2L)
084 * .column("FIRST_NAME", "Jack")
085 * .column("LAST_NAME", "Smith")
086 * .end() // null date of birth, because it's not in the row
087 * .build();
088 * </pre>
089 *
090 * is thus equivalent to:
091 *
092 * <pre>
093 * Map<String, Object> johnDoe = new HashMap<String, Object>();
094 * johnDoe.put("CLIENT_ID", 1L);
095 * johnDoe.put("FIRST_NAME", "John");
096 * johnDoe.put("LAST_NAME", "Doe");
097 * johnDoe.put("DATE_OF_BIRTH", "1975-07-19");
098 *
099 * Map<String, Object> jackSmith = new HashMap<String, Object>();
100 * jackSmith.put("CLIENT_ID", 2L);
101 * jackSmith.put("FIRST_NAME", "Jack");
102 * jackSmith.put("LAST_NAME", "Smith");
103 *
104 * Insert insert =
105 * Insert.into("CLIENT")
106 * .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
107 * .values(johnDoe)
108 * .values(jackSmith)
109 * .build();
110 * </pre>
111 *
112 * When building the Insert using column/value associations, it might seem redundant to specify the set of column names
113 * before inserting the rows. Remember, though, that all the rows of an Insert are inserted using the same
114 * parameterized SQL query. We thus need a robust and easy way to know all the columns to insert for every row of the
115 * insert. To be able to spot errors easily and early, and to avoid complex rules, the rule is thus simple: the set of
116 * columns (excluding the generated ones) is specified either by columns(), or by the columns of the first row. All the
117 * subsequent rows may not have additional columns. And <code>null</code> is inserted for all the absent columns of the
118 * subsequent rows. The above example can thus be written as
119 *
120 * <pre>
121 * Insert insert =
122 * Insert.into("CLIENT")
123 * .row().column("CLIENT_ID", 1L)
124 * .column("FIRST_NAME", "John")
125 * .column("LAST_NAME", "Doe")
126 * .column("DATE_OF_BIRTH", "1975-07-19")
127 * .end()
128 * .row().column("CLIENT_ID", 2L)
129 * .column("FIRST_NAME", "Jack")
130 * .column("LAST_NAME", "Smith")
131 * .end() // null date of birth, because it's not in the row
132 * .build();
133 * </pre>
134 *
135 * but the following will throw an exception, because the DATE_OF_BIRTH column is not part of the first row:
136 *
137 * <pre>
138 * Insert insert =
139 * Insert.into("CLIENT")
140 * .row().column("CLIENT_ID", 2L)
141 * .column("FIRST_NAME", "Jack")
142 * .column("LAST_NAME", "Smith")
143 * .column("CLIENT_TYPE", ClientType.HIGH_PRIORITY)
144 * .end()
145 * .row().column("CLIENT_ID", 1L)
146 * .column("FIRST_NAME", "John")
147 * .column("LAST_NAME", "Doe")
148 * .column("DATE_OF_BIRTH", "1975-07-19")
149 * .column("CLIENT_TYPE", ClientType.NORMAL)
150 * .end()
151 * .build();
152 * </pre>
153 *
154 * @author JB Nizet
155 */
156 @Immutable
157 public final class Insert implements Operation {
158 private final String table;
159 private final List<String> columnNames;
160 private final Map<String, List<Object>> generatedValues;
161 private final List<List<?>> rows;
162 private final boolean metadataUsed;
163
164 private final Map<String, Binder> binders;
165
166 private Insert(Builder builder) {
167 this.table = builder.table;
168 this.columnNames = builder.columnNames;
169 this.rows = builder.rows;
170 this.generatedValues = generateValues(builder.valueGenerators, rows.size());
171 this.binders = builder.binders;
172 this.metadataUsed = builder.metadataUsed;
173 }
174
175 private Map<String, List<Object>> generateValues(Map<String, ValueGenerator<?>> valueGenerators,
176 int count) {
177 Map<String, List<Object>> result = new LinkedHashMap<String, List<Object>>();
178 for (Map.Entry<String, ValueGenerator<?>> entry : valueGenerators.entrySet()) {
179 result.put(entry.getKey(), generateValues(entry.getValue(), count));
180 }
181 return result;
182 }
183
184 private List<Object> generateValues(ValueGenerator<?> valueGenerator, int count) {
185 List<Object> result = new ArrayList<Object>(count);
186 for (int i = 0; i < count; i++) {
187 result.add(valueGenerator.nextValue());
188 }
189 return result;
190 }
191
192 /**
193 * Inserts the values and generated values in the table. Unless <code>useMetadata</code> has been set to
194 * <code>false</code>, the given configuration is used to get the appropriate binder. Nevertheless, if a binder
195 * has explicitely been associated to a given column, this binder will always be used for this column.
196 */
197 @edu.umd.cs.findbugs.annotations.SuppressWarnings(
198 value = "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING",
199 justification = "The point here is precisely to compose a SQL String from column names coming from the user")
200 @Override
201 public void execute(Connection connection, BinderConfiguration configuration) throws SQLException {
202 StringBuilder sql = new StringBuilder("insert into ").append(table).append(" (");
203
204 List<String> allColumnNames = new ArrayList<String>(columnNames);
205 allColumnNames.addAll(generatedValues.keySet());
206
207 for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
208 String columnName = it.next();
209 sql.append(columnName);
210 if (it.hasNext()) {
211 sql.append(", ");
212 }
213 }
214 sql.append(") values (");
215 for (Iterator<String> it = allColumnNames.iterator(); it.hasNext(); ) {
216 it.next();
217 sql.append("?");
218 if (it.hasNext()) {
219 sql.append(", ");
220 }
221 }
222 sql.append(")");
223
224 PreparedStatement stmt = connection.prepareStatement(sql.toString());
225
226 try {
227 Map<String, Binder> metadataBinders = new HashMap<String, Binder>();
228 if (metadataUsed) {
229 initializeBinders(stmt, allColumnNames, configuration, metadataBinders);
230 }
231
232 int rowIndex = 0;
233 for (List<?> row : rows) {
234 int i = 0;
235 for (Object value : row) {
236 String columnName = columnNames.get(i);
237 Binder binder = getBinder(columnName, metadataBinders);
238 binder.bind(stmt, i + 1, value);
239 i++;
240 }
241 for (Map.Entry<String, List<Object>> entry : generatedValues.entrySet()) {
242 String columnName = entry.getKey();
243 List<Object> rowValues = entry.getValue();
244 Binder binder = getBinder(columnName, metadataBinders);
245 binder.bind(stmt, i + 1, rowValues.get(rowIndex));
246 i++;
247 }
248
249 stmt.executeUpdate();
250 rowIndex++;
251 }
252 }
253 finally {
254 stmt.close();
255 }
256 }
257
258 private void initializeBinders(PreparedStatement stmt,
259 List<String> allColumnNames,
260 BinderConfiguration configuration,
261 Map<String, Binder> metadataBinders) throws SQLException {
262 ParameterMetaData metadata = stmt.getParameterMetaData();
263 int i = 1;
264 for (String columnName : allColumnNames) {
265 if (!this.binders.containsKey(columnName)) {
266 metadataBinders.put(columnName, configuration.getBinder(metadata, i));
267 }
268 i++;
269 }
270 }
271
272 private Binder getBinder(String columnName, Map<String, Binder> metadataBinders) {
273 Binder result = binders.get(columnName);
274 if (result == null) {
275 result = metadataBinders.get(columnName);
276 }
277 if (result == null) {
278 result = Binders.defaultBinder();
279 }
280 return result;
281 }
282
283 @Override
284 public String toString() {
285 return "insert into "
286 + table
287 + " [columns="
288 + columnNames
289 + ", generatedValues="
290 + generatedValues
291 + ", rows="
292 + rows
293 + ", metadataUsed="
294 + metadataUsed
295 + ", binders="
296 + binders
297 + "]";
298
299 }
300
301 @Override
302 public int hashCode() {
303 final int prime = 31;
304 int result = 1;
305 result = prime * result + binders.hashCode();
306 result = prime * result + columnNames.hashCode();
307 result = prime * result + generatedValues.hashCode();
308 result = prime * result + Boolean.valueOf(metadataUsed).hashCode();
309 result = prime * result + rows.hashCode();
310 result = prime * result + table.hashCode();
311 return result;
312 }
313
314 @Override
315 public boolean equals(Object obj) {
316 if (this == obj) {
317 return true;
318 }
319 if (obj == null) {
320 return false;
321 }
322 if (getClass() != obj.getClass()) {
323 return false;
324 }
325 Insert other = (Insert) obj;
326
327 return binders.equals(other.binders)
328 && columnNames.equals(other.columnNames)
329 && generatedValues.equals(other.generatedValues)
330 && metadataUsed == other.metadataUsed
331 && rows.equals(other.rows)
332 && table.equals(other.table);
333 }
334
335 /**
336 * Creates a new Builder instance, in order to build an Insert operation into the given table
337 * @param table the name of the table to insert into
338 * @return the created Builder
339 */
340 public static Builder into(@Nonnull String table) {
341 Preconditions.checkNotNull(table, "table may not be null");
342 return new Builder(table);
343 }
344
345 /**
346 * A builder used to create an Insert operation. Such a builder may only be used once. Once it has built its Insert
347 * operation, all its methods throw an {@link IllegalStateException}.
348 * @see Insert
349 * @see Insert#into(String)
350 * @author JB Nizet
351 */
352 public static final class Builder {
353 private final String table;
354 private final List<String> columnNames = new ArrayList<String>();
355 private final Map<String, ValueGenerator<?>> valueGenerators = new LinkedHashMap<String, ValueGenerator<?>>();
356 private final List<List<?>> rows = new ArrayList<List<?>>();
357
358 private boolean metadataUsed = true;
359 private final Map<String, Binder> binders = new HashMap<String, Binder>();
360
361 private boolean built;
362
363 private Builder(String table) {
364 this.table = table;
365 }
366
367 /**
368 * Specifies the list of columns into which values will be inserted. The values must the be specified, after,
369 * using the {@link #values(Object...)} method, or with the {@link #values(java.util.Map)} method, or by adding
370 * a row with named columns fluently using {@link #row()}.
371 * @param columns the names of the columns to insert into.
372 * @return this Builder instance, for chaining.
373 * @throws IllegalStateException if the Insert has already been built, or if this method has already been
374 * called, or if one of the given columns is also specified as one of the generated value columns, or if the
375 * set of columns has already been defined by adding a first row to the builder.
376 */
377 public Builder columns(@Nonnull String... columns) {
378 Preconditions.checkState(!built, "The insert has already been built");
379 Preconditions.checkState(columnNames.isEmpty(), "columns have already been specified");
380 for (String column : columns) {
381 Preconditions.checkNotNull(column, "column may not be null");
382 Preconditions.checkState(!valueGenerators.containsKey(column),
383 "column "
384 + column
385 + " has already been specified as generated value column");
386 }
387 columnNames.addAll(Arrays.asList(columns));
388 return this;
389 }
390
391 /**
392 * Adds a row of values to insert.
393 * @param values the values to insert.
394 * @return this Builder instance, for chaining.
395 * @throws IllegalStateException if the Insert has already been built, or if the number of values doesn't match
396 * the number of columns.
397 */
398 public Builder values(@Nonnull Object... values) {
399 Preconditions.checkState(!built, "The insert has already been built");
400 Preconditions.checkArgument(values.length == columnNames.size(),
401 "The number of values doesn't match the number of columns");
402 rows.add(new ArrayList<Object>(Arrays.asList(values)));
403 return this;
404 }
405
406 /**
407 * Starts building a new row with named columns to insert. If the row is the first one being added and the
408 * columns haven't been set yet by calling <code>columns()</code>, then the columns of this row constitute the
409 * column names (excluding the generated ones) of the Insert being built
410 * @return a {@link RowBuilder} instance, which, when built, will add a row to this insert builder.
411 * @throws IllegalStateException if the Insert has already been built.
412 * @see RowBuilder
413 */
414 public RowBuilder row() {
415 Preconditions.checkState(!built, "The insert has already been built");
416 return new RowBuilder(this);
417 }
418
419 /**
420 * Adds a row to this builder. If no row has been added yet and the columns haven't been set yet by calling
421 * <code>columns()</code>, then the keys of this map constitute the column names (excluding the generated ones)
422 * of the Insert being built, in the order of the keys in the map (which is arbitrary unless an ordered or
423 * sorted map is used).
424 * @param row the row to add. The keys of the map are the column names, which must match with
425 * the column names specified in the call to {@link #columns(String...)}, or with the column names of the first
426 * added row. If a column name is not present in the map, null is inserted for this column.
427 * @return this Builder instance, for chaining.
428 * @throws IllegalStateException if the Insert has already been built.
429 * @throws IllegalArgumentException if a column name of the map doesn't match with any of the column names
430 * specified with {@link #columns(String...)}
431 */
432 public Builder values(@Nonnull Map<String, ?> row) {
433 Preconditions.checkState(!built, "The insert has already been built");
434 Preconditions.checkNotNull(row, "The row may not be null");
435
436 boolean setColumns = rows.isEmpty() && columnNames.isEmpty();
437 if (setColumns) {
438 columns(row.keySet().toArray(new String[row.size()]));
439 }
440 else {
441 Set<String> rowColumnNames = new HashSet<String>(row.keySet());
442 rowColumnNames.removeAll(columnNames);
443 if (!rowColumnNames.isEmpty()) {
444 throw new IllegalArgumentException(
445 "The following columns of the row don't match with any column name: " + rowColumnNames);
446 }
447 }
448
449 List<Object> values = new ArrayList<Object>(columnNames.size());
450 for (String columnName : columnNames) {
451 values.add(row.get(columnName));
452 }
453 rows.add(values);
454 return this;
455 }
456
457 /**
458 * Associates a Binder to one or several columns.
459 * @param binder the binder to use, regardless of the metadata, for the given columns
460 * @param columns the name of the columns to associate with the given Binder
461 * @return this Builder instance, for chaining.
462 * @throws IllegalStateException if the Insert has already been built,
463 * @throws IllegalArgumentException if any of the given columns is not
464 * part of the columns or "generated value" columns.
465 */
466 public Builder withBinder(@Nonnull Binder binder, @Nonnull String... columns) {
467 Preconditions.checkState(!built, "The insert has already been built");
468 Preconditions.checkNotNull(binder, "binder may not be null");
469 for (String columnName : columns) {
470 Preconditions.checkArgument(this.columnNames.contains(columnName)
471 || this.valueGenerators.containsKey(columnName),
472 "column "
473 + columnName
474 + " is not one of the registered column names");
475 binders.put(columnName, binder);
476 }
477 return this;
478 }
479
480 /**
481 * Specifies a default value to be inserted in a column for all the rows inserted by the Insert operation.
482 * Calling this method is equivalent to calling
483 * <code>withGeneratedValue(column, ValueGenerators.constant(value))</code>
484 * @param column the name of the column
485 * @param value the default value to insert into the column
486 * @return this Builder instance, for chaining.
487 * @throws IllegalStateException if the Insert has already been built, or if the given column is part
488 * of the columns to insert.
489 */
490 public Builder withDefaultValue(@Nonnull String column, Object value) {
491 return withGeneratedValue(column, ValueGenerators.constant(value));
492 }
493
494 /**
495 * Allows the given column to be populated by a value generator, which will be called for every row of the
496 * Insert operation being built.
497 * @param column the name of the column
498 * @param valueGenerator the generator generating values for the given column of every row
499 * @return this Builder instance, for chaining.
500 * @throws IllegalStateException if the Insert has already been built, or if the given column is part
501 * of the columns to insert.
502 */
503 public Builder withGeneratedValue(@Nonnull String column, @Nonnull ValueGenerator<?> valueGenerator) {
504 Preconditions.checkState(!built, "The insert has already been built");
505 Preconditions.checkNotNull(column, "column may not be null");
506 Preconditions.checkNotNull(valueGenerator, "valueGenerator may not be null");
507 Preconditions.checkArgument(!columnNames.contains(column),
508 "column "
509 + column
510 + " is already listed in the list of column names");
511 valueGenerators.put(column, valueGenerator);
512 return this;
513 }
514
515 /**
516 * Determines if the metadata must be used to get the appropriate binder for each inserted column (except
517 * the ones which have been associated explicitely with a Binder). The default is <code>true</code>. The insert
518 * can be faster if set to <code>false</code>, but in this case, the {@link Binders#defaultBinder() default
519 * binder} will be used for all the columns (except the ones which have been associated explicitely with a
520 * Binder).
521 * @return this Builder instance, for chaining.
522 * @throws IllegalStateException if the Insert has already been built.
523 */
524 public Builder useMetadata(boolean useMetadata) {
525 Preconditions.checkState(!built, "The insert has already been built");
526 this.metadataUsed = useMetadata;
527 return this;
528 }
529
530 /**
531 * Builds the Insert operation.
532 * @return the created Insert operation.
533 * @throws IllegalStateException if the Insert has already been built, or if no column and no generated value
534 * column has been specified.
535 */
536 public Insert build() {
537 Preconditions.checkState(!built, "The insert has already been built");
538 Preconditions.checkState(!this.columnNames.isEmpty() || !this.valueGenerators.isEmpty(),
539 "no column and no generated value column has been specified");
540 built = true;
541 return new Insert(this);
542 }
543 }
544
545 /**
546 * A row builder, constructed with {@link com.ninja_squad.dbsetup.operation.Insert.Builder#row()}. This builder
547 * allows adding a row with named columns to an Insert:
548 *
549 * <pre>
550 * Insert insert =
551 * Insert.into("CLIENT")
552 * .columns("CLIENT_ID", "FIRST_NAME", "LAST_NAME", "DATE_OF_BIRTH", "CLIENT_TYPE")
553 * .row().column("CLIENT_ID", 1L)
554 * .column("FIRST_NAME", "John")
555 * .column("LAST_NAME", "Doe")
556 * .column("DATE_OF_BIRTH", "1975-07-19")
557 * .column("CLIENT_TYPE", ClientType.NORMAL)
558 * .end()
559 * .row().column("CLIENT_ID", 2L)
560 * .column("FIRST_NAME", "Jack")
561 * .column("LAST_NAME", "Smith")
562 * .column("DATE_OF_BIRTH", "1969-08-22")
563 * .column("CLIENT_TYPE", ClientType.HIGH_PRIORITY)
564 * .end()
565 * .build();
566 * </pre>
567 *
568 * You may omit the call to <code>columns()</code>. In that case, the columns of the Insert will be the columns
569 * specified in the first added row.
570 */
571 public static final class RowBuilder {
572 private final Builder builder;
573 private final Map<String, Object> row;
574 private boolean ended;
575
576 private RowBuilder(Builder builder) {
577 this.builder = builder;
578 // note: very important to use a LinkedHashMap here, to guarantee the ordering of the columns.
579 this.row = new LinkedHashMap<String, Object>();
580 }
581
582 /**
583 * Adds a new named column to the row. If a previous value has already been added for the same column, it's
584 * replaced by this new value.
585 * @param name the name of the column, which must match with a column name defined in the Insert Builder
586 * @param value the value of the column for the constructed row
587 * @return this builder, for chaining
588 * @throws IllegalArgumentException if the given name is not the name of one of the columns to insert
589 */
590 public RowBuilder column(@Nonnull String name, Object value) {
591 Preconditions.checkState(!ended, "The row has already been ended and added to the Insert Builder");
592 if (!builder.columnNames.isEmpty()) {
593 Preconditions.checkNotNull(name, "the column name may not be null");
594 Preconditions.checkArgument(builder.columnNames.contains(name),
595 "column " + name + " is not one of the registered column names");
596 }
597 row.put(name, value);
598 return this;
599 }
600
601 /**
602 * Ends the row, adds it to the Insert Builder and returns it, for chaining.
603 * @return the Insert Builder
604 */
605 public Builder end() {
606 Preconditions.checkState(!ended, "The row has already been ended and added to the Insert Builder");
607 ended = true;
608 return builder.values(row);
609 }
610 }
611 }