mybatis学习(38):动态sql-foreach

mybatis学习(38):动态sql-foreach

目录结构

com.geyao.mybatis.mapperBlogMapper类package com.geyao.mybatis.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import com.geyao.mybatis.pojo.Blog; public interface BlogMapper { Blog selectBlog(Integer id); Blog selectBlog2(Integer id); List selectBlogByTitle(String title); List selectBlogByTitle2(String title); List selectBlogBySort(String column); List selectBlogByPage(int offset,int pagesize); List selectBlogByPage1(@Param(value="offset")int offset,@Param(value="pagesize")int pagesize); List selectBlogByPage2(Mapmap); int insertBlog(Blog blog); int insertBlogMysql(Blog blog); int updateBlog(Blog blog); int deleteBlogById(Integer id); List selectActiveBlogByTitle(String title); List selectActiveBlogByTitleOrStyle(Blog blog); List selectBlogByCondition(Blog blog); } BlogMapper.xml select id, title, authod_id as authodId, state, featured, style from Blog where id=#{id} select * from Blog where id=#{id} select * from Blog where title like #{title} select * from Blog where title like '${value}' select * from Blog order by ${value} select * from Blog limit #{0},#{1} select * from Blog limit #{offset},#{pagesize} select * from Blog limit #{offset},#{pagesize} INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) select seq.nextval as id from dual INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) SELECT LAST_INSERT_ID() INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) UPDATE Blog SET title = #{title}, authod_id = #{authodId}, state = #{state}, featured = #{featured}, style= #{ style} WHERE id = #{id} delete from blog where id =#{id} SELECT * FROM blog where state ='ACTIVE' AND title LIKE '%o%' SELECT * FROM blog where state ='ACTIVE' and lower(title) like lower(#{title}) and featured=true; select * from blog state=#{state} and lower(title) like lower(#{title}) and featured = #{featured} com.geyao.mybatis.pojoBlog类package com.geyao.mybatis.pojo; public class Blog { private Integer id; private String title; private int authodId; private String state; private Boolean featured; private String style; public Blog() { super(); this.title="未命名"; this.authodId=4; this.state="NOT"; this.featured=false; this.style="red"; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public int getAuthodId() { return authodId; } public void setAuthodId(int authodId) { this.authodId = authodId; } public String getState() { return state; } public void setState(String state) { this.state = state; } public Boolean getFeatured() { return featured; } public void setFeatured(Boolean featured) { this.featured = featured; } public String getStyle() { return style; } public void setStyle(String style) { this.style = style; } @Override public String toString() { return "Blog [id=" + id + ", title=" + title + ", authodId=" + authodId + ", state=" + state + ", featured=" + featured + ", style=" + style + "]\n"; } } com.geyao.mybatis.utilMybatisUtil类package com.geyao.mybatis.util; import java.io.InputStream; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory =null; static { try { InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } private MyBatisUtil() {} public static SqlSession getSqlSession() { return sqlSessionFactory.openSession(); } } log4j.properties### \u914D\u7F6E\u6839 ### log4j.rootLogger = debug,console ,fileAppender,dailyRollingFile,ROLLING_FILE,MAIL,DATABASE ### \u8BBE\u7F6E\u8F93\u51FAsql\u7684\u7EA7\u522B\uFF0C\u5176\u4E2Dlogger\u540E\u9762\u7684\u5185\u5BB9\u5168\u90E8\u4E3Ajar\u5305\u4E2D\u6240\u5305\u542B\u7684\u5305\u540D ### log4j.logger.org.apache=dubug log4j.logger.java.sql.Connection=dubug log4j.logger.java.sql.Statement=dubug log4j.logger.java.sql.PreparedStatement=dubug log4j.logger.java.sql.ResultSet=dubug ### \u914D\u7F6E\u8F93\u51FA\u5230\u63A7\u5236\u53F0 ### log4j.appender.console = org.apache.log4j.ConsoleAppender log4j.appender.console.Target = System.out log4j.appender.console.layout = org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern = %d{ABSOLUTE} %5p %c{1}:%L - %m%n mybatis-config.xml 单元测试com.geyao.mybatis.utiltestSelectBlog类package com.geyao.mybatis.mapper; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import com.geyao.mybatis.pojo.Blog; import com.geyao.mybatis.util.MyBatisUtil; public class testSelectBlog { @Test public void testSelectBlogNoInterface() { SqlSession session =MyBatisUtil.getSqlSession(); Blog blog =(Blog)session.selectOne("com.geyao.mybatis.mapper.BlogMapper.selectBlog", 101); session.close(); System.out.println(blog); } @Test public void testSelectBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = blogMapper.selectBlog(1); System.out.println(blog); } @Test public void testSelectBlog2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = blogMapper.selectBlog2(1); System.out.println(blog); } @Test public void testselectBlogByTitle() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByTitle("%g%"); System.out.println(blogList); } @Test public void testselectBlogByTitle2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByTitle2("%g%"); System.out.println(blogList); } @Test public void testselectBlogBySort() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogBySort("title"); System.out.println(blogList); } @Test public void testselectBlogByPage() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByPage(2,2); System.out.println(blogList); } @Test public void testselectBlogByPage1() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByPage1(2,2); System.out.println(blogList); } @Test public void testselectBlogByPage2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Map map =new HashMap(); map.put("offset", 2); map.put("pagesize", 2); List blogList = blogMapper.selectBlogByPage2(map); System.out.println(blogList); } @Test public void testInsertBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog=new Blog(); int count= blogMapper.insertBlog(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("插入的"+count+"记录"); } @Test public void testinsertBlogMysql() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog=new Blog(); int count= blogMapper.insertBlogMysql(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("插入的"+count+"记录"); } @Test public void testupdateBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog=new Blog(); blog.setId(1); blog.setTitle("geyao"); blog.setStyle("balck"); blog.setState("active"); blog.setFeatured(false); blog.setAuthodId(2); int count= blogMapper.updateBlog(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("修改"+count+"记录"); } }

jar包

链接：提取码：4r2m

运行结果

目录结构

com.geyao.mybatis.mapperBlogMapper类package com.geyao.mybatis.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import com.geyao.mybatis.pojo.Blog; public interface BlogMapper { Blog selectBlog(Integer id); Blog selectBlog2(Integer id); List selectBlogByTitle(String title); List selectBlogByTitle2(String title); List selectBlogBySort(String column); List selectBlogByPage(int offset,int pagesize); List selectBlogByPage1(@Param(value="offset")int offset,@Param(value="pagesize")int pagesize); List selectBlogByPage2(Mapmap); int insertBlog(Blog blog); int insertBlogMysql(Blog blog); int updateBlog(Blog blog); } BlogMapper.xml select id, title, authod_id as authodId, state, featured, style from Blog where id=#{id} select * from Blog where id=#{id} select * from Blog where title like #{title} select * from Blog where title like '${value}' select * from Blog order by ${value} select * from Blog limit #{0},#{1} select * from Blog limit #{offset},#{pagesize} select * from Blog limit #{offset},#{pagesize} INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) select seq.nextval as id from dual INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) SELECT LAST_INSERT_ID() INSERT INTO Blog( title, authod_id, state, featured, style ) VALUES( #{title}, #{authodId}, #{state}, #{featured}, #{style} ) UPDATE Blog SET title = #{title}, authod_id = #{authodId}, state = #{state}, featured = #{featured}, style= #{ style} WHERE id = #{id} com.geyao.mybatis.pojoBlog类package com.geyao.mybatis.pojo; public class Blog { private Integer id; private String title; private int authodId; private String state; private Boolean featured; private String style; public Blog() { super(); this.title="未命名"; this.authodId=4; this.state="NOT"; this.featured=false; this.style="red"; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public int getAuthodId() { return authodId; } public void setAuthodId(int authodId) { this.authodId = authodId; } public String getState() { return state; } public void setState(String state) { this.state = state; } public Boolean getFeatured() { return featured; } public void setFeatured(Boolean featured) { this.featured = featured; } public String getStyle() { return style; } public void setStyle(String style) { this.style = style; } @Override public String toString() { return "Blog [id=" + id + ", title=" + title + ", authodId=" + authodId + ", state=" + state + ", featured=" + featured + ", style=" + style + "]\n"; } } com.geyao.mybatis.utilMybatisUtil类package com.geyao.mybatis.util; import java.io.InputStream; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisUtil { private static SqlSessionFactory sqlSessionFactory =null; static { try { InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } private MyBatisUtil() {} public static SqlSession getSqlSession() { return sqlSessionFactory.openSession(); } } log4j.properties### \u914D\u7F6E\u6839 ### log4j.rootLogger = debug,console ,fileAppender,dailyRollingFile,ROLLING_FILE,MAIL,DATABASE ### \u8BBE\u7F6E\u8F93\u51FAsql\u7684\u7EA7\u522B\uFF0C\u5176\u4E2Dlogger\u540E\u9762\u7684\u5185\u5BB9\u5168\u90E8\u4E3Ajar\u5305\u4E2D\u6240\u5305\u542B\u7684\u5305\u540D ### log4j.logger.org.apache=dubug log4j.logger.java.sql.Connection=dubug log4j.logger.java.sql.Statement=dubug log4j.logger.java.sql.PreparedStatement=dubug log4j.logger.java.sql.ResultSet=dubug ### \u914D\u7F6E\u8F93\u51FA\u5230\u63A7\u5236\u53F0 ### log4j.appender.console = org.apache.log4j.ConsoleAppender log4j.appender.console.Target = System.out log4j.appender.console.layout = org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern = %d{ABSOLUTE} %5p %c{1}:%L - %m%n mybatis-config.xml 单元测试com.geyao.mybatis.utiltestSelectBlog类package com.geyao.mybatis.mapper; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import com.geyao.mybatis.pojo.Blog; import com.geyao.mybatis.util.MyBatisUtil; public class testSelectBlog { @Test public void testSelectBlogNoInterface() { SqlSession session =MyBatisUtil.getSqlSession(); Blog blog =(Blog)session.selectOne("com.geyao.mybatis.mapper.BlogMapper.selectBlog", 101); session.close(); System.out.println(blog); } @Test public void testSelectBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = blogMapper.selectBlog(1); System.out.println(blog); } @Test public void testSelectBlog2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = blogMapper.selectBlog2(1); System.out.println(blog); } @Test public void testselectBlogByTitle() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByTitle("%g%"); System.out.println(blogList); } @Test public void testselectBlogByTitle2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByTitle2("%g%"); System.out.println(blogList); } @Test public void testselectBlogBySort() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogBySort("title"); System.out.println(blogList); } @Test public void testselectBlogByPage() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByPage(2,2); System.out.println(blogList); } @Test public void testselectBlogByPage1() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList = blogMapper.selectBlogByPage1(2,2); System.out.println(blogList); } @Test public void testselectBlogByPage2() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Map map =new HashMap(); map.put("offset", 2); map.put("pagesize", 2); List blogList = blogMapper.selectBlogByPage2(map); System.out.println(blogList); } @Test public void testInsertBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog=new Blog(); int count= blogMapper.insertBlog(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("插入的"+count+"记录"); } @Test public void testinsertBlogMysql() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog=new Blog(); int count= blogMapper.insertBlogMysql(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("插入的"+count+"记录"); } @Test public void testupdateBlog() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = blogMapper.selectBlog(1); //Blog blog=new Blog(); blog.setId(1); blog.setTitle("geyao"); blog.setAuthodId(3); //blog.setStyle("balck"); //blog.setState("active"); //blog.setFeatured(false); //blog.setAuthodId(2); int count= blogMapper.updateBlog(blog); session.commit(); session.close(); System.out.println(blog); System.out.println("修改"+count+"记录"); } @Test public void testdeleteBlogById() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); int count= blogMapper.deleteBlogById(3); session.commit(); session.close(); System.out.println("删除的"+count+"记录"); } @Test public void testselectActiveBlogByTitle() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); List blogList=blogMapper.selectActiveBlogByTitle("o"); session.close(); System.out.println(blogList); } @Test public void testselectActiveBlogByTitleOrStyle() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setTitle("%o%"); blog.setStyle("black"); List blogList=blogMapper.selectActiveBlogByTitleOrStyle(blog); session.close(); System.out.println(blogList); } @Test public void testselectBlogByCondition() { SqlSession session =MyBatisUtil.getSqlSession(); BlogMapper blogMapper =session.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setTitle("%o%"); List blogList=blogMapper.selectBlogByCondition(blog); session.close(); System.out.println(blogList); } }

jar包

链接：提取码：4r2m

运行结果

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。