springboot中JpaRepository下@Query使用原生sql进行查询的几种用法

最近在项目开发的时候，采用springboot自带的JPA进行原生sql的查询，趟雷中归纳几种可以用来进行单表部分查询、多表关联部分查询、分页查询的几种用法。

设定查询表

//第一张表
public class Student {
    private String studentid;
    private String name;
    private String age;
    private String sex;
    private String createtime;
}
//第二张表
public class Teacher{
    private String techerid;
    private String name;
    private String age;
    private String sex;
    private String createtime;
}
//第三张表
public class Course {
    private String courseid;
    private String name;
}
//第四张表  关联表
public class CourseOfMan {
    private String courseid;
    private String teacherid;
    private String studentid;
}

1、解决springboot无法进行单表部分字段查询、多表部分字段查询。

假设我们现在只要查询学生的姓名name和年龄age，不查询其他的。

//我们按照springboot jpa文档的范例和平常的习惯经常会这么写
@Query(value ="select name,age from student where studentid=:studentid ",nativeQuery = true)
public Student findById(@param("studentid")String studentid);

但是这必然出错，因为student这张表里面除了name,age还有很多其他的属性，你的返回值是一个Student对象，而你在这次查询当中只取name和age，其他的属性没有都取出来，所以jpa会报错。如果按照我们正常的思维，我们肯定会说我们这么想也没错啊，有值的你帮我插进去，没值的你就给我放空不就行了嘛。jpa不是这样子，他一定要把你的返回值里面所有的元素都给填满，所以有的人就说创建一个DTO来存放你要查询的值，假设为StudentDTO（下面贴码），这样貌似是可以实现，看别人的文章都是成功的，但是在我这没成功过，看了一下别人说的要点就是StudentDTO的有参构造函数，里面的参数排列顺序必须和sql语句的顺序是一样的，错一个都不行,这点我做到了，但是还是不行。

public class StudentDTO{
    private String name;
    private String age;
    public StudentDTO(String name,String age){//这表的入参必须严格按照sql语句select取值的顺序
        this.name = name;
        this.age=age;
    }
}

这边给出我测试通过的方式。

//只需要把类型改成map或者list的类型就可以成功的把表里面的部分值给取出来
//查询单个用map，多个用list
@Query(value ="select name,age from student where studentid=:studentid ",nativeQuery = true)
public Map<String,Object> findById(@param("studentid")String studentid);

关联表查询取出部分字段也是同样的操作。

//查询某个学生的所有课程
@Query(value ="select s.name as studentname,c.name as coursername from student s,course c,courseofman cm where s.studentid=:studentid and s.studentid = cm.studentid and cm.courseid = c.courseid ",nativeQuery = true)
public List<Map<String,Object>> findStudentCourseById(@param("studentid")String studentid);

2、分页查询

springboot自带的分页查询是使用Page<T>，但是在查询的时候如果你查询的数量少于表中数据的数量时，就会报错。报错的查询格式如下

/*分页按照姓名模糊查询学生信息的部分字段
因为是部分查询，所以我们必定使用map或者list去接受结果，然后用Page去分页
在sql语句当中只能存在一种类型的占位符，使用的 ? 就不能再使用 : ，因为分页必须?#{#pageable}这么写，所以第一个查询只能使用 ? 这种方式的占位符 ?1 后面的数字代表第几个，从1开始算起
mysql分页是?#{#pageable}，oracle分页好像是/#pageable/(不对的话自己查一下) */
@Query(value ="select s.name,s.age,s.sex from student s where if(?1 !='',s.name like ?1,1=1) order by ?#{#pageable} ",
countQuery="select count(1) as total from student s where if(:name !='',s.name like :name,1=1)",
nativeQuery = true)
public Page<List<Map<String,Object>>> findStudents(@Param("name")String name,Pageable pageable);

/*在services的调用如下（不全部贴出来，只写关键步骤）*/
//防止分页出错处理
pageno<=0?1:pageno;
pagesize<=0?10:pagesize;
//services调用
Page<List<Map<String,Object>>> student = studentServices.finStudents(name,PageRequest.of(pageno-1,pagesize));
/*注意分页必须使用PageRequest.of这个方法，用其他的不行，而且必须写成pageno-1，因为他是从0开始算起，但是你不能提前把他写成0，这也会报错*/

这边给出我测试通过的方法

/*分页按照姓名模糊查询学生信息的部分字段
使用这种方法必须要把查询和查询总数分开，查询一共进行两次*/
@Query(value ="select s.name,s.age,s.sex from student s where if(?1 !='',s.name like ?1,1=1) limit :m,:n",
nativeQuery = true)
public List<Map<String,Object>> findStudents(@Param("name")String name,@Param("m")String m,@Param("n")String n);
@Query(value="select count(1) as total from student s where if(:name !='',s.name like :name,1=1)",
nativeQuery = true)
public Map<String,Object> findStudentsCount(@Param("name")String name);

/*在services的调用如下（不全部贴出来，只写关键步骤）*/
//防止分页出错处理
pageno<=0?1:pageno;
pagesize<=0?10:pagesize;
//我们可以通过Pageable来得到limit的offset和size
Pageable pageable = PageRequest.of(pageno-1,pagesize);
//services调用
List<Map<String,Object>> students = studentServices.finStudents(name,pageable.getOffset(),pageable.getPageSize());
long total = studentServices.findStudentsCount(name).get("total");

偶然看到网上有个大神将Page<T>给重新构造了一遍，把springboot自带的这些问题全给解决了，基本上就是我上面写的这些分页的整合，通过一个Page<T>就可以办到，他是说测试没问题，我看了一下他的代码也觉得没毛病，但是就是忘记保存了，有需要的人可以自己去找一下，然后找到的话，开心的话就分享给我一下。因为挺后悔的没有保存，万一要是用得着呢。

这些单表的操作全部都可以转化为多表的操作，方法都一样，只不过是sql语句不一样而已。把sql语句改成你想要的多表关联查询就可以了。

还有一点在springboot查询数据库时需要注意的，如果查询数据中包含时间

第一记得加上

DATE_FORMAT(createtime,'%Y-%m-%d %H:%i:%s') createtime

以防查询得到的时间结果后面多了个.0

第二，记得在application.properties加上时区设置

#时区设置
spring.jackson.time-zone=GMT+8

以防查询得到的时间比跟我们当前时间不一致。

本人只是一个还在学习的小喽啰，有什么错误或者需要补充的地方，欢迎一起讨论讨论。这些代码纯手打，有拼写错误的地方见谅一下。