SpringBoot12 QueryDSL02之利用QueryDSL实现多表关联查询
1 业务需求
有的系统业务逻辑比较复杂，存在着多表关联查询的的情况，查询的内容不仅仅是单张表的的内容而是多张表的字段组合而成的，直接使用SplringDataJPA实现是比较复杂的，但是如果使用QueryDSL可以很方便的实现，而且利用QueryDSL实现的关联查询不仅可以只输出单张表中满足条件的内容还可以输出多张表组合而成的字段
2 QueryDSL关联查询之单表数据输出
2.1 创建两个数据库表
一个学生包和一个学校表，学校表和学生表时一对多的关系
/* Navicat MySQL Data Transfer Source Server : mysql5.4 Source Server Version : 50540 Source Host : localhost:3306 Source Database : springboot Target Server Type : MYSQL Target Server Version : 50540 File Encoding : 65001 Date: 2018-03-31 09:11:27 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `querydsl_demo_school` -- ---------------------------- DROP TABLE IF EXISTS `querydsl_demo_school`; CREATE TABLE `querydsl_demo_school` ( `id` int(8) NOT NULL, `school` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of querydsl_demo_school -- ---------------------------- INSERT INTO `querydsl_demo_school` VALUES ('1', '弥陀中学'); INSERT INTO `querydsl_demo_school` VALUES ('2', '大足中学'); -- ---------------------------- -- Table structure for `querydsl_demo_student` -- ---------------------------- DROP TABLE IF EXISTS `querydsl_demo_student`; CREATE TABLE `querydsl_demo_student` ( `id` int(36) NOT NULL, `name` varchar(10) NOT NULL, `age` int(3) NOT NULL, `address` varchar(24) DEFAULT NULL, `school_id` int(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of querydsl_demo_student -- ---------------------------- INSERT INTO `querydsl_demo_student` VALUES ('1', 'warrior', '24', '重庆市大足区', '1'); INSERT INTO `querydsl_demo_student` VALUES ('2', 'fury', '23', '渝足', '1'); INSERT INTO `querydsl_demo_student` VALUES ('3', 'zeus', '32', '智凤', '1'); INSERT INTO `querydsl_demo_student` VALUES ('4', 'wys', '21', '广工', '2'); INSERT INTO `querydsl_demo_student` VALUES ('5', 'wym', '18', '松山湖', '2');
2.2 根据数据表创建实体类
package cn.test.demo.query_demo.model.javaModel; import lombok.Data; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; import java.io.Serializable; /** * @author 王杨帅 * @create 2018-03-30 21:16 * @desc 学校对应的实体类 **/ @Entity @Data @Table(name = "querydsl_demo_school") public class SchoolModel implements Serializable { @Id private Long id; private String school; }
package cn.test.demo.query_demo.model.javaModel; import lombok.Data; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; import java.io.Serializable; /** * @author 王杨帅 * @create 2018-03-29 21:52 * @desc 学生实体类 **/ @Entity @Data @Table(name = "querydsl_demo_student") public class StudentModel implements Serializable { @Id private Long id; private String name; private Integer age; private String address; @Column(name = "school_id") private Long schoolId; }
2.3 利用maven工具根据实体类创建查询实体类
package cn.test.demo.query_demo.model.javaModel; import static com.querydsl.core.types.PathMetadataFactory.*; import com.querydsl.core.types.dsl.*; import com.querydsl.core.types.PathMetadata; import javax.annotation.Generated; import com.querydsl.core.types.Path; /** * QSchoolModel is a Querydsl query type for SchoolModel */ @Generated("com.querydsl.codegen.EntitySerializer") public class QSchoolModel extends EntityPathBase<SchoolModel> { private static final long serialVersionUID = 643543783L; public static final QSchoolModel schoolModel = new QSchoolModel("schoolModel"); public final NumberPath<Long> id = createNumber("id", Long.class); public final StringPath school = createString("school"); public QSchoolModel(String variable) { super(SchoolModel.class, forVariable(variable)); } public QSchoolModel(Path<? extends SchoolModel> path) { super(path.getType(), path.getMetadata()); } public QSchoolModel(PathMetadata metadata) { super(SchoolModel.class, metadata); } }
package cn.test.demo.query_demo.model.javaModel; import static com.querydsl.core.types.PathMetadataFactory.*; import com.querydsl.core.types.dsl.*; import com.querydsl.core.types.PathMetadata; import javax.annotation.Generated; import com.querydsl.core.types.Path; /** * QStudentModel is a Querydsl query type for StudentModel */ @Generated("com.querydsl.codegen.EntitySerializer") public class QStudentModel extends EntityPathBase<StudentModel> { private static final long serialVersionUID = 751310108L; public static final QStudentModel studentModel = new QStudentModel("studentModel"); public final StringPath address = createString("address"); public final NumberPath<Integer> age = createNumber("age", Integer.class); public final NumberPath<Long> id = createNumber("id", Long.class); public final StringPath name = createString("name"); public final NumberPath<Long> schoolId = createNumber("schoolId", Long.class); public QStudentModel(String variable) { super(StudentModel.class, forVariable(variable)); } public QStudentModel(Path<? extends StudentModel> path) { super(path.getType(), path.getMetadata()); } public QStudentModel(PathMetadata metadata) { super(StudentModel.class, metadata); } }
2.4 创建持久层
package cn.test.demo.query_demo.dao; import cn.test.demo.query_demo.model.javaModel.SchoolModel; /** * @author 王杨帅 * @create 2018-03-30 21:19 * @desc **/ public interface SchoolJPA extends BaseJPA<SchoolModel> { }
2.5 创建持久层
技巧01：select() 方法指明查询结果只是来源于学生表，只不过这里是利用学生表对应的查询对象来表示的
坑01：虽然学校表和学生表是一对多的关系，但是select() 方法的参数就可以是学生表对应的查询实体对象也可以是学校表对应的查询实体对象，只不过select()中参数的类型决定了查询结果来源于那张表
技巧02：from() 方法指明关联查询的表，一个是学生表，另一个是学校表，只不过这里都是利用相关的查询实体来表示的
技巧03：where() 方法中既可以指定表之间的关联条件，又可以指定查询结果的条件限制
技巧04：fetch() 方法是执行查询操作并将查询到的数据组装成一个列表进行返回
@GetMapping(value = "/test") public List<StudentModel> test(@RequestParam("id") Long id) { QStudentModel _Q_studentModel = QStudentModel.studentModel; QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; return jpaQueryFactory.select(_Q_studentModel) .from(_Q_schoolModel, _Q_studentModel) .where(_Q_studentModel.schoolId.eq(_Q_schoolModel.id) .and(_Q_schoolModel.id.eq(id)) ) .fetch(); }
package cn.test.demo.query_demo.controller; import cn.test.demo.query_demo.model.javaModel.QSchoolModel; import cn.test.demo.query_demo.model.javaModel.QStudentModel; import cn.test.demo.query_demo.model.javaModel.SchoolModel; import cn.test.demo.query_demo.model.javaModel.StudentModel; import com.querydsl.jpa.impl.JPAQueryFactory; import lombok.Data; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.PostConstruct; import javax.annotation.Resource; import javax.persistence.EntityManager; import java.util.List; /** * @author 王杨帅 * @create 2018-03-30 21:20 * @desc **/ @RestController @Slf4j @RequestMapping(value = "/school") public class SchoolController { @Resource private EntityManager entityManager; private JPAQueryFactory jpaQueryFactory; @PostConstruct public void initFactory() { jpaQueryFactory = new JPAQueryFactory(entityManager); } @GetMapping(value = "/connect") public String connect() { String result = "测试school控制层"; log.info("测试信息为：{}", result); return result; } @GetMapping(value = "/findAll") public List<SchoolModel> findAll() { QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; List<SchoolModel> schoolModelList = jpaQueryFactory.selectFrom(_Q_schoolModel) .fetch(); log.info("查询到的数据为：{}", schoolModelList); return schoolModelList; } @GetMapping(value = "/test") public List<StudentModel> test(@RequestParam("id") Long id) { QStudentModel _Q_studentModel = QStudentModel.studentModel; QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; return jpaQueryFactory.select(_Q_studentModel) .from(_Q_schoolModel, _Q_studentModel) .where(_Q_studentModel.schoolId.eq(_Q_schoolModel.id) .and(_Q_schoolModel.id.eq(id)) ) .fetch(); } }
2.5 效果展示
这种关联查询只会将一个表中的数据查出，在本案例中只会将学生表中学校ID在学校表中出现的东西查询出来（即：如果学生表中有一条记录的学校ID不再学校表内，那么这条记录是不会被查询出来的），而且最终的查询结果没有学校表的信息，只有学生表的信息
3 QueryDSL关联查询之多表数据输出
3.1 创建数据库表
3.2 根据数据库表创建实体类
3.3 根据实体类创建查询实体类
3.4 创建数据传输实体类
package cn.test.demo.query_demo.model.dtoModel; import lombok.Data; import java.io.Serializable; /** * @author 王杨帅 * @create 2018-03-31 9:32 * @desc 学生详细信息实体 **/ @Data public class StudentDtoModel implements Serializable { private Long id; private String name; private Integer age; private String address; private Long schoolId; private String schoolname; }
3.5 创建持久层
3.6 创建控制层
技巧01：select() 方法中不再是简单的查询实体对象了，而是利用Porjections类提供的bean方法创建了一个新对象
技巧02：bean() 方法需要传入多个参数，第一个参数是查询结果封装类，之后的参数是将查询的字段和封装类进行对应
技巧03：如果封装类和查询实体类的属性名不一致时就需要别名，例如
学校表的对应的查询实体的学校名称字段是school而封装类对应的学校名称字段是schoolname,我们就需要利用下面的语句进行转换
_Q_schoolModel.school.as("schoolname")
@GetMapping(value = "/test02") public List<StudentDtoModel> test02() { QStudentModel _Q_studnetModel = QStudentModel.studentModel; QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; return jpaQueryFactory.select(Projections.bean( StudentDtoModel.class, _Q_studnetModel.id, _Q_studnetModel.name, _Q_studnetModel.address, _Q_studnetModel.age, _Q_studnetModel.schoolId, _Q_schoolModel.school.as("schoolname") )).from(_Q_schoolModel, _Q_studnetModel) .where(_Q_schoolModel.id.eq(_Q_studnetModel.schoolId)) .fetch(); }}
package cn.test.demo.query_demo.controller; import cn.test.demo.query_demo.model.dtoModel.StudentDtoModel; import cn.test.demo.query_demo.model.javaModel.QSchoolModel; import cn.test.demo.query_demo.model.javaModel.QStudentModel; import cn.test.demo.query_demo.model.javaModel.SchoolModel; import cn.test.demo.query_demo.model.javaModel.StudentModel; import com.querydsl.core.types.Projections; import com.querydsl.jpa.impl.JPAQueryFactory; import lombok.Data; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.annotation.PostConstruct; import javax.annotation.Resource; import javax.persistence.EntityManager; import java.util.List; /** * @author 王杨帅 * @create 2018-03-30 21:20 * @desc **/ @RestController @Slf4j @RequestMapping(value = "/school") public class SchoolController { @Resource private EntityManager entityManager; private JPAQueryFactory jpaQueryFactory; @PostConstruct public void initFactory() { jpaQueryFactory = new JPAQueryFactory(entityManager); } @GetMapping(value = "/connect") public String connect() { String result = "测试school控制层"; log.info("测试信息为：{}", result); return result; } @GetMapping(value = "/findAll") public List<SchoolModel> findAll() { QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; List<SchoolModel> schoolModelList = jpaQueryFactory.selectFrom(_Q_schoolModel) .fetch(); log.info("查询到的数据为：{}", schoolModelList); return schoolModelList; } @GetMapping(value = "/test") public List<SchoolModel> test(@RequestParam("id") Long id) { QStudentModel _Q_studentModel = QStudentModel.studentModel; QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; return jpaQueryFactory.select(_Q_schoolModel) .from(_Q_schoolModel, _Q_studentModel) .where(_Q_studentModel.schoolId.eq(_Q_schoolModel.id) // .and(_Q_schoolModel.id.eq(id)) ) .fetch(); } @GetMapping(value = "/test02") public List<StudentDtoModel> test02() { QStudentModel _Q_studnetModel = QStudentModel.studentModel; QSchoolModel _Q_schoolModel = QSchoolModel.schoolModel; return jpaQueryFactory.select(Projections.bean( StudentDtoModel.class, _Q_studnetModel.id, _Q_studnetModel.name, _Q_studnetModel.address, _Q_studnetModel.age, _Q_studnetModel.schoolId, _Q_schoolModel.school.as("schoolname") )).from(_Q_schoolModel, _Q_studnetModel) .where(_Q_schoolModel.id.eq(_Q_studnetModel.schoolId)) .fetch(); }}
4 QueryDSL参考文档
