spring data jpa 提供了强大的接口，一般情况下，接口默认提供的方法即可满足查询需求，但是一些特定场合我们还是需要使用自定义SQL查询。

1.表定义

@Table(name = "student")
@Entity
@Data
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @NotNull
    @Column(length = 128)
    private String name;

    @NotNull
    @Column(length = 128)
    private String idNo;

    @NotNull
    private Integer age;

    @NotNull
    @Column(length = 8)
    private String gender;

}

2. jpa自定义接口定义

此处定义了一个参数的自定义sql查询，age的数值作为参数进行查询

import com.example.jpademo.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface StudentRepository extends JpaRepository<Student, Integer> {

    @Query(value = "select * from student where age >= ?1", nativeQuery=true)
    List<Student> findCondQuery(Integer age);

}

3. pojo对象定义

3.1 studentDTO定义

package com.example.jpademo.pojo;

import lombok.Data;

@Data
public class StudentQueryDTO {
    
    Integer age;

}

3.2 studentVO定义

package com.example.jpademo.pojo;

import lombok.Data;

import javax.persistence.Column;
import javax.validation.constraints.NotNull;

@Data
public class StudentVO {

    @NotNull
    @Column(length = 128)
    private String name;

    @NotNull
    @Column(length = 128)
    private String idNo;

    @NotNull
    private Integer age;

    @NotNull
    @Column(length = 8)
    private String gender;

}

4. 服务定义

4.1 服务接口

package com.example.jpademo.service;

import com.example.jpademo.pojo.StudentQueryDTO;
import com.example.jpademo.pojo.StudentVO;
import com.example.jpademo.web.advice.RestResponse;

public interface StudentService {

    RestResponse nativeQuery(StudentQueryDTO studentQueryDTO);

    RestResponse add(StudentVO studentVO);

}

4.2 服务实现

package com.example.jpademo.service.impl;

import com.example.jpademo.entity.Student;
import com.example.jpademo.pojo.StudentQueryDTO;
import com.example.jpademo.pojo.StudentVO;
import com.example.jpademo.repository.StudentRepository;
import com.example.jpademo.service.StudentService;
import com.example.jpademo.web.advice.RestResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
@Transactional(rollbackFor = {Exception.class})
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentRepository studentRepository;

    @Override
    public RestResponse nativeQuery(StudentQueryDTO studentQueryDTO){

        List<Student> studentList;
        Integer age = studentQueryDTO.getAge();
        try {
            studentList = this.studentRepository.findCondQuery(age);
        }
        catch (Exception e){
            throw e;
        }
        return new RestResponse(HttpStatus.OK.value(), "succ", studentList);
    }

    @Override
    public RestResponse add(StudentVO studentVO)
    {
        Student student = new Student();
        student.setAge(studentVO.getAge());
        student.setGender(studentVO.getGender());
        student.setIdNo(studentVO.getIdNo());
        student.setName(studentVO.getName());
        this.studentRepository.save(student);
        return new RestResponse(HttpStatus.OK.value(), "succ", student);
    }
}

5. 单元测试

测试下自定义查询SQL的接口

package com.example.jpademo;

import com.example.jpademo.pojo.StudentQueryDTO;
import com.example.jpademo.pojo.StudentVO;
import com.example.jpademo.repository.StudentRepository;
import com.example.jpademo.service.StudentService;
import com.example.jpademo.web.advice.RestResponse;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;


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

    @Autowired
    private StudentRepository studentRepository;

    @Autowired
    private StudentService studentService;

//    @Test
//    public void contextLoads() {
//    }

    @Test
    public void testStudentServiceNativeQuery1(){

        StudentVO studentVO = new StudentVO();
        studentVO.setAge(17);
        studentVO.setGender("male");
        studentVO.setIdNo("001");
        studentVO.setName("jack");
        this.studentService.add(studentVO);

        StudentQueryDTO studentQueryDTO = new StudentQueryDTO();
        studentQueryDTO.setAge(17);
        RestResponse restResponse = this.studentService.nativeQuery(studentQueryDTO);
        Assert.assertNotEquals(null, restResponse.getData());
    }

    @Test
    public void testStudentServiceNativeQuery2(){

        StudentVO studentVO = new StudentVO();
        studentVO.setAge(15);
        studentVO.setGender("male");
        studentVO.setIdNo("001");
        studentVO.setName("jack");
        this.studentService.add(studentVO);

        StudentQueryDTO studentQueryDTO = new StudentQueryDTO();
        studentQueryDTO.setAge(16);
        RestResponse restResponse = this.studentService.nativeQuery(studentQueryDTO);
        Assert.assertNotEquals(null, restResponse.getData());
    }

}

6. 总结

总的来说绝大多数的单表条件查询，都可以用JPA接口非常方便的表述出来，但是有些场景下如果需要自定义语句，则可以按照本文的方式进行自定义SQL接口查询。