JPQL limit query [duplicate]

2020-05-25 05:14发布

站内文章 / Java

35 0

问题:

This question already has answers here:

Limit number of results in JPQL (2 answers)

How can I limit in a select query of JPQL named query? I need the limit to be done in the query level itself and not in the java layer!!! I am trying to use

@NamedQueries(value = {
        @NamedQuery(name = UserNotification.QueryName.NOTIFICATION_DISPLAYED,
                    query = "SELECT un FROM UserNotification un " +
                            "WHERE un.orgId IN (:orgList) " +
                            "AND un.user.id = :userId LIMIT 5")

but in vain!!!

Please suggest

回答1:

JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query. If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit. Then map an entity to this view as you would a table.

Example:

List<String> resultList= query.setMaxResults(100).getResultList();

回答2:

In case you are working with spring-data you should use the Pageable Interface. A sample code below,

My Service,

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;

@Service
public class MyModelService {

    @Autowired
    private MyModelRepository myModelRepository;

    @Transactional
    public Page<MyModel> findMyModelTop5() {
        return myModelRepository.findMyModelTop5(new PageRequest(0, 5));
    }
}

My Repository,

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

@Repository
public interface MyModelRepository extends JpaRepository<MyModel, Integer> {

    @Query("SELECT mm FROM MyModel mm")
    public Page<MyModel> findMyModelTop5(Pageable pageable);

}

You can find a more complete answer about the spring data available options here.

回答3:

For specific @NamedQueries where a limit is required, you can switch to @NamedNativeQuery

@NamedNativeQuery(
  name=UserNotification.QueryName.NOTIFICATION_DISPLAYED_LIMIT5,
  query="SELECT un.* FROM user_notification un " + 
        "WHERE un.user.id = ?1 LIMIT 5",
  resultClass=UserNotification.class
)

Not quite as smooth, but does the job.