@DataJpaTest updating actual data in case of MySQL but working fine with H2

Question

I am learning @DataJpaTest, my test case is as below

import com.demo.mockito.entity.StudentEntity;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.mockito.Mockito.times;
import static org.mockito.Mockito.verify;
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class StudentRepositoryTest {
    @Autowired
    private StudentRepository studentRepository;
    @Test
    public void findAll() {
        StudentEntity student1 = new StudentEntity("shrikant", new Date());
        studentRepository.save(student1);
        List<StudentEntity> entityList = studentRepository.findAll();
        assertEquals(1, entityList.size());
    }
}

it's giving me the error

expected: <1> but was: <33>
Expected :1
Actual   :33
<Click to see difference>

because right now there are 33 records in DB, and with every save test case, it increases.

src/main/test/application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/student_db?jdbcCompliantTruncation=false&sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.generate-ddl=true
spring.jpa.database.schema=student_db
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

build.gradle

plugins {
    id 'java'
    id 'org.springframework.boot' version '2.2.6.RELEASE'
    id 'io.spring.dependency-management' version '1.0.9.RELEASE'
}
group 'com.demo.mockito'
version '1.0-SNAPSHOT'
repositories {
    mavenCentral()
}
dependencies {
    compile 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    compileOnly 'org.projectlombok:lombok'
    annotationProcessor 'org.projectlombok:lombok'
    testRuntime('org.junit.jupiter:junit-jupiter-engine:5.2.0')
    runtime 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}

if i use h2 instead, it gives correct result beucase every time it recreates a new instance with no data.

is this intended behavior? or am I doing something wrong, is h2 standard in case of database testing. but I don't want to configure another database in my application when my intention is to test MySQL only.

Answer 1

if i use h2 instead, it gives correct result because every time it recreates a new instance with no data.

You answered yourself to your question.
Bu default, at each Spring Boot container starts (which happens when you define a test with @SpringBootTest or the test slicing @DataJpaTest annotation), a new database instance is created when you use an in-memory DB such as H2 (that is the default behavior of H2 that you may change) while when you use MySQL, Spring Boot doesn't use that strategy by default. It does not change the DB content.
The official doc states indeed :

Spring Boot chooses a default value for you based on whether it thinks your database is embedded. It defaults to create-drop if no schema manager has been detected or none in all other cases.

About :

but I don't want to configure another database in my application when my intention is to test MySQL only.

For unit tests you want to use in-memory DB as H2 because that is straight and doesn't require a long/complex setup (that is populating/cleaning DB state).
For integration tests you want to use the target DB (here MySQL) because you want to write tests that are the closest possible of your application behavior. To achieve that, you have to use a specific DB (a test database), you also have to populate fixture data for the tests and at last you have to clean data to make tests to be reproducible.

Both kinds of tests are complementary.