Querydsl 必学格式

<!--query dsl -->
 
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
</dependency>
 
 
<build>
    <plugins>
        <!--根据@Entity注解生成 对应 target 里面的entity对象
        QVoucher qVoucher = QVoucher.voucher;
        Long count =  queryFactory.delete(qVoucher).where(qVoucher.voucherId.eq(voucherId)).execute();-->
        <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>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

@SpringBootApplication
public class DemoApplication extends SpringBootServletInitializer {
//EM
    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(DemoApplication.class);
    }
 
 
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
 
    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
 
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setDataSource(dataSource);
        return transactionManager;
    }
 
}

# 子查询
 
QDepartment department = QDepartment.department;
QDepartment d = new QDepartment("d");
queryFactory.selectFrom(department)
    .where(department.size.eq(
        JPAExpressions.select(d.size.max()).from(d)))
     .fetch();
 
 
QEmployee employee = QEmployee.employee;
QEmployee e = new QEmployee("e");
queryFactory.selectFrom(employee)
    .where(employee.weeklyhours.gt(
        JPAExpressions.select(e.weeklyhours.avg())
            .from(employee.department.employees, e)
            .where(e.manager.eq(employee.manager))))
    .fetch();
 
 
 
# 更新修改
 
QCustomer customer = QCustomer.customer;
queryFactory.update(customer)
    .set(customer.name, "Bobby")
    .where(customer.name.eq("Bob"))
    .execute();
 
queryFactory.update(customer)
    .setNull(customer.name)
    .where(customer.name.eq("Bob"))
    .execute();
 
 
 
# 删除
 
QCustomer customer = QCustomer.customer;
queryFactory.delete(customer).execute();
queryFactory.delete(customer).where(customer.level.lt(3)).execute();
 
 
 
# 返回为 Tuple 类型的对象取值
 
# 1.
public List<Tuple> getOrdersListOnlinePaymentUnmatch() {
        QOrders qOrders = QOrders.orders;
        return hibernateQueryFactory
                .select(qOrders.ordersSn, qOrders.ordersAmount, qOrders.storeDiscountAmount, qOrders.couponAmount, qOrders.paymentTime)
                .from(qOrders)
                .fetch();
    }
 
# 2.
List<Tuple> orders = ordersDao.getOrdersListOnlinePaymentUnmatch();
 
# 3.获取第1个参数
orders.get(i).get(0, Long.class)
 
DTO
 
query = new JPASQLQuery<Void>(entityManager, templates);
List<CatDTO> catDTOs = query.select(Projections.constructor(CatDTO.class, cat.id, cat.name))
    .from(cat)
    .orderBy(cat.name.asc())
    .fetch();
 
 
public ServiceResponse<Object> selectCollection() {
        User user = ShiroKit.getUser();
        QProduct qProduct = QProduct.product;
        QCollection qCollection = QCollection.collection;
        List<ProductCollectionVo> list = jpaQueryFactory
                .select(Projections.bean(ProductCollectionVo.class,
                        qProduct.id,
                        qProduct.productName,
                        qProduct.productType,
                        qProduct.productDetails,
                        qProduct.produceBrand,
                        qProduct.produceMoney,
                        qProduct.produceBigimg,
                        qProduct.produceSmallimg,
                        qProduct.produceDetailsimg,
                        qCollection.id.as("collectionId")))
                .from(qProduct)
                .rightJoin(qCollection).on(qProduct.id.eq(qCollection.productId))
                .where(qCollection.userId.eq(user.getId())).fetch();
        return ServiceResponse.createBySuccess(list);
 
 
# 动态条件拼接查询
public void test() throws IOException {
        QCeshi qCeshi = QCeshi.ceshi;
        Predicate predicate = null;
        predicate = ExpressionUtils.and(predicate, qCeshi.name.eq("张三"));
        predicate = ExpressionUtils.and(predicate, qCeshi.id.eq(3));
        JPAQuery<Ceshi> where = jpaQueryFactory.selectFrom(qCeshi).where(predicate);
 
}
# 执行语句为
select ceshi from Ceshi ceshi where ceshi.name = ?1 and ceshi.id = ?2
 
 
 
NE : not equal 不等于 .ne
GE : greater than or equal 大于等于 .goe
LE : less than or equal 小于等于 .loe
GT : greater than 大于 .gt
LT : less than  小于 .lt
EQ : equal 等于 .eq