MyBatis开发步骤

MyBatis

开发环境：

    Java环境：JDK1.8.0_271
    IDE工具：IntelliJ IDEA 2021.1.2 x64
    测试包：junit4.3
    数据库：mysql8.0
    数据库可视化工具：Navicat Premium 15

框架搭建：准备jar包

mybatis核心：mybatis-3.2.2.jar
mysql数据库驱动：mysql-connector-java-8.0.22.jar

准备mybatis-config.xml 核心配置文件

<?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"/>
	<settings>
		<!--开启二级缓存-->
		<setting name="cacheEnabled" value="true"/>
		<!--resultMap映射关系权限-->
		<setting name="autoMappingBehavior" value="FULL"/>
	</settings>
    <!-- 实体类别名 -->
    <typeAliases>
        <package name="com.hui.entity"/>
    </typeAliases>
    <!-- 方法3 动态加载外部java配置文件 -->
    <environments default="development">
        <environment id="development">
            <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>
    
    <!-- 默认直接配置数据库
	environments 环境
	environment 环境变量
	transactionManager 事务处理
	dataSource 数据库配置  -->

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
 				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mydb" />
				<property name="username" value="root" />
				<property name="password" value="ok" />
			</dataSource>
		</environment>
	</environments>
    <mappers>
        <mapper resource="Mapper.xml的路径"/>
    </mappers>
</configuration>

准备数据库参数 db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/xxxx?&useSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
username=xxxx
password=xxxx

1.开发步骤

一、准备数据 mysql数据库

    1. 建库建表
    2.设置库表编码，添加表约束
    3.添加对应数据

二、书写实体类（数据库映射文件）

1.书写时遵循JavaEE开发命名规范，包含项目名、包名、类名、成员变量名、方法名、常量
2.书写时成员变量名称时，应和数据库对应表的列名、数据类型保持一致，数据库类型到实体时书写对应的Java数据类型
3.对实体类封装对应的方法（Constructor、Getter and Setter、toString()）

三、构建对应的DAO业务数据接口，设计对应的业务CRUD方法

四、构建DAO业务数据接口的Mapper.xml文件

• 简单 CRUD 业务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="">
    
    <select id=""  resultType="">
		 
	</select>
	
	<select id="" resultType="" parameterType="">
		
	</select>
    
    <insert id="" parameterType="">
		
	</insert>
	
	<delete id="" parameterType="">
		
	</delete>
    
    <update id="" parameterType="">
		
	</update>
    
</mapper>    

• 复杂业务Mapper （动态SQL）

<?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.hui.dao.UsersDao">

    <!--动态sql 单条件执行查询-->
    <select id="choose" resultType="Users">
        select * from users
        <where>
            <choose>
                <when test="name != null">
                    and name=#{name}
                </when>
                <when test="password != null">
                    and password=#{password}
                </when>
            </choose>
        </where>
    </select>

    <!--添加后返回新数据的ID-->
    <insert id="addUsers" parameterType="Users" useGeneratedKeys="true" keyProperty="id">
        insert into users(name,password,status,lastModifyTime,typeId)values (#{name},#{password},#{status},#{lastModifyTime},#{typeId})
    </insert>

    <!--动态sql 添加-->
    <insert id="addUsers1" parameterType="Users" useGeneratedKeys="true" keyProperty="id">
        insert into users
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="password != null">
                password,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="lastModifyTime != null">
                lastModifyTime,
            </if>
            <if test="typeId != 0">
                typeId,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="password != null">
                #{password},
            </if>
            <if test="status != null">
                #{status}
            </if>
            <if test="lastModifyTime != null">
                #{lastModifyTime},
            </if>
            <if test="typeId != 0">
                #{typeId},
            </if>
        </trim>
    </insert>

    <!--动态sql 更新-->
    <update id="updateUsers1" parameterType="Users">
        update users
        <trim prefix="set" suffixOverrides=",">
            <if test="name != null">
                name=#{name},
            </if>
            <if test="password != null">
                password=#{password},
            </if>
            <if test="status != null">
                status=#{status}
            </if>
            <if test="lastModifyTime != null">
                lastModifyTime=#{lastModifyTime},
            </if>
            <if test="typeId != 0">
                typeId=#{typeId},
            </if>
        </trim>
        where id=#{id}
    </update>

</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="com.hui.dao.FlightDao">

    <!--
	id="selectById" : 方法名
	resultType="com.hui.entity.Users" : 方法返回值类型/结果类型
	parameterType="com.hui.entity.Users" : 方法参数类型
	-->
    <!--
    配置MyBatis二级缓存相关参数
    eviction：缓存的回收策略
	LRU - 最近最少使用，移除最长时间不被使用的对象
	FIFO - 先进先出，按对象进入缓存的顺序来移除它们
	SOFT - 软引用，移除基于垃圾回收器状态和软引用规则的对象
	WEAK - 弱引用，更积极地移除基于垃圾收集器和弱引用规则的对象
	默认的是LRU
	flushInterval：缓存刷新间隔
	缓存多长时间清空一次，默认不清空，设置一个毫秒值
	readOnly：是否只读
	true：只读：mybatis认为所有从缓存中获取数据的操作都是只读操作，不会修改数据。
	mybatis为了加快获取数据，直接就会将数据在缓存中的引用交给用户 。不安全，速度快
	false：读写(默认)：mybatis觉得获取的数据可能会被修改
	mybatis会利用序列化&反序列化的技术克隆一份新的数据给你。安全，速度相对慢
	size：缓存存放多少个元素
	type：指定自定义缓存的全类名(实现Cache接口即可)
	-->
    <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"></cache>

    <select id="selectAll" resultType="Flight">
        select * from flight
    </select>

    <select id="selectById" resultType="Flight" parameterType="int">
        select * from flight where id=#{id}
    </select>

    <select id="selectByIdAndFlightNo" resultType="Flight">
        select * from flight where id=#{id} and flightNo=#{flightNo}
    </select>
    
    <select id="getSelectGetMap" resultType="Flight" parameterType="map">
        select * from flight where id=#{id} and flightNo=#{flightNo} and departureCity=#{departureCity}
    </select>

    <!--使用二级缓存的查询操作-->
    <!--useCache="true" : 在select标签中表示此方法执行时使用缓存（缓存只针对与查询操作）-->
    <select id="cacheSelectAll" resultType="Flight" useCache="true">
        select * from flight
    </select>

    <!--resultMap的查询-->
    <resultMap id="resultmap" type="Flight">
        <result column="id" property="id"/>
        <result column="flNo" property="flightNo"/>
        <result column="departureCity" property="departureCity"/>
    </resultMap>
    <!--select标签中有resultMap属性，表示使用对应resultMap标签的规则进行查询-->
    <select id="selectAllResultMap" resultType="Flight" resultMap="resultmap">
        select id,flightNo as flNo,departureCity from flight
    </select>

    <!--关联查询一对多-->
    <resultMap id="cityflight" type="City">
        <!--<id property="id" column="id"/>-->
        <collection property="flightArrayList" ofType="Flight">
            <id property="id" column="fid"/>
        </collection>
    </resultMap>
    <select id="selectFlightAndCity" resultMap="cityflight" parameterType="int">
        select c.*,f.id as fid,f.flightNo,f.departureCity,f.departureTime,f.arrivalCity,f.arrivalTime from city c left join flight f on c.id=f.departureCity where c.id=#{id}
    </select>

    <!--关联查询 多对一 -->
    <resultMap id="flightcity" type="Flight">
        <id property="id" column="id"/>
        <association property="city" javaType="City">
            <id property="id" column="id"/>
        </association>
    </resultMap>
    <select id="selectCityFlight" resultMap="flightcity" parameterType="int">
        select f.*,c.cityName from flight f left join city c on f.id=c.id where f.id=#{id}
    </select>

    <!--动态sql 查询全部 按条件查询 模糊查询-->
    <select id="selectLike" resultType="Flight" parameterType="string">
        select * from flight

        <where>
            <if test="id != null ">
                and id=#{id}
            </if>
           <!-- <if test="flightNo != null">
                and flightNo=#{flightNo}
            </if>-->
            <if test="flightNo != null">
                and flightNo like CONCAT('%',#{flightNo},'%')
            </if>
        </where>
    </select>

    <insert id="addFlight" parameterType="Flight">
        insert into flight (flightNo,departureCity,departureTime,arrivalCity,arrivalTime) values (#{flightNo},#{departureCity},#{departureTime},#{arrivalCity},#{arrivalTime})
    </insert>

    <!--添加后返回新数据的ID-->
    <insert id="addFlightAndId" parameterType="Flight" useGeneratedKeys="true" keyProperty="id">
        insert into flight (flightNo,departureCity,departureTime,arrivalCity,arrivalTime) values (#{flightNo},#{departureCity},#{departureTime},#{arrivalCity},#{arrivalTime})
    </insert>

    <delete id="deleteFlight" parameterType="int">
        delete from flight where id=#{id}
    </delete>

    <update id="updateFlight" parameterType="Flight">
        update flight set flightNo=#{flightNo},departureCity=#{departureCity},departureTime=#{departureTime},arrivalCity=#{arrivalCity},arrivalTime=#{arrivalTime} where id=#{id}
    </update>

</mapper>

• 关联查询注意事项

关联查询
  一对多
     <collection property="flightArrayList" ofType="Flight">
            <id property="id" column="fid"/>
        </collection>

多对一
   <association property="city" javaType="City">
            <id property="id" column="id"/>
        </association>
名词解释：
 property：数据库列名
 column：别名
当列名没有被起别名时可以不写<id/>标签 (包含主键)      

五、构建测试类（xxxTest.java）

/**
     * 1.读取配置文件
     */
    Reader reader;
    {
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**得到session 对象
     *sqlsession工厂创建对象
     *SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
     *sqlsession工厂
     *SqlSessionFactory sqlSessionFactory=builder.build(reader);
     *sqlsession  BaseDao --每个表DAO
     *SqlSession session=sqlSessionFactory.openSession(true); true 自动提交 false 手动提交 commit
     */
    /**
     * 2.数据库管理者
     */
    SqlSession session = new SqlSessionFactoryBuilder().build(reader).openSession(true);
    /**
     * 获得DAO接口的对象
     * */
    FlightDao flightDao = session.getMapper(FlightDao.class);
    
     @Test
      public void testXxxx(){
            flightDao.调用接口内的业务方法();
            //给到相应参数，进行测试
      }