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 }