?

??? 在开发中,经常会使用到JdbcTemplate来进行数据的查询操作.特别是大批量数据查询操作的时候.

?

更是需要做很大功夫的SQL优化,这个时候,使用JdbcTemplate进行数据查询

?

是再合适不过了.

?

可是JdbcTemplate中只提供了一些基础数据操作,比如查询,比如执行SQL语句的功能.并没有提供分页查询,我们需要对JdbcTemplate的功能做一扩展,使其支持不同数据库的分页查询.

?

由于日常开发的时候,主要使用到Oracle和MySQL两个数据库,所以就写了针对这两种数据库的分页查询封装.

?

?

package com.faceye.components.core.dao.impl.jdbc;

import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.faceye.components.core.service.ISystemConfig;
import com.faceye.util.Page;
/**
 * JdbcTemppate数据操作.
 */
public class GenericJdbcDao extends JdbcDaoSupport
{
	private Log log = LogFactory.getLog(this.getClass());
	private String dbType = null;
	private ISystemConfig systemConfig = null;

	public ISystemConfig getSystemConfig()
	{
		return systemConfig;
	}
/**
 * 取得数据库类型.
 * @return
 */
	private String getDefaultDBType()
	{
		String dbType = getSystemConfig().get("system.db.type");
		if (StringUtils.isEmpty(dbType))
		{
			dbType = "MySQL";
		}
		return dbType;

	}

	public void setSystemConfig(ISystemConfig systemConfig)
	{
		this.systemConfig = systemConfig;
	}
/**
 * 数据分页查询.
 * @param queryString
 * @param startIndex
 * @param pageSize
 * @return
 */
	private String getPageSQL(String queryString, Integer startIndex, Integer pageSize)
	{
		String dbType = this.getDbType();
		return this.getPageSQL(queryString, dbType, startIndex, pageSize);
	}
/**
 * 数据分页查询
 * @param queryString:SQL
 * @param dbType:数据库类型
 * @param startIndex,起始索引
 * @param pageSize,分页大小
 * @return
 */
	private String getPageSQL(String queryString, String dbType, Integer startIndex, Integer pageSize)
	{
		String pageSQL = "";
		if (dbType.equals("MySQL"))
		{
			pageSQL = this.getMySQLPageSQL(queryString, startIndex, pageSize);
		} else if (dbType.equals("Oracle"))
		{
			pageSQL = this.getOraclePageSQL(queryString, startIndex, pageSize);
		}
		return pageSQL;
	}

	/**
	 * 构造MySQL数据分页SQL
	 * @param queryString
	 * @param startIndex
	 * @param pageSize
	 * @return
	 */
	private String getMySQLPageSQL(String queryString, Integer startIndex, Integer pageSize)
	{
		String result = "";
		if (null != startIndex && null != pageSize)
		{
			result = queryString + " limit " + startIndex + "," + pageSize;
		} else if (null != startIndex && null == pageSize)
		{
			result = queryString + " limit " + startIndex;
		} else
		{
			result = queryString;
		}
		return result;
	}
/**
 * 构造 Oracle数据分页SQL
 * @param queryString
 * @param startIndex
 * @param pageSize
 * @return
 */
	private String getOraclePageSQL(String queryString, Integer startIndex, Integer pageSize)
	{
		if (StringUtils.isEmpty(queryString))
		{
			return null;
		}
		String itemSource = queryString.toLowerCase();
		int endIndex = startIndex + pageSize;
		String endSql = "select * from (select rOraclePageSQL.*,ROWNUM as currentRow from (" +
		        queryString + ") rOraclePageSQL where rownum <" + endIndex + ") where currentRow>" + startIndex;
		return endSql;
	}
/**
 * 构造数据总数查询 SQL
 * @param queryString
 * @return
 */