Announcement Announcement Module
No announcement yet.
Custom Query on repository? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Custom Query on repository?

    So I'd like to write a sql that will do some counting on a particular table. That table has a repository for it and I'd like to create a method in that repository to handle that.

    For example:
    public interface EmailHistoryRepository extends JpaRepository<EmailHistory, Long> {
    	@Query("select email_date, count(1) as sent from email_history group by email_date")
    	public Iterable<SomeCountObject> getHistoryCounts();
    Is this possible? I saw something about writing a separate interface and implementation to handle that, but do I need to write any code when I already have the sql in the @Query annotation? I'm assuming I can't do it the way it is above because this EmailHistoryRepository is only returning EmailHist objects.

    I did some google-ing but haven't found anything similar to what I'm looking to do so far.

  • #2
    Gotten further but doesn't feel right.

    I am able to get the interface partially working using nativeQuery=true, but I think I am missing a piece. Here is what I would like it to look like:
    public interface EmailHistoryRepository extends JpaRepository<EmailHistory, Long> {
    @Query(nativeQuery = true, value="SELECT TO_CHAR(email_date, 'yyyy-MM-dd') AS sentDate, COUNT(email_date) AS sentCount FROM email_history GROUP BY TO_CHAR(email_date, 'yyyy-MM-dd') ORDER BY TO_CHAR(email_date, 'yyyy-MM-dd') DESC")
    	public Iterable<EmailHistoryCounts> getSentCounts();
    When I try this approach, I get ClassCastException - it says Object cannot be cast to the EmailHistoryCounts.

    What I CAN do is change the signature to this:
    public Iterable<Object[]> getSentCounts();
    And then in my code that calls that method populate my EmailHistoryCounts object.
    Iterable<Object[]> counts = ehr.getSentCounts();
    List<EmailHistoryCounts> countArray = new ArrayList<EmailHistoryCounts>();
    for (Object[] obj : counts) {
    	EmailHistoryCounts ehc = new EmailHistoryCounts();
    	ehc.setSentDate((String) obj[0]);
    	ehc.setSentCount((BigDecimal) obj[1]);
    Any idea on why it can't cast the results to the EmailHistoryCounts?


    • #3
      Further investigation seems this is probably more Hibernate related than Spring Data.