Springboot + MySQL+ JPA Ⅶ querydsl使用方法

素颜马尾好姑娘i 2023-10-02 16:20 25阅读 0赞

### **一、pom.xml** ###

#### 1、添加依赖（版本号4.1会导致报错，使用4.4.0之后解决） ####

<dependency>
    	<groupId>com.querydsl</groupId>
    	<artifactId>querydsl-apt</artifactId>
    	<version>4.4.0</version>
    </dependency>
    
    <dependency>
    	<groupId>com.querydsl</groupId>
    	<artifactId>querydsl-jpa</artifactId>
    	<version>4.4.0</version>
    </dependency>

#### 2、添加脚本（用来生产Q文件的） ####

<plugin>
    	<groupId>com.mysema.maven</groupId>
    	<artifactId>apt-maven-plugin</artifactId>
    	<version>1.1.3</version>
    	<executions>
    		<execution>
    			<goals>
    				<goal>process</goal>
    			</goals>
    			<configuration>
    				<outputDirectory>target/generated-sources/java</outputDirectory>
    				<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
    			</configuration>
    		</execution>
    	</executions>
    </plugin>

实体类写完后需要点击maven下面的compile来生成Q类文件

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_16_color_FFFFFF_t_70_g_se_x_16][]

### 二、select单表 ###

#### User类 ####

@Data
    @Entity
    @JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
    public class User {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    
        private String name;
    
        private Integer age;
    
        @ManyToOne  //user对work是多对一
        @JoinColumn(name="work_id")  //所对应的数据库的列是work_id
        private Work work;
    
    }

#### Work类 ####

@Data
    @Entity
    @JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
    public class Work {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    
        private String name;
    }

#### service ####

@Service
    public class DslService {
    
        @Autowired
        private UserDao userDao;
    
        @Autowired
        private EntityManager entityManager;
    
        private JPAQueryFactory jpaQueryFactory;
    
    	//项目启动后就执行
        @PostConstruct
        public void initFactory()
        {
            jpaQueryFactory = new JPAQueryFactory(entityManager);
        }
    
    
        public List<User> getAll(){
    		
            QUser qUser = QUser.user;
            QWork qWork = QWork.work;
    		
            // queryFactory 是上方定义的工厂实体
            // select（生成的实体类的字段）.from（生成实体类的名称）.on（上方要查询的条件）.orderBy（排序）.fetch（）进行查询
            Predicate predicate = qUser.work.id.eq(qWork.id);
            JPAQuery<User> jpaQuery = jpaQueryFactory.select(qUser).from(qUser)
                    .leftJoin(qWork)
                    .on(predicate)
                    .orderBy(qUser.name.desc());
            List<User> list = jpaQuery.fetch();
    
            return list;
        }
    }

#### control ####

@RestController
    public class UserControl {
    	
    	@Autowired
        private DslService dslService;
    	
    	@GetMapping("/getAll")
        public List<User> getAll(){
            return dslService.getAll();
        }
    	
    }

### 三、多表返回的是Tuple，tuple.get(index,实体类.class)或者是tuple.get(q实体类) ###

public List<User> getAll(){
    
    	QUser qUser = QUser.user;
    	QWork qWork = QWork.work;
    
    	// queryFactory 是上方定义的工厂实体
    	// select（生成的实体类的字段）.from（生成实体类的名称）.where（上方要查询的条件）.orderBy（排序）.fetch（）进行查询
    	Predicate predicate = qUser.work.id.eq(qWork.id);
    	JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(qUser,qWork.name).from(qUser)
    			.leftJoin(qWork)
    			.on(predicate)
    			.orderBy(qUser.name.desc());
    	List<Tuple> list = jpaQuery.fetch();
    	List<User> users = new ArrayList<>();
    	list.forEach( (tuple) -> {
    		String name = tuple.get(qUser).getName();  //tuple.get(0,User.class).getName()效果一样
    		Integer age = tuple.get(qUser).getAge();
    		Integer id = tuple.get(qUser).getId();
    		Work work = tuple.get(qUser).getWork();
    		User user = new User();
    		user.setId(id);
    		user.setName(name);
    		user.setAge(age);
    		user.setWork(work);
    		users.add(user);
    	});
    
    	return users;
    }

### 四、分页查询 ###

public List<User> getAll(){
    
    	QUser qUser = QUser.user;
    	QWork qWork = QWork.work;
    
    	// queryFactory 是上方定义的工厂实体
    	// select（生成的实体类的字段）.from（生成实体类的名称）.where（上方要查询的条件）.orderBy（排序）.fetch（）进行查询
    	Predicate predicate = qUser.work.id.eq(qWork.id);
    	Pageable pageable = PageRequest.of(1,5); //第二页，页面大小为5（可以由入参决定）
    	JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(qUser,qWork.name).from(qUser)
    			.leftJoin(qWork)
    			.on(predicate)
    			.offset(pageable.getOffset())
    			.limit(pageable.getPageSize())
    			.orderBy(qUser.name.desc());
    	List<Tuple> list = jpaQuery.fetch();
    	List<User> users = new ArrayList<>();
    	list.forEach((tuple)->{
    		String name = tuple.get(qUser).getName();
    		Integer age = tuple.get(qUser).getAge();
    		Integer id = tuple.get(qUser).getId();
    		Work work = tuple.get(qUser).getWork();
    		User user = new User();
    		user.setId(id);
    		user.setName(name);
    		user.setAge(age);
    		user.setWork(work);
    		users.add(user);
    	});
    
    	return users;
    }

###  ###

### 附加： ###

### 获取的tuple类型可以通过流转换成集合（java8的特性） ###

public List<User> getAll(){
    
    	QUser qUser = QUser.user;
    	QWork qWork = QWork.work;
    
    	// queryFactory 是上方定义的工厂实体
    	// select（生成的实体类的字段）.from（生成实体类的名称）.where（上方要查询的条件）.orderBy（排序）.fetch（）进行查询
    	Predicate predicate = qUser.work.id.eq(qWork.id);
    	Pageable pageable = PageRequest.of(1,5);
    	JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(qUser,qWork.name).from(qUser)
    			.leftJoin(qWork)
    			.on(predicate)
    			.offset(pageable.getOffset())
    			.limit(pageable.getPageSize())
    			.orderBy(qUser.name.desc());
    	List<Tuple> list = jpaQuery.fetch();
    	//遍历 java8 自带流转换成集合
    	List<User> collect = list.stream().map(tuple -> {
    		User user = tuple.get(qUser);
    		return user;
    	}).collect(Collectors.toList());
    	return collect;
    }

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/fa0bacbb28024645b952f24a1c83d72b.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6Iul5Y2a6LGG,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_16_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/183f9567bc514ba78654812b4da1cdd9.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6Iul5Y2a6LGG,size_16,color_FFFFFF,t_70,g_se,x_16