Springboot + MySQL+ JPA Ⅶ querydsl使用方法

一、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类文件

二、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;
}