hibernate SQLQuey Query

亦凉 2022-04-12 11:51 148阅读 0赞

@Override
    public <T> List<T> getDtoBySql(final String sql,  List<?> param, Class<T> dtoClass,  Map<String, org.hibernate.type.Type> map) throws HibernateException{
       
    	Query query = this.getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(dtoClass));
    	for(Map.Entry<String, org.hibernate.type.Type> entry : map.entrySet()){
       
    		if(StringUtils.isNotEmpty(entry.getKey()) && null != entry.getValue()){
       
    			((SQLQuery) query).addScalar(entry.getKey(), entry.getValue());
    		}
    	}
    	addQueryParams(query,param);
    	return (List<T>)query.list();
    }
    
    public <T> List<T> getDtoBySql(final String sql,  List<?> param, Class<T> dtoClass) throws HibernateException{
       
    		Query query = this.getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(dtoClass));
    		addQueryParams(query,param);
    		return (List<T>)query.list();
    	}

Note:  
1. SQLQuey.setResultTransformer(ResultTransformer transformer) , transformer=Transformers.aliasToBean(dtoClass)如果dtoClass不是一个Entity，那么处理后接口查询出来的字段对应的java属性类型与数据库类型相对应，hibernate不会自动转换，如果DB里的字段类型是BigInt那么java对应的类型必须是BigInteger,或者显示进行转换，或者addScalar(String columnAlias, Type type);指定转换规则。如果不指定的话返回的是Object类型的集合，字段类型与数据库字段一致；但如果dtoClass是Entity，那么就可以不使用addScalar指定字段转换规则。  
2. public Query setResultTransformer(ResultTransformer transformer);  
通常是SQLQuery进行查询时将结果映射成任意指定类型。  
但是sql语句必须给每个字段显示的指定别名，形如  
select id as id,name as name,version as version,extra as extra from Customer；  
否则会抛出异常，如下：  
Exception in thread "main" org.hibernate.PropertyNotFoundException: Could not find setter for 3 on class com.muhardin.endy.belajar.hibernate.entity.Customer  
at org.hibernate.property.ChainedPropertyAccessor.getSetter(ChainedPropertyAccessor.java:66)  
at org.hibernate.transform.AliasToBeanResultTransformer.initialize(AliasToBeanResultTransformer.java:121)  
at org.hibernate.transform.AliasToBeanResultTransformer.transformTuple(AliasToBeanResultTransformer.java:84)  
at org.hibernate.hql.internal.HolderInstantiator.instantiate(HolderInstantiator.java:95)  
at org.hibernate.loader.hql.QueryLoader.getResultList(QueryLoader.java:464)  
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)  
at org.hibernate.loader.Loader.list(Loader.java:2364)  
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496)  
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)  
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231)  
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)  
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)  
at com.muhardin.endy.belajar.hibernate.dao.CustomerDao.getDtoByHql2(CustomerDao.java:125)  
at com.muhardin.endy.belajar.hibernate.dao.CustomerDao$$FastClassBySpringCGLIB$$495b970a.invoke(<generated>)  
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)  
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)  
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)  
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)  
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)  
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)  
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)  
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)  
at com.muhardin.endy.belajar.hibernate.dao.CustomerDao$$EnhancerBySpringCGLIB$$6169254c.getDtoByHql2(<generated>)  
at com.muhardin.endy.belajar.hibernate.App3.main(App3.java:63)  
  
3. Query 接口返回的类型应该是Entity，合法的Entity必须有有主键，必须有默认的构造函数。

Query query = sessionFactory.getCurrentSession().createQuery(hql).setResultTransformer(Transformers.aliasToBean(dtoClass));

Query query = sessionFactory.getCurrentSession().createQuery(hql)；  
这种写法是可以的但其实没有必要，因为hql查询的结果会自动与Entity进行映射，如果这样用了，那么hql语句也必须给每个字段取别名，否则会抛出异常。

List<Customer> list = dao.getDtoByHql2("select id as id,name as name,version as version,extra  from Customer c where id =? ",param,Customer.class);

4. public SQLQuery addScalar(String columnAlias, Type type);  
一旦使用了这个方法，就应该给所有字段进行处理，否则没有显示处理的字段，默认值将为null，如果为Number类型的则为0.

/**
    	 * 设置查询结果类型
    	 * @param query
    	 * @param resultClass
    	 */
    	private void setResultTransformer(SQLQuery query, Class<?> resultClass){
       
    		if (resultClass != null){
       
    			if (resultClass == Map.class){
       
    				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    			}else if (resultClass == List.class){
       
    				query.setResultTransformer(Transformers.TO_LIST);
    			}else{
       
    				query.addEntity(resultClass);
    			}
    		}
    	}

@SuppressWarnings("unchecked")
    	public  List getDtoByHql(final String hql,  List<?> param, Class dtoClass) throws HibernateException{
       
    		Query query = sessionFactory.getCurrentSession().createQuery(hql);
    		addQueryParams(query,param);
    		return (List)query.list();
    	}
    
    dao.getDtoByHql("select id as id,name as name,version as version,extra as extra from Customer",null,Customer.class);
    返回的是Object类型的集合。
    
    dao.getDtoByHql(" from Customer",null,Customer.class);
    返回的是Customer类型的集合