hibernate SQLQuey Query

最新推荐文章于 2024-09-21 10:38:43 发布

xls51314202005

最新推荐文章于 2024-09-21 10:38:43 发布

阅读量150

点赞数

文章标签： java 人工智能数据库

本文链接：https://blog.csdn.net/xls51314202005/article/details/84755127

版权


@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类型的集合