一对一查询

先创建好查询方法以及方法实现：

在数据库中，建立userinfo表和articleinfo表的关系(一篇文章对应一个作者)：

@Data
public class Articleinfo {
    private Integer id;
    private String title;
    private String content;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private Integer rcount;

    // 包含了 userinfo 对象
    private Userinfo user;
}

@Mapper
public interface ArticleInfoMapper {
    List<Articleinfo> getAll();
}

<?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="com.example.myblog.mapper.ArticleInfoMapper">

    <select id="getAll" resultType="com.example.myblog.model.Articleinfo">
        select a.*,u.* from articleinfo a left join userinfo u on a.uid=u.id
    </select>
</mapper>

@SpringBootTest
class ArticleInfoMapperTest {

    @Autowired
    private ArticleInfoMapper articleInfoMapper;
    
    @Test
    void getAll() {
        List<Articleinfo> list = articleInfoMapper.getAll();
        System.out.println();
        System.out.println(list);
        System.out.println();
    }
}

结果：

[Articleinfo(id=1, title=Java, content=Java正?, createtime=2022-10-05T16:44:28, updatetime=2022-10-05T16:44:28, rcount=1, user=null), Articleinfo(id=2, title=Spring, content=Spring正文, createtime=2022-10-05T16:57:40, updatetime=2022-10-05T16:57:40, rcount=1, user=null)]

可以看到并没有将user 信息查询出来，所以这样的查询方式不对。

正确的操作方法如下：

<?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="com.example.myblog.mapper.ArticleInfoMapper">
    <resultMap id="BaseMap" type="com.example.myblog.model.Articleinfo">
        <!--1.主键映射-->
        <id property="id" column="id"></id>
        <!--2.普通字段映射-->
        <result property="title" column="title"></result>
        <result property="content" column="content"></result>
        <result property="createtime" column="createtime"></result>
        <result property="updatetime" column="updatetime"></result>
        <result property="rcount" column="rcount"></result>
        <!--3.关联关系-->
        <association property="user"
                     resultMap="com.example.myblog.mapper.UserMapper.BaseMap"
                     columnPrefix="u_">

        </association>
    </resultMap>
    <select id="getAll" resultMap="BaseMap">
        select a.*,u.id u_id,u.username u_username,u.password u_password from articleinfo a left join userinfo u on a.uid=u.id
    </select>
</mapper>

  

结果： 

[Articleinfo(id=1, title=Java, content=Java正?, createtime=2022-10-05T16:44:28, updatetime=2022-10-05T16:44:28, rcount=1, user=Userinfo(id=1, name=admin, password=admin, photo=null, createtime=null, updatetime=null, state=0)), Articleinfo(id=2, title=Spring, content=Spring正文, createtime=2022-10-05T16:57:40, updatetime=2022-10-05T16:57:40, rcount=1, user=Userinfo(id=1, name=admin, password=admin, photo=null, createtime=null, updatetime=null, state=0))]

在上面<association>标签里面的user一定要和Articleinfo里面的user一致，不然就查询不到数据了

一对多查询 

先创建好查询方法以及方法实现：

在数据库中，建立userinfo表和articleinfo表的关系(一个作者对应多篇文章)：

@Data
@ToString
public class Userinfo {
    private int id;
    private String name;
    private String password;
    private String photo;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private int state;

    private List<Articleinfo> artList;
}

@Mapper
public interface UserMapper {
    List<Userinfo> getAll3();
}

<?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="com.example.myblog.mapper.UserMapper">
    <resultMap id="BaseMap2" type="com.example.myblog.model.Userinfo">
        <!--映射主键的(表中主键和程序实体类中的主键)-->
        <id column="id" property="id"></id>
        <!--普通列的映射-->
        <result column="username" property="name"></result>
        <result column="password" property="password"></result>
        <result column="photo" property="photo"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <!--外部关系-->
        <collection property="artList"
                    resultMap="com.example.myblog.mapper.ArticleInfoMapper.BaseMap"
                    columnPrefix="a_">
        </collection>
    </resultMap>
    <select id="getAll3" resultMap="BaseMap2">
        select u.*,a.id a_id,a.title a_title from userinfo u left join articleinfo a on u.id=a.uid
    </select>
</mapper>

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void getAll3() {
        List<Userinfo> userinfo = userMapper.getAll3();
        System.out.println(userinfo);
    }
}

 结果：

[Userinfo(id=1, name=admin, password=admin, photo=, createtime=2021-12-06T17:10:48, updatetime=2021-12-06T17:10:48, state=0, artList=[Articleinfo(id=1, title=Java, content=null, createtime=null, updatetime=null, rcount=null, user=null), Articleinfo(id=2, title=Spring, content=null, createtime=null, updatetime=null, rcount=null, user=null)])]

动态SQL的使用 

在之前学习JDBC的时候，有时会遇到拼接SQL语句的情况，比如拼接的SQL要添加空格、拼接的时候去掉列表最后一个列名的逗号，如果使用动态SQL就能彻底摆脱这种痛苦。

动态SQL

<if>标签 

有时我们添加字段的时候，有些字段是非必填项，这个时候就可以用到动态SQL中的<if>标签，它的语法如下：

<if test="..."> 
    参数名,
</if>
...
<if test="...">
    #{参数名}
</if>

它的含义是当某个字段不存在时，SQL语句就不用拼接该字段，存在时就需要拼接该字段 

演示如下：

@Mapper
public interface UserMapper {
    int add3(String username,String password,String p,int state);
}

<?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="com.example.myblog.mapper.UserMapper">
    <insert id="add3">
        insert into userinfo(username,password,
        <if test="p!=null">
            photo,
        </if>
        state)
        value(#{username},#{password},
        <if test="p!=null">
            #{p},
        </if>
        #{state})
    </insert>
</mapper>

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void add3() {
        userMapper.add3("张三","zhangsan",null,1);
    }
}

结果：

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void add3() {
        userMapper.add3("张三","zhangsan","img.gpj",1);
    }
}

<trim>标签 

<if>标签适合用来判断一个选填项，如果有多个字段都是选填的话，这个时候就要用到<trim>标签了，<trim>标签和<if>标签配合一起使用：

在使用<trim>标签之前先来掌握一下它的属性：

prefix：表示整个语句块以prefix的值作为前缀；

suffix：表示整个语句块以suffix的值作为后缀；

prefixOverrides：表示去掉整个语句块最前面符合条件的字符；

suffixOverrides：表示去掉整个语句块最后面符合条件的字符； 

演示如下：

@Mapper
public interface UserMapper {
    int add4(String username,String password,String p,Integer state);
}

<?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="com.example.myblog.mapper.UserMapper">
    <insert id="add4">
        insert into userinfo
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="p!=null">
                photo,
            </if>
            <if test="state!=null">
                state,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
            <if test="p!=null">
                #{p},
            </if>
            <if test="state!=null">
                #{state},
            </if>
        </trim>
    </insert>
</mapper>

上面的p不是字段名，而是传递过来的参数名 

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void add4() {
        userMapper.add4("张三","zhangsan",null,null);
    }
}

<where>标签 

传入的用户对象，根据属性做where条件查询，用户对象中属性不为null的，都为查询条件。

演示如下：

@Mapper
public interface UserMapper {
    Userinfo login2(String username, String password);
}

 

<?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="com.example.myblog.mapper.UserMapper">
    <select id="login2" resultType="com.example.myblog.model.Userinfo">
        select * from userinfo
        <where>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void login2() {
        Userinfo userinfo = userMapper.login2(null, "admin");
        System.out.println(userinfo);
    }
}

 

<where>标签有两个作用：
1.生成where，如果有查询条件那么会生成where，如果没有查询条件就会忽略where；

2.where会判断第一个查询条件前面有没有and，如果有则会删除；

上面的<where>标签也可以使用<trim prefix="where" prefixOverrides="and">替换 

<set>标签

如果需要根据传入的用户对象属性来更新用户数据，则可以使用<set>标签来指定动态内容。

例如：更新文章表中第一条记录：

 

@Mapper
public interface UserMapper {
    int updateContent(String title, String content,Integer id);
}

<?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="com.example.myblog.mapper.UserMapper">
    <update id="updateContent">
        update articleinfo
            <set>
                <if test="title!=null">
                    title=#{title},
                </if>
                <if test="content!=null">
                    content=#{content},
                </if>
            </set>
        where id=#{id}
    </update>
</mapper>

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void updateContent() {
        int updateContent = userMapper.updateContent(null, "Java正文信息",1);
        Assertions.assertEquals(1,updateContent);
    }
}

 

 

在这个例子中，set元素会动态地在行首插入set关键字，并会删除掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。或者可以使用<trim prefix="set" suffixOverrides=",">替换 

<foreach>标签 

对集合进行遍历时可以使用改标签。<foreach>标签有如下属性：

• collection：绑定方法参数中的集合，如List、Set、Map或者数组对象；
• item：遍历时的每一个对象；
• open：语句块开头的字符串；
• close：语句块结束的字符串；
• separator：每次遍历之间间隔的字符串； 

例如根据多个文章id来删除文章数据：

@Mapper
public interface UserMapper {
    int dels(List<Integer> list);
}

<?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="com.example.myblog.mapper.UserMapper">
    <delete id="dels">
        delete from articleinfo where id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>
</mapper>

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void dels() {
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        int i = userMapper.dels(list);
        System.out.println(i);
    }
}