org.springframework.jdbc.core.namedparam.SqlParameterSource Java Examples

/**
 * 批量插入
 */
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_"));
}
 

/**
 * 使用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());
}
 

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);
}
 

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

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]);
}
 

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

@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(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;
}
 

@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);
}
 

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

/**
 * 使用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());
}
 

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

@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"));
}
 

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

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

@Override
public Map<String, Object> queryForMap(String sql, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().queryForMap(sql, args);
}
 

public ResultPage<E> selectPageWhere(String sqlCondition, int pageNumber, int perPage,
                                     SqlParameterSource parameterSource) {
    String sql = "SELECT " + columns + " FROM " + tableName + " WHERE 1=1 AND " + sqlCondition;
    return getSQL().useSql(sql).parameter(parameterSource)
            .queryPage(pageNumber, perPage, new BeanPropertyRowMapper<>(entityClass));
}
 

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

@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"));

}
 

/**
 * Parse the SQL statement and locate any placeholders or named parameters. Named
 * parameters are substituted for a JDBC placeholder, and any select list is expanded
 * to the required number of placeholders. Select lists may contain an array of
 * objects, and in that case the placeholders will be grouped and enclosed with
 * parentheses. This allows for the use of "expression lists" in the SQL statement
 * like: <br /><br />
 * {@code select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))}
 * <p>The parameter values passed in are used to determine the number of placeholders to
 * be used for a select list. Select lists should be limited to 100 or fewer elements.
 * A larger number of elements is not guaranteed to be supported by the database and
 * is strictly vendor-dependent.
 * @param parsedSqlBean the parsed representation of the SQL statement
 * @param paramSource the source for named parameters
 * @return the SQL statement with substituted parameters
 * @see #parseSqlStatement
 */
public static String substituteNamedParameters(ParsedSqlBean parsedSqlBean, @Nullable SqlParameterSource paramSource) {
	String originalSql = parsedSqlBean.getOriginalSql();
	List<String> paramNames = parsedSqlBean.getParameterNames();
	if (paramNames.isEmpty()) {
		return originalSql;
	}
	StringBuilder actualSql = new StringBuilder(originalSql.length());
	int lastIndex = 0;
	for (int i = 0; i < paramNames.size(); i++) {
		String paramName = paramNames.get(i);
		int[] indexes = parsedSqlBean.getParameterIndexes(i);
		int startIndex = indexes[0];
		int endIndex = indexes[1];
		actualSql.append(originalSql, lastIndex, startIndex);
		if (paramSource != null && paramSource.hasValue(paramName)) {
			Object value = paramSource.getValue(paramName);
			if (value instanceof SqlParameterValue) {
				value = ((SqlParameterValue) value).getValue();
			}
			if (value instanceof Collection) {
				Iterator<?> entryIter = ((Collection<?>) value).iterator();
				int k = 0;
				while (entryIter.hasNext()) {
					if (k > 0) {
						actualSql.append(", ");
					}
					k++;
					Object entryItem = entryIter.next();
					if (entryItem instanceof Object[]) {
						Object[] expressionList = (Object[]) entryItem;
						actualSql.append('(');
						for (int m = 0; m < expressionList.length; m++) {
							if (m > 0) {
								actualSql.append(", ");
							}
							actualSql.append('?');
						}
						actualSql.append(')');
					}
					else {
						actualSql.append('?');
					}
				}
			}
			else {
				actualSql.append('?');
			}
		}
		else {
			actualSql.append('?');
		}
		lastIndex = endIndex;
	}
	actualSql.append(originalSql, lastIndex, originalSql.length());
	return actualSql.toString();
}
 

@Override
public int execute(SqlParameterSource parameterSource) {
	return doExecute(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 int[] executeBatch(SqlParameterSource... batch) {
	return doExecuteBatch(batch);
}
 

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

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

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

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

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