How to customize your own repository in SpringBoot

1. The first way

Follow this article to get detail
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
https://egkatzioura.com/2016/06/03/add-custom-functionality-to-a-spring-data-repository/


step 1: create your own Repo interface like this

public interface EmployeeRepositoryCustom {

    List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount);

}

step 2: create Impl interface based on the above interface EmployeeRepositoryCustom

public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public List<Employee> getFirstNamesLikeAndBonusBigger(String firstName, Double bonusAmount) {
        Query query = entityManager.createNativeQuery("select e.* from spring_data_jpa_example.bonus b, spring_data_jpa_example.employee e\n" +
                "where e.id = b.employee_id " +
                "and e.firstname LIKE ? " +
                "and b.amount> ? ", Employee.class);
        query.setParameter(1, firstName + "%");
        query.setParameter(2, bonusAmount);

        return query.getResultList();
    }
}

step 3: make EmployeeRepository extends  JpaRepository, EmployeeRepositoryCustom

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

step 4: Use this EmployeeRepository  in @Service layer

@Autowired
private EmployeeRepository employeeRepository;


@Transactional(readOnly = true)
public List<Employee> getFiltered(String firstName,@RequestParam(defaultValue = "0") Double bonusAmount) {

        return employeeRepository.getFirstNamesLikeAndBonusBigger(firstName,bonusAmount);
}

2. The second way
reference this tutorial
https://www.baeldung.com/spring-data-jpa-query

Using @Query
I suppose that we have City Table and Model class mapping that table

@Repository
public interface CityRepository extends CrudRepository<City, Long> {

 @Query("select c from City c where c.name like %?1")
 List<City> findByNameEndsWith(String chars);


@Query("SELECT u FROM User u WHERE u.status = 1")
Collection findAllActiveUsers();

// with native sql
@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection findAllActiveUsersNative();


// with sort repository.findAllUsers(new Sort("name"));
@Query(value = "SELECT u FROM User u")
List findAllUsers(Sort sort);


@Query(value = "SELECT u FROM User u ORDER BY id")
Page findAllUsersWithPagination(Pageable pageable);


@Query(
  value = "SELECT * FROM Users ORDER BY id", 
  countQuery = "SELECT count(*) FROM Users", 
  nativeQuery = true)
Page findAllUsersWithPagination(Pageable pageable);


@Query("SELECT u FROM User u WHERE u.status = ?1")
User findUserByStatus(Integer status);
 
@Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);

// with @params
@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByUserStatusAndUserName(@Param("status") Integer userStatus, 
  @Param("name") String userName);


@Query(value = "SELECT * FROM Users u WHERE u.status = :status and u.name = :name", 
  nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(
  @Param("status") Integer status, @Param("name") String name);

//Update Queries with @Modifying

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?", 
  nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

//Inserts
@Modifying
@Query(value = "insert into Users (name, age, email, status) values (:name, :age, :email, :status)",
  nativeQuery = true)
void insertUser(@Param("name") String name, @Param("age") Integer age, 
  @Param("status") Integer status, @Param("email") String email);