项目用到的sql

来源：互联网发布：java round up 编辑：程序博客网时间：2024/05/16 23:02

根据创建时间的年月日去查询：

select substr(created_time,1,10), count(*) from sync_recharge_log where recharge_type = 22 group by substr(created_time,1,10) ;

    @Override
    public Map<String, Long> querySyncRechargeLogNum(final RechargeType rechargeType) {
        return (Map<String, Long>)getHibernateTemplate().execute(new HibernateCallback<Object>() {
            public Object doInHibernate(Session session)
                    throws HibernateException {
                //StringBuffer hql = new StringBuffer("select sum(amount) as amount0, sum(payAmount) as payAmount1 from SyncRechargeLog srl ");
                StringBuffer hql = new StringBuffer("select substr(created_time,1,10),count(*) from sync_recharge_log srl ");
                hql.append(" where 1 = 1 ");
                if (rechargeType != null && rechargeType.getValue() != RechargeType.ALL.getValue()) {
                    hql.append(" and srl.recharge_type = \"").append(rechargeType.getValue()).append("\" ");
                }
                hql.append(" group by substr(created_time,1,10) " );
                logger.info("hql=" + hql.toString());
                //Query query = session.createQuery(hql.toString());
                Query query = session.createSQLQuery(hql.toString());
                logger.info("sql=" + hql.toString());
                Map<String, Long> map = new HashMap<String, Long>();
                List<Object[]> list = query.list();
                if (list != null) {
                    for (Object[] obj : list) {
                        map.put(obj[0] + "", Long.valueOf(obj[1] + ""));
                    }
                }
                return map;
            }
        });
    }

0 0