{"id":3980,"date":"2018-06-11T22:27:55","date_gmt":"2018-06-11T14:27:55","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=3980"},"modified":"2018-06-11T22:27:55","modified_gmt":"2018-06-11T14:27:55","slug":"java%e4%b8%ad%e4%bd%bf%e7%94%a8c3p0%e5%bb%ba%e7%ab%8bmysql%e6%95%b0%e6%8d%ae%e5%ba%93%e8%bf%9e%e6%8e%a5%e6%b1%a0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/3980.html","title":{"rendered":"Java\u4e2d\u4f7f\u7528c3p0\u5efa\u7acbMySQL\u6570\u636e\u5e93\u8fde\u63a5\u6c60"},"content":{"rendered":"

=Start=<\/p>\n

\u7f18\u7531\uff1a<\/h4>\n

\u5728\u5b66\u4e60Java\u7684\u8fc7\u7a0b\u4e2d\u4e0d\u65ad\u7528\u6587\u7ae0\u8fdb\u884c\u6574\u7406\u603b\u7ed3\uff08\u5e38\u7528\u529f\u80fd\u7684Java\u5b9e\u73b0\uff09\uff0c\u4e89\u53d6\u65e9\u65e5\u80fd\u8f83\u4e3a\u719f\u7ec3\u7684\u4f7f\u7528Java\u8fdb\u884c\u5f00\u53d1\u3002<\/p>\n

\u672c\u6765\u7528c3p0\u5efa\u7acb\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u8fd9\u90e8\u5206\u5185\u5bb9\u53ea\u60f3\u7528\u4e00\u7bc7\u6587\u7ae0\u7684\u4e00\u90e8\u5206\u6765\u8fdb\u884c\u4ee3\u7801\u6837\u4f8b\u8bf4\u660e\u7684\uff0c\u4f46\u662f\u6ca1\u60f3\u5230\u5728\u7f51\u4e0a\u641c\u5230\u7684\u4e00\u4e9b\u4ecb\u7ecd\u6027\u6587\u7ae0\u6216\u591a\u6216\u5c11\u90fd\u6709\u4e9b\u95ee\u9898\uff0c\u5bf9\u4e8e\u6211\u8fd9\u6837\u7684\u521d\u5b66\u8005\u592a\u4e0d\u53cb\u597d\uff0c\u4e00\u4e9b\u672c\u6765\u5f88\u7b80\u5355\u7684\u95ee\u9898\u5c31\u56e0\u4e3a\u4ed6\u4eec\u6587\u7ae0\u4e2d\u7684\u4e00\u53e5\u8bdd\u8ba9\u6211\u8d70\u4e86\u4e0d\u5c11\u5f2f\u8def\uff0c\u6240\u4ee5\u611f\u89c9\u6709\u5fc5\u8981\u5355\u72ec\u8bb0\u5f55\u4e00\u4e0b\u8fd9\u4e2a\u8fc7\u7a0b\uff0c\u65b9\u4fbf\u81ea\u5df1\u53c2\u8003\u3001\u67e5\u9605\u3002<\/p>\n

\u6b63\u6587\uff1a<\/h4>\n

\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n
1\u3001c3p0\u7684\u57fa\u672c\u6982\u5ff5<\/strong><\/h6>\n

c3p0\u662f\u4e00\u4e2a\u5f00\u6e90\u7684JDBC\u8fde\u63a5\u6c60\uff0c\u5b83\u5b9e\u73b0\u4e86\u6570\u636e\u6e90\u548cJNDI\u7ed1\u5b9a\uff0c\u652f\u6301JDBC3\u89c4\u8303\u548cJDBC2\u7684\u6807\u51c6\u6269\u5c55\u3002<\/p>\n

2\u3001c3p0\u7684\u51e0\u79cd\u914d\u7f6e\u65b9\u5f0f<\/strong><\/h6>\n

c3p0\u7684\u914d\u7f6e\u65b9\u5f0f\u5206\u4e3a\u4e09\u79cd\uff0c\u5206\u522b\u662f\uff1a<\/p>\n

    \n
  1. \u4f7f\u7528c3p0-config.xml\u6587\u4ef6\uff08\u6587\u4ef6\u540d\u79f0\u56fa\u5b9a<\/strong>\uff09\uff1b<\/li>\n
  2. \u4f7f\u7528c3p0.properties\u6587\u4ef6\uff08\u6587\u4ef6\u540d\u79f0\u56fa\u5b9a<\/strong>\uff09\uff1b<\/li>\n
  3. \u7528setters\u4e00\u4e2a\u4e2a\u5730\u8bbe\u7f6e\u5404\u4e2a\u914d\u7f6e\u9879\u3002<\/li>\n<\/ol>\n

    \u7279\u522b\u8bf4\u660e\uff1a\u5bf9\u4e8e\u7528IDEA\u5efa\u7acb\u7684Maven\u9879\u76ee\u6765\u8bf4\uff0c\u4e0a\u9762\u7684c3p0-config.xml\u548cc3p0.properties\u6587\u4ef6\uff0c\u8981\u653e\u5728\u9879\u76ee\u7684\u300cresources\u300d\u76ee\u5f55\u4e0b<\/strong><\/span>\uff0c\u800c\u4e0d\u662f\u4e00\u4e9b\u6587\u7ae0\u91cc\u8bf4\u7684\u300csrc\u300d\u76ee\u5f55\u4e0b\uff0c\u5426\u5219\u4f1a\u51fa\u73b0\uff1a<\/p>\n

    java.sql.SQLException: Connections could not be acquired from the underlying database!<\/p>\n

    Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.<\/p><\/blockquote>\n

    \u7684\u9519\u8bef\u3002<\/p>\n

    3\u3001\u4e0d\u540c\u65b9\u5f0f\u7684\u914d\u7f6e\u6837\u4f8b<\/strong><\/h6>\n

    \u2460c3p0-config.xml\u6587\u4ef6\u7684\u914d\u7f6e\u65b9\u5f0f<\/strong><\/span><\/p>\n

    <?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<c3p0-config>\r\n    <!-- \u9ed8\u8ba4\u914d\u7f6e\uff0c\u5982\u679c\u6ca1\u6709\u6307\u5b9a\u5219\u4f7f\u7528\u8fd9\u4e2a\u914d\u7f6e -->\r\n    <default-config>\r\n        <property name=\"jdbcUrl\">jdbc:mysql:\/\/localhost:3306\/test<\/property>\r\n        <property name=\"driverClass\">com.mysql.jdbc.Driver<\/property>\r\n        <property name=\"user\">root<\/property>\r\n        <property name=\"password\">123456<\/property>\r\n\r\n        <property name=\"checkoutTimeout\">3000<\/property>\r\n        <property name=\"idleConnectionTestPeriod\">30<\/property>\r\n        <property name=\"initialPoolSize\">10<\/property>\r\n        <property name=\"maxIdleTime\">30<\/property>\r\n        <property name=\"maxPoolSize\">100<\/property>\r\n        <property name=\"minPoolSize\">10<\/property>\r\n        <property name=\"maxStatements\">200<\/property>\r\n    <\/default-config>\r\n\r\n    <!-- \u547d\u540d\u7684\u914d\u7f6e -->\r\n    <named-config name=\"mysqlConnection\">\r\n        <property name=\"jdbcUrl\">jdbc:mysql:\/\/localhost:3306\/test<\/property>\r\n        <property name=\"driverClass\">com.mysql.jdbc.Driver<\/property>\r\n        <property name=\"user\">root<\/property>\r\n        <property name=\"password\">123456<\/property>\r\n\r\n        <property name=\"initialPoolSize\">25<\/property>\r\n        <property name=\"maxPoolSize\">50<\/property>\r\n        <property name=\"minPoolSize\">5<\/property>\r\n        <property name=\"acquireIncrement\">5<\/property>\r\n    <\/named-config>\r\n<\/c3p0-config><\/pre>\n
    \u5bf9\u5e94\u7684Java\u6d4b\u8bd5\u4ee3\u7801\u4e3a\uff1a<\/p>\n
    package com.ixyzero.learn.db;\r\n\r\nimport com.mchange.v2.c3p0.ComboPooledDataSource;\r\n\r\nimport java.sql.Connection;\r\nimport java.sql.PreparedStatement;\r\nimport java.sql.ResultSet;\r\n\r\n\/**\r\n * Created by ixyzero.com on 2018\/6\/11.\r\n *\/\r\npublic class TestConnXml {\r\n    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();\r\n\r\n    public static void main(String[] args) {\r\n        try{\r\n            Connection connection = dataSource.getConnection();\r\n            System.out.println(\"\u8fde\u63a5\u6570\u636e\u5e93\u6210\u529f\uff01\");\r\n\r\n            \/\/ \u6267\u884c\u67e5\u8be2\u8bed\u53e5\r\n            String sql = \"select * from websites limit 3\";\r\n            PreparedStatement ps = connection.prepareStatement(sql);\r\n            ResultSet rs = ps.executeQuery();\r\n            while (rs.next()) {\r\n                System.out.println(rs.getString(1));\r\n            }\r\n\r\n            connection.close();\r\n            System.out.println(\"\u6570\u636e\u5e93\u8fde\u63a5\u5df2\u5173\u95ed\uff01\");\r\n        }catch(Exception e) {\r\n            e.printStackTrace();\r\n            System.out.println(\"\u8fde\u63a5\u6570\u636e\u5e93\u5931\u8d25\uff01\");\r\n        }\r\n\r\n    }\r\n}<\/pre>\n
    \u2461c3p0.properties\u6587\u4ef6\u7684\u914d\u7f6e\u65b9\u5f0f<\/strong><\/span><\/p>\n
    c3p0.driverClass=com.mysql.jdbc.Driver\r\nc3p0.jdbcUrl=jdbc:mysql:\/\/localhost:3306\/test\r\nc3p0.user=root\r\nc3p0.password=123456<\/pre>\n
    \u6ce8\u610f\uff1a\u91c7\u7528c3p0.properties\u6587\u4ef6\u8fdb\u884c\u914d\u7f6e\u7684\uff0c\u90fd\u8981\u52a0\u4e0a\u524d\u7f00\u300cc3p0.\u300d\uff0c\u5426\u5219\u4e5f\u4f1a\u51fa\u73b0\u8fde\u4e0d\u4e0a\u7684\u9519\u8bef\uff01<\/strong><\/span><\/p>\n
    \u6d4b\u8bd5\u4ee3\u7801\u548c\u4e0a\u9762\u7684\u76f8\u540c\u3002<\/p>\n
    \u2462\u7528setter\u4e00\u4e2a\u4e2a\u7684\u8bbe\u7f6e\u76f8\u5173\u914d\u7f6e\u9879<\/strong><\/p>\n
    \u8fd9\u4e2a\u53ef\u4ee5\u901a\u8fc7\u914d\u7f6e\u6587\u4ef6\u5b58\u653e\u6570\u636e\u5e93\u7528\u6237\u540d\u3001\u5bc6\u7801\u3001\u8fde\u63a5\u7b49\u4fe1\u606f\uff0c\u4e5f\u53ef\u4ee5\u76f4\u63a5\u5199\u5728\u4ee3\u7801\u4e2d\uff1a<\/p>\n
    package com.ixyzero.learn.db;\r\n\r\nimport com.mchange.v2.c3p0.ComboPooledDataSource;\r\n\r\nimport java.beans.PropertyVetoException;\r\nimport java.sql.Connection;\r\nimport java.sql.PreparedStatement;\r\nimport java.sql.ResultSet;\r\n\r\n\/**\r\n * Created by ixyzero.com on 2018\/6\/11.\r\n *\/\r\npublic class TestConn {\r\n    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();\r\n\r\n    public static void main(String[] args) {\r\n        dataSource.setUser(\"root\");\r\n        dataSource.setPassword(\"123456\");\r\n        dataSource.setJdbcUrl(\"jdbc:mysql:\/\/localhost:3306\/test\");\r\n        try {\r\n            dataSource.setDriverClass(\"com.mysql.jdbc.Driver\");\r\n        } catch (PropertyVetoException e) {\r\n            e.printStackTrace();\r\n            System.out.println(\"setDriverClass \u5931\u8d25\uff01\");\r\n            return;\r\n        }\r\n        dataSource.setInitialPoolSize(5);\r\n        dataSource.setMinPoolSize(1);\r\n        dataSource.setMaxPoolSize(10);\r\n        dataSource.setMaxStatements(50);\r\n        dataSource.setMaxIdleTime(60);\r\n        System.out.println(dataSource);\r\n\r\n        try{\r\n            Connection connection = dataSource.getConnection();\r\n            System.out.println(\"\u8fde\u63a5\u6570\u636e\u5e93\u6210\u529f\uff01\");\r\n\r\n            \/\/ \u6267\u884c\u67e5\u8be2\u8bed\u53e5\r\n            String sql = \"select * from websites\";\r\n            PreparedStatement ps = connection.prepareStatement(sql);\r\n            ResultSet rs = ps.executeQuery();\r\n            while (rs.next()) {\r\n                System.out.println(rs.getString(1));\r\n            }\r\n\r\n            connection.close();\r\n            System.out.println(\"\u6570\u636e\u5e93\u8fde\u63a5\u5df2\u5173\u95ed\uff01\");\r\n        }catch(Exception e){\r\n            e.printStackTrace();\r\n            System.out.println(\"\u8fde\u63a5\u6570\u636e\u5e93\u5931\u8d25\uff01\");\r\n        }\r\n    }\r\n\r\n}<\/pre>\n
    &<\/p>\n
    \u4e0b\u9762\u7528\u5230\u7684\u914d\u7f6e\u6587\u4ef6\u4e5f\u8981\u653e\u5728\u9879\u76ee\u7684\u300cresources\u300d\u76ee\u5f55\u4e0b\uff01<\/strong><\/span><\/p>\n
    public class TestConnProperties {\r\n    private static Properties prop = new Properties();\r\n    private static InputStream in = TestConnProperties.class.getClassLoader().getResourceAsStream(\"db.properties\");\r\n    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();\r\n\r\n    public static void main(String[] args) {\r\n        try {\r\n            prop.load(in);\r\n        } catch (IOException e) {\r\n            e.printStackTrace();\r\n        }\r\n\r\n        dataSource.setUser(prop.getProperty(\"username\"));\r\n        dataSource.setPassword(prop.getProperty(\"password\"));\r\n        dataSource.setJdbcUrl(prop.getProperty(\"url\"));\r\n        try {\r\n            dataSource.setDriverClass(prop.getProperty(\"jdbcdriver\"));\r\n        } catch (PropertyVetoException e) {\r\n            e.printStackTrace();\r\n        }\r\n\r\n        \/\/...\r\n    }\r\n}<\/pre>\n
    4\u3001\u548c\u4e0d\u4f7f\u7528\u8fde\u63a5\u6c60\u7684\u60c5\u51b5\u505a\u5bf9\u6bd4<\/strong><\/span><\/h6>\n
    package com.ixyzero.learn.db;\r\n\r\nimport com.mchange.v2.c3p0.ComboPooledDataSource;\r\n\r\nimport java.beans.PropertyVetoException;\r\nimport java.sql.Connection;\r\nimport java.sql.DriverManager;\r\nimport java.sql.PreparedStatement;\r\nimport java.sql.ResultSet;\r\n\r\n\/**\r\n * Created by ixyzero.com on 2018\/6\/11.\r\n *\/\r\npublic class TestProperties {\r\n    \/\/ JDBC \u9a71\u52a8\u540d\u53ca\u6570\u636e\u5e93 URL\r\n    static final String JDBC_DRIVER = \"com.mysql.jdbc.Driver\";\r\n    static final String DB_URL = \"jdbc:mysql:\/\/localhost:3306\/test\";\r\n    \/\/ \u6570\u636e\u5e93\u7684\u7528\u6237\u540d\u4e0e\u5bc6\u7801\uff0c\u9700\u8981\u6839\u636e\u81ea\u5df1\u7684\u8bbe\u7f6e\r\n    static final String USER = \"root\";\r\n    static final String PASS = \"123456\";\r\n\r\n    private static ComboPooledDataSource datasource = new ComboPooledDataSource();\r\n\r\n    public static void main(String[] args) {\r\n        \/\/ jdbc without using connection pool\r\n        try {\r\n            for (int i = 0; i < 100; i++) {\r\n                long beginTime = System.currentTimeMillis();\r\n\r\n                Class.forName(JDBC_DRIVER);\r\n\r\n                Connection con = DriverManager.getConnection(DB_URL, USER, PASS);\r\n                String sql = \"select * from websites\";\r\n                PreparedStatement ps = con.prepareStatement(sql);\r\n                ResultSet rs = ps.executeQuery();\r\n                while (rs.next()) {\r\n                    \/\/ System.out.println(rs.getString(1));\r\n                }\r\n                con.close();\r\n                long endTime = System.currentTimeMillis();\r\n                System.out.println(String.format(\"\u7b2c%s\u6b21, %s (ms)\", i, (endTime - beginTime)));\r\n            }\r\n        } catch (Exception e) {\r\n            e.printStackTrace();\r\n        }\r\n        System.out.println(\"\\n\");\r\n\r\n\r\n        datasource.setUser(USER);\r\n        datasource.setPassword(PASS);\r\n        datasource.setJdbcUrl(DB_URL);\r\n        try {\r\n            datasource.setDriverClass(JDBC_DRIVER);\r\n        } catch (PropertyVetoException e) {\r\n            e.printStackTrace();\r\n        }\r\n        datasource.setInitialPoolSize(20);\r\n        datasource.setMinPoolSize(1);\r\n        datasource.setAcquireIncrement(5);\r\n        datasource.setMaxPoolSize(100);\r\n        datasource.setMaxStatements(50);\r\n        datasource.setMaxIdleTime(60);\r\n        \/\/ use c3p0 connection pool\r\n        try {\r\n            for (int i = 0; i < 100; i++) {\r\n                long beginTime = System.currentTimeMillis();\r\n                Connection con = datasource.getConnection();\r\n                \/\/ \u6267\u884c\u67e5\u8be2\u8bed\u53e5\r\n                String sql = \"select * from websites\";\r\n                PreparedStatement ps = con.prepareStatement(sql);\r\n                ResultSet rs = ps.executeQuery();\r\n                while (rs.next()) {\r\n                    \/\/ System.out.println(rs.getString(1));\r\n                }\r\n                con.close();\r\n                long endTime = System.currentTimeMillis();\r\n                System.out.println(String.format(\"[c3p0]\u7b2c%s\u6b21, %s (ms)\", i, (endTime - beginTime)));\r\n            }\r\n        } catch (Exception e) {\r\n            e.printStackTrace();\r\n        }\r\n\r\n    }\r\n}<\/pre>\n
    MySQL\u5728\u9ad8\u7248\u672c\u9700\u8981\u6307\u660e\u662f\u5426\u8fdb\u884cSSL\u8fde\u63a5\uff0c\u672a\u914d\u7f6euseSSL\u65f6\u8b66\u544a\u4fe1\u606f\u6b63\u597d\u53ef\u4ee5\u7528\u6765\u663e\u793a\u4e00\u6b21\u8fde\u63a5\u3002\u4ece\u5b9e\u9645\u8fd0\u884c\u65e5\u5fd7\u4e2d\u53ef\u4ee5\u770b\u51fa\uff0c\u5f53\u4f7f\u7528\u8fde\u63a5\u65f6\uff0c\u7a0b\u5e8f\u5728\u542f\u52a8\u65f6\u76f4\u63a5\u5efa\u7acb\u4e8620\u4e2a\u6570\u636e\u5e93\u8fde\u63a5\uff08\u5982\u679c\u4e0d\u663e\u5f0f\u6307\u5b9a\u7684\u8bdd\uff0c\u9ed8\u8ba4\u662f3\u4e2a\uff09\uff0c\u63a5\u4e0b\u6765\u7684\u6570\u636e\u5e93\u64cd\u4f5c\u901a\u8fc7\u8fde\u63a5\u6c60\u4e2d\u7684\u8fde\u63a5\u64cd\u4f5c\u6570\u636e\u5e93\uff0c\u4e0d\u518d\u65b0\u5efa\u8fde\u63a5\u3002\u800c\u4e0d\u4f7f\u7528\u8fde\u63a5\u6c60\u65f6\uff0c\u6bcf\u4e00\u6b21\u6570\u636e\u5e93\u64cd\u4f5c\u90fd\u5efa\u7acb\u4e86\u4e00\u6b21\u6570\u636e\u5e93\u8fde\u63a5\u3002<\/p>\n
    \u4ece\u4e0a\u9762\u7684\u6d4b\u8bd5\u4e5f\u53ef\u4ee5\u770b\u51fa\u2014\u2014\u4f7f\u7528\u8fde\u63a5\u6c60\u7684\u64cd\u4f5c\u8017\u65f6\u660e\u663e\u77ed\u4e8e\u6bcf\u6b21\u65b0\u5efa\u8fde\u63a5\u7684\u64cd\u4f5c\uff0c\u6240\u4ee5\uff0c\u5bf9\u4e8e\u5b58\u5728\u591a\u4e2a\u8fde\u63a5\u7684\u60c5\u51b5\u4e0b\uff0c\u4f7f\u7528\u8fde\u63a5\u6c60\u53ef\u4ee5\u5e26\u6765\u6781\u5927\u7684\u6548\u7387\u63d0\u5347\u3002<\/p>\n
    \u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n