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

wwz_ henu 2022-02-13 05:19:46 阅读数:511

springboot getting started getting started

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

     

copyright:author[wwz_ henu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130519436306.html