MyBatis

wang_peng

已于 2024-03-01 11:48:57 修改

阅读量454

点赞数 6

CC 4.0 BY-SA版权

分类专栏： SpringBoot 文章标签： mybatis

于 2023-11-30 11:44:05 首次发布

本文链接：https://blog.youkuaiyun.com/wang_peng/article/details/134627921

SpringBoot 专栏收录该内容

9 篇文章

订阅专栏

1.基础

1.1 pom

<dependencies>

        <!--MyBatis核心-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!--MySql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

</dependencies>

1.2 jdbc.properties

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

1.3 核心配置

mybatis-config文件中的标签必须按照固定的顺序(有的标签可以不写，但顺序一定不能乱)： properties、settings、typeAliases、typeHandlers、objectFactory、objectWrapperFactory、reflectorFactory、plugins、environments、databaseIdProvider、mappers

<?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="jdbc.properties"/>
    <typeAliases>
        <package name="com.example.mybatis.pojo"/>
    </typeAliases>

    <!--配置连接数据库的环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--引入映射文件-->
    <mappers>
        <mapper resource="mappers/PaymentMapper.xml"></mapper>
    </mappers>
</configuration>

1.4 mapper接口

public interface PaymentMapper {

    List<Payment> selectAll();

}

1.5 映射文件

<?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.mybatis.mapper">

    <select id="selectAll" resultMap="Payment">
        select * from payment
    </select>

</mapper>

1.6 文件结构

1.7 测试功能

//读取MyBatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");

//获取SqlSessionFactoryBuilder对象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();

//通过核心配置文件所对应的字节输入流创建工厂类SqlSessionFactory，生产SqlSession对象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);

//获取sqlSession，此时通过SqlSession对象所操作的sql都必须手动提交或回滚事务
SqlSession sqlSession = sqlSessionFactory.openSession(true);

//通过代理模式创建UserMapper接口的代理实现类对象
PaymentMapper mapper = sqlSession.getMapper(PaymentMapper.class);

List<Payment> payments = mapper.selectAll();

1.8 MyBatisX

安装之后会出现小鸟图标,直接导航和快速生成映射文件

1.9 log4j日志功能

1) pom

<!-- log4j日志 -->
<dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
</dependency>

2) log4j.xml文件,存放的位置是src/main/resources目录下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info" />
    </logger>
    <root> 
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

1.10 mappers 包

1) 映射文件

<!--引入映射文件-->
<mappers>
   <package name="org.example.mybatis.mappers"/>
</mappers>

2) package结构必须和mapper文件结构一样，名字也必须一样

2.查询

2.1 无参数

<?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.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap">
        select * from payment
    </select>

</mapper>

2.2 带参数

1) 接口

public interface PaymentMapper {

    List<Payment> selectAll(@Param("id") int id);

}

2) 映射文件

<?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.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap" >
        select * from payment where id = #{id}
    </select>

</mapper>

2.3 实体参数

1) 接口

public interface PaymentMapper {
    List<Payment> selectAll(Payment id);
}

2) 映射文件

<?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.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap" >
        select *
        from payment
        where id = #{id}
          and serial_name like concat('%',#{serialName,jdbcType=VARCHAR},'%')
          and serial_name like "%"#{serialName,jdbcType=VARCHAR}"%"
    </select>

</mapper>

2.4 map参数

1) 接口

public interface PaymentMapper {
    List<Payment> selectAll(Map<String,Object> map);
}

2) 映射文件

用2.3即可

2.5 动态条件 if

1) 接口

按2.3即可

2) 映射文件

<?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.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap" >
        select *
        from payment
        <where>
          <if test="serialName != null and serialName != ''">
              and serial_name like "%"#{serialName,jdbcType=VARCHAR}"%"
          </if>
        </where>
    </select>
</mapper>

2.6 动态条件 choose

<?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.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap" >
        select *
        from payment
        where
            <choose>
                <when test="serialName != null and serialName != ''">
                    serial_name like "%"#{serialName,jdbcType=VARCHAR}"%"
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
    </select>
</mapper>

2.7 参数

<mapper namespace="org.example.mybatis.mappers.PaymentMapper">

    <resultMap id="paymentResultMap" type="Payment">
        <result column="serial_name" property="serialName"></result>
    </resultMap>
    <select id="selectAll" resultMap="paymentResultMap" >
        select *
        from payment
        where id = ${arg0} and id = ${arg1}
    </select>

    <select id="selectAll2" resultMap="paymentResultMap" >
        select *
        from payment
        where id = ${param1} and id = ${param2}
    </select>
</mapper>

2.8 多对一

<?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.mybatis.mappers.EmpMapper" >
    
    <resultMap id="empMap" type="Emp">
        <id column="id" property="id"></id>
        <result column="user_name" property="userName"></result>
        <result column="age" property="age"></result>
        <result column="dept_id" property="deptId"></result>
        <association property="dept" javaType="Dept">
            <id property="id" column="did"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="empMap">
        select *, t_dept.id as did
            from t_emp left join t_dept  on t_emp.dept_id = t_dept.id
    </select>
</mapper>

2.9 一对多

<?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.mybatis.mappers.DeptMapper" >

    <resultMap id="deptMap" type="Dept">
        <id property="id" column="id" />
        <result property="deptName" column="dept_name" />
        <collection property="emps" ofType="Emp">
            <id column="empid" property="id" />
            <result column="user_name" property="userName" />
            <result column="age" property="age" />
            <result column="dept_id" property="deptId" />
        </collection>
    </resultMap>
    <select id="selectAll"  resultMap="deptMap">
        select *,t_emp.id as empid from t_dept left join t_emp on t_dept.id = t_emp.dept_id
    </select>

</mapper>

3.分页

3.1 添加依赖

<!--分页-->
<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.0</version>
</dependency>

3.2 配置分页插件

在MyBatis的核心配置文件（mybatis-config.xml）中配置插件，注意顺序

 <plugins>
        <!--设置分页插件-->
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

3.3 代码

InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);

//访问第一页，每页四条数据
PageHelper.startPage(2,2);
List<Emp> selects = mapper.selectAll();

3.4 PageInfo

InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
	SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
	SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
	SqlSession sqlSession = sqlSessionFactory.openSession(true);
	EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
	PageHelper.startPage(1, 4);
	List<Emp> emps = mapper.selectByExample(null);
	PageInfo<Emp> page = new PageInfo<>(emps,5);
	System.out.println(page);

4.添加，修改，删除

4.1 添加

<insert id="insert" parameterType="payment"
            useGeneratedKeys="true"
            keyProperty="id">
        insert into payment(serial_name)
        values (#{serialName})
</insert>

4.2 修改

!--int updateUser();-->
<update id="updateUser">
    update t_user set username = '张三' where id = 5
</update>

4.3 删除

<delete id="deleteUser">
    delete from t_user where id = 6
</delete>

4.4 批量删除

<delete id="deleteMore">
	delete from t_user where id in (${ids})
</delete>
//测试类
@Test
public void deleteMore() {
	SqlSession sqlSession = SqlSessionUtils.getSqlSession();
	SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
	int result = mapper.deleteMore("1,2,3,8");
	System.out.println(result);
}

5.事务

5.1 手动代码

public class DeptController {
    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    private DeptMapper deptMapper;
    @Autowired
    private EmpMapper empMapper;

    @GetMapping("insert")
    public BaseDto<Long> insert(String name) {

        BaseDto<Long> dto = new BaseDto<>();
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        TransactionStatus transactionStatus = this.dataSourceTransactionManager.getTransaction(def);

        try {
             //业务代码
            dataSourceTransactionManager.commit(transactionStatus);
        } catch (RuntimeException  e) {

            dataSourceTransactionManager.rollback(transactionStatus);
            dto.setMessage(e.getMessage());
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
        finally {
            return dto;
        }
    }
}

5.2 自动