{"id":451,"date":"2021-02-18T13:46:35","date_gmt":"2021-02-18T05:46:35","guid":{"rendered":"http:\/\/moyisuiying.com\/?p=451"},"modified":"2021-02-18T13:46:35","modified_gmt":"2021-02-18T05:46:35","slug":"mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%85%a5%e9%97%a8%e5%ad%a6%e4%b9%a0%ef%bc%88%e4%ba%94%ef%bc%89-%e4%bd%bf%e7%94%a8%e5%b7%a5%e5%85%b7%e7%b1%bb%e5%b0%81%e8%a3%85jdbc%e5%ae%9e%e7%8e%b0my","status":"publish","type":"post","link":"http:\/\/moyisuiying.com\/index.php\/javastudy\/mysql\/451.html","title":{"rendered":"MySQL\u6570\u636e\u5e93\u5165\u95e8\u5b66\u4e60\uff08\u4e94\uff09\u2014\u2014\u4f7f\u7528\u5de5\u5177\u7c7b\u5c01\u88c5JDBC\u5b9e\u73b0MySQL\u7684\u67e5\u8be2\u64cd\u4f5c"},"content":{"rendered":"\n

MySQL\u6570\u636e\u5e93\u5165\u95e8\u5b66\u4e60\uff08\u4e94\uff09\u2014\u2014\u4f7f\u7528\u5de5\u5177\u7c7b\u5c01\u88c5JDBC\u5b9e\u73b0MySQL\u7684\u67e5\u8be2\u64cd\u4f5c<\/h1>\n\n\n\n

1.\u524d\u8a00<\/h2>\n\n\n\n

\u6211\u4eec\u77e5\u9053\uff0c\u4e8b\u5b9e\u4e0a\uff0cMySQL\u7684 \u201c\u589e\u5220\u67e5\u6539\u201d\u5176\u5b9e\u5c31\u662f\u5206\u4e3a\u4e24\u7c7b\uff0c\u4e00\u7c7b\u662f\u66f4\u65b0\u64cd\u4f5c\uff1aint executeUpdate(String sql)\u5305\u62ec\u4e86\u63d2\u5165\uff08insert\uff09\uff0c\u66f4\u65b0\uff08update\uff09\uff0c\u5220\u9664\uff08delete\uff09\uff1b\u53e6\u5916\u4e00\u7c7b\u662f\u67e5\u8be2\u64cd\u4f5c\uff08select\uff09\uff0c\u7531ResultSet executeQuery(String sql) \u6216\u8005 ResultSet executeQuery() \u5b9e\u73b0\u3002\u5728\u4e0a\u7bc7\u6587\u7ae0\uff1aMySQL\u6570\u636e\u5e93\u5165\u95e8\u5b66\u4e60\uff08\u56db\uff09\u2014\u2014\u4f7f\u7528\u5de5\u5177\u7c7b\u5c01\u88c5JDBC\u5b9e\u73b0MySQL\u7684\u66f4\u65b0\u64cd\u4f5c<\/a>\u4e2d\u5df2\u7ecf\u5b9e\u73b0\u4e86\u81ea\u5b9a\u4e49\u5c01\u88c5\u7684MySQL\u7684\u66f4\u65b0\u64cd\u4f5c\u3002\u73b0\u5728\u6211\u4eec\u5f00\u59cb\u5bf9\u67e5\u8be2\u64cd\u4f5c\u8fdb\u884c\u5c01\u88c5.<\/p>\n\n\n\n

2.\u67e5\u8be2\u64cd\u4f5c\u7684\u6b65\u9aa4<\/h2>\n\n\n\n

2.1\u9996\u5148\u89c2\u5bdf\u4e00\u4e2a\u67e5\u8be2\u65b9\u6cd5<\/h3>\n\n\n\n
\/**\n     * \u901a\u8fc7ID\u67e5\u8be2book\n     * @param id \u8981\u67e5\u8be2\u7684book\u7684ID\n     * @return \u8fd4\u56deID\u5bf9\u5e94\u7684book\n     *\/\n    public Book findBookById(int id) {\n        \/\/\u9700\u8981\u6267\u884c\u7684\u67e5\u627e\u7684sql\u8bed\u53e5\n        String sql = \"select * from book  where id = ?\";\n        \/\/\u7ed3\u679c\u96c6\n        ResultSet resultSet = null;\n        Book book = null;\n        try {\n            \/\/\u83b7\u53d6\u521b\u5efa\u8fde\u63a5\n            Connection connection = JDBCUtil.getConnection();\n            \/\/\u521b\u5efa\u9884\u5904\u7406\u5bf9\u8c61\n            PreparedStatement preparedStatement = connection.prepareStatement(sql);\n            \/\/\u8bbe\u7f6eid\u5360\u4f4d\u7b26\u5bf9\u5e94\u7684\u503c\n            preparedStatement.setInt(1, id);\n            \/\/\u5f00\u59cb\u6267\u884c\u67e5\u8be2\u8bed\u53e5,\u5e76\u8fd4\u56de\u67e5\u8be2\u7684\u7ed3\u679c\u96c6\n            resultSet = preparedStatement.executeQuery();\n            \/\/\u5982\u679c\u67e5\u8be2\u5230\u7ed3\u679c\u4e0d\u4e3a\u7a7a\u800c\u662f\u6709\u8bb0\u5f55\n            if(resultSet.next()) {\n            \/\/\u83b7\u53d6book\u7684name\n            String name = resultSet.getString(\"name\");\n            \/\/\u83b7\u53d6book\u7684status\n            byte status = resultSet.getByte(\"status\");\n            \/\/\u83b7\u53d6book\u7684price\n            double price = resultSet.getDouble(\"price\");\n            \/\/\u83b7\u53d6book\u7684discount\n            float discount = resultSet.getFloat(\"discount\");\n            \/\/\u83b7\u53d6book\u7684isBorrowed\n            boolean isBorrowed = resultSet.getBoolean(\"isBorrowed\");\n            \/\/\u83b7\u53d6book\u7684createTime\n            Date date = resultSet.getDate(\"createTime\");\n            book = new Book();\n            book.setBorrowed(isBorrowed);\n            book.setDiscount(discount);\n            book.setId(id);\n            book.setPrice(price);\n            book.setName(name);\n            book.setStatus(status);\n            book.setCreateTime(date);\n            }\n            \/\/\u91ca\u653e\u8d44\u6e90,\u5148\u5f00\u542f\u7684\u540e\u91ca\u653e\n            JDBCUtil.close(connection, preparedStatement, resultSet);\n        } catch (SQLException e) {\n            e.printStackTrace();\n            book = null;\n        }\n\n\n        return book;\n\n    }<\/code><\/pre>\n\n\n\n
\u6211\u4eec\u53d1\u73b0\uff0c\u67e5\u8be2\uff08query\uff09\u64cd\u4f5c\u6709\u8fd9\u51e0\u4e2a\u6b65\u9aa4\uff1a
\u2460\u83b7\u53d6\u8fde\u63a5\u5bf9\u8c61Connection \u3002
\u2461\u6839\u636esql\u8bed\u53e5\u521b\u5efa\u9884\u5904\u7406\u5bf9\u8c61PreparedStatement
\u2462\u4f7f\u7528\u5b9e\u9645\u53c2\u6570\u66ff\u6362PreparedStatement \u4e2d\u7684\u5360\u4f4d\u7b26\u201c?\u201d\u3002
\u2463\u6267\u884c\u67e5\u8be2\u64cd\u4f5c\uff1apreparedStatement.executeQuery()\uff0c\u5f97\u5230\u7ed3\u679c\u96c6ResultSet\u3002
\u2464\u5c01\u88c5\u7ed3\u679c\u96c6ResultSet\u5230\u5b9e\u4f53\u5bf9\u8c61book\u4e2d\u3002
\u2465\u5173\u95ed\u8d44\u6e90
\u2466\u8fd4\u56de\u7ed3\u679c\u5bf9\u8c61book\u3002
\u8fd9\u4e2a\u662f\u5bf9\u4e8e\u65b9\u6cd5 public Book findBookById(int id)\uff1b\u6765\u8bf4\uff0c\u5b83\u7684\u53c2\u6570\u53ea\u6709\u4e00\u4e2a\uff0c\u4f46\u662f\u5728\u6709\u4e9b\u67e5\u8be2\u60c5\u51b5\u4e2d\uff0c\u67e5\u8be2\u7684\u6761\u4ef6\u80af\u5b9a\u4e0d\u6b62\u4e00\u4e2a\uff0c\u56e0\u6b64\u6765\u8bf4\uff0c\u6211\u4eec\u53ef\u4ee5\u5c06\u8fd9\u4e2a\u67e5\u8be2\u6761\u4ef6\u7528\u4e00\u4e2a\u53ef\u53d8\u7684Object\u6570\u7ec4params\u6765\u8868\u793a\u3002\u7136\u540epreparedStatement.setXXX(int parameterIndex, XXX x);\u4e2dXXX\u4e5f\u5c31\u662f\u586b\u5145\u7684\u5b9e\u9645\u53c2\u6570\u7c7b\u578b\u662f\u4e0d\u786e\u5b9a\u7684\u3002\u8fd9\u65f6\u5019\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528preparedStatement.setObject(int parameterIndex, Object x)\u6765\u8fdb\u884c\u8bbe\u7f6e\u53c2\u6570\u3002\u56e0\u4e3aObject\u662f\u6240\u6709\u7c7b\u7684\u516c\u5171\u7236\u7c7b\uff0c\u6240\u4ee5\u4f7f\u7528\u8fd9\u4e2a\u53c2\u6570\u53ef\u4ee5\u8fbe\u5230\u4ee3\u7801\u901a\u7528\u6027\u6765\u8bbe\u7f6e\u5360\u4f4d\u7b26\u201c?\"\u3002<\/p>\n\n\n\n
2.2\u62bd\u53d6\u901a\u7528\u7684\u67e5\u8be2\u65b9\u6cd5<\/h3>\n\n\n\n
\u4f5c\u4e3a\u4e00\u4e2a\u901a\u7528\u7684\u67e5\u8be2\u65b9\u6cd5\uff0c\u67e5\u8be2\u4e0d\u540c\u7684\u5bf9\u8c61\u65f6\u5019\u8fd4\u56de\u7684\u7ed3\u679c\u5bf9\u8c61\u4e0d\u540c\uff0c\u6bd4\u5982\u8bf4\u67e5\u8be2\u5b66\u751fStudent\u90a3\u4e48\u8fd4\u56de\u7684\u662fstudent\u5bf9\u8c61\uff0c\u67e5\u8be2book\uff0c\u90a3\u4e48\u8fd4\u56de\u7684\u662fbook\u5bf9\u8c61\u7b49\u3002\u56e0\u6b64\u8fd9\u4e2a\u901a\u7528\u7684\u67e5\u8be2\u65b9\u6cd5\u8fd4\u56de\u7684\u662f\u4e00\u4e2a\u6cdb\u578b\u5bf9\u8c61\uff0c\u4ea4\u7ed9\u67e5\u8be2\u8005\u6307\u5b9a\uff1a<\/p>\n\n\n\n
public static <T> T  query(String sql,Object... params)<\/code><\/pre>\n\n\n\n
\u4f46\u662f\u6211\u4eec\u77e5\u9053\uff0c\u8fd9\u6837\u5b50\u901a\u8fc7preparedStatement.setObject(i+1,params[i]);\u540e\u6267\u884c ResultSet result =preparedStatement.executeQuery();\u5f97\u5230\u7684\u662f\u4e00\u4e2a\u7ed3\u679c\u96c6ResultSet \uff0c\u8fd9\u662f\u65f6\u5019\u6211\u4eec\u9700\u8981\u5bf9\u8fd9\u4e2a\u7ed3\u679c\u96c6\u5c01\u88c5\u6210\u5bf9\u5e94\u7684\u5b9e\u4f53\u5bf9\u8c61\u3002\u7531\u6b64\u6211\u4eec\u9700\u8981\u6307\u5b9a\u4e00\u4e2a\u5904\u7406\u5668ResultSetHandler\u4e13\u95e8\u5904\u7406\u8fd9\u4e2a\u7ed3\u679c\u96c6\u3002\u8fd9\u91cc\u5b9a\u4e49\u4e00\u4e2a\u63a5\u53e3\uff1a<\/p>\n\n\n\n
\/**\n * @author \u964c\u610f\u968f\u5f71\n TODO :\u7ed3\u679c\u96c6\u5904\u7406\u5668\u3002\n *2021\u5e742\u670818\u65e5  \u4e0b\u534812:44:11\n *\/\npublic interface ResultSetHandler<T> {\n    \/**\n     * @param resultSet  \u67e5\u8be2\u7684\u7ed3\u679c\u96c6\n     * @return  \u8fd4\u56de\u4e00\u4e2a\u5b9e\u4f53\u5bf9\u8c61\n     *\/\n    T handleResutl(ResultSet resultSet);\n\n}<\/code><\/pre>\n\n\n\n
\u8fd9\u6837\uff0c\u6211\u4eec\u5b9a\u4e49\u7684\u8fd9\u4e2a\u67e5\u8be2\u65b9\u6cd5\u62bd\u53d6\u4e3a\uff1a<\/p>\n\n\n\n
public static <T> T  query(String sql, ResultSetHandler<T>  resultSetHandler,Object... params);<\/code><\/pre>\n\n\n\n
\u8fd9\u4e2aResultSetHandler\u63a5\u53e3\u6211\u4eec\u53ef\u4ee5\u7531\u67e5\u8be2\u8005\u81ea\u5b9a\u4e49\u5b9e\u73b0\u8fd9\u4e2a\u63a5\u53e3\uff0c\u7136\u540e\u5728\u8fd9\u4e2aT handleResutl(ResultSet resultSet);\u65b9\u6cd5\u4e2d\u5c06ResultSet\u5c01\u88c5\u6210\u4e00\u4e2a\u5b9e\u4f53\u5bf9\u8c61T\u5e76\u8fd4\u56de\u5373\u53ef\u3002<\/p>\n\n\n\n
2.3\u5b8c\u6574\u7684\u67e5\u8be2\u65b9\u6cd5\u4ee3\u7801<\/h3>\n\n\n\n
\/**\n * \u6267\u884csql\u7684\u67e5\u8be2\u8bed\u53e5\u3002\n * @param sql \u9700\u8981\u6267\u884c\u7684sql\u8bed\u53e5\n * @param params \u9884\u5904\u7406\u5360\u4f4d\u7b26\u4e2d\u7684\u5b9e\u9645\u53c2\u6570\u6570\u7ec4\n * @resultSetHandler \u7ed3\u679c\u96c6\u5904\u7406\u5668\uff0c\u8d1f\u8d23\u5c06\u7ed3\u679c\u96c6ResultSet\u5c01\u88c5\u6210\u5bf9\u8c61\u3002\n * @return \u8fd4\u56de\u67e5\u8be2\u7684\u5bf9\u8c61\u3002\n *\/\npublic static <T> T  query(String sql, ResultSetHandler<T>  resultSetHandler,Object... params){\n      if (params==null) return null;\n      \/\/\u83b7\u53d6\u8fde\u63a5\u5bf9\u8c61\n    Connection connection=JDBCUtil.getConnection();\n    PreparedStatement preparedStatement=null;\n\n    ResultSet resultSet = null;\n    try {\n        \/\/\u521b\u5efa\u9884\u5904\u7406\u5bf9\u8c61\n        preparedStatement= connection.prepareStatement(sql);\n        \/\/\u4f7f\u7528\u5b9e\u9645\u53c2\u6570\u66ff\u6362\u5360\u4f4d\u7b26\n        for (int i= 0;i < params.length;i++){\n              \/\/parameterIndex\u4ece1\u5f00\u59cb\n               preparedStatement.setObject(i+1,params[i]);\n        }\n        \/\/\u6267\u884csql\u67e5\u8be2\u8bed\u53e5\n        resultSet = preparedStatement.executeQuery();\n    } catch (SQLException e) {\n        e.printStackTrace();\n    }\n    \/\/\u5173\u95ed\u8d44\u6e90\n    JDBCUtil.close(connection,preparedStatement,null);\n    \/\/\u901a\u8fc7\u5904\u7406\u5668\u5904\u7406\u8fd9\u4e2a\u7ed3\u679c\u96c6\uff0c\u5c01\u88c5\u6210\u4e00\u4e2a\u5b9e\u4f53\u5bf9\u8c61\n    T t = resultSetHandler.resultSetHandler(resultSet);\n      return t;\n}<\/code><\/pre>\n\n\n\n
3.\u5b9e\u73b0\u67e5\u8be2book\u7684\u65b9\u6cd5<\/h2>\n\n\n\n
3.1\u9996\u5148\u81ea\u5b9a\u4e49\u5b9e\u73b0\u4e00\u4e2abook\u7684\u7ed3\u679c\u96c6\u5904\u7406\u5668\uff0c\u5bf9\u7ed3\u679c\u96c6\u8fdb\u884c\u5c01\u88c5\u6210\u4e00\u4e2a\u5bf9\u8c61<\/h3>\n\n\n\n
\/**\n * @author \u964c\u610f\u968f\u5f71\n TODO :book\u7684\u7ed3\u679c\u96c6\u5904\u7406\u5b9e\u73b0\u7c7b\n *2021\u5e742\u670818\u65e5  \u4e0b\u53481:26:07\n *\/\npublic class BookResultSetHandlerImpl implements ResultSetHandler<List<Book>>{\n\n    @Override\n    public List<Book> resultSetHandler(ResultSet resultSet) {\n        List<Book> bookList = new ArrayList<>();\n        try {\n            \/\/\u5982\u679c\u67e5\u8be2\u5230\u7ed3\u679c\u4e0d\u4e3a\u7a7a\u800c\u662f\u6709\u8bb0\u5f55\n            while(resultSet.next()) {\n            \/\/\u83b7\u53d6book\u7684ID\n            int id = resultSet.getInt(\"id\");\n            \/\/\u83b7\u53d6book\u7684name\n            String name = resultSet.getString(\"name\");\n            \/\/\u83b7\u53d6book\u7684status\n            byte status = resultSet.getByte(\"status\");\n            \/\/\u83b7\u53d6book\u7684price\n            double price = resultSet.getDouble(\"price\");\n            \/\/\u83b7\u53d6book\u7684discount\n            float discount = resultSet.getFloat(\"discount\");\n            \/\/\u83b7\u53d6book\u7684isBorrowed\n            boolean isBorrowed = resultSet.getBoolean(\"isBorrowed\");\n            \/\/\u83b7\u53d6book\u7684createTime\n            Date date = resultSet.getDate(\"createTime\");\n            Book book = new Book();\n            book.setBorrowed(isBorrowed);\n            book.setDiscount(discount);\n            book.setId(id);\n            book.setPrice(price);\n            book.setName(name);\n            book.setStatus(status);\n            book.setCreateTime(date);\n            bookList.add(book);\n            }\n        } catch (Exception e) {\n            e.printStackTrace();\n        }\n        return bookList;\n    }<\/code><\/pre>\n\n\n\n
3.2\u67e5\u8be2\u6240\u6709book<\/h3>\n\n\n\n
\/**\n     * \u67e5\u8be2\u6240\u6709\u56fe\u4e66\n     * @return \u8fd4\u56deList<book>\n     *\/\n    public List<Book> findAllBook() {\n        \/\/\u62fc\u63a5\u9700\u8981\u6267\u884c\u7684\u67e5\u627e\u7684sql\u8bed\u53e5\n        String sql = \"select * from book \";\n        \/\/\u7ed3\u679c\u96c6\n        List<Book> bookList = JDBCUtil.query(sql, new BookResultSetHandlerImpl());\n        return bookList;\n\n    }<\/code><\/pre>\n\n\n\n
3.3\u901a\u8fc7ID\u67e5\u8be2book<\/h3>\n\n\n\n
  \/**\n     * \u901a\u8fc7ID\u67e5\u8be2book\n     * @param id \u8981\u67e5\u8be2\u7684book\u7684ID\n     * @return \u8fd4\u56deID\u5bf9\u5e94\u7684book\n     *\/\n    public Book findBookById(int id) {\n        \/\/\u9700\u8981\u6267\u884c\u7684\u67e5\u627e\u7684sql\u8bed\u53e5\n        String sql = \"select * from book  where id = ?\";\n        \/\/\u7ed3\u679c\u96c6\n        List<Book> bookList = JDBCUtil.query(sql, new BookResultSetHandlerImpl(),id);\n        if (bookList == null || bookList.size() == 0 ) {\n            return null;\n        }\n        \/\/\u6839\u636eID\u67e5\u8be2\u53ea\u6709\u4e00\u4e2a\u5bf9\u8c61\n        Book book = bookList.get(0);\n        return book;\n\n    }<\/code><\/pre>\n\n\n\n
4.\u6d4b\u8bd5<\/h2>\n\n\n\n
\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"\/<\/figure >\n