Spring-data-jpa扩展查询 QueryDSL 实践

说明: QueryDSL是以函数连接的方式将SQL调用进行拆分，比较spring data jpa中的criteria查询方法还是简洁了不少。 (转载请注明来源:cnblogs coder-fang)

用例:通过服务调用，使用querydsl进行查询并直接返回DTO对象(自定义传输对象(根据业务需求)，注意区别于Entity)

实践步骤:

1.　　创建user与depart表，使用外键进行关联，并插入一些模拟数据。

2.　　创建sprintboot项目，在pom文件中加入以下依赖:

　　　　 <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <scope>provided</scope>
        </dependency>

 

3.　　在pom文件中<build>--><plugins>节点下加入plugin：

           <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>
                <dependencies>
                    <dependency>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>4.1.3</version>
                    </dependency>
                </dependencies>
            </plugin>

 

4.　　生成相关entity与repository对象,这里以user为例:

package com.test.demo.db;
//

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

/**
 * User generated by hbm2java
 */
@Entity
@Table(name = "user", catalog = "testdb")
public class User implements java.io.Serializable {

    private Integer id;
    private Department department;
    private String username;

    public User() {
    }

    public User(Department department, String username) {
        this.department = department;
        this.username = username;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "id", unique = true, nullable = false)
    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_depart")
    public Department getDepartment() {
        return this.department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    @Column(name = "username", length = 45)
    public String getUsername() {
        return this.username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

}

package com.test.demo.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.stereotype.Repository;

import com.test.demo.db.User;



@Repository
public interface UserRepository extends QueryDslPredicateExecutor<User>, JpaRepository<User, Integer>,JpaSpecificationExecutor<User>{
        
}

注意：repository需要继承 QueryDslPredicateExecutor<T>接口。

5.　　生成业务传输对象DTO：

package com.test.demo.controller;

import com.querydsl.core.annotations.QueryProjection;
import lombok.Data;

@SuppressWarnings("unused")
public @Data class UserDTO {    
    private String username;        
    private String departname;
}

 

6.　　创建controller进行测试:

package com.test.demo.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.test.demo.db.QUser;
import com.test.demo.repo.UserRepository;

@RestController
@RequestMapping("/")
public class TestController {

    @Autowired
    UserRepository userRepo;

    @Autowired
    @PersistenceContext
    EntityManager em;

    private JPAQueryFactory queryFactory;

    @PostConstruct
    public void init() {
        queryFactory = new JPAQueryFactory(em);
    }

    @RequestMapping("/users")
    Object getUsers(@RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "10") Integer size,
            @RequestParam(value = "name", required = false) String name,
            @RequestParam(value = "depart", required = false) String depart) {

        QUser user = QUser.user;

        JPAQuery<UserDTO> query = queryFactory
                .select(Projections.bean(UserDTO.class, user.username, user.department.name.as("departname")))
                .from(user);

        BooleanExpression pre = null;
        
        if (name!=null && !name.isEmpty()) {
            pre = user.username.startsWith(name);
        }
        if (depart!=null && !depart.isEmpty()) {
            pre = user.department.name.startsWith(depart);        
        }

        query.where(pre);
        query.limit(size);
        query.offset((page-1)*size);

        List<UserDTO> result = query.fetch();
        Map<String, Object> map = new HashMap<>();

        map.put("total", userRepo.count(pre));
        map.put("data", result);
        return map;

    }
}

注：这里就是使用querydsl进行查询，并直接转换需要的属性至DTO。并且代码中的pre是可以根据参数动态拼接的。

 

7.　　测试结果:

 

这是查询日志:

 

 

 

完。