MyBatis框架学习Ⅶ

最新推荐文章于 2023-04-08 18:00:36 发布

原创最新推荐文章于 2023-04-08 18:00:36 发布 · 280 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MyBatis

一个被转后台的Android开发专栏收录该内容

34 篇文章

订阅专栏

本文深入探讨MyBatis框架的高级特性，包括where和if标签的条件判断、sql片段的复用、foreach标签的列表操作等，通过具体示例展示如何在Mapper文件中灵活运用这些标签，实现复杂的SQL查询。

MyBatis标签学习

where 和 if标签学习

工程结构

在这里插入图片描述

配置文件

db.driver = com.mysql.jdbc.Driver
db.url = jdbc:mysql://localhost:3306/ee19_batis_day03?useUnicode=true&characterEncoding=utf-8
db.username = root
db.password = 123

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

<?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>
    <properties resource="db.properties">
    </properties>

    <!-- 别名设置 -->
    <typeAliases>
        <typeAlias type="entity.User" alias="user"></typeAlias>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <!-- 配置JDBC事务控制，由mybatis进行管理 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源，采用mybatis连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 加载映射文件 -->
    <mappers>
        <package name="dao.mapper"></package>
    </mappers>
</configuration>

实体类

package entity;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private Date birthday;
    private String address;
    private String sex;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

package entity;

public class UserQueryVo {
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

Mapper文件

<?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="dao.mapper.UserMapper">

    <select id="findUserListByTag" parameterType="entity.UserQueryVo" resultType="user">
        SELECT * FROM user
        <where>
          <if test="user != null">
              <if test="user.username != null and user.username != ''">
                 AND username  LIKE '%${user.username}%'
              </if>

              <if test="user.sex != null and user.sex != ''">
                  AND sex = #{user.sex}
              </if>
          </if>
        </where>
    </select>
</mapper>

package dao.mapper;

import entity.User;
import entity.UserQueryVo;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
    List<User> findUserListByTag (UserQueryVo vo) throws Exception;
}

测试文件

	@Test
    public void testFindUserListByTag() throws Exception {
        //读取配置文件
        //全局配置文件的路径
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        UserQueryVo userQueryVo = new UserQueryVo();
        User user = new User();
        user.setUsername("chaoge111");
        user.setSex("2");
        userQueryVo.setUser(user);
        List<User> userList = mapper.findUserListByTag(userQueryVo);
        System.out.println("userList = " + userList);
        sqlSession.close();
    }

sql片段（include、sql标签）

使用sql标签声明一个sql片段

<sql id="sqlFragment">
        <if test="user != null">
            <if test="user.username != null and user.username != ''">
                AND username  LIKE '%${user.username}%'
            </if>

            <if test="user.sex != null and user.sex != ''">
                AND sex = #{user.sex}
            </if>
        </if>
    </sql>

使用include标签引用sql片段

<select id="findUserListByTag" parameterType="entity.UserQueryVo" resultType="user">
        SELECT * FROM user
        <where>
            <include refid="sqlFragment"></include>
        </where>
    </select>

完整mapper文件

<?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="dao.mapper.UserMapper">

    <select id="findUserListByTag" parameterType="entity.UserQueryVo" resultType="user">
        SELECT * FROM user
        <where>
            <include refid="sqlFragment"></include>
        </where>
    </select>

    <sql id="sqlFragment">
        <if test="user != null">
            <if test="user.username != null and user.username != ''">
                AND username  LIKE '%${user.username}%'
            </if>

            <if test="user.sex != null and user.sex != ''">
                AND sex = #{user.sex}
            </if>
        </if>
    </sql>

</mapper>

package dao.mapper;

import entity.User;
import entity.UserQueryVo;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
    List<User> findUserListByTag (UserQueryVo vo) throws Exception;
}

测试文件

略

foreach标签学习

foreach是pojo类型

mapper文件

<?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="dao.mapper.UserMapper">

    <!--SELECT * FROM USER WHERE id IN (1, 16, 30)-->
    <select id="UserListByIds" parameterType="entity.UserQueryVo" resultType="user">
        select * from user
        <where>
            <if test="idList != null">
                and id in
                <foreach collection="idList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

</mapper>

package dao.mapper;

import entity.User;
import entity.UserQueryVo;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
    List<User> UserListByIds (UserQueryVo vo) throws Exception;
}

测试文件

@Test
    public void testUserListByIds() throws Exception {
        //读取配置文件
        //全局配置文件的路径
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        UserQueryVo userQueryVo = new UserQueryVo();

        List<Integer> list = new ArrayList<>();

        list.add(1);
        list.add(16);
        list.add(30);

        userQueryVo.setIdList(list);

        List<User> userList = mapper.UserListByIds(userQueryVo);
        System.out.println("userList = " + userList);
        sqlSession.close();
    }

foreach是List类型

mapper文件

<?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="dao.mapper.UserMapper">

    <!--SELECT * FROM USER WHERE id IN (1, 16, 30)-->
    <select id="UserListByListId" parameterType="java.util.List" resultType="user">
        select * from user
        <where>
            <if test="list != null and list.size > 0">
                and id in
                <foreach collection="list" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

package dao.mapper;

import entity.User;
import entity.UserQueryVo;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
    List<User> UserListByListId(List list) throws Exception;
}

测试文件

	@Test
    public void testUserListByIds() throws Exception {
        //读取配置文件
        //全局配置文件的路径
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //创建SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        UserQueryVo userQueryVo = new UserQueryVo();

        List<Integer> list = new ArrayList<>();

        list.add(1);
        list.add(16);
        list.add(30);

        userQueryVo.setIdList(list);

        List<User> userList = mapper.UserListByListId(list);
        System.out.println("userList = " + userList);
        sqlSession.close();
    }