package com.skyversation.poiaddr.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class DbConnection {
    private static DbConnection dbConnection;
    public Connection connection;

    public static DbConnection getInstance() {
        if (dbConnection == null) {
            dbConnection = new DbConnection();
        }
        return dbConnection;
    }

    private Statement stmt;
    @Value("${app.db.username}")
    private String db_user_name;
    @Value("${app.db.password}")
    private String db_password;
    @Value("${app.db.driver}")
    private String jdbcDriver;
    @Value("${app.db.jdbc_url}")
    private String jdbcURL;

    @PostConstruct
    public void initDbConnection() throws SQLException {
//      加载驱动
        System.out.println("加载驱动！");
        dbConnection = this;
        try {
            Class.forName(jdbcDriver);
        } catch (Exception e) {
            System.err.println("加载驱动异常：" + e);
            System.exit(1);
        }
        System.out.println("配置文件中的数据库用户名：" + db_user_name + "；密码：" + db_password);
        connection = DriverManager.getConnection(jdbcURL, db_user_name, db_password);
        stmt = connection.createStatement();

    }

    //  插入、删除、更新
    public int updateSql(String sqlStr) throws SQLException {
        try {
            return stmt.executeUpdate(sqlStr);
        } catch (Exception e) {
            if (connection == null || connection.isClosed()) {
                System.out.println("数据库异常断开，尝试重新连接！");
                connection = DriverManager.getConnection(jdbcURL, db_user_name, db_password);
                stmt = connection.createStatement();
                return this.updateSql(sqlStr);
            } else {
                System.err.println(e.getMessage());
                return -1;
            }
        }
    }

    //  只查询
    public List<Map<String, Object>> runSqlStr(String sqlStr) throws SQLException {
        try {
            ResultSet rs = stmt.executeQuery(sqlStr);
            ResultSetMetaData rsmd = rs.getMetaData();
            int size = rsmd.getColumnCount();
            List<Map<String, Object>> requestData = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> dataItem = new HashMap<>();
                for (int i = 0; i < size; i++) {
                    dataItem.put(rsmd.getColumnName(i + 1), rs.getString(i + 1));
                }
                requestData.add(dataItem);
            }
            rs.close();
            return requestData;
        } catch (Exception e) {
            if (connection == null || connection.isClosed()) {
                System.out.println("数据库异常断开，尝试重新连接！");
                connection = DriverManager.getConnection(jdbcURL, db_user_name, db_password);
                stmt = connection.createStatement();
                return this.runSqlStr(sqlStr);
            } else {
                System.err.println(e.getMessage());
                return null;
            }
        }
    }
}