Integration tests with Spring Boot,Testcontainers and MySQL

BySuresh Gadupu Java

In this blog post I show how to use mysql database with Testcontainers for doing database integration tests in Spring Boot application.

I have already written a detailed blog post about database integration testing with Spring Boot and Testcontainers in following article

Spring Boot integration testing with Testcontainers

In above blog post, I have used PostgresSQL database.

In this blog post , I will only cover the changes you need to do if you are using MySQL database.

Adding Dependencies

Add MySQL specific dependencies for test containers and MySQL driver library.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	...
	<properties>
		<java.version>11</java.version>
		<testcontainers.version>1.16.3</testcontainers.version>
	</properties>
	<dependencies>
		....
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>junit-jupiter</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>mysql</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.testcontainers</groupId>
				<artifactId>testcontainers-bom</artifactId>
				<version>${testcontainers.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
	......

</project>

2) Change the import SQL statements.

If you are importing initial data for testing purpose make sure that all table names are in lower case.

MySQL by default stores tables names in lower case and and while running queries also it expects table names in lower cases.

DELETE FROM employee;
DELETE FROM department;

INSERT INTO department(id,name) VALUES(100,'HR');
INSERT INTO department(id,name) VALUES(101,'PDE');
INSERT INTO department(id,name) VALUES(102,'Marketing');

INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(1,'Alex','Bonilla','M','1990-12-07','2011-04-01',100);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(2,'Duke','Stacey','M','1989-07-11','2012-08-21',100);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(3,'Hayley','Drake','M','1992-10-01','2007-07-06',101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(4,'Nancie','Beasley','M','1978-02-21','2003-03-03',101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(5,'Wyatt','Holder','M','1994-12-01','2016-02-15',101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(6,'Tobey','Drake','M','1992-10-01','2014-06-11',102);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(7,'Marvin','Hope','F','1978-02-21','2003-09-10',102);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(8,'Ibrar','Garcia','F','1994-12-01','2004-12-21',102);

If you see error like below , you have issue with table name casing.

Failed to execute SQL script statement #3 of class path resource [import.sql]: INSERT INTO Department(id,name) VALUES(100,'HR'); nested exception is java.sql.S
QLSyntaxErrorException: Table 'eis.Department' doesn't exist

MySQl does not support sequences directly.

If you have any Sequence related sql statement in import.sql file you should remove them.

MySQL has autoincrement keyword which acts like sequence.

For hibernate to generate table column with auto_increment keyword, you should change the Entity class. id field should use GenerationType.IDENTITY strategy.

@Entity
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

The generated table looks like below

Hibernate: create table employee (id integer not null auto_increment, 
                       birth_date date, 
                      first_name varchar(255),
                      gender varchar(255), 
                      hire_date date, 
                      last_name varchar(255), 
                      department_id integer, 
                      primary key (id)) engine=InnoDB

We can start containers using Testcontainers in 3 ways.

  1. Using Special JDBC URL ( This is specific to Database containers)
  2. Auto handling containers using @Container (Junit 5) or @ClassRule (Junit4) annotations
  3. Manual container starting

1.Using Special JDBC URL

If you are using MySQL 8.0, you can change application.properties file like below

spring.datasource.url=jdbc:tc:mysql:8.0.28:////eis
spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=true
#spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always

spring.datasource.username=admin
spring.datasource.password=admin
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.show-sql=true

If you are suing MySQl 5.7.x

spring.datasource.url=jdbc:tc:mysql:5.7.37:////eis

spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=true
#spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.mode=always

spring.datasource.username=admin
spring.datasource.password=admin
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL57Dialect
spring.jpa.show-sql=true

You can download source code from GitHub

Using @Container

If you are using Junit5 and Springboot version >= 2.2.6

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@Testcontainers
@DirtiesContext
public  class BaseIT {

	@Container
	public static MySQLContainer<?> mySqlDB = new MySQLContainer<>
			("mysql:5.7.37")
			.withDatabaseName("eis")
			.withUsername("admin")
			.withPassword("admin");


	@DynamicPropertySource
	public static void properties(DynamicPropertyRegistry registry) {
		registry.add("spring.datasource.url",mySqlDB::getJdbcUrl);
		registry.add("spring.datasource.username", mySqlDB::getUsername);
		registry.add("spring.datasource.password", mySqlDB::getPassword);

	}


	@Autowired
	protected TestRestTemplate testRestTemplate ;

}

If you use the Junit5 and Springboot version < 2.2.6, you can use following code

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(initializers = BaseIT2.TestEnvInitializer.class)
@Testcontainers
@DirtiesContext
public  class BaseIT2 {

	@Container
	public static MySQLContainer<?> mySqlDB = new MySQLContainer<>
			("mysql:5.7.37")
			.withDatabaseName("eis")
			.withUsername("admin")
			.withPassword("admin");


	static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

		@Override
		public void initialize(ConfigurableApplicationContext applicationContext) {
			TestPropertyValues values = TestPropertyValues.of(
					"spring.datasource.url=" + mySqlDB.getJdbcUrl(),
					"spring.datasource.password=" + mySqlDB.getPassword(),
					"spring.datasource.username=" + mySqlDB.getUsername()
			);
			values.applyTo(applicationContext);

		}

	}
	
	@Autowired
	protected TestRestTemplate testRestTemplate ;

}

You can download source code from GitHub

Manual container starting

If you are using Junit5 and Springboot version >= 2.2.6

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@Testcontainers
@DirtiesContext
public  class BaseIT {

	public static MySQLContainer<?> mySqlDB;

	static {
		mySqlDB = new MySQLContainer<>("postgres:mysql:5.7.37")
				.withDatabaseName("eis")
				.withUsername("admin")
				.withPassword("admin");

		mySqlDB.start();
	}


	@DynamicPropertySource
	public static void properties(DynamicPropertyRegistry registry) {
		registry.add("spring.datasource.url",mySqlDB::getJdbcUrl);
		registry.add("spring.datasource.username", mySqlDB::getUsername);
		registry.add("spring.datasource.password", mySqlDB::getPassword);

	}


	@Autowired
	protected TestRestTemplate testRestTemplate ;

}

If you use the Junit5 and Springboot version < 2.2.6, you can use following code

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(initializers = BaseIT2.TestEnvInitializer.class)
@Testcontainers
@DirtiesContext
public  class BaseIT2 {
	public static MySQLContainer<?> mySqlDB;

	static {
		mySqlDB = new MySQLContainer<>("postgres:mysql:5.7.37")
				.withDatabaseName("eis")
				.withUsername("admin")
				.withPassword("admin");

		mySqlDB.start();
	}


	static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

		@Override
		public void initialize(ConfigurableApplicationContext applicationContext) {
			TestPropertyValues values = TestPropertyValues.of(
					"spring.datasource.url=" + mySqlDB.getJdbcUrl(),
					"spring.datasource.password=" + mySqlDB.getPassword(),
					"spring.datasource.username=" + mySqlDB.getUsername()
			);
			values.applyTo(applicationContext);

		}

	}
	
	@Autowired
	protected TestRestTemplate testRestTemplate ;

}

You can download source code from GitHub

Similar Posts

Developing GraphQL API with Netflix DGS Framework (Part-I)

BySuresh Gadupu Java

While REST is common standard nowadays for developing the API for applications, If you have multiple clients like mobile desktop, web applications requesting different data , Rest API mostly results either under fetching or over fetching the data. We can use the GraphQL frameworks to develop API to resolve over fetching or under fetching data…

Integration testing AWS Lambda with Localstack and Testcontainers

BySuresh Gadupu Java

In my previous blog post, I covered how we can mock AWS services with LocalStack and test AWS Lambda from the terminal. In this post, I will cover how can we do integration testing of AWS Lambda using LocalStack and TestContainers with Java. In this post, I will demonstrate the event published to Kinesis stream…

Spring Boot integration testing with Testcontainers

BySuresh Gadupu Java

Springboot provides excellent support for unit testing and integration testing. I have covered how to do unit testing for Springboot controllers in various ways in one of my previous articles. While doing unit testing we try to test a single method while mocking all the dependencies. While doing integration testing we see that interaction between…

Building Spring Boot Native Applications

BySuresh Gadupu Java

Spring team is developing spring native module which provides support for compiling Spring applications to native executables using the GraalVM native-image compiler. Native applications provide following advantages compared to JVM based applications Required software Setting up the project To create a project, go to https://start.spring.io and select following spring version and dependencies. Note 1) Spring Native 0.10.5 only supports Spring Boot…