Querydsl 基本用法

我是个笔记 2020-05-08

1939

回顾之前的文章“新建数据库操作”，我们用到的是jpa + querydsl，也简单的进行了数据库配置和表新建。

今天我们就玩玩jpa、querydsl的基本操作。

以product项目为例，之前新建了product表，再新建一个category表，分别在entity、dao进行代码的基础配置：

entity文件夹，新建下面表后，执行maven 的compile操作，生成QCategory:

@Data
@Entity
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String categoryName;
    private String categoryCode;
}

dao文件夹，CategoryRepository引入jpa, querydsl的接口:

public interface CategoryRepository extends JpaRepository<Category, Integer>, QuerydslPredicateExecutor<Category> {
}

dao文件夹，新建一个类叫CategoryDao，进行category数据库的操作。

引入spring-boot-starter-test 包:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

新建一个单元测试的类，QuerydslTest，进行数据库的操作测试

现在贴完整代码如下：

CategoryDao.java

@Named
public class CategoryDao {
    @Autowired
    private CategoryRepository categoryRepository;
    @Autowired
    private JPAQueryFactory queryFactory;
    public Category addCategory(String categoryName, String categoryCode) {
        Category category = new Category();
        category.setCategoryName(categoryName);
        category.setCategoryCode(categoryCode);
        categoryRepository.save(category);
        return category;
    }
    /**
     * 仅限单表操作
     */
    public List<Category> queryAll() {
        //使用querydsl查询
        QCategory qCategory = QCategory.category;
        List<Category> result = queryFactory.selectFrom(qCategory) //查找源
                .orderBy(qCategory.id.desc()) //id倒序
                .fetch(); //执行
        return result;
    }
    /**
     * querydsl风格
     * @param id
     * @return
     */
    public Category getById(Long id) {
        //使用querydsl查询
        QCategory qCategory = QCategory.category;
        return queryFactory.selectFrom(qCategory) //查找源
                .where(qCategory.id.eq(id))
                .fetchOne();
    }
    /**
     * SpringDataJPA & querydsl风格
     * @param id
     * @return
     */
    public Category getById_2(Long id) {
        //使用querydsl查询
        QCategory qCategory = QCategory.category;
        Optional<Category> result = categoryRepository.findOne(qCategory.id.eq(id));
        return result.get();
    }
    /**
     * 此like的 str 要自行加 %
     * @param str
     * @return
     */
    public List<Category> getByLike(String str) {
        QCategory qCategory = QCategory.category;
        return queryFactory.selectFrom(qCategory).where(qCategory.categoryName.like(str)).fetch();
    }
    public Category updateCategory(Category category) {
        return categoryRepository.save(category);
    }
    public Long updateCategory_2(Category category) {
        QCategory qCategory = QCategory.category;
        return queryFactory.update(qCategory).set(qCategory.categoryName, category.getCategoryName())
                .set(qCategory.categoryCode, category.getCategoryCode())
                .where(qCategory.id.eq(category.getId())).execute();
    }
    public String delCategory(Category category) {
        categoryRepository.delete(category);
        return "success";
    }
    public String delCategory_2(Long id) {
        QCategory qCategory = QCategory.category;
        // where 可以写多个条件
        queryFactory.delete(qCategory).where(qCategory.id.eq(id)).execute();
        return "success";
    }
}

ProductDao.java

@Named
public class ProductDao {
    @Autowired
    private ProductRepository productRepository;
    @Autowired
    private JPAQueryFactory queryFactory;
    public Product addProduct(String name, Double price, Integer stock, String categoryName) {
        Product product = new Product();
        product.setName(name);
        product.setPrice(price);
        product.setStock(stock);
        product.setCategoryName(categoryName);
        productRepository.save(product);
        return product;
    }
    /**
     * 表关联查询,返回表的对象
     */
    public List<Product> getGoodsByCategoryCode(String str) {
        QProduct qProduct = QProduct.product;
        QCategory qCategory = QCategory.category;
        return queryFactory.select(qProduct).from(qProduct, qCategory)
                .where(qProduct.categoryName.eq(qCategory.categoryName)
                        .and(qCategory.categoryCode.eq(str))).fetch();
    }
    /**
     * 返回自定义的vo
     */
    public List<ProductAndCategoryVO> getProductandCategoryVo(String str) {
        QProduct qProduct = QProduct.product;
        QCategory qCategory = QCategory.category;
        return queryFactory.select(Projections.bean(ProductAndCategoryVO.class,
                qProduct.id,
                qProduct.name,
                qProduct.price,
                qProduct.stock,
                qProduct.locked,
                qProduct.categoryName,
                qCategory.categoryCode,
                qCategory.id.as("categoryId")))
                .from(qProduct, qCategory).where(qProduct.categoryName.eq(qCategory.categoryName)
                        .and(qCategory.categoryCode.eq(str))).fetch();
    }
    public Long getProductCount() {
        QProduct qProduct = QProduct.product;
        return queryFactory.select(qProduct.id.count()).from(qProduct).fetchOne();
    }
    public Double getPriceSum() {
        QProduct qProduct = QProduct.product;
        return queryFactory.select(qProduct.price.sum()).from(qProduct).fetchOne();
    }
    public Double getAvgPrice() {
        QProduct qProduct = QProduct.product;
        return queryFactory.select(qProduct.price.avg()).from(qProduct).fetchOne();
    }
    public Double getMaxPrice() {
        QProduct qProduct = QProduct.product;
        return queryFactory.select(qProduct.price.max()).from(qProduct).fetchOne();
    }
    public List<Tuple> groupProducts() {
        QProduct qProduct = QProduct.product;
        return queryFactory.select(qProduct.name.count(), qProduct.categoryName).from(qProduct).groupBy(qProduct.categoryName).fetch();
    }
    /**
     * 子查询
     */
    public List<Product> getProducts() {
        QProduct qProduct = QProduct.product;
        QCategory qCategory = QCategory.category;
        return queryFactory.selectFrom(qProduct).where(qProduct.categoryName.in
                (JPAExpressions.select(qCategory.categoryName).from(qCategory).
                        where(qCategory.categoryCode.eq("red"))
                )
        ).fetch();
    }
    /**
     * 分页
     */
    public List<Product> getLimitProducts(int page, int limit) {
        Pageable pageable = PageRequest.of(page, limit);
        QProduct qProduct = QProduct.product;
        return queryFactory.selectFrom(qProduct).offset(pageable.getOffset()).limit(pageable.getPageSize()).fetch();
    }
}

对于自定义返回的查询，我们新建了一个ProductAndCategoryVO.java类，代码如下：

@Data
public class ProductAndCategoryVO implements Serializable {
    // product的属性
    private Long id;
    private String name;
    private Double price;
    private Integer stock;
    private Integer locked;
    private String categoryName;
    // category属性
    private String categoryCode;
    private Long categoryId;
}

QuerydslTest.java

@RunWith(SpringRunner.class)
@SpringBootTest
public class QuerydslTest {
    @Autowired
    private CategoryDao categoryDao;
    @Autowired
    private ProductDao productDao;
    //    @Test
    public void addCategory() {
        System.out.println(categoryDao.addCategory("翡翠", "jade"));
        System.out.println(categoryDao.addCategory("南红", "red"));
    }
    //    @Test
    public void addProduct() {
        System.out.println(productDao.addProduct("商品1", 199.99, 10, "翡翠"));
        System.out.println(productDao.addProduct("商品2", 299.99, 20, "翡翠"));
        System.out.println(productDao.addProduct("商品3", 399.99, 30, "南红"));
        System.out.println(productDao.addProduct("商品4", 499.99, 40, "南红"));
        System.out.println(productDao.addProduct("商品5", 599.99, 50, "南红"));
    }
    //    @Test
    public void queryAll() {
        //使用querydsl查询
        List<Category> result = categoryDao.queryAll();
        if (!result.isEmpty()) {
            for (Category category : result) {
                System.out.println(category);
            }
        }
    }
    //    @Test
    public void getById() {
        System.out.println(categoryDao.getById(1l));
        System.out.println(categoryDao.getById_2(1l));
    }
    //    @Test
    public void getByLike() {
        List<Category> result = categoryDao.getByLike("翡%");
        if (!result.isEmpty()) {
            for (Category category : result) {
                System.out.println(category);
            }
        }
    }
    //    @Test
    public void updateCategory() {
        Category category = categoryDao.getById(1l);
        System.out.println(category);
        category.setCategoryName("翡翠");
        System.out.println(categoryDao.updateCategory(category));
    }
    //    @Test
    public void getProductByCategoryCode() {
        List<Product> result = productDao.getGoodsByCategoryCode("jade");
        if (!result.isEmpty()) {
            for (Product product : result) {
                System.out.println(product);
            }
        }
    }
    //    @Test
    public void getProductAndCategoryCode() {
        List<ProductAndCategoryVO> result = productDao.getProductandCategoryVo("jade");
        if (!result.isEmpty()) {
            for (ProductAndCategoryVO product : result) {
                System.out.println(product);
            }
        }
    }
    //    @Test
    public void testGroup() {
        System.out.println(productDao.getProductCount());
        System.out.println(productDao.getAvgPrice());
        System.out.println(productDao.getPriceSum());
        System.out.println(productDao.getMaxPrice());
        List<Tuple> result = productDao.groupProducts();
        if (!result.isEmpty()) {
            for (Tuple product : result) {
                System.out.println(product);
            }
        }
    }
    @Test
    public void getProducts() {
        List<Product> result = productDao.getProducts();
        if (!result.isEmpty()) {
            for (Product product : result) {
                System.out.println(product);
            }
        }
    }
    @Test
    public void getLimitedProducts() {
        List<Product> result = productDao.getLimitProducts(2, 2);
        if (!result.isEmpty()) {
            for (Product product : result) {
                System.out.println(product);
            }
        }
    }
}

最后运行QuerydslTest看单元测试的效果。

这次新加的文件比较多，整体文件结构如下：

数据库

文章转载自我是个笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Querydsl 基本用法

评论