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 }