SpringBoot整合SpringDataJpa QueryDSL和原生态SQL

1.导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jmj</groupId>
    <artifactId>springDataJPA-Springboot</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <querydsl.version>4.4.0</querydsl.version>
        <apt.version>1.1.3</apt.version>
        <!--    声明springboot的版本号 -->
        <spring-boot.version>2.5.5</spring-boot.version>
    </properties>
    <!--  引入springboot官方提供的所有依赖的版本号定义，如果项目中使用相关依赖，可以不必写版本号了-->
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <!--    引入springboot的web项目的依赖        -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--     data - jpa 启动器  -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!--        druid连接-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
    </dependencies>

    <!--  这个插件是为了让程序自动生成query type （查询实体，命名方式为:"Q"+对应实体名） maven插件 -->
    <build>
        <plugins>
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>${apt.version}</version>
                <dependencies>
                    <dependency>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>${querydsl.version}</version>
                    </dependency>
                </dependencies>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/queries</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                            <logOnlyOnError>true</logOnlyOnError>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>



</project>

2.mapper

package com.jmj.springDataApp.mapper;

import com.jmj.springDataApp.pojo.Student;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;

public interface StudentDSLMapper extends PagingAndSortingRepository<Student,Long> , QuerydslPredicateExecutor<Student> {
}

3.test

package com.jmj.springDataApp.mapper;

import com.jmj.springDataApp.pojo.QStudent;
import com.jmj.springDataApp.pojo.Student;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.Visitor;
import com.querydsl.core.types.dsl.BooleanExpression;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Nullable;

import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
class StudentDSLMapperTest {

    @Autowired
    private StudentDSLMapper mapper;

    @Test
    void test1() {
        QStudent student = QStudent.student;
        BooleanExpression eq = student.id.eq(7L);


        Iterable<Student> all = mapper.findAll(eq);
        System.out.println(all);

    }

    /**
     * 查询名称范围(in)
     * id>大于
     * 地址精确
     *
     */
    @Test
    void name() {
        QStudent student = QStudent.student;
        BooleanExpression a = student.id.in(3, 7, 9).and(student.name.startsWith("a")).and(student.grade.gt(0));
        Iterable<Student> all = mapper.findAll(a);
        System.out.println(all);

    }
}

原生态

package com.jmj.springDataApp.mapper;

import com.jmj.springDataApp.pojo.QStudent;
import com.jmj.springDataApp.pojo.Student;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceContext;
import java.util.List;

@SpringBootTest
public class EntityManagerTest {

    @PersistenceContext
    private EntityManager em;

    @Test
    void test1() {
        JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(em);
        QStudent student = QStudent.student;
        JPAQuery<Tuple> b = jpaQueryFactory.select(student.id, student.name, student.grade)
                .from(student)
                .where(student.id.gt(3), student.name.contains("b"))
                .orderBy(student.id.desc())
                .limit(3);

        List<Tuple> fetch = b.fetch();
        for (Tuple tuple : fetch) {
            Long aLong = tuple.get(student.id);
            String s = tuple.get(student.name);
            Integer integer = tuple.get(student.grade);
            System.out.println(aLong+s+integer);
        }
    }


    @Test
    void test2() {
        JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(em);
        QStudent student = QStudent.student;
        JPAQuery<Long> b = jpaQueryFactory.select(student.id.sum())
                .from(student)
                .where(student.id.gt(3), student.name.contains("b"))
                .orderBy(student.id.desc())
                .limit(3);

        List<Long> fetch = b.fetch();
        for (Long aLong : fetch) {
            System.out.println(aLong);
        }

        }

}