org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource#org.springframework.jdbc.core.namedparam.SqlParameterSource源码实例Demo

/**
 * 批量插入
 */
public int[] batchInsert(List<E> entities) {

    StringBuilder nameBuilder = new StringBuilder();
    StringBuilder valueBuilder = new StringBuilder();
    fields.forEach(field -> {
        nameBuilder.append(",").append(StringExtUtils.camelToUnderline(field.getName()));
        valueBuilder.append(",:").append(field.getName());
    });


    String sql = getSQL()
            .INSERT_INTO(tableName, nameBuilder.deleteCharAt(0).toString())
            .VALUES(valueBuilder.deleteCharAt(0).toString()).build();

    SqlParameterSource[] sqlParameterSources = new BeanPropertySqlParameterSource[entities.size()];
    for (int i = 0; i < sqlParameterSources.length; i++) {
        sqlParameterSources[i] = new BeanPropertySqlParameterSource(entities.get(i));
    }

    return getSQL().getNamedParameterJdbcTemplate().batchUpdate(sql, sqlParameterSources);
}
 

@Test
public void testIssue589() {
    SqlParameterSourceFactory<Issue589> parameterSourceFactory = JdbcTemplateMapperFactory.newInstance()
            .ignorePropertyNotFound()
            .addAlias("timestamp_", "timestamp")
            .addColumnProperty("timestamp_", SqlTypeColumnProperty.of(Types.TIMESTAMP))
            .newSqlParameterSourceFactory(Issue589.class);
    
    ZonedDateTime now = ZonedDateTime.now();
    Issue589 issue589 = new Issue589(now);

    SqlParameterSource sqlParameterSource = parameterSourceFactory.newSqlParameterSource(issue589);
    
    assertEquals(Types.TIMESTAMP, sqlParameterSource.getSqlType("timestamp_"));
    assertEquals(new Timestamp(Date.from(now.toInstant()).getTime()), sqlParameterSource.getValue("timestamp_"));
}
 

private SqlParameterSource createSqlParameterSource (TaskExecution aTaskExecution) {
  MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
  sqlParameterSource.addValue("id", aTaskExecution.getId());
  sqlParameterSource.addValue("parentId", aTaskExecution.getParentId());
  sqlParameterSource.addValue("jobId", aTaskExecution.getJobId());
  sqlParameterSource.addValue("status", aTaskExecution.getStatus().toString());
  sqlParameterSource.addValue("progress", aTaskExecution.getProgress());
  sqlParameterSource.addValue("createTime", aTaskExecution.getCreateTime());
  sqlParameterSource.addValue("startTime", aTaskExecution.getStartTime());
  sqlParameterSource.addValue("endTime", aTaskExecution.getEndTime());
  sqlParameterSource.addValue("serializedExecution", Json.serialize(json, aTaskExecution));
  sqlParameterSource.addValue("priority", aTaskExecution.getPriority());
  sqlParameterSource.addValue("taskNumber", aTaskExecution.getTaskNumber());
  return sqlParameterSource;
}
 

@Test
public void testSourceFactory() {
    String query = "WITH some_cte AS (\n" +
            "    SELECT :form_email_address\n" +
            "    FROM something s\n" +
            ")\n" +
            "SELECT :form_name\n" +
            "FROM something s";
    SqlParameterSourceFactory<Issue625> sourceFactory = JdbcTemplateMapperFactory.newInstance().newSqlParameterSourceFactory(Issue625.class, query);

    SqlParameterSource sqlParameterSource = sourceFactory.newSqlParameterSource(new Issue625("email", "value"));

    assertEquals("email", sqlParameterSource.getValue("form_email_address"));
    assertEquals("value", sqlParameterSource.getValue("form_name"));
}
 

/**
 * 使用SimpleJdbcInsert和BeanPropertySqlParameterSource方式 新增数据
 * @param customer
 */
public void useBeanPropertySqlParameterSource(Customer customer){
	//封装BeanPropertySqlParameterSource
	SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(customer);
	//设置表名(withTableName)和设置自增长主键(usingGeneratedKeyColumns)
	Number key = simpleJdbcInsert.withTableName("CUSTOMER").usingGeneratedKeyColumns("CUST_ID").executeAndReturnKey(parameterSource);
	customer.setCustId(key.intValue());//返回新增后的主键
	System.out.println(customer.getCustId());
}
 

@Test
public void testConstantValue() {
    SqlParameterSourceFactory<DbObject> parameterSourceFactory =
            JdbcTemplateMapperFactory
                    .newInstance()
                    .addColumnProperty("id", new ConstantValueProperty<Long>(-3l, Long.class))
                    .newSqlParameterSourceFactory(DbObject.class);

    SqlParameterSource parameterSource = parameterSourceFactory.newSqlParameterSource(new DbObject());

    assertEquals(-3l, parameterSource.getValue("id"));

}
 

public SqlParameterSource[] newSqlParameterSources(Iterator<T> values) {
    ArrayList<SqlParameterSource> sources = new ArrayList<SqlParameterSource>();
    while(values.hasNext()) {
        sources.add(newSqlParameterSource(values.next()));
    }
    return sources.toArray(new SqlParameterSource[0]);
}
 

public static SqlParameterSource generateParameterMap(Object t, List<String> ignores){
	Map<String, Object> paramMap = new HashMap<String, Object>();
	ReflectHelper reflectHelper = new ReflectHelper(t);
	PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(t.getClass());
	for (PropertyDescriptor pd : pds) {
		if(null != ignores && ignores.contains(pd.getName())){
			continue;
		}
		paramMap.put(pd.getName(), reflectHelper.getMethodValue(pd.getName()));
	}
	MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource(paramMap);
	return sqlParameterSource;
}
 

@Override
public Page<JobExecution> find(String jobGroup, String jobName, Query query) {
    String sql = "from " + getTableName() + " where jobGroup = :jobGroup and jobName = :jobName";

    SqlParameterSource source = new MapSqlParameterSource()
            .addValue("jobGroup", jobGroup)
            .addValue("jobName", jobName);

    return getLogs(sql, source, query);
}
 

public static SqlParameterSource generateParameterMap(Object t, List<String> ignores){
	Map<String, Object> paramMap = new HashMap<String, Object>();
	ReflectHelper reflectHelper = new ReflectHelper(t);
	PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(t.getClass());
	for (PropertyDescriptor pd : pds) {
		if(null != ignores && ignores.contains(pd.getName())){
			continue;
		}
		paramMap.put(pd.getName(), reflectHelper.getMethodValue(pd.getName()));
	}
	MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource(paramMap);
	return sqlParameterSource;
}
 

/**
 * 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;
}
 

/**
 * Method that provides execution of a batch insert using the passed in array of {@link SqlParameterSource}
 * @param batch array of SqlParameterSource with parameter names and values to be used in insert
 * @return array of number of rows affected
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
protected int[] doExecuteBatch(SqlParameterSource[] batch) {
	checkCompiled();
	List[] batchValues = new ArrayList[batch.length];
	int i = 0;
	for (SqlParameterSource parameterSource : batch) {
		List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
		batchValues[i++] = values;
	}
	return executeBatchInternal(batchValues);
}
 

protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
	ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Inserts a record in the DB
 * @param jdbcTemplate the Spring JDBC template object
 * @param query the query to run
 * @param bean the bean with the data to insert
 * @return the ID of the record updated
 */
public static int runInsert(final NamedParameterJdbcTemplate jdbcTemplate, final String query, final Object bean) {
    SqlParameterSource beanParameters = new BeanPropertySqlParameterSource(bean);
    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbcTemplate.update(query, beanParameters, keyHolder);
    return keyHolder.getKey().intValue();
}
 

public SqlParameterSource[] newSqlParameterSources(T[] values) {
    SqlParameterSource[] sources = new SqlParameterSource[values.length];

    for(int i = 0; i < values.length; i++) {
        T value = values[i];
        sources[i] = newSqlParameterSource(value);
    }
    return sources;
}
 

/**
 * 使用SimpleJdbcInsert和MapSqlParameterSource方式 新增数据
 * @param customer
 */
public void useMapSqlParameterSource(Customer customer){
	//封装MapSqlParameterSource
	SqlParameterSource parameterSource = new MapSqlParameterSource().
			addValue("NAME", customer.getName()).//为NAME列填充值
			addValue("AGE", customer.getAge());//为AGE列填充值
	//设置表名(withTableName)和设置自增长主键(usingGeneratedKeyColumns)
	Number key = simpleJdbcInsert.withTableName("CUSTOMER").usingGeneratedKeyColumns("CUST_ID").executeAndReturnKey(parameterSource);
	customer.setCustId(key.intValue());//返回新增后的主键
	System.out.println(customer.getCustId());
}
 

@Override
public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
	return getNamedParameterJdbcOperations().batchUpdate(sql, batchArgs);
}
 

/**
 * Delegate method that executes the insert using the passed-in {@link SqlParameterSource}.
 * @param parameterSource parameter names and values to be used in insert
 * @return the number of rows affected
 */
protected int doExecute(SqlParameterSource parameterSource) {
	checkCompiled();
	List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
	return executeInsertInternal(values);
}
 

@Override
public <T> T queryForObject(String sql, RowMapper<T> rm, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().queryForObject(sql, args, rm);
}
 

default void fileContent(int organizationId, String name, OutputStream out) {
    SqlParameterSource param = new MapSqlParameterSource("name", name).addValue("organizationId", organizationId);
    getNamedParameterJdbcTemplate().query("select content from resource_organizer where name = :name and organization_id_fk = :organizationId", param, OUTPUT_CONTENT.apply(out));
}
 

@Override
@SuppressWarnings("unchecked")
public <T> T executeFunction(Class<T> returnType, SqlParameterSource args) {
	return (T) doExecute(args).get(getScalarOutParameterName());
}
 

@Override
public Number executeAndReturnKey(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKey(parameterSource);
}
 

@Override
public int execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}
 

@Override
public int[] executeBatch(SqlParameterSource... batch) {
	return doExecuteBatch(batch);
}
 

private void validate(SqlParameterSource[] sqlParameterSources) {
    for(int i = 0; i < sqlParameterSources.length; i++) {
        assertEquals((long)i, sqlParameterSources[i].getValue("id"));
    }
}
 

@Test
public void testInstantShouldBeTimestampType() {
    String sql = "INSERT INTO table VALUES(:instant)";

    SqlParameterSourceFactory<ObjectWithInstant> sqlParameters =
            JdbcTemplateMapperFactory
                    .newInstance()
                    .newSqlParameterSourceFactory(ObjectWithInstant.class, sql);

    
    ObjectWithInstant data = new ObjectWithInstant(Instant.now());
    
    SqlParameterSource parameterSource = sqlParameters.newSqlParameterSource(data);

    assertEquals(Types.TIMESTAMP, parameterSource.getSqlType("instant"));
    assertEquals(new Timestamp(data.instant.toEpochMilli()), parameterSource.getValue("instant"));

}
 

@Override
public KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKeyHolder(parameterSource);
}
 

@Override
public <T> List<T> query(String sql, RowMapper<T> rm, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().query(sql, args, rm);
}
 

/**
 * 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;
}
 

@Override
public Map<String, Object> execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}