Querydsl is a framework that enables the construction of type-safe SQL-like queries in Java. This guide covers setup, basic usage, advanced features, and integration with Spring Data.
1. Setup and Configuration
Maven Dependencies
Basic Dependencies (pom.xml)
<properties> <querydsl.version>5.0.0</querydsl.version> </properties> <dependencies> <!-- Querydsl Core --> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-core</artifactId> <version>${querydsl.version}</version> </dependency> <!-- Querydsl JPA --> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-jpa</artifactId> <version>${querydsl.version}</version> </dependency> <!-- Querydsl APT for code generation --> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-apt</artifactId> <version>${querydsl.version}</version> <scope>provided</scope> </dependency> <!-- Spring Data JPA --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> </dependencies> <build> <plugins> <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> Spring Boot Configuration
Application Properties
spring: jpa: hibernate: ddl-auto: validate show-sql: true properties: hibernate: dialect: org.hibernate.dialect.PostgreSQLDialect datasource: url: jdbc:postgresql://localhost:5432/querydsl_demo username: postgres password: password
Configuration Class
@Configuration public class QuerydslConfig { @PersistenceContext private EntityManager entityManager; @Bean public JPAQueryFactory jpaQueryFactory() { return new JPAQueryFactory(entityManager); } } 2. Entity Models and Q-classes
JPA Entity Models
User Entity
@Entity @Table(name = "users") public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "username", nullable = false, unique = true) private String username; @Column(name = "email", nullable = false, unique = true) private String email; @Column(name = "first_name", nullable = false) private String firstName; @Column(name = "last_name", nullable = false) private String lastName; @Column(name = "age") private Integer age; @Enumerated(EnumType.STRING) @Column(name = "status", nullable = false) private UserStatus status; @Column(name = "created_at", nullable = false) private LocalDateTime createdAt; @Column(name = "updated_at") private LocalDateTime updatedAt; @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY) private List<Order> orders = new ArrayList<>(); @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY) private List<Address> addresses = new ArrayList<>(); // Constructors public User() { this.createdAt = LocalDateTime.now(); this.status = UserStatus.ACTIVE; } public User(String username, String email, String firstName, String lastName, Integer age) { this(); this.username = username; this.email = email; this.firstName = firstName; this.lastName = lastName; this.age = age; } // Business methods public void deactivate() { this.status = UserStatus.INACTIVE; this.updatedAt = LocalDateTime.now(); } public void activate() { this.status = UserStatus.ACTIVE; this.updatedAt = LocalDateTime.now(); } public String getFullName() { return firstName + " " + lastName; } // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public UserStatus getStatus() { return status; } public void setStatus(UserStatus status) { this.status = status; } public LocalDateTime getCreatedAt() { return createdAt; } public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; } public LocalDateTime getUpdatedAt() { return updatedAt; } public void setUpdatedAt(LocalDateTime updatedAt) { this.updatedAt = updatedAt; } public List<Order> getOrders() { return orders; } public void setOrders(List<Order> orders) { this.orders = orders; } public List<Address> getAddresses() { return addresses; } public void setAddresses(List<Address> addresses) { this.addresses = addresses; } } enum UserStatus { ACTIVE, INACTIVE, SUSPENDED, DELETED } Order Entity
@Entity @Table(name = "orders") public class Order { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "order_number", nullable = false, unique = true) private String orderNumber; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "user_id", nullable = false) private User user; @Column(name = "total_amount", nullable = false) private BigDecimal totalAmount; @Enumerated(EnumType.STRING) @Column(name = "status", nullable = false) private OrderStatus status; @Column(name = "order_date", nullable = false) private LocalDateTime orderDate; @Column(name = "shipped_date") private LocalDateTime shippedDate; @Column(name = "delivered_date") private LocalDateTime deliveredDate; @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY) private List<OrderItem> items = new ArrayList<>(); // Constructors public Order() { this.orderNumber = generateOrderNumber(); this.orderDate = LocalDateTime.now(); this.status = OrderStatus.PENDING; } public Order(User user, BigDecimal totalAmount) { this(); this.user = user; this.totalAmount = totalAmount; } private String generateOrderNumber() { return "ORD-" + System.currentTimeMillis() + "-" + UUID.randomUUID().toString().substring(0, 8); } // Business methods public void addItem(OrderItem item) { items.add(item); item.setOrder(this); } public void removeItem(OrderItem item) { items.remove(item); item.setOrder(null); } public void confirm() { this.status = OrderStatus.CONFIRMED; } public void ship() { this.status = OrderStatus.SHIPPED; this.shippedDate = LocalDateTime.now(); } public void deliver() { this.status = OrderStatus.DELIVERED; this.deliveredDate = LocalDateTime.now(); } public void cancel() { this.status = OrderStatus.CANCELLED; } // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getOrderNumber() { return orderNumber; } public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public BigDecimal getTotalAmount() { return totalAmount; } public void setTotalAmount(BigDecimal totalAmount) { this.totalAmount = totalAmount; } public OrderStatus getStatus() { return status; } public void setStatus(OrderStatus status) { this.status = status; } public LocalDateTime getOrderDate() { return orderDate; } public void setOrderDate(LocalDateTime orderDate) { this.orderDate = orderDate; } public LocalDateTime getShippedDate() { return shippedDate; } public void setShippedDate(LocalDateTime shippedDate) { this.shippedDate = shippedDate; } public LocalDateTime getDeliveredDate() { return deliveredDate; } public void setDeliveredDate(LocalDateTime deliveredDate) { this.deliveredDate = deliveredDate; } public List<OrderItem> getItems() { return items; } public void setItems(List<OrderItem> items) { this.items = items; } } enum OrderStatus { PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED, REFUNDED } OrderItem Entity
@Entity @Table(name = "order_items") public class OrderItem { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "order_id", nullable = false) private Order order; @Column(name = "product_id", nullable = false) private Long productId; @Column(name = "product_name", nullable = false) private String productName; @Column(name = "unit_price", nullable = false) private BigDecimal unitPrice; @Column(name = "quantity", nullable = false) private Integer quantity; @Column(name = "total_price", nullable = false) private BigDecimal totalPrice; // Constructors public OrderItem() {} public OrderItem(Order order, Long productId, String productName, BigDecimal unitPrice, Integer quantity) { this.order = order; this.productId = productId; this.productName = productName; this.unitPrice = unitPrice; this.quantity = quantity; this.totalPrice = unitPrice.multiply(BigDecimal.valueOf(quantity)); } // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Order getOrder() { return order; } public void setOrder(Order order) { this.order = order; } public Long getProductId() { return productId; } public void setProductId(Long productId) { this.productId = productId; } public String getProductName() { return productName; } public void setProductName(String productName) { this.productName = productName; } public BigDecimal getUnitPrice() { return unitPrice; } public void setUnitPrice(BigDecimal unitPrice) { this.unitPrice = unitPrice; } public Integer getQuantity() { return quantity; } public void setQuantity(Integer quantity) { this.quantity = quantity; } public BigDecimal getTotalPrice() { return totalPrice; } public void setTotalPrice(BigDecimal totalPrice) { this.totalPrice = totalPrice; } } Address Entity
@Entity @Table(name = "addresses") public class Address { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "user_id", nullable = false) private User user; @Column(name = "street", nullable = false) private String street; @Column(name = "city", nullable = false) private String city; @Column(name = "state", nullable = false) private String state; @Column(name = "zip_code", nullable = false) private String zipCode; @Column(name = "country", nullable = false) private String country; @Enumerated(EnumType.STRING) @Column(name = "address_type", nullable = false) private AddressType addressType; @Column(name = "is_primary") private Boolean isPrimary = false; // Constructors public Address() {} public Address(User user, String street, String city, String state, String zipCode, String country, AddressType addressType) { this.user = user; this.street = street; this.city = city; this.state = state; this.zipCode = zipCode; this.country = country; this.addressType = addressType; } // Getters and setters public Long getId() { return id; } public void setId(Long id) { this.id = id; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public String getStreet() { return street; } public void setStreet(String street) { this.street = street; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZipCode() { return zipCode; } public void setZipCode(String zipCode) { this.zipCode = zipCode; } public String getCountry() { return country; } public void setCountry(String country) { this.country = country; } public AddressType getAddressType() { return addressType; } public void setAddressType(AddressType addressType) { this.addressType = addressType; } public Boolean getIsPrimary() { return isPrimary; } public void setIsPrimary(Boolean isPrimary) { this.isPrimary = isPrimary; } } enum AddressType { HOME, WORK, BILLING, SHIPPING } Generated Q-classes
After building the project, Querydsl generates Q-classes in target/generated-sources/java:
// QUser.java (generated) @Generated("com.querydsl.codegen.EntitySerializer") public class QUser extends EntityPathBase<User> { private static final long serialVersionUID = 123456789L; public static final QUser user = new QUser("user"); public final NumberPath<Long> id = createNumber("id", Long.class); public final StringPath username = createString("username"); public final StringPath email = createString("email"); public final StringPath firstName = createString("firstName"); public final StringPath lastName = createString("lastName"); public final NumberPath<Integer> age = createNumber("age", Integer.class); public final EnumPath<UserStatus> status = createEnum("status", UserStatus.class); public final DateTimePath<LocalDateTime> createdAt = createDateTime("createdAt", LocalDateTime.class); public final DateTimePath<LocalDateTime> updatedAt = createDateTime("updatedAt", LocalDateTime.class); public final ListPath<Order, QOrder> orders = this.<Order, QOrder>createList("orders", Order.class, QOrder.class, PathInits.DIRECT2); public final ListPath<Address, QAddress> addresses = this.<Address, QAddress>createList("addresses", Address.class, QAddress.class, PathInits.DIRECT2); public QUser(String variable) { super(User.class, forVariable(variable)); } public QUser(Path<? extends User> path) { super(path.getType(), path.getMetadata()); } public QUser(PathMetadata metadata) { super(User.class, metadata); } } 3. Basic Querydsl Usage
Basic Repository with Querydsl
@Repository @Transactional(readOnly = true) public class UserQuerydslRepository { private final JPAQueryFactory queryFactory; // Q-class instances private final QUser user = QUser.user; private final QOrder order = QOrder.order; private final QAddress address = QAddress.address; public UserQuerydslRepository(JPAQueryFactory queryFactory) { this.queryFactory = queryFactory; } // Basic CRUD operations public Optional<User> findById(Long id) { return Optional.ofNullable( queryFactory.selectFrom(user) .where(user.id.eq(id)) .fetchOne() ); } public List<User> findAll() { return queryFactory.selectFrom(user) .fetch(); } @Transactional public User save(User userEntity) { // In real implementation, use EntityManager // This is just for demonstration return queryFactory.insert(user) .populate(userEntity) .execute(); } @Transactional public long deleteById(Long id) { return queryFactory.delete(user) .where(user.id.eq(id)) .execute(); } // Simple WHERE queries public Optional<User> findByUsername(String username) { return Optional.ofNullable( queryFactory.selectFrom(user) .where(user.username.eq(username)) .fetchOne() ); } public Optional<User> findByEmail(String email) { return Optional.ofNullable( queryFactory.selectFrom(user) .where(user.email.eq(email)) .fetchOne() ); } public List<User> findByStatus(UserStatus status) { return queryFactory.selectFrom(user) .where(user.status.eq(status)) .fetch(); } public List<User> findByAgeBetween(Integer minAge, Integer maxAge) { return queryFactory.selectFrom(user) .where(user.age.between(minAge, maxAge)) .fetch(); } // LIKE queries public List<User> findByFirstNameLike(String firstNamePattern) { return queryFactory.selectFrom(user) .where(user.firstName.like(firstNamePattern + "%")) .fetch(); } public List<User> findByLastNameContaining(String lastNamePart) { return queryFactory.selectFrom(user) .where(user.lastName.containsIgnoreCase(lastNamePart)) .fetch(); } // IN queries public List<User> findByIds(List<Long> ids) { return queryFactory.selectFrom(user) .where(user.id.in(ids)) .fetch(); } public List<User> findByStatusIn(List<UserStatus> statuses) { return queryFactory.selectFrom(user) .where(user.status.in(statuses)) .fetch(); } // NULL checks public List<User> findUsersWithNoAge() { return queryFactory.selectFrom(user) .where(user.age.isNull()) .fetch(); } public List<User> findUsersWithAge() { return queryFactory.selectFrom(user) .where(user.age.isNotNull()) .fetch(); } // Boolean operations public List<User> findActiveAdultUsers() { return queryFactory.selectFrom(user) .where(user.status.eq(UserStatus.ACTIVE) .and(user.age.goe(18))) .fetch(); } public List<User> findInactiveOrSuspendedUsers() { return queryFactory.selectFrom(user) .where(user.status.eq(UserStatus.INACTIVE) .or(user.status.eq(UserStatus.SUSPENDED))) .fetch(); } // Ordering and limiting public List<User> findTop10ByOrderByCreatedAtDesc() { return queryFactory.selectFrom(user) .orderBy(user.createdAt.desc()) .limit(10) .fetch(); } public List<User> findUsersOrderByAgeDescFirstNameAsc() { return queryFactory.selectFrom(user) .orderBy(user.age.desc(), user.firstName.asc()) .fetch(); } // Pagination public List<User> findUsersWithPagination(int offset, int limit) { return queryFactory.selectFrom(user) .offset(offset) .limit(limit) .fetch(); } public Page<User> findUsersPage(Pageable pageable) { List<User> content = queryFactory.selectFrom(user) .offset(pageable.getOffset()) .limit(pageable.getPageSize()) .orderBy(getOrderSpecifiers(pageable.getSort())) .fetch(); Long total = queryFactory.select(user.count()) .from(user) .fetchOne(); return new PageImpl<>(content, pageable, total != null ? total : 0L); } private OrderSpecifier<?>[] getOrderSpecifiers(Sort sort) { return sort.stream() .map(order -> { Order direction = order.isAscending() ? Order.ASC : Order.DESC; switch (order.getProperty()) { case "username": return new OrderSpecifier<>(direction, user.username); case "email": return new OrderSpecifier<>(direction, user.email); case "firstName": return new OrderSpecifier<>(direction, user.firstName); case "lastName": return new OrderSpecifier<>(direction, user.lastName); case "age": return new OrderSpecifier<>(direction, user.age); case "createdAt": return new OrderSpecifier<>(direction, user.createdAt); default: return new OrderSpecifier<>(direction, user.id); } }) .toArray(OrderSpecifier[]::new); } // Count queries public long countByStatus(UserStatus status) { return queryFactory.select(user.count()) .from(user) .where(user.status.eq(status)) .fetchOne(); } public long countActiveUsers() { return queryFactory.select(user.count()) .from(user) .where(user.status.eq(UserStatus.ACTIVE)) .fetchOne(); } // Exists queries public boolean existsByUsername(String username) { return queryFactory.selectOne() .from(user) .where(user.username.eq(username)) .fetchFirst() != null; } public boolean existsByEmail(String email) { return queryFactory.selectOne() .from(user) .where(user.email.eq(email)) .fetchFirst() != null; } // Distinct queries public List<String> findDistinctCities() { return queryFactory.select(address.city) .from(address) .distinct() .fetch(); } // Projections (select specific fields) public List<UserProjection> findUserProjections() { return queryFactory.select( Projections.bean(UserProjection.class, user.id, user.username, user.email, user.firstName, user.lastName, user.status ) ) .from(user) .fetch(); } public List<String> findUsernamesByStatus(UserStatus status) { return queryFactory.select(user.username) .from(user) .where(user.status.eq(status)) .fetch(); } // Tuple results public List<Tuple> findUserStats() { return queryFactory.select( user.status, user.count(), user.age.avg(), user.age.max(), user.age.min() ) .from(user) .groupBy(user.status) .fetch(); } } 4. Advanced Querydsl Features
Complex Joins and Relationships
@Repository @Transactional(readOnly = true) public class OrderQuerydslRepository { private final JPAQueryFactory queryFactory; private final QOrder order = QOrder.order; private final QUser user = QUser.user; private final QOrderItem orderItem = QOrderItem.orderItem; private final QAddress address = QAddress.address; public OrderQuerydslRepository(JPAQueryFactory queryFactory) { this.queryFactory = queryFactory; } // Basic JOIN operations public List<Order> findOrdersWithUser() { return queryFactory.selectFrom(order) .innerJoin(order.user, user) .fetchJoin() // Eagerly fetch user .fetch(); } public List<Order> findOrdersByUsername(String username) { return queryFactory.selectFrom(order) .innerJoin(order.user, user) .where(user.username.eq(username)) .fetch(); } public List<Order> findOrdersByUserEmail(String email) { return queryFactory.selectFrom(order) .innerJoin(order.user, user) .where(user.email.eq(email)) .fetch(); } // Multiple JOINs public List<Order> findOrdersWithUserAndItems() { return queryFactory.selectFrom(order) .innerJoin(order.user, user).fetchJoin() .leftJoin(order.items, orderItem).fetchJoin() .fetch(); } // JOIN with conditions public List<Order> findHighValueOrders(BigDecimal minAmount) { return queryFactory.selectFrom(order) .innerJoin(order.user, user) .where(order.totalAmount.gt(minAmount) .and(user.status.eq(UserStatus.ACTIVE))) .fetch(); } // EXISTS subquery public List<User> findUsersWithOrders() { return queryFactory.selectFrom(user) .where(JPAExpressions.selectOne() .from(order) .where(order.user.eq(user)) .exists()) .fetch(); } public List<User> findUsersWithoutOrders() { return queryFactory.selectFrom(user) .where(JPAExpressions.selectOne() .from(order) .where(order.user.eq(user)) .notExists()) .fetch(); } // IN subquery public List<Order> findOrdersFromActiveUsers() { return queryFactory.selectFrom(order) .where(order.user.in( JPAExpressions.selectFrom(user) .where(user.status.eq(UserStatus.ACTIVE)) )) .fetch(); } // Complex WHERE with subqueries public List<User> findUsersWithHighValueOrders(BigDecimal minOrderValue) { return queryFactory.selectFrom(user) .where(JPAExpressions.select(order.totalAmount.max()) .from(order) .where(order.user.eq(user)) .gt(minOrderValue)) .fetch(); } // Aggregation with GROUP BY public List<UserOrderStats> getUserOrderStatistics() { return queryFactory.select( Projections.bean(UserOrderStats.class, user.id.as("userId"), user.username, user.email, order.count().as("orderCount"), order.totalAmount.sum().as("totalSpent"), order.totalAmount.avg().as("averageOrderValue"), order.totalAmount.max().as("maxOrderValue") ) ) .from(order) .innerJoin(order.user, user) .groupBy(user.id, user.username, user.email) .having(order.count().gt(0L)) .orderBy(order.totalAmount.sum().desc()) .fetch(); } // Date range queries public List<Order> findOrdersBetweenDates(LocalDateTime startDate, LocalDateTime endDate) { return queryFactory.selectFrom(order) .where(order.orderDate.between(startDate, endDate)) .fetch(); } public List<Order> findRecentOrders(int days) { LocalDateTime cutoffDate = LocalDateTime.now().minusDays(days); return queryFactory.selectFrom(order) .where(order.orderDate.after(cutoffDate)) .fetch(); } // CASE expressions public List<UserOrderCategory> categorizeUsersByOrderValue() { NumberExpression<BigDecimal> totalSpent = order.totalAmount.sum(); StringExpression category = Expressions.stringTemplate( "case " + "when {0} > 1000 then 'VIP' " + "when {0} > 500 then 'Premium' " + "when {0} > 100 then 'Standard' " + "else 'Basic' end", totalSpent ); return queryFactory.select( Projections.bean(UserOrderCategory.class, user.id.as("userId"), user.username, totalSpent.as("totalSpent"), category.as("category") ) ) .from(order) .innerJoin(order.user, user) .groupBy(user.id, user.username) .fetch(); } // UNION queries (using BooleanBuilder for complex conditions) public List<Order> findOrdersByComplexCriteria(OrderSearchCriteria criteria) { BooleanBuilder predicate = new BooleanBuilder(); if (criteria.getUserId() != null) { predicate.and(order.user.id.eq(criteria.getUserId())); } if (criteria.getStatus() != null) { predicate.and(order.status.eq(criteria.getStatus())); } if (criteria.getMinAmount() != null) { predicate.and(order.totalAmount.goe(criteria.getMinAmount())); } if (criteria.getMaxAmount() != null) { predicate.and(order.totalAmount.loe(criteria.getMaxAmount())); } if (criteria.getStartDate() != null) { predicate.and(order.orderDate.goe(criteria.getStartDate())); } if (criteria.getEndDate() != null) { predicate.and(order.orderDate.loe(criteria.getEndDate())); } return queryFactory.selectFrom(order) .where(predicate) .orderBy(order.orderDate.desc()) .fetch(); } // Dynamic sorting public List<Order> findOrdersWithDynamicSorting(String sortBy, String direction) { OrderSpecifier<?> orderSpecifier; switch (sortBy) { case "amount": orderSpecifier = direction.equalsIgnoreCase("desc") ? order.totalAmount.desc() : order.totalAmount.asc(); break; case "date": orderSpecifier = direction.equalsIgnoreCase("desc") ? order.orderDate.desc() : order.orderDate.asc(); break; case "status": orderSpecifier = direction.equalsIgnoreCase("desc") ? order.status.desc() : order.status.asc(); break; default: orderSpecifier = order.id.asc(); } return queryFactory.selectFrom(order) .orderBy(orderSpecifier) .fetch(); } } Projection Classes
// Projection classes for type-safe results public class UserProjection { private Long id; private String username; private String email; private String firstName; private String lastName; private UserStatus status; // Constructors, getters, and setters public UserProjection() {} public UserProjection(Long id, String username, String email, String firstName, String lastName, UserStatus status) { this.id = id; this.username = username; this.email = email; this.firstName = firstName; this.lastName = lastName; this.status = status; } // Getters and setters... } public class UserOrderStats { private Long userId; private String username; private String email; private Long orderCount; private BigDecimal totalSpent; private BigDecimal averageOrderValue; private BigDecimal maxOrderValue; // Constructors, getters, and setters... } public class UserOrderCategory { private Long userId; private String username; private BigDecimal totalSpent; private String category; // Constructors, getters, and setters... } public class OrderSearchCriteria { private Long userId; private OrderStatus status; private BigDecimal minAmount; private BigDecimal maxAmount; private LocalDateTime startDate; private LocalDateTime endDate; // Constructors, getters, and setters... } 5. Integration with Spring Data JPA
Custom Repository with Querydsl
// Custom repository interface public interface CustomUserRepository { List<User> findActiveUsersWithOrders(); List<User> searchUsers(UserSearchCriteria criteria); Page<User> findUsersWithPagination(UserSearchCriteria criteria, Pageable pageable); List<UserOrderStats> getUserOrderStatistics(); long updateUserStatus(List<Long> userIds, UserStatus status); } // Implementation public class CustomUserRepositoryImpl implements CustomUserRepository { private final JPAQueryFactory queryFactory; private final EntityManager entityManager; private final QUser user = QUser.user; private final QOrder order = QOrder.order; public CustomUserRepositoryImpl(JPAQueryFactory queryFactory, EntityManager entityManager) { this.queryFactory = queryFactory; this.entityManager = entityManager; } @Override public List<User> findActiveUsersWithOrders() { return queryFactory.selectFrom(user) .innerJoin(user.orders, order).fetchJoin() .where(user.status.eq(UserStatus.ACTIVE)) .distinct() .fetch(); } @Override public List<User> searchUsers(UserSearchCriteria criteria) { BooleanBuilder predicate = buildSearchPredicate(criteria); return queryFactory.selectFrom(user) .where(predicate) .orderBy(user.createdAt.desc()) .fetch(); } @Override public Page<User> findUsersWithPagination(UserSearchCriteria criteria, Pageable pageable) { BooleanBuilder predicate = buildSearchPredicate(criteria); List<User> content = queryFactory.selectFrom(user) .where(predicate) .orderBy(getOrderSpecifiers(pageable.getSort())) .offset(pageable.getOffset()) .limit(pageable.getPageSize()) .fetch(); Long total = queryFactory.select(user.count()) .from(user) .where(predicate) .fetchOne(); return new PageImpl<>(content, pageable, total != null ? total : 0L); } @Override public List<UserOrderStats> getUserOrderStatistics() { return queryFactory.select( Projections.constructor(UserOrderStats.class, user.id, user.username, user.email, order.count(), order.totalAmount.sum(), order.totalAmount.avg(), order.totalAmount.max() ) ) .from(user) .leftJoin(user.orders, order) .groupBy(user.id, user.username, user.email) .fetch(); } @Override @Transactional public long updateUserStatus(List<Long> userIds, UserStatus status) { return queryFactory.update(user) .set(user.status, status) .set(user.updatedAt, LocalDateTime.now()) .where(user.id.in(userIds)) .execute(); } private BooleanBuilder buildSearchPredicate(UserSearchCriteria criteria) { BooleanBuilder predicate = new BooleanBuilder(); if (StringUtils.hasText(criteria.getSearchTerm())) { BooleanBuilder searchPredicate = new BooleanBuilder(); searchPredicate.or(user.username.containsIgnoreCase(criteria.getSearchTerm())); searchPredicate.or(user.email.containsIgnoreCase(criteria.getSearchTerm())); searchPredicate.or(user.firstName.containsIgnoreCase(criteria.getSearchTerm())); searchPredicate.or(user.lastName.containsIgnoreCase(criteria.getSearchTerm())); predicate.and(searchPredicate); } if (criteria.getStatus() != null) { predicate.and(user.status.eq(criteria.getStatus())); } if (criteria.getMinAge() != null) { predicate.and(user.age.goe(criteria.getMinAge())); } if (criteria.getMaxAge() != null) { predicate.and(user.age.loe(criteria.getMaxAge())); } if (criteria.getHasOrders() != null) { if (criteria.getHasOrders()) { predicate.and(JPAExpressions.selectOne() .from(order) .where(order.user.eq(user)) .exists()); } else { predicate.and(JPAExpressions.selectOne() .from(order) .where(order.user.eq(user)) .notExists()); } } return predicate; } private OrderSpecifier<?>[] getOrderSpecifiers(Sort sort) { if (sort == null || sort.isUnsorted()) { return new OrderSpecifier[]{user.id.asc()}; } return sort.stream() .map(order -> { Order direction = order.isAscending() ? Order.ASC : Order.DESC; switch (order.getProperty()) { case "username": return new OrderSpecifier<>(direction, user.username); case "email": return new OrderSpecifier<>(direction, user.email); case "firstName": return new OrderSpecifier<>(direction, user.firstName); case "lastName": return new OrderSpecifier<>(direction, user.lastName); case "age": return new OrderSpecifier<>(direction, user.age); case "createdAt": return new OrderSpecifier<>(direction, user.createdAt); default: return new OrderSpecifier<>(direction, user.id); } }) .toArray(OrderSpecifier[]::new); } } // Extended JPA repository public interface UserRepository extends JpaRepository<User, Long>, CustomUserRepository { // Spring Data JPA methods Optional<User> findByUsername(String username); Optional<User> findByEmail(String email); List<User> findByStatus(UserStatus status); List<User> findByAgeBetween(Integer minAge, Integer maxAge); boolean existsByUsername(String username); boolean existsByEmail(String email); } // Search criteria public class UserSearchCriteria { private String searchTerm; private UserStatus status; private Integer minAge; private Integer maxAge; private Boolean hasOrders; // Constructors, getters, and setters... } 6. Service Layer
Service Implementation
@Service @Transactional public class UserService { private final UserRepository userRepository; private final UserQuerydslRepository userQuerydslRepository; public UserService(UserRepository userRepository, UserQuerydslRepository userQuerydslRepository) { this.userRepository = userRepository; this.userQuerydslRepository = userQuerydslRepository; } // Basic CRUD operations public User createUser(CreateUserRequest request) { if (userRepository.existsByUsername(request.getUsername())) { throw new UserAlreadyExistsException("Username already exists: " + request.getUsername()); } if (userRepository.existsByEmail(request.getEmail())) { throw new UserAlreadyExistsException("Email already exists: " + request.getEmail()); } User user = new User( request.getUsername(), request.getEmail(), request.getFirstName(), request.getLastName(), request.getAge() ); return userRepository.save(user); } public User getUserById(Long id) { return userRepository.findById(id) .orElseThrow(() -> new UserNotFoundException("User not found with id: " + id)); } public User getUserByUsername(String username) { return userRepository.findByUsername(username) .orElseThrow(() -> new UserNotFoundException("User not found with username: " + username)); } public List<User> getUsersByStatus(UserStatus status) { return userRepository.findByStatus(status); } public List<User> searchUsers(UserSearchCriteria criteria) { return userRepository.searchUsers(criteria); } public Page<User> searchUsersWithPagination(UserSearchCriteria criteria, Pageable pageable) { return userRepository.findUsersWithPagination(criteria, pageable); } public User updateUser(Long userId, UpdateUserRequest request) { User user = getUserById(userId); if (StringUtils.hasText(request.getFirstName())) { user.setFirstName(request.getFirstName()); } if (StringUtils.hasText(request.getLastName())) { user.setLastName(request.getLastName()); } if (request.getAge() != null) { user.setAge(request.getAge()); } return userRepository.save(user); } public void deactivateUser(Long userId) { userRepository.updateUserStatus(List.of(userId), UserStatus.INACTIVE); } public void activateUser(Long userId) { userRepository.updateUserStatus(List.of(userId), UserStatus.ACTIVE); } public void bulkUpdateUserStatus(List<Long> userIds, UserStatus status) { userRepository.updateUserStatus(userIds, status); } public void deleteUser(Long userId) { if (!userRepository.existsById(userId)) { throw new UserNotFoundException("User not found with id: " + userId); } userRepository.deleteById(userId); } // Advanced queries using Querydsl directly public List<UserOrderStats> getUserOrderStatistics() { return userRepository.getUserOrderStatistics(); } public List<User> findActiveUsersWithOrders() { return userRepository.findActiveUsersWithOrders(); } public List<User> findTopSpenders(int limit) { return userQuerydslRepository.findTopSpenders(limit); } public Map<UserStatus, Long> getUserCountByStatus() { List<Tuple> stats = userQuerydslRepository.getUserStats(); return stats.stream() .collect(Collectors.toMap( tuple -> tuple.get(user.status), tuple -> tuple.get(user.count()) )); } } @Service @Transactional public class OrderService { private final OrderQuerydslRepository orderQuerydslRepository; private final UserRepository userRepository; public OrderService(OrderQuerydslRepository orderQuerydslRepository, UserRepository userRepository) { this.orderQuerydslRepository = orderQuerydslRepository; this.userRepository = userRepository; } public List<Order> getOrdersByUser(Long userId) { User user = userRepository.findById(userId) .orElseThrow(() -> new UserNotFoundException("User not found with id: " + userId)); return orderQuerydslRepository.findOrdersByUsername(user.getUsername()); } public List<Order> getHighValueOrders(BigDecimal minAmount) { return orderQuerydslRepository.findHighValueOrders(minAmount); } public List<Order> getRecentOrders(int days) { return orderQuerydslRepository.findRecentOrders(days); } public List<UserOrderCategory> categorizeUsersBySpending() { return orderQuerydslRepository.categorizeUsersByOrderValue(); } public List<Order> searchOrders(OrderSearchCriteria criteria) { return orderQuerydslRepository.findOrdersByComplexCriteria(criteria); } } 7. Testing
Unit Tests with Querydsl
@DataJpaTest @Import({QuerydslConfig.class, UserQuerydslRepository.class, OrderQuerydslRepository.class}) class QuerydslRepositoryTest { @Autowired private TestEntityManager entityManager; @Autowired private UserQuerydslRepository userRepository; @Autowired private OrderQuerydslRepository orderRepository; private User testUser; private Order testOrder; @BeforeEach void setUp() { testUser = new User("testuser", "[email protected]", "Test", "User", 25); entityManager.persist(testUser); testOrder = new Order(testUser, new BigDecimal("99.99")); entityManager.persist(testOrder); entityManager.flush(); } @Test void shouldFindUserByUsername() { // When Optional<User> foundUser = userRepository.findByUsername("testuser"); // Then assertThat(foundUser).isPresent(); assertThat(foundUser.get().getEmail()).isEqualTo("[email protected]"); } @Test void shouldFindOrdersByUsername() { // When List<Order> orders = orderRepository.findOrdersByUsername("testuser"); // Then assertThat(orders).hasSize(1); assertThat(orders.get(0).getTotalAmount()).isEqualByComparingTo("99.99"); } @Test void shouldFindActiveUsers() { // Given User inactiveUser = new User("inactive", "[email protected]", "Inactive", "User", 30); inactiveUser.deactivate(); entityManager.persist(inactiveUser); entityManager.flush(); // When List<User> activeUsers = userRepository.findByStatus(UserStatus.ACTIVE); // Then assertThat(activeUsers).hasSize(1); assertThat(activeUsers.get(0).getUsername()).isEqualTo("testuser"); } } @SpringBootTest class UserServiceIntegrationTest { @Autowired private UserService userService; @Autowired private UserRepository userRepository; @BeforeEach void setUp() { userRepository.deleteAll(); } @Test void shouldCreateAndFindUser() { // Given CreateUserRequest request = new CreateUserRequest( "john_doe", "[email protected]", "John", "Doe", 30 ); // When User user = userService.createUser(request); // Then assertThat(user.getId()).isNotNull(); assertThat(user.getUsername()).isEqualTo("john_doe"); User foundUser = userService.getUserByUsername("john_doe"); assertThat(foundUser.getEmail()).isEqualTo("[email protected]"); } @Test void shouldSearchUsers() { // Given User user1 = new User("user1", "[email protected]", "Alice", "Smith", 25); User user2 = new User("user2", "[email protected]", "Bob", "Johnson", 35); userRepository.saveAll(List.of(user1, user2)); UserSearchCriteria criteria = new UserSearchCriteria(); criteria.setSearchTerm("Alice"); // When List<User> users = userService.searchUsers(criteria); // Then assertThat(users).hasSize(1); assertThat(users.get(0).getUsername()).isEqualTo("user1"); } } This comprehensive guide covers Querydsl setup, basic and advanced querying techniques, integration with Spring Data JPA, and testing strategies. Querydsl provides type-safe query construction that helps prevent runtime errors and makes code more maintainable and refactor-friendly.