基于 {@link EntityManager} 的数据库操作方法集

基于 {@link EntityManager} 的数据库操作方法集。

package com.kinth.scheduler.service;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.Id;
import javax.persistence.Parameter;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import org.springframework.data.jpa.repository.query.QueryUtils;
import org.springframework.transaction.annotation.Transactional;
import com.kinth.common.util.Tools;
/**
 * 基于 {@link EntityManager} 的数据库操作方法集
 */
@Named
public class EntityManagerService {

    @Inject
    private EntityManager entityManager;

    /**
     * 绑定query命名参数的值
     * @param query
     * @param paramMap
     * @return
     */
    private Query setQueryParameters(Query query, Map<String, Object> paramMap) {
        // 根据query的参数集设置各个参数值
        Set<Parameter<?>> set = query.getParameters();
        Iterator<Parameter<?>> i = set.iterator();
        while (i.hasNext()) {
            String param = i.next().getName();
            query.setParameter(param, paramMap.get(param));
        }
        return query;
    }

    /**
     * JPQL分页查询 ： 加载分页数据
     * 
     * @param jpql
     * @param paramMap
     * @param page
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Object> loadPage(String jpql, Map<String, Object> paramMap, Integer page, Integer pageSize) {
        page = (page == null || page == 0 ? 1 : page);
        pageSize = (pageSize == null || pageSize == 0 ? 10 : pageSize);
        Query query = entityManager.createQuery(jpql);
        this.setQueryParameters(query, paramMap);
        query.setFirstResult((page - 1) * pageSize);
        query.setMaxResults(pageSize);
        List<Object> list = query.getResultList();
        return list;
    }

    /**
     * JPQL分页查询 : 统计记录总数
     * 
     * @param jpql
     * @param paramMap
     * @return
     */
    public int count(String jpql, Map<String, Object> paramMap) {
        String t = QueryUtils.createCountQueryFor(jpql);
        TypedQuery<Long> query = entityManager.createQuery(t, Long.class);
        this.setQueryParameters(query, paramMap);
        Long total = query.getSingleResult();
        return total.intValue();
    }


    /**
     * SQL查询结果列表：执行原生查询SQL或存储过程
     * <br>注：sqlserver 的存储过程首行加上 SET NOCOUNT ON
     * @param sql
     * @param paramMap
     * @param page
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Object> executeNativeQuery(String sql, Map<String, Object> paramMap) {
        Query query = entityManager.createNativeQuery(sql);
        this.setQueryParameters(query, paramMap);
        List<Object> list = query.getResultList();
        return list;
    }

    /**
     * SQL查询结果列表：执行原生查询SQL或存储过程
     * <br>注：sqlserver 的存储过程首行加上 SET NOCOUNT ON
     * @param sql
     * @param paramMap
     * @param clazz
     * @return
     */
    @SuppressWarnings("unchecked")
    public <T> List<T> executeNativeQuery(String sql, Map<String, Object> paramMap, Class<T> clazz) {
        Query query = entityManager.createNativeQuery(sql);
        this.setQueryParameters(query, paramMap);
        List<T> list = query.getResultList();
        return list;
    }

    /**
     * SQL查询单个结果：执行原生查询SQL或存储过程
     * @param sql
     * @param paramMap
     * @param page
     * @return
     */
    public Object getSingleResultByNativeQuery(String sql, Map<String, Object> paramMap) {
        Query query = entityManager.createNativeQuery(sql);
        this.setQueryParameters(query, paramMap);
        Object result = query.getSingleResult();
        return result;
    }

    /**
     * SQL更新或删除：执行原生查询SQL
     * @param sql
     * @param paramMap
     * @param page
     * @return
     */
    @Transactional
    public int updateByNativeQuery(String sql, Map<String, Object> paramMap) {
        Query query = entityManager.createNativeQuery(sql);
        this.setQueryParameters(query, paramMap);
        int count = query.executeUpdate();
        return count;
    }

    /**
     * 通过NamedQeury查询一条记录
     * @param queryName
     * @param params
     * @param clazz
     * @return
     */
    public Object findOneEntity(String queryName, String params) {
        if (Tools.emptyRequestParameter(params)) {
            return null;
        }

        Query query = entityManager.createNamedQuery(queryName);

        String[] paramArr = params.split(",");
        for (int i=0 ; i<paramArr.length ; i++) {
            query.setParameter(i+1, paramArr[i]);
        }

        List<?> resultList = query.getResultList();
        if (resultList == null || resultList.size() == 0) {
            return null;
        }

        return resultList.get(0);
    }

    /**
     * 通过属性值查询实体
     * @param entity
     * @param properties
     * @return
     */
    public Object findOneEntityByProperty(Object entity, String properties) {
        if (Tools.emptyRequestParameter(properties)) {
            return null;
        }

        String canonicalName = entity.getClass().getCanonicalName();
        String[] canonicalNameSplit = canonicalName.split("\\.");
        String entityName = canonicalNameSplit[canonicalNameSplit.length - 1];

        String[] propertyArr = properties.split(",");
        String jpql = "select entity"
                + " from " + entityName + " entity"
                + " where ";
        int i;
        for (i=0 ; i<propertyArr.length ; i++) {
            jpql += "entity."+propertyArr[i]+" = ?"+ (i+1) +" and ";
        }
        jpql = jpql.substring(0, jpql.length() - " and ".length());

        Query query = entityManager.createQuery(jpql);
        for (i=0 ; i<propertyArr.length ; i++) {
            query.setParameter(i+1, getEntityProperty(entity, propertyArr[i]));
        }

        List<?> resultList = query.getResultList();
        if (resultList == null || resultList.size() == 0) {
            return null;
        }

        return resultList.get(0);
    }

    /**
     * 通过jpql查询实体
     * @param jpql
     * @param params
     * @return
     */
    public Object findOneEntityByJpql(String jpql, String params) {
        Query query = entityManager.createQuery(jpql);
        if (!Tools.emptyRequestParameter(params)) {
            String[] paramArr = params.split(",");
            for (int i = 0 ; i < paramArr.length ; i++) {
                query.setParameter(i+1, paramArr[i]);
            }
        }

        List<?> resultList = query.getResultList();
        if (resultList == null || resultList.size() == 0) {
            return null;
        }

        return resultList.get(0);
    }

    /**
     * 设置对象的id
     * @param id
     * @param entity
     */
    public void setEntityId(Object id, Object entity) {
        Method idGetter = null;
        Method[] methods = entity.getClass().getDeclaredMethods();
        for (Method method : methods) {
            if (method.isAnnotationPresent(Id.class)) {
                idGetter = method;
                break;
            }
        }

        if (idGetter == null) {
            return;
        }

        Class<?> clazz = entity.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            try {
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), clazz);
                Method getMethod = propertyDescriptor.getReadMethod();
                if (getMethod.equals(idGetter)) {
                    Method setMethod = propertyDescriptor.getWriteMethod();
                    setMethod.invoke(entity, id);
                    return;
                }
            } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                // do nothing
            }

        }
    }

    /**
     * 获取对象的id
     * @param entity
     * @return
     */
    public Object getEntityId(Object entity) {
        Method[] methods = entity.getClass().getDeclaredMethods();
        for (Method method : methods) {
            if (method.isAnnotationPresent(Id.class)) {
                try {
                    Object id = method.invoke(entity);
                    return id;
                } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    break;
                }
            }
        }

        return null;
    }

    /**
     * 获取实体的属性值
     * @param entity
     * @param property
     * @return
     */
    public Object getEntityProperty(Object entity, String property) {
        Class<?> clazz = entity.getClass();
        try {
            PropertyDescriptor propertyDescriptor = new PropertyDescriptor(property, clazz);
            Method getMethod = propertyDescriptor.getReadMethod();
            Object id = getMethod.invoke(entity);
            return id;
        } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 合并两个实体
     * @param originEntiy
     * @param newEntity
     */
    public void mergeEntity(Object originEntiy, Object newEntity) {
        Class<?> clazz = newEntity.getClass();

        Field[] fields = newEntity.getClass().getDeclaredFields();
        for (Field field : fields) {
            try {
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), clazz);
                Method getter = propertyDescriptor.getReadMethod();
                Object fieldValue = getter.invoke(newEntity);
                if (fieldValue != null) {
                    Method setter = propertyDescriptor.getWriteMethod();
                    setter.invoke(originEntiy, fieldValue);
                }
            } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                // do nothing
            }
        }
    }

    /**
     * 持久化实体，首先通过属性（多个属性用,分隔）查找实体是否存在，如果存在则更新实体，否则insert
     * @param entity
     * @param queryProperty
     * @throws ClassNotFoundException
     */
    @Transactional
    public void persistSingleEntity(Object entity,String queryProperty) throws ClassNotFoundException {
        Object dbEntity = this.findOneEntityByProperty(entity, queryProperty);
        if (dbEntity != null) {
            Object id = this.getEntityId(dbEntity);
            this.setEntityId(id, entity);
            entityManager.merge(entity);
        } else {
            entityManager.persist(entity);
        }
    }
}