Native Queries

A native query is plain SQL for your database. Use it for database-specific features: full-text search, window functions, JSON columns, ON DUPLICATE KEY, recursive CTEs, or a hand-tuned report.

public interface ProductRepository extends JpaRepository<Product, Long> {

    // MySQL full-text search (needs FULLTEXT INDEX on name, description)
    @Query(value = """
           select * from products
           where match(name, description) against (:q in natural language mode)
           limit 20
           """, nativeQuery = true)
    List<Product> fullTextSearch(@Param("q") String q);
}

Spring Data cannot always derive a count query from native SQL. Give it a countQuery:

@Query(value = """
       select p.* from products p
       join categories c on c.id = p.category_id
       where c.name = :category
       """,
       countQuery = """
       select count(*) from products p
       join categories c on c.id = p.category_id
       where c.name = :category
       """,
       nativeQuery = true)
Page<Product> findByCategoryNative(@Param("category") String category, Pageable pageable);

Native queries can return interface projections. Column aliases must match the getter names.

public interface TopSeller {
    Long getProductId();
    String getName();
    Long getUnitsSold();
    Integer getRankInCategory();
}

@Query(value = """
       select p.id as productId, p.name as name, sum(i.quantity) as unitsSold,
              rank() over (partition by p.category_id order by sum(i.quantity) desc) as rankInCategory
       from order_items i join products p on p.id = i.product_id
       group by p.id, p.name, p.category_id
       """, nativeQuery = true)
List<TopSeller> topSellers();