Java enthusiast crafting code with passion. enthusiastic about creating robust, scalable applications. Let's code, innovate, and build the future together!
Spring Boot Data JPA = Java Objects ⇄ Hibernate ⇄ Database (automatic CRUD, no SQL needed)
Press enter or click to view image in full size
Working with databases can be challenging, but Spring Data JPA makes it surprisingly simple. In this comprehensive guide, I’ll walk you through everything you need to know about advanced database operations in Spring Boot — from complex relationships to performance optimization.
Whether you’re building your first Spring Boot application or looking to level up your database skills, this guide has got you covered. Let’s dive in!
When to use: Simple queries with straightforward conditions.
Method 2: JPQL Queries (Database-Independent)
JPQL works with your entity objects, not database tables. This means your queries work across different databases!
@Repository public interface BookRepository extends JpaRepository<Book, Long> {
// Simple JPQL query @Query("SELECT b FROM Book b WHERE b.title LIKE %:keyword%") List<Book> searchByTitle(@Param("keyword") String keyword);
// JPQL with JOIN @Query("SELECT b FROM Book b JOIN b.author a WHERE a.email = :email") List<Book> findBooksByAuthorEmail(@Param("email") String email);
// JPQL with aggregation @Query("SELECT a.name, COUNT(b) FROM Book b JOIN b.author a GROUP BY a.name") List<Object[]> countBooksByAuthor();
// JPQL with multiple conditions @Query(""" SELECT b FROM Book b WHERE b.author.name = :authorName AND b.price BETWEEN :minPrice AND :maxPrice ORDER BY b.publishedDate DESC """) List<Book> findBooksByAuthorAndPriceRange( @Param("authorName") String authorName, @Param("minPrice") Double minPrice, @Param("maxPrice") Double maxPrice );
// Fetch specific fields only (DTO projection) @Query("SELECT new com.example.dto.BookDTO(b.id, b.title, b.price) FROM Book b WHERE b.author.id = :authorId") List<BookDTO> findBookDTOsByAuthor(@Param("authorId") Long authorId); }
JPQL Pro Tips
Use entity names (Book), not table names (books)
Reference entity fields (b.author.name), not column names
Perfect for queries that need to work on MySQL, PostgreSQL, Oracle, etc.
Method 3: Native SQL Queries (Maximum Control)
When you need database-specific features or maximum performance, use native SQL.
@Repository public interface BookRepository extends JpaRepository<Book, Long> {
// Simple native query @Query(value = "SELECT * FROM books WHERE isbn = :isbn", nativeQuery = true) Book findByIsbnNative(@Param("isbn") String isbn);
// Complex native query with joins @Query(value = """ SELECT b.* FROM books b INNER JOIN authors a ON b.author_id = a.id WHERE a.name = :authorName AND b.published_date > :date ORDER BY b.published_date DESC LIMIT :limit """, nativeQuery = true) List<Book> findRecentBooksByAuthor( @Param("authorName") String authorName, @Param("date") LocalDate date, @Param("limit") int limit );
// Native update query @Modifying @Transactional @Query(value = "UPDATE books SET price = price * 1.1 WHERE author_id = :authorId", nativeQuery = true) int increasePriceByAuthor(@Param("authorId") Long authorId);
// Native query with custom result mapping @Query(value = """ SELECT a.name as authorName, COUNT(b.id) as bookCount, AVG(b.price) as avgPrice FROM authors a LEFT JOIN books b ON a.id = b.author_id GROUP BY a.id, a.name HAVING COUNT(b.id) > :minBooks """, nativeQuery = true) List<Map<String, Object>> getAuthorStatistics(@Param("minBooks") int minBooks); }
Pagination & Sorting
Imagine you have 10,000 books in your database. Loading them all at once would crash your application!
Get Java Fusion’s stories in your inbox
Join Medium for free to get updates from this writer.
Pagination is the solution. Let’s learn how to implement it properly.
Basic Pagination Setup
Repository Layer
@Repository public interface ProductRepository extends JpaRepository<Product, Long> {
// Pagination with category filter Page<Product> findByCategory(String category, Pageable pageable);
// Pagination with custom query @Query("SELECT p FROM Product p WHERE p.price BETWEEN :minPrice AND :maxPrice") Page<Product> findByPriceRange( @Param("minPrice") Double minPrice, @Param("maxPrice") Double maxPrice, Pageable pageable );
// Pagination with search @Query("SELECT p FROM Product p WHERE LOWER(p.name) LIKE LOWER(CONCAT('%', :keyword, '%'))") Page<Product> searchProducts(@Param("keyword") String keyword, Pageable pageable); }
// Get products with simple sorting public Page<Product> getProducts(int page, int size, String sortBy) { Pageable pageable = PageRequest.of(page, size, Sort.by(sortBy).descending()); return productRepository.findAll(pageable); }
// Get products with multiple sort criteria public Page<Product> getProductsWithComplexSort(int page, int size) { Sort sort = Sort.by( Sort.Order.desc("price"), // First sort by price (high to low) Sort.Order.asc("name") // Then by name (A to Z) );
// Read-only transaction (faster for queries) @Transactional(readOnly = true) public List<Order> getOrdersByCustomer(Long customerId) { return orderRepository.findByCustomerId(customerId); }
// Transaction with timeout @Transactional(timeout = 30) // 30 seconds max public Order createOrder(OrderRequest request) { Order order = new Order(); order.setCustomerId(request.getCustomerId()); order.setItems(request.getItems()); order.setStatus(OrderStatus.PENDING);
return orderRepository.save(order); }
// Transaction with custom isolation level @Transactional(isolation = Isolation.SERIALIZABLE) public void processHighValuePayment(Long orderId, PaymentDetails details) { Order order = orderRepository.findById(orderId) .orElseThrow(() -> new OrderNotFoundException("Order not found"));
// Process payment paymentService.charge(details);
// Update order order.setStatus(OrderStatus.PAID); order.setPaymentDate(LocalDateTime.now()); orderRepository.save(order); }
// Rollback for specific exceptions @Transactional(rollbackFor = {BusinessException.class, ValidationException.class}) public Order placeOrder(OrderRequest request) { // Validate inventory boolean available = inventoryService.checkAvailability(request.getItems()); if (!available) { throw new ValidationException("Some items are out of stock"); }
// Create order Order order = new Order(); order.setCustomerId(request.getCustomerId()); order.setItems(request.getItems()); Order savedOrder = orderRepository.save(order);
// Send confirmation email (non-critical - don't rollback if this fails) try { emailService.sendOrderConfirmation(savedOrder); } catch (Exception e) { log.error("Failed to send email, but order was created successfully", e); }
return savedOrder; } }
Transaction Anti-Patterns to Avoid
// ❌ BAD: Transaction in controller @RestController public class BadController {
@Transactional // DON'T DO THIS! @PostMapping("/orders") public ResponseEntity<Order> createOrder(@RequestBody OrderRequest request) { // Transactions should be in service layer, not controllers! } }
// ❌ BAD: Calling external API inside transaction @Transactional public void processOrder(Order order) { orderRepository.save(order);
// DON'T call external APIs inside transactions! // This holds database connections while waiting for external response externalPaymentAPI.charge(order.getAmount()); } // ✅ GOOD: Call external API outside transaction public void processOrder(Order order) { // First call external API PaymentResult result = externalPaymentAPI.charge(order.getAmount());
// Then save to database in transaction saveOrderWithPayment(order, result); } @Transactional private void saveOrderWithPayment(Order order, PaymentResult result) { order.setPaymentResult(result); orderRepository.save(order); }
Database Migration with Flyway
Managing database changes across environments (dev, staging, production) is challenging. Flyway makes it simple and automated!
Why Use Flyway?
Imagine this scenario
Developer A adds a new column to users table
Developer B creates a new orders table
How do you keep all environments in sync? 🤔
Flyway solves this by version-controlling your database schema!
-- Create indexes CREATE INDEX idx_books_author_id ON books(author_id); CREATE INDEX idx_books_isbn ON books(isbn); CREATE INDEX idx_books_title ON books(title); CREATE INDEX idx_books_price ON books(price);
V3__Add_Categories_And_Tags.sql
-- Third migration: Add category field to books ALTER TABLE books ADD COLUMN category VARCHAR(100);
-- Create tags table for many-to-many relationship CREATE TABLE tags ( id BIGSERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create junction table CREATE TABLE book_tags ( book_id BIGINT NOT NULL, tag_id BIGINT NOT NULL, PRIMARY KEY (book_id, tag_id), FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ); -- Insert some tags INSERT INTO tags (name) VALUES ('Programming'), ('Self-Help'), ('Business'), ('Technology'), ('Fiction');
V4__Create_Students_And_Courses.sql
-- Fourth migration: Create student and course tables CREATE TABLE students ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE courses ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, code VARCHAR(20) UNIQUE NOT NULL, credits INTEGER NOT NULL DEFAULT 3, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE student_course ( student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, grade VARCHAR(2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE ); -- Create indexes CREATE INDEX idx_students_email ON students(email); CREATE INDEX idx_courses_code ON courses(code);
V5__Add_Audit_Columns.sql
-- Fifth migration: Add audit columns to all tables ALTER TABLE books ADD COLUMN created_by VARCHAR(255), ADD COLUMN updated_by VARCHAR(255);
ALTER TABLE authors ADD COLUMN created_by VARCHAR(255), ADD COLUMN updated_by VARCHAR(255); -- Create trigger function for updated_at CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Create triggers CREATE TRIGGER update_books_updated_at BEFORE UPDATE ON books FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); CREATE TRIGGER update_authors_updated_at BEFORE UPDATE ON authors FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); ``` ### Flyway Migration Naming Convention ```
@EntityGraph(attributePaths = {"books"}) @Query("SELECT a FROM Author a") List<Author> findAllWithBooks();
Exposing Entities Directly in APIs
The Problem
@RestController public class BadBookController {
@GetMapping("/books/{id}") public ResponseEntity<Book> getBook(@PathVariable Long id) { Book book = bookRepository.findById(id).orElseThrow(); return ResponseEntity.ok(book); // ⚠️ DON'T DO THIS! } }
Why is this bad?
Exposes your internal data structure
Can cause lazy loading exceptions
May leak sensitive information
Makes API changes difficult
Solution: Use DTOs
// Create DTO classes @Data @Builder public class BookDTO { private Long id; private String title; private String isbn; private Double price; private AuthorDTO author; }
@Data @Builder public class AuthorDTO { private Long id; private String name; private String email; } // Service layer @Service public class BookService {
@Autowired private BookRepository bookRepository;
public BookDTO getBookById(Long id) { Book book = bookRepository.findById(id) .orElseThrow(() -> new ResourceNotFoundException("Book not found"));
@GetMapping("/{id}") public ResponseEntity<BookDTO> getBook(@PathVariable Long id) { BookDTO book = bookService.getBookById(id); return ResponseEntity.ok(book); } }
Proper Exception Handling
Create Custom Exceptions
public class ResourceNotFoundException extends RuntimeException { public ResourceNotFoundException(String message) { super(message); } }
public class DuplicateResourceException extends RuntimeException { public DuplicateResourceException(String message) { super(message); } }
Global Exception Handler
@RestControllerAdvice public class GlobalExceptionHandler {
Java enthusiast crafting code with passion. enthusiastic about creating robust, scalable applications. Let's code, innovate, and build the future together!
