jap sql 保存_Spring Data JPA 之 原生SQL使用

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 {

@Query(value = "select * from student where age >= ?1", nativeQuery=true)

List 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 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接口查询。