0

I have two tables user and userprofiles.

User

mysql> SHOW columns FROM user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(45)  | YES  |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
| email    | varchar(45)  | NO   | UNI | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

and UserProfiles 

mysql> SHOW columns FROM userprofiles;
+---------------+---------------+------+-----+-------------------------+----------------+
| Field         | Type          | Null | Key | Default                 | Extra          |
+---------------+---------------+------+-----+-------------------------+----------------+
| userprofileid | int(11)       | NO   | PRI | NULL                    | auto_increment |
| userid        | int(11)       | NO   | MUL | NULL                    |                |
| userpics      | varchar(45)   | YES  |     | /images/faces/face2.jpg |                |.
.
.
.
| datecreated   | datetime      | YES  |     | CURRENT_TIMESTAMP       |                |
+---------------+---------------+------+-----+-------------------------+----------------+
15 rows in set (0.00 sec)

And These two entities are One to One relationship. 

@Entity
@Table(name="user")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private int id;
...
..
    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY, optional = false)
    private UserProfile userProfile;
..

and UserProflies entity

@Entity
@Table(name="userprofiles")
public class UserProfile {

    @OneToOne(fetch = FetchType.LAZY)
    @MapsId
    @JoinColumn(name="userid")
    private User user;

    @Id
    private int id;
...
..

As you can see, only in UserProflies table has a datecreated infomation. What I want to do is, get all User descending order by datecreated.

So, In my controller.

@GetMapping("/allUserInfo")
    public String getAllUserInfo(@PageableDefault(page = 1) Pageable pageable, Model model) { 

        int pageNum = pageable.getPageNumber(); 
        pageable = new PageRequest(pageNum, 10, new Sort(Sort.Direction.DESC, "datecreated"));

        Page<User> users = userService.findAll(pageable);
        model.addAttribute("users", users);

        return "_allUserInfo";
    }

As My DAO interface extends JpaRepository class. I tried like this.. 

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserDAO userDAO;
..
.
    @Override
    public Page<User> findAll(Pageable pageable) {
        return userDAO.findAll(pageable);
    }
..

@Repository
public interface UserDAO extends JpaRepository<User, Long> {

    @Query("SELECT u FROM userprofile upf JOIN upf.user u")
    Page<User> findAll(Pageable pageable);
}

but It failed.. I just want to take User Pageable Objects ordered by datecreated. so that I can display it to the view. Am I on the right track? or there are other way of doing this? 

Validation failed for query for method public abstract org.springframework.data.domain.Page com.myfolder.boards.dao.UserDAO.findAll(org.springframework.data.domain.Pageable)!

Above is error msg..

EDIT - full error msg 

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'webSecurityConfig': Unsatisfied dependency expressed through field 'userService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userServiceImpl': Unsatisfied dependency expressed through field 'userDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userDAO': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.rainbowtape.boards.dao.UserDAO.findAll(org.springframework.data.domain.Pageable)!
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:592)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:370)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1219)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:551)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:754)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:444)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:326)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4641)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5109)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
    at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
    at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:943)
    at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:839)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
    at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
    at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:943)
    at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:258)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
    at org.apache.catalina.core.StandardService.startInternal(StandardService.java:422)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
    at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:770)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:682)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492)
Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userServiceImpl': Unsatisfied dependency expressed through field 'userDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userDAO': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.rainbowtape.boards.dao.UserDAO.findAll(org.springframework.data.domain.Pageable)!
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:592)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:370)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1219)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:551)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:207)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1059)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:589)
    ... 45 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userDAO': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.rainbowtape.boards.dao.UserDAO.findAll(org.springframework.data.domain.Pageable)!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1583)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:207)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1059)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:589)
    ... 58 more
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.rainbowtape.boards.dao.UserDAO.findAll(org.springframework.data.domain.Pageable)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:138)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:205)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:77)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:451)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:223)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:277)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:263)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:101)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
    ... 68 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: userprofile is not mapped [SELECT u FROM userprofile upf JOIN upf.user u]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:713)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347)
    at com.sun.proxy.$Proxy74.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
    ... 81 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: userprofile is not mapped [SELECT u FROM userprofile upf JOIN upf.user u]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79)
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:219)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704)
    ... 89 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: userprofile is not mapped
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:169)
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91)
    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:79)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:331)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3695)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3584)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:720)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:576)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:271)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191)
    ... 95 more

EDIT+2

Silly Me. 

@Query("SELECT u FROM UserProfile upf JOIN upf.user u")

this worked.

Jin Lim
  • 1,759
  • 20
  • 24
  • 1
    first you have to correct the query userprofile to be replaced by UserProfile. And it will be helpful if you post the entire error trace. – hasnae Feb 19 '19 at 15:23
  • Thanks! @Query("SELECT u FROM UserProfile upf JOIN upf.user u") this Seems works. – Jin Lim Feb 19 '19 at 15:54

3 Answers3

0

take a look at: https://www.baeldung.com/spring-data-jpa-pagination-sorting

You could extend the PagingAndSortingRepository interface:

public interface ProductRepository extends PagingAndSortingRepository<Product, Integer> {

    List<Product> findAllByPrice(double price, Pageable pageable);
}

then you can do:

Pageable sortedByName = 
  PageRequest.of(0, 3, Sort.by("name"));

Pageable sortedByPriceDesc = 
  PageRequest.of(0, 3, Sort.by("price").descending());

Pageable sortedByPriceDescNameAsc = 
  PageRequest.of(0, 5, Sort.by("price").descending().and(Sort.by("name")));
Willem
  • 992
  • 6
  • 13
  • The problem I am having is, I want to sort data using another table's column. so I tried "JOIN" using JPQL way. Also note that JpaRepository already Include PagingAndSortingRepository. – Jin Lim Feb 19 '19 at 15:05
0

There is many problems in your question:

1 - You're querying about the whole entity "User u" in your query. Are you sure that you need all attributes? Maybe you should use query with constructor to one DTO. (Example in manish answer here) How to return a custom object from a Spring Data JPA GROUP BY query

2 - You're querying in User and sorting by a field that is not in User entity. Maybe you should use this query in UserProfile Repository. Or you can add to your sorting field "userProfile.datecreated" instead of "datecreated" and see if this solve your problem.

3 - Don't hide the error message. Probably if I had seen it, I could help you more.

Raniere
  • 106
  • 4
  • 1. User entity has only 4 Fields. It's doesn't sound necessary to make another DTO for this. 2. That's why I was trying to "Join". As you suggested, If I use this query in UserProfile Repository, return format will be UserProflie. I want User entity. I don't want to add extra filed into database. 3. I didn't hide error. I will add full error msg as you wish. – Jin Lim Feb 19 '19 at 15:42
  • 1
    With you fix userprofile -> to UserProfile it worked? – Raniere Feb 19 '19 at 17:14
0

This is a little old, but I will put here to allow future references.

You ca use JPQL (that allow pageable) or NATIVE QUERY (require more work):

  1. JPQL (in this case the pagination is very easy):
@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);
  1. Native queries:
@Query(
  value = "SELECT * FROM Users ORDER BY id", 
  countQuery = "SELECT count(*) FROM Users", 
  nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

I got this code from Baeldung.

Zetared
  • 110
  • 4