Spring Data Series (Part 4): @Query Annotation

1. Suppose you want to build a query that takes a variety of filtered criteria. Say I want to fetch "Person" based on name, country, gender, and age. We can achieve this via a Spring Data method convention, like findPersonbyNameAndCountryAndGenderAndAge but the readability is pretty bad. So, let's avoid this and use the @Query annotation and create your own query with a suitable method name.
   
   @Query("select p from Person p where p.name like ?1 and p.country like
?2 and gender like ?3 and age=?4  order by country")

findPerson(String name,String country,String gender,Integer age);

2. Suppose you want to sort your query by a specific property — for example, the name.

3. Suppose you want to override the CRUDRepository findOne method. Just redefine this method, maintaining the signature, and put an @Query annotation with your customized query, Now Spring data takes your version. 
   
   @Query("select p from Person p where p.id = ?1 and  country='America'")

4. You need to define the functionality not provided by the Repository interface.

package com.example.repo;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.example.person.Person;

@Repository
public interface PersonRepositary extends CrudRepository<Person, Long>,QueryDslPredicateExecutor<Person> {

    @Query("select p from Person p where p.country like ?1 order by country")
    List<Person> findByCountryContains(String country);

    List<Person> findPersonByHobbyName(String name);

    @Query("select p from Person p where p.id = ?1 and  country='America'")
    Person findOne(Long id);           
}

package com.example.person;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;
    private String country;
    private String gender;

    @OneToMany(mappedBy="person",targetEntity=Hobby.class,
        fetch=FetchType.EAGER,cascade=CascadeType.ALL)
    List<Hobby> hobby;

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getCountry() {
        return country;
    }
    public void setCountry(String country) {
        this.country = country;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }           

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }           

    public List<Hobby> getHobby() {
        return hobby;
    }
    public void setHobby(List<Hobby> hobby) {
        this.hobby = hobby;
    }

    public void addHobby(Hobby ihobby)
    {
        if(hobby == null)
        {
            hobby = new ArrayList<Hobby>();
        }
        hobby.add(ihobby);
    }
    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", country=" + country + ", gender=" + gender + "]";
    }                    
}

package com.example.person;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import com.example.repo.HobbyRepository;
import com.example.repo.PersonRepositary;
import com.querydsl.core.types.dsl.BooleanExpression;


@SpringBootApplication
@EnableJpaRepositories("com.example.repo")
public class PersonApplication {

    @Autowired
    HobbyRepository hRepo;
    private static final Logger log = LoggerFactory.getLogger(PersonApplication.class);

    @Bean
    public CommandLineRunner demo(PersonRepositary repository) {

        //insertPerson(repository);
        //findPersonByHobbyname(repository,"Photography");
        //findPersonByNameQueryDsl(repository,"Photography");
        //findPersonByCountry(repository,"India");
        Person p = repository.findOne(13L);
        log.info("Person " + p);
        return null;
    }

    private void findPersonByNameQueryDsl(PersonRepositary repository,String hobby)
    {
        BooleanExpression nameExpr = QPerson.person.name.contains("Shamik");

        Iterable<Person> pList = repository.findAll(nameExpr);
        for(Person p : pList)
        log.info("Person " + p);
    }

    private void findPersonByHobbyname(PersonRepositary repository,String hobby)
    {
        List<Person> pList = repository.findPersonByHobbyName(hobby);
        for(Person p : pList)
        log.info("Person " + p);
    }

    private void findPersonByCountry(PersonRepositary repository,String country)
    {
        List<Person> pList = repository.findByCountryContains(country);
        for(Person p : pList)
        log.info("Person " + p);
    }

    private void insertPerson(PersonRepositary repository)
    {                       
        Person p = new Person();
        p.setName("Samir mitra");
        p.setCountry("America");
        p.setGender("male");

        Person p1 = new Person();
        p1.setName("Shamik mitra");
        p1.setCountry("India");
        p1.setGender("male");

        repository.save(p);
        repository.save(p1);

        Hobby hobby1 =new Hobby();
        hobby1.setName("Photography");
        hobby1.setPerson(p);                     

        Hobby hobby2 =new Hobby();
        hobby2.setName("Dancing");
        hobby2.setPerson(p1);

        Hobby hobby3 =new Hobby();
        hobby3.setName("Photography");
        hobby3.setPerson(p1);

        hRepo.save(hobby1);
        hRepo.save(hobby2);
        hRepo.save(hobby3);

    }         
    public static void main(String[] args) {                      
        SpringApplication.run(PersonApplication.class, args);       
    }
}

Output

person0_.country as country2_1_,
person0_.gender as gender3_1_,
person0_.name as name4_1_
and person0_.country='America'
2016-10-07 12:29:39.570  INFO 5744 --- [main] com.example.person.PersonApplication : Person Person [id=13, name=Samir mitra, country=America, gender=male]