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

The following examples show how to use org.springframework.jdbc.core.namedparam.SqlParameterSource. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: BaseDAO.java    From FastSQL with Apache License 2.0 6 votes vote down vote up
/**
 * 批量插入
 */
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);
}
 
Example #2
Source File: SqlParameterSourceTest.java    From SimpleFlatMapper with MIT License 6 votes vote down vote up
@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_"));
}
 
Example #3
Source File: JdbcCustomerDAO.java    From maven-framework-project with MIT License 5 votes vote down vote up
/**
 * 使用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());
}
 
Example #4
Source File: DatasourceServletAction.java    From ureport with Apache License 2.0 5 votes vote down vote up
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);
}
 
Example #5
Source File: MigrateForm.java    From jeewx with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #6
Source File: SqlParameterSourceFactory.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
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]);
}
 
Example #7
Source File: EasyRunner.java    From maestro-java with Apache License 2.0 5 votes vote down vote up
/**
 * 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();
}
 
Example #8
Source File: SqlParameterSourceTest.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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"));

}
 
Example #9
Source File: SqlParameterSourceFactory.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
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;
}
 
Example #10
Source File: JdbcJobExecutions.java    From quartz-glass with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example #11
Source File: TableMetaDataContext.java    From effectivejava with Apache License 2.0 5 votes vote down vote up
/**
 * 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;
}
 
Example #12
Source File: JdbcCustomerDAO.java    From maven-framework-project with MIT License 5 votes vote down vote up
/**
 * 使用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());
}
 
Example #13
Source File: MigrateForm.java    From jeecg with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #14
Source File: Issue625Test.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@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"));
}
 
Example #15
Source File: JdbcTaskExecutionRepository.java    From piper with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example #16
Source File: AbstractJdbcInsert.java    From effectivejava with Apache License 2.0 5 votes vote down vote up
/**
 * 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);
}
 
Example #17
Source File: SimpleJdbcTemplate.java    From effectivejava with Apache License 2.0 4 votes vote down vote up
@Override
public Map<String, Object> queryForMap(String sql, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().queryForMap(sql, args);
}
 
Example #18
Source File: BaseDAO.java    From FastSQL with Apache License 2.0 4 votes vote down vote up
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));
}
 
Example #19
Source File: SimpleJdbcInsert.java    From effectivejava with Apache License 2.0 4 votes vote down vote up
@Override
public KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKeyHolder(parameterSource);
}
 
Example #20
Source File: SqlParameterSourceTest.java    From SimpleFlatMapper with MIT License 4 votes vote down vote up
@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"));

}
 
Example #21
Source File: ProcedureParameterUtils.java    From opscenter with Apache License 2.0 4 votes vote down vote up
/**
 * 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();
}
 
Example #22
Source File: SimpleJdbcInsert.java    From effectivejava with Apache License 2.0 4 votes vote down vote up
@Override
public int execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}
 
Example #23
Source File: SimpleJdbcTemplate.java    From effectivejava with Apache License 2.0 4 votes vote down vote up
@Override
public <T> List<T> query(String sql, RowMapper<T> rm, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().query(sql, args, rm);
}
 
Example #24
Source File: CallMetaDataContext.java    From effectivejava with Apache License 2.0 4 votes vote down vote up
/**
 * 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;
}
 
Example #25
Source File: SimpleJdbcInsert.java    From java-technology-stack with MIT License 4 votes vote down vote up
@Override
public int[] executeBatch(SqlParameterSource... batch) {
	return doExecuteBatch(batch);
}
 
Example #26
Source File: SimpleJdbcCall.java    From java-technology-stack with MIT License 4 votes vote down vote up
@Override
public Map<String, Object> execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}
 
Example #27
Source File: SimpleJdbcInsert.java    From java-technology-stack with MIT License 4 votes vote down vote up
@Override
public KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKeyHolder(parameterSource);
}
 
Example #28
Source File: SimpleJdbcInsert.java    From java-technology-stack with MIT License 4 votes vote down vote up
@Override
public Number executeAndReturnKey(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKey(parameterSource);
}
 
Example #29
Source File: SimpleJdbcInsert.java    From java-technology-stack with MIT License 4 votes vote down vote up
@Override
public int execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}
 
Example #30
Source File: UploadedResourceRepository.java    From alf.io with GNU General Public License v3.0 4 votes vote down vote up
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));
}