{"id":1873,"date":"2022-11-28T21:38:31","date_gmt":"2022-11-28T13:38:31","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1873"},"modified":"2022-11-28T21:38:31","modified_gmt":"2022-11-28T13:38:31","slug":"jdbcutils%e4%b8%ad%e4%bd%bf%e7%94%a8c3p0%e6%95%b0%e6%8d%ae%e5%ba%93%e8%bf%9e%e6%8e%a5%e6%b1%a0%e8%8e%b7%e5%8f%96%e8%bf%9e%e6%8e%a5%e3%80%81dbcp%e6%95%b0%e6%8d%ae%e5%ba%93%e8%bf%9e%e6%8e%a5%e6%b1%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1873","title":{"rendered":"JDBCUtils\u4e2d\u4f7f\u7528C3P0\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u83b7\u53d6\u8fde\u63a5\u3001DBCP\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u7684\u4e24\u79cd\u5b9e\u73b0\u65b9\u5f0f\u3001Druid\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u6280\u672f\u7684\u5b9e\u73b0"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>package top.qaqaq.P48.util;\r\n\r\nimport java.io.File;\r\nimport java.io.FileInputStream;\r\nimport java.io.FileNotFoundException;\r\nimport java.io.IOException;\r\nimport java.io.InputStream;\r\nimport java.sql.Connection;\r\nimport java.sql.DriverManager;\r\nimport java.sql.ResultSet;\r\nimport java.sql.SQLException;\r\nimport java.sql.Statement;\r\nimport java.util.Properties;\r\n\r\nimport javax.sql.DataSource;\r\n\r\nimport org.apache.commons.dbcp2.BasicDataSourceFactory;\r\n\r\nimport com.alibaba.druid.pool.DruidDataSourceFactory;\r\nimport com.mchange.v2.c3p0.ComboPooledDataSource;\r\n\r\npublic class JDBCUtils {\r\n\r\n\t\/**\r\n\t * \r\n\t * @Description \u83b7\u53d6\u6570\u636e\u5e93\u7684\u8fde\u63a5\r\n\t * @author RichieZhang\r\n\t * @date 2022\u5e7411\u670826\u65e5\u4e0b\u53484:00:53\r\n\t * @return\r\n\t * @throws Exception\r\n\t *\/\r\n\tpublic static Connection getConnection() throws Exception {\r\n\t\t\/\/ 1.\u8bfb\u53d6\u914d\u7f6e\u6587\u4ef6\u4e2d\u76844\u4e2a\u57fa\u672c\u4fe1\u606f\r\n\t\tInputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream(\"jdbc.properties\");\r\n\r\n\t\tProperties pros = new Properties();\r\n\t\tpros.load(is);\r\n\r\n\t\tString user = pros.getProperty(\"user\");\r\n\t\tString password = pros.getProperty(\"password\");\r\n\t\tString url = pros.getProperty(\"url\");\r\n\t\tString driverClass = pros.getProperty(\"driverClass\");\r\n\r\n\t\t\/\/ 2.\u52a0\u8f7d\u9a71\u52a8\r\n\t\tClass.forName(driverClass);\r\n\r\n\t\t\/\/ 3.\u83b7\u53d6\u8fde\u63a5\r\n\t\tConnection conn = DriverManager.getConnection(url, user, password);\r\n\t\treturn conn;\r\n\r\n\t}\r\n\r\n\t\/**\r\n\t * \r\n\t * @Description \u4f7f\u7528C3P0\u7684\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u6280\u672f\r\n\t * @author RichieZhang\r\n\t * @date 2022\u5e7411\u670828\u65e5\u4e0b\u53487:01:06\r\n\t * @return\r\n\t * @throws SQLException\r\n\t *\/\r\n\t\/\/ \u6570\u636e\u5e93\u8fde\u63a5\u6c60\u53ea\u9700\u63d0\u4f9b\u4e00\u4e2a\u5373\u53ef\u3002\r\n\tprivate static ComboPooledDataSource cpds = new ComboPooledDataSource(\"helloc3p0\");\r\n\r\n\tpublic static Connection getConnection1() throws SQLException {\r\n\t\tConnection conn = cpds.getConnection();\r\n\r\n\t\treturn conn;\r\n\t}\r\n\r\n\t\/**\r\n\t * \r\n\t * @Description \u4f7f\u7528DBCP\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u6280\u672f\u83b7\u53d6\u6570\u636e\u5e93\u8fde\u63a5\r\n\t * @author RichieZhang\r\n\t * @date 2022\u5e7411\u670828\u65e5\u4e0b\u53488:16:11\r\n\t * @return\r\n\t * @throws Exception\r\n\t *\/\r\n\tprivate static DataSource source;\r\n\tstatic {\r\n\t\ttry {\r\n\t\t\tProperties pros = new Properties();\r\n\r\n\t\t\tFileInputStream is = new FileInputStream(new File(\"src\/dbcp.properties\"));\r\n\r\n\t\t\tpros.load(is);\r\n\t\t\t\/\/ \u521b\u5efa\u4e00\u4e2aDBCP\u6570\u636e\u5e93\u8fde\u63a5\u6c60\r\n\t\t\tsource = BasicDataSourceFactory.createDataSource(pros);\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t}\r\n\r\n\tpublic static Connection getConnection2() throws Exception {\r\n\r\n\t\tConnection conn = source.getConnection();\r\n\r\n\t\treturn conn;\r\n\t}\r\n\r\n\t\/**\r\n\t * \u4f7f\u7528Druid\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u6280\u672f\r\n\t *\/\r\n\tprivate static DataSource source1;\r\n\tstatic {\r\n\t\ttry {\r\n\t\t\tProperties pros = new Properties();\r\n\r\n\t\t\tInputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream(\"druid.properties\");\r\n\r\n\t\t\tpros.load(is);\r\n\r\n\t\t\tsource1 = DruidDataSourceFactory.createDataSource(pros);\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t}\r\n\r\n\tpublic static Connection getConnection3() throws SQLException {\r\n\r\n\t\tConnection conn = source1.getConnection();\r\n\r\n\t\treturn conn;\r\n\t}\r\n\r\n\t\/**\r\n\t * \r\n\t * @Description \u5173\u95ed\u8fde\u63a5\u548cStatement\u7684\u64cd\u4f5c\r\n\t * @author RichieZhang\r\n\t * @date 2022\u5e7411\u670826\u65e5\u4e0b\u53484:04:17\r\n\t * @param conn\r\n\t * @param ps\r\n\t *\/\r\n\tpublic static void closeResource(Connection conn, Statement ps) {\r\n\t\ttry {\r\n\t\t\tif (ps != null)\r\n\t\t\t\tps.close();\r\n\t\t} catch (SQLException e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t\ttry {\r\n\t\t\tif (conn != null)\r\n\t\t\t\tconn.close();\r\n\t\t} catch (SQLException e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\r\n\t}\r\n\r\n\t\/**\r\n\t * \r\n\t * @Description \u5173\u95ed\u8d44\u6e90\u64cd\u4f5c\r\n\t * @author RichieZhang\r\n\t * @date 2022\u5e7411\u670826\u65e5\u4e0b\u53485:29:47\r\n\t * @param conn\r\n\t * @param ps\r\n\t * @param rs\r\n\t *\/\r\n\tpublic static void closeResource(Connection conn, Statement ps, ResultSet rs) {\r\n\t\ttry {\r\n\t\t\tif (ps != null)\r\n\t\t\t\tps.close();\r\n\t\t} catch (SQLException e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t\ttry {\r\n\t\t\tif (conn != null)\r\n\t\t\t\tconn.close();\r\n\t\t} catch (SQLException e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t\ttry {\r\n\t\t\tif (rs != null)\r\n\t\t\t\trs.close();\r\n\t\t} catch (SQLException e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\r\n\t}\r\n\r\n}\r\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>package top.qaqaq.P48.juint;\r\n\r\nimport static org.junit.jupiter.api.Assertions.*;\r\n\r\nimport java.sql.Connection;\r\nimport java.sql.Date;\r\nimport java.text.SimpleDateFormat;\r\nimport java.util.List;\r\n\r\nimport org.junit.jupiter.api.Test;\r\n\r\nimport top.qaqaq.P48.util.JDBCUtils;\r\nimport top.qaqaq.P43.bean.Customer;\r\nimport top.qaqaq.P45.dao.CustomerDAOImpl;\r\n\r\nclass CustomerDAOImplTest {\r\n\t\r\n\tprivate CustomerDAOImpl dao = new CustomerDAOImpl();\r\n\r\n\t@Test\r\n\tvoid testInsert() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tCustomer cust = new Customer(1, \"\u5f20\u5c0f\u98de\", \"xiaofei@126.com\", new Date(42131114141214L));\r\n\t\t\tdao.insert(conn, cust);\r\n\t\t\t\r\n\t\t\tSystem.out.println(\"\u6dfb\u52a0\u6210\u529f\");\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testDeleteById() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tdao.deleteById(conn, 13);\r\n\t\t\t\r\n\t\t\tSystem.out.println(\"\u5220\u9664\u6210\u529f\");\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testUpdateConnectionCustomer() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tSimpleDateFormat sdf = new SimpleDateFormat(\"yyyy-MM-dd\");\r\n\t\t\tjava.util.Date birth = sdf.parse(\"2000-01-01\");\r\n\t\t\t\r\n\t\t\tCustomer cust = new Customer(18,\"\u8d1d\u591a\u82ac\",\"beiduofen@126.com\",new Date(birth.getTime()));\r\n\t\t\tdao.update(conn, cust);\r\n\t\t\t\r\n\t\t\tSystem.out.println(\"\u4fee\u6539\u6210\u529f\");\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testGetCustomerById() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\t\r\n\t\t\tCustomer cust = dao.getCustomerById(conn, 19);\r\n\t\t\t\r\n\t\t\tSystem.out.println(cust);\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testGetAll() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tList&lt;Customer> list = dao.getAll(conn);\r\n\t\t\t\r\n\t\t\tlist.forEach(System.out::println);\r\n\t\t\t\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testGetCount() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tLong count = dao.getCount(conn);\r\n\t\t\t\r\n\t\t\tSystem.out.println(\"\u8868\u4e2d\u7684\u8bb0\u5f55\u6570\u4e3a\uff1a\" + count);\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n\t@Test\r\n\tvoid testGetMaxBirth() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tconn = JDBCUtils.getConnection();\r\n\t\t\t\r\n\t\t\tDate maxBirth = dao.getMaxBirth(conn);\r\n\t\t\t\r\n\t\t\tSystem.out.println(\"\u6700\u5927\u7684\u751f\u65e5\u4e3a\uff1a\" + maxBirth);\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t} finally {\r\n\t\t\tJDBCUtils.closeResource(conn, null);\r\n\t\t}\r\n\t\t\r\n\t}\r\n\r\n}\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[48],"class_list":["post-1873","post","type-post","status-publish","format-standard","hentry","category-jdbc-code","tag-jdbc"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1873"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1873"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1873\/revisions"}],"predecessor-version":[{"id":1874,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1873\/revisions\/1874"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}