org.springframework.jdbc.core.SqlParameterValue Java Examples

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

}
 

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

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

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