spring data jpa 提供了強(qiáng)大的接口，一般情況下，接口默認(rèn)提供的方法即可滿足查詢需求突委，但是一些特定場合我們還是需要使用自定義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自定義接口定義
此處定義了一個(gè)參數(shù)的自定義sql查詢纷跛，age的數(shù)值作為參數(shù)進(jìn)行查詢
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. 服務(wù)定義
4.1 服務(wù)接口
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 服務(wù)實(shí)現(xiàn)
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. 總結(jié)
總的來說絕大多數(shù)的單表?xiàng)l件查詢，都可以用JPA接口非常方便的表述出來，但是有些場景下如果需要自定義語句与倡，則可以按照本文的方式進(jìn)行自定義SQL接口查詢净响。
