JPA自定义sql处理器

梦里梦外; 2021-09-25 14:40 342阅读 0赞

JPA自定义sql处理器 后台查询需要各种形式的sql，还是原生sql用着方便  
简单归类了下

import java.util.List;
    
    import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import javax.persistence.Query;
    
    import org.apache.log4j.Logger;
    import org.apache.poi.ss.formula.functions.T;
    import org.springframework.stereotype.Service;
    
    import com.game.util.JsonTool;
    import com.game.util.SpringManager;
    /**
     * JPA自定义sql处理器
     * @author gaoyannan
     * @time 20210902
     *
     */
    @SuppressWarnings("all")
    @Service("SqlService")
    public class SqlService {
    	public static SqlService util() {
    		return SpringManager.getBean(SqlService.class);
    	}
    	@PersistenceContext
    	public EntityManager em;
    	protected final static Logger logger = Logger.getLogger(SqlService.class);
    	
    	
    	/**
    	 * 判断表是否存在  false不存在 true存在
    	 * @param tableName 表名
    	 * @return
    	 */
    	public boolean isTableName(String tableName) {
    		String sql="SELECT table_name FROM information_schema.TABLES WHERE table_name ='"+tableName+"' LIMIT 0,1;";
    		logger.info(">>>>>>>>>>>>>>>>>>>>SQL=" + sql);
    		Query query = em.createNativeQuery(sql);
    		
    		if(query.getResultList().size()==0) {
    			logger.info(">>>>>>>>>>>>>>>>>>>>tableName=" + tableName+" 不存在");
    			return false;
    		}
    		logger.info(">>>>>>>>>>>>>>>>>>>>tableName=" + tableName+" 存在");
    		return true;
    	}
    	/**
    	 * 执行自定义sql 返回obj类型
    	 * @param sql
    	 * @return
    	 */
    	public Object queryObj(String sql) {
    		logger.info(">>>>>>>>>>>>>>>>>>>>SQL=" + sql);
    		Query query = em.createNativeQuery(sql);
    		Object o=query.getSingleResult();
    		logger.info(">>>>>>>>>>>>>>>>>>>>return queryObj=" + JsonTool.encode(o));
    		return o;
    	}
    	
    	/**
    	 * 执行自定义sql 返回ObjList类型
    	 * @param sql
    	 * @return
    	 */
    	public List<Object> queryList(String sql) {
    		logger.info(">>>>>>>>>>>>>>>>>>>>SQL=" + sql);
    		Query query = em.createNativeQuery(sql);
    		List<Object> list= query.getResultList();
    		logger.info(">>>>>>>>>>>>>>>>>>>>return queryList=" + JsonTool.encode(list));
    		return list;
    	}
    	/**
    	 * 执行自定义sql 返回ClassList类型
    	 * @param <T>
    	 * @param sql
    	 * @param resultClass
    	 * @return
    	 */
    
    	public <T> List<T> queryList(String sql,Class resultClass) {
    		logger.info(">>>>>>>>>>>>>>>>>>>>SQL=" + sql);
    		Query query = em.createNativeQuery(sql,resultClass);
    		List<T>  list=query.getResultList();
    		logger.info(">>>>>>>>>>>>>>>>>>>>return queryList_resultClass=" + JsonTool.encode(list));
    		return list;
    	}
    }