Springboot from getting started to getting started (eight orm-jpa-5 complex queries)

Query based on method name , Simple as it is , But it can't meet complex queries , have access to JpaSpecificationExecutor For complex queries

One . Create in the database user、user_comment surface

user_comment.user_id = user.id

CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`user`(`id`, `name`, `email`) VALUES (1, 'admin', ' Zhang San @ Li Si .com');

 

CREATE TABLE `user_comment` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`comment` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `test`.`user_comment`(`id`, `user_id`, `comment`) VALUES (1, 1, 'comment1');

Two . Generate entity

Generate entity See for steps  SpringBoot From entry to entry （ 5、 ... and ORM-JPA-1） 

3、 ... and . modify IUserRepository, Inherit JpaSpecificationExecutor

package com.example.demo.repository;
import com.example.demo.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
@Repository
public interface IUserRepository extends JpaRepository<UserEntity,Integer>, JpaSpecificationExecutor<UserEntity> {
}

JpaSpecificationExecutor Receive in interface method Specification As a parameter  

3、 ... and . To write Specification

For demonstration purposes , stay Demo1Application Create in file Specification

Add... In the class implements Specification Subclasses of

 private class MySpec implements Specification<UserEntity>{
@Override
public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
return null;
}
}

stay toPredicate In the method , Write query content

private class MySpec implements Specification<UserEntity>{
@Override
public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Path<Integer> id = root.get("id");
Path<String> name = root.get("name");
Path<String> email = root.get("email");
Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email," Zhang San @ Li Si .com"));
return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1));
}
}

The above example SQL by

select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1

Four . Create a route and access

 @GetMapping("/user/all")
public List<UserEntity> userAll() {
List<UserEntity> list = userRepository.findAll(new MySpec());
return list;
}

5、 ... and . Repeat step three , It can realize complex query

1.JOIN

• modify UserEntity, Add fields , Configure relationships  

  private Set<UserCommentEntity> userCommentEntity;
  @OneToMany
  @JoinColumn(name = "user_id",referencedColumnName = "id")
  public Set<UserCommentEntity> getUserCommentEntity(){
  return userCommentEntity;
  }
  public void setUserCommentEntity(Set<UserCommentEntity> userCommentEntity){
  this.userCommentEntity = userCommentEntity;
  }

  To write toPredicate

   private class MySpec implements Specification<UserEntity>{
  @Override
  public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
  Path<Integer> id = root.get("id");
  Path<String> name = root.get("name");
  Path<String> email = root.get("email");
  Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email," Zhang San @ Li Si .com"));
  Join<UserEntity, UserCommentEntity> join = root.join("userCommentEntity",JoinType.LEFT);
  return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1));
  }
  }

  Equate to

  select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ left outer join user_comment usercommen1_ on userentity0_.id=usercommen1_.user_id where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1