package com.swt.jdbc.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

public class JdbcUtils {

	private static JdbcUtils instance = new JdbcUtils();

	public static JdbcUtils getInstance() {
		return instance;
	}

	private Connection getConn() {
		Connection conn = null;
		try {
			// DbUtils.loadDriver("com.mysql.jdbc.Driver");
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=UTF8", "root", "root");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 获取全部内容(使用MapListHandler)
	 *
	 * @return
	 */
	public List<?> getAll(String sql) throws SQLException {
		return query(sql, null, null);
	}

	/**
	 * 获取全部内容(使用BeanListHandler)
	 *
	 * @return
	 */
	public List<?> getAll(String sql, Class<?> clazz) throws SQLException {
		return query(sql, null, clazz);
	}

	/**
	 * 根据条件获取内容
	 *
	 * @param id
	 * @return
	 */
	public List<?> getById(String sql, String id, Class<?> clazz) throws SQLException {
		Object[] object = new Object[1];
		object[0] = id;
		return query(sql, object, clazz);
	}

	/**
	 * 添加内容
	 *
	 * @throws SQLException
	 */
	public void insert(String sql, Object[] param) throws SQLException {
		operDB(sql, param);
	}

	/**
	 * 根据ID修改内容
	 *
	 * @throws SQLException
	 */
	public void update(String sql, Object[] param) throws SQLException {
		operDB(sql, param);
	}

	/**
	 * 根据ID删除内容
	 *
	 * @throws SQLException
	 */
	public void delete(String sql, String id) throws SQLException {
		Object[] object = new Object[1];
		object[0] = id;
		operDB(sql, object);
	}

	/**
	 * 数据库操作方法
	 *
	 * @param sql
	 *            sql语句
	 * @param object
	 *            参数
	 * @throws SQLException
	 */
	private void operDB(String sql, Object[] object) throws SQLException {
		Connection conn = this.getConn();
		try {
			QueryRunner qr = new QueryRunner();
			qr.update(conn, sql, object);
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtils.close(conn);
		}
	}

	/**
	 * 查询操作
	 *
	 * @param sql
	 *            sql语句
	 * @param param
	 *            参数
	 * @return
	 * @throws SQLException
	 */
	public List<?> query(String sql, Object[] param, Class<?> clazz) throws SQLException {
		List<?> list = null;
		Connection conn = this.getConn();
		QueryRunner qr = new QueryRunner();
		try {
			if (clazz != null) {
				list = (List<?>) qr.query(conn, sql, new BeanListHandler(clazz), param);
			} else {
				list = (List<?>) qr.query(conn, sql, new MapListHandler(), param);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtils.close(conn);
		}
		return list;
	}

	public static void main(String[] args) {

		try {
			List<?> l = JdbcUtils.getInstance().getAll("select * from t_user");
			for (Object temp : l) {
				Map<?, ?> map = (Map<?, ?>) temp;
				System.out.println(map.get("name"));
			}
			List<?> list = JdbcUtils.getInstance().getAll("select * from t_user", UserBean.class);
			for (Object temp : list) {
				UserBean ib = (UserBean) temp;
				System.out.println(ib.getName());
			}

			List<?> ll = JdbcUtils.getInstance().getById("select * from t_user where id=?", "2", UserBean.class);
			for (Object temp : ll) {
				UserBean ib = (UserBean) temp;
				System.out.println(ib.getName());
			}
			JdbcUtils.getInstance().insert("insert into t_user (name, age) values ( ?, ?)", new Object[] { "hjj", 23 });
			JdbcUtils.getInstance().update("update t_user set name =? , age=? where id=?",
					new Object[] { "hjj", 23, 1 });
			JdbcUtils.getInstance().delete("delete from t_user where id =?", "1");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}