Home > database >  How to get all values from a table in spring boot with where condition?
How to get all values from a table in spring boot with where condition?

Time:09-29

I am learning Spring Boot and basically want to make a query like this that returns all rows whose age is 5.

SELECT * FROM pets WHERE age = 5;

With Spring Boot, getting all pets or a pet with a unique id is easy using the JPA and Hibernate and I can simply do this.

PetsController.java

//for all pets
@GetMapping
    public List<Pet> getAllPets() {
        return petService.getAllPets();
    }

//for getting one pet with unique id
@GetMapping("{id}")
    public ResponseEntity<Pet> getPetById(@PathVariable("id") int petId) {
        ResponseEntity<Pet> matchingPet = new ResponseEntity<Pet>(petService.getPetById(petId), HttpStatus.OK);
        return matchingPet;
    }

PetService.java

public interface PetService {
    List<Pet> getAllPets();
    Pet getPetById(int petId);
}

PetServiceImpl.java

@Service
public class PetServiceImpl implements PetService {

    private PetRepository petrepository;

    public PetServiceImpl(PetRepository petrepository) {
        this.petrepository = petrepository;
    }


    @Override
    public List<Pet> getAllPets() {
        return petrepository.findAll();
    }

    @Override
    public Pet getPetById(int petId) {
        Optional<Pet> pet = petrepository.findById(petId);
        if (pet.isPresent()) {
            return pet.get();
        }
        else {
            return null;
        }
    }
}

PetRepository.java

public interface PetRepository extends JpaRepository<Pet, Integer> {
}

Since getting list of pets and getting pets by id was easy enough by using the JpaRepository, I was wondering if there are any methods available that does something similar that returns all pets whose age is 5. Or would I need to make a custom query for this? And if so, how?

CodePudding user response:

public interface PetRepository extends JpaRepository<Pet, Integer> {
    List<Pet> findAllByAge(Integer age);
}

CodePudding user response:

In your repository you can create a method like:

public List<Pet> findByAge(Integer age);

CodePudding user response:

If you're just going to do a search by age, you can use Query creation from method names method.

public interface PetRepository extends JpaRepository<Pet, Integer> {
    List<Pet> findAllByAge(Integer age);
}

OR

Alternatively, you can also use the @Query annotation to apply your query.

public interface PetRepository extends JpaRepository<Pet, Integer> {
    @Query("select p from Pet p where p.age = ?1")
    List<Pet> findAllByAge(Integer age);
}

Note: If you have too many criteria or if you want to search according to certain conditions, you should use Specification.

  • Related