Use in-memory db to write tests for Rest controllers

Question

I'm writing a test for a Spring boot Rest controller. This rest controller writes some values to the db.

I want to use in-memory database which Spring provides for this test. According to this doc I have to annotate the test class with @DataJpaTest, which causes this error:

java.lang.IllegalStateException: Failed to load ApplicationContext

Further down in the error stack trace I see the following exception was thrown:

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource': Invocation of init method failed; nested exception is java.lang.IllegalStateException: Failed to replace DataSource with an embedded database for tests. If you want an embedded database please put a supported one on the classpath or tune the replace attribute of @AutoconfigureTestDatabase.

This is the test class which I'm working on:

@RunWith(SpringRunner.class)
@SpringBootTest
@AutoConfigureMockMvc
@DataJpaTest
public class AuthenticationControllerFTest {

    @Autowired 
    private MockMvc mockMvc;

    @MockBean
    private AuthenticationManager authenticationManager;

    @Autowired
    private WebApplicationContext context;

    @Autowired
    private Filter springSecurityFilterChain;

    @Before
    public void setup() {
        mockMvc = MockMvcBuilders.webAppContextSetup(context)
                .addFilters(springSecurityFilterChain).build();
    }

    @Test
    public void testCreate() throws Exception {

        String exampleUserInfo = "{\"name\":\"Salam12333\",\"username\":\"[email protected]\",\"password\":\"Salam12345\"}";
        RequestBuilder requestBuilder = MockMvcRequestBuilders
                .post("/signup")
                .accept(MediaType.APPLICATION_JSON).content(exampleUserInfo)
                .contentType(MediaType.APPLICATION_JSON);

        MvcResult result = mockMvc.perform(requestBuilder).andReturn();

        MockHttpServletResponse response = result.getResponse();
        int status = response.getStatus();
        Assert.assertEquals("http response status is wrong", 200, status);
    }
}

What is causing this error ?

Edit 1 This is the content of my application.properties:

spring.datasource.username = hello
spring.datasource.password = hello
spring.datasource.driver-class-name= com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/myproject?useSSL=false

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE
logging.level.org.springframework.web=DEBUG

server.port = 8443
server.ssl.key-store = classpath:tomcat.keystore
server.ssl.key-store-password = hello
server.ssl.key-password = hello
server.ssl.enabled = true
server.ssl.key-alias=myproject

Edit 2

I added the following to my pom.xml:

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>test</scope>
</dependency>

I created application-test.properties with the following content:

spring.datasource.username= root
spring.datasource.password= password
spring.datasource.driver-class-name= org.h2.Driver
spring.datasource.url= jdbc:h2:mem:db;DB_CLOSE_DELAY=-1

What is the username and passowrd ? Where should I set them ?
I also added @ActiveProfiles("test") to the test class, when I run the test I get an error which includes this line :

Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

"If you want an embedded database please put a supported one on the classpath or tune the replace attribute of @AutoconfigureTestDatabase". So, did you put an embedded database on the classpath? — K.Nicholas
– K.Nicholas, Commented Jul 30, 2017 at 17:18
I add my application.properties file content to the original post. — Arian
– Arian, Commented Jul 30, 2017 at 17:58
application.properties doesn't affect classpath. You need to add h2 or hsqldb to your pom.xml. — K.Nicholas
– K.Nicholas, Commented Jul 30, 2017 at 18:28
spring.datasource.url= jdbc:h2:mem:db;DB_CLOSE_DELAY=-1 -- h2 is not hsqldb. See answer below .. go slowly and carefully. — K.Nicholas
– K.Nicholas, Commented Jul 30, 2017 at 18:40

fg78nc · Accepted Answer · 2017-07-30 20:07:21Z

8

+50

Assuming you annotate class with @SpringBootApplication, which enables auto-configuration and you have H2 dependency on classpath(see below) Spring Boot will see H2 in-memory database dependency and it will create javax.sql.DataSource implementation. Default connection URL is jdbc:h2:mem:testdb and the default username and password are: username: sa and password: empty.

application.properties file

spring.datasource.url=jdbc:h2:mem:tesdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    spring.datasource.driverClassName=org.h2.Driver
    spring.datasource.username=sa
    spring.datasource.password=

    spring.datasource.testWhileIdle = true
    spring.datasource.validationQuery = SELECT 1

    spring.jpa.show-sql = true
    spring.h2.console.enabled=true // if you need console

H2 Dependency

    <dependency>
      <groupId>com.h2database</groupId>
       <artifactId>h2</artifactId>
      <scope>runtime</scope>
   </dependency>

   <dependency> // If you need h2 web console 
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
   </dependency>

You can gain access to h2 console for management http://localhost:8080/h2-console

edited Jul 30, 2017 at 20:07

answered Jul 30, 2017 at 19:13

fg78nc

5,2624 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arian Over a year ago

Are the values for the username and password default ? if not, where should I set them ? Why is the scope runtime and not test ?

fg78nc Over a year ago

@ArianHosseinzadeh I have updated my answer, Credentials are default, you can change them in h2 console, added description to my answer. Not sure about scope - runtime works fine for me. You can change for test.

Yogi · Accepted Answer · 2017-08-01 15:24:12Z

For Testing REST service with in-memory DB, you need to following things:
1. Add h2 dependency in pom.xml

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</dependency>

2. Define h2 configuration in application.properties or application.yaml

spring.jpa.database = h2
spring.datasource.url=jdbc:hsqldb:mem:testdb
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.HSQLDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create

3. Annotate you test class

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)

Complete code will be like:

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class AuthenticationControllerFTest {

    @Autowired 
    private MockMvc mockMvc;

    @MockBean
    private AuthenticationManager authenticationManager;

    @Autowired
    private WebApplicationContext context;

    @Autowired
    private Filter springSecurityFilterChain;

    @Before
    public void setup() {
        mockMvc = MockMvcBuilders.webAppContextSetup(context)
                .addFilters(springSecurityFilterChain).build();
    }

    @Test
    public void testCreate() throws Exception {

        String exampleUserInfo = "{\"name\":\"Salam12333\",\"username\":\"[email protected]\",\"password\":\"Salam12345\"}";
        RequestBuilder requestBuilder = MockMvcRequestBuilders
                .post("/signup")
                .accept(MediaType.APPLICATION_JSON).content(exampleUserInfo)
                .contentType(MediaType.APPLICATION_JSON);

        MvcResult result = mockMvc.perform(requestBuilder).andReturn();

        MockHttpServletResponse response = result.getResponse();
        int status = response.getStatus();
        Assert.assertEquals("http response status is wrong", 200, status);
    }
}

René Winkler · Accepted Answer · 2017-07-27 06:25:42Z

2

Remove both annotations @AutoConfigureMockMvc and @DataJpaTest. You are trying to test the complete applciation, so need the @SpringBootTest annotation. @DataJpaTest is only needed if you want to test only the data apllication slice. Have a look at this: https://spring.io/blog/2016/04/15/testing-improvements-in-spring-boot-1-4

answered Jul 27, 2017 at 6:25

René Winkler

7,2388 gold badges48 silver badges76 bronze badges

2 Comments

Arian Over a year ago

But I don't want to use the actual db. I want to use in-memory db.

Arian Over a year ago

When I remove both annotations, I get this error:

Unsatisfied dependency expressed through field 'mockMvc'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.test.web.servlet.MockMvc' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}

Irfan Bhindawala · Accepted Answer · 2017-08-01 13:49:46Z

In spring boot we do not require to add any thing additional for in memory database configuration except for jar file on class path and application property file (application.properties) on the class path (src/test/resources if maven is used) rest of the things will be taken care by spring boot (beauty of boot).

Another option is to provide profile specific properties file on the class path src/amin/resources (for example application-test.properties)

Both of the file are valid for test configurations

Sample configuration for property file is given below (consider HSQL DB jar on class path):

spring.jpa.hibernate.ddl-auto = create-drop
spring.jpa.database = HSQL
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.HSQLDialect
spring.datasource.driverClassName = org.hsqldb.jdbcDriver
spring.datasource.url: jdbc:hsqldb:mem:scratchdb
spring.datasource.username = sa
spring.datasource.password = pass

K.Nicholas · Accepted Answer · 2017-07-30 18:36:45Z

1

Maybe this helps.

spring.datasource.url=jdbc:hsqldb:mem:testdb
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.database-platform=org.hibernate.dialect.HSQLDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create

See also Configure specific in memory database for testing purpose in Spring

answered Jul 30, 2017 at 18:36

K.Nicholas

11.6k4 gold badges50 silver badges74 bronze badges

5 Comments

Arian Over a year ago

And I assume that I have to configure hsqldb first, right ?

Arian Over a year ago

Is there a way to set username, password and create testdb in Java ?

K.Nicholas Over a year ago

The database is in memory ... what do you mean create testdb? Hibernate will create the tables from your entities on startup. If you want to preload it with values you have to use a SQL script. If you have other questions please search SO or ask a new question.

Arian Over a year ago

My question is regarding your answer. Where does the values for username and password come from ? from my understanding it tries to connect to testdb using values provided for properties username and password.

K.Nicholas Over a year ago

They are hardcoded for hsqldb. I think any database name will do, see Chapter 1. Running and Using HyperSQL.

Ravi Parekh · Accepted Answer · 2021-06-08 09:09:24Z

I believe, you can use below in-memory db with integration test -

This will also help if you are using json[b](though it is in DB2 but some ops like inserting/updating not support with our code) datatype or any other field that is not present in DB2 (compatibility issue).

Then refer this TestContainer - Stackoverflow answer

Pom.xml

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>postgresql</artifactId>
        <version>1.15.1</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>1.15.1</version>
        <scope>test</scope>
    </dependency>

XyzIT.java

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("test")
@Testcontainers

mockMvc = MockMvcBuilders.webAppContextSetup(webApplicationContext).build();
@Test
void test(){
        var mvcResult = mockMvc.perform(MockMvcRequestBuilders.post("/opportunities/process")
                .header("emailId", "[email protected]")
                .header("Authorization", "authorization")
                .header("Content-Type", "application/json").content(objectMapper.writeValueAsString(opportunity))).andReturn();

}

Application-test.yml

  datasource:
    initialization-mode: always
    schema: classpath*:schema-h2.sql  #initial sql script to createDB
    url: jdbc:tc:postgresql:11.9:///
  jpa:
    hibernate.ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
        default_schema: public
    show-sql: true

Collectives™ on Stack Overflow

Use in-memory db to write tests for Rest controllers

6 Answers 6

2 Comments

Comments

2 Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

2 Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related