how to create a Mock database and add data in mock db for integration testing using postgres db and java

You can use testcontainers for achieving this. You can watch this video to get an idea about this and how to configure it in your spring boot application

For your spring integration test create a configuration like as shown below

AbstractIT.java

@Testcontainers
@SpringBootTest(webEnvironment= SpringBootTest.WebEnvironment.RANDOM_PORT, properties = {"spring.main.allow-bean-definition-overriding=true"})
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // don't replace our DB with an in-memory one
@ContextConfiguration(initializers = AbstractIT.DockerPostgresDataSourceInitializer.class)
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public abstract class AbstractIT {

    private static PostgreSQLContainer<?> postgresDBContainer = new PostgreSQLContainer<>("postgres:9.6")
            .withUrlParam("TC_DAEMON", "true")
            .withFileSystemBind("docker/db", "/docker-entrypoint-initdb.d", BindMode.READ_WRITE);

    static {
        postgresDBContainer.start();
    }

    public static class DockerPostgresDataSourceInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext applicationContext) {
            Assertions.assertNotNull(applicationContext);
            TestPropertySourceUtils.addInlinedPropertiesToEnvironment(
                    applicationContext,
                    "spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true",
                    "spring.datasource.driver-class-name="+postgresDBContainer.getDriverClassName(),
                    "spring.datasource.url=" + postgresDBContainer.getJdbcUrl(),
                    "spring.datasource.username=" + postgresDBContainer.getUsername(),
                    "spring.datasource.password=" + postgresDBContainer.getPassword()
            );
        }
    }
}

Now you can write your Integration test like as shown in the below sample example

UserControllerIT.java

class UserControllerIT extends AbstractIT {

    @Autowired
    private TestRestTemplate template;

    @Autowired
    private UserRepository userRepository;

    @Test
    @Order(1)
    @DisplayName("Testing to get all the user details")
    void getAllUsersTest() {
        ResponseEntity<List<UserDTO>> response = this.template.exchange("/v1/users", GET, HttpEntity.EMPTY, new ParameterizedTypeReference<List<UserDTO>>() {
        });
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);
        assertThat(response.getBody())
                .isNotEmpty()
                .hasSize(3)
                .extracting(UserDTO::getEmail).containsAnyOf("[email protected]");
    }

    @Test
    @Order(2)
    @DisplayName("Testing for saving user details")
    void saveUsersTest() {
        ResponseEntity<Void> response = this.template.postForEntity("/v1/users", new HttpEntity<>(buildUserRequest()), Void.class);
        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.CREATED);
        long usersCount = userRepository.count();
        assertThat(usersCount).isEqualTo(4);
    }

    @Test
    @Order(3)
    @DisplayName("Testing to deleting user details by id")
    void deleteUsersTest() {

        this.template.delete("/v1/users/{id}", singleParam("id", "21"));

        boolean userExists = userRepository.existsById(21L);
        assertThat(userExists).isFalse();
    }

    @Test
    @Order(4)
    @DisplayName("Testing to finding user details by id")
    void findUserByIdTest() {
        ResponseEntity<UserDTO> response = this.template.getForEntity("/v1/users/{id}", UserDTO.class, singleParam("id", "22"));

        assertThat(response.getStatusCode()).isEqualTo(HttpStatus.OK);

        assertThat(response.getBody())
                .extracting(UserDTO::getEmail, UserDTO::getFirstName, UserDTO::getLastName)
                .containsExactly("[email protected]", "John", "Duke");
    }

    private UserDTO buildUserRequest() {
        return UserDTO.builder()
                .email("[email protected]")
                .lastName("Maria")
                .firstName("Fernandes")
                .build();
    }

    private Map<String, String> singleParam(String key, String value) {
        Map<String, String> params = new HashMap<>();
        params.put(key, value);
        return params;
    }
}

You can see the full running application under this Github Repo - https://github.com/nidhishkrishnan/stackoverflow/tree/master/employee-testcontainer