Java SqlParameterSourceUtils类代码示例

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * On peut utiliser un {@link SqlParameterSourceUtils} pour cr�er rapidement un array de param�tres
 * Il faut que les noms des champs coincident avec les noms des propri�t� dans la requete
 */
public int[] updateUsers(final User... user) {
    String sqlUpdate = "update users set name = :name, email = :mail where id = :id";

    SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(user);

    return namedParameterJdbcTemplate.batchUpdate(sqlUpdate, params);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
@Override
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
	/**
	 * 优先使用 EntityPropertySqlParameterSource
	 */
	if(parameterSource instanceof EntityPropertySqlParameterSource){
		List<Object> values = new ArrayList<Object>();
		for (String column : this.getTableColumns()) {
			if (parameterSource.hasValue(column)) {
				values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
			}else{
				values.add(null);
			}
		}
		return values;
	}
	
	
	return super.matchInParameterValuesWithInsertColumns(parameterSource);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
private void updateAddressesWithComplexData() {
    final List<Address> updAddrList = new ArrayList(3500);
    final List<Map<String, Object>> adrList = template.queryForList("SELECT ID, VALUE FROM ADDRESS");
    for (final Map<String, Object> map : adrList) {
        final Address address = getFirst(addressService.filterAddresses((String) map.get("VALUE")), null);
        if (address != null) {
            address.setId((String) map.get("ID"));
            updAddrList.add(address);
        }
    }

    namedTemplate.batchUpdate(
            "UPDATE ADDRESS " +
                    "SET " +
                    "AREA_WITH_TYPE = :areaWithType, " +
                    "CITY_WITH_TYPE = :cityWithType, " +
                    "REGION_FIAS_ID = :regionFiasId, " +
                    "REGION_KLADR_ID = :regionKladrId, " +
                    "REGION_WITH_TYPE = :regionWithType, " +
                    "SETTLEMENT_WITH_TYPE = :settlementWithType, " +
                    "STREET_WITH_TYPE = :streetWithType " +
                    "WHERE ID = :id",
            SqlParameterSourceUtils.createBatch(updAddrList.toArray()
            ));
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match the provided column names and values with the list of columns used.
 * @param parameterSource the parameter names and values
 */
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
	List<Object> values = new ArrayList<Object>();
	// for parameter source lookups we need to provide caseinsensitive lookup support since the
	// database metadata is not necessarily providing case sensitive column names
	Map<String, String> caseInsensitiveParameterNames =
			SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
	for (String column : this.tableColumns) {
		if (parameterSource.hasValue(column)) {
			values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
		}
		else {
			String lowerCaseName = column.toLowerCase();
			if (parameterSource.hasValue(lowerCaseName)) {
				values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
			}
			else {
				String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
				if (parameterSource.hasValue(propertyName)) {
					values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
				}
				else {
					if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
						values.add(
								SqlParameterSourceUtils.getTypedValue(parameterSource,
										caseInsensitiveParameterNames.get(lowerCaseName)));
					}
					else {
						values.add(null);
					}
				}
			}
		}
	}
	return values;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
@Test
public void testInsertBatch() {
    List<GeneratedAlwaysRecord> records = new ArrayList<>();
    GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");
    records.add(record);
    
    record = new GeneratedAlwaysRecord();
    record.setId(101);
    record.setFirstName("Jim");
    record.setLastName("Smith");
    records.add(record);

    SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(records.toArray());
    
    BatchInsert<GeneratedAlwaysRecord> batchInsert = insert(records)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategy.SPRING_NAMED_PARAMETER);
    
    int[] updateCounts = template.batchUpdate(batchInsert.getInsertStatementSQL(), batch);
    
    assertThat(updateCounts.length).isEqualTo(2);
    assertThat(updateCounts[0]).isEqualTo(1);
    assertThat(updateCounts[1]).isEqualTo(1);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match the provided column names and values with the list of columns used.
 * 
 * @param sqlParameterSource
 *            the parameter names and values
 * @param reconciledUpdatingColumns
 */
public List<Object> sortAndTypeInParameter(SqlParameterSource sqlParameterSource,
		List<String> reconciledUpdatingColumns) {
	List<Object> values = new ArrayList<Object>();
	// for parameter source lookups we need to provide caseinsensitive
	// lookup support since the
	// database metadata is not necessarily providing case sensitive column
	// names
	Map<?, ?> caseInsensitiveParameterNames = SqlParameterSourceUtils
			.extractCaseInsensitiveParameterNames(sqlParameterSource);
	for (String column : reconciledUpdatingColumns) {
		if (sqlParameterSource.hasValue(column)) {
			values.add(SqlParameterSourceUtils.getTypedValue(sqlParameterSource, column));
		} else {
			String lowerCaseName = column.toLowerCase();
			if (sqlParameterSource.hasValue(lowerCaseName)) {
				values.add(SqlParameterSourceUtils.getTypedValue(sqlParameterSource, lowerCaseName));
			} else {
				String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
				if (sqlParameterSource.hasValue(propertyName)) {
					values.add(SqlParameterSourceUtils.getTypedValue(sqlParameterSource, propertyName));
				} else {
					if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
						values.add(SqlParameterSourceUtils.getTypedValue(sqlParameterSource,
								(String) caseInsensitiveParameterNames.get(lowerCaseName)));
					} else {
						values.add(null);
					}
				}
			}
		}
	}
	return values;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match the provided column names and values with the list of columns used.
 * @param parameterSource the parameter names and values
 */
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
	List<Object> values = new ArrayList<Object>();
	// for parameter source lookups we need to provide caseinsensitive lookup support since the
	// database metadata is not necessarily providing case sensitive column names
	Map caseInsensitiveParameterNames =
			SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
	for (String column : this.tableColumns) {
		if (parameterSource.hasValue(column)) {
			values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
		}
		else {
			String lowerCaseName = column.toLowerCase();
			if (parameterSource.hasValue(lowerCaseName)) {
				values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
			}
			else {
				String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
				if (parameterSource.hasValue(propertyName)) {
					values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
				}
				else {
					if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
						values.add(
								SqlParameterSourceUtils.getTypedValue(parameterSource,
										(String) caseInsensitiveParameterNames.get(lowerCaseName)));
					}
					else {
						values.add(null);
					}
				}
			}
		}
	}
	return values;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
protected void convertOldAddress(final String table, final String addrIdColumn, final String region, final String city, final String street_bld) {
    final List<Map<String, Object>> postList = template.queryForList(
            "SELECT " +
                    "    ID," +
                    "    CONCAT(" + region + "," +
                    "            \" \"," +
                    "            " + city + "," +
                    "            \" \"," +
                    "            " + street_bld + ") addr " +
                    "FROM" +
                    "    " + table + " " +
                    "WHERE" +
                    "    " + region + " IS NOT NULL" +
                    "        OR " + city + " IS NOT NULL" +
                    "        OR " + street_bld + " IS NOT NULL");
    final List<Address> newAddrList = new ArrayList(128);
    final List<Map<String, Object>> idAddrList = new ArrayList(128);
    for (final Map<String, Object> map : postList) {
        final Address address = getFirst(addressService.filterAddresses((String) map.get("addr")), null);
        if (address != null) {
            final String leId = (String) map.get("ID");
            final String addrId = UUID.randomUUID().toString();
            address.setId(addrId);
            newAddrList.add(address);
            idAddrList.add(ImmutableMap.of("id", leId, "addr_id", addrId));
        }
    }
    insertNewAddresses(newAddrList);
    namedTemplate.batchUpdate(
            "UPDATE " + table + " SET " + addrIdColumn + " = :addr_id WHERE ID = :id",
            SqlParameterSourceUtils.createBatch(idAddrList.toArray(new Map[idAddrList.size()])));
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match input parameter values with the parameters declared to be used in the call.
 * @param parameterSource the input values
 * @return a Map containing the matched parameter names with the value taken from the input
 */
public Map<String, Object> matchInParameterValuesWithCallParameters(SqlParameterSource parameterSource) {
	// For parameter source lookups we need to provide case-insensitive lookup support
	// since the database metadata is not necessarily providing case sensitive parameter names.
	Map<String, String> caseInsensitiveParameterNames =
			SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

	Map<String, String> callParameterNames = new HashMap<String, String>(this.callParameters.size());
	Map<String, Object> matchedParameters = new HashMap<String, Object>(this.callParameters.size());
	for (SqlParameter parameter : this.callParameters) {
		if (parameter.isInputValueProvided()) {
			String parameterName = parameter.getName();
			String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
			if (parameterNameToMatch != null) {
				callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName);
			}
			if (parameterName != null) {
				if (parameterSource.hasValue(parameterName)) {
					matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, parameterName));
				}
				else {
					String lowerCaseName = parameterName.toLowerCase();
					if (parameterSource.hasValue(lowerCaseName)) {
						matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
					}
					else {
						String englishLowerCaseName = parameterName.toLowerCase(Locale.ENGLISH);
						if (parameterSource.hasValue(englishLowerCaseName)) {
							matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, englishLowerCaseName));
						}
						else {
							String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(parameterName);
							if (parameterSource.hasValue(propertyName)) {
								matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
							}
							else {
								if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
									String sourceName = caseInsensitiveParameterNames.get(lowerCaseName);
									matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, sourceName));
								}
								else {
									logger.warn("Unable to locate the corresponding parameter value for '" + parameterName +
											"' within the parameter values provided: " + caseInsensitiveParameterNames.values());
								}
							}
						}
					}
				}
			}
		}
	}

	if (logger.isDebugEnabled()) {
		logger.debug("Matching " + caseInsensitiveParameterNames.values() + " with " + callParameterNames.values());
		logger.debug("Found match for " + matchedParameters.keySet());
	}
	return matchedParameters;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match input parameter values with the parameters declared to be used in the call.
 * @param parameterSource the input values
 * @return a Map containing the matched parameter names with the value taken from the input
 */
public Map<String, Object> matchInParameterValuesWithCallParameters(SqlParameterSource parameterSource) {
	// For parameter source lookups we need to provide case-insensitive lookup support
	// since the database metadata is not necessarily providing case sensitive parameter names.
	Map caseInsensitiveParameterNames =
			SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

	Map<String, String> callParameterNames = new HashMap<String, String>(this.callParameters.size());
	Map<String, Object> matchedParameters = new HashMap<String, Object>(this.callParameters.size());
	for (SqlParameter parameter : this.callParameters) {
		if (parameter.isInputValueProvided()) {
			String parameterName = parameter.getName();
			String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
			if (parameterNameToMatch != null) {
				callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName);
			}
			if (parameterName != null) {
				if (parameterSource.hasValue(parameterName)) {
					matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, parameterName));
				}
				else {
					String lowerCaseName = parameterName.toLowerCase();
					if (parameterSource.hasValue(lowerCaseName)) {
						matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
					}
					else {
						String englishLowerCaseName = parameterName.toLowerCase(Locale.ENGLISH);
						if (parameterSource.hasValue(englishLowerCaseName)) {
							matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, englishLowerCaseName));
						}
						else {
							String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(parameterName);
							if (parameterSource.hasValue(propertyName)) {
								matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
							}
							else {
								if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
									String sourceName = (String) caseInsensitiveParameterNames.get(lowerCaseName);
									matchedParameters.put(parameterName, SqlParameterSourceUtils.getTypedValue(parameterSource, sourceName));
								}
								else {
									logger.warn("Unable to locate the corresponding parameter value for '" + parameterName +
											"' within the parameter values provided: " + caseInsensitiveParameterNames.values());
								}
							}
						}
					}
				}
			}
		}
	}

	if (logger.isDebugEnabled()) {
		logger.debug("Matching " + caseInsensitiveParameterNames.values() + " with " + callParameterNames.values());
		logger.debug("Found match for " + matchedParameters.keySet());
	}
	return matchedParameters;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * Match the provided column names and values with the list of columns used.
 * 
 * @param sqlParameterSource
 *            the parameter names and values
 * @param reconciledUpdatingColumns
 */
public List<Object> sortAndTypeInParameter(
		SqlParameterSource sqlParameterSource,
		List<String> reconciledUpdatingColumns) {
	List<Object> values = new ArrayList<Object>();
	// for parameter source lookups we need to provide caseinsensitive
	// lookup support since the
	// database metadata is not necessarily providing case sensitive column
	// names
	Map<?, ?> caseInsensitiveParameterNames = SqlParameterSourceUtils
			.extractCaseInsensitiveParameterNames(sqlParameterSource);
	for (String column : reconciledUpdatingColumns) {
		if (sqlParameterSource.hasValue(column)) {
			values.add(SqlParameterSourceUtils.getTypedValue(
					sqlParameterSource, column));
		} else {
			String lowerCaseName = column.toLowerCase();
			if (sqlParameterSource.hasValue(lowerCaseName)) {
				values.add(SqlParameterSourceUtils.getTypedValue(
						sqlParameterSource, lowerCaseName));
			} else {
				String propertyName = JdbcUtils
						.convertUnderscoreNameToPropertyName(column);
				if (sqlParameterSource.hasValue(propertyName)) {
					values.add(SqlParameterSourceUtils.getTypedValue(
							sqlParameterSource, propertyName));
				} else {
					if (caseInsensitiveParameterNames
							.containsKey(lowerCaseName)) {
						values.add(SqlParameterSourceUtils.getTypedValue(
								sqlParameterSource,
								(String) caseInsensitiveParameterNames
										.get(lowerCaseName)));
					} else {
						values.add(null);
					}
				}
			}
		}
	}
	return values;
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
protected void updateRegions(final String table, final String column) {
    namedTemplate.batchUpdate(
            MessageFormat.format("UPDATE {0} SET {1} = :nameWithType WHERE {1} = :oldName", table, column),
            SqlParameterSourceUtils.createBatch(addressService.findAllRegions().toArray()));
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
@Transactional
public int[] create(List<Job> jobs) {
	
	SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(jobs.toArray());
	
	return jdbc.batchUpdate("insert into Jobs (id, name, text, email) values (:id, :name, :text, :email)", params);
}
 

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; //导入依赖的package包/类
/**
 * 使用namedParameterJdbcTemplate和SqlParameterSourceUtils批量插入
 * @param customers
 */
public void insertBatchUseSqlParameterSourceUtils(final List<Customer> customers) {

	SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(customers.toArray());

	namedParameterJdbcTemplate.batchUpdate("INSERT INTO CUSTOMER (NAME, AGE) VALUES (:name, :age)",params);

}