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:
Code:
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.
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:
Code:
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:
Code:
public Iterable<Object[]> getSentCounts();
And then in my code that calls that method populate my EmailHistoryCounts object.
Code:
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]);
countArray.add(ehc);
}
Any idea on why it can't cast the results to the EmailHistoryCounts?