数据库 : [dbcp](3) : dpcp连接池springboot

参考

    https://blog.csdn.net/god_v/article/details/80656827

    spring boot项目打成的 jar包无法获取src/main/resources下文件 - OSCHINA - 中文开源技术交流社区 

注 : 连接需要调用关闭方法才会归还连接池里面

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;

@Service
@Slf4j
public class DbcpService {

    private DataSource dataSource;

    /**
     * 连接地址
     */
    @Value("${mysql8.ip:127.0.0.1}")
    private String ip;

    /**
     * mysql端口
     */
    @Value("${mysql8.port:3306}")
    private Integer port;

    /**
     * 默认数据库
     */
    @Value("${mysql8.database:test}")
    private String database;

    /**
     * mysql账号名
     */
    @Value("${mysql8.username:root}")
    private String username;

    /**
     * mysql密码
     */
    @Value("${mysql8.password:123456}")
    private String password;

    /**
     * 是否打印日志, 默认否
     */
    private Boolean printLog = Boolean.FALSE;

    public void setPrintLog(Boolean boo) {
        printLog = boo;
    }

    @PostConstruct
    private void init() {
        try {
            Properties properties = new Properties();
            properties.put("driverClassName", "com.mysql.jdbc.Driver");
            properties.put("url", "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=GMT%2b8&allowMultiQueries=true&allowPublicKeyRetrieval=true");
            properties.put("username", username);
            properties.put("password", password);
            properties.put("initialSize", "1");
            properties.put("maxTotal", "4");
            properties.put("maxIdle", "2");
            properties.put("minIdle", "1");
            properties.put("maxWaitMillis", "1000");
            properties.put("testOnBorrow", "true");
            properties.put("validationQuery", "select 1");
            //创建dataSource对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);
            log.info("数据库连接池初始化完成");
        } catch (Exception e) {
            log.error("初始化数据连接池失败", e);
        }
    }

    public List<List<String>> query(String sql) {
        return query(sql, Boolean.FALSE);
    }

    public void execute(String sql) {
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();) {
            long startTime = System.currentTimeMillis();
            statement.execute(sql);
            if (printLog) {
                log.info("执行sql成功,sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
            }
        } catch (Exception e) {
            System.err.println("sql执行失败:[" + sql + "]");
            throw new RuntimeException(e);
        }
    }

    public List<List<String>> query(String sql, Boolean returnColumnName) {
        long startTime = System.currentTimeMillis();
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int column = resultSetMetaData.getColumnCount();
            List<List<String>> result = new LinkedList<>();
            if (returnColumnName) {
                List<String> title = new ArrayList<>();
                for (int i = 1; i <= column; i++) {
                    title.add(resultSetMetaData.getColumnName(i));
                }
                result.add(title);
            }
            while (rs.next()) {
                List<String> list = new LinkedList<>();
                for (int i = 1; i <= column; i++) {
                    list.add(rs.getString(i));
                }
                result.add(list);
            }
            if (printLog) {
                log.info("查询sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
            }
            return result;
        } catch (Exception e) {
            log.warn("sql执行失败,sql:[" + sql + "]");
            throw new RuntimeException(e);
        }
    }

    public Integer count(String sql) {
        List<List<String>> rs = query(sql);
        return Integer.parseInt(rs.get(0).get(0));
    }

    public void print(List<List<String>> res) {
        Map<Integer, Integer> columnMaxLength = new HashMap<>();
        for (int i = 0; i < res.size(); i++) {
            List<String> strings = res.get(i);
            for (int k = 0; k < strings.size(); k++) {
                if (!columnMaxLength.containsKey(k)) {
                    columnMaxLength.put(k, 0);
                }
                if (strings.get(k) == null) {
                    continue;
                }
                int length = strings.get(k).getBytes().length;

                if (length > columnMaxLength.get(k)) {
                    columnMaxLength.put(k, length);
                }
            }
        }
        for (int i = 0; i < res.size(); i++) {
            List<String> strings = res.get(i);
            for (int k = 0; k < strings.size(); k++) {
                System.out.format("%-" + (columnMaxLength.get(k) + 5) + "s", strings.get(k));
            }
            System.out.println();
        }
    }
}

maven

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.14</version>
        </dependency>