SimpleJdbcTestUtils: Query table row [SPR-4545]

[spring-projects-issues]

Paul Benedict opened SPR-4545 and commented

My Hibernate integration tests follow a typical pattern: save entity through session, flush session, JDBC select rows from table matching persisted identifiers. I use JDBC to verify my Hibernate mappings.

I'd like to propose a new method that queries a table passing one or multiple parameters:
SimpleJdbcTestUtils.queryRow(String tableName, String keyName, Object keyValue);
SimpleJdbcTestUtils.queryRow(String tableName, Map keys);

which would translate to:
SELECT * FROM {tableName} WHERE {keyName} = {keyValue} respectively.

If you can dream up better semantics, please do.

---

Affects: 2.5.2

[spring-projects-issues]

James Earl Douglas commented

Perhaps something like this would do. I took it for a test drive and it seems do do the trick.

public class JdbcTestUtilsTests extends TestCase {

	// . . .

	public Object queryRow(String tableName, String keyName, Object keyValue) {
		Map keys = new HashMap();
		keys.put(keyName, keyValue);
		return this.queryRow(tableName, keys);
	}

	public Object queryRow(String tableName, final Map keys) {
		StringBuffer keyNamesStringBuffer = new StringBuffer();
		for (Object key : keys.keySet()) {
			if (keyNamesStringBuffer.length() > 0) {
				keyNamesStringBuffer.append(" ,");
			}
			keyNamesStringBuffer.append(key);
		}

		StringBuffer queryStringBuffer = new StringBuffer();
		queryStringBuffer.append("select ");
		queryStringBuffer.append(keyNamesStringBuffer);
		queryStringBuffer.append(" from ");
		queryStringBuffer.append(tableName);
		queryStringBuffer.append(" where ");

		StringBuffer keyValuesStringBuffer = new StringBuffer();
		for (Object key : keys.keySet()) {
			if (keyValuesStringBuffer.length() > 0) {
				keyValuesStringBuffer.append(" and");
			}
			keyValuesStringBuffer.append(key);
			keyValuesStringBuffer.append(" = ? ");
		}

		queryStringBuffer.append(keyValuesStringBuffer);

		return jdbcTemplate.queryForObject(queryStringBuffer.toString(), keys
				.values().toArray(), new RowMapper() {
			public Object mapRow(ResultSet resultSet, int rowNum)
					throws SQLException {
				Map rowMap = new HashMap();
				for (Object key : keys.keySet()) {
					rowMap.put(key, resultSet.getObject((String) key));
				}

				return rowMap;
			}
		});
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}

[spring-projects-issues]

Rossen Stoyanchev commented

This issue has been resolved through a selective bulk update, as part of a larger effort to better manage unresolved issues. To qualify for the update, the issue was either created before Spring 3.0 or affects a version older than Spring 3.0 and is not a bug.

There is a good chance the request was made obsolete, or at least partly outdated, by changes in later versions of Spring including deprecations. It is also possible it didn't get enough traction or we didn't have enough time to address it. One way or another, we didn't get to it.

If you believe the issue, or some aspects of it, are still relevant and worth pursuing at present you may re-open this issue or create a new one with a more up-to-date description.

We thank you for your contributions and encourage you to become familiar with the current process of managing Spring Framework JIRA issues that has been in use for over a year.