{"id":2790,"date":"2020-12-19T16:08:31","date_gmt":"2020-12-19T08:08:31","guid":{"rendered":"http:\/\/127.0.0.2\/?p=2790"},"modified":"2020-12-19T16:20:36","modified_gmt":"2020-12-19T08:20:36","slug":"2020java%e6%a1%86%e6%9e%b6%e6%95%99%e7%a8%8b%e4%b9%8bmybatis%e5%8a%a8%e6%80%81sql","status":"publish","type":"post","link":"https:\/\/www.9713job.com\/?p=2790","title":{"rendered":"2020java\u6846\u67b6\u6559\u7a0b\u4e4bmybatis\u52a8\u6001sql"},"content":{"rendered":"

2020java\u6846\u67b6\u6559\u7a0b\u4e4bmybatis\u52a8\u6001sql<\/h3>\n

1.if<\/h3>\n

\u9700\u6c42\uff1a<\/p>\n

1.\u67e5\u8be2\u5df2\u6fc0\u6d3b\u7684\uff0c\u5e76\u4e14\u535a\u5ba2\u7684\u540d\u5b57\u662f\u5305\u542b\u67d0\u4e2a\u67e5\u8be2\u5b57\u7b26\u4e32\u7684\u8bb0\u5f55
\n2.\u5982\u679c\u7528\u6237\u6ca1\u6709\u8f93\u5165\u4efb\u4f55\u67e5\u8be2\u5b57\u7b26\u4e32\uff0c\u90a3\u4e48\u5c31\u663e\u793a\u6240\u6709\u5df2\u6fc0\u6d3b\u7684\u535a\u5ba2<\/p>\n

mapper:<\/strong><\/p>\n

<select id=\"selectActiveBlogByTitle\" parameterType=\"string\" resultMap=\"blogResultMap\">\r\n    select*from blog where state = 'active'\r\n    <if test=\"value !=null and value !='' \">\r\n        and title like concat('%','${value}','%')\r\n    <\/if>\r\n<\/select><\/pre>\n
\"\"
\ndao:<\/strong><\/p>\n
List<Blog> selectActiveBlogByTitle(String title);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
 <\/p>\n
@Test\r\npublic void test13(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    List<Blog> blogList = mapper.selectActiveBlogByTitle(\"a\");\r\n    List<Blog> blogList = mapper.selectActiveBlogByTitle(null);\r\n    sqlSession.close();\r\n    System.out.println(blogList );\r\n}<\/pre>\n
\"\"<\/p>\n
2.choose\u3001when\u3001otherwise<\/h3>\n
\u9700\u6c42\uff1a
\n1.\u67e5\u8be2\u5df2\u6fc0\u6d3b\u7684
\n2.\u5982\u679c\u7528\u6237\u8f93\u5165\u4e86\u6807\u9898\u7684\u67e5\u8be2\u5173\u952e\u5b57\uff0c\u5219\u6839\u636e\u5173\u952e\u5b57\u67e5\u8be2
\n3.\u5426\u5219\u6839\u636eblog\u7684\u98ce\u683c\u6837\u5f0f\u67e5\u8be2
\n4.\u5982\u679c\u4ec0\u4e48\u90fd\u6ca1\u8f93\u5165\uff0c\u5219\u663e\u793a\u63a8\u8350\u7684\u535a\u5ba2<\/p>\n
mapper\uff1a<\/strong><\/p>\n
<select id=\"selectActiveBlogByTitleOrStyle\" parameterType=\"Blog\" resultMap=\"blogResultMap\">\r\n    select*from blog where state='active'\r\n    <choose>\r\n        <when test=\"title !=null and titlt !=''\">\r\n            and lower(title) like lower(#{title})\r\n        <\/when>\r\n        <when test=\"style != null and style !=''\">\r\n            and style = #{style}\r\n        <\/when>\r\n        <otherwise>\r\n            and featured = true\r\n        <\/otherwise>\r\n    <\/choose>\r\n<\/select><\/pre>\n
\"\"<\/p>\n
dao\uff1a<\/strong><\/p>\n
List<Blog> selectActiveBlogByTitleOrStyle(Blog blog);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test14(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    Blog blog=new Blog();\r\n    blog.setTitle(\"%o%\");\r\n    blog.setStyle(\"black\");\r\n    List<Blog> blogList = mapper.selectActiveBlogByTitleOrStyle(blog);\r\n    sqlSession.close();\r\n    System.out.println(blogList);\r\n}<\/pre>\n
\"\"<\/p>\n
3.where<\/h3>\n
\u9700\u6c42\uff1a\u591a\u6761\u4ef6\u67e5\u8be2\uff0c\u6839\u636e\u72b6\u6001\u3001\u6807\u9898\u3001\u662f\u5426\u88ab\u63a8\u8350<\/p>\n
mapper\uff1a<\/strong><\/p>\n
<select id=\"selectBlogByCondition\" parameterType=\"Blog\" resultMap=\"blogResultMap\">\r\n    select*from blog\r\n    <where>\r\n        <if test=\"state != null and state !='' \">\r\n            state=#{state}\r\n        <\/if>\r\n        <if test=\"title != null and title !='' \">\r\n            and lower(title) like lower(#{title})\r\n        <\/if>\r\n        <if test=\"featured != null\">\r\n            and featured = #{featured}\r\n        <\/if>\r\n    <\/where>\r\n<\/select>\r\n\r\n\"\"\r\n<\/pre>\n
dao\uff1a<\/strong><\/p>\n
List<Blog> selectBlogByCondition(Blog blog);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test15(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    Blog blog=new Blog();\r\n    blog.setTitle(\"%o%\");\r\n    blog.setStyle(\"black\");\r\n    List<Blog> blogList = mapper.selectBlogByCondition(blog);\r\n    sqlSession.close();\r\n    System.out.println(blogList);\r\n}<\/pre>\n
\"\"<\/p>\n
4.set<\/h3>\n
\u9700\u6c42\uff1a<\/p>\n
\u6309\u9700\u4fee\u6539\uff0c\u4fee\u6539\u6307\u5b9a\u7684\u5217\uff0c\u672a\u6307\u5b9a\u4e0d\u4fee\u6539<\/p>\n
mapper\uff1a<\/strong><\/p>\n
<update id=\"updateBlogByCondition\" parameterType=\"Blog\">\r\n    update blog\r\n    <set>\r\n        <if test=\"title !=null\">title=#{title},<\/if>\r\n        <if test=\"style !=null\">style=#{style},<\/if>\r\n        <if test=\"authorId !=null\">author_Id=#{authorId}<\/if>\r\n    <\/set>\r\n    where id=#{id}\r\n<\/update>\r\n\r\n\"\"\r\n<\/pre>\n
dao\uff1a<\/strong><\/p>\n
int updateBlogByCondition(Blog blog);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test16(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    Blog blog=new Blog();\r\n    blog.setId(1);\r\n    blog.setTitle(\"my blog\");\r\n    int i=mapper.updateBlogByCondition(blog);\r\n    sqlSession.commit();\r\n    sqlSession.close();\r\n    System.out.println(blog);\r\n}<\/pre>\n
\"\"<\/p>\n
5.trim<\/h3>\n
mapper\uff1a<\/strong><\/p>\n
<select id=\"selectBlogByConditionTrim\" parameterType=\"Blog\" resultMap=\"blogResultMap\">\r\n    select*from blog\r\n    <trim prefix=\"where\" prefixOverrides=\"and \/ or\">\r\n        <if test=\"state != null and state !='' \">\r\n            state=#{state}\r\n        <\/if>\r\n        <if test=\"title != null and title !='' \">\r\n            and lower(title) like lower(#{title})\r\n        <\/if>\r\n        <if test=\"featured != null\">\r\n            and featured = #{featured}\r\n        <\/if>\r\n    <\/trim>\r\n<\/select>\r\n<update id=\"updateBlogByConditionTrim\" parameterType=\"Blog\">\r\n    update blog\r\n    <trim prefix=\"sex\" suffixOverrides=\",\">\r\n        <if test=\"title !=null\">title=#{title},<\/if>\r\n        <if test=\"style !=null\">style=#{style},<\/if>\r\n        <if test=\"authorId !=null\">author_Id=#{authorId}<\/if>\r\n    <\/trim>\r\n    where id=#{id}\r\n<\/update><\/pre>\n
\"\"
\n\"\"
\ndao\uff1a<\/strong><\/p>\n
List<Blog> selectBlogByConditionTrim(Blog blog);\r\nint updateBlogByConditionTrim(Blog blog);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test17(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    Blog blog=new Blog();\r\n    blog.setTitle(\"%o%\");\r\n    blog.setStyle(\"black\");\r\n    List<Blog> blogList = mapper.selectBlogByConditionTrim(blog);\r\n    sqlSession.close();\r\n    System.out.println(blogList);\r\n}\r\n@Test\r\npublic void test18(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    Blog blog=new Blog();\r\n    blog.setId(1);\r\n    blog.setTitle(\"my blog\");\r\n    int i=mapper.updateBlogByConditionTrim(blog);\r\n    sqlSession.commit();\r\n    sqlSession.close();\r\n    System.out.println(blog);\r\n}<\/pre>\n
\"\"
\n\"\"<\/p>\n
6.foreach\u6279\u91cf\u5220\u9664<\/h3>\n
\u9700\u6c42\uff1a\u6279\u91cf\u5220\u9664<\/p>\n
mapper\uff1a<\/strong><\/p>\n
<delete id=\"deleteBlogList\" parameterType=\"list\">\r\n    delete from blog where id in\r\n    <foreach collection=\"list\" item=\"i\" open=\"(\" close=\")\" separator=\",\">\r\n        #{i}\r\n    <\/foreach>\r\n<\/delete><\/pre>\n
\"\"
\ndao\uff1a<\/strong><\/p>\n
int deleteBlogList(List<Integer> ids);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test19(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    List<Integer> ids= Arrays.asList(32,33,34);\r\n    int item = mapper.deleteBlogList(ids);\r\n    sqlSession.commit();\r\n    sqlSession.close();\r\n}<\/pre>\n
\"\"<\/p>\n
7.SQL\u7247\u6bb5<\/h3>\n
mapper\uff1a<\/strong><\/p>\n
<!-- sql\u7247\u6bb5 -->\r\n<sql id=\"columnBase\">\r\n    id,username,password,rolename,identity,mobile\r\n<\/sql>\r\n<!-- \u901a\u8fc7 id \u67e5\u8be2\u7528\u6237 2 -->\r\n<select id=\"selectUserBase\" resultType=\"Users\" parameterType=\"int\">\r\n    select\r\n    <include refid=\"columnBase\"><\/include>\r\n    from users where id = #{id}\r\n<\/select><\/pre>\n
\"\"
\ndao\uff1a<\/strong><\/p>\n
List<Users> selectUserBase(Integer id);<\/pre>\n
\u6d4b\u8bd5\uff1a<\/strong><\/p>\n
@Test\r\npublic void test20(){\r\n    SqlSession sqlSession = MybatisUtils.getSqlSession();\r\n    UsersDao mapper = sqlSession.getMapper(UsersDao.class);\r\n    List<Users> users = mapper.selectUserBase(2);\r\n    System.out.println(users);\r\n}<\/pre>\n
\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
2020java\u6846\u67b6\u6559\u7a0b\u4e4bmybatis\u52a8\u6001sql 1.if \u9700\u6c42\uff1a 1.\u67e5\u8be2\u5df2\u6fc0\u6d3b\u7684\uff0c\u5e76\u4e14\u535a\u5ba2\u7684\u540d\u5b57\u662f\u5305\u542b\u67d0 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-2790","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/posts\/2790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.9713job.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2790"}],"version-history":[{"count":6,"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/posts\/2790\/revisions"}],"predecessor-version":[{"id":2796,"href":"https:\/\/www.9713job.com\/index.php?rest_route=\/wp\/v2\/posts\/2790\/revisions\/2796"}],"wp:attachment":[{"href":"https:\/\/www.9713job.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.9713job.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2790"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.9713job.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}