JDBC封装工具类

1、接口

package com.database.db;

import java.sql.Connection;
import java.sql.SQLException;

public interface IBONDataSource {
    public Connection getConnection() throws SQLException;
}

2、实现类

package com.database.db;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;


@Repository("bONDataSource")
public class BONDataSource implements IBONDataSource {

    private static Logger STD_LOG = Logger.getLogger(BONDataSource.class);

    private static BONDataSource instance;

    private ComboPooledDataSource cpds = null;

    private BONDataSource() {
        cpds = new ComboPooledDataSource();
    }

    public static IBONDataSource getInstance() {
        if (instance == null) {
            synchronized (BONDataSource.class) {
                if (instance == null) {
                    instance = new BONDataSource();
                    STD_LOG.info("BONDataSource been allocated");
                }
            }
        }
        return instance;
    }

    public static void destroy() {
        if (instance != null) {
            synchronized (BONDataSource.class) {
                if (instance != null) {
                    instance.cpds.close();
                    instance.cpds = null;
                    instance = null;
                    STD_LOG.info("BONDataSource been destoryed");
                }
            }
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        STD_LOG.info("Get Connection from Connection Pool");
        return this.cpds.getConnection();
    }
}

3、调用

@Autowired
    IBONDataSource bONDataSource;

    public int insert(String vin,String appName,String pu,String system,String page) throws SQLException {
        String preparedSQLString = "INSERT INTO car_request (vin, app_name, pu, system , page) VALUES(?, ?, ?, ?,?)";

        Connection connection = bONDataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(preparedSQLString);
        statement.setString(1, vin);
        statement.setString(2, appName);
        statement.setString(3, pu);
        statement.setString(4, system);
        statement.setString(4, page);

        STD_LOG.info("I'm going to execute [" + statement.toString() + "]");
        int result = statement.executeUpdate();

        if (statement != null) {
            statement.close();
        }

        if (connection != null) {
            connection.close();
        }

        return result;
    }

4、依赖包

<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

5、配置文件
c3p0.properties

c3p0.jdbcUrl = jdbc:mysql://localhost:3306/boss?useUnicode=true&characterEncoding=utf-8
c3p0.driverClass = com.mysql.jdbc.Driver
c3p0.user = root
c3p0.password = *****
c3p0.maxPoolSize = 50
c3p0.minPoolSize = 5
c3p0.maxIdleTime = 0
c3p0.initialPoolSize = 5
c3p0.acquireIncrement = 5