Spring Boot Jpa provides methods such as CURD by default, which often fail to meet our business requirements in daily life. In this chapter, a simple query case is defined to explain.

Quick start

pom.xml and application.properties in the project are the same as Chapter 1

Entity class mapping database table

user entity class

@Entity
public class User implements Serializable {

    private static final long serialVersionUID = -390763540622907853L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private Integer age;
    
    private String email;

    // Omit constructor set/get        

}

Custom simple query

There is a query generator mechanism based on the method name in spring data internal infrastructure, which is very useful for building constraint queries on the entities of the repository. The prefix of the mechanism method is find By, read... By,query… By,count… By And get... From these methods, you can analyze the rest of it (the fields in the entity). The import clause can contain other expressions, such as setting different flags on the query that Distinct is going to create. However, the first by serves as a separator to indicate the beginning of the actual standard. At a very basic level, you can define physical conditions And concatenate them (And Or).

Inheriting pagingandsortingreposition

public interface UserPagingRepository extends PagingAndSortingRepository<User, Long> {
    // Search by name
    List<User> findByName(String name);

    // Search by name
    List<User> queryByName(String name);

    // Search by name or email
    List<User> findByNameOrEmail(String name,String email);

    // Calculate the number of an age
    int countByAge(int age);
}

Test class

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserPagingRepositoryTest {

    /**
     * Object of ambition
     */
    private Logger logger = LoggerFactory.getLogger(UserPagingRepositoryTest.class);

    @Autowired
    private UserPagingRepository userPagingRepository;


    @Before
    public void save() {
        logger.info("New data result = {}", userPagingRepository.save(new User("millet", 9,"a@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("Zhang San", 16,"b@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("Three elder brother", 12,"c@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("M two", 13,"e@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("A third", 12,"f@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("Zhang San", 12,"g@qq.com")));
        logger.info("New data result = {}", userPagingRepository.save(new User("M two", 8,"h@qq.com")));
    }


    @Test
    public void find(){
        logger.info("Search by name(findByName) result = {}", userPagingRepository.findByName("Zhang San"));
        logger.info("Search by name(queryByName) result = {}", userPagingRepository.queryByName("Zhang San"));
        logger.info("By name or email(findByNameOrEmail)  lookup result = {}", userPagingRepository.findByNameOrEmail("Zhang San","f@qq.com"));
        logger.info("Through one age Quantity(countByAge) result = {}", userPagingRepository.countByAge(12));
    }


}

@Before will run before @ test.

Output log:

Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.name=?
//Search by name (findByName) result = [User{id=2, name = Zhang San ', age = 16, email = B @ QQ. Com'}, user {id = 6, name = Zhang San ', age = 12, email = g @ QQ. Com'}]
    
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.name=?
//Query byname result = [user {id = 2, name ='zhang San ', age=16, email='b@qq.com'}, User{id=6, name ='zhang San ', age=12, email='g@qq.com'}]
    
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.name=? or user0_.email=?
//Find result = [User{id=2, name ='zhang San ', age=16, email='b@qq.com'}, User{id=5, name ='a San ', age=12, email='f@qq.com'}, User{id=6, name ='zhang San ', age=12, email='g@qq.com'}]
    
Hibernate: select count(user0_.id) as col_0_0_ from user user0_ where user0_.age=?
//Count by age result = 3

The logs are redundant, and redundant logs are deleted. From the logs, we can find that JPA automatically resolves to SQL according to our defined interface method

The keywords supported in the method are as follows

Keyword	Example	JPQL expression
And	findByLastnameAndFirstname	... where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	... where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname, findByFirstnameIs, findByFirstnameEquals	... where x.firstname = ?1
Between	findByStartDateBetween	... where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	... where x.age <= ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	... where x.age >= ?1
After	findByStartDateAfter	... where x.startDate > ?1
Before	findByStartDateBefore	... where x.startDate < ?1
IsNull, Null	findByAge(Is)Null	... where x.age is null
IsNotNull, NotNull	findByAge(Is)NotNull	... where x.age not null
Like	findByFirstnameLike	... where x.firstname like ?1
NotLike	findByFirstnameNotLike	... where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	... where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	... where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	... where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	... where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	... where x.lastname <> ?1
In	findByAgeIn(Collection ages)	... where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	... where x.age not in ?1
True	findByActiveTrue()	... where x.active = true
False	findByActiveFalse()	... where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	... where UPPER(x.firstame) = UPPER(?1)

Paging and sorting

Data paging and sorting are also necessary in daily life. To use paging and sorting in Spring Boot Jpa, you need to pass Pageable instances into Repository interface methods.

public interface UserPagingRepository extends PagingAndSortingRepository<User, Long> {
   // Query by name criteria
    List<User> findByName(String name, Pageable pageable);

}

test method

    @Test
    public void pageAndSort(){
        Sort sort = new Sort(Sort.Direction.DESC, "age");
        int page = 0;
        int size = 10;
        Pageable pageable = PageRequest.of(page, size, sort);
        logger.info("Conditional query result = {}", userPagingRepository.findByName("Zhang San",pageable));
        logger.info("---------------------------------");
        logger.info("Sort by age result = {}", userPagingRepository.findAll(sort));
    }

test result

2020-02-29 17:02:37.431  INFO 48944 --- [           main] c.m.c.r.UserPagingRepositoryTest         : Conditional query result = [User{id=2, name='Zhang San', age=16, email='b@qq.com'}, User{id=6, name='Zhang San', age=12, email='g@qq.com'}]
2020-02-29 17:02:37.431  INFO 48944 --- [           main] c.m.c.r.UserPagingRepositoryTest         : ---------------------------------
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ order by user0_.age desc
2020-02-29 17:02:37.459  INFO 48944 --- [           main] c.m.c.r.UserPagingRepositoryTest         : Sort by age result = [User{id=2, name='Zhang San', age=16, email='b@qq.com'}, User{id=4, name='M two', age=13, email='e@qq.com'}, User{id=3, name='Three elder brother', age=12, email='c@qq.com'}, User{id=5, name='A third', age=12, email='f@qq.com'}, User{id=6, name='Zhang San', age=12, email='g@qq.com'}, User{id=1, name='millet', age=9, email='a@qq.com'}, User{id=7, name='M two', age=8, email='h@qq.com'}]

Complex condition query

We demonstrated CrudRepository and pagingandsortingreposition earlier. Next, we operate more complex statements by inheriting JpaRepository and jpaspecification executor.

public interface JpaSpecificationExecutor<T> {
    // Querying a single object according to the Specification criteria
    Optional<T> findOne(@Nullable Specification<T> var1);
    // Query according to the Specification condition and return the List result
    List<T> findAll(@Nullable Specification<T> var1);
    // Query according to Specification condition and paging condition
    Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
    // Query and return List results according to Specification and sorting conditions
    List<T> findAll(@Nullable Specification<T> var1, Sort var2);
    // Query quantity according to Specification condition
    long count(@Nullable Specification<T> var1);
}

UserJpaRepository data layer interface

public interface UserJpaRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor {
}

Test class

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserJpaRepositoryTest {


    @Test
    public void specification() {
        Specification specification = new Specification<User>() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                // like fuzzy query, root.get("name") attribute name "% 3%" is three
                Predicate p1 = cb.like(root.get("name"), "%Three%");
                // greaterThan means age is greater than 10
                Predicate p2 = cb.greaterThan(root.get("age"), 10);
                // cb.and(p1, p2), and means p1 and p2 and the relationship, in addition to and, or, not and so on. Click CriteriaBuilder to view
                return cb.and(p1, p2);
            }
        };

    }


    @Test
    public void ConditionalQuery() {
        int page = 0;
        int size = 10;
        Pageable pageable = PageRequest.of(page, size);
        // Simulate incoming conditions
        User user = new User("Three", 10, "b@qq.com");
        Specification specification = new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                // Determine whether the value passed in is empty
                if (!"".equals(user.getName())) {
                    predicates.add(cb.like(root.get("name"), "%" + user.getName() + "%"));
                }
                // Judge whether the age is empty
                if (user.getAge() != null) {
                    predicates.add(cb.greaterThan(root.get("age"), user.getAge()));
                }

                return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        Page result = userJpaRepository.findAll(specification, pageable);
        logger.info("Conditional query result = {}", result.getContent());
    }

}

The specification() method is easier to understand. If you understand this method, you can better understand the ConditionalQuery() method. ConditionalQuery() method is also a high frequency method used in actual development.

The jpaspecification executor can implement almost any logic through CriteriaQuery.

Code of this chapter

• Github: https://github.com/mtcarpenter/spring-data-chapter