{"id":1774,"date":"2022-11-26T22:26:47","date_gmt":"2022-11-26T14:26:47","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1774"},"modified":"2022-11-27T12:40:30","modified_gmt":"2022-11-27T04:40:30","slug":"preparedstatement%e5%ae%9e%e7%8e%b0%e8%a1%a8%e6%95%b0%e6%8d%ae%e7%9a%84%e6%b7%bb%e5%8a%a0%e6%93%8d%e4%bd%9c%e3%80%81%e5%b0%81%e8%a3%85%e6%8d%ae%e5%ba%93%e8%bf%9e%e6%8e%a5%e5%92%8c%e5%85%b3%e9%97%ad","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1774","title":{"rendered":"PreparedStatement\u5b9e\u73b0\u8868\u6570\u636e\u7684\u6dfb\u52a0\u64cd\u4f5c\u3001\u5c01\u88c5\u636e\u5e93\u8fde\u63a5\u548c\u5173\u95ed\u64cd\u4f5c\u3001PreparedStatement\u5b9e\u73b0\u8868\u6570\u636e\u7684\u4fee\u6539\u64cd\u4f5c\u3001PreparedStatement\u5b9e\u73b0\u901a\u7528\u7684\u589e\u5220\u6539\u64cd\u4f5c"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>package top.qaqaq.P13.preparedstatement.crud;\n\nimport java.io.IOException;\nimport java.io.InputStream;\nimport java.sql.Connection;\nimport java.sql.Date;\nimport java.sql.DriverManager;\nimport java.sql.PreparedStatement;\nimport java.sql.SQLException;\nimport java.sql.Statement;\nimport java.text.ParseException;\nimport java.text.SimpleDateFormat;\nimport java.util.Properties;\n\nimport org.junit.Test;\n\nimport top.qaqaq.P14.util.JDBCUtils;\n\n\/*\n * \u4f7f\u7528PreparedStatement\u6765\u66ff\u6362Statement\u3002\u5b9e\u73b0\u5bf9\u6570\u636e\u8868\u7684\u589e\u5220\u6539\u67e5\u64cd\u4f5c\n * \n * \u589e\u5220\u6539\uff1b\u67e5\n * \n *\/\npublic class PreparedStatementUpdateTest {\n\t\n\t\n\t\n\t@Test\n\tpublic void testCommonUpdate() {\n\/\/\t\tString sql = \"delete from customers where id = ?\";\n\/\/\t\tupdate(sql,3);\n\t\t\n\t\tString sql = \"update `order` set order_name = ? where order_id = ?\";\n\t\tupdate(sql,\"DD\",\"2\");\n\t\t\n\t}\n\t\n\t\/\/\u901a\u7528\u7684\u589e\u5220\u6539\u64cd\u4f5c\n\tpublic void update(String sql,Object ...args) {\/\/sql\u4e2d\u5360\u4f4d\u7b26\u7684\u4e2a\u6570\u4e0e\u53ef\u53d8\u5f62\u53c2\u7684\u957f\u5ea6\u76f8\u540c\uff01\n\t\tConnection conn = null;\n\t\tPreparedStatement ps = null;\n\t\ttry {\n\t\t\t\/\/1.\u83b7\u53d6\u6570\u636e\u5e93\u7684\u8fde\u63a5\n\t\t\tconn = JDBCUtils.getConnection();\n\t\t\t\/\/2.\u9884\u7f16\u8bd1sql\u8bed\u53e5\uff0c\u8fd4\u56dePreparedStatement\u5b9e\u4f8b\n\t\t\tps = conn.prepareStatement(sql);\n\t\t\t\/\/3.\u586b\u5145\u5360\u4f4d\u7b26\n\t\t\tfor(int i = 0; i &lt; args.length; i++) {\n\t\t\t\tps.setObject(i + 1, args&#91;i]);\/\/\u5c0f\u5fc3\u53c2\u6570\u58f0\u660e\u9519\u8bef\uff01\uff01\n\t\t\t}\n\t\t\t\/\/4.\u6267\u884c\n\t\t\tps.execute();\n\t\t} catch (SQLException e) {\n\t\t\t\/\/ TODO Auto-generated catch block\n\t\t\te.printStackTrace();\n\t\t} catch (Exception e) {\n\t\t\t\/\/ TODO Auto-generated catch block\n\t\t\te.printStackTrace();\n\t\t}finally {\n\t\t\t\/\/5.\u8d44\u6e90\u7684\u5173\u95ed\n\t\t\tJDBCUtils.closeResource(conn, ps);\n\t\t}\n\t\t\n\t}\n\t\n\t\/\/\u4fee\u6539customers\u8868\u7684\u4e00\u6761\u8bb0\u5f55\n\t@Test\n\tpublic void testUpdate() {\n\t\tConnection conn = null;\n\t\tPreparedStatement ps = null;\n\t\ttry {\n\t\t\t\/\/1.\u83b7\u53d6\u6570\u636e\u5e93\u7684\u8fde\u63a5\n\t\t\tconn = JDBCUtils.getConnection();\n\t\t\t\/\/2.\u9884\u7f16\u8bd1sql\u8bed\u53e5\uff0c\u8fd4\u56dePreparedStatement\u5b9e\u4f8b\n\t\t\tString sql = \"update customers set name = ? where id = ?\";\n\t\t\tps = conn.prepareStatement(sql);\n\t\t\t\/\/3.\u586b\u5145\u5360\u4f4d\u7b26\n\t\t\tps.setObject(1, \"\u83ab\u624e\u7279\");\n\t\t\tps.setObject(2, 18);\n\t\t\t\/\/4.\u6267\u884c\n\t\t\tps.execute();\n\t\t} catch (Exception e) {\n\t\t\te.printStackTrace();\n\t\t} finally {\n\t\t\t\/\/5.\u8d44\u6e90\u7684\u5173\u95ed\n\t\t\tJDBCUtils.closeResource(conn, ps);\n\t\t}\n\t\t\n\t}\n\t\n\t\/\/\u5411customers\u8868\u4e2d\u6dfb\u52a0\u4e00\u6761\u8bb0\u5f55\n\t@Test\n\tpublic void testInsert() {\n\t\tConnection conn = null;\n\t\tPreparedStatement ps = null;\n\t\ttry {\n\t\t\t\/\/ 1.\u8bfb\u53d6\u914d\u7f6e\u6587\u4ef6\u4e2d\u76844\u4e2a\u57fa\u672c\u4fe1\u606f\n\t\t\tInputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream(\"jdbc.properties\");\n\t\t\t\n\t\t\tProperties pros = new Properties();\n\t\t\tpros.load(is);\n\t\t\t\n\t\t\tString user = pros.getProperty(\"user\");\n\t\t\tString password = pros.getProperty(\"password\");\n\t\t\tString url = pros.getProperty(\"url\");\n\t\t\tString driverClass = pros.getProperty(\"driverClass\");\n\t\t\t\n\t\t\t\/\/ 2.\u52a0\u8f7d\u9a71\u52a8\n\t\t\tClass.forName(driverClass);\n\t\t\t\n\t\t\t\/\/ 3.\u83b7\u53d6\u8fde\u63a5\n\t\t\tconn = DriverManager.getConnection(url, user, password);\n\t\t\t\n\/\/\t\tSystem.out.println(conn);\n\t\t\t\n\t\t\t\/\/4.\u9884\u7f16\u8bd1sql\u8bed\u53e5\uff0c\u8fd4\u56dePreparedStatement\u7684\u5b9e\u4f8b\n\t\t\tString sql = \"insert into customers(name,email,birth)values(?,?,?)\";\/\/?:\u5360\u4f4d\u7b26\n\t\t\tps = conn.prepareStatement(sql);\n\t\t\t\/\/5.\u586b\u5145\u5360\u4f4d\u7b26\n\t\t\tps.setString(1, \"\u54ea\u5412\");\n\t\t\tps.setString(2, \"nezha@gmail.com\");\n\t\t\tSimpleDateFormat sdf = new SimpleDateFormat(\"yyyy-MM-dd\");\n\t\t\tjava.util.Date date = sdf.parse(\"1000-01-01\");\n\t\t\tps.setDate(3, new Date(date.getTime()));\n\t\t\t\n\t\t\t\/\/6.\u6267\u884c\u64cd\u4f5c\n\t\t\tps.execute();\n\t\t} catch (Exception e) {\n\t\t\te.printStackTrace();\n\t\t}finally {\n\t\t\t\n\t\t\t\/\/7.\u8d44\u6e90\u7684\u5173\u95ed\n\t\t\ttry {\n\t\t\t\tif(ps != null)\n\t\t\t\tps.close();\n\t\t\t} catch (SQLException e) {\n\t\t\t\te.printStackTrace();\n\t\t\t}\n\t\t\ttry {\n\t\t\t\tif(conn != null)\n\t\t\t\tconn.close();\n\t\t\t} catch (SQLException e) {\n\t\t\t\te.printStackTrace();\n\t\t\t}\n\t\t}\n\t\t\n\t}\n\t\n\t\n\n}\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>package top.qaqaq.P14.util;\r\n\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\n\/**\r\n * \r\n * @Description \u64cd\u4f5c\u6570\u636e\u5e93\u7684\u5de5\u5177\u7c7b\r\n * @author RichieZhang Email:18358892@qq.com\r\n * @version\r\n * @date 2022\u5e7411\u670826\u65e5\u4e0b\u53483:57:13\r\n *\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 \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 \t\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<\/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-1774","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\/1774"}],"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=1774"}],"version-history":[{"count":2,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1774\/revisions"}],"predecessor-version":[{"id":1777,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1774\/revisions\/1777"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1774"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}