org.springframework.jdbc.core.SqlParameterValue Java Examples

The following examples show how to use org.springframework.jdbc.core.SqlParameterValue. 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: NamedParameterJdbcTemplateTests.java    From spring-analysis-note with MIT License 6 votes vote down vote up
@Test
public void testQueryWithRowCallbackHandler() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	final List<Customer> customers = new LinkedList<>();
	namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, rs -> {
		Customer cust = new Customer();
		cust.setId(rs.getInt(COLUMN_NAMES[0]));
		cust.setForename(rs.getString(COLUMN_NAMES[1]));
		customers.add(cust);
	});

	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #2
Source File: NamedParameterJdbcTemplateTests.java    From java-technology-stack with MIT License 6 votes vote down vote up
@Test
public void testExecuteWithTypedParameters() throws SQLException {
	given(preparedStatement.executeUpdate()).willReturn(1);

	params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
	Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
			(PreparedStatementCallback<Object>) ps -> {
				assertEquals(preparedStatement, ps);
				ps.executeUpdate();
				return "result";
			});

	assertEquals("result", result);
	verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setObject(2, 1, Types.INTEGER);
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #3
Source File: NamedParameterJdbcTemplateTests.java    From java-technology-stack with MIT License 6 votes vote down vote up
@Test
public void testQueryWithResultSetExtractor() throws SQLException {
	given(resultSet.next()).willReturn(true);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	Customer cust = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
			rs -> {
				rs.next();
				Customer cust1 = new Customer();
				cust1.setId(rs.getInt(COLUMN_NAMES[0]));
				cust1.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust1;
			});

	assertTrue("Customer id was assigned correctly", cust.getId() == 1);
	assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #4
Source File: SqlParameterSourceUtils.java    From java-technology-stack with MIT License 6 votes vote down vote up
/**
 * Create a wrapped value if parameter has type information, plain object if not.
 * @param source the source of parameter values and type information
 * @param parameterName the name of the parameter
 * @return the value object
 */
@Nullable
public static Object getTypedValue(SqlParameterSource source, String parameterName) {
	int sqlType = source.getSqlType(parameterName);
	if (sqlType != SqlParameterSource.TYPE_UNKNOWN) {
		if (source.getTypeName(parameterName) != null) {
			return new SqlParameterValue(sqlType, source.getTypeName(parameterName), source.getValue(parameterName));
		}
		else {
			return new SqlParameterValue(sqlType, source.getValue(parameterName));
		}
	}
	else {
		return source.getValue(parameterName);
	}
}
 
Example #5
Source File: DirectSqlSavePartition.java    From metacat with Apache License 2.0 6 votes vote down vote up
private List<PartitionSequenceIds> getPartitionSequenceIds(final QualifiedName tableName,
                                                           final List<String> partitionNames) {
    final List<String> paramVariables = partitionNames.stream().map(s -> "?").collect(Collectors.toList());
    final String paramVariableString = Joiner.on(",").skipNulls().join(paramVariables);
    final SqlParameterValue[] values = new SqlParameterValue[partitionNames.size() + 2];
    int index = 0;
    values[index++] = new SqlParameterValue(Types.VARCHAR, tableName.getDatabaseName());
    values[index++] = new SqlParameterValue(Types.VARCHAR, tableName.getTableName());
    for (String partitionName : partitionNames) {
        values[index++] = new SqlParameterValue(Types.VARCHAR, partitionName);
    }
    return jdbcTemplate.query(
        String.format(SQL.PARTITIONS_SELECT, paramVariableString), values,
        (rs, rowNum) -> new PartitionSequenceIds(rs.getLong("part_id"), rs.getLong("sd_id"),
            rs.getLong("serde_id")));
}
 
Example #6
Source File: NamedParameterJdbcTemplateTests.java    From java-technology-stack with MIT License 6 votes vote down vote up
@Test
public void testQueryWithRowCallbackHandler() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	final List<Customer> customers = new LinkedList<>();
	namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, rs -> {
		Customer cust = new Customer();
		cust.setId(rs.getInt(COLUMN_NAMES[0]));
		cust.setForename(rs.getString(COLUMN_NAMES[1]));
		customers.add(cust);
	});

	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #7
Source File: NamedParameterJdbcTemplateTests.java    From java-technology-stack with MIT License 6 votes vote down vote up
@Test
public void testQueryWithRowMapper() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	List<Customer> customers = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
			(rs, rownum) -> {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			});
	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #8
Source File: NamedParameterJdbcTemplateTests.java    From java-technology-stack with MIT License 6 votes vote down vote up
@Test
public void testQueryForObjectWithRowMapper() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	Customer cust = namedParameterTemplate.queryForObject(SELECT_NAMED_PARAMETERS, params,
			(rs, rownum) -> {
				Customer cust1 = new Customer();
				cust1.setId(rs.getInt(COLUMN_NAMES[0]));
				cust1.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust1;
			});
	assertTrue("Customer id was assigned correctly", cust.getId() == 1);
	assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #9
Source File: NamedParameterJdbcTemplateTests.java    From effectivejava with Apache License 2.0 6 votes vote down vote up
@Test
public void testExecuteWithTypedParameters() throws SQLException {
	given(preparedStatement.executeUpdate()).willReturn(1);

	params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
	Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
			new PreparedStatementCallback<Object>() {
				@Override
				public Object doInPreparedStatement(PreparedStatement ps)
						throws SQLException {
					assertEquals(preparedStatement, ps);
					ps.executeUpdate();
					return "result";
				}
			});

	assertEquals("result", result);
	verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setObject(2, 1, Types.INTEGER);
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #10
Source File: MySqlTagService.java    From metacat with Apache License 2.0 6 votes vote down vote up
/**
 * remove.
 *
 * @param name               qualifiedName
 * @param tags               tags
 * @param updateUserMetadata flag to update user metadata
 */
public void remove(final QualifiedName name, final Set<String> tags, final boolean updateUserMetadata) {
    try {
        jdbcTemplate.update(String.format(SQL_DELETE_TAG_ITEM_TAGS_BY_NAME_TAGS,
            "'" + Joiner.on("','").skipNulls().join(tags) + "'"),
            new SqlParameterValue(Types.VARCHAR, name.toString()));
        if (updateUserMetadata) {
            final TagItem tagItem = get(name);
            tagItem.getValues().removeAll(tags);
            final Map<String, Set<String>> data = Maps.newHashMap();
            data.put(NAME_TAGS, tagItem.getValues());
            userMetadataService
                .saveDefinitionMetadata(name, "admin", Optional.of(metacatJson.toJsonObject(data)),
                    true);
        }
    } catch (Exception e) {
        final String message = String.format("Failed to remove tags for name %s", name);
        log.error(message, e);
        throw new UserMetadataServiceException(message, e);
    }
}
 
Example #11
Source File: NamedParameterJdbcTemplateTests.java    From spring-analysis-note with MIT License 6 votes vote down vote up
@Test
public void testQueryForObjectWithRowMapper() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	Customer cust = namedParameterTemplate.queryForObject(SELECT_NAMED_PARAMETERS, params,
			(rs, rownum) -> {
				Customer cust1 = new Customer();
				cust1.setId(rs.getInt(COLUMN_NAMES[0]));
				cust1.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust1;
			});
	assertTrue("Customer id was assigned correctly", cust.getId() == 1);
	assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #12
Source File: NamedParameterJdbcTemplateTests.java    From spring-analysis-note with MIT License 6 votes vote down vote up
@Test
public void testQueryWithRowMapper() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	List<Customer> customers = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
			(rs, rownum) -> {
				Customer cust = new Customer();
				cust.setId(rs.getInt(COLUMN_NAMES[0]));
				cust.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust;
			});
	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #13
Source File: NamedParameterJdbcTemplateTests.java    From spring-analysis-note with MIT License 6 votes vote down vote up
@Test
public void testQueryWithResultSetExtractor() throws SQLException {
	given(resultSet.next()).willReturn(true);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	Customer cust = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
			rs -> {
				rs.next();
				Customer cust1 = new Customer();
				cust1.setId(rs.getInt(COLUMN_NAMES[0]));
				cust1.setForename(rs.getString(COLUMN_NAMES[1]));
				return cust1;
			});

	assertTrue("Customer id was assigned correctly", cust.getId() == 1);
	assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #14
Source File: NamedParameterJdbcTemplateTests.java    From spring-analysis-note with MIT License 6 votes vote down vote up
@Test
public void testExecuteWithTypedParameters() throws SQLException {
	given(preparedStatement.executeUpdate()).willReturn(1);

	params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
	Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
			(PreparedStatementCallback<Object>) ps -> {
				assertEquals(preparedStatement, ps);
				ps.executeUpdate();
				return "result";
			});

	assertEquals("result", result);
	verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setObject(2, 1, Types.INTEGER);
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #15
Source File: MySqlTagService.java    From metacat with Apache License 2.0 6 votes vote down vote up
@Override
public void delete(final QualifiedName name, final boolean updateUserMetadata) {
    try {
        jdbcTemplate
            .update(SQL_DELETE_TAG_ITEM_TAGS_BY_NAME, new SqlParameterValue(Types.VARCHAR, name.toString()));
        jdbcTemplate.update(SQL_DELETE_TAG_ITEM, new SqlParameterValue(Types.VARCHAR, name.toString()));
        if (updateUserMetadata) {
            // Set the tags in user metadata
            final Map<String, Set<String>> data = Maps.newHashMap();
            data.put(NAME_TAGS, Sets.newHashSet());
            userMetadataService
                .saveDefinitionMetadata(name, "admin", Optional.of(metacatJson.toJsonObject(data)),
                    true);
        }
    } catch (Exception e) {
        final String message = String.format("Failed to delete all tags for name %s", name);
        log.error(message, e);
        throw new UserMetadataServiceException(message, e);
    }
}
 
Example #16
Source File: SqlParameterSourceUtils.java    From spring-analysis-note with MIT License 6 votes vote down vote up
/**
 * Create a wrapped value if parameter has type information, plain object if not.
 * @param source the source of parameter values and type information
 * @param parameterName the name of the parameter
 * @return the value object
 */
@Nullable
public static Object getTypedValue(SqlParameterSource source, String parameterName) {
	int sqlType = source.getSqlType(parameterName);
	if (sqlType != SqlParameterSource.TYPE_UNKNOWN) {
		if (source.getTypeName(parameterName) != null) {
			return new SqlParameterValue(sqlType, source.getTypeName(parameterName), source.getValue(parameterName));
		}
		else {
			return new SqlParameterValue(sqlType, source.getValue(parameterName));
		}
	}
	else {
		return source.getValue(parameterName);
	}
}
 
Example #17
Source File: NamedParameterJdbcTemplateTests.java    From spring4-understanding with Apache License 2.0 6 votes vote down vote up
@Test
public void testExecuteWithTypedParameters() throws SQLException {
	given(preparedStatement.executeUpdate()).willReturn(1);

	params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
	Object result = namedParameterTemplate.execute(UPDATE_NAMED_PARAMETERS, params,
			new PreparedStatementCallback<Object>() {
				@Override
				public Object doInPreparedStatement(PreparedStatement ps)
						throws SQLException {
					assertEquals(preparedStatement, ps);
					ps.executeUpdate();
					return "result";
				}
			});

	assertEquals("result", result);
	verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setObject(2, 1, Types.INTEGER);
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #18
Source File: MysqlUserMetadataService.java    From metacat with Apache License 2.0 6 votes vote down vote up
/**
 * delete Data Metadatas.
 *
 * @param uris               uri list
 * @param removeDataMetadata flag to remove data meta data
 */
@SuppressWarnings("checkstyle:methodname")
private void _deleteDataMetadata(
    @Nullable final List<String> uris,
    final boolean removeDataMetadata
) {
    if (uris != null && !uris.isEmpty()) {
        final List<String> paramVariables = uris.stream().map(s -> "?").collect(Collectors.toList());
        final String[] aUris = uris.toArray(new String[0]);
        final String paramString = Joiner.on(",").skipNulls().join(paramVariables);
        final List<Long> ids = jdbcTemplate
            .query(String.format(SQL.GET_DATA_METADATA_IDS, paramString), aUris, (rs, rowNum) -> rs.getLong("id"));
        if (!ids.isEmpty()) {
            final List<String> idParamVariables = ids.stream().map(s -> "?").collect(Collectors.toList());
            final SqlParameterValue[] aIds = ids.stream().map(id -> new SqlParameterValue(Types.BIGINT, id))
                .toArray(SqlParameterValue[]::new);
            final String idParamString = Joiner.on(",").skipNulls().join(idParamVariables);
            jdbcTemplate.update(String.format(SQL.DELETE_DATA_METADATA_DELETE, idParamString), (Object[]) aIds);
            if (removeDataMetadata) {
                jdbcTemplate.update(String.format(SQL.DELETE_DATA_METADATA, idParamString), (Object[]) aIds);
            }
        }
    }
}
 
Example #19
Source File: NamedParameterJdbcTemplateTests.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
@Test
public void testUpdateWithTypedParameters() throws SQLException {
	given(preparedStatement.executeUpdate()).willReturn(1);

	params.put("perfId", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("priceId", new SqlParameterValue(Types.INTEGER, 1));
	int rowsAffected = namedParameterTemplate.update(UPDATE_NAMED_PARAMETERS, params);

	assertEquals(1, rowsAffected);
	verify(connection).prepareStatement(UPDATE_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setObject(2, 1, Types.INTEGER);
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #20
Source File: SqlParameterSourceUtils.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
/**
 * Create a wrapped value if parameter has type information, plain object if not.
 * @param source the source of paramer values and type information
 * @param parameterName the name of the parameter
 * @return the value object
 */
public static Object getTypedValue(SqlParameterSource source, String parameterName) {
	int sqlType = source.getSqlType(parameterName);
	if (sqlType != SqlParameterSource.TYPE_UNKNOWN) {
		if (source.getTypeName(parameterName) != null) {
			return new SqlParameterValue(sqlType, source.getTypeName(parameterName), source.getValue(parameterName));
		}
		else {
			return new SqlParameterValue(sqlType, source.getValue(parameterName));
		}
	}
	else {
		return source.getValue(parameterName);
	}
}
 
Example #21
Source File: MapSqlParameterSourceTests.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
@Test
public void sqlParameterValueRegistersSqlType() throws Exception {
	MapSqlParameterSource msps = new MapSqlParameterSource("FOO", new SqlParameterValue(2, "Foo"));
	assertEquals("Correct SQL Type not registered", 2, msps.getSqlType("FOO"));
	MapSqlParameterSource msps2 = new MapSqlParameterSource();
	msps2.addValues(msps.getValues());
	assertEquals("Correct SQL Type not registered", 2, msps2.getSqlType("FOO"));
}
 
Example #22
Source File: MapSqlParameterSource.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
/**
 * Add a Map of parameters to this parameter source.
 * @param values a Map holding existing parameter values (can be {@code null})
 * @return a reference to this parameter source,
 * so it's possible to chain several calls together
 */
public MapSqlParameterSource addValues(Map<String, ?> values) {
	if (values != null) {
		for (Map.Entry<String, ?> entry : values.entrySet()) {
			this.values.put(entry.getKey(), entry.getValue());
			if (entry.getValue() instanceof SqlParameterValue) {
				SqlParameterValue value = (SqlParameterValue) entry.getValue();
				registerSqlType(entry.getKey(), value.getSqlType());
			}
		}
	}
	return this;
}
 
Example #23
Source File: NamedParameterJdbcTemplateTests.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
@Test
public void testQueryWithRowMapper() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	List<Customer> customers = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params,
			new RowMapper<Customer>() {
				@Override
				public Customer mapRow(ResultSet rs, int rownum) throws SQLException {
					Customer cust = new Customer();
					cust.setId(rs.getInt(COLUMN_NAMES[0]));
					cust.setForename(rs.getString(COLUMN_NAMES[1]));
					return cust;
				}
			});
	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #24
Source File: DirectSqlDatabase.java    From metacat with Apache License 2.0 5 votes vote down vote up
/**
 * Updates the database object.
 * @param databaseInfo database object
 */
public void update(final DatabaseInfo databaseInfo) {
    log.debug("Start: Database update using direct sql for {}", databaseInfo.getName());
    final long start = registry.clock().wallTime();
    try {
        final Long databaseId = getDatabaseId(databaseInfo.getName());
        final DatabaseInfo existingDatabaseInfo = getDatabaseById(databaseId, databaseInfo.getName());
        final Map<String, String> newMetadata = databaseInfo.getMetadata() == null ? Maps.newHashMap()
            : databaseInfo.getMetadata();
        final MapDifference<String, String> diff = Maps.difference(existingDatabaseInfo.getMetadata(), newMetadata);
        insertDatabaseParams(databaseId, diff.entriesOnlyOnRight());
        final Map<String, String> updateParams = diff.entriesDiffering().entrySet().stream()
            .collect(Collectors.toMap(Map.Entry::getKey, s -> s.getValue().rightValue()));
        updateDatabaseParams(databaseId, updateParams);
        final String uri =
            Strings.isNullOrEmpty(databaseInfo.getUri()) ? existingDatabaseInfo.getUri() : databaseInfo.getUri();
        final String newOwner = getOwner(databaseInfo.getAudit());
        final String owner =
            Strings.isNullOrEmpty(newOwner) ? newOwner : existingDatabaseInfo.getAudit().getCreatedBy();
        jdbcTemplate.update(SQL.UPDATE_DATABASE, new SqlParameterValue(Types.VARCHAR, uri),
            new SqlParameterValue(Types.VARCHAR, owner),
            new SqlParameterValue(Types.BIGINT, databaseId));
    } finally {
        this.fastServiceMetric.recordTimer(
            HiveMetrics.TagAlterDatabase.getMetricName(), registry.clock().wallTime() - start);
        log.debug("End: Database update using direct sql for {}", databaseInfo.getName());
    }
}
 
Example #25
Source File: NamedParameterJdbcTemplateTests.java    From spring4-understanding with Apache License 2.0 5 votes vote down vote up
@Test
public void testQueryWithRowCallbackHandler() throws SQLException {
	given(resultSet.next()).willReturn(true, false);
	given(resultSet.getInt("id")).willReturn(1);
	given(resultSet.getString("forename")).willReturn("rod");

	params.put("id", new SqlParameterValue(Types.DECIMAL, 1));
	params.put("country", "UK");
	final List<Customer> customers = new LinkedList<Customer>();
	namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, new RowCallbackHandler() {
		@Override
		public void processRow(ResultSet rs) throws SQLException {
			Customer cust = new Customer();
			cust.setId(rs.getInt(COLUMN_NAMES[0]));
			cust.setForename(rs.getString(COLUMN_NAMES[1]));
			customers.add(cust);
		}
	});

	assertEquals(1, customers.size());
	assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1);
	assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod"));
	verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
	verify(preparedStatement).setObject(1, 1, Types.DECIMAL);
	verify(preparedStatement).setString(2, "UK");
	verify(preparedStatement).close();
	verify(connection).close();
}
 
Example #26
Source File: NamedParameterUtils.java    From effectivejava with Apache License 2.0 5 votes vote down vote up
/**
 * Convert a Map of named parameter values to a corresponding array.
 * @param parsedSql the parsed SQL statement
 * @param paramSource the source for named parameters
 * @param declaredParams the List of declared SqlParameter objects
 * (may be {@code null}). If specified, the parameter metadata will
 * be built into the value array in the form of SqlParameterValue objects.
 * @return the array of values
 */
public static Object[] buildValueArray(
		ParsedSql parsedSql, SqlParameterSource paramSource, List<SqlParameter> declaredParams) {

	Object[] paramArray = new Object[parsedSql.getTotalParameterCount()];
	if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) {
		throw new InvalidDataAccessApiUsageException(
				"Not allowed to mix named and traditional ? placeholders. You have " +
				parsedSql.getNamedParameterCount() + " named parameter(s) and " +
				parsedSql.getUnnamedParameterCount() + " traditional placeholder(s) in statement: " +
				parsedSql.getOriginalSql());
	}
	List<String> paramNames = parsedSql.getParameterNames();
	for (int i = 0; i < paramNames.size(); i++) {
		String paramName = paramNames.get(i);
		try {
			Object value = paramSource.getValue(paramName);
			SqlParameter param = findParameter(declaredParams, paramName, i);
			paramArray[i] = (param != null ? new SqlParameterValue(param, value) : value);
		}
		catch (IllegalArgumentException ex) {
			throw new InvalidDataAccessApiUsageException(
					"No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage());
		}
	}
	return paramArray;
}
 
Example #27
Source File: MysqlUserMetadataService.java    From metacat with Apache License 2.0 5 votes vote down vote up
@Override
@Transactional(readOnly = true)
public List<QualifiedName> searchByOwners(final Set<String> owners) {
    final List<QualifiedName> result = Lists.newArrayList();
    final StringBuilder query = new StringBuilder(SQL.SEARCH_DEFINITION_METADATA_NAMES);
    final List<SqlParameterValue> paramList = Lists.newArrayList();
    query.append(" where 1=0");
    owners.forEach(s -> {
        query.append(" or data like ?");
        paramList.add(new SqlParameterValue(Types.VARCHAR, "%\"userId\":\"" + s.trim() + "\"%"));
    });
    final SqlParameterValue[] params = new SqlParameterValue[paramList.size()];
    try {
        // Handler for reading the result set
        final ResultSetExtractor<Void> handler = rs -> {
            while (rs.next()) {
                final String definitionName = rs.getString("name");
                result.add(QualifiedName.fromString(definitionName, false));
            }
            return null;
        };
        jdbcTemplate.query(query.toString(), paramList.toArray(params), handler);
    } catch (Exception e) {
        log.error("Failed to search by owners", e);
        throw new UserMetadataServiceException("Failed to search by owners", e);
    }
    return result;

}
 
Example #28
Source File: MysqlUserMetadataService.java    From metacat with Apache License 2.0 5 votes vote down vote up
/**
 * executeUpdateForKey.
 *
 * @param query     sql query string
 * @param keyValues parameters
 * @return number of updated rows
 */
private int executeUpdateForKey(final String query, final String... keyValues) {
    try {
        final SqlParameterValue[] values =
            Arrays.stream(keyValues).map(keyValue -> new SqlParameterValue(Types.VARCHAR, keyValue))
                .toArray(SqlParameterValue[]::new);
        return jdbcTemplate.update(query, (Object[]) values);
    } catch (Exception e) {
        final String message = String.format("Failed to save data for %s", Arrays.toString(keyValues));
        log.error(message, e);
        throw new UserMetadataServiceException(message, e);
    }
}
 
Example #29
Source File: SqlParameterSourceUtils.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Create a wrapped value if parameter has type information, plain object if not.
 * @param source the source of paramer values and type information
 * @param parameterName the name of the parameter
 * @return the value object
 */
public static Object getTypedValue(SqlParameterSource source, String parameterName) {
	int sqlType = source.getSqlType(parameterName);
	if (sqlType != SqlParameterSource.TYPE_UNKNOWN) {
		if (source.getTypeName(parameterName) != null) {
			return new SqlParameterValue(sqlType, source.getTypeName(parameterName), source.getValue(parameterName));
		}
		else {
			return new SqlParameterValue(sqlType, source.getValue(parameterName));
		}
	}
	else {
		return source.getValue(parameterName);
	}
}
 
Example #30
Source File: MapSqlParameterSource.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Add a Map of parameters to this parameter source.
 * @param values a Map holding existing parameter values (can be {@code null})
 * @return a reference to this parameter source,
 * so it's possible to chain several calls together
 */
public MapSqlParameterSource addValues(Map<String, ?> values) {
	if (values != null) {
		for (Map.Entry<String, ?> entry : values.entrySet()) {
			this.values.put(entry.getKey(), entry.getValue());
			if (entry.getValue() instanceof SqlParameterValue) {
				SqlParameterValue value = (SqlParameterValue) entry.getValue();
				registerSqlType(entry.getKey(), value.getSqlType());
			}
		}
	}
	return this;
}