Writing Custom Repositories with ClickHouse in Spring Boot

November 26, 2025

This guide shows how to create custom repositories in Spring Boot with ClickHouse for flexible query handling.

1. Define an Entity

Create a simple entity mapped to a ClickHouse table:

@Entity
@Table(name = "sales")
public class Sales {
    @Id
    private Long id;
    private String product;
    private Double amount;
    private LocalDate orderDate;
}

2. Create a Base Repository

Extend JpaRepository for basic operations:

public interface SalesRepository extends JpaRepository<Sales, Long>, SalesCustomRepository {
}

3. Define a Custom Repository Interface

public interface SalesCustomRepository {
    List<Sales> findTopProductsByRevenue(int limit);
}

4. Implement the Custom Repository

Use EntityManager or JdbcTemplate for custom queries:

@Repository
public class SalesCustomRepositoryImpl implements SalesCustomRepository {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Sales> findTopProductsByRevenue(int limit) {
        String sql = "SELECT * FROM sales ORDER BY amount DESC LIMIT :limit";
        return em.createNativeQuery(sql, Sales.class)
                 .setParameter("limit", limit)
                 .getResultList();
    }
}

5. Use the Repository in Services

@Service
public class SalesService {

    private final SalesRepository repository;

    public SalesService(SalesRepository repository) {
        this.repository = repository;
    }

    public List<Sales> getTopProducts(int limit) {
        return repository.findTopProductsByRevenue(limit);
    }
}

6. Expose an Endpoint

@RestController
@RequestMapping("/sales")
public class SalesController {

    private final SalesService service;

    public SalesController(SalesService service) {
        this.service = service;
    }

    @GetMapping("/top")
    public List<Sales> getTopProducts(@RequestParam int limit) {
        return service.getTopProducts(limit);
    }
}