wwz_ henu 2022-02-13 05:19:46 阅读数:511
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
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
copyright:author[wwz_ henu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130519436306.html