Hibernate创建执行sql的具体实现 java

4. 创建执行sql的具体实现

一般在工作中，只需要创建前面三种即可，然后通过对应的servic类去调用比如：getSession().save();getSession().update();方法等等，但是如果没有写sql执行类，我们就需要自己写（即getSession().save()背后的逻辑实现），这个没有什么好说的，基本上不会有任何改动，使用时直接复制就行。

package com.fhb.egms.dao;

import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.AliasToEntityMapResultTransformer;

public class ApplicationDaoSupport {
	
	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	protected Session getSession() {
		return sessionFactory.getCurrentSession();
	}

	protected List find(String hql) {
		Query query = getSession().createQuery(hql);

		return query.list();
	}

	protected List findByNamedParam(String hql, String paramName, Object value) {
		String[] paramNames = { paramName };
		Object[] values = { value };

		return findByNamedParam(hql, paramNames, values);
	}

	protected List findByNamedParam(String hql, String[] paramNames,
			Object[] values, int maxResult) {
		
		Query query = getSession().createQuery(hql);

		if (maxResult > 0) {
			query.setMaxResults(maxResult);
		}
	
		// Set named parameters
		if (paramNames != null || values != null) {
			// validation
			if (paramNames != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (paramNames == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (paramNames.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (paramNames.length > 0) {
				for (int i = 0; i < paramNames.length; i++) {
					
					if (values[i] instanceof Collection) {
						query.setParameterList(paramNames[i], (Collection<?>) values[i]);
						
					} else if (values[i] instanceof Object[]) {	
						query.setParameterList(paramNames[i], (Object[]) values[i]);

					} else {
						query.setParameter(paramNames[i], values[i]);
					}
				}
			}
		}
		
		return query.list();
	}
	
	protected List findByNamedParam(String hql, String[] paramNames,
			Object[] values) {
		return findByNamedParam(hql, paramNames, values, -1);
	}

	public List<Object> findQuery(StringBuilder sql, List<String> paramName,
			List<Object> paramList) {

		List<Object> queryResult = executeNativeSQL(sql.toString(),
				paramName.toArray(new String[0]), paramList.toArray());

		return queryResult;
	}

	public List<Object> findQuery(String sql, List<String> paramName,
			List<Object> paramList) {

		List<Object> queryResult = executeNativeSQL(sql,
				paramName.toArray(new String[0]), paramList.toArray());

		return queryResult;
	}

	public int updateQuery(StringBuilder sql, List<String> paramName,
			List<Object> paramList) {

		return executeNativeSQLUpdate(sql.toString(),
				paramName.toArray(new String[0]), paramList.toArray());

	}

	public int hqlQuery(StringBuilder hql, List<String> paramName,
			List<Object> paramList) {

		return hqlQuery(hql.toString(), paramName, paramList);
	}

	public int hqlQuery(String hql, List<String> paramName,
			List<Object> paramList) {

		Session session = getSession();
		Query query = session.createQuery(hql.toString());

		// Set named parameters
		if (paramName != null || paramList != null) {
			// validation
			if (paramName != null && paramList == null) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - values is null while namedParams is not");
			} else if (paramName == null && paramList != null) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - namedParams is null while values is not");
			} else if (paramName.size() != paramList.size()) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - namedParams.length is not equal to values.length");
			} else if (paramName.size() > 0) {
				for (int i = 0; i < paramName.size(); i++) {
					query.setParameter(paramName.get(i), paramList.get(i));
				}
			}
		}

		return query.executeUpdate();
	}

	public Object save(Object obj) {

		Object obj1 = null;

		obj1 = getSession().save(obj);

		return obj1;

	}

	public Object get(Class cls, Serializable str) {

		return getSession().get(cls, str);

	}

	public List loadAll(Class cls, String table) {

		List lst = null;

		lst = find(table);

		return lst;
	}

	public int setSessionMaxGroupConcatLength(int length) {
		Session session = getSession();

		String setSql = "SET SESSION group_concat_max_len = :length";

		SQLQuery sqlQuery = session.createSQLQuery(setSql);
		sqlQuery.setParameter("length", length);

		return sqlQuery.executeUpdate();
	}

	protected int executeNativeSQLUpdate(String sql, String[] namedParams,
			Object[] values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
		flush();
		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - namedParams is null while values is not");
			} else if (namedParams.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQLUpdate - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameter(namedParams[i], values[i]);
				}
			}
		}

		return sqlQuery.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	protected List<Object> executeNativeSQL(String sql, String[] namedParams,
			Object[] values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
		flush();
		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (namedParams.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameter(namedParams[i], values[i]);
				}
			}
		}

		return sqlQuery.list();
	}

	protected int executeNativeUpdateSQL(String sql, String[] namedParams,
			Object[] values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
		flush();
		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (namedParams.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameter(namedParams[i], values[i]);
				}
			}
		}

		return sqlQuery.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	protected List<Object> executeNativeSQLWithParamList(String sql,
			String[] namedParams, List<Object> values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
		flush();
		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (namedParams.length != values.size()) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameterList(namedParams[i],
							(Collection) values.get(i));
				}
			}
		}

		return sqlQuery.list();
	}

	protected Object executeNativeSQLWithUniqueResult(String sql,
			String[] namedParams, Object[] values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());
		flush();
		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (namedParams.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameter(namedParams[i], values[i]);
				}
			}
		}
		return sqlQuery.uniqueResult();
	}

	protected void flush() {
		getSession().flush();
	}

	public List<Map<String, Object>> findQueryToMap(StringBuilder sql,
			List<String> paramName, List<Object> paramList) {

		List<Map<String, Object>> queryResult = executeNativeSQLToMap(
				sql.toString(), paramName.toArray(new String[0]),
				paramList.toArray());

		return queryResult;
	}

	@SuppressWarnings("unchecked")
	protected List<Map<String, Object>> executeNativeSQLToMap(String sql,
			String[] namedParams, Object[] values) throws RuntimeException {
		Session session = getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql.toString());

		// add by peter
		sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

		// Set named parameters
		if (namedParams != null || values != null) {
			// validation
			if (namedParams != null && values == null) {
				throw new RuntimeException(
						"executeNativeSQL - values is null while namedParams is not");
			} else if (namedParams == null && values != null) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams is null while values is not");
			} else if (namedParams.length != values.length) {
				throw new RuntimeException(
						"executeNativeSQL - namedParams.length is not equal to values.length");
			} else if (namedParams.length > 0) {
				for (int i = 0; i < namedParams.length; i++) {
					sqlQuery.setParameter(namedParams[i], values[i]);
				}
			}
		}
		getSession().flush();
		return sqlQuery.list();
	}

}