Today we’ll tackle JDBC options for testing in Spring.

Before we start, there’s one more annotation to learn from Spring: @Repository. We put it on a class to tell Spring that this is a data class, talking to a data source. A @Repository is basically a component or a bean. Repositories are the Data Access Objects we use to talk to the database.

Our DAO uses JdbcTemplate which is Spring main gateway to the database. It is configured with a data source to talk to a specific database. For example, a configuration for data source will look like this:

@Configuration
public class JdbcTestConfiguration {

	@Bean
	public JdbcTemplate jdbcTemplate(DataSource datasource ) {
		return new JdbcTemplate(dataSource());
	}

	@Bean
	public DataSource dataSource() {
		return DataSourceBuilder.create()
				.driverClassName("org.h2.Driver")
				.url("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1")
				.username("sa")
				.password("sa")
				.build();
			}
}Code language: PHP (php)

Obviously, for integration tests, we want to work with a different data source than production. Using Spring handy DataSourceBuilder class, giving it different database properties, we can use a different database for each integration test class. Here, I’m using H2 in-memory database basic configuration. All the tricks we’ve already discussed with configurations apply here.

A repository object looks like this:

@Repository
public class ItemRepository {

	@Autowired JdbcTemplate jdbcTemplate;

	public void addItem(Item item) {
				String name = item.getName();
				Int itemValue = item.getValue();
				jdbcTemplate.update(
					"INSERT INTO items(name, value) VALUES(?,?)"
					, name, itemValue);
	}
}Code language: JavaScript (javascript)

This repository uses the JdbcTemplate we create in the integration test configuration above, so when it loads, it is already connected to an existing database. It is ready to work – at least to add items.

The missing link here, is obviously the table creation. We can use the @SQL scripts we explored last time to create it before the integration test runs. Now we have a way to talk to the database before, during and after the integration test. We have achieved full database integration. Almost.

Suppose I want to write an integration test to check that data was actually added to the database. Something like this:

@Test
public void itemIsAddedByController() {
	Item item = new Item("Item1", 2);
	//The controller calls ItemRepository's addItem method
	controller.addItem(item);

	assertEquals(2, itemRepository.findByName("Item1").getItemValue());
}Code language: PHP (php)

Integration tests usually don’t work directly against the repositories , they usually operate on their users, like the controller in our example. But we might need access for certain operations for the integration tests.

In our case, our repository doesn’t have a findByName method, that will help us check if data was actually persisted. Another example would be a deleteAllItems method, I would want to run at the end of each integration test to clean the table.

We have two options: The first is to define additional methods on the DAO for the integration test. For example:

@Repository
public class ItemRepository {

	@Autowired JdbcTemplate jdbcTemplate;

	public void addItem(Item item) {
		String name = item.getName();
		Int itemValue = item.getValue();
		jdbcTemplate.update(
		"INSERT INTO items(name, value) VALUES(?,?)"
		, name, itemValue);
	}

	public Item findByName(String name) {
		Item item = jdbcTemplate.queryForObject(
				"select * from items where name=?",
				new Object[] {name},
				new BeanPropertyRowMapper<Item>(Item.class)
				);
		return item;
	}
}Code language: JavaScript (javascript)

Another option is to use Spring injection flexibility. Instead of using the production ItemRepository, we can derive a class from it TestItemRepository. It can have more accessibility (but we need to make the base’s JdbcTemplate protected to use it in the derived repository):

@Repository
public class TestItemRepository extends ItemRepository {

	public Item findByName(String name) {
		Item item = jdbcTemplate.queryForObject(
				"select * from items where name=?",
				new Object[] {name},
				new BeanPropertyRowMapper<Item>(Item.class)
				);
		return item;
	}
}Code language: JavaScript (javascript)

And use our JdbcTestConfiguration to inject it instead. It does require casting in the integration test body:

@Test
public void itemIsAddedByController_WithTestItemRepository() {
	Item item = new Item("Item1", 2);
	//The controller calls ItemRepository's addItem method
	controller.addItem(item);

	TestItemRepository testRepository = (TestItemRepository) itemRepository;
	assertEquals(2, testRepository.findByName("Item1").getValue());
}Code language: PHP (php)

We added more accessibility, in return for some testing code. That’s a fair price.

The final option is inject the JdbcTemplate bean directly and use it to get the data we need:

@Autowired JdbcTemplate jdbcTemplate;

@Test
public void itemIsAddedByController_UsingJdbcTemplateToQuery() {
	Item item = new Item("Item1", 2);
	//The controller calls ItemRepository's addItem method
	controller.addItem(item); 

	Item addedItem = jdbcTemplate.queryForObject(
			"select * from items where name=?", 
			new Object[] {"Item1"},
			new BeanPropertyRowMapper<Item>(Item.class)
			);
	
	assertEquals(2, addedItem.getValue());
}Code language: JavaScript (javascript)

Right. Enough JDBC. Next time: JPA.

You want to know more about Spring Testing, don’t you? Check out the Spring API Testing workshop and my book “Everyday Spring Testing“.

Categories: Uncategorized

2 Comments

prithvi · September 6, 2021 at 2:20 pm

very nice article, but still cant understand when running the test how does it switches jdbc template to test jdbc template

    Gil Zilberfeld · September 7, 2021 at 10:09 am

    Hi, when running in test mode, you need to supply the JDBC template, configured to your settings. Because Spring loads the test application context, it gets “switched”, at load time. So only the test JDBC template gets loaded.

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *