レポート日、週、月、年springbootデータクエリー

2377 ワード

idea レポートデータ照会

service.java   
/**
 *  
 * @param type   1： ；2： ：3：  4： 
 * @param startDay  
 * @param endDay  
 * @return list
 */
public List countNumber(int type, String startDay, String endDay) {
    if (StringUtils.isEmpty(startDay)){
        startDay = "2017-11-13";
    }
    if (StringUtils.isEmpty(endDay)){
        endDay = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
    }
    List list;
    switch(type){
        case 1:
            list= authenticationRepository.countDay(startDay, endDay);
            break;
        case 2:
            list= authenticationRepository.countWeek(startDay, endDay);
            break;
        case 3:
            list=  authenticationRepository.countMonth(startDay, endDay);
            break;
        default:
            list=  authenticationRepository.countYear(startDay, endDay);
            break;
    }
    System.out.println("=================");
    List listVo = new ArrayList<>();
    list.forEach(item ->{
        CountNumberVO vo = new CountNumberVO();
        try{
            BeanUtils.populate(vo, item);
        }catch (Exception e){
            e.printStackTrace();
        }
        listVo.add(vo);
    });
    return listVo;
}

Repository.java

@Query(value = "SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) date, COUNT( * ) number FROM register_user  where create_time BETWEEN ?1 and ?2 GROUP BY date; ",nativeQuery = true)
List countDay(String startDay, String endDay);

@Query(value = "select DATE_FORMAT(create_time,'%Y-%u') date,count(*) number from  register_user where create_time BETWEEN ?1 and ?2 group by date",nativeQuery = true)
List  countWeek(String startDay, String endDay);

@Query(value = "select DATE_FORMAT(create_time,'%Y-%m') date,count(*) number from register_user where create_time BETWEEN  ?1 and ?2 group by date;",nativeQuery = true)
List countMonth(String startDay, String endDay);

@Query(value = "select date_format(create_time, '%Y') date, count(*) number from register_user where create_time BETWEEN  ?1 and ?2 group by date;",nativeQuery = true)
List countYear(String startDay, String endDay);

帆ソフトレポート内蔵hsqlデータベースfinedb接続方式

CSS