MyBatis框架学习总结

        MyBatis是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。总的来说，MyBatis就是帮助我们更好地操作MySQL数据库。

1 Lombok插件

        安装Lombok插件，类上通过注解的方式，会自动完成getter和setter的编写，提高效率。

<!--所需依赖-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.16</version>
    <scope>provided</scope>
</dependency>

<!--注解-->
@AllArgsConstructor：生成全参构造器。
@NoArgsConstructor：生成无参构造器。
@Getter/@Setter： 作用类上，生成所有成员变量的getter/setter方法；作用于成员变量上，生成该成员变量的getter/setter方法。可以设定访问权限及是否懒加载等。
@Data：作用于类上，是以下注解的集合：@ToString @EqualsAndHashCode @Getter @Setter @RequiredArgsConstructor
@Log：作用于类上，生成日志变量。针对不同的日志实现产品，有不同的注解。

在lombok的作用下，在类上添加@Slf4j注解，便可直接使用日志--log.debug()

---

2 MyBatis基本操作

        通过MyBaits进行MySQL数据库的增删改查。

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

<!--数据库驱动依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.32</version>
</dependency>

<!--日志依赖-->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>${logback-classic.version}</version>
</dependency>

<!--druid数据源依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.9</version>
</dependency>

2.1【基本操作】

@Slf4j
public class MyBatisALLTest {
    //SQLsession工厂，用于获取会话连接
    SqlSessionFactory sqlSessionFactory = null;

    @Before
    public void MyBatisBefore() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        log.debug("sqlSessionFactory is [{}]", sqlSessionFactory);
    }

    @Test
    public void MyBatisWay(){
        try (SqlSession session = sqlSessionFactory.openSession()) {
            PageHelper.startPage(1, 5);
            List<Object> objects = session.selectList(IUserMapper.class.getName() + ".selectAll");
            log.debug("result is [{}]", objects);
        }
    }

    @Test
    public void MyBatisLikeUN(){
        try (SqlSession session = sqlSessionFactory.openSession()) {
            IUserMapper mapper = session.getMapper(IUserMapper.class);
            List<User> blog = mapper.selectLikeUN("%" + "s" + "%");
            log.debug("result is [{}]", blog);
        }
    }

    @Test
    public void MyBatisELD(){
        try (SqlSession session = sqlSessionFactory.openSession(true)) {
            IEmployeeMapper mapper = session.getMapper(IEmployeeMapper.class);
            List<Employee> blog = mapper.selectEmployee();
            log.debug("result is [{}]", blog);
        }
    }
}

2.2【结果集映射】

        如果实例对象的成员变量和数据库的变量命名对应不上，查出的值会为null。

//UserMapper.xml
<resultMap id="UserMap" type="User">
   <!-- id为主键 -->
   <id column="id" property="id"/>
   <!-- column是数据库表的列名 , property是对应实体类的属性名 -->
   <result column="user_name" property="username"/>
   <result column="password" property="password"/>
</resultMap>

<select id="selectUserById" resultMap="UserMap">
  select id, username, password from user where id = #{id}
</select>

2.3【动态SQL】

        MyBatis提供了对SQL语句动态的组装能力，大量的判断都可以在 MyBatis的映射XML文件里面配置，以达到许多我们需要大量代码才能实现的功能，大大减少了我们编写代码的工作量。

①if

<select id="selectByUser" resultType="User">
	select * from user
	where 1 =1
	<if test="id != null">
		AND id = #{id}
	</if>
	<if test="username != null and username != ''">
		AND username = #{username}
	</if>
	<if test="password != null and password != ''">
		AND password = #{password}
	</if>
</select>

②where

<select id="selectByUser" resultType="User">
	select * from user
	<where>
	<if test="id != null">
		AND id = #{id}
	</if>
	<if test="username != null and username != ''">
		AND username = #{username}
	</if>
	<if test="password != null and password != ''">
		AND password = #{password}
	</if>
    <where>
</select>

③trim

        下面的SQL表示，当id，username和password当中有一个存在时，会把sql语句中最前面的一个“AND”替换成“WHERE”，防止最后的sql语句出现语法错误。

<select id="selectByUser" resultType="User">
	select * from user
	<trim prefix="WHERE" prefixOverrides="AND">
	<if test="id != null">
		AND id = #{id}
	</if>
	<if test="username != null and username != ''">
		AND username = #{username}
	</if>
	<if test="password != null and password != ''">
		AND password = #{password}
	</if>
    </trim>
</select>

④choose、when、otherwise

<select id="selectByUP" resultType="User">
    select * from user
    <trim prefix="WHERE" prefixOverrides="AND">
        <choose>
            <when test="id != null">
                AND id = #{id}
            </when>
            <when test="username != null and username != ''">
                AND username = #{username}
            </when>
            <when test="password != null and password != ''">
                AND password = #{password}
            </when>
            <otherwise>
                <!--  此处设置默认执行的语句  -->
            </otherwise>
        </choose>
    </trim>
</select>

⑤set

        set元素遇到','会自动把','去掉。

<update id="updateUser" parameterType="User">
	UPDATE user
	<set>
		<if test="username != null and username != ''">
			username = #{username},
		</if>
		<if test="password != null and password != ''">
			password = #{password}
		</if>
	</set>
	WHERE id = #{id}
</update>

⑥foreach

        foreach元素是一个循环语句，它的作用是遍历集合，可以支持数组、List、Set接口。

<!--批量删除-->
int deleteByIds(@Param("ids")List<Integer> ids);

<delete id="deleteByIds" parameterType="User">
	delete from user
	WHERE id IN
	<foreach collection="ids" open="(" close=")" separator="," item="id">
		#{id}
	</foreach>
</delete>
    
<!--foreach的参数-->    
1.collection配置的是传递进来的参数名称。
2.item配置的是循环中当前的元素。
3.index配置的是当前元素在集合的位置下标。
4.open和 close配置的是以什么符号将这些集合元素包装起来。
5.separator是各个元素的间隔符。

<!--批量插入-->
int addManyUser(@Param("users")List<User> users);
    
<insert id="addManyUser" parameterType="User">
	insert into `user`(username, password)
	values
	<foreach collection="users" item="user" separator=",">
		(#{user.username}, #{user.password})
	</foreach>
</insert>

2.4 SQL片段

        针对经常使用的SQL语句，将其封装起来，方便使用。

<sql id="if-title-author">
	<trim prefix="WHERE" prefixOverrides="AND">
		<if test="id != null">
			AND id = #{id}
		</if>
		<if test="username != null and username != ''">
			AND username = #{username}
		</if>
		<if test="password != null and password != ''">
			AND password = #{password}
		</if>
	</trim>
</sql>
<sql id="param">
	id, username, password
</sql>

<!--使用sql片段，节省代码量-->
<select id="selectByUser" resultType="User">
	select <include refid="param"/> from user
	<include refid="if-title-author" />
</select>

2.5 多表联查

public class Employee implements Serializable {
    private static final Long serialVersionUID = 1L;
    private Integer id;
    private String name;
    //维护表关系
    private Dept dept;
}

public class Dept implements Serializable {
    private static final Long serialVersionUID = 1L;
    private Integer id;
    private String name;
    //维护表关系
    private List<Employee> employee;
}

#association--员工级联部门，多对一

#按查询嵌套

        一种级联查询的方式，会产生多个sql语句，第一个sql的查询语句结果会触发第二个查询语句。

<!--DeptMapper.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IDeptMapper">
    <resultMap id="DeptMap" type="Dept">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
    </resultMap>

    <sql id="param">
        `id`, `name`
    </sql>

    <select id="selectDept" resultMap="DeptMap">
        select <include refid="param"/>
        from dept where id = #{id}
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
    <!--  结果集映射  -->
    <!--  按查询嵌套 -->
    <resultMap id="EmployeeMap" type="Employee">
        <id column="id" property="id" />
        <result column="name" property="name"/>
        <!--    多表联查    -->
        <!--    通过向 DeptMapper.xml传递did，查询出类Dept的属性值，将其传递给Employee类的dept成员变量,property指代的是Employee中的dept成员变量名   -->
        <association property="dept" column="did" javaType="Dept"
                     select="com.cql.dao.IDeptMapper.selectDept">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </association>
    </resultMap>

    <sql id="param">
        `id`, `name`, `did`
    </sql>

    <!--  按查询嵌套 -->
    <select id="selectEmployee" resultMap="EmployeeMap">
        select <include refid="param" />
        from employee
    </select>
</mapper>

#按结果嵌套--常用

        使用复杂查询，在根据结果的字段进行对象的封装，本质只会发送一个sql。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
    <!--  结果集映射  -->
    <!--  按结果嵌套  -->
    <resultMap id="EmployeeMap" type="Employee">
        <id column="eid" property="id" />
        <result column="ename" property="name"/>
        <association property="dept" javaType="dept" >
            <id column="did" property="id"/>
            <result column="dname" property="name"/>
        </association>
    </resultMap>

    <sql id="param">
        `id`, `name`, `did`
    </sql>

	<!--  按结果嵌套  -->
    <select id="selectEmployee" resultMap="EmployeeMap" >
        select e.id eid,e.name ename, d.id did,d.name dname
        from employee e left join dept d
        on d.id = e.did
    </select>
</mapper>

#collection--部门级联员工，一对多

#按查询嵌套

<!--Employee.xml-->
<mapper namespace="com.cql.dao.IEmpolyeeMapper">
    <!--  结果集映射  -->
    <!--  按查询嵌套 -->
    <resultMap id="EmployeeMap2" type="Employee">
        <id column="eid" property="id" />
        <result column="ename" property="name"/>
    </resultMap>

    <sql id="param">
        `id`, `name`, `did`
    </sql>

    <!--  通过部门查询员工  -->
    <select id="selectEmployeeByDId" resultMap="EmployeeMap2">
        select id eid, name ename from employee where did = #{id}
    </select>

</mapper>

<mapper namespace="com.cql.dao.IDeptMapper">
    <resultMap id="DeptMap" type="Dept">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--  将其传递给Dept类的employee成员变量，返回结果是List，链表成员变量是Employee  -->
        <collection property="employee" javaType="List" ofType="Employee"
                    column="id" select="com.cql.dao.IEmpolyeeMapper.selectEmployeeByDId">
                    <id column="eid" property="id"/>
                    <result column="ename" property="name"/>
        </collection>
    </resultMap>

    <sql id="param">
        `id`, `name`
    </sql>

    <select id="selectALL" resultMap="DeptMap">
        select <include refid="param"/>
        from dept
    </select>

</mapper>

#按结果嵌套--常用

<mapper namespace="com.cql.dao.IDeptMapper">
    <resultMap id="DeptMap" type="Dept">
        <id column="did" property="id"/>
        <result column="dname" property="name"/>
        <!--  将其传递给Dept类的employee成员变量，返回结果是List，链表成员变量是Employee  -->
        <collection property="employee" javaType="List" ofType="Employee">
                    <id column="eid" property="id"/>
                    <result column="ename" property="name"/>
        </collection>
    </resultMap>

    <sql id="param">
        `id`, `name`
    </sql>

    <select id="selectDept" resultMap="DeptMap">
        select <include refid="param"/>
        from dept where id = #{id}
    </select>
    <select id="selectALL" resultMap="DeptMap">
        select d.id did, d.name dname, e.id eid, e.name ename
        from dept d
        left join employee e on d.id = e.did
    </select>

</mapper>

---

3 切换数据源

public class DruidDataSourceFactory implements DataSourceFactory {
    private Properties properties;
    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }

    @Override
    public DataSource getDataSource() {
        DruidDataSource dataSource= new DruidDataSource();
        dataSource.configFromPropety(properties);
        return dataSource;
    }
}

---

4 注解配置总结

<!--用于自动生成getter和setter-->
@Data
@AllArgsConstructor
@NoArgsConstructor


<!--多参数匹配问题-->
@Param
int addUserP(@Param("ids") Integer id, @Param("usernames") String username, @Param("passwords") String password);

<insert id="addUserP" parameterType="User">
    insert into user (id,username,password) values (#{ids},#{usernames},#{passwords})
</insert>


<!--接口中添加注解开发，无需使用XML文件配置命令，Java注解的表达力和灵活性十分有限，仅作为了解-->
@Select()
@Update()
@Delete()
@Insert()

@Insert("insert into user (id,username,password) values (#{ids},#{usernames},#{passwords})")
int addUserP(@Param("ids") Integer id, @Param("usernames") String username, @Param("passwords") String password);

---

5 XMl配置文件配置总结

<!--mybatisconfig.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--数据库账户密码-->
    <properties>
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/myself_ssm?serverTimezone=Asia/Shanghai&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;useServerPrepStmts=true&amp;cachePrepStmts=true"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </properties>

    <!--设置日志-->
    <settings>
<!--        <setting name="logImpl" value="SLF4J"/>-->
        <setting name="logPrefix" value="MyBatisSQL."/>
    </settings>

    <!--为每个返回类型起别名-->
    <typeAliases>
        <typeAlias type="com.cql.entity.User"  alias="User"/>
        <typeAlias type="com.cql.entity.Employee"  alias="Employee"/>
        <typeAlias type="com.cql.entity.Dept"  alias="Dept"/>
        <typeAlias type="com.cql.datasource.DruidDataSourceFactory" alias="DRUID"/>
    </typeAliases>

    <!--分页插件-->
    <plugins>
        <plugin interceptor="com.cql.plugin.ExamplePlugin">
            <property name="someProperty" value="100"/>
        </plugin>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
            <!-- <property name="param1" value="value1"/> -->
        </plugin>
    </plugins>

    <!--设置所使用的数据源-->
    <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>
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="DRUID">
                <property name="druid.driverClassName" value="${driver}"/>
                <property name="druid.url" value="${url}"/>
                <property name="druid.username" value="${username}"/>
                <property name="druid.password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <!--定义mapper资源，将配置文件注册到主配置文件中-->
    <mappers>
        <mapper resource="Mapper/UserMapper.xml"/>
        <mapper resource="Mapper/EmployeeMapper.xml"/>
        <mapper resource="Mapper/DeptMapper.xml"/>
    </mappers>
</configuration>

<!--UserMapper.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cql.Dao.IUserMapper">
    <select id="selectAll" resultType="User">
        select * from user
    </select>
    
    <!--#{password} 与实体类中参数的变量名要一致，resultType是返回类型-->
    <select id="selectByUser" resultType="User">
        select * from user where password = #{password} and username = #{username}
    </select>
    <select id="selectByMap" resultType="User">
        select * from user where password = #{password} and username = #{username}
    </select>

    <!--模糊查询-->
    <select id="selectLikeUN" resultType="User">
        select * from user where username like #{name}
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id,username,password) values (#{id},#{username},#{password})
    </insert>
</mapper>

---

6 依赖配置总结

<!--lombok依赖-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.16</version>
    <scope>provided</scope>
</dependency>

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

<!--数据库驱动依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.32</version>
</dependency>

<!--日志依赖-->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>${logback-classic.version}</version>
</dependency>

<!--druid数据源依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.9</version>
</dependency>