Mybatis 续中续2

注解CRUD

可以在使用工具类的时候实现自动提交事务！

自动提交事务

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package org.apache.ibatis.session;

import java.sql.Connection;

public interface SqlSessionFactory {
    SqlSession openSession();

    SqlSession openSession(boolean var1); // 设置自动提交事务

    SqlSession openSession(Connection var1);

    SqlSession openSession(TransactionIsolationLevel var1);

    SqlSession openSession(ExecutorType var1);

    SqlSession openSession(ExecutorType var1, boolean var2);

    SqlSession openSession(ExecutorType var1, TransactionIsolationLevel var2);

    SqlSession openSession(ExecutorType var1, Connection var2);

    Configuration getConfiguration();
}




/**
     * 既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
     * SqlSession 提供了在数据库执行 SQL 命令所需的所有方法
     */
    public static SqlSession getsqlSession(){

        //return sqlSessionFactory.openSession();
        // true  设置自动提交事务
        return sqlSessionFactory.openSession(true);

    }

select

/**
     * 如果多个基本类型参数 @Param("name") 必须加注解
     *  #{id} 参数名 对应 @Param("id") 中的字段名
     * BlogAdmin getBlogById(@Param("id")int id,@Param("name") String name);
     * @param id
     * @return
     */
    @Select("select * from blog_admin where id = #{id}")
    BlogAdmin getBlogById(@Param("id")int id);


 SqlSession sqlSession = MybatisUtils.getsqlSession();

       BlogAdminMapper mapper = sqlSession.getMapper(BlogAdminMapper.class);

        //List<BlogAdmin> blogAdmins = mapper.getBlogAdmin();
        //for (BlogAdmin blogAdmin : blogAdmins) {
        //    System.out.println(blogAdmin);
        //}
        BlogAdmin admin = mapper.getBlogById(1);
        System.out.println(admin);
       sqlSession.close();

insert

/**
     * 引用对象不需要 @Param("name")
     * @param admin
     * @return
     */
    @Insert("insert into blog_admin (id,name,password) value (#{id},#{name},#{password})")
    int addBlogAdmin(BlogAdmin admin);



 @Test
    public void Test(){
       SqlSession sqlSession = MybatisUtils.getsqlSession();

       BlogAdminMapper mapper = sqlSession.getMapper(BlogAdminMapper.class);

        /*
        //查询所有
        List<BlogAdmin> blogAdmins = mapper.getBlogAdmin();
        for (BlogAdmin blogAdmin : blogAdmins) {
            System.out.println(blogAdmin);
        }
        //根据id查询
        BlogAdmin admin = mapper.getBlogById(1);
        System.out.println(admin);*/
        mapper.addBlogAdmin(new BlogAdmin(5,"admin1","123456"));
        //设置了自动提交事务，不需要再手动进行事务提交
        // sqlSession.commit();
       sqlSession.close();
    }

update

@Update("update blog_admin set  name = #{name},password=#{password} where id=#{id}")
int updateBlog(BlogAdmin admin);

注意：需要在Mybatis核心配置文件中绑定接口

<!-- 绑定接口  -->
    <mappers>
        <mapper class="com.depressiom.dao.BlogAdminMapper"/>
        <!--   通配导入resource  *.xml   -->
        <mapper resource="com/depressiom/dao/*.xml"/>
    </mappers>

Lombok

Lombok项目是一个java库，它可以自动插入到编辑器和构建工具中，增强java的性能。不需要再写getter、setter或equals方法，只要有一个注解，你的类就有一个功能齐全的构建器、自动记录变量等等。

使用步骤

在IDEA中安装Lombok插件

在项目中导入Lombok 的jar包

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

在实体类中使用

package com.depressiom.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @ClassName BlogAdmin
 * @Description 实体类
 * @Date 2022/11/28
 * @Author depressiom
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BlogAdmin {
    private int id;
    private String name;
    private String  password;
}

@Data

@AllArgsConstructor(有参构造) @NoArgsConstructor(无参构造)

取舍

多对一处理

环境搭建

导入Lombok

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatis_study</artifactId>
        <groupId>com.depressiom</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis_03</artifactId>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>
</project>

实体类

package com.depressiom.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @ClassName Cate
 * @Description 栏目
 * @Date 2022/12/1
 * @Author depressiom
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cate {
    private int id;
    private String catename;
}


package com.depressiom.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @ClassName ArtcleMapper
 * @Description 栏目下面的文章
 * @Date 2022/12/1
 * @Author depressiom
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Artcle {
    private int id;
    private String title;
    private String desc;
    private String content;
    //文章需要关联栏目
    private Cate cate;
    private String time;
    private String pic;
}

实体类对应的接口

package com.depressiom.dao;

import com.depressiom.pojo.Cate;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @ClassName CateMapper
 * @Description 栏目接口
 * @Date 2022/12/1
 * @Author depressiom
 */
public interface CateMapper {

    @Select("select * from blog_cate")
    List<Cate> getALLCate();
}


package com.depressiom.dao;

import com.depressiom.pojo.Artcle;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @ClassName ArtcleMapper
 * @Description 文章接口
 * @Date 2022/12/1
 * @Author depressiom
 */
public interface ArtcleMapper {
    @Select("select * from blog_artcle")
    List<Artcle> getArtcle();
}

接口对应的mapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.CateMapper">

</mapper>


<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.ArtcleMapper">

</mapper>

在Mybatis核心配置中绑定接口

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--  引入属性  -->
    <properties resource="db.properties"/>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!--  给实体类取别名  -->
    <typeAliases>
        <typeAlias type="com.depressiom.pojo.Artcle" alias="artcle"/>
        <typeAlias type="com.depressiom.pojo.Cate" alias="cate"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<!-- 绑定接口 -->
    <mappers>
        <!--   通配符绑定  报错   -->
<!--        <mapper resource="com/depressiom/dao/*.xml"/>-->
        <mapper class="com.depressiom.dao.CateMapper"/>
        <mapper class="com.depressiom.dao.ArtcleMapper"/>
    </mappers>
</configuration>

子查询嵌套处理

接口

/**
     *  查询所有文章 并且查询出对应的栏目信息
     *  复杂sql 注解不满足要求
     */
    List<Artcle> getArtcleAndCate();

Mapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.ArtcleMapper">
    <!--
    思路：
    1.查询所有的文章信息
    2.根据查询出来的栏目id 查询栏目信息   子查询

    -->
    <select id="getArtcleAndCate" resultMap="artcleAndCate">
        select * from blog_artcle
    </select>
    <resultMap id="artcleAndCate" type="artcle">
        <result property="id" column="id"/>
        <result property="title" column="title"/>
        <!--
        复杂的属性我们需要单独处理：1.association 对象使用  2.collection 集合使用
            属性解读：
                property：实体类字段
                column ： 数据库字段
                javaType：java的实体类
                select：查询语句的id
          -->
        <association property="cate" column="cateid" javaType="cate" select="getCate"/>
    </resultMap>
    <select id="getCate" resultType="cate">
        select * from blog_cate where id = #{id}
    </select>
</mapper>

测试

 @Test
    public void TestArtcle(){
       SqlSession sqlSession = MybatisUtils.getsqlSession();

       ArtcleMapper mapper = sqlSession.getMapper(ArtcleMapper.class);
       List<Artcle> artcles = mapper.getArtcleAndCate();
        for (Artcle artcle : artcles) {
            System.out.println(artcle);
        }
       sqlSession.close();
    }

结果嵌套处理

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.ArtcleMapper">
    <!--
    思路：
    1.查询所有的文章信息
    2.根据查询出来的栏目id 查询栏目信息   按照结果嵌套处理
    -->
    <select id="getArtcleAndCate" resultMap="artcleAndCate">
        select a.id as id,a.title as title,c.catename as cname from blog_artcle as a,blog_cate as c where a.cateid = c.id
    </select>
    <resultMap id="artcleAndCate" type="artcle">
        <result property="id" column="id"/>
        <result property="title" column="title"/>
        <!--  结果集直接映射javaType，复杂类型里面的返回结果在进行cate实体与数据库字段对应  -->
        <association property="cate" javaType="cate">
            <result property="catename" column="cname"/>
        </association>
    </resultMap>
</mapper>

一对多处理

环境搭建，实体类修改，其他同上

package com.depressiom.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @ClassName Cate
 * @Description 栏目
 * @Date 2022/12/1
 * @Author depressiom
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cate {
    private int id;
    private String catename;
    // 一个栏目下，多篇文章
    private List<Artcle> artcles;
}

按照结果嵌套处理

接口

package com.depressiom.dao;

import com.depressiom.pojo.Cate;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @ClassName CateMapper
 * @Description 栏目接口
 * @Date 2022/12/1
 * @Author depressiom
 */
public interface CateMapper {

    @Select("select * from blog_cate")
    List<Cate> getALLCate();

    // 根据栏目id查询 栏目下 多篇文章
    Cate getCateAndArtcle(@Param("id")int id);
}

Mapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.CateMapper">
    <!--    按照结果嵌套处理    -->
    <select id="getCateAndArtcle" resultMap="cateInfo">
        select a.id as id, c.id as cid,a.title as title,c.catename as cname
        from blog_artcle as a,blog_cate as c where a.cateid = c.id and c.id = #{id}
    </select>
    <resultMap id="cateInfo" type="cate">
        <result column="cid" property="id"/>
        <result property="catename" column="cname"/>
        <!--  复杂的属性我们需要单独处理：1.association 对象使用  2.collection 集合使用
          javaType:指定属性的类型
          集合中的泛型信息，我们使用ofType获取
          -->
        <collection property="artcles" ofType="artcle">
            <result property="id" column="id"/>
            <result property="title" column="title"/>
            <result property="cateid" column="cid"/>
        </collection>
    </resultMap>
</mapper>

测试

@Test
    public void TestCate(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        CateMapper mapper = sqlSession.getMapper(CateMapper.class);
        Cate cate = mapper.getCateAndArtcle(1);
        System.out.println(cate);
        /**
         * Cate(
         * id=1, catename=父亲的散文诗,
         * artcles=
         * [Artcle(id=1, title=test, desc=null, content=null, cateid=1, time=null, pic=null, cate=null),
         * Artcle(id=2, title=test1, desc=null, content=null, cateid=1, time=null, pic=null, cate=null)])
         */
        sqlSession.close();
    }

按照子查询嵌套处理

Mapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.CateMapper">
    <!--    按照结果嵌套处理    -->
    <select id="getCateAndArtcle" resultMap="cateInfo">
        select * from blog_cate where id= #{id}
    </select>
    <resultMap id="cateInfo" type="cate">
        <!--  绑定子查询  select="getArtcleByCateID"   子查询id为 上面查询的 column="id" -->
        <collection property="artcles" javaType="ArrayList" ofType="artcle" select="getArtcleByCateID" column="id">
        <!--     实体类与数据库字段一致，可省略     -->
        </collection>
    </resultMap>

    <select id="getArtcleByCateID" resultType="artcle">
        select * from blog_artcle where cateid = #{cid}
    </select>
</mapper>

测试

@Test
    public void TestCate(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();

        CateMapper mapper = sqlSession.getMapper(CateMapper.class);
        Cate cate = mapper.getCateAndArtcle(2);
        System.out.println(cate);

        sqlSession.close();
    }

cate id=0,是因为未绑定，绑定之后正常

动态SQL

根据不同的条件生成不同的SQL语句

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

环境搭建

package com.depressiom.pojo;

import lombok.Data;

import java.util.Date;

/**
 * @ClassName Blog
 * @Description 博客实体类
 * @Date 2022/12/2
 * @Author depressiom
 */
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

package com.depressiom.dao;

import com.depressiom.pojo.Blog;

/**
 * @ClassName BlogMapper
 * @Description 接口
 * @Date 2022/12/2
 * @Author depressiom
 */
public interface BlogMapper {
    int addBlog(Blog blog);
}

package com.depressiom.dao;

import com.depressiom.pojo.Blog;
import com.depressiom.utils.IDUtils;
import com.depressiom.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import javax.xml.crypto.Data;
import java.util.Date;

/**
 * @ClassName MapperTest
 * @Description 测试
 * @Date 2022/11/28
 * @Author depressiom
 */
public class MapperTest {

    @Test
    public void addTest(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("Spring学习");
        blog.setAuthor("depressiom");
        blog.setCreateTime(new Date());
        blog.setViews(999);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("SpringCloud学习");
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("SpringBoot学习");
        mapper.addBlog(blog);

        sqlSession.close();
    }


}


package com.depressiom.utils;

import java.util.UUID;

/**
 * @ClassName IDUtils
 * @Description 随机id
 * @Date 2022/12/2
 * @Author depressiom
 */
public class IDUtils {
    public static String getId(){
        // 返回随机id
        return UUID.randomUUID().toString().replaceAll("-","");
    }

}

<!--  mapper.xml  -->
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        value (#{id},#{title},#{author},#{createTime},#{views})
    </insert>
</mapper>

<!--  mybatis_config.xml  -->
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--  引入属性  -->
    <properties resource="db.properties"/>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--   解决字段名与数据库字段不一致    开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn  -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--  给实体类取别名  -->
    <typeAliases>
        <typeAlias type="com.depressiom.pojo.Blog" alias="blog"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<!-- 绑定接口 -->
    <mappers>
        <mapper class="com.depressiom.dao.BlogMapper"/>
    </mappers>
</configuration>

IF语句

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.depressiom.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        value (#{id},#{title},#{author},#{createTime},#{views})
    </insert>

    <!--    -->
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>
</mapper>

@Test
    public void queryBlogIF(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","Spring学习");
       List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose、when、otherwise

MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句

 <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <choose>
            <when test="title != null">
                where title = #{title}
            </when>
            <when test="author != null">
                where author = #{author}
            </when>
            <otherwise>
                where views = 999
            </otherwise>
        </choose>
    </select>

trim、where、set

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

 <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <if test="title != null">
               and  title = #{title}
            </if>
            <if test="author != null">
               and  author = #{author}
            </if>
        </where>
    </select>

<update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author},
            </if>
            <if test="views != null">
                views = #{views}
            </if>
        </set>
        where id= #{id}
    </update>

SQL片段

可以将一部分功能单独拿出来，便于复用

 <!-- sql片段   -->
    <sql id="ifSql">
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
   
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <!-- 引入sql片段   -->
            <include refid="ifSql"/>
        </where>
    </select>

注意点：最好基于单表定义SQL片段；不要存在where标签

Foreach

 <!-- 通过传递的万能map，map中可以存在一个集合  -->
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
               id = #{id}
            </foreach>
        </where>
    </select>

 @Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getsqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        ArrayList<String> ids = new ArrayList<String>();
        ids.add("ef26703e50b540ae8d0586660e390c03");
        ids.add("aa42a46d769245fd99d459a03a2ec544");
        ids.add("69bc244d2d764b909768a47337461fe6");
        map.put("ids",ids);

        List<Blog> blogs = mapper.queryBlogForeach(map);

        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

动态SQL就是在拼接SQL，保证SQL的正确性，按照SQL格式，排列组合