MockMVC with a @Query in my Repository or refactor my controller to not use it?

I have a @Query in my Repository that I return data to my controller based on a SQL Query, how would I mock this?

Here is my Repository

package movieweb.movies.repository;
import movieweb.movies.models.UserMovies;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserMoviesRepository extends CrudRepository<UserMovies, Integer> {
@Query(value = "select * from movies, user_movies where movies.movie_id = user_movies.movie_id and uname = ?1", nativeQuery = true)
List<UserMovies> findByUname(String uname);
}

Here is my Controller

package movieweb.movies.controllers;
import movieweb.movies.models.Movies;
import movieweb.movies.models.UserMovies;
import movieweb.movies.repository.UserMoviesRepository;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Query;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class UserMoviesController {
@Autowired
private UserMoviesRepository umRepository;
@CrossOrigin
@PostMapping(path = "/newUserMovie")
public ResponseEntity<UserMovies> addNewUserMovie(@RequestBody UserMovies data){
umRepository.save(data);
return new ResponseEntity<UserMovies>(data, HttpStatus.CREATED);
}
@CrossOrigin
@GetMapping(path="/getUserMovies")
public  List<UserMovies> getUsersMovies(){
return (List<UserMovies>) umRepository.findAll();
}
@CrossOrigin
@GetMapping(path = "/getUserMovies/{uname}")
public  List<UserMovies> getUserMovies(@PathVariable String uname){
return umRepository.findByUname(uname);
}
@CrossOrigin
@DeleteMapping(path ="/deleteUserMovies/{id}")
ResponseEntity deleteUserMovie(@PathVariable Integer id) {
umRepository.deleteById(id);
return new ResponseEntity(HttpStatus.ACCEPTED);
}
@CrossOrigin
@PutMapping("/usermovie/update/{id}")
public ResponseEntity<UserMovies> updateMovie(@RequestBody UserMovies updateMovie, @PathVariable Integer id) {
return umRepository.findById(id)
.map(userMovies -> {
userMovies.setMovieId(updateMovie.getMovieId());
userMovies.setUname(updateMovie.getUname());
umRepository.save(userMovies);
return new ResponseEntity<UserMovies>(userMovies, HttpStatus.OK);
})
.orElseGet(() -> {
updateMovie.setMovieId(id);
umRepository.save(updateMovie);
return new ResponseEntity<UserMovies>(updateMovie, HttpStatus.OK);
});
}
}

And here is what I am trying to do in my test

@Test
void getUserMoviesPerUser() throws Exception{
ArrayList<UserMovies> userMovies = new ArrayList<>();
userMovies.add(new UserMovies(1, "jamie", 1));
userMovies.add(new UserMovies(2, "joe", 1));
userMovies.add(new UserMovies(3, "jamie", 2));
userMovies.add(new UserMovies(4, "joe", 2));
when(userMoviesRepository.findByUname("jamie")).thenReturn(userMovies);
mockMvc.perform(get("/getUserMovies/{uname}", "jamie"))
.andDo(print())
.andExpect(jsonPath("$", hasSize(2)))
.andExpect(jsonPath("$[0].userMovieID", is(1)))
.andExpect(jsonPath("$[0].uname", is("jamie")))
.andExpect(jsonPath("$[0].movieId", is(1)))
.andExpect(jsonPath("$[1].userMovieID", is(3)))
.andExpect(jsonPath("$[1].uname", is("jamie")))
.andExpect(jsonPath("$[1].movieId", is(1)));
Mockito.verify(userMoviesRepository, times(1)).findAll();
}

The problem I am facing is when I run the test above I am not getting back just 2 records like I would expect, I get back 4. This makes sense in the fact that I cant depend on SQL to run tests. Is there a better way to write my controller to get the data based on movie_id?

In your code you are creating 4 records for mocking then simply remove 2 of them so that you have 2 records as response.

@Test
void getUserMoviesPerUser() throws Exception{
ArrayList<UserMovies> userMovies = new ArrayList<>();
userMovies.add(new UserMovies(1, "jamie", 1));
userMovies.add(new UserMovies(2, "joe", 1));
when(userMoviesRepository.findByUname("jamie")).thenReturn(userMovies);
mockMvc.perform(get("/getUserMovies/{uname}", "jamie"))
.andDo(print())
.andExpect(jsonPath("$", hasSize(2)))
.andExpect(jsonPath("$[0].userMovieID", is(1)))
.andExpect(jsonPath("$[0].uname", is("jamie")))
.andExpect(jsonPath("$[0].movieId", is(1)))
.andExpect(jsonPath("$[1].userMovieID", is(3)))
.andExpect(jsonPath("$[1].uname", is("jamie")))
.andExpect(jsonPath("$[1].movieId", is(1)));
Mockito.verify(userMoviesRepository, times(1)).findAll();
}

I think you need to return the only records you were expecting. like

@Test
void getUserMoviesPerUser() throws Exception{
ArrayList<UserMovies> userMovies = new ArrayList<>();
userMovies.add(new UserMovies(1, "jamie", 1));
userMovies.add(new UserMovies(3, "jamie", 2));
when(userMoviesRepository.findByUname("jamie")).thenReturn(userMovies);
mockMvc.perform(get("/getUserMovies/{uname}", "jamie"))
.andDo(print())
.andExpect(jsonPath("$", hasSize(2)))
.andExpect(jsonPath("$[0].userMovieID", is(1)))
.andExpect(jsonPath("$[0].uname", is("jamie")))
.andExpect(jsonPath("$[0].movieId", is(1)))
.andExpect(jsonPath("$[1].userMovieID", is(3)))
.andExpect(jsonPath("$[1].uname", is("jamie")))
.andExpect(jsonPath("$[1].movieId", is(1)));
Mockito.verify(userMoviesRepository, times(1)).findAll();
}