Selects are IN

Today it will be short. I will write about one small Spring feature that I have discovered recently. It is another nice functionality of SimpleJdbcTemplate.

Imagine that you want to find all account with given account numbers. Since we want to gain maximal performance we want to do it in one SQL statement.

SELECT ACCOUNT_NUMBER, BALANCE FROM ACCOUNT WHERE ACCOUNT_NUMBER IN (?, ?)

Using Spring it is easy. The only trouble is, that I do not know number of account numbers beforehand. Therefore I do not know the number of question marks needed in the SQL query. Of course I can create the statement dynamically using string manipulation, but the code would be messy. Fortunately Spring comes to rescue. If I use named parameters in SimpleJdbcTemplate, Spring will automatically do everything for me. So the code will be nice and simple as it should be

	public List<Account> findAccounts(Set<String> accountNumbers)
	{
		return getSimpleJdbcTemplate().query(
				"SELECT ACCOUNT_NUMBER, BALANCE FROM ACCOUNT WHERE ACCOUNT_NUMBER IN (:accountNumbers)",
				ACCOUNT_ROW_MAPPER,
				Collections.singletonMap("accountNumbers", accountNumbers)
		);
	}

Nice, isn't it? Source code can be found in SVN. More details are in the Spring documentation.

Tags: , ,

Comments are closed.