package com.zte.webfile.dal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DataAdapter {

	private final static Logger log = Logger.getLogger(DataAdapter.class);

	/**
	 * 根據傳入的sql語句，獲取PreparedStatement對象
	 * 
	 * @param sql
	 *            sql語句
	 * @return
	 * @throws Exception 
	 */
	protected static PreparedStatement getPreparedStatement(String sql)
			throws Exception {
		DataSource ds = JNDIDataSourceFactory.getDataSource();
		Connection conn = ds.getConnection();
		PreparedStatement statement = conn.prepareStatement(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				// 使游標的精確移動功能有效
				ResultSet.CONCUR_UPDATABLE);
		System.out.println();
		return statement;
	}

	protected static CallableStatement getPrepareCall(String sql) throws Exception {
		DataSource ds = JNDIDataSourceFactory.getDataSource();
		Connection conn = ds.getConnection();
		CallableStatement cs = conn.prepareCall(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
				// 使游標的精確移動功能有效
				ResultSet.CONCUR_UPDATABLE);
		return cs;
	}

	/**
	 * 執行數據查詢操作，返回分頁后的結果集。若pageSize小于0，則不分頁。 使用方法： <code>
	 * 	      // sql語句
	 *        String sql = "select * from testTable where condi1 = ? " +
	 *        			"and condi2 = ?";
	 *        // 添加參數
	 *        Vector params = new Vector();
	 *        params.add("p1");
	 *        params.add("p2");
	 *        
	 *        // 執行查詢
	 *        Vector datas = DataAdapter.getData(sql, params);
	 *        
	 *        // 解析數據
	 *        if(datas != null) {
	 *	        for(int i = 0; i < datas.size; i++) {
	 *				Vector row = (Vector) datas.get[i];
	 *				for(int j = 0; j < row.size; j++) {
	 *					System.out.print(row.get[j] + "  ");
	 *				}
	 *				System.out.println();
	 *			}
	 *        }
	 * </code>
	 * @param sql
	 *            待執行的sql語句
	 * @param args
	 *            sql語句中的參數列表
	 * @param pageSize
	 *            數據集分頁大小
	 * @param pageNumber
	 *            數據集分頁頁號
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql, Vector params, int pageSize,
			int pageNumber) throws Exception {
		Vector datas = new Vector();

		try {
			PreparedStatement ps = getPreparedStatement(sql);

			// 設置查詢參數
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					//log.info(params.get(i));
					ps.setObject(i + 1, params.get(i));
				}

			}
			// 執行查詢
			Pageable rs = new PageableResultSet(ps.executeQuery());
			rs.setPageSize(pageSize);
			rs.gotoPage(pageNumber);
			// 放入緩沖列表
			ResultSetMetaData rsmd = rs.getMetaData();
			int i = 0;
			rs.previous();
			for (; i < rs.getPageRowsCount() && rs.next(); i++) {
				Vector row = new Vector();
				for (int j = 1; j < rsmd.getColumnCount() + 1; j++) {
					row.add(rs.getObject(j));
				}
				datas.add(row);
			}
			if (i == 0)
				return null;
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return null;
		}
		return datas;
	}

	public static Vector getDataByStoreProcedure(String sql, Vector params,
			int pageSize, int pageNumber) throws Exception {
		Vector datas = new Vector();

		try {
			CallableStatement cs = getPrepareCall(sql);

			// 設置查詢參數
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					// log.info(params.get(i));
					cs.setObject(i + 1, params.get(i));
				}

			}
			// 執行查詢
			Pageable rs = new PageableResultSet(cs.executeQuery());
			rs.setPageSize(pageSize);
			rs.gotoPage(pageNumber);
			// 放入緩沖列表
			ResultSetMetaData rsmd = rs.getMetaData();
			int i = 0;
			rs.previous();
			for (; i < rs.getPageRowsCount() && rs.next(); i++) {
				Vector row = new Vector();
				for (int j = 1; j < rsmd.getColumnCount() + 1; j++) {
					row.add(rs.getObject(j));
				}
				datas.add(row);
			}
			if (i == 0)
				return null;
			rs.close();
			cs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return null;
		}
		return datas;
	}

	/**
	 * 執行數據查詢操作，不分頁。
	 * 
	 * @param sql
	 *            待執行的sql語句
	 * @param args
	 *            sql語句中的參數列表
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql, Vector params) throws Exception {
		return getData(sql, params, 0, 0);
	}
	
	public static Vector getDataByStoreProcedure(String sql, Vector params) throws Exception {
		return getDataByStoreProcedure(sql, params, 0, 0);
	}

	/**
	 * 執行數據查詢操作，不提供參數
	 * 
	 * @param sql
	 *            待執行的sql語句
	 * @return
	 * @throws Exception 
	 */
	public static Vector getData(String sql) throws Exception {
		return getData(sql, null);
	}

	/**
	 * 執行單值查詢，對于只想得到一個數據的查詢，可以使用此方法。若沒有 有效數據，則返回null
	 * 
	 * @param sql
	 *            待執行的sql語句
	 * @param params
	 *            參數列表
	 * @return 數據查詢的唯一值
	 * @throws Exception 
	 */
	public static Object getSingleValue(String sql, Vector params) throws Exception {
		Vector datas = getData(sql, params);
		if ((datas == null) || (datas.size() == 0))
			return null;
		Vector row = (Vector) datas.get(0);
		if ((row == null) || (row.size() == 0))
			return null;
		return row.get(0);
	}

	/**
	 * 執行數據查詢操作
	 * 
	 * @param sql
	 *            待執行的sql語句
	 * @param params
	 *            sql語句中的參數列表
	 * @throws Exception 
	 */
	public static int update(String sql, Vector params) throws Exception {
		int returnValue = -1;
		try {
			PreparedStatement ps = getPreparedStatement(sql);

			// 設置查詢參數
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					// log.info(params.get(i));
					ps.setObject(i + 1, params.get(i));
				}
			}

			returnValue = ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}
		return returnValue;
	}

}