package com.ozs.mdm.util;

import java.sql.Connection;
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;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @ClassName: JDBCUtil
 * @Description: JDBC工具类
 * @author: Zhao You Kai
 * @date: Feb 21, 2019 10:34:35 AM
 */
public class JDBCUtil {
	private final Logger log = LoggerFactory.getLogger(this.getClass());

	Connection conn = null;
	PreparedStatement pstmt = null;
	Statement st = null;
	ResultSet rs = null;

	/**
	 * 执行数据库更新sql
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int update(String sql) throws Exception {
		int i = 0;
		log.info("执行SQL::" + sql);
		try {
			// 不自动提交
			conn.setAutoCommit(false);
			st = conn.createStatement();
			i = st.executeUpdate(sql);
		} catch (SQLException e) {
			log.error("SQL执行异常:" + e.getMessage());
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}

		return i;
	}

	/**
	 * 执行查询sql
	 * 
	 * @param sql
	 * @return
	 */
	public List<Map<String, Object>> getData(String sql) {
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			log.info("执行SQL::" + sql);
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			// 获得结果集结构信息（元数据）
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					// rs.getObject(i) 获得当前行某一列字段的值
					// 获得当前行某一列字段的值
					map.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 初始化
	public Boolean init(String driverName, String url, String ip, String databaseName, String port, String username,
			String password) {
		Boolean state = false;
		// 替换JDBC连接url属性
		url = url.replaceAll("host", ip).replaceAll("port", port).replaceAll("databaseName", databaseName);

		try {
			// 不同的数据库有不同的驱动
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, username, password);
			// 开始连接数据库
			state = true;
		} catch (Exception e) {
			state = false;
			e.printStackTrace();
		}
		return state;
	}

	// 关闭连接
	public void closeJDBC() throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (st != null) {
			st.close();
		}
		if (pstmt != null) {
			pstmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	// 手动提交
	public void commit() throws SQLException {
		if (conn != null) {
			conn.commit();
		}
	}

	// 回滚
	public void rollback() throws SQLException {
		if (conn != null) {
			conn.rollback();
		}
	}

	// public static void main(String[] args) throws SQLException {
	// String dirver = "com.mysql.jdbc.Driver";
	// String url = "jdbc:mysql://host:port/databaseName";
	// String sql = "SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment,
	// NUMERIC_SCALE, COLUMN_TYPE, IS_NULLABLE FROM information_schema. COLUMNS
	// WHERE TABLE_SCHEMA = 'dc_mdm_data' ";
	// JDBCUtil jdbcUtil = new JDBCUtil();
	// jdbcUtil.init(dirver, url, "10.199.10.9", "dc", "3306", "dyqd_dc",
	// "Datau$dc19");
	// List<Map<String, Object>> list = jdbcUtil.getData(sql);
	//
	// jdbcUtil.closeJDBC();
	// }

}
