MyBatis持久层框架详解

five小点心

已于 2023-05-11 09:44:55 修改

阅读量1.1k

点赞数

分类专栏： # Java 文章标签： mysql 数据库 sql Powered by 金山文档

于 2023-01-10 14:43:49 首次发布

本文链接：https://blog.csdn.net/d905133872/article/details/128567681

版权

Java 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

一、什么是MyBatis

MyBatis是一个实现了数据持久化的开源框架。简单理解就是对JDBC进行封装。

MyBasic优点：

和JDBC相比，减少了50%以上的代码量

MyBatis是最简单的持久化框架，小巧且简单

MyBatis相当灵活，不会对应用程序或者数据库的现有设计强加任何影响，SQL卸载XML里，从程序代码中彻底分离，降低耦合度，便于统一管理和优化，并可重复使用

提供XML标签，支持编写动态SQL语句

提供映射标签，支持对象与数据库的ORM字段关系映射

MyBatis缺点：

SQL语句的编写工作量较大，尤其是字段多、关联表多的时候。对开发人员编写SQL语句有一定的要求

SQL语句依赖于数据库，导致数据库一致性差，不能随意更换数据库

二、三层架构

表现层：用于展示数据

业务层：用于处理业务需求

持久层：和数据库进行交互

三、MyBatis的环境配置

1、新建项目

（我在创建的时候出现了maven-clean-plugun报红，解决方案参考链接）

2、环境配置

pom.xml添加配置

// 添加MySQL配置
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
</dependency>

// 添加oracle配置
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.2</version>
</dependency>

// 添加MyBatis配置
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.2</version>
</dependency>

resource设置为资源根目录

在resource下添加配置文件

database.properties文件

添加MySQL和Oracle配置参数

mysqldriver=com.mysql.cj.jdbc.Driver
mysqlurl=jdbc:mysql://192.168.153.134:3306/new?allowMultiQueries=true
mysqluser=root
mysqlpwd=123123
oracleDriver=oracle.jdbc.driver.OracleDriver
oracleUrl=jdbc:oracle:thin:@192.168.153.136:1521:prod
oracleuser=scott
oraclepwd=123123

mybatis-config.xml文件

通过mybatis-config文件连接MySQL/Oracle文件，模板如下：

mapper配置中可以使用相对路径，也可以使用绝对路径。由于版本不同，相对路径的书写方式也有所区别。

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

    <typeAliases>
        <package name="org.example.pojo"/>
    </typeAliases>

    <environments default="mysqlDev">
        <environment id="mysqlDev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${mysqldriver}"/>
                <property name="url" value="${mysqlurl}"/>
                <property name="username" value="${mysqluser}"/>
                <property name="password" value="${mysqlpwd}"/>

            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="org.example.dao/DogDao.xml"/>
        
    </mappers>

</configuration>

mybatis-config中配置mapper映射路径

方法一：
<mappers>
    <package name="org.example.dao" />
</mappers>

方法二：
<mappers>
    <mapper resource="org.example.dao/DogDao.xml"/>       
</mappers>

方法三：
<mappers>
    <mapper url="file:///D:\Servers\KB21\Java\mybatisstu\src\resource\org.example.dao\DogDao.xml"/>
</mappers>

test类-定义工厂模式

public class DogTest {
    // 工厂模式
    SqlSessionFactory sqlSessionFactory=null;
    // 定义会话
    SqlSession sqlSession = null;
    // 定义DogDao对象
    DogDao dogDao = null;

    @Before
    public void init(){
        System.out.println("Start");
        // 加载mybatis_config.xml配置文件
        InputStream inputStream = DogTest.class.
                getClassLoader().
                getResourceAsStream("mybatis-config.xml");
        // 获取builder建造工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        // 通过建造工厂来建造SqlSessionFactory
        sqlSessionFactory = builder.build(inputStream);
        // 创建Session会话
        sqlSession = sqlSessionFactory.openSession();
        // 获取Session会话的执行者
        dogDao = sqlSession.getMapper(DogDao.class);

    }

    @Test
    public void showInfo(){
        System.out.println(sqlSessionFactory);
        System.out.println(sqlSession);
        System.out.println(dogDao);
    }

    @After
    public void down(){
        sqlSession.commit();
        sqlSession.close();
        System.out.println("Over");
    }
}

如果能正常显示，则可以继续测试方法是否可以执行

四、SQL语句编写

1、增删改语句编写

定义DogDao接口

在接口中定义方法

public interface DogDao {
    int save(Dog dog);
    int update(Dog dog);
    int delete(Dog dog);

<!--批量操作-->
    int batchDelete(List<Integer> dogID);
    int batchAdd(List<Dog> dogs);
    int batchUpdate(List<Dog> dogs);
}

编写SQL语句DogDao.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="org.example.dao.DogDao">

    <insert id="save">
        insert into dog(name,health,love,strain,lytm)
        values(#{name},#{health},#{love},#{strain},now())
    </insert>

    <update id="update">
        update dog
        set name=#{name},health=#{health}
        where id=#{id}
    </update>

    <delete id="delete">
        delete from dog where id=#{id}
    </delete>

    <delete id="batchDelete">
        delete from dog where id in <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
    </delete>

    <sql id="tableName">dog</sql>
    <insert id="batchAdd">
        insert into <include refid="tableName"/>
        values
            <foreach collection="list" item="dog" separator="," >
            (null,#{dog.name},#{dog.health},#{dog.love},#{dog.strain},now())
            </foreach>
    </insert>


    <update id="batchUpdate">

        <foreach collection="list" item="dog" separator=";">
            update
            <include refid="tableName"/>
            <set>
                <if test="dog.name!=null and dog.name!='' ">
                    name=#{dog.name},
                </if>
                <if test="dog.health!=null">
                    health=#{dog.health},
                </if>
                <if test="dog.love!=null">
                    love=#{dog.love},
                </if>
                <if test="dog.strain!=null and dog.strain!='' ">
                    strain=#{dog.strain},
                </if>
            </set>
            where id=#{dog.id}
        </foreach>
    </update>


</mapper>

2、动态编写SQL语句

在上面的insert语句中，我们插入字段的时候只能按照指定字段进行插入，这样的写法所有字段都必须填写。如果有字段不需要填写，这种写法显然不合适，需要进行升级。

DogDao.xml

<!--动态新增-->
    <insert id="add">
        insert into dog
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name!=null and name!=''">
                name,
            </if>
            <if test="health!=null">
                health,
            </if>
            <if test="love!=null">
                love,
            </if>
            <if test="strain!=null and strain!=''">
                strain,
            </if>
            <if test="lytm!=null">
                lytm,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name!=null and name!=''">
                #{name},
            </if>
            <if test="health!=null">
                #{health},
            </if>
            <if test="love!=null">
                #{love},
            </if>
            <if test="strain!=null and strain!=''">
                #{strain},
            </if>
            <if test="lytm!=null">
                #{lytm},
            </if>
        </trim>
    </insert>

测试

方法测试

在测试类里面添加测试语句

@Test
    public void testSaveDog(){
        Dog dog = new Dog();
        dog.setName("绿绿");
        dog.setHealth(100);
        dog.setLove(100);
        dog.setStrain("狼狗");
        dogDao.save(dog);
    }

    @Test
    public void testUpdateDog(){
        Dog dog = new Dog();
        dog.setId(38);
        dog.setHealth(80);
        dog.setName("卡卡");
        dogDao.update(dog);
    }

    @Test
    public void testDeleteDog(){
        Dog dog = new Dog();
        dog.setId(27);
        dogDao.delete(dog);
    }

    @Test
    public void testBatchDeleteDog(){
        List<Integer> dogID = new ArrayList<>();

        dogID.add(22);
        dogID.add(23);
        dogID.add(24);
        dogDao.batchDelete(dogID);
    }

    @Test
    public void testBatchAdd(){
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog("白白",100,100,"白狗");
        Dog dog2 = new Dog("黄黄",100,100,"黄狗");
        Dog dog3 = new Dog("黑黑",100,100,"黑狗");
        Dog dog4 = new Dog("花花",100,100,"花狗");
        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        dogs.add(dog4);
        dogDao.batchAdd(dogs);

    }

    @Test
    public void testBatchUpdate(){
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog(28,"白白",100,80,"白狗");
        Dog dog2 = new Dog(29,"黄黄",100,80,"黄狗");
        Dog dog3 = new Dog(30,"黑黑",100,80,"黑狗");
        Dog dog4 = new Dog(31,"花花",100,80,"花狗");
        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        dogs.add(dog4);
        dogDao.batchUpdate(dogs);
    }

}

3、查找语句

在DogDao接口中定义方法体，查询全部数据

public interface DogDao {
    List<Dog> findAllDog();
}

DogDao.xml

<select id="findAllDog" resultType="Dog">
    select id,name,health,love,strain,lytm from dog;
</select>

4、条件查询

DogDao接口

//    根据狗狗的名字(模糊查询)和亲密度查询狗狗的信息 可以自由选择降序和升序(1.升序,2.降序)
List<Dog> getAllDogByNameAndLove(
      @Param("dogname") String dogName,
      @Param("doglove") Integer love,
      @Param("ordervalue") Integer orderValue
);

DogDao.xml

<select id="getAllDogByNameAndLove" resultType="Dog">
    select id,name,health,love,strain,lytm from dog
    <where>
        <if test="dogname!=null and dogname!=''">
            name like concat('%',#{dogname},'%')
        </if>
        <if test="doglove!=null">
            and love=#{doglove}
        </if>
    </where>
    <choose>
        <when test="ordervalue==1">
            order by lytm asc
        </when>
        <when test="ordervalue==2">
            order by lytm desc
        </when>
        <otherwise>
            order by id desc
        </otherwise>
    </choose>
</select>

Test类

当name、love都为null时，全部输出。默认排序方式为通过id降序排序

@Test
public void testgetAllDogByNameAndLove(){
//    List<Dog> dogs = dogDao.getAllDogByNameAndLove(null, null, null);
//    List<Dog> dogs = dogDao.getAllDogByNameAndLove(null,100,2);
    List<Dog> dogs = dogDao.getAllDogByNameAndLove("黑",null,1);

    for (Dog dog : dogs) {
        System.out.println(dog);
    }
}

5、多表联查 - 一对多

Dog类

public class Dog {
    // 当前狗狗对象有过的主人信息
    private List<Master> masters; 

    // getter and setter方法
    public List<Master> getMasters() {
        return masters;
    }
    public void setMasters(List<Master> masters) {
        this.masters = masters;
    }
}

DogDao接口

public interface DogDao {

//    根据狗狗ID找出狗狗的详细信息，以及所有领养过此狗狗的所有主人信息（集合）
    Dog getDogAndMastersByDogId(Integer id);

//    根据指定狗狗品种找出狗狗的详细信息以及所有领养过此品种的所有主人信息（集合）
    List<Dog> getAllDogAndMasterByDogStrain(String strain);

}

DogDao.xml

<!--定义Dog与Master的映射关系-->
<resultMap id="dogMap" type="Dog">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <result column="health" property="health"></result>
    <result column="love" property="love"></result>
    <result column="strain" property="strain"></result>
    <result column="lytm" property="lytm"></result>
    <collection property="masters" ofType="Master">
        <id column="pid" property="pid"></id>
        <result column="uname" property="name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
        <result column="yearnum" property="yearnum"></result>
        <result column="did" property="did"></result>
    </collection>
</resultMap>

<!-- 根据狗狗ID找出狗狗的详细信息，以及所有领养过此狗狗的所有主人信息 -->
<select id="getDogAndMastersByDogId" resultMap="dogMap">
    select d.id,d.name,d.health,d.love,d.strain,d.lytm,m.pid,m.name uname,m.age,m.gender,m.yearnum,m.did
    from dog d left join master m on m.did=d.id
    where d.id=#{dogId}
</select>

<!-- 根据指定狗狗品种找出狗狗的详细信息以及所有领养过此品种的所有主人信息 -->
<select id="getAllDogAndMasterByDogStrain" resultMap="dogMap">
    select d.id,d.name,d.health,d.love,d.strain,d.lytm,m.pid,m.name uname,m.age,m.gender,m.yearnum,m.did
    from dog d left join master m on m.did=d.id
    where d.strain=#{dogStrain}
</select>

Test类

@Test
    public void testgetDogAndMastersByDogId(){
        Dog dog = dogDao.getDogAndMastersByDogId(3);
        System.out.println(dog);
        List<Master> masters = dog.getMasters();
        for(Master master:masters){
            System.out.println(master);
        }
    }
    @Test
    public void testgetAllDogAndMasterByDogStrain(){
        List<Dog> dogs = dogDao.getAllDogAndMasterByDogStrain("西施");
        for(Dog dog:dogs){
            System.out.println(dog);
            List<Master> masters = dog.getMasters();
            for(Master master:masters){
                System.out.println(master);
            }
        }
    }

resultMap和resultType的区别：

resultType:只有查询出来的列名和pojo的属性一致时才会映射成功

resultMap:如果查询出来的列名和pojo的属性名不一致时,可以通过resultMap对列名和属性名做映射关系

6、多表联查 - 一对一

新建一个狗狗品种的详细描述

StrainType类

public class StrainType {
    private Integer id;
    private String name;
    private String description;

// 重写toString方法

// getter and setter方法

}

DogDao接口

定义方法体

//    Dog List<Master> StrainInfo
Dog getDogAndMastersAndStraininfoByDogId(Integer id);

DogDao.xml

重新定义映射集合关系

<resultMap id="dogMap2" type="Dog">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <result column="health" property="health"></result>
    <result column="love" property="love"></result>
    <result column="strain" property="strain"></result>
    <result column="lytm" property="lytm"></result>

<!--一对一映射-->
    <association property="strainType" javaType="StrainType">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <result column="description" property="description"></result>
    </association>
<!--一对多映射-->
    <collection property="masters" ofType="Master">
        <id column="pid" property="pid"></id>
        <result column="uname" property="name"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
        <result column="yearnum" property="yearnum"></result>
        <result column="did" property="did"></result>
    </collection>
</resultMap>

<select id="getDogAndMastersAndStraininfoByDogId" resultMap="dogMap2">
    select d.id,d.name,d.health,d.love,d.strain,d.lytm,
           s.id sid,s.name sname,s.description,
           m.pid,m.name uname,m.age,m.gender,m.yearnum,m.did
    from dog d
        left join straininfo s on d.strain = s.name
        left join master m on d.id = m.did where d.id=#{dogId};
</select>

Test类

@Test
    public void testgetDogAndMastersAndStraininfoByDogId(){
        Dog dog = dogDao.getDogAndMastersAndStraininfoByDogId(2);
        System.out.println("狗狗信息");
        System.out.println(dog);
        System.out.println("狗狗主人的信息：");
        List<Master> masters = dog.getMasters();
        for(Master master:masters){
            System.out.println(master);
        }
        System.out.println("狗狗品种：");
        System.out.println(dog.getStrainType());

    }