Hibernate查询返回自定义对象

    /**
     * Convert query result to vo list util class.
     */
    class AliasToBeanResultTransformer implements ResultTransformer {
        private static final long serialVersionUID = -5199190581393587893L;
        private final Class<T> resultClass;
        private Setter[] setters;
        private PropertyAccessor propertyAccessor;
        public AliasToBeanResultTransformer(Class<T> resultClass) {
            if (resultClass == null) throw new IllegalArgumentException("resultClass cannot be null");
            this.resultClass = resultClass;
            propertyAccessor = new ChainedPropertyAccessor(new PropertyAccessor[]{PropertyAccessorFactory.getPropertyAccessor(resultClass, null), PropertyAccessorFactory.getPropertyAccessor("field")});
        }
        public Object transformTuple(Object[] tuple, String[] aliases) {
            Object result;
            try {
                if (setters == null) {
                    setters = new Setter[aliases.length];
                    for (int i = 0; i < aliases.length; i++) {
                        String alias = convertColumnToProperty(aliases[i]);
                        if (alias != null) {
                            try {
                                setters[i] = propertyAccessor.getSetter(resultClass, alias);
                            } catch (PropertyNotFoundException e) {
                                continue;
                            }
                        }
                    }
                }
                result = resultClass.newInstance();
                for (int i = 0; i < aliases.length; i++) {
                    if (setters[i] != null) {
                        setters[i].set(result, tuple[i], null);
                    }
                }
            } catch (InstantiationException e) {
                throw new HibernateException("Could not instantiate resultclass: " + resultClass.getName());
            } catch (IllegalAccessException e) {
                throw new HibernateException("Could not instantiate resultclass: " + resultClass.getName());
            }
            return result;
        }
        /**
         * Converts the specified 'XXX_YYY_ZZZ'-like column name to its
         * 'xxxYyyZzz'-like Java property name.
         *
         * @param columnName the column name
         * @return the Java property name
         */
        public String convertColumnToProperty(String columnName) {
            columnName = columnName.toLowerCase();
            StringBuffer buff = new StringBuffer(columnName.length());
            StringTokenizer st = new StringTokenizer(columnName, "_");
            while (st.hasMoreTokens()) {
                buff.append(StringUtils.capitalize(st.nextToken()));
            }
            buff.setCharAt(0, Character.toLowerCase(buff.charAt(0)));
            return buff.toString();
        }
        @SuppressWarnings({ "rawtypes", "unchecked" })
		public List<T> transformList(List collection) {
            return collection;
	}
}

 

然后使用:

@SuppressWarnings("unchecked")
	public Collection<Table> findBy(final QueryObj queryObj) {
		final String sql = "SELECT nvl(t.e_year, '无') AS year,l.m AS month,nvl(t.status, 0) as status,"
			+ " t.create_emp_code as createEmpCode,t.create_date as createDate," 
			+ " t.update_emp_code as updateEmpCode,t.update_date as updateDate"
			+ " FROM (SELECT LEVEL m FROM dual CONNECT BY LEVEL <= 12) l"
			+ " LEFT OUTER JOIN table t ON l.m = t.e_month AND t.e_year = ? AND t.e_page = ?"
			+ " ORDER BY l.m ";
		final String year = "year";
		final Long page = 1;
		return (Collection<Table>) super.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						SQLQuery query = session
								.createSQLQuery(sql);
						query.setString(0, year);
						query.setLong(1, page);
						//这里用addScalar指定查询结果集字段转换,尤其是用到sql函数时第一个参数是as 别名,第二个是返回值的类型
						query.addScalar("year", Hibernate.STRING);
						query.addScalar("month", Hibernate.STRING);
						query.addScalar("status", Hibernate.LONG);
						query.addScalar("createEmpCode", Hibernate.STRING);
						query.addScalar("updateEmpCode", Hibernate.STRING);
						query.addScalar("createDate", Hibernate.DATE);
						query.addScalar("updateDate", Hibernate.DATE);
						//关键转换方法query.setResultTransformer,参数AliasToBeanResultTransformer(映射到的POJO类).
						query.setResultTransformer(new AliasToBeanResultTransformer(Table.class));
						//OK hibernate会自动映射过去,如果不用这个方法它返回的时候是Collection<Object>,所以不能起到转换的作用,在JBoss中运行查询时会报错的.
						return query.list();
					}
				});
	}

 

 PS:最后注意如果使用hibernate4 ，那么会替代hibernate3 的Hibernate.INTEGER的用法，

在Hibernate4中使用的方法是 StandardBasicTypes.INTEGER用法

评论

1 楼 olddoor 2015-12-24  

要不要这么帅