MyBatis笔记（2）

最新推荐文章于 2024-07-18 13:30:10 发布

代码敲到烂

最新推荐文章于 2024-07-18 13:30:10 发布

阅读量902

点赞数 34

文章标签： mybatis 笔记

本文链接：https://blog.csdn.net/Qcqc520/article/details/139664156

版权

这是续写，1点这里

10 MyBatis的延迟加载

项目结构：

10.1 什么是延迟加载

就是懒加载，不需要的时候不加载，需要的时候才加载

10.2 一对一懒加载

需求：根据id查询账户信息及关联的用户信息，若不查询用户相关信息，则不加载查询用户的sql语句

AccountMapper

public interface AccountMapper {
    /**
     * 根据id找到用户
     * @param id id
     * @return 用户信息
     */
    Account findAccountById(Integer id);
}

AccountMapper.xml

<mapper namespace="com.by.mapper.AccountMapper">
    <resultMap id="findAccountByIdResultMap" type="com.by.pojo.Account">
        <id column="id" property="id"/>
        <result column="uid" property="uid"/>
        <result column="money" property="money"/>
         <!--
          property：属性名
          javaType：属性的类型
          select： 要调用的 select 映射的 id
          column ： 传递给 select 映射的参数
          fetchType="lazy"：懒加载，默认情况下是没有开启延迟加载的，局部配置
         -->
        <association
                property="user"
                javaType="com.by.pojo.User"
                column="uid"
                select="com.by.mapper.UserMapper.getUserById"
                fetchType="lazy">
        </association>
    </resultMap>
    <select id="findAccountById" parameterType="int" resultMap="findAccountByIdResultMap">
        select * from account WHERE id=#{id}
    </select>
</mapper>

UserMapper.xml

<mapper namespace="com.by.mapper.UserMapper">

    <select id="getUserById" parameterType="int" resultType="com.by.pojo.User">
        SELECT * FROM user WHERE id = #{uid}
    </select>
</mapper>

test

这里输出的userList需要用到user的sql语句，所以没有懒加载

    //一对一懒加载
    @Test
    public void test() throws Exception{
        AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);
        Account userList = mapper.findAccountById(1);
        System.out.println(userList);
    }

这里输出的userlist的money，money是Acount的字段，无需用到User表，所以user的sql语句进行了懒加载

    //一对一懒加载
    @Test
    public void test() throws Exception{
        AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);
        Account userList = mapper.findAccountById(1);
        System.out.println(userList.getMoney());
    }

10.3 一对多懒加载

一个用户可能有多个账户，根据id查询用户关联的账户

UserMapper

    /**
     * 根据id查询用户关联的账户
     * @param id id
     * @return 存放的用户及关联的账户信息
     */
    User findUserById(Integer id);

UserMapper.xml

<mapper namespace="com.by.mapper.UserMapper">
    <resultMap id="findUserByIdResultMap" type="com.by.pojo.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        
                <!--一对多-->
        <collection property="accountList"
                    ofType="com.by.pojo.Account"
                    column="id"
                    select="com.by.mapper.AccountMapper.getAccountById"
                    fetchType="lazy">
        </collection>
    </resultMap>
    
<select id="findUserById" parameterType="int" resultMap="findUserByIdResultMap">
    SELECT * FROM user WHERE id = #{id}
</select>
</mapper>

AccountMapper.xml

<select id="getAccountById" parameterType="int" resultType="com.by.pojo.Account">
    SELECT * FROM account WHERE uid=#{uid}
</select>

Test

    //一对多懒加载
    @Test
    public void test1() throws Exception{
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.findUserById(41);
        System.out.println(userById);
    }

    //一对多懒加载
    @Test
    public void test1() throws Exception{
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.findUserById(41);
        System.out.println(userById.getUsername());
    }

10.4 多对多懒加载

一个用户可能有多个角色，一个角色可能有多个用户

RoleMapper

public interface RoleMapper {
    /**
     * 找到所有角色及关联的用户
     * @return 存放所有的角色信息
     */
    List<Role> findAll();
}

RoleMapper.xml

<mapper namespace="com.by.mapper.RoleMapper">
    <resultMap id="findAllResultMap" type="com.by.pojo.Role">
        <id column="id" property="id"/>
        <result column="ROLE_NAME" property="roleName"/>
        <result column="ROLE_DESC" property="roleDesc"/>
        <collection property="userList"
                    column="id"
                    ofType="com.by.pojo.User"
                    select="com.by.mapper.UserMapper.findUserByRoleId"
                    fetchType="lazy">
        </collection>

    </resultMap>
    <select id="findAll" resultMap="findAllResultMap">
        SELECT * FROM role
    </select>
</mapper>

UserMapper.xml

<select id="findUserByRoleId" parameterType="int" resultType="com.by.pojo.User">
    select *
    from user u join user_role ur on u.id = ur.uid
    where ur.rid = #{rid}
</select>

Test

    //多对多懒加载
    @Test
    public void test2() throws Exception{

        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        List<Role> roleList = mapper.findAll();
        roleList.forEach(role -> {
            System.out.println(role);
        });
    }

    //多对多懒加载
    @Test
    public void test2() throws Exception{

        RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
        List<Role> roleList = mapper.findAll();
        roleList.forEach(role -> {
            System.out.println(role.getRoleName);
        });
    }

10.5 全局开启懒加载

每次都在collection或者association里声明过于麻烦，可以设置全局懒加载，这样默认就是懒加载了，mybatis默认是饿加载。

mybatis-config.xml

<!-- 全局配置延迟加载策略 -->
<settings>
    <!--  打开延迟加载的开关  -->
    <setting name="lazyLoadingEnabled" value="true"/>
</settings>

11 MyBatis的动态SQL

项目结构：

11.1 什么是动态sql

就是在mybatis的sql语句里加一些逻辑标签拼接sql语句。

11.2 if标签

需求：不确定用户根据哪个字段查询用户信息，将用户查询的条件存放到User对象里，再在sql语句里进行判断并对字段赋值再进行相应的查询

UserMapper

/**
 * 查询用户
 * @param user 查询条件存放再user里
 * @return 查询结果
 */
List<User> findUser(User user);

UserMapper.xml

<!--    IF标签-->
<select id="findUser" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
    SELECT * FROM user WHERE 1=1 <!-- 1=1是防止第一个条件出现AND -->
    <if test="id!=null">
        AND id = #{id}
    </if>
    <if test="username!=null and username!=''">
        AND username = #{username}
    </if>
    <if test="password!=null and password!=''">
        AND password = #{password}
    </if>
    <if test="birthday!=null">
        AND birthday = #{birthday}
    </if>
    <if test="sex!=null and sex!=''">
        AND sex = #{sex}
    </if>
    <if test="address!=null and address!=''">
        AND address = #{address}
    </if>
</select>

Test

@Test
public void testIF(){
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    //假设用户对id字段和name字端查询
    User user = new User().setId(41).setUsername("张三丰");
    List<User> userList = userMapper.findUser(user);
    for (User user1 : userList) {
        System.out.println(user1);
    }
}

11.3 where标签

为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。

还是和if标签一样的需求、方法

UserMapper.xml

<!--    WHERE标签-->
<select id="findUser2" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
    SELECT * FROM user
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="username!=null and username!=''">
            AND username = #{username}
        </if>
        <if test="password!=null and password!=''">
            AND password = #{password}
        </if>
        <if test="birthday!=null">
            AND birthday = #{birthday}
        </if>
        <if test="sex!=null and sex!=''">
            AND sex = #{sex}
        </if>
        <if test="address!=null and address!=''">
            AND address = #{address}
        </if>
    </where>
</select>

Test

11.4 set标签

用于更新列，去除多余的”，“

UserMapper

/**
 * 根据id修改用户信息
 * @param user 存放id和要修改的用户信息
 */
void updateUserById(User user);

UserMapper.xml

<!--    SET标签-->
<update id="updateUserById" parameterType="com.by.pojo.User">
    UPDATE user
    <set>
        <if test="username!=null and username!=''">
             username = #{username},
        </if>
        <if test="password!=null and password!=''">
             password = #{password},
        </if>
        <if test="birthday!=null">
             birthday = #{birthday},
        </if>
        <if test="sex!=null and sex!=''">
             sex = #{sex},
        </if>
        <if test="address!=null and address!=''">
             address = #{address},
        </if>
    </set>
    WHERE id = #{id}
</update>

Test

@Test
public void testSET(){
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    //修改性别为女 姓名为张二丰
    User user = new User().setId(41).setUsername("张二丰").setSex("女");
    userMapper.updateUserById(user);
}

11.5 trim标签

添加前缀和后缀，去掉多余的前缀和后缀

UserMapper

/**
 * 新增用户信息
 * @param user 用户信息
 */
void addUser(User user);

UserMapper.xml

<!--    trim标签-->
<insert id="addUser" parameterType="com.by.pojo.User">
    INSERT INTO user
    <!--
		prefix：添加前缀
		suffix：添加后缀
		suffixOverrides：后缀覆盖 
	-->
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username!=null and username!=''">
            username,
        </if>
        <if test="password!=null and password!=''">
            password,
        </if>
        <if test="birthday!=null">
            birthday,
        </if>
        <if test="sex!=null and sex!=''">
            sex,
        </if>
        <if test="address!=null and address!=''">
            address,
        </if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username!=null and username!=''">
            #{username},
        </if>
        <if test="password!=null and password!=''">
            #{password},
        </if>
        <if test="birthday!=null">
            #{birthday},
        </if>
        <if test="sex!=null and sex!=''">
            #{sex},
        </if>
        <if test="address!=null and address!=''">
            #{address},
        </if>
    </trim>
</insert>

Test

@Test
public void testTRIM(){
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    //新增杨过信息
    User user = new User()
            .setUsername("杨过")
            .setSex("男")
            .setPassword("111")
            .setBirthday(new Date())
            .setAddress("襄阳市");
    userMapper.addUser(user);
}

11.6 foreach标签

foreach标签的常见使用场景是集合进行遍历

UserMapper

/**
 * 根据id批量删除用户
 * @param idList ids
 * 这里建议用注解传参
 */
void DeleteUserByIds(@Param("idList") List<Integer> idList);

UserMapper.xml

<!--    FOREACH-->
<delete id="DeleteUserByIds" parameterType="list">
    DELETE FROM user where         <!--id in(41,42,43)-->
    <!--
        collection：取值list、array、map、@Param("keyName")、对象的属性名
        item：循环取出的具体对象
        open：起始符
        separator：分隔符
        close：结束符
    -->
    <foreach collection="idList" item="id" open="id in(" separator="," close=")">
        #{id}
    </foreach>
</delete>

Test

    @Test
    public void testFOREACH(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<Integer> idList = new ArrayList<>();
        idList.add(41);
        idList.add(42);
        idList.add(43);
        userMapper.DeleteUserByIds(idList);
    }

11.7 sql标签

sql元素标签用来定义可重复使用的SQL代码片段，使用时只需要用include元素标签引用即可

UserMapper

List<User> findUser3(User user);

UserMapper.xml

<!--    SQL标签-->
<sql id="query_user_where">
    <if test="id!=null">
        AND id = #{id}
    </if>
    <if test="username!=null and username!=''">
        AND username = #{username}
    </if>
    <if test="password!=null and password!=''">
        AND password = #{password}
    </if>
    <if test="birthday!=null">
        AND birthday = #{birthday}
    </if>
    <if test="sex!=null and sex!=''">
        AND sex = #{sex}
    </if> 
    <if test="address!=null and address!=''">
        AND address = #{address}
    </if>
</sql>
<select id="findUser3" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
    SELECT * FROM user
    <where>
        <include refid="query_user_where"></include>
    </where>
</select>

Test

@Test
public void TestSQL(){
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = new User().setUsername("张三丰").setSex("男");
    List<User> userList = userMapper.findUser3(user);
    for (User user1 : userList) {
        System.out.println(user1);
    }
}

12 MyBatis缓存

12.1 一级缓存

范围：sqlSession

配置：默认开启

走缓存：同一个sqlSession

不走缓存：不同的sqlSession 或在两次查询之间执行了增删改操作

12.2 二级缓存

范围：sqlSessionFactory

配置：在mapper标签里开启二级缓存

<mapper>
    <!--开启二级缓存-->
    <cache></cache>
</mapper>

走缓存：同一个sqlSessionFactory

不走缓存：不同的sqlSessionFactory 或在两次查询之间执行了增删改操作

13 MyBatis的注解开发-了解

创建工程：
在这里插入图片描述

13.1 注解开发的缺点

MyBatis可以在接口中直接添加MyBatis注解，完成CRUD。

但注解模式属于硬编码到.java文件中，失去了使用配置文件外部修改的优势，可结合需求选用。

13.2.mapper

public interface UserMapper {
    /**
     * 查询所有用户
     *
     * @return 存放所有用户的集合
     */
    @Select("SELECT * FROM user")
    List<User> findAll();

    /**
     * 添加用户
     *
     * @param user 新用户信息
     */
    @Insert("INSERT INTO user(username,password,birthday,sex,address) " +
            "values (#{username},#{password},#{birthday},#{sex},#{address})")
    //主键回填
    @SelectKey(keyColumn = "id", keyProperty = "id", resultType = Integer.class,
            before = false, statement = {"SELECT LAST_INSERT_ID()"})
    void insertUser(User user);

    /**
     * 修改用户信息
     *
     * @param user 修改后的用户信息
     */
    @Update("UPDATE user SET username=#{username},password=#{password},birthday=#{birthday},sex=#{sex},address=#{address}" +
            "WHERE id = #{id}")
    void updateUserById(User user);

    /**
     * 根据id删除用户
     *
     * @param id 被删除的id
     */
    @Delete("DELETE FROM user WHERE id = #{id}")
    void deleteUserById(Integer id);

    /**
     * 统计用户个数
     *
     * @return 个数
     */
    @Select("SELECT count(*) FROM user")
    int countUser();

    /**
     * 一对多，查询用户及其关联的账户信息
     * @return 存放的信息
     */
    @Results(id="resultMap",
            value= {
                    @Result(id=true,column="id",property="id"),
                    @Result(column="username",property="username"),
                    @Result(column="sex",property="sex"),
                    @Result(column="address",property="address"),
                    @Result(column="birthday",property="birthday"),
                    @Result(column="id",property="accountList",
                            many=@Many(
                                    select="com.by.mapper.AccountMapper.findByuId",
                                    fetchType= FetchType.LAZY
                            )
                    )
            })
    @Select("select * from user")
    public List<User> findAll2();

    @Select("SELECT * FROM user WHERE id = #{uid}")
    User findById(Integer id);
}

public interface AccountMapper {
    /**
     * 一对一，查询账户及其关联的用户信息
     * @return 存放的信息
     */
    @Select("select * from account where uid = #{uid} ")
    List<Account> findByuId(Integer id);

    @Results(id = "accountMap",value = {
            @Result(id=true,column="id",property="id"),
            @Result(column="uid",property="uid"),
            @Result(column="money",property="money"),
            @Result(column="uid",
                    property="user",
                    one=@One(select="com.by.mapper.UserMapper.findById",
                            fetchType= FetchType.LAZY)
            )
    })
    
    @Select("select * from account")
    List<Account> findAll();
}

12.3.pojo

@Data
@Accessors(chain = true)
public class User {

  private Integer id;
  private String username;
  private Date birthday;
  private String sex;
  private String address;
  private String password;
  private List<Account> accountList;

}

@Data
@Accessors(chain = true)
public class Account  implements Serializable {
  private static final long serialVersionUID =  1888976205348140162L;

  private Integer id;
  private Integer uid;
  private double money;
  private User user;
}

12.4.测试

    @Test
    public void testFindAll(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.findAll();
        userList.forEach(System.out::println);
    }

    @Test
    public void testInsertUser(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User().setUsername("东方不败").setPassword("111").setBirthday(new Date())
                .setAddress("河南省");
        userMapper.insertUser(user);
        System.out.println(user);
    }

    @Test
    public void testUpdateUserById(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User().setId(41).setUsername("东方不败").setPassword("111").setBirthday(new Date())
                .setAddress("河南省");
        userMapper.updateUserById(user);
        System.out.println(user);
        //查看是否修改成功
        List<User> userList = userMapper.findAll();
        userList.forEach(System.out::println);
    }

    @Test
    public void testDeleteUserById(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteUserById(41);
        //查看是否删除
        List<User> userList = userMapper.findAll();
        userList.forEach(System.out::println);
    }

    @Test
    public void testCountUser(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int countUser = userMapper.countUser();
        System.out.println(countUser);
    }

    @Test
    public void testOneToMany(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.findAll2();
        userList.forEach(System.out::println);
    }

    @Test
    public void testOneToOne(){
        AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);
        List<Account> accounts = accountMapper.findAll();
        accounts.forEach(System.out::println);
    }

private String address;
private String password;
private List accountList;

}


```java
@Data
@Accessors(chain = true)
public class Account  implements Serializable {
  private static final long serialVersionUID =  1888976205348140162L;

  private Integer id;
  private Integer uid;
  private double money;
  private User user;
}