JDBCUtils封装(包括c3p0以及druid)

1.普通方法静态代码块实现连接

//db.properties 文件夹 放在src路径下
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/javassm?useSSL=true&characterEncoding=utf8
jdbcuser = root
password = 12345

public class JdbcUtils {
    private static String url;
    private static String jdbcuser;
    private static String password;
    private static String driver;

    private static Connection conn = null;
    private static PreparedStatement psmt = null;
    private static ResultSet rs = null;


    //静态代码块 关联db.properties 在连接前传参
    static {
        try {
            Properties properties = new Properties();      
            //利用类加载器加载src路径读取db.properties文件     properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            jdbcuser = properties.getProperty("jdbcuser");
            password = properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //建立连接
    public static Connection getConnection() throws SQLException {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return DriverManager.getConnection(url, jdbcuser, password);
    }

 //查询方法
    public static ResultSet executeQuery(String sql, Object[] parmas) {
        try {
            conn = JdbcUtils.getConnection();
            psmt = conn.prepareStatement(sql);
            if (parmas != null) {
                for (int i = 0; i < parmas.length; i++) {
                    psmt.setObject((i + 1), parmas[i]);
                }
                rs = psmt.executeQuery();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return rs;
    }

    //增删改方法
    public static int executeUpdate(String sql, Object[] parmas) {
        int num = 0;
        try {
            conn = JdbcUtils.getConnection();
            psmt = conn.prepareStatement(sql);
            if (parmas != null) {
                for (int i = 0; i < parmas.length; i++) {
                    psmt.setObject((i + 1), parmas[i]);
                }
            }
            num = psmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }


    //释放资源的方法
    public static void close(Connection conn, PreparedStatement psmt, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (psmt != null) {
            try {
                psmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

2.c3p0封装

jar包导入：c3p0-0.9.5.2.jar mohange-commons-java-0.2.12.jar(依赖包)
不要忘了mysql的驱动包

//c3p0-config.xml文件 注：c3p0的配置文件只能有两个名字 c3p0.properties 或者c3p0-config.xml 不然无法读取
<c3p0-config>
    <!-- 命名的配置 命名为test-->
    <named-config name="test">
        <!-- 连接数据库的4项基本参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/javassm?useSSL=true</property>
        <property name="user">root</property>
        <property name="password">12345</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化连接数 -->
        <property name="initialPoolSize">5</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">10</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">40</property>
        <!-- JDBC的标准参数，用以控制数据源内加载的PrepareStatements数量 -->
        <property name="maxStatements">200</property>
        <!-- 连接池内单个连接所拥有的最大缓存statements数 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>

<!-- 默认配置，如果没有指定使用则使用这个配置 -->
<default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/javassm?useSSL=true</property>
    <property name="user">root</property>
    <property name="password">12345</property>
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>
    <!-- intergalactoApp adopts a different approach to configuring statement caching -->
    <property name="maxStatements">0</property>
    <property name="maxStatementsPerConnection">5</property>
    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe">
        <property name="acquireIncrement">1</property>
        <property name="initialPoolSize">1</property>
        <property name="minPoolSize">1</property>
        <property name="maxPoolSize">5</property>
        <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
</default-config>
</c3p0-config>

    //数据库连接池建立连接 c3p0
    public Connection c3p0() {
        DataSource ds = new ComboPooledDataSource();//新建一个连接池对象,括号里可以填信息 跟配置文件相关 当括号不填name时，使用默认配置 填时使用name对应的配置
        try {
            conn = ds.getConnection();//获取连接池对象
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

3.druid连接池

jar包：druid-1.0.9.jar + mysql的驱动包
配置文件: 注：以.properties的形式 名字可以任意 可以放在任意目录下

//druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/javassm?useSSL=true&characterEncoding=utf8
username=root
password=12345
initialSize=5
maxActive=10
maxWait=3000
maxIdle=8
minIdle=3

public class DruidUtils {
    private static Properties pro = null;
    private static Connection conn = null;
    private static DataSource ds = null;

    static {
        pro = new Properties();
        InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            pro.load(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //数据池 建立连接 返回conn
    public static Connection getConnection() {
        try {
        //利用DruidDataSourceFactory工厂建立连接
            DataSource ds = DruidDataSourceFactory.createDataSource(pro);
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

//返回datasource 供jdbctenmplate用
    public static DataSource getDataSource() {
        try {
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ds;
    }

    //释放资源
    public static void close(PreparedStatement pstmt, ResultSet rs, Connection conn) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();//归还连接
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }