package org.virtus.study.remotesql.app.base.data;

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

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.virtus.study.remotesql.app.base.data.service.SQLPersister;
import org.virtus.study.remotesql.app.base.ex.DatabaseNotSupportException;

/**
 * 描述 ：spring jdbc 扩展
 *
 * @author Sylar
 *
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public class JdbcDaoSupportEx extends NamedParameterJdbcDaoSupport implements SQLPersister{
	
	private final static int DBTYPE_ORACLE=0;
	private final static int DBTYPE_MYSQL=1;
	
	private int dbmsType=DBTYPE_ORACLE;
	
	public void setDbType(String type)
	{
		this.dbmsType=type.indexOf("oracle")>=0 ? DBTYPE_ORACLE : DBTYPE_MYSQL;
	}
	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param param
	 * @return
	 */
	@Override
	public Map getOrderedRow(String sql, Object... param) {
		SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, param);
		if(rs == null) {
			return null;
		}
		Map result = new LinkedHashMap();
		int columnCount = rs.getMetaData().getColumnCount();
		for(int i=1; i<=columnCount; i++) {
			//rs.getMetaData().getColumnName(i) 获取不到别名   改用getColumnLabel
			//rs.first()判断是否有数据
			result.put(rs.getMetaData().getColumnLabel(i), rs.first() ? rs.getString(i) : null);
		}
		return result;
	}

	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param params
	 * @return
	 */
	@Override
	public List<?> find(String sql, Object... params) {
		return this.getJdbcTemplate().query(sql, params, new QueryRowMapper());
	}

	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param pageNow
	 * @param pageSize
	 * @param sql
	 * @param params
	 * @return
	 */
	@Override
	public Pager findPagination(int pageNow, int pageSize, String sql,
			Object... params) {
		String countSQL = " SELECT count(*) c FROM (" + sql + ") count";
		int recordCount = Integer.parseInt(this.getOne(countSQL, params));

		int m = (recordCount % pageSize);
		int pageCount = (int) (recordCount - m) / pageSize + (m > 0 ? 1 : 0);
		if (pageCount <= 0) pageCount = 1;

		if (pageNow > pageCount) pageNow = pageCount;
		if (pageNow <= 0) pageNow = 1;

		String pageSql = this.getPaginationSql(sql, pageNow, pageSize);
		
		Pager pager = new Pager(pageNow, pageSize);
		pager.setRows(find(pageSql, params));
		
		pager.setRecordCount(recordCount);
		pager.setPageCount(pageCount);

		return pager;
	}

	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param pageNow
	 * @param pageSize
	 * @return
	 */
	private String getPaginationSql(String sql, int pageNow, int pageSize) {
		int startPos = (pageNow - 1) * pageSize;
		
		switch(this.dbmsType)
		{
			case DBTYPE_ORACLE: return this.getPageSqlOracle(sql, startPos, pageSize);
			case DBTYPE_MYSQL: return this.getPageSqlMysql(sql, startPos, pageSize);
		}
		
		throw new DatabaseNotSupportException("database type is not supported");
	}

	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param startPos
	 * @param pageSize
	 * @return
	 */
	private String getPageSqlMysql(String sql, int startPos, int pageSize) {
		StringBuffer buff = new StringBuffer();
		buff.append(sql).append(" LIMIT ").append(startPos).append(", ").append(pageSize);
		return buff.toString();
	}
	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param startPos
	 * @param pageSize
	 * @return
	 */
	private String getPageSqlOracle(String sql, int startPos, int pageSize) {
		StringBuffer buff = new StringBuffer();
		int endPos = startPos + pageSize;
		buff.append("SELECT *  FROM (SELECT A.*, ROWNUM rn FROM (").append(sql).append(") a ");
		buff.append(" WHERE ROWNUM <=").append(endPos).append(") b WHERE b.rn>").append(startPos);

		return buff.toString();
	}
	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param params
	 * @return
	 */
	@Override
	public int executeNonQuery(String sql, Object... params) {
		return this.getJdbcTemplate().update(sql, params);
	}

	/**
	 * 描述 ：
	 * 
	 * @author Sylar
	 * @param sql
	 * @param params
	 * @return
	 */
	@Override
	public String getOne(String sql, Object... params) {
		SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, params);
		return rs.next() ? rs.getString(1) : null;
	}

}