MACRO NEPAL
JOOQ for Type-Safe SQL in Java: Complete Guide
awiskar acharya
November 17, 2025
9:44 am

Learn how to use JOOQ (Java Object Oriented Querying) for type-safe SQL construction and execution in Java applications.

Table of Contents

  1. JOOQ Overview & Setup
  2. Code Generation
  3. Basic CRUD Operations
  4. Advanced Querying
  5. Transactions & Batch Operations
  6. Custom Types & Converters
  7. Performance Optimization
  8. Integration with Spring Boot

JOOQ Overview & Setup

What is JOOQ?

JOOQ is a popular Java library that provides:

  • Type-safe SQL query construction
  • Code generation from database schema
  • Fluent API for SQL building
  • Support for complex SQL features

Maven Configuration

<properties>
<jooq.version>3.19.0</jooq.version>
<jooq-meta.version>3.19.0</jooq-meta.version>
<jooq-codegen.version>3.19.0</jooq-codegen.version>
</properties>
<dependencies>
<!-- JOOQ Runtime -->
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>${jooq.version}</version>
</dependency>
<!-- Database Driver (PostgreSQL example) -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
<!-- Spring Boot Starter (optional) -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<!-- JOOQ Code Generation Plugin -->
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq-codegen.version}</version>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<configurationFile>src/main/resources/jooq-config.xml</configurationFile>
</configuration>
</plugin>
</plugins>
</build>

Gradle Configuration

dependencies {
implementation 'org.jooq:jooq:3.19.0'
implementation 'org.postgresql:postgresql:42.6.0'
implementation 'org.springframework.boot:spring-boot-starter-jooq'
}
// JOOQ Code Generation Task
task generateJooqClasses {
doLast {
Configuration configuration = new Configuration()
.withJdbc(new Jdbc()
.withDriver('org.postgresql.Driver')
.withUrl('jdbc:postgresql://localhost:5432/mydb')
.withUser('username')
.withPassword('password'))
.withGenerator(new Generator()
.withDatabase(new Database()
.withName('org.jooq.meta.postgres.PostgresDatabase')
.withIncludes('.*')
.withExcludes('')
.withInputSchema('public'))
.withTarget(new Target()
.withPackageName('com.example.jooq')
.withDirectory('src/main/java')))
GenerationTool.generate(configuration)
}
}

Code Generation

1. JOOQ Configuration File

<!-- src/main/resources/jooq-config.xml -->
<configuration>
<jdbc>
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost:5432/ecommerce</url>
<user>admin</user>
<password>secret</password>
</jdbc>
<generator>
<name>org.jooq.codegen.JavaGenerator</name>
<database>
<name>org.jooq.meta.postgres.PostgresDatabase</name>
<includes>.*</includes>
<excludes>
schema_version | flyway_schema_history | .*\\$.*
</excludes>
<inputSchema>public</inputSchema>
<!-- Custom types -->
<forcedTypes>
<forcedType>
<name>varchar</name>
<includeTypes>JSONB?|JSON</includeTypes>
</forcedType>
<forcedType>
<name>boolean</name>
<includeTypes>BOOL</includeTypes>
</forcedType>
</forcedTypes>
</database>
<generate>
<pojos>true</pojos>
<immutablePojos>false</immutablePojos>
<daos>true</daos>
<javaTimeTypes>true</javaTimeTypes>
<fluentSetters>true</fluentSetters>
<springAnnotations>true</springAnnotations>
</generate>
<target>
<packageName>com.example.ecommerce.jooq</packageName>
<directory>src/main/java</directory>
</target>
</generator>
</configuration>

2. Database Schema Example

-- Sample database schema for e-commerce application
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE addresses (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
type VARCHAR(20) NOT NULL, -- 'SHIPPING', 'BILLING'
street VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
zip_code VARCHAR(20) NOT NULL,
country VARCHAR(100) NOT NULL DEFAULT 'US',
is_primary BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INTEGER NOT NULL DEFAULT 0,
category VARCHAR(100) NOT NULL,
tags JSONB,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
order_number VARCHAR(50) NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
total_amount DECIMAL(10,2) NOT NULL,
shipping_address_id BIGINT NOT NULL REFERENCES addresses(id),
billing_address_id BIGINT NOT NULL REFERENCES addresses(id),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
subtotal DECIMAL(10,2) NOT NULL
);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

Basic CRUD Operations

3. JOOQ Configuration Service

@Configuration
public class JooqConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public DataSourceConnectionProvider connectionProvider(DataSource dataSource) {
return new DataSourceConnectionProvider(dataSource);
}
@Bean
public DefaultConfigurationCustomizer configurationCustomizer() {
return configuration -> {
// Configure JOOQ settings
configuration.set(SQLDialect.POSTGRES);
configuration.set(ExecuteListenerProvider.providers(
new JooqQueryListener()
));
configuration.settings()
.withExecuteLogging(true)
.withRenderSchema(false);
};
}
@Bean
public DefaultDSLContext dsl(org.jooq.Configuration configuration) {
return new DefaultDSLContext(configuration);
}
}
// Query listener for logging and monitoring
@Component
public class JooqQueryListener extends DefaultExecuteListener {
private static final Logger logger = LoggerFactory.getLogger(JooqQueryListener.class);
@Override
public void executeStart(ExecuteContext ctx) {
super.executeStart(ctx);
// Log the SQL query with parameters
if (logger.isDebugEnabled()) {
String sql = ctx.sql();
logger.debug("Executing SQL: {}", sql);
}
}
@Override
public void executeEnd(ExecuteContext ctx) {
super.executeEnd(ctx);
// Log execution time and results
long time = System.currentTimeMillis() - ctx.queryCreated();
logger.debug("Query executed in {} ms", time);
}
}

4. Customer Repository

@Repository
@Transactional
public class CustomerRepository {
private static final Logger logger = LoggerFactory.getLogger(CustomerRepository.class);
private final DSLContext dsl;
// Generated JOOQ tables
private static final Customers CUSTOMERS = Customers.CUSTOMERS;
private static final Addresses ADDRESSES = Addresses.ADDRESSES;
public CustomerRepository(DSLContext dsl) {
this.dsl = dsl;
}
// CREATE operations
public Long createCustomer(CustomerRecord customer) {
return dsl.insertInto(CUSTOMERS)
.set(CUSTOMERS.EMAIL, customer.getEmail())
.set(CUSTOMERS.FIRST_NAME, customer.getFirstName())
.set(CUSTOMERS.LAST_NAME, customer.getLastName())
.set(CUSTOMERS.PHONE, customer.getPhone())
.returning(CUSTOMERS.ID)
.fetchOne()
.getId();
}
public void createCustomerWithAddress(CustomerRecord customer, AddressRecord address) {
dsl.transaction(configuration -> {
DSLContext transactionDsl = DSL.using(configuration);
// Insert customer
Long customerId = transactionDsl.insertInto(CUSTOMERS)
.set(CUSTOMERS.EMAIL, customer.getEmail())
.set(CUSTOMERS.FIRST_NAME, customer.getFirstName())
.set(CUSTOMERS.LAST_NAME, customer.getLastName())
.set(CUSTOMERS.PHONE, customer.getPhone())
.returning(CUSTOMERS.ID)
.fetchOne()
.getId();
// Insert address
transactionDsl.insertInto(ADDRESSES)
.set(ADDRESSES.CUSTOMER_ID, customerId)
.set(ADDRESSES.TYPE, address.getType())
.set(ADDRESSES.STREET, address.getStreet())
.set(ADDRESSES.CITY, address.getCity())
.set(ADDRESSES.STATE, address.getState())
.set(ADDRESSES.ZIP_CODE, address.getZipCode())
.set(ADDRESSES.COUNTRY, address.getCountry())
.set(ADDRESSES.IS_PRIMARY, address.getIsPrimary())
.execute();
});
}
// READ operations
public Optional<CustomerRecord> findById(Long id) {
return dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.ID.eq(id))
.fetchOptional();
}
public Optional<CustomerRecord> findByEmail(String email) {
return dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.EMAIL.eq(email))
.fetchOptional();
}
public List<CustomerRecord> findAllActiveCustomers() {
return dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.orderBy(CUSTOMERS.LAST_NAME.asc(), CUSTOMERS.FIRST_NAME.asc())
.fetch();
}
public List<CustomerRecord> findCustomersByStatus(String status) {
return dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.STATUS.eq(status))
.fetch();
}
public Page<CustomerRecord> findCustomersPaginated(Pageable pageable) {
// Get total count
int total = dsl.fetchCount(CUSTOMERS);
// Get paginated results
List<CustomerRecord> customers = dsl.selectFrom(CUSTOMERS)
.orderBy(getSortFields(pageable.getSort()))
.limit(pageable.getPageSize())
.offset(pageable.getOffset())
.fetch();
return new PageImpl<>(customers, pageable, total);
}
// UPDATE operations
public int updateCustomerStatus(Long customerId, String status) {
return dsl.update(CUSTOMERS)
.set(CUSTOMERS.STATUS, status)
.set(CUSTOMERS.UPDATED_AT, LocalDateTime.now())
.where(CUSTOMERS.ID.eq(customerId))
.execute();
}
public int updateCustomerEmail(Long customerId, String email) {
return dsl.update(CUSTOMERS)
.set(CUSTOMERS.EMAIL, email)
.set(CUSTOMERS.UPDATED_AT, LocalDateTime.now())
.where(CUSTOMERS.ID.eq(customerId))
.execute();
}
// DELETE operations (soft delete)
public int deactivateCustomer(Long customerId) {
return dsl.update(CUSTOMERS)
.set(CUSTOMERS.STATUS, "INACTIVE")
.set(CUSTOMERS.UPDATED_AT, LocalDateTime.now())
.where(CUSTOMERS.ID.eq(customerId))
.execute();
}
public int deleteCustomerPermanently(Long customerId) {
return dsl.deleteFrom(CUSTOMERS)
.where(CUSTOMERS.ID.eq(customerId))
.execute();
}
// Helper methods
private Collection<SortField<?>> getSortFields(Sort sort) {
if (sort == null || sort.isUnsorted()) {
return List.of(CUSTOMERS.LAST_NAME.asc(), CUSTOMERS.FIRST_NAME.asc());
}
return sort.stream()
.map(order -> {
SortField<?> field = switch (order.getProperty()) {
case "firstName" -> CUSTOMERS.FIRST_NAME;
case "lastName" -> CUSTOMERS.LAST_NAME;
case "email" -> CUSTOMERS.EMAIL;
case "createdAt" -> CUSTOMERS.CREATED_AT;
default -> CUSTOMERS.LAST_NAME;
};
return order.isAscending() ? field.asc() : field.desc();
})
.collect(Collectors.toList());
}
}

5. Product Repository

@Repository
@Transactional
public class ProductRepository {
private final DSLContext dsl;
private static final Products PRODUCTS = Products.PRODUCTS;
private static final OrderItems ORDER_ITEMS = OrderItems.ORDER_ITEMS;
public ProductRepository(DSLContext dsl) {
this.dsl = dsl;
}
// Complex queries with joins and aggregations
public List<ProductSales> findTopSellingProducts(int limit) {
return dsl.select(
PRODUCTS.NAME,
PRODUCTS.CATEGORY,
ORDER_ITEMS.QUANTITY.sum().as("total_quantity"),
ORDER_ITEMS.SUBTOTAL.sum().as("total_revenue")
)
.from(PRODUCTS)
.join(ORDER_ITEMS).on(PRODUCTS.ID.eq(ORDER_ITEMS.PRODUCT_ID))
.where(PRODUCTS.IS_ACTIVE.isTrue())
.groupBy(PRODUCTS.ID, PRODUCTS.NAME, PRODUCTS.CATEGORY)
.orderBy(field("total_quantity").desc())
.limit(limit)
.fetch()
.map(record -> new ProductSales(
record.get(PRODUCTS.NAME),
record.get(PRODUCTS.CATEGORY),
record.get("total_quantity", Integer.class),
record.get("total_revenue", BigDecimal.class)
));
}
public List<ProductRecord> findProductsByCategory(String category, Pageable pageable) {
return dsl.selectFrom(PRODUCTS)
.where(PRODUCTS.CATEGORY.eq(category))
.and(PRODUCTS.IS_ACTIVE.isTrue())
.orderBy(PRODUCTS.NAME.asc())
.limit(pageable.getPageSize())
.offset(pageable.getOffset())
.fetch();
}
public List<ProductRecord> searchProducts(String searchTerm) {
return dsl.selectFrom(PRODUCTS)
.where(PRODUCTS.NAME.likeIgnoreCase("%" + searchTerm + "%")
.or(PRODUCTS.DESCRIPTION.likeIgnoreCase("%" + searchTerm + "%")))
.and(PRODUCTS.IS_ACTIVE.isTrue())
.orderBy(PRODUCTS.NAME.asc())
.fetch();
}
public int updateProductStock(Long productId, int quantityChange) {
return dsl.update(PRODUCTS)
.set(PRODUCTS.STOCK_QUANTITY, PRODUCTS.STOCK_QUANTITY.add(quantityChange))
.set(PRODUCTS.UPDATED_AT, LocalDateTime.now())
.where(PRODUCTS.ID.eq(productId))
.and(PRODUCTS.STOCK_QUANTITY.add(quantityChange).ge(0))
.execute();
}
public Map<String, Long> getProductCountByCategory() {
return dsl.select(PRODUCTS.CATEGORY, count())
.from(PRODUCTS)
.where(PRODUCTS.IS_ACTIVE.isTrue())
.groupBy(PRODUCTS.CATEGORY)
.fetch()
.stream()
.collect(Collectors.toMap(
record -> record.get(PRODUCTS.CATEGORY),
record -> record.get(count())
));
}
// Record class for custom result
public record ProductSales(
String productName,
String category,
Integer totalQuantity,
BigDecimal totalRevenue
) {}
}

Advanced Querying

6. Complex Query Service

@Service
@Transactional(readOnly = true)
public class AdvancedQueryService {
private final DSLContext dsl;
// Generated tables
private static final Customers CUSTOMERS = Customers.CUSTOMERS;
private static final Orders ORDERS = Orders.ORDERS;
private static final OrderItems ORDER_ITEMS = OrderItems.ORDER_ITEMS;
private static final Products PRODUCTS = Products.PRODUCTS;
private static final Addresses ADDRESSES = Addresses.ADDRESSES;
public AdvancedQueryService(DSLContext dsl) {
this.dsl = dsl;
}
// Complex join with multiple conditions
public List<CustomerOrderSummary> getCustomerOrderSummaries() {
return dsl.select(
CUSTOMERS.FIRST_NAME,
CUSTOMERS.LAST_NAME,
CUSTOMERS.EMAIL,
count(ORDERS.ID).as("order_count"),
sum(ORDERS.TOTAL_AMOUNT).as("total_spent"),
max(ORDERS.CREATED_AT).as("last_order_date")
)
.from(CUSTOMERS)
.leftJoin(ORDERS).on(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.groupBy(CUSTOMERS.ID, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME, CUSTOMERS.EMAIL)
.having(count(ORDERS.ID).gt(0))
.orderBy(field("total_spent").desc())
.fetch()
.map(record -> new CustomerOrderSummary(
record.get(CUSTOMERS.FIRST_NAME) + " " + record.get(CUSTOMERS.LAST_NAME),
record.get(CUSTOMERS.EMAIL),
record.get("order_count", Long.class),
record.get("total_spent", BigDecimal.class),
record.get("last_order_date", LocalDateTime.class)
));
}
// Subquery example
public List<ProductRecord> findProductsNeverOrdered() {
var orderedProductIds = dsl.select(ORDER_ITEMS.PRODUCT_ID)
.from(ORDER_ITEMS)
.groupBy(ORDER_ITEMS.PRODUCT_ID);
return dsl.selectFrom(PRODUCTS)
.where(PRODUCTS.ID.notIn(orderedProductIds))
.and(PRODUCTS.IS_ACTIVE.isTrue())
.fetch();
}
// Conditional queries with dynamic filters
public List<OrderRecord> findOrdersWithFilters(OrderFilter filter) {
var condition = DSL.noCondition();
if (filter.customerId() != null) {
condition = condition.and(ORDERS.CUSTOMER_ID.eq(filter.customerId()));
}
if (filter.status() != null && !filter.status().isEmpty()) {
condition = condition.and(ORDERS.STATUS.in(filter.status()));
}
if (filter.fromDate() != null) {
condition = condition.and(ORDERS.CREATED_AT.ge(filter.fromDate()));
}
if (filter.toDate() != null) {
condition = condition.and(ORDERS.CREATED_AT.le(filter.toDate()));
}
if (filter.minAmount() != null) {
condition = condition.and(ORDERS.TOTAL_AMOUNT.ge(filter.minAmount()));
}
if (filter.maxAmount() != null) {
condition = condition.and(ORDERS.TOTAL_AMOUNT.le(filter.maxAmount()));
}
return dsl.selectFrom(ORDERS)
.where(condition)
.orderBy(ORDERS.CREATED_AT.desc())
.fetch();
}
// Window functions for advanced analytics
public List<CustomerRanking> getCustomerSpendingRankings() {
var totalSpent = sum(ORDERS.TOTAL_AMOUNT).over()
.partitionBy(ORDERS.CUSTOMER_ID)
.as("customer_total");
var spendingRank = rowNumber().over()
.orderBy(field("customer_total").desc())
.as("rank");
return dsl.select(
CUSTOMERS.FIRST_NAME,
CUSTOMERS.LAST_NAME,
totalSpent,
spendingRank
)
.from(CUSTOMERS)
.join(ORDERS).on(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.groupBy(CUSTOMERS.ID, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME)
.orderBy(field("rank").asc())
.fetch()
.map(record -> new CustomerRanking(
record.get(CUSTOMERS.FIRST_NAME) + " " + record.get(CUSTOMERS.LAST_NAME),
record.get("customer_total", BigDecimal.class),
record.get("rank", Integer.class)
));
}
// Common Table Expression (CTE) example
public List<MonthlySales> getMonthlySalesReport(int year) {
var monthlySales = name("monthly_sales").fields(
"year_month", "total_sales", "order_count"
).as(
dsl.select(
dateTrunc("month", ORDERS.CREATED_AT).as("year_month"),
sum(ORDERS.TOTAL_AMOUNT).as("total_sales"),
count(ORDERS.ID).as("order_count")
)
.from(ORDERS)
.where(extract(ORDERS.CREATED_AT, DatePart.YEAR).eq(year))
.groupBy(dateTrunc("month", ORDERS.CREATED_AT))
);
return dsl.with(monthlySales)
.select()
.from(monthlySales)
.orderBy(field("year_month").asc())
.fetch()
.map(record -> new MonthlySales(
record.get("year_month", LocalDateTime.class),
record.get("total_sales", BigDecimal.class),
record.get("order_count", Long.class)
));
}
// JSON querying (PostgreSQL JSONB)
public List<ProductRecord> findProductsByTag(String tag) {
return dsl.selectFrom(PRODUCTS)
.where(PRODUCTS.TAGS.contains(tag))
.and(PRODUCTS.IS_ACTIVE.isTrue())
.fetch();
}
// Record classes for complex results
public record CustomerOrderSummary(
String customerName,
String email,
Long orderCount,
BigDecimal totalSpent,
LocalDateTime lastOrderDate
) {}
public record CustomerRanking(
String customerName,
BigDecimal totalSpent,
Integer rank
) {}
public record MonthlySales(
LocalDateTime month,
BigDecimal totalSales,
Long orderCount
) {}
public record OrderFilter(
Long customerId,
List<String> status,
LocalDateTime fromDate,
LocalDateTime toDate,
BigDecimal minAmount,
BigDecimal maxAmount
) {}
}

Transactions & Batch Operations

7. Transaction Management Service

@Service
public class OrderService {
private static final Logger logger = LoggerFactory.getLogger(OrderService.class);
private final DSLContext dsl;
private final ProductRepository productRepository;
private static final Orders ORDERS = Orders.ORDERS;
private static final OrderItems ORDER_ITEMS = OrderItems.ORDER_ITEMS;
private static final Products PRODUCTS = Products.PRODUCTS;
public OrderService(DSLContext dsl, ProductRepository productRepository) {
this.dsl = dsl;
this.productRepository = productRepository;
}
@Transactional
public OrderResult createOrder(CreateOrderRequest request) {
return dsl.transactionResult(configuration -> {
DSLContext transactionDsl = DSL.using(configuration);
try {
// 1. Validate stock availability
validateStockAvailability(transactionDsl, request.items());
// 2. Create order record
Long orderId = createOrderRecord(transactionDsl, request);
// 3. Create order items and update stock
createOrderItems(transactionDsl, orderId, request.items());
// 4. Calculate and update order total
BigDecimal totalAmount = calculateOrderTotal(transactionDsl, orderId);
updateOrderTotal(transactionDsl, orderId, totalAmount);
logger.info("Order created successfully: {}", orderId);
return new OrderResult(orderId, "SUCCESS", totalAmount);
} catch (Exception e) {
logger.error("Failed to create order", e);
throw new OrderCreationException("Order creation failed", e);
}
});
}
@Transactional
public void bulkUpdateProductPrices(List<PriceUpdate> updates) {
dsl.batch(updates.stream()
.map(update -> dsl.update(PRODUCTS)
.set(PRODUCTS.PRICE, update.newPrice())
.set(PRODUCTS.UPDATED_AT, LocalDateTime.now())
.where(PRODUCTS.ID.eq(update.productId()))
)
.collect(Collectors.toList())
).execute();
}
@Transactional
public void bulkInsertProducts(List<ProductRecord> products) {
var batchInsert = dsl.batchInsert(products);
batchInsert.execute();
}
// Optimistic locking example
@Transactional
public boolean updateProductWithOptimisticLock(ProductRecord product, LocalDateTime currentVersion) {
int updated = dsl.update(PRODUCTS)
.set(PRODUCTS.NAME, product.getName())
.set(PRODUCTS.PRICE, product.getPrice())
.set(PRODUCTS.DESCRIPTION, product.getDescription())
.set(PRODUCTS.UPDATED_AT, LocalDateTime.now())
.where(PRODUCTS.ID.eq(product.getId()))
.and(PRODUCTS.UPDATED_AT.eq(currentVersion))
.execute();
return updated > 0;
}
private void validateStockAvailability(DSLContext dsl, List<OrderItemRequest> items) {
for (OrderItemRequest item : items) {
int availableStock = dsl.select(PRODUCTS.STOCK_QUANTITY)
.from(PRODUCTS)
.where(PRODUCTS.ID.eq(item.productId()))
.fetchOne(PRODUCTS.STOCK_QUANTITY);
if (availableStock < item.quantity()) {
throw new InsufficientStockException(
"Insufficient stock for product: " + item.productId());
}
}
}
private Long createOrderRecord(DSLContext dsl, CreateOrderRequest request) {
return dsl.insertInto(ORDERS)
.set(ORDERS.CUSTOMER_ID, request.customerId())
.set(ORDERS.ORDER_NUMBER, generateOrderNumber())
.set(ORDERS.STATUS, "PENDING")
.set(ORDERS.SHIPPING_ADDRESS_ID, request.shippingAddressId())
.set(ORDERS.BILLING_ADDRESS_ID, request.billingAddressId())
.set(ORDERS.TOTAL_AMOUNT, BigDecimal.ZERO) // Temporary value
.returning(ORDERS.ID)
.fetchOne()
.getId();
}
private void createOrderItems(DSLContext dsl, Long orderId, List<OrderItemRequest> items) {
var batchInserts = new ArrayList<InsertValuesStepN<OrderItemsRecord>>();
for (OrderItemRequest item : items) {
// Get current product price
BigDecimal unitPrice = dsl.select(PRODUCTS.PRICE)
.from(PRODUCTS)
.where(PRODUCTS.ID.eq(item.productId()))
.fetchOne(PRODUCTS.PRICE);
BigDecimal subtotal = unitPrice.multiply(BigDecimal.valueOf(item.quantity()));
// Prepare insert
var insert = dsl.insertInto(ORDER_ITEMS)
.columns(
ORDER_ITEMS.ORDER_ID,
ORDER_ITEMS.PRODUCT_ID,
ORDER_ITEMS.QUANTITY,
ORDER_ITEMS.UNIT_PRICE,
ORDER_ITEMS.SUBTOTAL
)
.values(
orderId,
item.productId(),
item.quantity(),
unitPrice,
subtotal
);
batchInserts.add(insert);
// Update product stock
dsl.update(PRODUCTS)
.set(PRODUCTS.STOCK_QUANTITY, PRODUCTS.STOCK_QUANTITY.sub(item.quantity()))
.set(PRODUCTS.UPDATED_AT, LocalDateTime.now())
.where(PRODUCTS.ID.eq(item.productId()))
.execute();
}
// Execute all item inserts
dsl.batch(batchInserts).execute();
}
private BigDecimal calculateOrderTotal(DSLContext dsl, Long orderId) {
return dsl.select(sum(ORDER_ITEMS.SUBTOTAL))
.from(ORDER_ITEMS)
.where(ORDER_ITEMS.ORDER_ID.eq(orderId))
.fetchOneInto(BigDecimal.class);
}
private void updateOrderTotal(DSLContext dsl, Long orderId, BigDecimal totalAmount) {
dsl.update(ORDERS)
.set(ORDERS.TOTAL_AMOUNT, totalAmount)
.set(ORDERS.STATUS, "CONFIRMED")
.set(ORDERS.UPDATED_AT, LocalDateTime.now())
.where(ORDERS.ID.eq(orderId))
.execute();
}
private String generateOrderNumber() {
return "ORD-" + System.currentTimeMillis() + "-" + 
ThreadLocalRandom.current().nextInt(1000, 9999);
}
// DTO classes
public record CreateOrderRequest(
Long customerId,
Long shippingAddressId,
Long billingAddressId,
List<OrderItemRequest> items
) {}
public record OrderItemRequest(Long productId, Integer quantity) {}
public record OrderResult(Long orderId, String status, BigDecimal totalAmount) {}
public record PriceUpdate(Long productId, BigDecimal newPrice) {}
}

Custom Types & Converters

8. Custom Data Type Binding

// JSONB converter for PostgreSQL
@Component
public class JsonbConverter implements Binding<JSONB, String> {
@Override
public Converter<String, JSONB> converter() {
return new Converter<String, JSONB>() {
@Override
public JSONB from(String databaseObject) {
return databaseObject == null ? null : JSONB.valueOf(databaseObject);
}
@Override
public String to(JSONB userObject) {
return userObject == null ? null : userObject.data();
}
@Override
public Class<String> fromType() {
return String.class;
}
@Override
public Class<JSONB> toType() {
return JSONB.class;
}
};
}
@Override
public void sql(BindingSQLContext<JSONB> ctx) throws SQLException {
ctx.render().visit(DSL.val(ctx.convert(converter()).value()));
}
@Override
public void register(BindingRegisterContext<JSONB> ctx) throws SQLException {
ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
}
@Override
public void set(BindingSetStatementContext<JSONB> ctx) throws SQLException {
ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
}
@Override
public void get(BindingGetResultSetContext<JSONB> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
}
@Override
public void get(BindingGetStatementContext<JSONB> ctx) throws SQLException {
ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
}
@Override
public void set(BindingSetSQLOutputContext<JSONB> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
@Override
public void get(BindingGetSQLInputContext<JSONB> ctx) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
}
// Custom enum converter
public class StatusConverter implements Converter<String, CustomerStatus> {
@Override
public CustomerStatus from(String databaseObject) {
return databaseObject == null ? null : CustomerStatus.valueOf(databaseObject);
}
@Override
public String to(CustomerStatus userObject) {
return userObject == null ? null : userObject.name();
}
@Override
public Class<String> fromType() {
return String.class;
}
@Override
public Class<CustomerStatus> toType() {
return CustomerStatus.class;
}
}
public enum CustomerStatus {
ACTIVE, INACTIVE, SUSPENDED, PENDING_VERIFICATION
}
// Custom configuration for type binding
@Configuration
public class CustomJooqConfig {
@Bean
public DefaultConfigurationCustomizer jooqConfigCustomizer() {
return config -> {
// Register custom converters
config.set(new DefaultBinding(
SQLDataType.VARCHAR, 
new JsonbConverter()
));
config.set(new DefaultBinding(
SQLDataType.VARCHAR.asEnumDataType(CustomerStatus.class),
new StatusConverter()
));
};
}
}

Performance Optimization

9. JOOQ Performance Tips

@Service
public class OptimizedQueryService {
private final DSLContext dsl;
public OptimizedQueryService(DSLContext dsl) {
this.dsl = dsl;
}
// Use fetchGroups for one-to-many relationships
public Map<CustomerRecord, Result<OrderRecord>> getCustomersWithOrders() {
return dsl.select()
.from(CUSTOMERS)
.leftJoin(ORDERS).on(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.fetchGroups(CUSTOMERS, ORDERS);
}
// Use fetchLazy for large result sets
public void processLargeCustomerSet() {
try (Cursor<CustomerRecord> cursor = dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.fetchLazy()) {
for (CustomerRecord customer : cursor) {
// Process each customer
processCustomer(customer);
// Manual memory management for very large datasets
if (cursor.rownum() % 1000 == 0) {
System.gc(); // Use cautiously
}
}
}
}
// Use fetchInto for direct DTO mapping
public List<CustomerDto> getCustomerDtos() {
return dsl.select(
CUSTOMERS.FIRST_NAME,
CUSTOMERS.LAST_NAME,
CUSTOMERS.EMAIL,
count(ORDERS.ID).as("orderCount")
)
.from(CUSTOMERS)
.leftJoin(ORDERS).on(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
.groupBy(CUSTOMERS.ID, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME, CUSTOMERS.EMAIL)
.fetchInto(CustomerDto.class);
}
// Optimized bulk operations
public void bulkCustomerStatusUpdate(List<Long> customerIds, String newStatus) {
dsl.update(CUSTOMERS)
.set(CUSTOMERS.STATUS, newStatus)
.set(CUSTOMERS.UPDATED_AT, LocalDateTime.now())
.where(CUSTOMERS.ID.in(customerIds))
.execute();
}
// Use exists for existence checks (more efficient than count)
public boolean customerHasOrders(Long customerId) {
return dsl.fetchExists(
dsl.selectOne()
.from(ORDERS)
.where(ORDERS.CUSTOMER_ID.eq(customerId))
);
}
// Optimized pagination with keyset pagination
public List<CustomerRecord> getCustomersKeysetPagination(Long lastId, int limit) {
return dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.ID.gt(lastId))
.orderBy(CUSTOMERS.ID.asc())
.limit(limit)
.fetch();
}
// Use explain to analyze query performance
public void analyzeQueryPerformance() {
String explain = dsl.explain(
dsl.selectFrom(CUSTOMERS)
.where(CUSTOMERS.STATUS.eq("ACTIVE"))
.orderBy(CUSTOMERS.LAST_NAME.asc())
);
logger.debug("Query plan: {}", explain);
}
private void processCustomer(CustomerRecord customer) {
// Process customer record
}
// DTO class
public static class CustomerDto {
private String firstName;
private String lastName;
private String email;
private Long orderCount;
// Getters and setters
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 String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
public Long getOrderCount() { return orderCount; }
public void setOrderCount(Long orderCount) { this.orderCount = orderCount; }
}
}

Integration with Spring Boot

10. Spring Boot Configuration

# application.yml
spring:
datasource:
url: jdbc:postgresql://localhost:5432/ecommerce
username: admin
password: secret
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
jooq:
sql-dialect: POSTGRES
logging:
level:
org.jooq: DEBUG
org.jooq.tools.LoggerListener: DEBUG

11. Spring Service Integration

@Service
@Transactional
public class EcommerceService {
private final DSLContext dsl;
private final CustomerRepository customerRepository;
private final ProductRepository productRepository;
private final AdvancedQueryService advancedQueryService;
public EcommerceService(DSLContext dsl,
CustomerRepository customerRepository,
ProductRepository productRepository,
AdvancedQueryService advancedQueryService) {
this.dsl = dsl;
this.customerRepository = customerRepository;
this.productRepository = productRepository;
this.advancedQueryService = advancedQueryService;
}
public CustomerDashboard getCustomerDashboard(Long customerId) {
// Get customer basic info
var customer = customerRepository.findById(customerId)
.orElseThrow(() -> new CustomerNotFoundException(customerId));
// Get recent orders
var recentOrders = getRecentOrders(customerId);
// Get order statistics
var orderStats = getCustomerOrderStats(customerId);
// Get recommended products
var recommendations = getProductRecommendations(customerId);
return new CustomerDashboard(customer, recentOrders, orderStats, recommendations);
}
@Transactional(readOnly = true)
public SalesReport generateSalesReport(ReportCriteria criteria) {
var monthlySales = advancedQueryService.getMonthlySalesReport(criteria.year());
var topProducts = productRepository.findTopSellingProducts(10);
var customerRankings = advancedQueryService.getCustomerSpendingRankings();
return new SalesReport(monthlySales, topProducts, customerRankings, criteria);
}
@Async
@Transactional
public CompletableFuture<Void> processCustomerDataAsync(Long customerId) {
return CompletableFuture.runAsync(() -> {
// Process customer data asynchronously
updateCustomerStatistics(customerId);
generateCustomerInsights(customerId);
});
}
// Helper methods
private List<OrderRecord> getRecentOrders(Long customerId) {
return dsl.selectFrom(Orders.ORDERS)
.where(Orders.ORDERS.CUSTOMER_ID.eq(customerId))
.orderBy(Orders.ORDERS.CREATED_AT.desc())
.limit(10)
.fetch();
}
private OrderStats getCustomerOrderStats(Long customerId) {
var result = dsl.select(
count(Orders.ORDERS.ID).as("total_orders"),
sum(Orders.ORDERS.TOTAL_AMOUNT).as("total_spent"),
avg(Orders.ORDERS.TOTAL_AMOUNT).as("avg_order_value")
)
.from(Orders.ORDERS)
.where(Orders.ORDERS.CUSTOMER_ID.eq(customerId))
.fetchOne();
return new OrderStats(
result.get("total_orders", Long.class),
result.get("total_spent", BigDecimal.class),
result.get("avg_order_value", BigDecimal.class)
);
}
private List<ProductRecord> getProductRecommendations(Long customerId) {
// Simple recommendation based on customer's previous purchases
return dsl.selectDistinct(Products.PRODUCTS.fields())
.from(Products.PRODUCTS)
.join(OrderItems.ORDER_ITEMS).on(Products.PRODUCTS.ID.eq(OrderItems.ORDER_ITEMS.PRODUCT_ID))
.join(Orders.ORDERS).on(OrderItems.ORDER_ITEMS.ORDER_ID.eq(Orders.ORDERS.ID))
.where(Orders.ORDERS.CUSTOMER_ID.eq(customerId))
.and(Products.PRODUCTS.IS_ACTIVE.isTrue())
.orderBy(Products.PRODUCTS.CREATED_AT.desc())
.limit(5)
.fetchInto(Products.PRODUCTS);
}
private void updateCustomerStatistics(Long customerId) {
// Update customer statistics asynchronously
}
private void generateCustomerInsights(Long customerId) {
// Generate customer insights asynchronously
}
// DTO classes
public record CustomerDashboard(
CustomerRecord customer,
List<OrderRecord> recentOrders,
OrderStats orderStats,
List<ProductRecord> recommendations
) {}
public record OrderStats(Long totalOrders, BigDecimal totalSpent, BigDecimal avgOrderValue) {}
public record SalesReport(
List<AdvancedQueryService.MonthlySales> monthlySales,
List<ProductRepository.ProductSales> topProducts,
List<AdvancedQueryService.CustomerRanking> customerRankings,
ReportCriteria criteria
) {}
public record ReportCriteria(Integer year, String category, LocalDate fromDate, LocalDate toDate) {}
}

This comprehensive JOOQ guide covers everything from basic setup and code generation to advanced querying patterns, transaction management, and Spring Boot integration, providing a solid foundation for building type-safe, performant database applications in Java.

Category : JAVA

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper