java-classic-code/jpa-sqltemplate/DAOServiceImpl.java

/*
 * Copyright 2009 by primedata Corporation.
 * , 
 * 
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * primedata Corporation ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with primedata.
 */

package com.primeton.dams.utils.template.impl;

import com.primeton.dams.utils.template.CoreSQLParser;
import com.primeton.dams.utils.template.DOResultTransformer;
import com.primeton.dams.utils.template.HibernateStatementSessionFactoryBean;
import com.primeton.dams.utils.template.IDAOService;
import com.primeton.dams.utils.template.Page;
import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.internal.QueryImpl;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.LockModeType;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * find开头的方法是HQL方法，query开头的是SQL方法。
 * @author user
 * @version 1.0  2007-1-27
 *
 * @author zhaopx
 * @version 7.1
 * @date 2020-08-25
 */
@Slf4j
@Repository(value = "daoService")
public class DAOServiceImpl implements IDAOService {

	/**
	 * LOG
	 */
	private static Logger logger = LoggerFactory.getLogger(DAOServiceImpl.class);

	@Autowired
	JdbcTemplate jdbcTemplate;

	@PersistenceContext
	EntityManager entityManager;

	@Override
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public DAOServiceImpl() {
		super();
	}

	/** Get HQL from xxxSql.xml file*/
	@Override
	public String getSQL(String name) {
		return HibernateStatementSessionFactoryBean.getStatementText(name);
	}
	
	/** Get dynamic HQL from xxxSql.xml file, voOrMap is a VO or a map.*/
	@Override
	public String getSQL(String name, Object voOrMap) {
		return HibernateStatementSessionFactoryBean.getStatementText(name, voOrMap);
	}
	
	/** Get dynamic Params from xxxSql.xml file, voOrMap is a VO or a map.*/
	@Override
	public Object[] getParam(String name, Object voOrMap) {
		return HibernateStatementSessionFactoryBean.getStatementParam(name, voOrMap);
	}
	
	/** Get dynamic Params from xxxSql.xml file, voOrMap is a VO or a map.*/
	@Override
	public Map getMapParam(String name, Object voOrMap) {
		return HibernateStatementSessionFactoryBean.getStatementParamMap(name, voOrMap);
	}
	
	//Persist the given transient instance
    @Override
	public void add(Object vo) throws DataAccessException {
		entityManager.persist(vo);
    }

    //Update the given persistent instance
    @Override
	public void update(Object vo) throws DataAccessException {
		entityManager.merge(vo);
    }
    
    @Override
	public void merge(Object entity) throws DataAccessException {
		entityManager.merge(entity);
    }

    //Save or update the given persistent instance, according to its id
    @Override
	public void addOrUpdate(Object vo) throws DataAccessException {
		entityManager.persist(vo);
    }
    
    @Override
	public void addAll(Collection vos) throws DataAccessException {
		for (Object vo : vos) {
			entityManager.persist(vo);
		}
	}

    //Return the persistent instance of the given entity class with the given identifier,
    //or null if not found
    @Override
	public Object get(Class entityClass, Serializable id) {
		return entityManager.find(entityClass, id);
    }

    //Return the persistent instance of the given entity class with the given identifier,
    //or null if not found
    @Override
	public Object get(Class entityClass, Serializable id, LockModeType lockMode) {
        return entityManager.find(entityClass, id, lockMode);
    }

    //Delete the given persistent instance, according to its id
    @Override
	public void delete(Class entityClass, Serializable id) throws DataAccessException {
		entityManager.remove(get(entityClass, id));
    }

    //Delete the given persistent instance
    @Override
	public void delete(Object entity) throws DataAccessException {
		entityManager.remove(entity);
    }

    //Delete all given persistent instances
    @Override
	public void deleteAll(Collection entities) throws DataAccessException {
		for (Object entity : entities) {
			entityManager.remove(entity);
		}
    }


    //Update/delete all objects according to the given query
    @Override
	public int execute(String updateSql) {
		return entityManager.createNativeQuery(updateSql).executeUpdate();
    }
    
    //Update/delete all objects according to the given query
    @Override
	public int execute(String updateSql, Object[] params) {
		final javax.persistence.Query nativeQuery = entityManager.createNativeQuery(updateSql);
		for (int i=1; params!=null && i<params.length+1; i++) {
			nativeQuery.setParameter(i, params[i-1]);
		}
		return nativeQuery.executeUpdate();
	}
    
    //flush session
    @Override
	public void flush() {
    	entityManager.flush();
    }
    
    //clear session
    @Override
	public void clear() {
    	entityManager.clear();
    }
    

    // count(*).
    @Override
	public int count(String queryString, Object[] values) {
		return countSQL(queryString, values);
	}
    

	/**
	 * 执行SQL查询。提供结果集的转换接口ResultTransformer。
	 * @param query
	 * @param params
	 * @param rt
	 * @param firstResult
	 * @param maxResult
	 * @return
	 */
	@Override
	public List query(javax.persistence.Query query, Object[] params, ResultTransformer rt, final int firstResult, final int maxResult) {
		for (int i=1; params!=null && i<params.length+1; i++) {
			query.setParameter(i, params[i-1]);
		}
		query.setMaxResults(maxResult);
		query.setFirstResult(firstResult);
		if (rt == null) {
			query.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		} else {
			query.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(rt);
		}
		return query.getResultList();
	}

	@Override
	public List query(javax.persistence.Query query, Object[] params, ResultTransformer rt) {
		for (int i=1; params!=null && i<params.length+1; i++) {
			query.setParameter(i, params[i-1]);
		}
		if (rt == null) {
			query.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		} else {
			query.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(rt);
		}
		return query.getResultList();
	}
	
	@Override
	public List query(String sql, Object[] params, ResultTransformer rt) {
		return query(entityManager.createNativeQuery(sql), params, rt);
	}


	/**
	 * 执行SQL查询
	 * @param id 脚本
	 * @param params 参数
	 * @param entityClass 转换
	 * @return Object[]/Object
	 */
	@Override
	public List queryById(String id, Map<String, Object> params, Class entityClass) {
		final String sql = getSQL(id, params);
		if(StringUtils.isBlank(sql)) {
			throw new IllegalArgumentException("undefined statement id: " + id);
		}
		final Object[] sqlParams = (params == null ? null : getParam(id, params));
		final Query nativeQuery = entityManager.createNativeQuery(sql);
		if (entityClass == null) {
			nativeQuery.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		} else {
			nativeQuery.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(new DOResultTransformer(entityClass));
		}
		return query(nativeQuery, sqlParams, entityClass, null, null);
	}

	/**
	 * 执行 statement id 的查询，返回  ResultTransformer 的转换结果, 并应用分页
	 * @param id
	 * @param params
	 * @param entityClass
	 * @param firstResult
	 * @param maxResult
	 * @return
	 */
	@Override
	public List queryById(String id, Map<String, Object> params, Class entityClass, final int firstResult, final int maxResult) {
		final String sql = getSQL(id, params);
		if(StringUtils.isBlank(sql)) {
			throw new IllegalArgumentException("undefined statement id: " + id);
		}
		final Object[] sqlParams = (params == null ? null : getParam(id, params));
		final Query nativeQuery = entityManager.createNativeQuery(sql);
		if (entityClass == null) {
			nativeQuery.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		} else {
			nativeQuery.unwrap(org.hibernate.SQLQuery.class)
					.setResultTransformer(new DOResultTransformer(entityClass));
		}
		return query(nativeQuery, sqlParams, entityClass, firstResult, maxResult);
	}


	private List query(javax.persistence.Query query, Object[] params, Class entityClass, final Integer firstResult, final Integer maxResult) {
		for (int i=1; params!=null && i<params.length+1; i++) {
			query.setParameter(i, params[i-1]);
		}
		if (firstResult != null) {
			query.setFirstResult(firstResult);
		}
		if (maxResult != null) {
			query.setMaxResults(maxResult);
		}
		return query.getResultList();
	}


	@Override
	public List querySQL(javax.persistence.Query query, Object[] params) {
		return this.query(query, params, null);
	}
	
	@Override
	public List queryForMap(String sql, Object[] params) {
		return this.query(sql, params, Transformers.ALIAS_TO_ENTITY_MAP);
	}
	
	@Override
	public List queryForMap(javax.persistence.Query query, Object[] params) {
		return this.query(query, params, Transformers.ALIAS_TO_ENTITY_MAP);
	}

	@Override
	public List queryForMap(String sql, Object[] params, int firstResult, int maxResult) {
		final javax.persistence.Query query1 = entityManager.createNativeQuery(sql);
		for (int i=1; params!=null && i<params.length+1; i++) {
			query1.setParameter(i, params[i-1]);
		}

		query1.setFirstResult(firstResult);
		query1.setMaxResults(maxResult);
		query1.unwrap(org.hibernate.SQLQuery.class)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query1.getResultList();
	}
	
	private List queryForEntity(javax.persistence.Query query, Object[] params, Class entityClass) {
		for (int i=1; params!=null && i<params.length+1; i++) {
			query.setParameter(i, params[i-1]);
		}
		return query.getResultList();
	}

	@Override
	public List queryForEntity(String sql, Class entityClass) {
		final javax.persistence.Query query1 = entityManager.createQuery(sql, entityClass);
		query1.unwrap(QueryImpl.class)
				.setResultTransformer(new DOResultTransformer(entityClass));
		return query1.getResultList();
	}

	@Override
	public List queryForEntity(String sql, Object[] params, Class entityClass) {
		final javax.persistence.Query query1 = entityManager.createQuery(sql);
		query1.unwrap(QueryImpl.class)
				.setResultTransformer(new DOResultTransformer(entityClass));
		return this.queryForEntity(query1, params, entityClass);
	}
	
	@Override
	public List queryForEntity(String sql, Object[] params,
							   Class entityClass, int firstResult, int maxResult) {
		final javax.persistence.Query query1 = entityManager.createQuery(sql);
		QueryImpl query = query1.unwrap(QueryImpl.class);
		query.setResultTransformer(new DOResultTransformer(entityClass));
		query.setMaxResults(maxResult).setFirstResult(firstResult);
		return this.queryForEntity(query1, params, entityClass);
	}

	/**
	 * 查询返回一条数据，无数据则返回 null
	 * @param sql
	 * @param params
	 * @param entityClass
	 * @param <T>
	 * @return
	 */
	public <T> T queryForObject(String sql, Object[] params, @NonNull Class<T> entityClass) {
		final javax.persistence.Query query1 = entityManager.createNativeQuery(sql);
		for (int i=1; params!=null && i<params.length+1; i++) {
			query1.setParameter(i, params[i-1]);
		}
		query1.unwrap(org.hibernate.SQLQuery.class)
				.setResultTransformer(new DOResultTransformer(entityClass));
		try {
			return (T) query1.getSingleResult();
		} catch (NoResultException e) {
			// 未查询到数据，则返回 null
			return null;
		} catch (NonUniqueResultException e) {
			// 查询到多条，则返回第 0 条
			return (T)query1.getResultList().get(0);
		}
	}

	@Override
	public long queryForLong(String sql, Object[] params) {
		return countSQL(sql, params);
	}

	@Override
	public int updateById(String id, Map<String, Object> params) {
		final String sql = getSQL(id, params);
		if(StringUtils.isBlank(sql)) {
			throw new IllegalArgumentException("undefined statement id: " + id);
		}
		final Object[] sqlParams = (params == null ? null : getParam(id, params));
		SQLQuery query = entityManager.createNativeQuery(sql).unwrap(SQLQuery.class);
		if (sqlParams != null && sqlParams.length > 0) {
			for (int i=1; i<sqlParams.length+1; i++) {
				query.setParameter(i, sqlParams[i-1]);
			}
		}
		return query.executeUpdate();
	}

	@Override
	public int updateSQL(String sql, final Object[] params) {
		if (StringUtils.isBlank(sql)) {
			return 0;
		}
		SQLQuery query = entityManager.createNativeQuery(sql).unwrap(SQLQuery.class);
		if (params != null && params.length > 0) {
			for (int i=1; i<params.length+1; i++) {
				query.setParameter(i, params[i-1]);
			}
		}
		return query.executeUpdate();
	}
	
	@Override
	public int[] batchUpdateSQL(String[] sql) {
		int[] r = new int[sql.length];
		for (int i = 0; i < sql.length; i++) {
			r[i] = updateSQL(sql[i], null);
		}
		return r;
	}

	@Override
	public List queryPageList(String sql, Object[] params, Page page) {
		SQLQuery query = entityManager.createNativeQuery(sql).unwrap(SQLQuery.class);
		for (int i=1; params!=null && i<params.length+1; i++) {
			query.setParameter(i, params[i-1]);
		}
		int totalCount = this.countSQL(sql, params);
		query.setMaxResults(page.getPageSize());
		query.setFirstResult(page.getStartIndex());
		List list = new ArrayList();
		if (totalCount > 0) {
			list = query.list();
		}
		page.setCurrRecords(list.size());
		page.setQueryResult(list);
		page.setTotalRecs(totalCount);
		return list;
	}
	
	@Override
	public List queryPageMap(String sql, Object[] params, Page page) {
		int totalCount = this.countSQL(sql, params);
		List<?> list = new ArrayList();
		if (totalCount > 0) {
			final javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
			SQLQuery query = nativeQuery.unwrap(SQLQuery.class);
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			query.setMaxResults(page.getPageSize());
			query.setFirstResult(page.getStartIndex());
			return this.queryForMap(nativeQuery, params);
		}
		page.setCurrRecords(list.size());
		page.setQueryResult(list);
		page.setTotalRecs(totalCount);
		return list;
    }
	
	/*
	 * (non-Javadoc)
	 * @see com.primedata.primedatamm.core.dao.IDAOService
	 * #queryPageEntity(java.lang.String, java.lang.Object[], 
	 * com.primedata.primedatamm.core.common.Page, EntityList)
	 */
	@Override
	public List queryPageEntity(String sql, Object[] params, Page page, @NonNull Class entityClass) {
		int totalCount = this.countSQL(sql, params);
		List<?> list = new ArrayList();
		if (totalCount > 0) {
			final javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
			SQLQuery query = nativeQuery.unwrap(SQLQuery.class);
			for (int i=1; params!=null && i<params.length+1; i++) {
				query.setParameter(i, params[i-1]);
			}
			nativeQuery.unwrap(org.hibernate.SQLQuery.class)
						.setResultTransformer(new DOResultTransformer(entityClass));
			query.setMaxResults(page.getPageSize());
			query.setFirstResult(page.getStartIndex());
			return query.list();
		}
		page.setCurrRecords(list.size());
		page.setQueryResult(list);
		page.setTotalRecs(totalCount);
		return list;
	}

	/*
	 * (non-Javadoc)
	 * @see com.primedata.primedatamm.core.dao.IDAOService#getCountSQL(java.lang.String)
	 */
	@Override
	public String getCountSQL(String sql) {
		return CoreSQLParser.getCountSQL(sql);
	}

    // count sql query result.
    @Override
	public int countSQL(String sql, Object[] params) {
    	String strQuery = this.getCountSQL(sql);
		int count = 0;
		final javax.persistence.Query nativeQuery = entityManager.createNativeQuery(strQuery);
		for (int i=1; params!=null && i<params.length+1; i++) {
			nativeQuery.setParameter(i, params[i-1]);
		}
		if (nativeQuery != null) {
			// 取出count总数
			final Object singleResult = nativeQuery.getSingleResult();
			if(singleResult instanceof Number) {
				count = ((Number)singleResult).intValue();
			}
		}
		return count;
	}
    

    // 创建statement的id=getSEQ_xxx，其SQL如: select $sequence$.nextval from dual
    @Override
	public Long getSQNextValue(String sequence) {
    	Map<String,Object> map = new HashMap<String,Object>();
    	map.put("sequence", sequence);
		return queryForLong(getSQL("getSQNextValue", map), getParam("getSQNextValue", map));
	}

	@Override
	public Long getSQCurrValue(String sequence) {
		Map<String,Object> map = new HashMap<String,Object>();
    	map.put("sequence", sequence);
		return queryForLong(getSQL("getSQCurrValue", map), getParam("getSQCurrValue", map));
	}
}