vendredi 10 juillet 2015

How to write Hibernate group by query from jpaRepository?

I have webapp with CRUD operation , now I need to execute this query :

select CAST(al.access_date AS DATE) as datum, meal_type ,    count(meal_type) 
       FROM access_log al GROUP BY  CAST(al.access_date AS DATE) ,    meal_type

I can't to find on which way I need to running query like this and where to write?

I have Repository:

@Repository
public interface AccessLogRepository extends JpaRepository<AccessLogModel, String>{

 AccessLog findByName(String name);

 @Modifying
 @Query("select CAST(al.access_date AS DATE) as datum, meal_type ,     count(meal_type) from access_log al GROUP BY  CAST(al.access_date AS DATE) ,    meal_type")
 List<AccessLogModel> getReports()  
 }

Service interface :

public interface AccessLogService {

AccessLog findOne(int id);
List<AccessLog > findAll();
void save(AccessLog modul);
void remove(int id);
AccessLog findByName(String name);

}

And service Impl:

@Service
public class JpaAccessLogServiceImpl implements AccessLogService {

@Autowired
AccessLogRepository accessLogRepository;

@Override
public AccessLog findOne(int id) {
    return accessLogRepository.findOne(id);
}
@Override
public AccessLog findByName(String name) {
    return accessLogRepository.findByName(name);
} .......

But I dont't know how to write and run this query, on which way? Do I need to write new Class for that ? This query work in DB.

select CAST(al.access_date AS DATE) as datum, meal_type ,    count(meal_type) 
   FROM access_log al GROUP BY  CAST(al.access_date AS DATE) ,    meal_type

Aucun commentaire:

Enregistrer un commentaire