文章目录
SpringBoot使用JPA@Query注解查询
前两篇文章介绍了SpringDataJPA的两种查询方式,使用API查询,使用方法名查询,那么这篇文章介绍了如何使用 @Query 注解来自定义JPQL语句查询
1. 环境配置
之前的文章中有,复制粘贴 依赖坐标
和 application.yml
配置就行
参考环境配置 : SpringBoot框架使用SpringDataJPA
2. 数据库表配置
2.1 问题表创建语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_problem
-- ----------------------------
DROP TABLE IF EXISTS `tb_problem`;
CREATE TABLE `tb_problem` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ID',
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标题',
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '内容',
`createtime` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
`updatetime` datetime(0) NULL DEFAULT NULL COMMENT '修改日期',
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID',
`nickname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
`visits` bigint(20) NULL DEFAULT NULL COMMENT '浏览量',
`thumbup` bigint(20) NULL DEFAULT NULL COMMENT '点赞数',
`reply` bigint(20) NULL DEFAULT NULL COMMENT '回复数',
`solve` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否解决',
`replyname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '回复人昵称',
`replytime` datetime(0) NULL DEFAULT NULL COMMENT '回复日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '问题' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_problem
-- ----------------------------
INSERT INTO `tb_problem` VALUES ('1', 'PHP进阶的有关问题', '比如,我有一个表单页,需要填tit,tel以及上传的文件', '2022-05-06 15:41:58', '2022-05-07 15:42:04', '1', '三胖', 541, 200, 0, '1', 'tom', '2022-05-07 07:42:52');
INSERT INTO `tb_problem` VALUES ('2', 'SpringMVC怎么传输Json数据', '是打发士大夫撒打发士大夫撒地方官后更改', '2022-05-06 15:43:53', '2022-05-07 13:43:57', '2', '第二个三胖', 112, 12, 3, '2', 'jrry', '2022-05-07 13:44:30');
INSERT INTO `tb_problem` VALUES ('3', 'SpringCloud进阶', '氨基酸录得风景你', '2022-05-06 15:59:06', '2022-05-07 15:59:09', '1', '三胖', 223, 11, 2, '1', 'rose', '2022-05-07 15:59:30');
SET FOREIGN_KEY_CHECKS = 1;
2.2 问题表与标签表(多对多)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_pl
-- ----------------------------
DROP TABLE IF EXISTS `tb_pl`;
CREATE TABLE `tb_pl` (
`problemid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '问题ID',
`labelid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标签ID',
PRIMARY KEY (`problemid`, `labelid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_pl
-- ----------------------------
INSERT INTO `tb_pl` VALUES ('1', '1');
INSERT INTO `tb_pl` VALUES ('1', '2');
INSERT INTO `tb_pl` VALUES ('2', '1');
SET FOREIGN_KEY_CHECKS = 1;
3. 实体类配置
3.1 问题表实体类
package com.tensquare.qa.pojo;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
/**
* problem实体类
* @author Administrator
*
*/
@Entity
@Table(name="tb_problem")
public class Problem implements Serializable{
@Id
private String id;//ID
private String title;//标题
private String content;//内容
private java.util.Date createtime;//创建日期
private java.util.Date updatetime;//修改日期
private String userid;//用户ID
private String nickname;//昵称
private Long visits;//浏览量
private Long thumbup;//点赞数
private Long reply;//回复数
private String solve;//是否解决
private String replyname;//回复人昵称
private java.util.Date replytime;//回复日期
... GET SET 方法 ...
}
3.2 问题表与标签表 PL 实体类
package com.tensquare.qa.pojo;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
@Entity
@Table(name = "tb_pl")
public class PL implements Serializable {
@Id
private String problemid;
// 联合主键 两个字段加在一起不能有相同的值 即可
@Id
private String labelid;
public String getProblemid() {
return problemid;
}
public void setProblemid(String problemid) {
this.problemid = problemid;
}
public String getLabelid() {
return labelid;
}
public void setLabelid(String labelid) {
this.labelid = labelid;
}
}
4. Dao代码
那么problemid ,labelid 对应的1 和2 是什么意思呢,问题ID是1的 problemid ,对应的标签有两个 1, 2 , 那么暂定1=java问题标签
2=php问题标签
,也就是 java和php的标签下都有这个问题,也就是说这个问题对应着两个标签
那么如果按照标签=1查询问题的话,也就是说关于java的问题有几个,那么可以看到 Label=1对应的 Problem字段有两个,也就是说关于java的问题有两个,分别是问题1,和2
package com.tensquare.qa.dao;
import com.tensquare.qa.pojo.Problem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
/**
* problem数据访问接口
*
* @author Administrator
*/
public interface ProblemDao extends JpaRepository<Problem, String>, JpaSpecificationExecutor<Problem> {
/***
* 查询最新问题列表
* 需求 : 查询指定标签下的问题,按回复时间降序排序, 分页查询
* @param labelId
* @param pageable
* @return
* */
// 原生 sql 语句 : SELECT * FROM `tb_problem` p where p.id IN(select problemid from tb_pl where tb_pl.labelid = 1) ORDER BY p.replytime DESC
@Query("select p from Problem p where id in( select problemid from PL where labelid=?1 ) order by replytime desc")
public Page<Problem> findNewListByLabelId(String labelId, Pageable pageable);
/*** 根据标签ID查询热门问题列表
* 需求: 查询指定标签下的问题回复数,按回复数量降序排序, 分页查询
* @param labelId
* @param pageable
* @return
* */
@Query("select p from Problem p where id in( select problemid from PL where labelid=?1 ) order by reply desc")
public Page<Problem> findHotListByLabelId(String labelId, Pageable pageable);
/*** 根据标签ID查询等待回答列表
* 需求: 查询指定标签下的问题,回复数为0 ,且发布时间为最新的问题列表降序排序
* @param labelId
* @param pageable
* @return
* */
@Query("select p from Problem p where id in( select problemid from PL where labelid=?1 ) and reply=0 order by createtime desc")
public Page<Problem> findWaitListByLabelId(String labelId, Pageable pageable);
}
5. Service 代码
package com.tensquare.qa.service;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Selection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import com.tensquare.qa.dao.ProblemDao;
import com.tensquare.qa.pojo.Problem;
import utils.IdWorker;
/**
* problem服务层
*
* @author Administrator
*/
@Service
public class ProblemService {
@Autowired
private ProblemDao problemDao;
// 根据标签ID查询最新问题列表
public Page<Problem> newlist(String label, int page, int size) {
// PageRequest.of(page - 1, size) 分页对象, 第一个参数是页码,第二个参数是每页显示几条
return problemDao.findNewListByLabelId(label, PageRequest.of(page - 1, size));
}
// 根据标签ID查询热门问题列表
public Page<Problem> hotlist(String label, int page, int size) {
return problemDao.findHotListByLabelId(label, PageRequest.of(page - 1, size));
}
// 根据标签ID查询等待回答列表
public Page<Problem> waitlist(String label, int page, int size) {
return problemDao.findWaitListByLabelId(label, PageRequest.of(page - 1, size));
}
}
6. Controller 代码
package com.tensquare.qa.controller;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.tensquare.qa.pojo.Problem;
import com.tensquare.qa.service.ProblemService;
import entity.PageResult;
import entity.Result;
import entity.StatusCode;
/**
* problem控制器层
*
* @author Administrator
*/
@RestController
@CrossOrigin
@RequestMapping("/problem")
public class ProblemController {
@Autowired
private ProblemService problemService;
// 最新回答列表 restFul风格
@RequestMapping(value = "/newlist/{label}/{page}/{size}", method = RequestMethod.GET)
public Result newlist(@PathVariable String label, @PathVariable int page, @PathVariable int size) {
Page<Problem> newlist = problemService.newlist(label, page, size);
return new Result(true, StatusCode.OK, "查询成功",
new PageResult(newlist.getTotalElements(), newlist.getContent()));
}
// 热门回答列表
@RequestMapping(value = "/hotlist/{label}/{page}/{size}", method = RequestMethod.GET)
public Result hotlist(@PathVariable String label, @PathVariable int page, @PathVariable int size) {
Page<Problem> newlist = problemService.hotlist(label, page, size);
return new Result(true, StatusCode.OK, "查询成功",
new PageResult(newlist.getTotalElements(), newlist.getContent()));
}
// 等待回答列表
@RequestMapping(value = "/waitlist/{label}/{page}/{size}", method = RequestMethod.GET)
public Result waitlist(@PathVariable String label, @PathVariable int page, @PathVariable int size) {
Page<Problem> newlist = problemService.waitlist(label, page, size);
return new Result(true, StatusCode.OK, "查询成功",
new PageResult(newlist.getTotalElements(), newlist.getContent()));
}
}
7. 测试结果
需求 : 根据标签ID查询最新问题列表
接口地址 : localhost:9003/problem/newlist/1/1/3
需求 : 根据标签ID查询热门问题列表
接口地址 : localhost:9003/problem/hotlist/1/1/3
需求 : 根据标签ID查询等待回答列表
接口地址: localhost:9003/problem/waitlist/1/1/3
8. 简单的update操作
Dao层代码
/*
* 文章审核 ,改变状态为 1
* */
@Modifying
@Query("update Article as a set a.state = 1 where a.id = ?1")
public void examine(String articleId);