Setmaxresults for Spring-Data-JPA Annotation

How to Use JPA setMaxResults() (LIMIT 20) Without Entity Manager?

How about using Pageable interface in Spring data?

package com.example.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable

import com.example.entities.Employee;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query("SELECT "
        + "e.eid, "
        + "e.firstname, "
        + "e.lastname, "
        + "e.dept) "
        + "FROM Employee e")
    public Page<Employee> getAllEmployees(Pageable pageable);

}

In your controller you can set how many items you'd like to get :

@RequestMapping(name = "/getAllEmployees")
public @ResponseBody List<Employee> getAllEmployees() {
    return empRepo.getAllEmployees(new PageRequest(0, 20)).getContent();
}

How to limit result in @Query used in Spring Data Repository

You can provide limitations by limitstatement in your SQL. And have nativeQuery = true in @Query annotation to set JPA provider(like Hibernate) to consider this as a native SQL query.

@Query(nativeQuery = true, value = "SELECT * FROM SLSNotification s WHERE s.userId = :userId ORDER BY snumber DESC LIMIT 20")
List<SLSNotification> getUserIdforManage(@Param("userId") String userId);

Or

Additionally if you want to exploit the handy features from Spring Data JPA,
you can do it by proper method naming

List<SLSNotification> findByUserIdOrderBySNumber(@Param("userId") String userId, Pageable pageable);

If you dont know already, Spring Data JPA constructs Query from the method names. Amazing, right? Read this documentation for better understanding.

Now just call this method like

Pageable topTwenty = PageRequest.of(0, 20);
List<SLSNotification> notifications = repository.findByUserIdOrderBySNumber("101", topTwenty);

Besides, If you are using Java 8

You have option for having default method in interface and make life a bit easier

 List<SLSNotification> findByUserIdOrderBySNumber(@Param("userId") String userId, Pageable pageable);

 default List<User> findTop20ByUserIdOrderBySNumber(String userId) {
    return findByUserIdOrderBySNumber(userId, PageRequest.of(0,20));
 }

How to do decimal precision in Spring Data Jpa @Query Annotation

Colons need to be escaped!

Solution 1: escape colons
Every : needs to be replaced by \\:

AVG(feedback_rating)\\:\\:numeric(10, 2)

@Query(value = "SELECT AVG(feedback_rating)\\:\\:numeric(10, 2) FROM app_customer_feedback WHERE " +
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND " +
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Solution 2: double colons
Every : needs to be replaced by double colons ::

AVG(feedback_rating)::::numeric(10, 2)

@Query(value = "SELECT AVG(feedback_rating)::::numeric(10, 2) FROM app_customer_feedback WHERE " +
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND " +
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Solution 3: use Cast instead of colons

cast(AVG(feedback_rating) as numeric(10, 2))

@Query(value = "SELECT cast(AVG(feedback_rating) as numeric(10, 2)) FROM app_customer_feedback WHERE " +
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND " +
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Unable to Map Data Type in Spring JPA using Projections

Your query: @Query(nativeQuery = true, value = "select user_id as userId from user_test") is returning a list of userId from user_test table, not a list of UserProjection. So, you can either declare a constructor and project your values there or just take return a list of userId in your query method.
Simply try this:

@Query(nativeQuery = true, value = "select user_id as userId from user_test")
public List<UUID> getProjectionData();

Or

@Query(nativeQuery = false, value = "select new full_dto_path.UserProjection(user_id) from UserTest")
 public List<UserProjection> getProjectionData();

Before applying second approach, make sure you have defined your constructor for that single field.

Note: full_dto_path means the fully qualified path of your UserProjection dto.

Hope this will work.

Spring Data JDBC: No such property found for type

Just remove the method getIngredients() and use findAll() (provided by the super interface) instead.

You should also remove the other methods, because they are already provided by the super interface

• save() <- provided by CrudRepository
• getById() <- CrudRepository provide Optional<T> findById(ID id);
• getIngredients <- CrudRepository provide Iterable<T> findAll();

---

Related Topics