AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2022-08-09 182
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
云数据库 RDS PostgreSQL，高可用系列 2核4GB
简介： mybatis学习(38):动态sql-foreach

image.pngimage.png

com.geyao.mybatis.mapper
BlogMapper类
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<Blog> selectBlogByTitle(String title);
     List<Blog> selectBlogByTitle2(String title);
     List<Blog> selectBlogBySort(String column);
     List<Blog> selectBlogByPage(int offset,int pagesize);
     List<Blog> selectBlogByPage1(@Param(value="offset")int offset,@Param(value="pagesize")int pagesize);
     List<Blog> selectBlogByPage2(Map<String, Object>map);
     int insertBlog(Blog blog);
     int insertBlogMysql(Blog blog);
     int updateBlog(Blog blog);
     int deleteBlogById(Integer id);
     List<Blog> selectActiveBlogByTitle(String title);
     List<Blog> selectActiveBlogByTitleOrStyle(Blog blog);
     List<Blog> selectBlogByCondition(Blog blog);
 }
 BlogMapper.xml<?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- 为这个mapper指定一个唯一的namespace，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
 例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
  -->
 <mapper namespace="com.geyao.mybatis.mapper.BlogMapper">
     <!-- 在select标签中编写查询的SQL语句， 设置select标签的id属性为getUser，id属性值必须是唯一的，不能够重复
     使用parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型
     resultType="me.gacl.domain.User"就表示将查询结果封装成一个User类的对象返回
     User类就是users表所对应的实体类
     -->
     <!-- 
         根据id查询得到一个user对象
      -->
      <resultMap type="Blog" id="blogResultMap">
      <id column="id" property="id" jdbcType="INTEGER"></id>
          <result column="authod_id" property="authodId" jdbcType="INTEGER"/>
      </resultMap>
      <!-- crud -->
     <select id="selectBlog" parameterType="int"   resultType="Blog">
         select 
         id,
         title,
         authod_id as authodId,
         state,
         featured,
         style
          from Blog where id=#{id}
     </select> 
     <select id="selectBlog2" parameterType="int"  resultMap="blogResultMap">
           select *
          from Blog where id=#{id}
     </select>
     <select id="selectBlogByTitle" parameterType="String" resultMap="blogResultMap">
         select * from Blog where title like #{title}
     </select>
     <select id="selectBlogByTitle2" parameterType="String" resultMap="blogResultMap">
         select * from Blog where title like '${value}'
     </select>
      <select id="selectBlogBySort" parameterType="String" resultMap="blogResultMap">
         select * from Blog order by ${value}
     </select>
       <select id="selectBlogByPage"  resultMap="blogResultMap">
         select * from Blog limit #{0},#{1}
     </select>
      <select id="selectBlogByPage1"  resultMap="blogResultMap">
         select * from Blog limit #{offset},#{pagesize}
     </select>
      <select id="selectBlogByPage2"  resultMap="blogResultMap">
         select * from Blog limit #{offset},#{pagesize}
     </select>
      <insert id="insertBlog" parameterType="Blog" useGeneratedKeys="true" keyProperty="id">
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
      <insert id="insertBlogOracle" parameterType="Blog" >
      <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
          select seq.nextval as id from dual
      </selectKey>
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
      <insert id="insertBlogMysql" parameterType="Blog" >
      <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
          SELECT LAST_INSERT_ID()
      </selectKey>
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
     <update id="updateBlog" parameterType="Blog" >
     UPDATE 
     Blog
     SET
     title = #{title},
    authod_id = #{authodId},
     state = #{state},
     featured = #{featured},
 style= #{ style}
     WHERE id = #{id}
     </update>
     <delete id="deleteBlogById" parameterType="int">
         delete from blog where id =#{id}
     </delete>
     <!-- 动态sql -->
     <select id="selectActiveBlogByTitle" parameterType="String" resultMap="blogResultMap">
         SELECT * FROM blog
         where state ='ACTIVE'
         <if test="value!=null and value !=''">
         AND title LIKE '%o%'
         </if>
     </select>
     <select id="selectActiveBlogByTitleOrStyle" parameterType="Blog" resultMap="blogResultMap">
     SELECT * FROM blog
         where state ='ACTIVE'
         <choose>
         <when test="title !=null and title !=''">
             and lower(title) like lower(#{title})
         </when>
         <when test="style !=null and style!=''">
         </when>
         <otherwise>
         and featured=true;
         </otherwise>
         </choose>
     </select>
     <select id="selectBlogByCondition" parameterType="Blog" resultMap="blogResultMap">
     select * from blog
     <where>
     <if test="state !=null and state !=''">
     state=#{state}
     </if>
     <if test="title !=null and title !=''">
     and lower(title) like lower(#{title})
     </if>
     <if test=" featured !=null">
    and featured = #{featured}
     </if>
     </where>
     </select>
 </mapper>
 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<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
 <typeAliases>
     <typeAlias type="com.geyao.mybatis.pojo.Blog" alias="Blog"/>
 </typeAliases>
     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC" />
             <!-- 配置数据库连接信息 -->
             <dataSource type="POOLED">
                 <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8" />
                 <property name="username" value="root" />
                 <property name="password" value="123" />
             </dataSource>
         </environment>
     </environments>
       <mappers>
         <!-- 注册userMapper.xml文件， 
          userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml-->
          <mapper resource="com/geyao/mybatis/mapper/BlogMapper.xml"/>
      </mappers>
 </configuration>
 单元测试com.geyao.mybatis.util
testSelectBlog类
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<Blog> blogList = blogMapper.selectBlogByTitle("%g%");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByTitle2() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByTitle2("%g%");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogBySort() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogBySort("title");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByPage(2,2);
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage1() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByPage1(2,2);
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage2() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     Map<String, Object> map =new HashMap<String, Object>();
     map.put("offset", 2);
     map.put("pagesize", 2);
     List<Blog> 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+"记录");
 }
 }

image.png

com.geyao.mybatis.mapper
BlogMapper类
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<Blog> selectBlogByTitle(String title);
     List<Blog> selectBlogByTitle2(String title);
     List<Blog> selectBlogBySort(String column);
     List<Blog> selectBlogByPage(int offset,int pagesize);
     List<Blog> selectBlogByPage1(@Param(value="offset")int offset,@Param(value="pagesize")int pagesize);
     List<Blog> selectBlogByPage2(Map<String, Object>map);
     int insertBlog(Blog blog);
     int insertBlogMysql(Blog blog);
     int updateBlog(Blog blog);
 }
 BlogMapper.xml<?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- 为这个mapper指定一个唯一的namespace，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的
 例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀)
  -->
 <mapper namespace="com.geyao.mybatis.mapper.BlogMapper">
     <!-- 在select标签中编写查询的SQL语句， 设置select标签的id属性为getUser，id属性值必须是唯一的，不能够重复
     使用parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型
     resultType="me.gacl.domain.User"就表示将查询结果封装成一个User类的对象返回
     User类就是users表所对应的实体类
     -->
     <!-- 
         根据id查询得到一个user对象
      -->
      <resultMap type="Blog" id="blogResultMap">
      <id column="id" property="id" jdbcType="INTEGER"></id>
          <result column="authod_id" property="authodId" jdbcType="INTEGER"/>
      </resultMap>
     <select id="selectBlog" parameterType="int"   resultType="Blog">
         select 
         id,
         title,
         authod_id as authodId,
         state,
         featured,
         style
          from Blog where id=#{id}
     </select> 
     <select id="selectBlog2" parameterType="int"  resultMap="blogResultMap">
           select *
          from Blog where id=#{id}
     </select>
     <select id="selectBlogByTitle" parameterType="String" resultMap="blogResultMap">
         select * from Blog where title like #{title}
     </select>
     <select id="selectBlogByTitle2" parameterType="String" resultMap="blogResultMap">
         select * from Blog where title like '${value}'
     </select>
      <select id="selectBlogBySort" parameterType="String" resultMap="blogResultMap">
         select * from Blog order by ${value}
     </select>
       <select id="selectBlogByPage"  resultMap="blogResultMap">
         select * from Blog limit #{0},#{1}
     </select>
      <select id="selectBlogByPage1"  resultMap="blogResultMap">
         select * from Blog limit #{offset},#{pagesize}
     </select>
      <select id="selectBlogByPage2"  resultMap="blogResultMap">
         select * from Blog limit #{offset},#{pagesize}
     </select>
      <insert id="insertBlog" parameterType="Blog" useGeneratedKeys="true" keyProperty="id">
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
      <insert id="insertBlogOracle" parameterType="Blog" >
      <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
          select seq.nextval as id from dual
      </selectKey>
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
      <insert id="insertBlogMysql" parameterType="Blog" >
      <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
          SELECT LAST_INSERT_ID()
      </selectKey>
         INSERT INTO Blog(
         title,
         authod_id,
         state,
         featured,
         style
         )
         VALUES(
         #{title},
         #{authodId},
         #{state},
         #{featured},
         #{style}
         )
     </insert>
     <update id="updateBlog" parameterType="Blog" >
     UPDATE 
     Blog
     SET
     title = #{title},
    authod_id = #{authodId},
     state = #{state},
     featured = #{featured},
 style= #{ style}
     WHERE id = #{id}
     </update>
 </mapper>
 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<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
 <typeAliases>
     <typeAlias type="com.geyao.mybatis.pojo.Blog" alias="Blog"/>
 </typeAliases>
     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC" />
             <!-- 配置数据库连接信息 -->
             <dataSource type="POOLED">
                 <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8" />
                 <property name="username" value="root" />
                 <property name="password" value="123" />
             </dataSource>
         </environment>
     </environments>
       <mappers>
         <!-- 注册userMapper.xml文件， 
          userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml-->
          <mapper resource="com/geyao/mybatis/mapper/BlogMapper.xml"/>
      </mappers>
 </configuration>
 单元测试com.geyao.mybatis.util
testSelectBlog类
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<Blog> blogList = blogMapper.selectBlogByTitle("%g%");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByTitle2() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByTitle2("%g%");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogBySort() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogBySort("title");
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByPage(2,2);
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage1() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     List<Blog> blogList = blogMapper.selectBlogByPage1(2,2);
     System.out.println(blogList);
 }
 @Test
 public void testselectBlogByPage2() {
     SqlSession session =MyBatisUtil.getSqlSession();
     BlogMapper blogMapper =session.getMapper(BlogMapper.class);
     Map<String, Object> map =new HashMap<String, Object>();
     map.put("offset", 2);
     map.put("pagesize", 2);
     List<Blog> 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<Blog> 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<Blog> 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<Blog> blogList=blogMapper.selectBlogByCondition(blog);
     session.close();
     System.out.println(blogList);
 }
 }

image.png

文章标签：
云数据库 RDS MySQL 版
Java
mybatis
数据库连接
关键词：
MyBatis sql
MyBatis学习
SQL foreach
MyBatis动态
MyBatis sql-foreach
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
前端歌谣
目录
相关文章
蓝易云
|
3月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言，通过MyBatis的XML配置实现灵活的动态SQL查询，可以让开发者以声明式的方式构建SQL语句，既保证了SQL操作的灵活性，又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
蓝易云
279 18
游客kufrkwrbkmpsa
|
8月前
|
SQL Java 数据库连接
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
游客kufrkwrbkmpsa
167 1
wanderInk
|
7月前
|
SQL Java 数据库连接
MyBatis动态SQL字符串空值判断，这个细节99%的程序员都踩过坑！
本文深入探讨了MyBatis动态SQL中字符串参数判空的常见问题。通过具体案例分析，对比了`name != null and name != &#39;&#39;`与`name != null and name != &#39; &#39;`两种写法的差异，指出后者可能引发逻辑混乱。为避免此类问题，建议在后端对参数进行预处理（如trim去空格），简化MyBatis判断逻辑，提升代码健壮性与可维护性。细节决定成败，严谨处理参数判空是写出高质量代码的关键。
wanderInk
1022 0
Floweroath
|
3月前
|
SQL Java 数据库连接
SSM相关问题-1--#{}和${}有什么区别吗？--Mybatis都有哪些动态sql？能简述一下动 态sql的执行原理吗？--Spring支持的几种bean的作用域 Scope
在MyBatis中，`#{}`是预处理占位符，可防止SQL注入，适用于大多数参数传递场景；而`${}`是直接字符串替换，不安全，仅用于动态表名、列名等特殊场景。二者在安全性、性能及使用场景上有显著区别。
Floweroath
81 0
Bluening
|
6月前
|
SQL XML Java
菜鸟之路Day35一一Mybatis之XML映射与动态SQL
本文介绍了MyBatis框架中XML映射与动态SQL的使用方法，作者通过实例详细解析了XML映射文件的配置规范，包括namespace、id和resultType的设置。文章还对比了注解与XML映射的优缺点，强调复杂SQL更适合XML方式。在动态SQL部分，重点讲解了`&lt;if&gt;`、`&lt;where&gt;`、`&lt;set&gt;`、`&lt;foreach&gt;`等标签的应用场景，如条件查询、动态更新和批量删除，并通过代码示例展示了其灵活性与实用性。最后，通过`&lt;sql&gt;`和`&lt;include&gt;`实现代码复用，优化维护效率。
Bluening
580 5
蓝染-惣右介
|
11月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作，以及通过图形化工具DataGrip进行数据库管理和查询。此外，还讲解了数据的增、删、改、查操作，以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
蓝染-惣右介
1021 55
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
游客pb5c4iyea3ani
|
8月前
|
SQL Java 数据库连接
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
游客pb5c4iyea3ani
254 6
拉丁解牛说技术
|
8月前
|
SQL 缓存 Java
框架源码私享笔记(02)Mybatis核心框架原理 | 一条SQL透析核心组件功能特性
本文详细解构了MyBatis的工作机制，包括解析配置、创建连接、执行SQL、结果封装和关闭连接等步骤。文章还介绍了MyBatis的五大核心功能特性：支持动态SQL、缓存机制（一级和二级缓存）、插件扩展、延迟加载和SQL注解，帮助读者深入了解其高效灵活的设计理念。
拉丁解牛说技术
288 1
刘大猫.
|
9月前
|
SQL XML Java
九、MyBatis动态SQL
九、MyBatis动态SQL
刘大猫.
157 2
潜意识Java
|
10月前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能，涵盖其背景、应用场景及实现方式。
潜意识Java
1099 6

热门文章

最新文章

  • 1
    Spring boot 使用mybatis generator 自动生成代码插件
  • 2
    MyBatis - Mybatis Generator Maven 插件自动生成代码
  • 3
    Mybatis-plus查询表中指定字段(不查询全部字段)
  • 4
    MyBatis-Spring配置简单了解
  • 5
    一文彻底搞懂Mybatis系列（一）之mybatis入门
  • 6
    MyBatis学习之简单增删改查操作、MyBatis存储过程、MyBatis分页、MyBatis一对一、MyBatis一对多
  • 7
    MyBatis系列目录--2. Mybatis的简单CRUD(含操作视频)
  • 8
    Mybatis第二篇| CRUD，这才是程序员入门第一课
  • 9
    【BackEnd】SpringBoot整合MybatisPlus实现登录注册功能（适合初学者）
  • 10
    spring+springMVC+mybatis的整合 part10
  • 1
    CTE vs 子查询：深入拆解PostgreSQL复杂SQL的隐藏性能差异
    564
  • 2
    GitHub 热门！MindsDB 破解 AI + 数据库瓶颈，究竟有什么惊艳亮点？只需 SQL 即可实现智能预测
    985
  • 3
    什么！我把SQL编辑器装进了大模型？
    997
  • 4
    实现MySQL与SQL Server之间数据迁移的有效方法
    509
  • 5
    菜鸟之路Day35一一Mybatis之XML映射与动态SQL
    580
  • 6
    SQL Server 2025 - 从本地到云端的 AI 就绪企业数据库
    467
  • 7
    狂揽20.2k星！还在傻傻的写SQL吗，那你就完了！这款开源项目，让数据分析像聊天一样简单？再见吧SQL
    336
  • 8
    【SQL周周练】：利用行车轨迹分析犯罪分子作案地点
    207
  • 9
    SQL在线美化工具
    621
  • 10
    Dataphin V5.0：支持创建异步调用API，实现慢 SQL 复杂计算的直连消费
    274

    • 相关课程

    更多
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询
  • MyBatis持久层框架入门

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • Java Spring Boot开发实战系列课程【第6讲】：Spring Boot 2.0实战MyBatis与优化(Java面试题)
    • 下一篇
    阿里云弹性公网IP线路类型【BGP(多线)_精品】是什么意思？