个人学习系列 - Spring Boot 整合 QueryDSL

之前研究Jooq，今天来研究一下搭配JPA的QueryDSL吧。

简介

Querydsl是一个Java开源框架用于构建类型安全的SQL查询语句。它采用API代替拼凑字符串来构造查询语句。可跟 Hibernate 和 JPA 等框架结合使用。

新建Spring Boot项目

。。。还说啥？

1. pom.xml

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--QueryDSL支持-->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <scope>provided</scope>
        </dependency>
        
        <!--QueryDSL支持-->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
	    <!-- 这个插件是为了让程序自动生成query type(查询实体，命名方式为："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>
        </plugins>
    </build>

2. application.yml

server:
  port: 8888
spring:
  datasource:
    url: jdbc:mysql://IP地址:3306/querydsl?characterEncoding=UTF-8
    username: 用户名
    password: 密码
  jpa:
    show-sql: true # 控制台打印SQL
    hibernate:
      ddl-auto: update

3. JPAQueryFactory Bean

/**
 * JPAQueryFactory Bean
 * @author zhouzhaodong
 */
@Configuration
public class PeopleQueryConfig {
    @Bean
    public JPAQueryFactory jpaQuery(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

4. 新建实体类

/**
 * 实体类
 * @author zhouzhaodong
 */
@Entity
@Table(name="people")
public class People {

    @Id
    @Column(name="id")
    @GeneratedValue
    private Integer id;

    @Column(name="name")
    private String name;

    @Column(name="age")
    private Integer age;

    @Column(name="address")
    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

5. 新建service

/**
 * service
 * @author zhouzhaodong
 */
public interface PeopleService {

    /**
     * 根据id查询
     * @param id
     * @return
     */
    People selectOne(Integer id);

}

6. 新建serviceImpl

/**
 * serviceImpl
 * @author zhouzhaodong
 */
@Service
public class PeopleServiceImpl implements PeopleService {

    @Resource
    JPAQueryFactory queryFactory;

    @Override
    public People selectOne(Integer id) {
        QPeople people = QPeople.people;
        return queryFactory.selectFrom(people).where(people.id.eq(id)).fetchOne();
    }
}

7. 新建controller

/**
 * controller
 * @author zhouzhaodong
 */
@RestController
public class PeopleController {

    @Resource
    PeopleService peopleService;

    @RequestMapping("/selectById")
    public People selectById(){
        return peopleService.selectOne(0);
    }

}

8. 新建repository，可以使用JPA原有的功能

/**
 * 实现jpaRepository
 * @author zhouzhaodong
 */
public interface PeopleRepository extends JpaRepository<People, Integer> {
}

9. postman测试

数据库数据如下：

请求结果：

个人博客地址：

http://www.zhouzhaodong.xyz

Github代码地址：

https://github.com/zhouzhaodong/springboot/tree/master/querydsl