数据库Utils

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 数据库相关操作
 *
 * @author zhangkun
 * @version 1.0
 * @date 2021/9/14 下午4:38
 */
@Slf4j
public class DbDriver implements AutoCloseable {
    public static final String RETAILDATA = "retail_data";
    public static final String RETAILMANAGE = "retail_manage";
    public static final String SALESMAN = "salesman";

    // 关于AutoCloseable：http://blog.csdn.net/fireofjava/article/details/7220754
    private Connection conn;

    private static DbDriver retailData;
    private static DbDriver retailManage;
    private static DbDriver salesman;

    private void getCon(String dbHost, int dbPort, String dbName, String dbUser, String dbPassword) {
        try {
            String dbUrl = String.format("jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=false",
                    dbHost, dbPort, dbName);
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }

    public List<Map<String, Object>> multiSelect(String sql) {
        if (sql.contains("*")) {
            throw new RuntimeException("查询sql语句不能使用*，必须指定列名");
        }

        List<Map<String, Object>> results = new ArrayList<>();
        List<String> columns = parseColumnName(sql);

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Map<String, Object> record = new HashMap<>();

                for (String col : columns) {
                    record.put(col, rs.getString(col));
                }

                results.add(record);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

        return results;
    }

    public Map<String, Object> singleSelect(String sql) {
        List<Map<String, Object>> results = multiSelect(sql);

        if (results != null && results.size() > 0) {
            return results.get(0);
        }

        return null;
    }

    public int insert(String sql) {
        if (!sql.trim().toLowerCase().startsWith("insert")) {
            throw new RuntimeException("插入语句必须以insert开始");
        }

        return doUpdate(sql);
    }

    public int update(String sql) {
        if (!sql.trim().toLowerCase().startsWith("update")) {
            throw new RuntimeException("更新语句必须以update开始");
        }

        return doUpdate(sql);
    }

    public int delete(String sql) {
        if (!sql.trim().toLowerCase().startsWith("delete")) {
            throw new RuntimeException("删除语句必须以delete开始");
        }

        return doUpdate(sql);
    }

    @Override
    public void close() {
        // System.out.println("execute DbManager.close method!!");
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private int doUpdate(String sql) {
        try (Statement stmt = conn.createStatement()) {
            return stmt.executeUpdate(sql);
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }

    private List<String> parseColumnName(String sql) {
        String regex = "select\\s+([a-zA-Z\\d_,\\s]+)\\s+from";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
        Matcher matcher = pattern.matcher(sql);

        if (matcher.find()) {
            return Arrays.stream(matcher.group(1).split(",")).map(String::trim).collect(Collectors.toList());
        }

        throw new RuntimeException("sql语句格式错误，请不要使用表别名");
    }

    private DbDriver(String dbHost, int dbPort, String dbName, String dbUser, String dbPassword) {

        getCon(dbHost, dbPort, dbName, dbUser, dbPassword);
    }

    /**
     * 获取数据连接对象
     *
     * @param dbName
     * @return
     * @throws Exception
     */
    public static DbDriver getInstance(String dbName) {

        if (StringUtils.isBlank(dbName)) {
            throw new RuntimeException("数据源不存在");
        } else if (RETAILDATA.equals(dbName)) {
            return Optional.ofNullable(retailData).orElse(new DbDriver("x.x.x.x", 3306, "库名", "xxxx", "xxxx"));
        } else if (RETAILMANAGE.equals(dbName)) {
            return Optional.ofNullable(retailManage).orElse(new DbDriver("x.x.x.x", 3306, "库名", "xxxx", "xxxx"));
        } else if (SALESMAN.equals(dbName)) {
            return Optional.ofNullable(salesman).orElse(new DbDriver("x.x.x.x",3306, "库名", "xxx", "xxx"));
        } else {
            log.error("数据源不存在");
            throw new RuntimeException("数据源不存在");
        }

    }


    public static void main(String[] args) {
        try {
            DbDriver instance = DbDriver.getInstance(DbDriver.SALESMAN);

            int update = instance.update("update shopping_guide set `status` =1 where id =1 ");
            System.out.println(update);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}