createSQLQuery,addScalar列名用大写

createSQLQuery,addScalar列名用大写

public List findByGroupByMaterial(String date1,String date2){ 
        try { 
            StringBuffer hql = new StringBuffer(); 
            hql.append("SELECT t.mt_material_id ID, ") 
                .append(" t1.name mt_name, ") 
                .append(" t1.code MT_CODE, ") 
                .append(" SUM(t.num)NUM, ") 
                .append(" SUM(decode((SELECT substr(s.code, ") 
                .append(" 0, ") 
                .append(" 3) ") 
                .append(" FROM im_affair_type s ") 
                .append(" WHERE s.id = t.im_affair_type_id), ") 
                .append(" 'imp', ") 
                .append(" 1, ") 
                .append(" 'exp', ") 
                .append(" -1) * t.num * t.price) MONEY ") 
                .append("FROM im_affair t, ") 
                .append(" mt_material t1 ") 
                .append("WHERE t.mt_material_id = t1.id AND ") 
                   
                .append(" To_date('"+date1+"', ") 
                .append(" 'yyyy-mm-dd') <= ") 
                .append(" t.accountant_date AND ") 
                .append(" t.accountant_date >= ") 
                .append(" To_date('"+date2+"', ") 
                .append(" 'yyyy-mm-dd') ") 
                   
// .append(" t.accountant_date BETWEEN ") 

// .append(" To_date('"+date1+"', ") 

// .append(" 'yyyy-mm-dd') AND ") 

// .append(" To_date('"+date2+"', ") 

// .append(" 'yyyy-mm-dd') ") 

                .append("GROUP BY t.mt_material_id, ") 
                .append(" t1.NAME, ") 
                .append(" t1.code "); 
               
            List list = session.createSQLQuery(hql.toString()) 
            .addScalar("ID", Hibernate.LONG)//这里ID要用大写才对，不然会报列名错误 

            .addScalar("MT_NAME", Hibernate.STRING)// 

            .addScalar("MT_CODE", Hibernate.STRING) 
            .addScalar("NUM", Hibernate.DOUBLE) 
            .addScalar("MONEY", Hibernate.DOUBLE)//同上，都要大写 

            .list(); 
            log.info("findByGroupByMaterial successful and the list size is "+list.size()); 
            return list; 
        } catch (HibernateException e) { 
            // TODO Auto-generated catch block 

            e.printStackTrace(); 
            throw e; 
        } 
    } 

评论

1 楼 fuwang 2009-02-16  

我这里不用大写？为什么？难道是因为我用的oracle?你用的mysql