Integration tests with Spring Boot,Testcontainers and MySQL

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.

Using Special JDBC URL ( This is specific to Database containers)
Auto handling containers using @Container (Junit 5) or @ClassRule (Junit4) annotations
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

Integration tests with Spring Boot,Testcontainers and MySQL

Adding Dependencies

Using @Container

Manual container starting

LocalStack – Mock AWS cloud in your local development

Spring Boot Integration Tests with docker-compose

How to store list of strings in single column using JPA

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

Comparing Native Image Size of Java ,Spring Boot, Quarkus and Micronaut

Adding Dependencies

Using @Container

Manual container starting

Similar Posts