hibernate 执行本地 sql

最新推荐文章于 2019-06-21 17:19:00 发布

Terry_5008

最新推荐文章于 2019-06-21 17:19:00 发布

阅读量185

点赞数

分类专栏： hibernate 文章标签： SQL Hibernate C C++ C#

本文链接：https://blog.csdn.net/iteye_5008/article/details/81921739

版权

hibernate 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

1.为了把sql查询返回的关系数据映射为对象，需要在sql查询语句中为字段指定别名

String sql = "select cs.ID as{c.id},cs.NAME as {c.name},cs.AGE as {c.age} "
                +" from CUSTOMERS cs  where cs.ID = 1";

Query query = session.createSQLQuery(sql,"c",Customer.class);

createSQLQuery的第二个参数设定为类的别名，上述把Customer类的别名设为"c"，在sql语句中，每个字段的别名形式为"c.XXX"，字段的别名必须位于大括号内。

2.sql对 CUSTOMERS表和ORDERS表进行内连接查询

String sql = "select {c.*},{o.*} from CUSTOMERS c inner join ORDERS o where c.ID = o.CUSTOMER_ID";

Query query = session.createSQLQuery(sql,new String[]{"c","o"},
               new Class[]{Customer.class,Order.class});

3.如果执行带一个参数的 createSQLQuery(sql)，返回结果是对象数组，无映射到实体类，也无需带大括号的别名

4.为了便于维护（如数据库表结构发生变化，必须修改程序代码），把sql查询语句放到映射文件中

session.getNamedQuery("findCustomersAndOrders")

<sql-query name="findCustomersAndOrders">![CDATA[
       select {c.*},{o.*} from CUSTOMERS c inner join ORDERS o where c.ID=o.CUSTOMER_ID ]]>
       <return alias="c" class="Customer"></return>
       <return alias="o" class="Order"></return>
    </sql-query>

5.使用Spring的时候

@Override
public List<ViewRecord> getViewRecordByUserAndRandom6(int userId) {
	final int userIdf = userId;
	List<ViewRecord> viewRecordList = this.getHibernateTemplate().executeFind(new HibernateCallback() {
	public Object doInHibernate(Session session) throws HibernateException, SQLException {
         SQLQuery query = session.createSQLQuery("select * from viewrecord where userId=? order by rand() limit 6");  
	query.setInteger(0, userIdf);
	return query.list();
			}
		});
	return viewRecordList;
	}

6.执行本地 SQL 返回 hibernate 的 Entity List

StringBuilder queryString = new StringBuilder();
		queryString.append("select * from cc_role ");
		queryString.append("where status = 2 ");
		queryString.append("start with id in  ");
		queryString.append("(select r.id from cc_role r, cc_user_role ur  ");
		queryString.append("where ur.user_id = " + parentId.longValue() + " and ur.role_id = r.id and r.status = 2 and ur.status = 2 ");
		queryString.append("and (ur.effective_date is not null and sysdate >= ur.effective_date or ur.effective_date is null) ");
		queryString.append("and (ur.expire_date is not null and sysdate <= ur.expire_date or ur.expire_date is null )) ");
		queryString.append("connect by prior id = parent_id ");
		queryString.append("order siblings by name ");
		
		return getSession().createSQLQuery(queryString.toString()).addEntity("cc_role", Role.class).list();

addEntity("cc_role", Role.class).list()

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "CC_ROLE")
@SequenceGenerator(name = "seq", sequenceName = "CC_ROLE_SEQ")
public class Role implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 4316769641666942491L;

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
	private Long id;
	private Long parent_id;
	private String name;
	private String descr;
	private Long status;
	private Date create_date;
	private Long create_by;
	private Date update_date;
	private Long update_by;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}
	
	public Long getParent_id() {
		return parent_id;
	}

	public void setParent_id(Long parent_id) {
		this.parent_id = parent_id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDescr() {
		return descr;
	}

	public void setDescr(String descr) {
		this.descr = descr;
	}

	public Long getStatus() {
		return status;
	}

	public void setStatus(Long status) {
		this.status = status;
	}

	public Date getCreate_date() {
		return create_date;
	}

	public void setCreate_date(Date create_date) {
		this.create_date = create_date;
	}

	public Long getCreate_by() {
		return create_by;
	}

	public void setCreate_by(Long create_by) {
		this.create_by = create_by;
	}

	public Date getUpdate_date() {
		return update_date;
	}

	public void setUpdate_date(Date update_date) {
		this.update_date = update_date;
	}

	public Long getUpdate_by() {
		return update_by;
	}

	public void setUpdate_by(Long update_by) {
		this.update_by = update_by;
	}

	// 创建人
	public String getCreate_name() {
		Long key=getCreate_by()!=null? getCreate_by():null;

		return CommonHelper.getInstance().getUserName(key);
	}

	// 修改人
	public String getUpdate_name() {
		Long key=getUpdate_by()!=null? getUpdate_by():null;

		return CommonHelper.getInstance().getUserName(key);
	}

}

7.执行本地 SQL 返回封装好的 class。addEntity() 或者 setResultTransformer()

① addEntity()

如果使用原生sql语句进行query查询时，hibernate是不会自动把结果包装成实体的。所以要手动调用addEntity(Class class)等一系列方法。
如session.createSQLQuery(sql).addEntity(Class class);注意hibernate3.0.5不支持，单个参数的addEntity方法

具体的使用方面可以参考本页的（6.）

② setResultTransformer()

另外，hibernate3.2可以对原生sql 查询使用ResultTransformer。这会返回不受Hibernate管理的实体。
session.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
.setResultTransformer(Transformers.aliasToBean(CatDTO.class))

或setResultTransformer(new AliasToBeanResultTransformer (CatDTO.class))

上面的查询将会返回CatDTO的列表,它将被实例化并且将NAME和BIRTHDAY的值注射入对应的属性或者字段。
但必须注意，对每一个列都必须addScalar("列名")

可参考： http://bbs.xml.org.cn/blog/more.asp?name=lhwork&id=15351

参考具体内容如下：

People using the Criteria API have either transparently or knowingly used a ResultTransformer. A ResultTransformer is a nice and simple interface that allows you to transform any Criteria result element. E.g. you can make any Criteria result be returned as a java.util.Map or as a non-entity Bean.

Criteria Transformers
Imagine you have a StudentDTO class:

    public class StudentDTO {
      private String studentName;
      private String courseDescription;
     
      public StudentDTO() { }
         
      ...
    }

Then you can make the Criteria return non-entity classes instead of scalars or entities by applying a ResultTransformer:

 List resultWithAliasedBean = s.createCriteria(Enrolment.class)
      .createAlias("student", "st").createAlias("course", "co")
      .setProjection( Projections.projectionList()
                       .add( Projections.property("st.name"), "studentName" )
                       .add( Projections.property("co.description"), "courseDescription" )
              )
              .setResultTransformer( Transformers.aliasToBean(StudentDTO.class) )
              .list();

     StudentDTO dto = (StudentDTO)resultWithAliasedBean.get(0);

This is how ResultTransformer have been available since we introduced projection to the Criteria API in Hibernate 3.

It is just one example of the built in transformers and users can provide their own transformers if they so please.

Jealous programming

Since I am more a HQL/SQL guy I have been jealous on Criteria for having this feature and I have seen many requests for adding it to all our query facilities.

Today I put an end to this jealousy and introduced ResultTransformer for HQL and SQL in Hibernate 3.2.

HQL Transformers
In HQL we already had a "kind" of result transformers via the ("select new" http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html#queryhql-select) syntax, but for returning non-entity beans it only provided value injection of these beans via its constructor. Thus if you used the same DTO in many different scenarios you could end up having many constructors on this DTO purely for allowing the "select new" functionality to work.

Now you can get the value injected via property methods or fields instead, removing the need for explicit constructors.

List resultWithAliasedBean = s.createQuery(
      "select e.student.name as studentName," +
      "       e.course.description as courseDescription" +
      "from   Enrolment as e")
      .setResultTransformer( Transformers.aliasToBean(StudentDTO.class))
      .list();

    StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);

SQL Transformers

With native sql returning non-entity beans or Map's is often more useful instead of basic Object[]. With result transformers that is now possible.

List resultWithAliasedBean = s.createSQLQuery(
      "SELECT st.name as studentName, co.description as courseDescription " +
      "FROM Enrolment e " +
      "INNER JOIN Student st on e.studentId=st.studentId " +
      "INNER JOIN Course co on e.courseCode=co.courseCode")
      .addScalar("studentName")
      .addScalar("courseDescription")
      .setResultTransformer( Transformers.aliasToBean(StudentDTO.class))
      .list();

    StudentDTO dto =(StudentDTO) resultWithAliasedBean.get(0);

Tip: the addScalar() calls were required on HSQLDB to make it match a property name since it returns column names in all uppercase (e.g. "STUDENTNAME"). This could also be solved with a custom transformer that search the property names instead of using exact match - maybe we should provide a fuzzyAliasToBean() method ;)

Map vs. Object[]
Since you can also use a transformer that return a Map from alias to value/entity (e.g. Transformers.ALIAS_TO_MAP), you are no longer required to mess with index based Object arrays when working with a result.

List iter = s.createQuery(
      "select e.student.name as studentName," +
      "       e.course.description as courseDescription" +
      "from   Enrolment as e")
      .setResultTransformer( Transformers.ALIAS_TO_MAP )
      .iterate();

    String name = (Map)(iter.next()).get("studentName");

Again, this works equally well for Criteria, HQL and native SQL.

Reaching Nirvana of native sql
We still miss a few things, but with the addition of ResultTranformer support for SQL and the other additions lately to the native sql functionality in Hibernate we are close to reach the Nirvana of native sql support.

Combined with StatelessSession you actually now got a very flexible and full powered "sql executor" which transparently can map to and from objects with native sql without any ORM overhead.

...and when you get tired of managing the sql, objectstate, lifecycles, caching etc. of your objects manually and want to benefit from the power of an ORM then you got it all readily available to you ;)

8.根据（7.）的描述，自己封装了通过 sping hibernate 执行本地 SQL

创建继承的HibernateCallback的类

可以返回hibernate 定义的 Entity list，也可以是自己封装的 javaBean list

@SuppressWarnings("rawtypes")
public class HibernateCallbackImpl<T extends Collection> implements
		HibernateCallback<T> {


	public static Log log = LogFactory.getLog(ComHibernateCallbackImpl.class);

	/** 要执行的 SQL */
	private String sql;
	/** 参数 */
	private Object[] params;

	/** hibernate entity */
	private Class entityClass;

	/** 自己封装的Bean */
	private Class javaBeanClass;
	/** 如果是自己的 javabean 需要传参数 */
	private List<String> javaBeanParams;

	/** 分页：index */
	private Integer index;
	/** 分页：size */
	private Integer size;

	public ComHibernateCallbackImpl() {

	}

	/**
	 * 返回 hibernate entity
	 * 
	 * @param sql
	 * @param params
	 * @param entityClass
	 */
	public ComHibernateCallbackImpl(String sql, Object[] params,
			Class entityClass) {
		this.sql = sql;
		this.params = params;
		this.entityClass = entityClass;
	}

	/**
	 * 返回 hibernate entity，分页
	 * 
	 * @param sql
	 * @param params
	 * @param entityClass
	 * @param index
	 * @param size
	 */
	public ComHibernateCallbackImpl(String sql, Object[] params,
			Class entityClass, Integer index, Integer size) {
		this.sql = sql;
		this.params = params;
		this.entityClass = entityClass;
		this.index = index;
		this.size = size;
	}

	/**
	 * 返回自己的 javabean，分页
	 * 
	 * @param sql
	 * @param params
	 * @param javaBeanClass
	 * @param javaBeanParams
	 * @param index
	 * @param size
	 */
	public ComHibernateCallbackImpl(String sql, Object[] params,
			Class javaBeanClass, List<String> javaBeanParams, Integer index,
			Integer size) {
		this.sql = sql;
		this.params = params;
		this.javaBeanClass = javaBeanClass;
		this.javaBeanParams = javaBeanParams;
		this.index = index;
		this.size = size;
	}

	/**
	 * 返回自己的 javabean
	 * 
	 * @param sql
	 * @param params
	 * @param javaBeanClass
	 * @param javaBeanParams
	 */
	public ComHibernateCallbackImpl(String sql, Object[] params,
			Class javaBeanClass, List<String> javaBeanParams) {
		this.sql = sql;
		this.params = params;
		this.javaBeanClass = javaBeanClass;
		this.javaBeanParams = javaBeanParams;
	}

	/**
	 * callback 执行本地SQL<br>
	 * 返回类型 可以是 hibernate 定义的 Entity list，也可以是自己封装的 javaBean list
	 * 
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	@Override
	public T doInHibernate(Session session) throws HibernateException,
			SQLException {
		if (StringUtils.isBlank(this.sql)) {
			HibernateException e = new HibernateException("SQL不能为空");
			log.error("SQL不能为空！！！", e);
			throw e;
		}
		SQLQuery query = session.createSQLQuery(this.sql);
		// 参数设定
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				Object obj = params[i];
				if (obj instanceof Long) {
					query.setLong(i, (Long) obj);
				} else if (obj instanceof Date) {
					query.setDate(i, (Date) obj);
				} else if (obj instanceof Double) {
					query.setDouble(i, (Double) obj);
				} else if (obj instanceof String) {
					query.setString(i, (String) obj);
				} else {
					query.setParameter(i, obj);
				}
			}
		}

		// 是否分页查询
		if (index != null && size != null) {
			query.setFirstResult(this.index);
			query.setMaxResults(this.size);

		}

		if (this.entityClass != null) {
			query.addEntity(entityClass);
		} else if (this.javaBeanClass != null) {
			if (this.javaBeanParams != null) {
				for (String str : javaBeanParams) {
					// 这里设置返回类型，强制为 String。
					// 如果做的好一点，可以在javaBeanParams里存一个对象，该对象有返回的列名和返回列的类型。现在因为时间不足，直接返回String
					query.addScalar(str, StringType.INSTANCE);
				}
			}

			query.setResultTransformer(Transformers.aliasToBean(javaBeanClass));
		} else {
			HibernateException e = new HibernateException("没有指定hibernate的返回类型！");
			log.error("没有指定hibernate的返回类型！", e);
			throw e;
		}

		return (T) query.list();
	}

	// ****************************
	// *设置 setter getter 方法
	// ****************************
	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	public Object[] getParams() {
		return params;
	}

	public void setParams(Object[] params) {
		this.params = params;
	}

	public Class getEntityClass() {
		return entityClass;
	}

	public void setEntityClass(Class entityClass) {
		this.entityClass = entityClass;
	}

	public Class getJavaBeanClass() {
		return javaBeanClass;
	}

	public void setJavaBeanClass(Class javaBeanClass) {
		this.javaBeanClass = javaBeanClass;
	}

	public List<String> getJavaBeanParams() {
		return javaBeanParams;
	}

	public void setJavaBeanParams(List<String> javaBeanParams) {
		this.javaBeanParams = javaBeanParams;
	}

	public Integer getIndex() {
		return index;
	}

	public void setIndex(Integer index) {
		this.index = index;
	}

	public Integer getSize() {
		return size;
	}

	public void setSize(Integer size) {
		this.size = size;
	}

	
}

调用返回 hibernate Entity的List

SQL的查询结果中必须覆盖 Entity的全部项目

HibernateCallbackImpl<List<A>> callback = new HibernateCallbackImpl<List<A>>();
		callback.setSql("SQL");
		callback.setEntityClass(A.class);

		List<A> aList = this
				.getHibernateTemplate().execute(callback);

调用返回自己设置的 javabean 的List

SQL的查询结果中必须覆盖 javabean 的全部项目

设置 setJavaBeanParams所有数据，貌似 oracle 以外的数据库，不需要这个设置

oracle 的时候，javabean的属性貌似要全部大写，并且必须设定 addScalar，也就下面的
setJavaBeanParams，这些属性也必须都是大写

http://lisong0624.blog.163.com/blog/static/18871986201152094422549/

HibernateCallbackImpl<List<Test001>> callback = new HibernateCallbackImpl<List<Test001>>();
		//Sql的查询结果必须包含javabean的所有属性项目
		callback.setSql("SQL");
		callback.setJavaBeanClass(A.class);

                List<String> list=  new ArrayList<String>();
                list.add("TESTCOL");//一定要大写
		//设置 javabean 所有数据，貌似 oracle 以外的数据库，不需要这个设置
		callback.setJavaBeanParams(list);
		
		List<Test001> aList = this
				.getHibernateTemplate().execute(callback);

出了以上2种方法，也可以通过HibernateCallbackImpl类的构造函数使用

9.根据（7.）的描述，自己封装了通过 sping hibernate 执行本地 SQL（二）

public class TempSQLQuery {
	/**
	 * 包装的SqlQuery
	 */
	private SQLQuery query;
	
	/**
	 * 是否自动添加hibernate 字段映射信息
	 */
	private boolean autoAddScalar = false;
	
	/**
	 * 当autoAddScalar为false的时候，需要设置需要映射的字段名，区分大小写
	 */
	private List<String> columnList = new ArrayList<String>();
	
	/**
	 * 构造方法
	 * 
	 * @param query
	 * @param autoAddScalar
	 */
	public TempSQLQuery(SQLQuery query, boolean autoAddScalar){
		this.query = query;
		this.autoAddScalar = autoAddScalar;
	}
	
	/**
	 * 构造方法，默认autoAddScalar为true
	 * @param query
	 */
	public TempSQLQuery(SQLQuery query){
		this.query = query;
		this.autoAddScalar = true;
	}
	
	/**
	 * 设置autoAddSaclar
	 * true: 自动添加hibernate 字段映射信息
	 * false: 不自动添加hibernate 字段映射信息，此时需要调用addScalarColumn方法
	 * @param autoAddScalar
	 */
	public void setAutoAddScalar(boolean autoAddScalar){
		this.autoAddScalar = autoAddScalar;
	}
	
	/**
	 * 添加一个hibernate字段映射名
	 * @param column
	 */
	public void addScalarColumn(String column){
		this.columnList.add(column);
	}
	
	/**
	 * 批量添加一批hibernate字段映射名
	 * @param column
	 */
	public void addScalarColumn(List<String> columnList){
		this.columnList.addAll(columnList);
	}
	
	/**
	 * set transformer，这里已经进行了包装，只要传递最终需要转换的实体类的class即可
	 * 
	 * @param target
	 * @return
	 */
	public SQLQuery setResultTransformer(Class<?> target){
		Field[] fields = target.getDeclaredFields();
		for(Field field : fields){
			if(autoAddScalar || columnList.contains(field.getName())){
				query.addScalar(field.getName(), getTypeFromClass(field.getType()));
			}
		}
		query.setResultTransformer(Transformers
				.aliasToBean(target));
		return query;
	}
	
	/**
	 * 字段类型和hibernateType之间的转换，如果有缺少的，可以继续添加if else
	 * 
	 * @param target 字段类
	 * @return hibernate type
	 */
	private Type getTypeFromClass(Class<?> target) {
		if (String.class.equals(target)) {
			return StandardBasicTypes.STRING;
		} else if (Long.class.equals(target)) {
			return StandardBasicTypes.LONG;
		} else if (Date.class.equals(target)) {
			return StandardBasicTypes.DATE;
		} else if (Float.class.equals(target)) {
			return StandardBasicTypes.FLOAT;
		} else if (Double.class.equals(target)) {
			return StandardBasicTypes.DOUBLE;
		} else if (Integer.class.equals(target)) {
			return StandardBasicTypes.INTEGER;
		} else if (BigDecimal.class.equals(target)) {
			return StandardBasicTypes.BIG_DECIMAL;
		} else if (BigInteger.class.equals(target)) {
			return StandardBasicTypes.BIG_INTEGER;
		}
		return StandardBasicTypes.STRING;
	}
}

util类

/**
	 * 查询Temp 实体列表
	 * @param sql
	 * @param targetClass
	 * @return 列表结果
	 */
	protected List<T> queryTempEntityList(String sql, Class<T> targetClass){
		return queryTempEntityList(sql , (Object[])null, targetClass);
	}
	
	/**
	 * 查询Temp 实体列表
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @return  列表结果
	 */
	protected List<T> queryTempEntityList(String sql, Object[] params, 
			Class<T> targetClass) {

		return queryTempEntityList(sql, params, null, targetClass);
	}
	
	/**
	 * 查询Temp 实体列表
	 * 
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @return 列表结果
	 */
	protected List<T> queryTempEntityList(String sql, Map<String, Object> params, 
			Class<T> targetClass) {

		return queryTempEntityList(sql, params, null, targetClass);
	}
	
	/**
	 * 查询Temp 实体列表
	 * 
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return 列表结果
	 */
	protected List<T> queryTempEntityList(final String sql, final Object[] params,
			final List<String> columnList, final Class<T> targetClass) {

		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				return queryList(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 查询Temp 实体列表
	 *  
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return 列表结果
	 */
	protected List<T> queryTempEntityList(final String sql, final Map<String, Object> params, 
			final List<String> columnList, final Class<T> targetClass) {

		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				return queryList(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 查询Temp 实体
	 * 
	 * @param sql
	 * @param targetClass
	 * @return 实体
	 */
	protected T queryTempEntity(String sql, Class<T> targetClass) {
		return queryTempEntity(sql, (Object[])null, targetClass);
	}
	
	/**
	 * 查询Temp 实体
	 * 
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @return 实体
	 */
	protected T queryTempEntity(String sql, Object[] params,
			final Class<T> targetClass) {
		return queryTempEntity(sql, params, null, targetClass);
	}
	
	/**
	 * 查询Temp 实体
	 * 
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @return 实体
	 */
	protected T queryTempEntity(String sql, Map<String, Object> params, 
			final Class<T> targetClass) {
		return queryTempEntity(sql, params, null, targetClass);
	}
	
	/**
	 * 查询Temp 实体
	 * 
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return 实体
	 */
	protected T queryTempEntity(final String sql, final Object[] params,
			final List<String> columnList, final Class<T> targetClass) {
		return getHibernateTemplate().execute(new HibernateCallback<T>() {
			public T doInHibernate(Session session) throws HibernateException,
					SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				return queryUniqueResult(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 查询Temp 实体
	 * 
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return 实体
	 */
	protected T queryTempEntity(final String sql, final Map<String, Object> params, 
			final List<String> columnList, final Class<T> targetClass) {
		return getHibernateTemplate().execute(new HibernateCallback<T>() {
			public T doInHibernate(Session session) throws HibernateException,
					SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				return queryUniqueResult(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 查询Temp 实体列表, 带翻页
	 * @param sql
	 * @param targetClass
	 * @param index
	 * @param size
	 * @return 列表结果
	 */
	protected List<T> queryTempPageEntityList(String sql, Class<T> targetClass, int index, int size){
		return queryTempPageEntityList(sql , (Object[])null, targetClass, index, size);
	}
	
	/**
	 * 查询Temp 实体列表, 带翻页
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @param index
	 * @param size
	 * @return  列表结果
	 */
	protected List<T> queryTempPageEntityList(String sql, Object[] params, 
			Class<T> targetClass, int index, int size) {

		return queryTempPageEntityList(sql, params, null, targetClass, index, size);
	}
	
	/**
	 * 查询Temp 实体列表, 带翻页
	 * 
	 * @param sql
	 * @param params
	 * @param targetClass
	 * @param index
	 * @param size
	 * @return 列表结果
	 */
	protected List<T> queryTempPageEntityList(String sql, Map<String, Object> params, 
			Class<T> targetClass, int index, int size) {

		return queryTempPageEntityList(sql, params, null, targetClass, index, size);
	}
	
	/**
	 * 查询Temp 实体列表, 带翻页
	 * 
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @param index
	 * @param size
	 * @return 列表结果
	 */
	protected List<T> queryTempPageEntityList(final String sql, final Object[] params,
			final List<String> columnList, final Class<T> targetClass, final int index, final int size) {

		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				sqlQuery.setFirstResult(index);
				sqlQuery.setMaxResults(size);
				return queryList(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 查询Temp 实体列表, 带翻页
	 *  
	 * @param sql
	 * @param params
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @param index
	 * @param size
	 * @return 列表结果
	 */
	protected List<T> queryTempPageEntityList(final String sql, final Map<String, Object> params, 
			final List<String> columnList, final Class<T> targetClass, final int index, final int size) {

		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery sqlQuery = generateSQLQuery(session, sql, params);
				sqlQuery.setFirstResult(index);
				sqlQuery.setMaxResults(size);
				return queryList(sqlQuery, columnList, targetClass);
			}
		});
	}
	
	/**
	 * 根据session、sql、params生成SQLQuery
	 * @param session
	 * @param sql
	 * @param params
	 * @return SQLQuery
	 */
	private SQLQuery generateSQLQuery(Session session, String sql, Object[] params){
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				sqlQuery.setParameter(i, params[i]);
			}
		}
		sqlQuery.setReadOnly(true);
		return sqlQuery;
	}
	
	/**
	 * 根据session、sql、params生成SQLQuery
	 * @param session
	 * @param sql
	 * @param params
	 * @return SQLQuery
	 */
	private SQLQuery generateSQLQuery(Session session, String sql, Map<String, Object> params){
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		if (params != null) {
			Iterator<Entry<String, Object>> iterator = params.entrySet().iterator();
			while(iterator.hasNext()){
				Entry<String, Object> entry = iterator.next();
				sqlQuery.setParameter(entry.getKey(), entry.getValue());
			}
		}
		sqlQuery.setReadOnly(true);
		return sqlQuery;
	}
	
	/**
	 * 根据sqlQuery, 需要查询的字段列表，目标返回实体类来查询 list结果
	 * @param sqlQuery
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return list结果
	 */
	private List<T> queryList(SQLQuery sqlQuery, List<String> columnList, Class<T> targetClass){
		TempSQLQuery TempSQLQuery = new TempSQLQuery(sqlQuery, true);
		if(columnList != null){
			TempSQLQuery.setAutoAddScalar(false);
			TempSQLQuery.addScalarColumn(columnList);
		}
		TempSQLQuery.setResultTransformer(targetClass);
		return sqlQuery.list();
	}
	
	/**
	 * 根据sqlQuery, 需要查询的字段列表，目标返回实体类来查询 单个结果
	 * @param sqlQuery
	 * @param columnList 如果不是查询全部字段，这里需要把要查询的字段名字列出来，大小写敏感
	 * @param targetClass
	 * @return 单个结果
	 */
	private T queryUniqueResult(SQLQuery sqlQuery, List<String> columnList, Class<T> targetClass){
		TempSQLQuery TempSQLQuery = new TempSQLQuery(sqlQuery, true);
		if(columnList != null){
			TempSQLQuery.setAutoAddScalar(false);
			TempSQLQuery.addScalarColumn(columnList);
		}
		TempSQLQuery.setResultTransformer(targetClass);
		return (T) sqlQuery.uniqueResult();
	}

Terry_5008

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
hibernate 执行本地 sql

1.为了把sql查询返回的关系数据映射为对象，需要在sql查询语句中为字段指定别名String sql = "select cs.ID as{c.id},cs.NAME as {c.name},cs.AGE as {c.age} " +" from CUSTOMERS cs where cs.ID = 1";Query query = ...
复制链接

扫一扫