Springboot+thymeleaf项目之@Query注解实现复杂查询

最新推荐文章于 2024-05-02 21:23:35 发布

爱学习的诸葛铁锤

最新推荐文章于 2024-05-02 21:23:35 发布

阅读量1.2k

点赞数 1

分类专栏： JAVA学习文章标签： java mysql spring boot

本文链接：https://blog.csdn.net/qq_41873311/article/details/110389307

版权

JAVA学习专栏收录该内容

3 篇文章 0 订阅

订阅专栏

需求分析

现有一张表，字段有id，dept_name（部门名称），exit_or_enter(进或是出)，staff_name(员工名称)

备注：exit_or_enter 进门为1，出门为2

需要统计每个部门 “进门”的人数（即 exit_or_enter=1）以及对应的人员名
在这里插入图片描述

为了方便理解，先来看一下最后的效果

字段dept_name(部门），num(“进”部门的人数），names（部门人员名集合）
在这里插入图片描述

自定义SQL语句

如果用JPA常用的查询语句，似乎不能完成这一复杂的查询。所以我们为了达到这一目标，需要自定义SQL语句

分析一下需求，要完成这一查询，或许要用到多表连接，这里我使用的是“LEFT JOIN ON”。

表a

首先查询所有的部门，作为表a

 (select distinct dept_name as dept_name from staff) a

看一下查询结果，一共4个部门，如图。

在这里插入图片描述

表b

再统计每个部门“进门”的人数和对应的人员名集合，作为表b

( select dept_name,count(*) as num,GROUP_CONCAT(staff_name) as names from staff where exit_or_enter=1 GROUP BY dept_name) b

COUNT(*) 很好理解，合计一下。问题在合并人员名，形成一个集合。

由于我这里使用的是MySQL数据库，所以我们要用到函数GROUP_CONCAT(）, 功能是合并列值将一列的多个值合并成一行

这样，整条SQL语句就很好理解了。看一下统计结果，一共3条记录，如图。

在这里插入图片描述

对比一下我们预期的效果，少了一个部门“运营”，虽然sum是0，names是空，但是我们为了之后的展示，仍要统计。

连接表a和表b

select a.dept_name,COALESCE(b.num,0) as num,COALESCE(b.names,'空') as names from
	 (select distinct dept_name from staff) a
	left join 
	( select dept_name,count(*) num,GROUP_CONCAT(staff_name) as names from staff where exit_or_enter=1 GROUP BY dept_name) b
	on a.dept_name=b.dept_name

使用LEFT JOIN ON 将两表连接，连接条件是a.dept_name=b.dept_name

LEFT JOIN学习链接: https://www.w3school.com.cn/sql/sql_join_left.asp.

根据列名“dept_name”将两个表连接成一个表之后，我们再对这个表进行查询，查询a表的dept_name，b表的num和names

然后这里用到一个函数COALESCE()
这个函数主要用来进行空值处理，其参数格式如下：
COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
如果expression不为空值则返回expression；否则判断value1是否是空值，
如果value1不为空值则返回value1；否则判断value2是否是空值，
如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

因此COALESCE(b.num,0)的意思就是处理表b字段num的空值
如果不是空值，就返回表达式的值；如果是空值即num=0，就返回value=0

所以，COALESCE(b.names,‘空’)的意思就是处理表b字段names的空值
如果不是空值，就返回表达式的值；如果是空值即names=null，就返回value=‘空’

查询结果如图，正确，达到预期目标！

在这里插入图片描述

至此,SQL语句就写好啦！

使用@Query注解

Entity层

package com.example.thymedemo.entity;

import javax.persistence.*;

@Entity(name = "staff")
public class StaffInfo {
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Id
    private int id;
    private String deptName;
    private String staffName;
    private int exit_or_enter;

    public int getId(){
        return id;
    }
    public void setId(int id){
        this.id = id;
    }
    public String getDeptName() {
        return deptName;
    }
    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }
    public String getStaffName() {
        return staffName;
    }
    public void setStaffName(String staffName) {
        this.staffName = staffName;
    }
    public int getExit_or_enter() { return exit_or_enter; }
    public void setExit_or_enter(int exit_or_enter) { this.exit_or_enter = exit_or_enter; }
}

Dao层

package com.example.thymedemo.dao;

import com.example.thymedemo.entity.StaffInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;

public interface StaffInfoDao extends JpaRepository<StaffInfo, Integer> {

    @Query(value = "select a.dept_name,COALESCE(b.num,0) as num,COALESCE(b.names,'空') as names from\n" +
            "\t (select distinct dept_name from staff) a\n" +
            "\tleft join ( select dept_name,count(*) num,GROUP_CONCAT(staff_name) as names from staff where exit_or_enter=1 GROUP BY dept_name) b\n" +
            "\ton a.dept_name=b.dept_name",nativeQuery = true
    )
    List<Object[]> findContent();
}

@Query(value=" ",nativeQuery = true),value里面复制粘贴我们刚才整理好的SQL语句

nativeQuery = true，是可以执行原生sql语句，所谓原生sql，也就是说这段sql拷贝到数据库中，然后把参数值给一下就能运行了

然后定义一个方法 findContent()，准备调用

Controller层

package com.example.thymedemo.controller;

import com.example.thymedemo.dao.StaffInfoDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;

@Controller
public class StaffInfoController {
    @Autowired
    StaffInfoDao staffInfoDao;

    @RequestMapping("/count")
    public String count(Model model){

        List<Object[]> data = staffInfoDao.findContent();
        model.addAttribute("data",data);

        return "count";
    }
}