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.