Hibernate sql查询转为对象

 hibernate 查询转换为指定的Bean,自带AliasToBeanResultTransformer必须把数据库列指定别外与Bean的属性相同,自定义实现数据库列直接转为属性

• 自定义转换类ToBeanResultTransformer

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Arrays;

import org.hibernate.HibernateException;
import org.hibernate.property.BasicPropertyAccessor.BasicSetter;
import org.hibernate.property.ChainedPropertyAccessor;
import org.hibernate.property.PropertyAccessor;
import org.hibernate.property.PropertyAccessorFactory;
import org.hibernate.property.Setter;
import org.hibernate.transform.AliasedTupleSubsetResultTransformer;

/**
 * 数据库字段到实体属性的转换
 * 
 * @author xulin
 * @since 2017年3月22日 下午5:48:55
 */
public class ToBeanResultTransformer extends AliasedTupleSubsetResultTransformer {

	private static final long serialVersionUID = 1L;
	
	@SuppressWarnings("rawtypes")
	private final Class resultClass;
	private boolean isInitialized;
	private String[] aliases;
	private Setter[] setters;

	@SuppressWarnings("rawtypes")
	public ToBeanResultTransformer(Class resultClass) {
		if ( resultClass == null ) {
			throw new IllegalArgumentException( "resultClass cannot be null" );
		}
		isInitialized = false;
		this.resultClass = resultClass;
	}

	@Override
	public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
		return false;
	}

	@Override
	public Object transformTuple(Object[] tuple, String[] aliases) {
		Object result;

		try {
			if ( ! isInitialized ) {
				initialize( aliases );
			}
			else {
				check( aliases );
			}
			
			result = resultClass.newInstance();

			for ( int i = 0; i < aliases.length; i++ ) {
				if ( setters[i] != null ) {
					
					Setter setter=setters[i];
					
					Object value=tuple[i];
					if(value!=null){
						
						//处理数据库类型和java数据类型转换
						Class<?> paramType=setter.getMethod().getParameterTypes()[0];
						if(!paramType.isInstance(value)){
							//1,BigInteger to Long
							if(BigInteger.class.isInstance(value)){
								BigInteger bintValue=(BigInteger)value;
								if(Long.class.isAssignableFrom(paramType) || long.class.isAssignableFrom(paramType)){
									tuple[i]=bintValue.longValue();
								}
							}else if(BigDecimal.class.isInstance(value)){
								BigDecimal bdecValue=(BigDecimal)value;
								if(Double.class.isAssignableFrom(paramType) || double.class.isAssignableFrom(paramType)){
									tuple[i]=bdecValue.doubleValue();
								}
							}else if(Character.class.isInstance(value)){
								Character charValue=(Character)value;
								if(String.class.isAssignableFrom(paramType)){
									tuple[i]=charValue.toString();
								}
							}
						}
					}
					
					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;
	}
	
	private String toCamelCase(String s) {
		if (s == null) {
			return null;
		}
		if (s.indexOf('_') == -1) return s;
		
		s = s.toLowerCase();

		StringBuilder sb = new StringBuilder(s.length());
		boolean upperCase = false;
		for (int i = 0; i < s.length(); i++) {
			char c = s.charAt(i);

			if (c == '_') {
				upperCase = true;
			} else if (upperCase) {
				sb.append(Character.toUpperCase(c));
				upperCase = false;
			} else {
				sb.append(c);
			}
		}

		return sb.toString();
	}
   
	private void initialize(String[] aliases) {
		
		PropertyAccessor propertyAccessor = new ChainedPropertyAccessor(
				new PropertyAccessor[] {
						PropertyAccessorFactory.getPropertyAccessor( resultClass, null ),
						PropertyAccessorFactory.getPropertyAccessor( "field" )
				}
		);
		this.aliases = new String[ aliases.length ];
		setters = new Setter[ aliases.length ];
		for ( int i = 0; i < aliases.length; i++ ) {
			String alias = aliases[ i ];
			if ( alias != null ) {
				this.aliases[ i ] = alias;
				setters[ i ] = propertyAccessor.getSetter( resultClass, toCamelCase(alias));
			}
		}
		isInitialized = true;
	}

	private void check(String[] aliases) {
		if ( ! Arrays.equals( aliases, this.aliases ) ) {
			throw new IllegalStateException(
					"aliases are different from what is cached; aliases=" + Arrays.asList( aliases ) +
							" cached=" + Arrays.asList( this.aliases ) );
		}
	}

	@Override
	public boolean equals(Object o) {
		if ( this == o ) {
			return true;
		}
		if ( o == null || getClass() != o.getClass() ) {
			return false;
		}

		ToBeanResultTransformer that = (ToBeanResultTransformer ) o;

		if ( ! resultClass.equals( that.resultClass ) ) {
			return false;
		}
		if ( ! Arrays.equals( aliases, that.aliases ) ) {
			return false;
		}

		return true;
	}

	@Override
	public int hashCode() {
		int result = resultClass.hashCode();
		result = 31 * result + ( aliases != null ? Arrays.hashCode( aliases ) : 0 );
		return result;
	}
}

•  查询基类

import java.math.BigInteger;
import java.util.Collections;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.util.Assert;

/**
 * 基础查询扩展
 * 
 * @author xulin
 * @since 2017年3月22日 下午2:01:19
 */
public abstract class BaseRepository {
	private static final Log LOGGER = LogFactory.getLog(BaseRepository.class);

	/**
	 * 查询记录条数
	 * 
	 * @param sql 查询语句
	 * @return 条数
	 */
	protected long countBySql(String sql) {
		Assert.hasText(sql);
		int index = StringUtils.indexOfIgnoreCase(sql, " FROM ");
		
		Assert.isTrue(index > -1, "SQL must has ' FROM ' sentence.");
		String countSql = "SELECT COUNT(*) " + sql.substring(index - 1);

		if (LOGGER.isInfoEnabled()) LOGGER.info("COUNT SQL : " + countSql);
		BigInteger bint = (BigInteger) getEntityManager().createNativeQuery(countSql).getSingleResult();

		return bint.longValue();
	}

	/**
	 * 查询列表,
	 * 
	 * @param sql 查询语句
	 * @param pageable 分页信息
	 * @param resultMap 是否返回Map对象
	 * @return 返回List,记录为对象数组或对象Map
	 */
	protected List<?> findListBySql(String sql, Pageable pageable, boolean resultMap) {

		if (LOGGER.isInfoEnabled()) LOGGER.info("SQL : " + sql);
		Query query = getEntityManager().createNativeQuery(sql);
		if (pageable != null) {
			query.setFirstResult(pageable.getOffset());
			query.setMaxResults(pageable.getPageSize());
		}
		if (resultMap) query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

		return query.getResultList();
	}

	/**
	 * 查询分页,返回
	 * 
	 * @param sql SQL查询语句
	 * @param pageable 分布对象
	 * @param resultMap 是否返回Map对象
	 * @return Page对象,记录为对象数组或对象Map
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	protected Page<?> findPageBySql(String sql, Pageable pageable, boolean resultMap) {

		// 获取总数
		long count = countBySql(sql);
		if (count == 0) {
			return new PageImpl(Collections.EMPTY_LIST, pageable, 0);
		}

		// 获取列表
		List<?> list = findListBySql(sql, pageable, resultMap);

		return new PageImpl(list, pageable, count);
	}

	/**
	 * 查询列表
	 * 
	 * @param sql
	 * @param pageable 分布信息
	 * @param resultType 封装类型
	 * @return 对象列表
	 */
	@SuppressWarnings("unchecked")
	protected <T> List<T> findListBySqlToBean(String sql, Pageable pageable, Class<T> resultType) {

		if (LOGGER.isInfoEnabled()) LOGGER.info("SQL : " + sql);
		Query query = getEntityManager().createNativeQuery(sql);
		// 分页信息
		if (pageable != null) {
			query.setFirstResult(pageable.getOffset());
			query.setMaxResults(pageable.getPageSize());
		}
		// 封装对象
		query.unwrap(SQLQuery.class).setResultTransformer(new ToBeanResultTransformer(resultType));

		return query.getResultList();
	}

	/**
	 * 查询分页
	 * 
	 * @param sql
	 * @param pageable 分布信息
	 * @param resultType 封装类型
	 * @return 分页对象
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	protected <T> Page<T> findPageBySqlToBean(String sql, Pageable pageable, Class<T> resultType) {

		// 获取总数
		long count = countBySql(sql);
		if (count == 0) {
			return new PageImpl(Collections.EMPTY_LIST, pageable, 0);
		}

		// 获取列表
		List<?> list = findListBySqlToBean(sql, pageable, resultType);

		return new PageImpl(list, pageable, count);
	}

	protected abstract EntityManager getEntityManager();

}

 

转载于:https://my.oschina.net/xl76832215/blog/868594