package com.runwit.ebookstore.services.dao;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public abstract class DbDAO {
	static {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			System.out.println("JDBC驅動加載成功！");
		} catch (ClassNotFoundException cnf) {
			cnf.printStackTrace();
		}
	}

	protected Connection conn;

	protected PreparedStatement pstmt;

	protected Statement stmt;

	protected ResultSet rs;

	private String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=book";

	private String userName = "shen";

	private String userPwd = "shen";

	protected DbDAO() {
		try {
			DbConfig config = DbConfig.getInstance();
			url = config.getUrl();
			userName = config.getUserName();
			userPwd = config.getUserPwd();

		} catch (IOException ex) {
			System.err.println("加載數據庫配置文件失敗");
			ex.printStackTrace();
		}
	}

	protected void openConnection() throws SQLException {
		if (conn == null)
			conn = DriverManager.getConnection(url, userName, userPwd);

	}

	protected void openStatement() throws SQLException {
		if (stmt == null)
			stmt = conn.createStatement();
	}

	protected void openPreparedStatement(String sql) throws SQLException {
		if (pstmt == null)
			pstmt = conn.prepareStatement(sql);

	}

	protected void closeConnection() {
		try {
			conn.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			conn = null;
		}
	}

	protected void closeStatement() {
		try {
			stmt.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			stmt = null;
		}

	}

	protected void closePreparedStatement() {
		try {
			pstmt.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			pstmt = null;
		}

	}

	public int updateBySql(String sql) throws SQLException {
		try {
			openConnection();
			openStatement();
			DebugUtil.printSql(sql);
			return stmt.executeUpdate(sql);

		} finally {
			closeStatement();
			closeConnection();
		}
	}

	public int updateBySql(String sql, Object[] paramValues, int[] types)
			throws SQLException {
		try {
			DebugUtil.printSql(sql);
			openConnection();
			openPreparedStatement(sql);

			// 參數綁定

			for (int i = 0; i < paramValues.length; i++) {
				bindingParam(pstmt, i + 1, paramValues[i], types[i]);
			}

			return pstmt.executeUpdate();

		} finally {
			closePreparedStatement();
			closeConnection();
		}

	}

	public void openResultSet(String sql) throws SQLException {
		rs = stmt.executeQuery(sql);
	}

	public void openResultSet(Object[] paramValues, int[] types)
			throws SQLException {
		// 參數綁定
		for (int i = 0; i < paramValues.length; i++) {
			bindingParam(pstmt, i + 1, paramValues[i], types[i]);
		}
		rs = pstmt.executeQuery();
	}

	public void closeResultSet() {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			rs = null;
		}
	}

	public List queryToMap(String sql) {
		List alRet = new ArrayList();
		try {
			openConnection();
			openStatement();
			openResultSet(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();

			while (rs.next()) {
				Map m = new HashMap();
				for (int i = 1; i <= colCount; i++)
					m.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i));
				alRet.add(m);
			}
			return alRet;

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			closeResultSet();
			closeStatement();
			closeConnection();
		}

		return alRet;

	}
	
	public long getMaxID(String columnName, String tableName) {
		List result = queryToMap("select max("+columnName+") as maxid from "+tableName);
		if(result.size() > 0) {
			HashMap m = (HashMap)result.get(0);
			Object val = m.get("maxid");
			if(val != null) {
				return ((Long)val).longValue();
			}
		}
		return -1;
	}

	public List queryToMap(String sql, Object[] paramValues, int[] types) {
		List alRet = new ArrayList();
		try {
			openConnection();
			openPreparedStatement(sql);
			openResultSet(paramValues, types);
			ResultSetMetaData meta = rs.getMetaData();
			int colCount = meta.getColumnCount();

			while (rs.next()) {
				Map m = new HashMap();
				for (int i = 1; i <= colCount; i++)
					m.put(meta.getColumnName(i).toLowerCase(), rs.getObject(i));
				alRet.add(m);
			}
			return alRet;

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			closeResultSet();
			closePreparedStatement();
			closeConnection();
		}

		return alRet;

	}

	private void bindingParam(PreparedStatement pstmt, int idx, Object val,
			int type) throws SQLException {
		switch (type) {
		case SHORT_TYPE:
			pstmt.setShort(idx, ((Short) val).shortValue());
			break;
		case INT_TYPE:
			pstmt.setInt(idx, ((Integer) val).intValue());
			break;
		case STRING_TYPE:
			pstmt.setString(idx, val.toString());
			break;
		case OBJECT_TYPE:
			pstmt.setObject(idx, val);
			break;
		case DATE_TYPE:
			pstmt.setDate(idx, (Date) val);
			break;
		case BYTEARRAY_TYPE:
			pstmt.setBytes(idx, (byte[])val);
			break;
		case BIGDECIMAL_TYPE:
			pstmt.setBigDecimal(idx, (BigDecimal)val);
			break;
		}
	}

	public List queryRowToMap(String sql) throws SQLException {
		System.out.println(sql);
		try {
			openConnection();
			openStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount(); // 列總數
			System.out.println("列總數:" + columnCount);
			List alRet = new ArrayList();
			while (rs.next()) {
				HashMap rowMap = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String columnName = meta.getColumnName(i).toLowerCase(); // 列名
					Object obj = rs.getObject(i);// 列值
					rowMap.put(columnName, obj);
				}
				alRet.add(rowMap);
			}
			return alRet;
		} finally {
			closeStatement();
			closeConnection();
		}
	}

	public List queryBySql(String sql) throws SQLException {
		System.out.println(sql);
		try {
			openConnection();
			openStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount(); // 列總數
			System.out.println("列總數:" + columnCount);
			List alRet = new ArrayList();
			while (rs.next()) {
				alRet.add(mapRowToModel(rs));
//				HashMap rowMap = new HashMap();
//				for (int i = 1; i <= columnCount; i++) {
//					String columnName = meta.getColumnName(i).toLowerCase(); // 列名
//					Object obj = rs.getObject(i);// 列值
//					rowMap.put(columnName, obj);
//				}
//				alRet.add(rowMap);
			}
			return alRet;
		} finally {
			closeStatement();
			closeConnection();
		}
	}

	public static final int SHORT_TYPE = 1;

	public static final int INT_TYPE = 2;

	public static final int LONG_TYPE = 3;

	public static final int STRING_TYPE = 4;

	public static final int OBJECT_TYPE = 5;

	public static final int DATE_TYPE = 6;
	
	public static final int BYTEARRAY_TYPE = 7;
	
	public static final int BIGDECIMAL_TYPE = 8;
	
	public abstract Object mapRowToModel(ResultSet rs) throws SQLException;
}