1

I need to query some count per day and i would like to create named native query that will return this in map. How can i do that ?

<named-native-query name="getLeadNumberByDayInDateRange" result-set-mapping="map">
            <query>
                SELECT addeddate, 
                       Count(DISTINCT campaignid, email) AS count 
                FROM   leads 
                GROUP  BY addeddate   
            </query>
        </named-native-query>

So i am writing query in xml and i would like to add this into JpaRepository : 

@Query
public Map<LocalDateTime, Integer> getLeadNumberByDayInDateRange();
user3364181
  • 531
  • 3
  • 14
  • 32

1 Answers1

2

To do that with named queries one can do something like:

@Query("SELECT l.addeddate, Count(DISTINCT l.campaignid)
        FROM leads l 
        GROUP BY l.addeddate")
public Map<LocalDateTime, Integer> getLeadNumberByDayInDateRange();

or something like:

@NamedQuery(name = "getLeadNumberByDayInDateRange", 
query = "SELECT l.addeddate, Count(DISTINCT l.campaignid)
         FROM leads l 
         GROUP BY l.addeddate")
public Map<LocalDateTime, Integer> getLeadNumberByDayInDateRange();

Or if you want some custom mappings, you can use [sql result set mappings].1

Turbut Alin
  • 2,568
  • 1
  • 21
  • 30
  • When i try this i get : Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found ',' near line 1, column 48 [SELECT l.addeddate, Count(DISTINCT l.campaignid, l.email) as count FROM leads l GROUP BY l.addeddate]. Can you help me do that in xml ? – user3364181 Oct 19 '17 at 14:01
  • well i need unique combination of those 2 values so i have to give it 2 params – user3364181 Oct 21 '17 at 10:46