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

The following examples show how to use org.springframework.jdbc.core.namedparam.SqlParameterSource. These examples are extracted from open source projects. 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 Project: FastSQL   Source File: BaseDAO.java    License: 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 Project: SimpleFlatMapper   Source File: SqlParameterSourceTest.java    License: 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 Project: piper   Source File: JdbcTaskExecutionRepository.java    License: 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 4
Source Project: SimpleFlatMapper   Source File: Issue625Test.java    License: 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 5
Source Project: maven-framework-project   Source File: JdbcCustomerDAO.java    License: 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 6
Source Project: SimpleFlatMapper   Source File: SqlParameterSourceTest.java    License: 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 7
Source Project: SimpleFlatMapper   Source File: SqlParameterSourceFactory.java    License: 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 8
Source Project: jeecg   Source File: MigrateForm.java    License: 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 9
Source Project: quartz-glass   Source File: JdbcJobExecutions.java    License: 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 10
Source Project: jeewx   Source File: MigrateForm.java    License: 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 11
Source Project: effectivejava   Source File: TableMetaDataContext.java    License: 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 Project: effectivejava   Source File: AbstractJdbcInsert.java    License: 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 13
Source Project: ureport   Source File: DatasourceServletAction.java    License: 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 14
Source Project: maestro-java   Source File: EasyRunner.java    License: 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 15
Source Project: SimpleFlatMapper   Source File: SqlParameterSourceFactory.java    License: 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 16
Source Project: maven-framework-project   Source File: JdbcCustomerDAO.java    License: 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 17
Source Project: effectivejava   Source File: SimpleJdbcTemplate.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
	return getNamedParameterJdbcOperations().batchUpdate(sql, batchArgs);
}
 
Example 18
Source Project: spring-analysis-note   Source File: AbstractJdbcInsert.java    License: MIT License 4 votes vote down vote up
/**
 * 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);
}
 
Example 19
Source Project: effectivejava   Source File: SimpleJdbcTemplate.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public <T> T queryForObject(String sql, RowMapper<T> rm, SqlParameterSource args)
		throws DataAccessException {
	return getNamedParameterJdbcOperations().queryForObject(sql, args, rm);
}
 
Example 20
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));
}
 
Example 21
Source Project: effectivejava   Source File: SimpleJdbcCall.java    License: Apache License 2.0 4 votes vote down vote up
@Override
@SuppressWarnings("unchecked")
public <T> T executeFunction(Class<T> returnType, SqlParameterSource args) {
	return (T) doExecute(args).get(getScalarOutParameterName());
}
 
Example 22
Source Project: spring-analysis-note   Source File: SimpleJdbcInsert.java    License: MIT License 4 votes vote down vote up
@Override
public Number executeAndReturnKey(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKey(parameterSource);
}
 
Example 23
Source Project: effectivejava   Source File: SimpleJdbcInsert.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public int execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}
 
Example 24
Source Project: spring-analysis-note   Source File: SimpleJdbcInsert.java    License: MIT License 4 votes vote down vote up
@Override
public int[] executeBatch(SqlParameterSource... batch) {
	return doExecuteBatch(batch);
}
 
Example 25
Source Project: SimpleFlatMapper   Source File: SqlParameterSourceTest.java    License: MIT License 4 votes vote down vote up
private void validate(SqlParameterSource[] sqlParameterSources) {
    for(int i = 0; i < sqlParameterSources.length; i++) {
        assertEquals((long)i, sqlParameterSources[i].getValue("id"));
    }
}
 
Example 26
Source Project: SimpleFlatMapper   Source File: SqlParameterSourceTest.java    License: 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 27
Source Project: effectivejava   Source File: SimpleJdbcInsert.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource) {
	return doExecuteAndReturnKeyHolder(parameterSource);
}
 
Example 28
Source Project: effectivejava   Source File: SimpleJdbcTemplate.java    License: 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 29
Source Project: effectivejava   Source File: CallMetaDataContext.java    License: 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 30
Source Project: java-technology-stack   Source File: SimpleJdbcCall.java    License: MIT License 4 votes vote down vote up
@Override
public Map<String, Object> execute(SqlParameterSource parameterSource) {
	return doExecute(parameterSource);
}