CODEWITHSUNDEEP
springhibernatespring-bootjpaspring-data-jpa
sam
sam

Reputation: 2004

Query and Database performance when used with JpaRepository findAll() vs native query using JpaRepository

I am developing a spring boot project where i am having two functions for JPA on which i need to figure out which function will perform better and put less pressure on database query performance and utilise Hibernate caching. Please guide on which query to use.

My Repository interface:

@Repository
public interface CustomersRepository
        extends JpaRepository<CustomersEntity, Long> {

    @Query(nativeQuery = true, value = "SELECT * FROM customers WHERE c_mobile = ?1")
    CustomersEntity findcustomerByMobile(String mobileNo);

    @Override
    List<CustomersEntity> findAll();
}

My Service class:

@Scope("request")
@Service
public class CustomerServiceImpl implements ICustomerService {

    @Autowired
    private CustomersRepository customersRepository;

    @Override
    public boolean findCustomerByMobile1(long mobileNo) {

        CustomersEntity customersEntity = customersRepository.findcustomerByMobile(mobileNo);

        if (customersEntity != null)
            return true;
        else
            return false;
    }

    @Override
    public boolean findCustomerByMobile2(long mobileNo) {

        List<CustomersEntity> entityList = customersRepository.findAll();

        for (CustomersEntity entity : entityList) {
            if (entity.getcMobile() == mobileNo) {
                return true;
            }
        }

        return false;
    }
}

Upvotes: 1

Views: 1831

Answers (1)

Cepr0
Cepr0

Reputation: 30474

There is no need to download all records from the database to your app and then filtering them. With thousands of records it will slow down.

Instead you should create an index on c_mobile field then use just like this simple method:

public interface CustomerRepo extends JpaRepository<CustomersEntity, Long> {
    CustomersEntity findByMobileNo(String mobileNo);
}

It will work in a flash (with index).

More info about building query methods you can find here.

Upvotes: 2

Related Questions