{"id":1926,"date":"2022-11-29T15:33:22","date_gmt":"2022-11-29T07:33:22","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1926"},"modified":"2022-11-29T15:33:23","modified_gmt":"2022-11-29T07:33:23","slug":"%e4%bd%bf%e7%94%a8queryrunner%e6%b5%8b%e8%af%95%e6%b7%bb%e5%8a%a0%e6%95%b0%e6%8d%ae%e7%9a%84%e6%93%8d%e4%bd%9c%e3%80%81%e4%bd%bf%e7%94%a8queryrunner%e6%9f%a5%e8%af%a2%e8%a1%a8%e4%b8%ad%e4%b8%80","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1926","title":{"rendered":"\u4f7f\u7528QueryRunner\u6d4b\u8bd5\u6dfb\u52a0\u6570\u636e\u7684\u64cd\u4f5c\u3001\u4f7f\u7528QueryRunner\u67e5\u8be2\u8868\u4e2d\u4e00\u6761\u6216\u591a\u6761\u8bb0\u5f55\u7684\u64cd\u4f5c\u3001\u4f7f\u7528QueryRunner\u67e5\u8be2\u8868\u4e2d\u7279\u6b8a\u503c\u7684\u64cd\u4f5c\u3001\u81ea\u5b9a\u4e49ResultSetHandler\u7684\u5b9e\u73b0\u7c7b\u5b8c\u6210\u67e5\u8be2\u64cd\u4f5c"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>package top.qaqaq.P51.dbutils;\r\n\r\nimport java.sql.Connection;\r\nimport java.sql.Date;\r\nimport java.sql.ResultSet;\r\nimport java.sql.SQLException;\r\nimport java.util.List;\r\nimport java.util.Map;\r\n\r\nimport org.apache.commons.dbutils.QueryRunner;\r\nimport org.apache.commons.dbutils.ResultSetHandler;\r\nimport org.apache.commons.dbutils.handlers.BeanHandler;\r\nimport org.apache.commons.dbutils.handlers.BeanListHandler;\r\nimport org.apache.commons.dbutils.handlers.MapHandler;\r\nimport org.apache.commons.dbutils.handlers.MapListHandler;\r\nimport org.apache.commons.dbutils.handlers.ScalarHandler;\r\nimport org.junit.Test;\r\n\r\nimport top.qaqaq.P43.bean.Customer;\r\nimport top.qaqaq.P48.util.JDBCUtils;\r\n\r\n\/*\r\n * commons-dbutils \u662f Apache \u7ec4\u7ec7\u63d0\u4f9b\u7684\u4e00\u4e2a\u5f00\u6e90 JDBC\u5de5\u5177\u7c7b\u5e93,\u5c01\u88c5\u4e86\u9488\u5bf9\u4e8e\u6570\u636e\u5e93\u7684\u589e\u5220\u6539\u67e5\u64cd\u4f5c\r\n * \r\n *\/\r\npublic class QueryRunnerTest {\r\n\t\r\n\t\/\/\u6d4b\u8bd5\u63d2\u5165\r\n\t@Test\r\n\tpublic void testInsert() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\tString sql = \"insert into customers(name,email,birth) values(?,?,?)\";\r\n\t\t\tint insertCount = runner.update(conn, sql, \"\u8521\u5f90\u5764\", \"caixukun@126.com\", \"1997-09-08\");\r\n\t\t\tSystem.out.println(\"\u6dfb\u52a0\u4e86\" + insertCount + \"\u6761\u8bb0\u5f55\");\r\n\t\t} catch (SQLException 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\r\n\t}\r\n\t\r\n\t\/\/\u6d4b\u8bd5\u67e5\u8be2\r\n\t\/*\r\n\t * BeanHander:\u662fResultSetHandler\u63a5\u53e3\u7684\u5b9e\u73b0\u7c7b\uff0c\u7528\u4e8e\u5c01\u88c5\u8868\u4e2d\u7684\u4e00\u6761\u8bb0\u5f55\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery1() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\tString sql = \"select id,name,email,birth from customers where id = ?\";\r\n\t\t\tBeanHandler&lt;Customer> handler = new BeanHandler&lt;>(Customer.class);\r\n\t\t\tCustomer customer = runner.query(conn, sql, handler, 25);\r\n\t\t\tSystem.out.println(customer);\r\n\t\t} catch (SQLException 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\t\r\n\t\/*\r\n\t * BeanListHandler:\u662fResultSetHandler\u63a5\u53e3\u7684\u5b9e\u73b0\u7c7b\uff0c\u7528\u4e8e\u5c01\u88c5\u8868\u4e2d\u7684\u591a\u6761\u8bb0\u5f55\u6784\u6210\u7684\u96c6\u5408\u3002\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery2() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\tString sql = \"select id,name,email,birth from customers where id &lt; ?\";\r\n\t\t\t\r\n\t\t\tBeanListHandler&lt;Customer> handler = new BeanListHandler&lt;>(Customer.class);\r\n\t\t\t\r\n\t\t\tList&lt;Customer> list = runner.query(conn, sql, handler, 25);\r\n\t\t\tlist.forEach(System.out::println);\r\n\t\t} catch (SQLException 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\t\r\n\t\/*\r\n\t * MapHander:\u662fResultSetHandler\u63a5\u53e3\u7684\u5b9e\u73b0\u7c7b\uff0c\u5bf9\u5e94\u8868\u4e2d\u7684\u4e00\u6761\u8bb0\u5f55\u3002\r\n\t * \u5c06\u5b57\u6bb5\u53ca\u76f8\u5e94\u5b57\u6bb5\u7684\u503c\u4f5c\u4e3amap\u4e2d\u7684key\u548cvalue\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery3() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\tString sql = \"select id,name,email,birth from customers where id = ?\";\r\n\t\t\t\r\n\t\t\tMapHandler handler = new MapHandler();\r\n\t\t\t\r\n\t\t\tMap&lt;String,Object> map = runner.query(conn, sql, handler, 25);\r\n\t\t\tSystem.out.println(map);\r\n\t\t} catch (SQLException 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\t\r\n\t\/*\r\n\t * MapListHander:\u662fResultSetHandler\u63a5\u53e3\u7684\u5b9e\u73b0\u7c7b\uff0c\u5bf9\u5e94\u8868\u4e2d\u7684\u591a\u6761\u8bb0\u5f55\u3002\r\n\t * \u5c06\u5b57\u6bb5\u53ca\u76f8\u5e94\u5b57\u6bb5\u7684\u503c\u4f5c\u4e3amap\u4e2d\u7684key\u548cvalue\u3002\u5c06\u8fd9\u4e9bmap\u6dfb\u52a0\u5230List\u4e2d\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery4() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\tString sql = \"select id,name,email,birth from customers where id &lt; ?\";\r\n\t\t\t\r\n\t\t\tMapListHandler handler = new MapListHandler();\r\n\t\t\t\r\n\t\t\tList&lt;Map&lt;String, Object>> list = runner.query(conn, sql, handler, 25);\r\n\t\t\tlist.forEach(System.out::println);\r\n\t\t} catch (SQLException 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\t\r\n\t\/*\r\n\t * ScalarHandler:\u7528\u4e8e\u67e5\u8be2\u7279\u6b8a\u503c\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery5() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\t\r\n\t\t\tString sql = \"select count(*) from customers\";\r\n\t\t\t\r\n\t\t\tScalarHandler handler = new ScalarHandler();\r\n\t\t\t\r\n\t\t\tLong count = (Long) runner.query(conn, sql, handler);\r\n\t\t\tSystem.out.println(count);\r\n\t\t} catch (SQLException 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\t\r\n\t@Test\r\n\tpublic void testQuery6() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\t\r\n\t\t\tString sql = \"select max(birth) from customers\";\r\n\t\t\t\r\n\t\t\tScalarHandler handler = new ScalarHandler();\r\n\t\t\t\r\n\t\t\tDate maxBirth = (Date) runner.query(conn, sql, handler);\r\n\t\t\tSystem.out.println(maxBirth);\r\n\t\t} catch (SQLException 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\t\r\n\t\/*\r\n\t * \u81ea\u5b9a\u4e49ResultSetHandler\u7684\u5b9e\u73b0\u7c7b\r\n\t *\/\r\n\t@Test\r\n\tpublic void testQuery7() {\r\n\t\tConnection conn = null;\r\n\t\ttry {\r\n\t\t\tQueryRunner runner = new QueryRunner();\r\n\t\t\tconn = JDBCUtils.getConnection3();\r\n\t\t\t\r\n\t\t\tString sql = \" select id,name,email,birth from customers where id = ?\";\r\n\t\t\tResultSetHandler&lt;Customer> handler = new ResultSetHandler&lt;Customer>() {\r\n\r\n\t\t\t\t@Override\r\n\t\t\t\tpublic Customer handle(ResultSet rs) throws SQLException {\r\n\/\/\t\t\t\t\tSystem.out.println(\"handle\");\r\n\/\/\t\t\t\t\treturn null;\r\n\t\t\t\t\t\r\n\/\/\t\t\t\t\treturn new Customer(12, \"\u6210\u9f99\", \"jacky@126.com\", new Date(23131414214412L));\r\n\t\t\t\t\t\r\n\t\t\t\t\tif(rs.next()) {\r\n\t\t\t\t\t\tint id = rs.getInt(\"id\");\r\n\t\t\t\t\t\tString name = rs.getString(\"name\");\r\n\t\t\t\t\t\tString email = rs.getString(\"email\");\r\n\t\t\t\t\t\tDate birth = rs.getDate(\"birth\");\r\n\t\t\t\t\t\tCustomer customer = new Customer(id, name, email, birth);\r\n\t\t\t\t\t\treturn customer;\r\n\t\t\t\t\t}\r\n\t\t\t\t\t\r\n\t\t\t\t\treturn null;\r\n\t\t\t\t}\r\n\t\t\t\t\r\n\t\t\t};\r\n\t\t\tCustomer customer = runner.query(conn, sql, handler,23);\r\n\t\t\tSystem.out.println(customer);\r\n\t\t} catch (SQLException 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<\/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-1926","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\/1926"}],"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=1926"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1926\/revisions"}],"predecessor-version":[{"id":1927,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1926\/revisions\/1927"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}