google-site-verification: google61fe8ba583a51912.html
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