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

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

2022-08-09 78
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 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
SQL foreach
MyBatis学习
MyBatis动态
MyBatis sql-foreach
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
前端歌谣
目录
相关文章
蓝染-惣右介
|
9天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作，以及通过图形化工具DataGrip进行数据库管理和查询。此外，还讲解了数据的增、删、改、查操作，以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
蓝染-惣右介
24 1
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
热爱技术的小郑
|
5月前
|
SQL Java 测试技术
3、Mybatis-Plus 自定义sql语句
这篇文章介绍了如何在Mybatis-Plus框架中使用自定义SQL语句进行数据库操作。内容包括文档结构、编写mapper文件、mapper.xml文件的解释说明、在mapper接口中定义方法、在mapper.xml文件中实现接口方法的SQL语句，以及如何在单元测试中测试自定义的SQL语句，并展示了测试结果。
热爱技术的小郑
312 3
3、Mybatis-Plus 自定义sql语句
蓝易云
|
15天前
|
SQL XML Java
mybatis实现动态sql
MyBatis的动态SQL功能为开发人员提供了强大的工具来应对复杂的查询需求。通过使用 `<if>`、`<choose>`、`<foreach>`等标签，可以根据不同的条件动态生成SQL语句，从而提高代码的灵活性和可维护性。本文详细介绍了动态SQL的基本用法和实际应用示例，希望对您在实际项目中使用MyBatis有所帮助。
蓝易云
46 11
蓝染-惣右介
|
2月前
|
SQL 缓存 Java
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
本文详细介绍了MyBatis的各种常见用法MyBatis多级缓存、逆向工程、分页插件 包括获取参数值和结果的各种情况、自定义映射resultMap、动态SQL
蓝染-惣右介
140 1
【详细实用のMyBatis教程】获取参数值和结果的各种情况、自定义映射、动态SQL、多级缓存、逆向工程、分页插件
蓝易云
|
2月前
|
SQL 安全 前端开发
Web学习_SQL注入_联合查询注入
联合查询注入是一种强大的SQL注入攻击方式，攻击者可以通过 `UNION`语句合并多个查询的结果，从而获取敏感信息。防御SQL注入需要多层次的措施，包括使用预处理语句和参数化查询、输入验证和过滤、最小权限原则、隐藏错误信息以及使用Web应用防火墙。通过这些措施，可以有效地提高Web应用程序的安全性，防止SQL注入攻击。
蓝易云
64 2
java冯坚持
|
3月前
|
SQL Java 数据库连接
mybatis使用四：dao接口参数与mapper 接口中SQL的对应和对应方式的总结，MyBatis的parameterType传入参数类型
这篇文章是关于MyBatis中DAO接口参数与Mapper接口中SQL的对应关系，以及如何使用parameterType传入参数类型的详细总结。
java冯坚持
61 10
杀死一只知更鸟debug
|
4月前
|
SQL XML Java
mybatis复习03，动态SQL，if，choose，where,set,trim标签及foreach标签的用法
文章介绍了MyBatis中动态SQL的用法，包括if、choose、where、set和trim标签，以及foreach标签的详细使用。通过实际代码示例，展示了如何根据条件动态构建查询、更新和批量插入操作的SQL语句。
杀死一只知更鸟debug
113 2
mybatis复习03，动态SQL，if，choose，where,set,trim标签及foreach标签的用法
java冯坚持
|
3月前
|
Java 关系型数据库 MySQL
springboot学习五：springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库，并进行基本的增删改查操作的教程。
java冯坚持
333 0
springboot学习五：springboot整合Mybatis 连接 mysql数据库
java冯坚持
|
3月前
|
SQL 存储 数据库
SQL学习一：ACID四个特性，CURD基本操作，常用关键字，常用聚合函数，五个约束，综合题
这篇文章是关于SQL基础知识的全面介绍，包括ACID特性、CURD操作、常用关键字、聚合函数、约束以及索引的创建和使用，并通过综合题目来巩固学习。
java冯坚持
64 1
李Muzi
|
4月前
|
Java 关系型数据库 数据库连接
mybatis-plus学习
MyBatis-Plus ，MyBatis 最佳搭档，只做增强不做改变，为简化开发、提高效率而生。
李Muzi
52 5

热门文章

最新文章

  • 1
    Flask 框架防止 SQL 注入攻击的方法
  • 2
    Vanna：开源 AI 检索生成框架，自动生成精确的 SQL 查询
  • 3
    SQL自学笔记（3）：SQL里的DCL,DQL都代表什么？
  • 4
    Flink SQL Deduplication 去重以及如何获取最新状态操作
  • 5
    MySQL导入.sql文件后数据库乱码问题
  • 6
    MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
  • 7
    MySQL 高级(进阶) SQL 语句
  • 8
    MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
  • 9
    SQL自学笔记（1）：什么是SQL？有什么用？
  • 10
    SQL慢查询优化策略
  • 1
    Mybatis逆向工程的2种方法，一键高效快速生成Pojo、Mapper、XML，摆脱大量重复开发
    89
  • 2
    mybatis的一对多
    50
  • 3
    mybatis动态sql
    48
  • 4
    【JAVA进阶篇教学】第九篇：MyBatis-Plus用法介绍
    283
  • 5
    解决一个mybatisplus left join里有ur报错问题
    81
  • 6
    java mysql druid mybatis-plus里使用多表删除出错的一种处理方式
    108
  • 7
    nbcio-boot升级springboot、mybatis-plus和JSQLParser后的LocalDateTime日期json问题
    98
  • 8
    基于jeecg-boot的nbcio-boot因升级mybatis-plus到3.5.3.1和JSQLParser 到4.6而引起的在线报表配置报错处理
    195
  • 9
    升级mybatis-plus到3.5.3.1和JSQLParser 从4.3升级到4.6版本引起的插入问题解决
    645
  • 10
    MyBatis #与$的区别以及动态SQL
    53

    • 相关课程

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

    • 相关电子书

    更多
  • MaxCompute SQL 2.0 全新的计算引擎
  • SQL Server云化思考与实践
  • Java Spring Boot开发实战系列课程【第6讲】：Spring Boot 2.0实战MyBatis与优化(Java面试题)

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月