I use Spring Data JPA in my project. When I request a list of movies made by a particular director, I get:
javax.persistence.NonUniqueResultException: query did not return a unique result: 11
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:128) ~[hibernate-core-5.6.1.Final.jar:5.6.1.Final]
at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1672) ~[hibernate-core-5.6.1.Final.jar:5.6.1.Final]
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:111) ~[hibernate-core-5.6.1.Final.jar:5.6.1.Final]
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:198) ~[spring-data-jpa-2.6.0.jar:2.6.0]
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) ~[spring-data-jpa-2.6.0.jar:2.6.0]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155) ~[spring-data-jpa-2.6.0.jar:2.6.0]
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143) ~[spring-data-jpa-2.6.0.jar:2.6.0]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.6.0.jar:2.6.0]
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.6.0.jar:2.6.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159) ~[spring-data-commons-2.6.0.jar:2.6.0]
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138) ~[spring-data-commons-2.6.0.jar:2.6.0]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) ~[spring-data-commons-2.6.0.jar:2.6.0]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.13.jar:5.3.13]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.13.jar:5.3.13]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.13.jar:5.3.13]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.13.jar:5.3.13]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145) ~[spring-data-jpa-2.6.0.jar:2.6.0]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.13.jar:5.3.13]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.13.jar:5.3.13]
at com.sun.proxy.$Proxy113.findDirectorsByDirectorId(Unknown Source) ~[na:na]
at net.yukitteru.what_to_watch.controller.MovieController.showMovies(MovieController.java:33) ~[main/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.13.jar:5.3.13]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.13.jar:5.3.13]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.13.jar:5.3.13]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) ~[tomcat-embed-core-9.0.55.jar:4.0.FR]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.13.jar:5.3.13]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.55.jar:4.0.FR]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.13.jar:5.3.13]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.13.jar:5.3.13]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.13.jar:5.3.13]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.13.jar:5.3.13]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.13.jar:5.3.13]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.3.13.jar:5.3.13]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.55.jar:9.0.55]
at java.base/java.lang.Thread.run(Thread.java:829) ~[na:na]
MovieRepository:
@Repository
public interface MovieRepository extends JpaRepository<Movies, Integer> {
List<Movies> findAllByDirectorsById(Directors director);
}
DirectorRepository:
@Repository
public interface DirectorRepository extends JpaRepository<Directors, Integer> {
Directors findDirectorsByDirectorId(Persons directorId);
}
PersonRepository:
@Repository
public interface PersonRepository extends JpaRepository<Persons, Integer> {
Persons findPersonsByName(String name);
}
MovieController:
@Controller
@RequestMapping("/movies")
public class MovieController {
private final DirectorRepository directorRepository;
private final MovieRepository movieRepository;
private final PersonRepository personRepository;
@Autowired
public MovieController(DirectorRepository directorRepository, MovieRepository movieRepository, PersonRepository personRepository) {
this.directorRepository = directorRepository;
this.movieRepository = movieRepository;
this.personRepository = personRepository;
}
@GetMapping("")
public List<Movies> showMovies() {
List<Movies> m = movieRepository.findAllByDirectorsById(
directorRepository.findDirectorsByDirectorId(
personRepository.findPersonsByName("Mel Brooks")));
return m;
}
}
I can't understand why when sql querying everything works as it should, but when using expressions to search the repository, it doesn't work properly. After reading similar questions, I didn't find anything that solved my problem.
Directors Entity:
@Entity
@NoArgsConstructor
@Setter
@EqualsAndHashCode
public class Directors {
private Integer id;
private Movies movieId;
private Persons directorId;
@Id
@Column(name = "id", nullable = false)
public Integer getId() {
return id;
}
@ManyToOne
@JsonIgnore
@JoinColumn(name = "movie_id", referencedColumnName = "id")
public Movies getMovieId() {
return movieId;
}
@ManyToOne
@JsonIgnore
@JoinColumn(name = "director_id", referencedColumnName = "id")
public Persons getDirectorId() {
return directorId;
}
}
Persons Entity:
@Entity
@NoArgsConstructor
@Setter
@EqualsAndHashCode
public class Persons {
private Integer id;
private String name;
private Set<Directors> directorsById;
private Set<Actors> actorsById;
@Id
@Column(name = "id", nullable = false)
public Integer getId() {
return id;
}
@Basic
@Column(name = "name", nullable = true, length = -1)
public String getName() {
return name;
}
public void Name(String name) {
this.name = name;
}
@OneToMany(mappedBy = "directorId")
public Set<Directors> getDirectorsById() {
return directorsById;
}
@OneToMany(mappedBy = "personId")
public Set<Actors> getActorsById() {
return actorsById;
}
}
Movies Entity:
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Setter
@EqualsAndHashCode
public class Movies {
private Integer id;
private String title;
private String releaseDate;
private Long budget;
private Long revenue;
private Double popularity;
private Integer runtime;
private Double rating;
private String overview;
private Languages originalLanguage;
private Collections belongsToCollection;
private Set<MoviesGenres> moviesGenresById;
private Set<Directors> directorsById;
private Set<Actors> actorsById;
private Set<MoviesKeywords> moviesKeywordsById;
private Set<SpokenLanguages> spokenLanguagesById;
private Set<ProductionCountries> productionCountriesById;
private Set<MoviesProductionCompanies> moviesProductionCompaniesById;
@Id
@Column(name = "id", nullable = false)
public Integer getId() {
return id;
}
@Basic
@Column(name = "title", nullable = true, length = -1)
public String getTitle() {
return title;
}
@Basic
@Column(name = "release_date", nullable = true, length = -1)
public String getReleaseDate() {
return releaseDate;
}
@Basic
@Column(name = "budget", nullable = true)
public Long getBudget() {
return budget;
}
@Basic
@Column(name = "revenue", nullable = true)
public Long getRevenue() {
return revenue;
}
@Basic
@Column(name = "popularity", nullable = true, precision = 0)
public Double getPopularity() {
return popularity;
}
@Basic
@Column(name = "runtime", nullable = true)
public Integer getRuntime() {
return runtime;
}
@Basic
@Column(name = "rating", nullable = true, precision = 0)
public Double getRating() {
return rating;
}
@Basic
@Column(name = "overview", nullable = true, length = -1)
public String getOverview() {
return overview;
}
@ManyToOne
@JsonIgnore
@JoinColumn(name = "original_language", referencedColumnName = "id")
public Languages getOriginalLanguage() {
return originalLanguage;
}
@ManyToOne
@JsonIgnore
@JoinColumn(name = "belongs_to_collection", referencedColumnName = "id")
public Collections getBelongsToCollection() {
return belongsToCollection;
}
@OneToMany(mappedBy = "movieId")
public Set<MoviesGenres> getMoviesGenresById() {
return moviesGenresById;
}
@OneToMany(mappedBy = "movieId")
public Set<Directors> getDirectorsById() {
return directorsById;
}
@OneToMany(mappedBy = "movieId")
public Set<Actors> getActorsById() {
return actorsById;
}
@OneToMany(mappedBy = "movieId")
public Set<MoviesKeywords> getMoviesKeywordsById() {
return moviesKeywordsById;
}
@OneToMany(mappedBy = "movieId")
public Set<SpokenLanguages> getSpokenLanguagesById() {
return spokenLanguagesById;
}
@OneToMany(mappedBy = "movieId")
public Set<ProductionCountries> getProductionCountriesById() {
return productionCountriesById;
}
@OneToMany(mappedBy = "movieId")
public Set<MoviesProductionCompanies> getMoviesProductionCompaniesById() {
return moviesProductionCompaniesById;
}
}
CodePudding user response：
personsRespository.findByPersosnName() has more than one result, that is a list, since the method has return type not a list, this is what is causing the issue.
Check your database, there must be more data in the Persons table with the same name.
Try creating a method Persons List findPersonsByNameList(String name). Check if this is actually returning a list with only 1 value with the same parameter.
CodePudding user response：
The problem is that one Persons can be many Directors (i.e. have directed many movies).
The DirectorRepository is therefore wrong:
@Repository
public interface DirectorRepository extends JpaRepository<Directors, Integer> {
Directors findDirectorsByDirectorId(Persons directorId);
}
implies that a Persons can only ever be the Directors of at most one movie.
What should that method return if the Persons is the Directors of more than one movie? Should it pick one at random? That wouldn't be very useful! And it would leave you wondering: why does MovieRepository.findAllByDirectorsById() only ever return one movie?
That is why it was decided that a query method that finds more results than it can return throws a NonUniqueResultException.
How you can fix it? You need to declare the findDirectorsByDirectorId() method in such a way that it can return multiple Directors:
@Repository
public interface DirectorRepository extends JpaRepository<Directors, Integer> {
List<Directors> findDirectorsByDirectorId(Persons directorId);
}
and in turn you need to declare the method MovieRepository.findAllByDirectorsById() so that it can accept multiple Directors:
@Repository
public interface MovieRepository extends JpaRepository<Movies, Integer> {
List<Movies> findAllByDirectorsIn(List<Directors> director);
}