package com.emr.util;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Oracle链接数据库对象
 *
 * @author ZL
 *
 *         2017年7月27日
 */
public class OracleConnect {
    private static DruidDataSource dataSourceTest = null;
    private static PreparedStatement pst = null;
    public static ResultSet rs = null;

    private String url = PropertiesUtils.getProperty("oracleUrl");
    private String userName = PropertiesUtils.getProperty("oracleUserName");
    private String passWord = PropertiesUtils.getProperty("oraclePassWord");
    /**
     * 构造函数完成数据库的连接和连接对象的生成
     */
    private OracleConnect() {
        try {
            if (dataSourceTest == null) {
                dataSourceTest = new DruidDataSource();
                // 设置连接参数
                dataSourceTest.setUrl("jdbc:oracle:thin:@" + url);
                dataSourceTest.setDriverClassName("oracle.jdbc.driver.OracleDriver");
                dataSourceTest.setUsername(userName);
                dataSourceTest.setPassword(passWord);
                dataSourceTest.setInitialSize(3);
                dataSourceTest.setMaxActive(10);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 取得已经构造生成的数据库连接
     *
     * @return 返回数据库连接对象
     */
    public Connection getConnect() {
        try {
            return dataSourceTest.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 使用完一定要关闭（释放资源）
     * @param rs          ResultSet
     * @param stat        Statement
     * @param conn        Connection
     * @throws Exception
     */
    private static void CloseConn(ResultSet rs, Statement stat, Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询一条(多条)记录
     *
     * @param arg0  查询的sql语句
     * @return  list
     * @throws Exception
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static String select(String arg0) throws Exception {
        String str = "";
        Connection conn = new OracleConnect().getConnect();
        pst = conn.prepareStatement(arg0);
        rs = pst.executeQuery();
        try {
            if (rs != null) {
                while (rs.next()) {
                    str = rs.getString(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleConnect.CloseConn(rs, pst, conn);
        }
        return str;
    }
    public static List<String> selectList(String arg0) throws Exception {
        List<String> str = new ArrayList<>();
        Connection conn = new OracleConnect().getConnect();
        pst = conn.prepareStatement(arg0);
        rs = pst.executeQuery();
        try {
            if (rs != null) {
                while (rs.next()) {
                    str.add(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleConnect.CloseConn(rs, pst, conn);
        }
        return str;
    }
    public static List<Map> selectListConvertMap(String sql) throws Exception{
        List<Map> list = new ArrayList<>();
        Connection conn = new OracleConnect().getConnect();
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        try {
            if (rs != null) {
                ResultSetMetaData md = rs.getMetaData();//获取键名
                int columnCount = md.getColumnCount();//获取列的数量
                while (rs.next()) {
                    Map<String, Object> rowData = new HashMap<>();//声明Map
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
                    }
                    list.add(rowData);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            OracleConnect.CloseConn(rs, pst, conn);
        }
        return list;
    }

    public static int insert(String sql){
        Connection conn = new OracleConnect().getConnect();
        try {
            pst=conn.prepareStatement(sql);
            int i = pst.executeUpdate();
            conn.commit();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("");
        }finally {
            OracleConnect.CloseConn(null, pst, conn);
        }
    }

    public static int update(String sql) throws Exception {
        Connection conn = new OracleConnect().getConnect();
        try {
            Statement stmt=conn.createStatement();//创建一个Statement对象
            int i = stmt.executeUpdate(sql);//执行SQL语句
            conn.commit();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            OracleConnect.CloseConn(null, pst, conn);
        }
        return 1;
    }
}
