spring jpa Subquery

转载

编程艺术大师 2024-10-17 13:35:47

文章标签 spring jpa Subquery java spring hibernate List 文章分类 架构后端开发

Spring Data Querydsl-JPA 的简单入门笔记

一、 Querydsl面试常识问题

1.Querydsl是什么？

二、简单项目使用

1.pom依赖
2.新增插件，自动生成Q版实体类：
3.JPAQueryFactory配置：
4.新建po实体
5.使用 compile编译建立Q版对象
6.简单操作-用法：

6.1. 注入 JPAQueryFactory
6.2. 条件查询
6.3. 模糊查询并排序
6.4. 区间用法（between）
6.5. 分页查询
6.6. in查询
6.7. 查询将用户id，名称，年龄拼接的结果
6.8.聚合函数group by的用法：
6.9. 多条件查询：
6.10. 多表查询：

三、spring data jpa 简单入门笔记

一、 Querydsl面试常识问题

1.Querydsl是什么？

维护HQL查询官方封装类 Spring Data Querydsl 支持JPA，JDO，JDBC，Lucene，Hibernate Search，MongoDB，Collections和RDFBean作为它的后端。

二、简单项目使用

1.pom依赖

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

2.新增插件，自动生成Q版实体类：

<!-- query dsl 构建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>

3.JPAQueryFactory配置：

作用：使用QueryDSL的功能时，会依赖使用到JPAQueryFactory，而JPAQueryFactory在这里依赖使用EntityManager，所以在主类中做如下配置，使得Spring自动帮我们注入EntityManager与自动管理JPAQueryFactory

package com.example.querydsljpademo.config;


import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import javax.persistence.EntityManager;


/**
* 使用QueryDSL的功能时，会依赖使用到JPAQueryFactory，而JPAQueryFactory在这里依赖使用EntityManager，
* 所以在主类中做如下配置，使得Spring自动帮我们注入EntityManager与自动管理JPAQueryFactory
* @author NJ
* @create 2022/12/5 15:12
*/
@Configuration
public class WebMvcConfig implements WebMvcConfigurer {
    
    @Bean
    public JPAQueryFactory jpaQuery(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

4.新建po实体

package com.example.querydsljpademo.entity;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.springframework.format.annotation.DateTimeFormat;


import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;


@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "demo_entity")
@Entity
@Accessors(chain = true)
public class DemoEntity implements Serializable {

    @Id
    @GeneratedValue
    private Integer id;

    private Integer age;

    private Integer userId;

    private String name;

    private Integer height;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date createTime;


}

5.使用 compile编译建立Q版对象

spring jpa Subquery_List

代码地址：https://gitee.com/njgitee/querydsl-jpa-demo.git Querydsl-JPA学习：

6.简单操作-用法：

6.1. 注入 JPAQueryFactory

@Autowired
private JPAQueryFactory jpaQueryFactory;

6.2. 条件查询

@Autowired
private JPAQueryFactory jpaQueryFactory;

/**
* 条件查询
*/
@Test
void contation() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //单表获取年龄为10的结果集
    List<DemoEntity> ageList = jpaQueryFactory.selectFrom(demoEntity).where(demoEntity.age.eq(10)).fetch();
    System.out.println(ageList);
}

6.3. 模糊查询并排序

/**
* 模糊查询并排序
*/
@Test
void like() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //模糊查询并排序
    List<DemoEntity> ageList = jpaQueryFactory.selectFrom(demoEntity).where(demoEntity.name.like("%大%")).orderBy(demoEntity.age.desc()).fetch();
    System.out.println(ageList);
}

6.4. 区间用法（between）

/**
* 区间用法（between）
*/
@SneakyThrows
@Test
void between() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //between 区间的用法：单表获取开始时间是XX-XX 区间的用户
    String time = "2019-07-22 00:00:00";
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Date date= df.parse(time);
    Timestamp nousedate = new Timestamp(date.getTime());


    String time1 = "2019-07-23 00:00:00";
    Date date1= df.parse(time1);
    Timestamp nousedate1 = new Timestamp(date1.getTime());


    List<DemoEntity> beginTimeList = jpaQueryFactory.selectFrom(demoEntity).where(demoEntity.createTime.between(nousedate, nousedate1)).fetch();
    System.out.println("单表获取开始时间是XX-XX 区间的用户的结果集:"+beginTimeList);
}

6.5. 分页查询

/**
* 分页查询
*/
@SneakyThrows
@Test
void limit() {
    Integer pageNo=1;
    Integer pageSize=10;
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    HashMap<String, Object> result = new HashMap<>();
    BooleanExpression booleaBuilder = demoEntity.name.like("%大%");
    long count = jpaQueryFactory.select(demoEntity.count()).from(demoEntity).
            where(booleaBuilder).fetchOne();
    List<DemoEntity> list = jpaQueryFactory.select(demoEntity).
            from(demoEntity).
            where(booleaBuilder).
            orderBy(demoEntity.age.desc()).
            offset((pageNo-1)*pageSize).
            limit(pageSize).
            fetch();
    result.put("content",list);
    result.put("total",count);
    result.put("pageNo",pageNo);
    result.put("pageSize",pageSize);
    System.out.println("单表获取分页的结果集:"+result);
}

6.6. in查询

/**
* in查询
*/
@SneakyThrows
@Test
void in() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //in 的用法：单表获取年龄是10,20的用户
    List<Integer> ageLists = new ArrayList<>();
    ageLists.add(10);
    ageLists.add(20);
    List<DemoEntity> ages = jpaQueryFactory.selectFrom(demoEntity).where(demoEntity.age.in(ageLists)).fetch();
    System.out.println("单表获取年龄是10,20的用户的结果集:"+ages);
}

6.7. 查询将用户id，名称，年龄拼接的结果

/**
* 查询将用户id，名称，年龄拼接的结果
*/
@SneakyThrows
@Test
void concat() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //聚合函数-concat()的使用：单表查询将用户id，名称，年龄拼接的结果
    List<String> concatList = jpaQueryFactory.select(demoEntity.id.stringValue()
            .concat(demoEntity.name).concat(demoEntity.age.stringValue()))
            .from(demoEntity).fetch();
    System.out.println("单表查询将用户id，名称，年龄拼接的结果:"+concatList);
}

6.8.聚合函数group by的用法：

/**
* 聚合函数group by的用法：
*/
@SneakyThrows
@Test
void group() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    List<Map<String,Object>> tupleJPAQuery = jpaQueryFactory.select(demoEntity.age, demoEntity.count().as("count"))
            .from(demoEntity).groupBy(demoEntity.age)
            .fetch().stream().map(x->{
                Map<String,Object> resultMap = new HashMap<>();
                resultMap.put("age",x.get(0,QDemoEntity.class));
                resultMap.put("count",x.get(1,QDemoEntity.class));
                return resultMap;
            }).collect(Collectors.toList());


    System.out.println("单表分组的结果集:"+tupleJPAQuery);
}

6.9. 多条件查询：

/**
* 多条件查询：
*/
@SneakyThrows
@Test
void booleanBuilder() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    //多条件处理
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    booleanBuilder.and(demoEntity.age.eq(10));
    booleanBuilder.and(demoEntity.name.contains("小"));
    List<DemoEntity> mostlist = jpaQueryFactory.selectFrom(demoEntity).where(booleanBuilder).fetch();
    System.out.println("单表查询多条件处理的结果:"+mostlist);
}

6.10. 多表查询：

/**
* 多表查询：
*/
@SneakyThrows
@Test
void leftjoin() {
    QDemoEntity demoEntity = QDemoEntity.demoEntity;
    QUserEntity userEntity = QUserEntity.userEntity;
    List<Tuple> tupleList = jpaQueryFactory.select(demoEntity.age,userEntity.height,userEntity.name)
            .from(demoEntity).leftJoin(userEntity).on(demoEntity.userId.eq(userEntity.id))
            .orderBy(userEntity.age.desc()).fetch();
    System.out.println("tupleList的结果集:" + tupleList);
    List<Map<String, Object>> resultList = tupleList.stream().map(x -> {
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("nameDept", x.get(0, DemoEntity.class));
        resultMap.put("nameUser", x.get(0, UserEntity.class));
        resultMap.put("demo-age", x.get(demoEntity.age));
        resultMap.put("user-height", x.get(userEntity.height));
        resultMap.put("user-name", x.get(userEntity.name));
        return resultMap;
    }).collect(Collectors.toList());
    System.out.println("resultList的结果集:" + resultList);


    //封装一下结果集
    List<DemoDto> list = jpaQueryFactory.select(
            Projections.bean(DemoDto.class,
                    demoEntity.age,userEntity.height,userEntity.name))
            .from(demoEntity).leftJoin(userEntity).on(demoEntity.userId.eq(userEntity.id))
            .orderBy(userEntity.age.desc()).fetch();
    System.out.println("list的结果集:" + list);
}