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 楼 piao_live 2008-05-29  

这位兄弟说得太对了。。。