JPA exists子查询 SQLServer

项目结构：

 

 

引导类代码：

package com.aniu.niu;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class NiuApplication {

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

}

 

Entity相关代码：

package com.aniu.niu.entity;

import javax.annotation.Generated;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Student {
    @Id
    private String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getCourseId() {
        return courseId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId;
    }

    @Column(name = "name")
    private String studentName;
    private String sex;
    private int age;
    //演示 数据库字段和类属性不一致的情况， courseId关联 Course表
    @Column(name = "course")
    private String courseId;
}

package com.aniu.niu.entity;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Course {
    @Id
    public String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String code;
    private String name;
}

repository代码：

package com.aniu.niu.repository;

import com.aniu.niu.entity.Student;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface StudentRepository extends JpaRepository<Student, String> {
    List<Student> findAll(Specification<Student> specification);
}

Controller代码：

package com.aniu.niu.controller;

import com.aniu.niu.entity.Course;
import com.aniu.niu.entity.Student;
import com.aniu.niu.repository.StudentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Subquery;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@RestController
@RequestMapping(value = "stu")
public class StudentController {
    @Autowired
    private StudentRepository repository;

    /**
     * 注意参数添加 @RequestBody
     * @param student
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "/create")
    public String createStudent(@RequestBody Student student){
        if(StringUtils.isEmpty(student.getId())){
            student.setId(UUID.randomUUID().toString());
        }
        repository.save(student);
        return "创建成功";
    }

    @RequestMapping(method = RequestMethod.POST, value = "/upd")
    public String modifyStudent(@RequestBody Student student){
        repository.save(student);
        return "修改成功";
    }


    @RequestMapping(method = RequestMethod.GET, value = "/findbyid")
    public Student findStudent(String id){
        Student student = repository.findById(id).get();
        return student;
    }

    @RequestMapping(method = RequestMethod.GET, value = "/findall")
    public List<Student> findAllStudent(){
        List<Student> students = repository.findAll();
        return students;
    }

    @RequestMapping(method = RequestMethod.GET, value = "/gthan")
    public List<Student> findStudentByExists(int age){
        Specification<Student> studentSpecification =  (root, criteriaQuery, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            Subquery<String> subQuery = criteriaQuery.subquery(String.class);
            Root subRoot = subQuery.from(Course.class);
            //注意courseId条件和数据库字段不一致，要以实体上的名称为准
            subQuery
                    .select(cb.literal("1"))
                    .where(
                            cb.equal(subRoot.get("id"), root.get("courseId"))
                    );

            predicates.add(cb.exists(subQuery));
            predicates.add(cb.greaterThan(root.get("age").as(Integer.class), age));
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        List<Student> students = repository.findAll(studentSpecification);
        return students;
    }
}

application.properties配置文件内容：

# 应用名称
spring.application.name=niu
# 应用服务 WEB 访问端口
server.port=8080

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=jpatest
spring.datasource.username=sa
spring.datasource.password=123123a?
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto=none

 

最后Postman调用：

http://localhost:8080/stu/gthan?age=20

sql效果:

Hibernate: select student0_.id as id1_1_, student0_.age as age2_1_, student0_.course as course3_1_, student0_.sex as sex4_1_, student0_.name as name5_1_ from student student0_ where (exists (select '1' from course course1_ where course1_.id=student0_.course)) and cast(student0_.age as int)>20