Custom Query on repository?

[sbirnie]

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.

[sbirnie]

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]);
	countArray.add(ehc);
}

Any idea on why it can't cast the results to the EmailHistoryCounts?

[sbirnie]

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