Mybatis - 笔记

1. 快速使用

步骤:
1. 添加Mybatis依赖坐标。
2. 创建数据库实体表,准备数据。
3. 创建实体类。
4. 编写配置文件mybatis-config.xml
5. 编写映射文件XxxMapper.xml
6. 测试类测试

1. 添加Mybatis依赖坐标。

<!--mybatis依赖-->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.9</version>
</dependency>
<!--数据库依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>

2. 数据库表

实体类

package pojo;

/**
 * @Date: 2022/4/21 20:11
 * @author: ZHX
 * @Description: 实体类 User类
 */
public class User {
    private int id;
    private String username;
    private String password;

    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

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

    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

mybatis-config.xml

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
        
<configuration>
    <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/xin?useSSL=false&amp;characterEncoding=utf8&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

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

    <select id="findAll" resultType="pojo.User">
        select *
        from user
    </select>
</mapper>

测试

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import pojo.User;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @Date: 2022/4/25 14:42
 * @author: ZHX
 * @Description: 测试使用Mybatis框架简单查询
 */
public class Test {
    public static void main(String[] args) throws IOException {
        InputStream resource = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        List<User> userList = sqlSession.selectList("UserMapper.findAll");

        System.out.println(userList);

        sqlSession.close();
    }
}

2. mybatis-config.xml配置文件

常用配置

<?xml version="1.0" encoding="UTF-8" ?>
<!--xml文档版本，编码-->

<!--dtd约束-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!--根标签-->
<configuration>

    <!--properties:设置属性(键值对形式),在该配置文件中可以通过key使用value,用来替换需要动态配置的属性值-->
    <!--方式一 引入外部文件-->
    <properties resource="jdbc.properties"/>
    <!--方式二 直接设置-->
    <properties>
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/xin?useSSL=false&amp;characterEncoding=utf8&amp;serverTimezone=UTC"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </properties>
    <!--方式三 引入网络资源-->
    <properties url="http://localhost:80/...."/>

    <!--typeAliases:别名,在配置文件中设置的别名可以在mapper映射文件中使用-->
    <typeAliases>
        <!--方式一: 给某个类单独设置别名-->
        <typeAlias type="pojo.User" alias="user"/>
        <!--方式二:包扫描,给该包下的所有类设置别名,默认别名是类名首字母小写-->
        <package name="pojo"/>
    </typeAliases>

    <!--environments:数据库环境，支持多环境.default选中该id的环境-->
    <environments default="development">
        <!--开发环境-->
        <environment id="development">

            <!--指定事务管理器的类型，JDBC、MANAGED-->
            <!--JDBC:直接使用JDBC的提交和回滚操作-->
            <!--MANAGED:从不提交或回滚，默认关闭-->
            <transactionManager type="JDBC"/>

            <!--指定数据源,POOLED、UNPOOLED、JNDI-->
            <!--POOLED:使用数据库连接池-->
            <!--UNPOOLED:不使用连接池，每次请求都要打开或关闭连接-->
            <!--JNDI:这个数据源实现是为了能在如 EJB 或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置一个 JNDI 上下文的数据源引用。-->
            <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>

        <!--测试环境-->
        <environment id="test">
            <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="mapper/UserMapper.xml"/>
        <!--方式二:使用资源定位符-->
        <mapper url="file://mapper/UserMapper.xml"/>
        <!--方式三:使用接口作为映射,有要求全限定名必须和XxxMapper.xml文件的namespace一致.-->
        <mapper class="mapper.UserMapper"/>
        <!--方式四:将包内的接口全部注册为映射器-->
        <package name="mapper"/>
    </mappers>
</configuration>

其他配置

Mybatis | XML配置

黑马程序员最全SSM框架教程

3. XxxMapper.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属性: 唯一标识,区分mapper.xml文件，建议和dao接口对应:包名.接口名-->
<mapper namespace="mapper.UserMapper">

    <!--查询-->
    <select id="findAll" resultType="user">
        select *
        from user
    </select>

    <!--插入-->
    <insert id="insert" parameterType="user">
        # 类似与jdbc中的预编译sql写法:insert into user(id, username, password) values(?,?,?)
        insert into user(id, username, password)
        values (#{id}, #{username}, #{password})
    </insert>

    <!--更新-->
    <update id="update" parameterType="user">
        update user
        set username = #{username},
            password = #{password}
        where id = #{id}
    </update>

    <!--删除-->
    <delete id="deleteById" parameterType="int">
        delete
        from user
        where id = #{id}
    </delete>

    <!--sql语句抽取-->
    <sql id="selectUser">
        select *
        from user
    </sql>

    <!--动态sql-->
    <!--if 多条件查询，不确定用户会用几个参数来查,所以null的字段不查-->
    <!--<where>标签相当于自带个1=1,当if条件都不满足时，where 1=1-->
    <select id="findByCondition" parameterType="user" resultType="user">
        /*使用抽取的sql*/
        <include refid="selectUser"/>
        <where>
            <if test="id!=0">
                id =#{id}
            </if>
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="password != null">
                and password = #{password}
            </if>
        </where>
    </select>
    <!--动态sql in-->
    <select id="findByIds" parameterType="list" resultType="user">
        select *
        from user
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

其他配置

Mybatis | XML映射文件

4. 多表操作

表与表的关系:
1.一对一:共用同一个主键或一个表的外键和另一个表的主键映射。
2.一对多或多对于:
3.多对多:

数据库实体表

订单表order

用户表user

4.1 一对一

sql参考

select *, a.id as oid, b.id as uid
from `order` a,
     user b
where a.user_id = b.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="mapper.OrderMapper">

    <resultMap id="orderMap" type="pojo.Order">
        <!--手动自定字段和实体属性的映射关系-->
        <id column="oid" property="id"/>
        <result column="order_time" property="orderTime"/>
        <result column="total" property="total"/>

        <!--Order对象中的user属性赋值-->
        <!--方式一-->
        <result column="uid" property="user.id"/>
        <result column="username" property="user.username"/>
        <result column="password" property="user.password"/>
        <!--方式二-->
        <association property="user" javaType="pojo.User">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>

    <!--多表查询-->
    <!-- 查询该订单以及对应的用户信息。查询结果无法封装到匹配的实体类,用resultMap进行中间转换-->
    <select id="findOrderAndUser" resultMap="orderMap">
        select *, a.id as oid, b.id as uid
        from `order` a,
             user b
        where a.user_id = b.id;
    </select>
</mapper>

4.2 一对多

sql参考

select *, u.id as uid
from user u,
     `order` o
where u.id = o.user_id;

    <!--封装多表查询的结果-->
    <resultMap id="userOrders" type="pojo.User">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <collection property="orderList" ofType="pojo.Order">
            <id column="oid" property="id"/>
            <result column="order_time" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>

    <!--一对多,查询一个用户的所有订单-->
    <select id="findUser" resultMap="userOrders">
        select *, u.id as uid
        from user u,
             `order` o
        where u.id = o.user_id;
    </select>

4.3 多对多

一个用户有多个角色，一个角色属于多个用户。

表

<!--封装多表查询的结果-->
<resultMap id="userAndRole" type="pojo.User">
    <id column="uid" property="id"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <collection property="roleList" ofType="pojo.Role">
        <id column="rid" property="id"/>
        <result column="role_description" property="description"/>
    </collection>
</resultMap>
<!--多对多查询-->
<select id="findUserAndRole" resultMap="userAndRole">
    select *
    from user u,
         role r,
         user_role ur
    where u.id = ur.uid
      and r.id = ur.rid
</select>

5. 参考资料

MyBatis官网

黑马程序员最全SSM框架教程