Spring DAO设计实战

引用

  提供不同数据源和方言实现智能分页，因Spring单例模式，可以利用Cglib实现动态数据源切换方案,基础东西不讲了，直接看代码。

引用

  持久超接口,获取操作模板，可以是JdbcTemplate、SqlSessionTemplate等

package com.sunshine.basic.dao;

public interface SuperDao<T> {
	
	/**
	 * 获取操作模板
	 * @return
	 */
	 T getTemplate();
	
}

引用

查询+分页操作接口

package com.sunshine.basic.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.sunshine.basic.dao.page.PagingParameter;
import com.sunshine.basic.exception.DaoAccessException;

/**
 * 查询+分页操作接口
 * @author OY
 * @since 2016/01/20
 * @see V2.0.0
 */
public interface JdbcQueryDao extends SuperDao<JdbcTemplate>{
	
	/***
	 * 查询单个字段值
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	Object getField(String sql, Object...paras) throws DaoAccessException;
	/**
	 * 查询单个字段值
	 * 采用?占位符
	 * @param sql
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	Object getField(String sql, List<Object> paras) throws DaoAccessException;
	/**
	 * 查询单个字段值
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	Object getField(String sql, Map<String, Object> paras) throws DaoAccessException;
	//===================================================================================
	/**
	 * 查询单个对象
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param paras
	 * @return
	 */
	Map<String, Object> getObject(String sql, Object...paras) throws DaoAccessException;
	/**
	 * 查询单个对象
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	Map<String, Object> getObject(String sql, List<Object> paras) throws DaoAccessException;
	/**
	 * 查询单个对象
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	Map<String, Object> getObject(String sql, Map<String, Object> paras) throws DaoAccessException;
	
	/**
	 * 查询单个对象(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param classz
	 * @param paras
	 * @return
	 */
	<T> T getObject(String sql, Class<T> classz, Object...paras) throws DaoAccessException;
	/**
	 * 查询单个对象(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param classz
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	<T> T getObject(String sql, Class<T> classz, List<Object> paras) throws DaoAccessException;
	/**
	 * 查询单个对象(封装)
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param classz
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	<T> T getObject(String sql, Class<T> classz, Map<String, Object> paras) throws DaoAccessException;
	
	//====================================================================================================
	/**
	 * 查询列表
	 * 采用?占位符,如 name=?
	 * @param sql SQL语句
	 * @param paras 参数
	 * @return
	 * @throws DaoAccessException
	 */
	List<Map<String, Object>> getList(String sql, Object...paras) throws DaoAccessException;
	/**
	 * 查询列表
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	List<Map<String, Object>> getList(String sql, List<Object> paras) throws DaoAccessException;
	/**
	 * 查询列表
	 * @param sql
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	List<Map<String, Object>> getList(String sql, Map<String, Object> paras) throws DaoAccessException;
	
	
	/**
	 * 查询列表(封装)
	 * 采用?占位符,如 name=?
	 * @param sql SQL语句
	 * @param classz 结果封装类
	 * @param paras 参数
	 * @return
	 * @throws DaoAccessException
	 */
	<T> List<T> getList(String sql, Class<T> classz, Object...paras) throws DaoAccessException;
	/**
	 * 查询列表(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param classz
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	<T> List<T> getList(String sql, Class<T> classz, List<Object> paras) throws DaoAccessException;
	/**
	 * 查询列表(封装)
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param classz
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	<T> List<T> getList(String sql, Class<T> classz, Map<String, Object> paras) throws DaoAccessException;
	
	
	//====================================================================================================
	
	
	/**
	 * 查询总记录数
	 * 采用?占位符,如 name=?
	 * @param sql                 查询SQL
	 * @param limit               查询限制,limit=0查询不受限制
	 * @return
	 * @throws SQLException
	 * @throws DaoAccessException
	 */
	public int getRecordCounts(String sql, int limit, Object...paras)throws DaoAccessException;
	/**
	 * 查询记录总数
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param limit
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	public int getRecordCounts(String sql, int limit, List<Object> paras)throws DaoAccessException;
	/**
	 * 查询记录总数
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param limit
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	public int getRecordCounts(String sql, int limit, Map<String, Object> paras)throws DaoAccessException;
	
	/**
	 * 查询分页列表
	 * 采用?占位符,如 name=?
	 * @param sql                 查询SQL
	 * @param pagingParameter     分页对象,pagingParameter=null或无参数构分页对象,不做分页
	 * @return
	 * @throws SQLException
	 * @throws DaoAccessException
	 */
	public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, Object...paras)throws DaoAccessException;
	/**
	 * 查询分页列表
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param pagingParameter
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, List<Object> paras)throws DaoAccessException;
	/**
	 * 查询分页列表
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param pagingParameter
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, Map<String, Object> paras)throws DaoAccessException;
	
	/**
	 * 查询分页列表(封装)
	 * 采用?占位符,如 name=?
	 * @param <T>         
	 * @param sql                 查询SQL
	 * @param pagingParameter     分页对象,pagingParameter=null或无参数构分页对象,不做分页
	 * @param classz
	 * @return
	 * @throws SQLException
	 * @throws DaoAccessException
	 */
	public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, Object...paras)throws DaoAccessException;
	/**
	 * 查询分页列表(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param classz
	 * @param pagingParameter
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, List<Object> paras)throws DaoAccessException;
	/**
	 * 查询分页列表(封装)
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param classz
	 * @param pagingParameter
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, Map<String, Object> paras)throws DaoAccessException;

	//==================================================================================================================
	/**
	 * 分页查询结果
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param paras
	 * @return
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Object...paras)throws DaoAccessException;
	/**
	 * 分页查询结果
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, List<Object> paras)throws DaoAccessException;
	/**
	 * 分页查询结果
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Map<String, Object> paras)throws DaoAccessException;
	
	/**
	 * 分页查询结果(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param clazz
	 * @param paras
	 * @return
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, Object...paras)throws DaoAccessException;
	/**
	 * 分页查询结果(封装)
	 * 采用?占位符,如 name=?
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param clazz
	 * @param paras
	 * @return
	 * @throws DaoAccessException
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, List<Object> paras)throws DaoAccessException;
	/**
	 * 分页查询结果(封装)
	 * 采用名称占位符,如 name=:name
	 * @param sql
	 * @param curPage
	 * @param pageSize
	 * @param clazz
	 * @param paras key与占位符名称一致
	 * @return
	 * @throws DaoAccessException
	 */
	public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, Map<String, Object> paras)throws DaoAccessException;

}

/*更新接口*/
package com.sunshine.basic.dao;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.sunshine.basic.exception.DaoAccessException;


public interface JdbcUpdateDao extends SuperDao<JdbcTemplate>{
	
	void update(String sql, Object...paras) throws DaoAccessException;
	
	void update(String sql, List<Object> paras) throws DaoAccessException;
	
	void update(String sql, Map<String, Object> paras) throws DaoAccessException;
	
}

/*插入接口*/
package com.sunshine.basic.dao;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.sunshine.basic.exception.DaoAccessException;


public interface JdbcInsertDao extends SuperDao<JdbcTemplate>{
	
	void insert(String sql, Object...paras) throws DaoAccessException;
	
	void insert(String sql, List<Object> paras) throws DaoAccessException;
	
	void insert(String sql, Map<String, Object> paras) throws DaoAccessException;
	
}

引用

操作实现，暂未实现更新、插入。定义模板获取方法、SQL解析器由子类实现（设计模式之模板模式）

 package com.sunshine.basic.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.sunshine.basic.dao.page.DataStore;
import com.sunshine.basic.dao.page.PagingParameter;
import com.sunshine.basic.dao.parser.AbstractParser;
import com.sunshine.basic.dao.parser.Dialect;
import com.sunshine.basic.dao.parser.Parser;
import com.sunshine.basic.exception.DaoAccessException;
/**
 * 持久操作
 * @author OY
 * @since 2016/01/20
 * @see V2.0.0
 */
public abstract class AbstractJdbcCommDao implements JdbcQueryDao, JdbcInsertDao, JdbcUpdateDao{

	private Logger log = Logger.getLogger(getClass());
	
	private NamedParameterJdbcTemplate nameJdbTemplate;
	
	private JdbcTemplate jdbcTemplate;
	
	public NamedParameterJdbcTemplate getNamedJdbcTemplate(){
		nameJdbTemplate = new NamedParameterJdbcTemplate(
				getTemplate());
		return nameJdbTemplate;
	}
	/**
	 * 子类实现获取JdbcTemplate
	 * @return
	 * @throws DaoAccessException
	 */
	public abstract JdbcTemplate getSubJdbcTemplate() throws DaoAccessException ;
	/**
	 * 子类实现解析器(根据方言构造分页语句)
	 * @return
	 * @throws DaoAccessException
	 */
	public abstract Parser getParser() throws DaoAccessException;
	
	@Override
	public final JdbcTemplate getTemplate() {
		try {
			jdbcTemplate = getSubJdbcTemplate();
			if(jdbcTemplate == null) {
				log.info("jdbcTemplate is not exits!");
				throw new IllegalAccessException("jdbcTemplate is not exits!");
				//return SpringApplicationContext.getBean(DEFAULT_JDBC_TEMPLATE, JdbcTemplate.class);
			}
		} catch (Exception e) {
			log.error("jdbcTemplate is not exits!");
			e.printStackTrace();
		}
		return jdbcTemplate;
	}
	
	@Override
	public Object getField(String sql, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Object result = null;
		try{
			result = getTemplate().queryForObject(sql, paras, Object.class);
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	}

	@Override
	public Object getField(String sql, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getField(sql, paras.toArray());
	}

	@Override
	public Object getField(String sql, Map<String, Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Object result = null;
		try{
			result = getNamedJdbcTemplate().queryForObject(sql, paras, Object.class);
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	}
	
	@Override
	public Map<String, Object> getObject(String sql, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Map<String, Object> result = null;
		try{
			result = getTemplate().queryForMap(sql, paras);
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	}

	@Override
	public Map<String, Object> getObject(String sql, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getObject(sql, paras.toArray());
	}

	@Override
	public Map<String, Object> getObject(String sql, Map<String, Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Map<String, Object> result = null ;
		try{
			result = getNamedJdbcTemplate().queryForMap(sql, paras);
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	}

	@Override
	public <T> T getObject(String sql, Class<T> classz, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		T result = null;
		try{
			result = getTemplate().queryForObject(sql, paras, new BeanPropertyRowMapper<T>(classz));
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	} 

	@Override
	public <T> T getObject(String sql, Class<T> classz, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getObject(sql, classz, paras.toArray());
	}

	@Override
	public <T> T getObject(String sql, Class<T> classz,
			Map<String, Object> paras) throws DaoAccessException {
		log.info("jcbksql-" + sql);
		T result = null;
		try{
			result = getNamedJdbcTemplate().queryForObject(sql, paras, new BeanPropertyRowMapper<T>(classz));
		}catch(EmptyResultDataAccessException e){
			//不做处理
		}
		return result;
	}
    
	@Override
	public List<Map<String, Object>> getList(String sql, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getTemplate().queryForList(sql, paras);
	}

	@Override
	public List<Map<String, Object>> getList(String sql, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getList(sql, paras.toArray());
	}

	@Override
	public List<Map<String, Object>> getList(String sql,
			Map<String, Object> paras) throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getNamedJdbcTemplate().queryForList(sql, paras);
	}

	@Override
	public <T> List<T> getList(String sql, Class<T> classz, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getTemplate().query(sql, paras, new BeanPropertyRowMapper<T>(classz));
	}

	@Override
	public <T> List<T> getList(String sql, Class<T> classz, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getList(sql, classz, paras.toArray());
	}

	@Override
	public <T> List<T> getList(String sql, Class<T> classz,
			Map<String, Object> paras) throws DaoAccessException {
		log.info("jcbksql-" + sql);
		return getNamedJdbcTemplate().query(sql, paras, new BeanPropertyRowMapper<T>(classz));
	}

	
	@Override
	public int getRecordCounts(String sql, int limit, Object... paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		String cSql = getParser().getCountSql(sql);
		log.info("jcbksql-count-" + cSql);
		Object result = getField(cSql, paras);
		return Integer.valueOf((result == null)?"0":String.valueOf(result));
	}

	@Override
	public int getRecordCounts(String sql, int limit, List<Object> paras)
			throws DaoAccessException {
		
		return getRecordCounts(sql, limit, paras.toArray());
	}

	@Override
	public int getRecordCounts(String sql, int limit, Map<String, Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		String cSql = getParser().getCountSql(sql);
		log.info("jcbksql-count-" + cSql);
		Object result = getField(cSql, paras);
		return Integer.valueOf((result==null)?"0":String.valueOf(result));
	}


	/**
	 * 增加分页参数(分页启始行、页大小) 
	 */
	private List<Object> addPageParameters(List<Object> paras, PagingParameter pagingParameter, Parser parser){
		List<Object> plist = new ArrayList<Object>(2);
		/* 结束行或页大小 */
		plist.add(pagingParameter.getEndRow(parser.getDialect()));
		/* 开始 */
		plist.add(pagingParameter.getStartRow());
		Dialect dialect = parser.getDialect();
		switch (dialect) {
	        case mysql:
	        	Collections.reverse(plist); /*MYSQL*/
	            break;
	        default:
                break;
		}
		if(paras != null){
			List<Object> temp = Collections.synchronizedList(paras);
			plist.addAll(0, temp);
		}
		return plist;
	}
	
	/**
	 * 增加分页参数(分页启始行、页大小) 
	 */
	private Map<String, Object> addPageParameters(Map<String, Object> paras, PagingParameter pagingParameter, Parser parser){
		Map<String, Object> pMap = new HashMap<String, Object>(2);
		pMap.put(AbstractParser.START_INDEX_NAME, pagingParameter.getStartRow());
		pMap.put(AbstractParser.END_INDEX_NAME, pagingParameter.getEndRow(parser.getDialect()));
		if(paras != null){
			paras.putAll(pMap);
			return paras;
		}
		return pMap;
	}
	
	@Override
	public List<Map<String, Object>> getRecordData(String sql,
			PagingParameter pagingParameter, Object... paras)
			throws DaoAccessException {
		
		return getRecordData(sql, pagingParameter, Arrays.asList(paras));
	}
	
	@Override
	public List<Map<String, Object>> getRecordData(String sql,
			PagingParameter pagingParameter, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Parser parser = getParser();
		String pSql = parser.getPageSql(sql);
		log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
		List<Object> _paras = addPageParameters(paras, pagingParameter, parser);
		return getList(pSql, _paras);
	}

	@Override
	public List<Map<String, Object>> getRecordData(String sql,
			PagingParameter pagingParameter, Map<String, Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Parser parser = getParser();
		String pSql = parser.getPageSqlForPlace(sql);
		log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
		Map<String, Object> _paras = addPageParameters(paras, pagingParameter, parser);
		return getList(pSql, _paras);
	}

	@Override
	public <T> List<T> getRecordData(String sql, Class<T> classz,
			PagingParameter pagingParameter, Object... paras)
			throws DaoAccessException {
		
		return getRecordData(sql, classz, pagingParameter, Arrays.asList(paras));
	}

	@Override
	public <T> List<T> getRecordData(String sql, Class<T> classz,
			PagingParameter pagingParameter, List<Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Parser parser = getParser();
		String pSql = parser.getPageSqlForPlace(sql);
		log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
		List<Object> _paras = addPageParameters(paras, pagingParameter, parser);
		return getList(pSql, classz, _paras);
	}

	@Override
	public <T> List<T> getRecordData(String sql, Class<T> classz,
			PagingParameter pagingParameter, Map<String, Object> paras)
			throws DaoAccessException {
		log.info("jcbksql-" + sql);
		Parser parser = getParser();
		String pSql = parser.getPageSqlForPlace(sql);
		log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
		Map<String, Object> _paras = addPageParameters(paras, pagingParameter, parser);
		return getList(pSql, classz, _paras);
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, Object... paras) throws DaoAccessException {
		
		return getPageForMap(sql, curPage, pageSize, Arrays.asList(paras));
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, List<Object> paras) throws DaoAccessException {
		int total = getRecordCounts(sql, 0, paras);
		PagingParameter pp = new PagingParameter(curPage, pageSize, total);
		List<Map<String, Object>> list = getRecordData(sql, pp, paras);
		return new DataStore(total,list).getEntity();
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, Map<String, Object> paras) throws DaoAccessException {
		int total = getRecordCounts(sql, 0, paras);
		PagingParameter pp = new PagingParameter(curPage, pageSize, total);
		List<Map<String, Object>> list = getRecordData(sql, pp, paras);
		return new DataStore(total,list).getEntity();
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, Class<?> clazz, Object... paras)
			throws DaoAccessException {
		
		return getPageForMap(sql, curPage, pageSize, clazz, Arrays.asList(paras));
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, Class<?> clazz, List<Object> paras)
			throws DaoAccessException {
		int total = getRecordCounts(sql, 0, paras);
		PagingParameter pp = new PagingParameter(curPage, pageSize, total);
		List<?> list = getRecordData(sql, clazz, pp, paras);
		return new DataStore(total,list).getEntity();
	}

	@Override
	public Map<String, Object> getPageForMap(String sql, int curPage,
			int pageSize, Class<?> clazz, Map<String, Object> paras)
			throws DaoAccessException {
		int total = getRecordCounts(sql, 0, paras);
		PagingParameter pp = new PagingParameter(curPage, pageSize, total);
		List<?> list = getRecordData(sql, clazz, pp, paras);
		return new DataStore(total,list).getEntity();
	}
	
	@Override
	public void update(String sql, Object... paras) throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
	
	@Override
	public void update(String sql, List<Object> paras)
			throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
	
	@Override
	public void update(String sql, Map<String, Object> paras)
			throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
	
	@Override
	public void insert(String sql, Object... paras) throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
	
	@Override
	public void insert(String sql, List<Object> paras)
			throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
	
	@Override
	public void insert(String sql, Map<String, Object> paras)
			throws DaoAccessException {
		// TODO Auto-generated method stub
		throw new DaoAccessException("暂不支持该操作!");
	}
}

 /*为了实现动态修改数据源名称，增加了适配类*/
package com.sunshine.basic.dao;

import org.springframework.beans.BeansException;
import org.springframework.jdbc.core.JdbcTemplate;

import com.sunshine.basic.dao.AbstractJdbcCommDao;
import com.sunshine.basic.dao.parser.AbstractParser;
import com.sunshine.basic.dao.parser.Dialect;
import com.sunshine.basic.dao.parser.Parser;
import com.sunshine.basic.exception.DaoAccessException;
import com.sunshine.basic.tools.ApplicationContextTools;

/**
 * 利用JdbcAdapterDaoProxy生成子类代理，然后修改数据源名称
 * @see com.sunshine.monitor.comm.dao.adapter.JdbcAdapterDaoProxy
 * @author OY
 *
 */
public abstract class AbstractJdbcAdapterDao extends AbstractJdbcCommDao {
	
	/* 静态数据源 */
	private String jdbcTemplateName;
	
	private Dialect dialect;
	
	/**
	 * 子类实现静态JdbcTemplate操作模板及方言
	 * @param jdbcTemplateName
	 * @param dialect
	 */
	public AbstractJdbcAdapterDao(String jdbcTemplateName, Dialect dialect){
		
		this.jdbcTemplateName = jdbcTemplateName;
		
		this.dialect = dialect;
	}
	/*动态修改数据源名称*/
	public final void setJdbcTemplateName(String jdbcTemplateName){
		
		this.jdbcTemplateName = jdbcTemplateName;
	}
	
	@Override
	public final JdbcTemplate getSubJdbcTemplate() throws DaoAccessException {
		JdbcTemplate jdbcTemplate = null;
		try {
			jdbcTemplate = ApplicationContextTools.getBean(
						this.jdbcTemplateName, JdbcTemplate.class);
			if(jdbcTemplate == null)
				throw new DaoAccessException("JdbcTemplate实例访问失败!");
		} catch (BeansException e) {
			e.printStackTrace();
			throw new DaoAccessException("JdbcTemplate实例访问失败!");
		}
		return jdbcTemplate;
	}
	
	@Override
	public Parser getParser() throws DaoAccessException {
		
		return AbstractParser.newParser(this.dialect);
	}
}

 /*Oracle实现类*/
package com.sunshine.basic.dao.jdbc;

import org.springframework.stereotype.Repository;

import com.sunshine.basic.dao.AbstractJdbcAdapterDao;
import com.sunshine.basic.dao.parser.Dialect;

@Repository("oracleJdbcDao")
public class OracleJdbcDao extends AbstractJdbcAdapterDao {

	public OracleJdbcDao(){
		super("jdbcTemplate", Dialect.of("oracle"));
	}
}

/*GreenPum实现类*/
package com.sunshine.basic.dao.jdbc;

import org.springframework.stereotype.Repository;

import com.sunshine.basic.dao.AbstractJdbcAdapterDao;
import com.sunshine.basic.dao.parser.Dialect;

/**
 * @author oy
 *
 */
@Repository("greenPlumJdbcDao")
public class GreenPlumJdbcDao extends AbstractJdbcAdapterDao {

	public GreenPlumJdbcDao(){
		super("gpJdbcTemplate",Dialect.of("postgresql"));
	}

}

 package com.sunshine.basic.dao.proxy;

import java.lang.reflect.Method;
import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;
/**
 * 生成代理对象
 * @author oy
 *
 */
public class JdbcAdapterDaoProxy implements MethodInterceptor{
	
	private Object target;  
	 
	public JdbcAdapterDaoProxy(){
		
	}

	public Object createInstance(Object target){
		this.target = target;
		Enhancer enhancer = new Enhancer();  
        enhancer.setSuperclass(this.target.getClass());  
        // 回调方法  
        enhancer.setCallback(this);  
        // 创建代理对象  
        return enhancer.create();  		
	}
	
	@Override
	public Object intercept(Object obj, Method method, Object[] args,
			MethodProxy proxy) throws Throwable {
		
		return proxy.invokeSuper(obj, args);
	}
	
}

引用

  数据库方言及SQL智能分页

 package com.sunshine.basic.dao.parser;

public interface Parser {

    /**
     * 获取总数sql - 如果要支持其他数据库，修改这里就可以
     *
     * @param sql 原查询sql
     * @return 返回count查询sql
     */
    String getCountSql(String sql);

    /**
     * 获取分页sql - 如果要支持其他数据库，修改这里就可以
     *
     * @param sql 原查询sql
     * @return 返回分页sql
     */
    String getPageSql(String sql);
    
    /**
     * 
     * @param sql
     * @return
     */
    String getPageSqlForPlace(String sql);
    
    /**
     * 获取方言
     * @return
     */
    Dialect getDialect();
    
    /**
     * 设置方言
     * @param dialect
     */
    void setDialect(Dialect dialect);

}

package com.sunshine.basic.dao.parser;

import com.sunshine.basic.dao.parser.impl.MysqlParser;
import com.sunshine.basic.dao.parser.impl.OracleParser;
import com.sunshine.basic.dao.parser.impl.PostgreSQLParser;

public abstract class AbstractParser implements Parser{
	
	//处理SQL
    public static final SqlParser sqlParser = new SqlParser();
    
    private Dialect dialect;
    
    public static final String END_INDEX_NAME="endIndex";
    
    public static final String START_INDEX_NAME = "startIndex";
    
    public Dialect getDialect(){
    	
    	return dialect;
    }
    
    public void setDialect(Dialect dialect){
    	
    	this.dialect = dialect;
    }
    
    public static Parser newParser(Dialect dialect) {
    	Parser parser = null;
        switch (dialect) {
        	case postgresql:
        		 parser = new PostgreSQLParser();
                 break;
            case mysql:
                parser = new MysqlParser();
                break;
            case oracle:
                parser = new OracleParser();
                break;
            default:
                break;
        }
        if(parser != null)
        	parser.setDialect(dialect);
        return parser;
    }

    public String getCountSql(final String sql) {
    	
        return sqlParser.getSmartCountSql(sql);
    }
    
    public abstract String getPageSql(String sql);

}

package com.sunshine.basic.dao.parser.impl;

import com.sunshine.basic.dao.parser.AbstractParser;

public class OracleParser extends AbstractParser {
	
	@Override
    public String getPageSql(String sql) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
        sqlBuilder.append("select * from ( select tmp_page.*, rownum row_id from ( ");
        sqlBuilder.append(sql);
        sqlBuilder.append(" ) tmp_page where rownum <= ? ) where row_id > ?");
        return sqlBuilder.toString();
    }
	
	@Override
	public String getPageSqlForPlace(String sql) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
        sqlBuilder.append("select * from ( select tmp_page.*, rownum row_id from ( ");
        sqlBuilder.append(sql);
        sqlBuilder.append(" ) tmp_page where rownum <= :");
        sqlBuilder.append(END_INDEX_NAME);
        sqlBuilder.append(") where row_id > :");
        sqlBuilder.append(START_INDEX_NAME);
        return sqlBuilder.toString();
    }
}

/*方言*/
package com.sunshine.basic.dao.parser;

public enum Dialect {
	
    mysql, oracle, postgresql;

    public static Dialect of(String dialect) {
        try {
            Dialect d = Dialect.valueOf(dialect);
            return d;
        } catch (IllegalArgumentException e) {
            String dialects = null;
            for (Dialect d : Dialect.values()) {
                if (dialects == null) {
                    dialects = d.toString();
                } else {
                    dialects += "," + d;
                }
            }
            throw new IllegalArgumentException("分页dialect参数值错误，可选值为[" + dialects + "]");
        }
    }

    public static String[] dialects() {
        Dialect[] dialects = Dialect.values();
        String[] ds = new String[dialects.length];
        for (int i = 0; i < dialects.length; i++) {
            ds[i] = dialects[i].toString();
        }
        return ds;
    }
    
    public static String fromJdbcUrl(String jdbcUrl) {
        String[] dialects = dialects();
        for (String dialect : dialects) {
            if (jdbcUrl.indexOf(":" + dialect + ":") != -1) {
                return dialect;
            }
        }
        return null;
    }
}

/*SQL智分析，依赖jsqlparse.jar*/
package com.sunshine.basic.dao.parser;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;

import org.apache.log4j.Logger;

/**
 * sql解析类，提供更智能的count查询sql
 */
public class SqlParser {
	
    private static final List<SelectItem> COUNT_ITEM;
    
    private static final Alias TABLE_ALIAS;
    
    private Logger log = Logger.getLogger(SqlParser.class);

    static {
        COUNT_ITEM = new ArrayList<SelectItem>();
        COUNT_ITEM.add(new SelectExpressionItem(new Column("count(1)")));

        TABLE_ALIAS = new Alias("table_count");
        TABLE_ALIAS.setUseAs(false);
    }

    // 缓存已经修改过的sql
    private Map<String, String> CACHE = new ConcurrentHashMap<String, String>();

    public void isSupportedSql(String sql) {
        if (sql.trim().toUpperCase().endsWith("FOR UPDATE")) {
            throw new RuntimeException("分页不支持包含for update的sql");
        }
    }

    /**
     * 获取智能的countSql
     *
     * @param sql
     * @return
     */
    public String getSmartCountSql(String sql) {
    	log.info("Sql parse before:" + sql);
        //校验是否支持该sql
        isSupportedSql(sql);
        if (CACHE.get(sql) != null) {
        	log.info("Count sql parse(Cache):" + CACHE.get(sql));
            return CACHE.get(sql);
        }
        //解析SQL
        Statement stmt = null;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (Throwable e) {
            //无法解析的用一般方法返回count语句
            String countSql = getSimpleCountSql(sql);
            CACHE.put(sql, countSql);
            return countSql;
        }
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //处理body-去order by
        processSelectBody(selectBody);
        //处理with-去order by
        processWithItemsList(select.getWithItemsList());
        //处理为count查询
        sqlToCount(select);
        String result = select.toString();
        CACHE.put(sql, result);
        log.info("Sql parse after:" + sql);
        return result;
    }

    /**
     * 获取普通的Count-sql
     * @param sql 原查询sql
     * @return 返回count查询sql
     */
    public String getSimpleCountSql(final String sql) {
        isSupportedSql(sql);
        StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
        stringBuilder.append("select count(*) from (");
        stringBuilder.append(sql);
        stringBuilder.append(") tmp_count");
        return stringBuilder.toString();
    }

    /**
     * 将sql转换为count查询
     * @param select
     */
    public void sqlToCount(Select select) {
        SelectBody selectBody = select.getSelectBody();
        // 是否能简化count查询
        if (selectBody instanceof PlainSelect && isSimpleCount((PlainSelect) selectBody)) {
            ((PlainSelect) selectBody).setSelectItems(COUNT_ITEM);
        } else {
            PlainSelect plainSelect = new PlainSelect();
            SubSelect subSelect = new SubSelect();
            subSelect.setSelectBody(selectBody);
            subSelect.setAlias(TABLE_ALIAS);
            plainSelect.setFromItem(subSelect);
            plainSelect.setSelectItems(COUNT_ITEM);
            select.setSelectBody(plainSelect);
        }
    }

    /**
     * 是否可以用简单的count查询方式
     * @param select
     * @return
     */
    public boolean isSimpleCount(PlainSelect select) {
        //包含group by的时候不可以
        if (select.getGroupByColumnReferences() != null) {
            return false;
        }
        //包含distinct的时候不可以
        if (select.getDistinct() != null) {
            return false;
        }
        for (SelectItem item : select.getSelectItems()) {
            //select列中包含参数的时候不可以，否则会引起参数个数错误
            if (item.toString().contains("?")) {
                return false;
            }
            //如果查询列中包含函数，也不可以，函数可能会聚合列
            if (item instanceof SelectExpressionItem) {
                if (((SelectExpressionItem) item).getExpression() instanceof Function) {
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 处理selectBody去除Order by
     *
     * @param selectBody
     */
    public void processSelectBody(SelectBody selectBody) {
        if (selectBody instanceof PlainSelect) {
            processPlainSelect((PlainSelect) selectBody);
        } else if (selectBody instanceof WithItem) {
            WithItem withItem = (WithItem) selectBody;
            if (withItem.getSelectBody() != null) {
                processSelectBody(withItem.getSelectBody());
            }
        } else {
            SetOperationList operationList = (SetOperationList) selectBody;
            if (operationList.getPlainSelects() != null && operationList.getPlainSelects().size() > 0) {
                List<PlainSelect> plainSelects = operationList.getPlainSelects();
                for (PlainSelect plainSelect : plainSelects) {
                    processPlainSelect(plainSelect);
                }
            }
            if (!orderByHashParameters(operationList.getOrderByElements())) {
                operationList.setOrderByElements(null);
            }
        }
    }

    /**
     * 处理PlainSelect类型的selectBody
     *
     * @param plainSelect
     */
    public void processPlainSelect(PlainSelect plainSelect) {
        if (!orderByHashParameters(plainSelect.getOrderByElements())) {
            plainSelect.setOrderByElements(null);
        }
        if (plainSelect.getFromItem() != null) {
            processFromItem(plainSelect.getFromItem());
        }
        if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
            List<Join> joins = plainSelect.getJoins();
            for (Join join : joins) {
                if (join.getRightItem() != null) {
                    processFromItem(join.getRightItem());
                }
            }
        }
    }

    /**
     * 处理WithItem
     *
     * @param withItemsList
     */
    public void processWithItemsList(List<WithItem> withItemsList) {
        if (withItemsList != null && withItemsList.size() > 0) {
            for (WithItem item : withItemsList) {
                processSelectBody(item.getSelectBody());
            }
        }
    }

    /**
     * 处理子查询
     *
     * @param fromItem
     */
    public void processFromItem(FromItem fromItem) {
        if (fromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin) fromItem;
            if (subJoin.getJoin() != null) {
                if (subJoin.getJoin().getRightItem() != null) {
                    processFromItem(subJoin.getJoin().getRightItem());
                }
            }
            if (subJoin.getLeft() != null) {
                processFromItem(subJoin.getLeft());
            }
        } else if (fromItem instanceof SubSelect) {
            SubSelect subSelect = (SubSelect) fromItem;
            if (subSelect.getSelectBody() != null) {
                processSelectBody(subSelect.getSelectBody());
            }
        } else if (fromItem instanceof ValuesList) {

        } else if (fromItem instanceof LateralSubSelect) {
            LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
            if (lateralSubSelect.getSubSelect() != null) {
                SubSelect subSelect = lateralSubSelect.getSubSelect();
                if (subSelect.getSelectBody() != null) {
                    processSelectBody(subSelect.getSelectBody());
                }
            }
        }
        //Table时不用处理
    }

    /**
     * 判断Orderby是否包含参数，有参数的不能去
     *
     * @param orderByElements
     * @return
     */
    public boolean orderByHashParameters(List<OrderByElement> orderByElements) {
        if (orderByElements == null) {
            return false;
        }
        for (OrderByElement orderByElement : orderByElements) {
            if (orderByElement.toString().contains("?")) {
                return true;
            }
        }
        return false;
    }
    
	public static void main(String[] args) {
		String countSql = new SqlParser()
		.getSmartCountSql("select count(t.hphm) as GCCS, t.hphm as HPHM, t.hpys as HPYS, t.hpzl as HPZL   from veh_passrec t  where t.hphm like '粤A_____' and t.gcsj > '2010-02-18 00:00:00' and t.gcsj <= '2010-02-21 23:59:59'  group by t.hphm, t.hpys, t.hpzl");
		System.out.println(countSql);
	}
}

/*分页参数对象*/
package com.sunshine.basic.dao.page;

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;

import com.sunshine.basic.dao.parser.Dialect;

/**
 * 封装分页参数
 */
public class PagingParameter implements Serializable{
	
	private static final long serialVersionUID = -5871263750693828476L;

	/** 分页起始行，默认为-1，表示不分页，查询全部记录 */
	private int curPage = -1;
	/** 每页显示行数，默认为0，表示不分页，查询全部记录 */
	private int pageSize = 0;
	/** 总记录*/
	private int totalRows = 0;
	
	/**
	 * 构造方法，不指定分页起始行和每页显示行数，默认不分页，查询全部记录
	 */
	public PagingParameter(){
	}
	
	/**
	 * 构造方法
	 * @param start
	 * @param pageSize
	 */
	public PagingParameter(int curPage,int pageSize, int totalRows){
		this.curPage = curPage;
		this.pageSize = pageSize;
		this.totalRows = totalRows;
	}
	
	public int getTotalRows() {
		return totalRows;
	}

	public int getCurPage() {
		return curPage;
	}
	
	public void setCurPage(int curPage) {
		this.curPage = curPage;
	}
	
	public int getPageSize() {
		return pageSize;
	}
	
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}

	/**
	 * 判断分页参数是否无效，如果返回true(表示分页参数无效)则不分页，查询全部的记录
	 *
	 * @return
	 */
	public boolean isInvalid() {
		return curPage < 0 || pageSize <= 0;
	}

	/**
	 * 构造开始行与结束行
	 * @return
	 */
	public Map<String,Integer> getStartAndEndRow(){
		// 总页数
		int totalPages = totalPage();
		Map<String,Integer> map = null;
		// 起始行数
		int start = (curPage - 1) * pageSize;
		// 结束行数
		int end = 0;
		if (totalRows < pageSize) {
			end = totalRows;
		} else if ((totalRows % pageSize == 0)
				|| (totalRows % pageSize != 0 && curPage < totalPages)) {
			end = curPage * pageSize;
		} else if (totalRows % pageSize != 0 && curPage == totalPages) {// 最后一页
			end = totalRows;
		}
		map = new HashMap<String,Integer>();
		map.put("start", start);
		map.put("end", end);
		return map ;
	}
	
	/**
	 * 结束行
	 * @return
	 */
	public int getEndRow(Dialect dialect){
		int end = 0;
		switch (dialect) {
            case mysql:
            	end = pageSize;
                break;
            case postgresql:
            	end = pageSize;
            	break;
            case oracle:
        		end = getOracleEndRow();
                break;
            default:
                break;
        }
		return end;
	}
	
	public int getOracleEndRow(){
		int end = 0;
		int totalPages = totalPage();
		if (totalRows < pageSize) {
			end = totalRows;
		} else if ((totalRows % pageSize == 0)
				|| (totalRows % pageSize != 0 && curPage < totalPages)) {
			end = curPage * pageSize;
		} else if (totalRows % pageSize != 0 && curPage == totalPages) {// 最后一页
			end = totalRows;
		}
		return end;
	}
	
	
	/**
	 * 开始行
	 * @return
	 */
	public int getStartRow(){
		
		return (curPage - 1) * pageSize;
	}
	
	/**
	 * 总页数
	 * @return
	 */
	public int totalPage(){
		int totalPages = 0;
		if (pageSize != -1) {
			int pc = (int)Math.ceil(totalRows/pageSize);
			totalPages = (pc == 0) ? 1 : pc;
		} else {
			totalPages = 1;
		}
		return totalPages;
	}
}

评论

1 楼 arong 2016-01-26  

不错，学习了。