mybatis

支持普通 SQL 查询，存储过程和高级映射的ORM持久层框架。以一 个 SqlSessionFactory 对象的实例为核心。

---

从 XML 中构建 SqlSessionFactory

• configuration 配置
• properties 属性
• settings 设置
• typeAliases 类型命名
• typeHandlers 类型处理器
• objectFactory 对象工厂
• plugins 插件
• environments 环境
• environment 环境变量
• transactionManager 事务管理器
• dataSource 数据源
• 映射器

String resource = "org/mybatis/example/Configuration.xml";
Reader reader = Resources.getResourceAsReader(resource);
sqlMapper = new SqlSessionFactoryBuilder().build(reader);

---

mybatis.cfg.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文件 -->
    <properties resource="datasources.properties"/>

    <!-- 给我们的JAVABEAN定义别名 -->
    <typeAliases>
    <!-- 一个一个的告知，很麻烦
        <typeAlias type="com.lovo.my.beans.UserBean" alias="UserBean"/>
         -->
         <!-- 自动扫描包，将包内的所有JAVA类的类名，来作为该类的类别名 -->
        <package name="com.lovo.my.beans"></package>
    </typeAliases>

    <!-- 定义mybatis运行环境,default用于设置默认环境 -->
    <environments default="development">
        <environment id="development">
        <!-- transactionManager主要用于设置事务管理器，mybatis提供了2种事物管理器，
        分别是:JDBC,MANAGED ,JDBC代表是直接使用JDBC的提交或回滚来处理事物
        MANAGED 代表使用外部容器，如Spring等容器来操作事物 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI 
            POOLED 支持JDBC数据源连接池
            UNPOOLD 不支持数据源连接池
            JNDI 支持外部容器连接池  -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.name}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!-- 
        <mapper resource="com/lovo/my/dao/IUserMapper.xml"/>
         -->
         <!-- 自动扫描包，告知包内的接口与SQL映射文件 -->
        <package name="com.lovo.my.dao"/>
    </mappers>

</configuration>

---

SQL 映射的 XML 文件

• cache - 配置给定命名空间的缓存。
• cache-ref – 从其他命名空间引用缓存配置。
• resultMap – 最复杂，也是最有力量的元素，用来描述如何从数据库结果集中来加载你的对象。
• sql – 可以重用的 SQL 块，也可以被其他语句引用。
• insert – 映射插入语句
• update – 映射更新语句
• delete – 映射删除语句
• select – 映射查询语句

<?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.lovo.my.dao.IUserMapper">

    <!-- 自定义映射关系 -->
    <resultMap id="userMap" type="UserBean">
        <result property="id" column="id" javaType="java.lang.Integer"/>
        <result property="userName" column="user_name" javaType="java.lang.String"/>
        <result property="password" column="password" javaType="java.lang.String"/>
        <result property="salary" column="salary" javaType="java.lang.Double"/>
    </resultMap>

    <!-- insert 编写一个新增方法 -->
    <insert id="saveUserBean" parameterType="UserBean" useGeneratedKeys="true" keyProperty="u.id">
        insert into t_user (user_name,password,sex,salary) values (#{u.userName},#{u.password},#{u.sex},#{u.salary})
    </insert>

    <insert id="batchAddUserBean" parameterType="java.util.List">
        insert into t_user (user_name,password,sex,salary) values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName},#{user.password},#{user.sex},#{user.salary})
        </foreach>
    </insert>



    <!-- update 编写一个修改方法，在方法中，除了Id属性是必填以外，其他属性均可不填 -->
    <update id="updateUserBean">
        update t_user set user_name = #{u.userName},password=#{u.password},sex=#{u.sex},salary=#{u.salary} where id= #{id}
    </update>

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

    <!-- 批量删除的语法是： delete from t_user id in (,,,,) -->
    <delete id="batchDeleteUserBean">
        delete from t_user where id in 
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>  
    </delete>




    <select id="getUserBeanByNameAndPassword" resultMap="userMap">
        select * from t_user where user_name = #{userName} and password = #{password}
    </select>

    <select id="findAllUserBean" resultMap="userMap">
        select * from t_user
    </select>

    <select id="selectCountUserBeanByCondition" resultType="int">
        select count(*) from t_user where 1=1
        <!-- <if test="userName != null and userName != ''">
            and user_name like '%${userName}%'
        </if> 

        <if test ="sex != null and sex != ''">
            and sex = #{sex}
        </if> -->

        <include refid="item"/>
    </select>

    <select id="selectUserBeanByCondition" resultMap="userMap">
        select * from t_user where 1=1
        <!-- <if test="userName != null and userName != ''">
            and user_name like '%${userName}%'
        </if> 

        <if test ="sex != null and sex != ''">
            and sex = #{sex}
        </if> -->

        <include refid="item"/>
        limit ${index},${rows}
    </select>

    <sql id="item">
        <if test="userName != null and userName != ''">
            and user_name like '%${userName}%'
        </if> 

        <if test ="sex != null and sex != ''">
            and sex = #{sex}
        </if>
    </sql>

</mapper>

one2one

关联查询 方式一

    <resultMap id="wifeAndHusbandMap" type="WifeBean">
        <result property="id" column="id" javaType="java.lang.Integer"/>
        <result property="wifeName" column="wife" javaType="java.lang.String"/>

        <association property="husband" javaType="HusbandBean">
            <result property="id" column="hid" javaType="java.lang.Integer"/>
            <result property="husbandName" column="hhusband" javaType="java.lang.String"/>
        </association>
    </resultMap>

    <select id="queryWifeAndHusband" resultMap="wifeAndHusbandMap">
        select w.id as wid,w.wife as wwife,h.id as hid,h.husband as hhusband from t_wife as w,t_husband as h where h.id = w.fk_husband_id and w.id = #{id}
    </select>

关联查询 方式二

    <resultMap id="wifeAndHusbandMap" type="WifeBean">
        <result property="id" column="id" javaType="java.lang.Integer"/>
        <result property="wifeName" column="wife" javaType="java.lang.String"/>

        <!-- 所有的关联查询中，column="" 这一部分，一定是后面方法所需要的 -->
        <association property="husband" column="fk_husband_id" select="com.lovo.my.dao.IHusbandMapper.getHusbandBeanById" javaType="HusbandBean"/>
    </resultMap>

    <select id="queryWifeAndHusband" resultMap="wifeAndHusbandMap">
        select * from t_wife where id = #{id}
    </select>

one2many

<?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.even.mapper.one2many.ILockMapper">

    <resultMap type="LockBean" id="LockIncludeKeysMap">
        <result property="id" column="id"/>
        <result property="lockName" column="lock_name"/>
        <collection property="keys" column="id" select="com.even.mapper.one2many.IKeyMapper.findByLockId"></collection>
    </resultMap>

    <insert id="save">
        insert into t_lock(lock_name) values(#{lock.lockName})
    </insert>

    <select id="findById" resultType="LockBean">
        select id,lock_name as lockName from t_lock where id = #{id}
    </select>

    <select id="findByIdIncludeKeys" resultMap="LockIncludeKeysMap">
        select id,lock_name from t_lock where id = #{id}
    </select>

</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.even.mapper.one2many.IKeyMapper">

    <resultMap type="KeyBean" id="KeyIncludeLockMap">
        <result property="id" column="id"/>
        <result property="keyName" column="key_name"/>
        <association property="lock" column="fk_lock_id" select="com.even.mapper.one2many.ILockMapper.findById"></association>
    </resultMap>

    <insert id="batchSave">
        insert into t_key(key_name,fk_lock_id) values
        <foreach collection="list" item="key" separator=",">
            (#{key.keyName},#{key.lock.id})
        </foreach>
    </insert>

    <select id="findByIdIncludeLock" resultMap="KeyIncludeLockMap">
        select id,key_name,fk_lock_id from t_key where id = #{id}
    </select>

</mapper>

many2many

<?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.even.mapper.many2many.IStudentMapper">

    <resultMap type="StudentBean" id="StudentMap">
        <result property="id" column="id"/>
        <result property="studentName" column="student_name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <collection property="courses" column="id" select="findCourseByStudentId"></collection>
    </resultMap>

    <select id="findByIdIncludeCourses" resultMap="StudentMap">
        select id,student_name,sex,age from t_student where id = #{id}
    </select>

    <select id="findCourseByStudentId" resultType="CourseBean">
        select id,course_name as courseName from t_course where id in(select fk_course_id from t_student_course where fk_student_id = #{id})
    </select>

</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.even.mapper.many2many.ICourseMapper">

    <resultMap type="CourseBean" id="CourseMap">
        <result property="id" column="id"/>
        <result property="courseName" column="course_name"/>
        <collection property="students" column="id" select="findStudentByCourseId"></collection>
    </resultMap>

    <select id="findByIdIncludeStudents" resultMap="CourseMap">
        select id,course_name from t_course where id = #{id}
    </select>

    <select id="findStudentByCourseId" resultType="StudentBean">
        select id,student_name as studentName,sex,age from t_student where id in(select fk_student_id from t_student_course where fk_course_id = #{id})
    </select>

</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.even.mapper.many2many.IStudentCourseMapper">

    <insert id="choiceCourse">
        insert into t_student_course(fk_student_id,fk_course_id) values
        <foreach collection="list" item="sc" separator=",">
            (#{sc.studentBean.id},#{sc.courseBean.id})
        </foreach>
    </insert>

</mapper>