package com.maysnow;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 访问数据库的工具类
 * @author MaySnow
 *
 */
public class DBHelp<T> {
	//一定要先导入jar包mysql-connector-java-5.0.5-bin.jar
	private final String DRIVER = "com.mysql.jdbc.Driver";
	private final String URL = "jdbc:mysql://localhost:3306/mydb";
	private final String NAME = "root";
	private final String PWD = "root";

	/**
	 * 获取数据库连接对象
	 * @return Connection类的对象
	 */
	public Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, NAME, PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public List<T> executeQueryForList(String sql, RowMapper<T> rm, Object...args) { //相当于是一个args数组，里面是Object类型的数
		Connection conn= null;
		PreparedStatement stat = null;
		ResultSet rs = null;

		List<T> list = new ArrayList<T>();

		try {
			conn = getConnection();//连接数据库
			stat = conn.prepareStatement(sql);//创建一个preparedStatement对象来将参数化的sql语句发送到数据库

			//给指定参数赋值
			for(int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}

			rs = stat.executeQuery();

			while(rs.next()) { //rs.next(),Resultset光标最初位于第一行之前；当第一次调用next()方法使第一行成为当前行；第二次调用使第二行成为当前行；依次类推
				T obj = rm.mapRow(rs);
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return list;

	}

	public T executeQueryForObject(String sql, RowMapper<T> rm, Object...args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T obj = null;

		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);

			for(int i = 0; i<args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}

			rs = stat.executeQuery();

			if(rs.next()) {
				obj = rm.mapRow(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return obj;
	}

	/**
	 *执行insert、update、delete语句
	 * @param sql insert or update or delete
	 * @param args
	 * @return true代表成功， false代表失败
	 */
	public boolean executeSQL(String sql, Object...args) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);

			for(int i = 0; i<args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}

			int rows = stat.executeUpdate();
			if(rows > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stat, conn);
		}

		return false;
	}

	/**
	 * 释放数据库资源（注意finally）
	 * @param rs
	 * @param stat
	 * @param conn
	 */
	public void close(ResultSet rs, Statement stat, Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null) {
					stat.cancel();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 释放数据库资源(方法重载)
	 * @param stat
	 * @param conn
	 */
	public void close(Statement stat, Connection conn) {
		close(null, stat, conn);
	}

}