MyBatis（一）-映射

MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架，其使用简单的xml或注解用于配置和原始映射，将接口和Java的POJO映射成数据库中的记录；

所用的jar以及xml:

链接：https://pan.baidu.com/s/11eoHPbvS8mNzkAgolFYqoQ
提取码：ze4i

命令行自动生成映射文件：java -jar mybatis1.jar -configfile generator.xml -overwrite

MyBatis持久化步骤：

• 持久化类PO和编写持久化操作的Mapper.xml（对应的Mapper接口），在其中定义要执行的SQL语句；
• 获取SqlSessionFactory;
• 获取SqlSession;
• 用面向对象的方式操作数据库；
• 关闭事务，关闭SqlSession;

SqlSessionFactory是单个数据库映射关系经过编译后的内存镜像，且线程安全；

sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

SqlSession是执行持久化的操作的对象，类似于JDBC的Connection,是应用程序与持久存储层之间执行交互操作的一个单线程对象，其方法有：

语句执行方法：	这些方法被用来执行定义在 SQL 映射的 XML 文件中的 SELECT,INSERT,UPDA E T 和 DELETE 语句。它们都会自行解释,每一句都使用语句的 ID 属性和参数对象,参数可以 是原生类型(自动装箱或包装类) ,JavaBean,POJO 或 Map。 namespace + id : 即：namespace 定位到唯一的mapper映射文件，id 定位到这个mapper映射文件的指定的sql语句：
<T> T selectOne(String statement, Object parameter)
<E> List<E> selectList(String statement, Object parameter)
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey)
int insert(String statement, Object parameter)
int update(String statement, Object parameter)
int delete(String statement, Object parameter)
<T> T selectOne(String statement)	selectOne 和 selectList 的不同仅仅是 selectOne 必须返回一个对象。 如果多余一个, 或者 没有返回 (或返回了 null) 那么就会抛出异常。 , 如果你不知道需要多少对象, 使用 selectList。 如果你想检查一个对象是否存在,那么最好返回统计数(0 或 1) 。因为并不是所有语句都需 要参数,这些方法都是有不同重载版本的,它们可以不需要参数对象。
<E> List<E> selectList(String statement)
<K,V> Map<K,V> selectMap(String statement, String mapKey)
int insert(String statement)
int update(String statement)
int delete(String statement)
<E> List<E> selectList (String statement, Object parameter, RowBounds rowBounds)	它们允许你限制返回行数的范围,或者提供自定 义结果控制逻辑,这通常用于大量的数据集合。
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowbounds)
void select (String statement, Object parameter, ResultHandler handler)
void select (String statement, Object parameter, RowBounds rowBounds, ResultHandler handler)
事务控制方法：	控制事务范围有四个方法。如果是自动提交或正在使用外部事务管理器,则不需要这四个方法。若使用 JDBC 事务管理员,由 Connection 实例来控制,则需要这四个方法； 默认情况下 MyBatis 不会自动提交事务, 除非它侦测到有插入, 更新或删除操作改变了 数据库。如果你已经做出了一些改变而没有使用这些方法,那么你可以传递 true 到 commit 和 rollback 方法来保证它会被提交(注意,你不能在自动提交模式下强制 session,或者使用 了外部事务管理器时) 。很多时候你不用调用 rollback(),因为如果你没有调用 commit 时 MyBatis 会替你完成。然而,如果你需要更多对多提交和回滚都可能的 session 的细粒度控 制,你可以使用回滚选择来使它成为可能。
void commit()
void commit(boolean force)
void rollback()
void rollback(boolean force)
void clearCache()	清理 Session 级的缓存
void close()	确保 SqlSession 被关闭
<T> T getMapper(Class<T> type)	使用映射器，返回Mapper接口的代理对象
Connection getConnection()	获得JDBC的数据库连接对象

MyBatis初始化基本过程：

sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

SqlSessionFactoryBuilder根据传入的数据流生成Configuration对象，然后根据Configuration对象创建默认的SqlSessionFactory实例；

简单的xml-CRUD：

UserMapper.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="com.answer.mapper.UserMapper">
    <select id="selectUserById" parameterType="int" resultType="com.answer.model.User" resultMap="userResultMap">
        select * from tb_user where id= #{id}
    </select>
    <insert id="insert" parameterType="com.answer.model.User">
        insert into tb_user() values (#{id},#{username},#{loginname},
        #{pword},#{phone},#{address})
    </insert>
    <delete id="delete" parameterType="int">
        delete from tb_user where id=#{id}
    </delete>
    <update id="update" parameterType="com.answer.model.User">
        update tb_user
        set username =#{username},loginname=#{loginname},
        pword=#{pword},phone=#{phone},address=#{address}
        where id=#{id}
    </update>
</mapper>

UserMapper接口：

public interface UserMapper {
    User selectUserById(int id);
    List<Order> selectOrderByUserId(int id);
    int insert(User user);
    int delete(int id);
    int update(User user);
    User selectUserByIdDy(int id);
}

测试：

private SqlSessionFactory sqlSessionFactory;
    public UserImpl(){
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

    }
    @Test
    public void query(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            User user=sqlSession.selectOne("selectUserById",1);
            System.out.println(user.getOrders());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
    @Test
    public void insert(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            User user=new User();
            user.setId(2);
            user.setUsername("刘");
            user.setLoginname("liu");
            user.setPword("123123");
            user.setPhone("98474929");
            user.setAddress("兰州");
            sqlSession.selectOne("insert",user);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
    @Test
    public void del(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            sqlSession.selectOne("delete",2);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
    @Test
    public void update(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{

            User user=new User();
            user.setId(2);
            user.setUsername("刘");
            user.setLoginname("liuyf");
            user.setPword("123123000");
            user.setPhone("9847492999");
            user.setAddress("兰州安宁");
            sqlSession.selectOne("update",user);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

xml关联映射：

一对一：Card和Person

<?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.answer.mapper.CardMapper">
    <select id="selectCardById" parameterType="int" resultType="com.answer.model.Card">
        select * from tb_card where id= #{id}
    </select>
</mapper>

<mapper namespace="com.answer.mapper.PersonMapper">
    <select id="selectPersonById" parameterType="int" resultMap="personMapper">
        select * from tb_person where id= #{id}
    </select>
    <resultMap id="personMapper" type="com.answer.model.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"></result>
        <result property="sex" column="sex"></result>
        <result property="age" column="age"></result>
        <!-- 一对一关联映射-->
        <association property="card" column="card_id"
                     select="com.answer.mapper.CardMapper.selectCardById"
                     javaType="com.answer.model.Card">
        </association>
    </resultMap>
</mapper>

测试：

private SqlSessionFactory sqlSessionFactory;
    public PersonImpl(){
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

    }
    @Test
    public void query(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            /*Card card=sqlSession.selectOne("selectCardById",1);
            System.out.println(card.getCard());*/
            Person person=sqlSession.selectOne("selectPersonById",1);
            System.out.println(person.getName()+":"+person.getCard().getCard());
            /*PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
            Person person=personMapper.selectPersonById(1);*/
            //System.out.println(person.getName()+":"+person.getCard().getCard());
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

一对多：Class和Student

<mapper namespace="com.answer.mapper.ClassMapper">
    <select id="selectClassById"  parameterType="int" resultMap="classResultMapper">
        select * from tb_class where id= #{id}
    </select>
    <resultMap id="classResultMapper" type="com.answer.model.Class">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="name" column="name"/>
        <!-- 一对多关联映射-->
        <collection property="student" javaType="ArrayList"
                    column="id" ofType="com.answer.model.Student"
                    select="com.answer.mapper.StudentMapper.selectStudentByClassId"
                    fetchType="lazy">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
        </collection>
    </resultMap>
</mapper>

<mapper namespace="com.answer.mapper.StudentMapper">
    <select id="selectStudentById" parameterType="int" resultMap="studentResultMapper">
  	SELECT * FROM tb_clazz c,tb_student s
  	WHERE c.id = s.clazz_id
  	 AND s.id = #{id}
  </select>
    <select id="selectStudentByClassId"  parameterType="int" resultMap="studentResultMapper">
        select * from tb_student where class_id= #{id}
    </select>
    <resultMap id="studentResultMapper" type="com.answer.model.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <!-- 多对一关联映射-->
        <association property="aClass" column="class_id"
                     select="com.answer.mapper.ClassMapper.selectClassById"
                     javaType="com.answer.model.Class">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>
</mapper>

测试：

private SqlSessionFactory sqlSessionFactory;
    public ClassImpl(){
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

    }
    @Test
    public void query(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            Class c=sqlSession.selectOne("selectClassById",1);
            System.out.println(c.getName());
            List<Student> list=c.getStudent();
            list.forEach(student -> System.out.println(student.getName()+":"+student.getaClass().getName()));
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

多对多：Order和Article(需建立中间表item)

<mapper namespace="com.answer.mapper.OrderMapper">

    <resultMap id="orderResultMap" type="com.answer.model.Order">
        <id property="id" column="oid"/>
        <result property="code" column="code"/>
        <result property="total" column="total"/>
        <association property="user" column="user_id"
                     select="com.answer.mapper.UserMapper.selectUserById"
                     javaType="com.answer.model.User">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="loginname" column="loginname"/>
            <result property="pword" column="pword"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
        <collection property="articles" javaType="ArrayList"
                    column="id" ofType="com.answer.model.Article"
                    select="com.answer.mapper.ArticleMapper.selectArticleByOrderId"
                    fetchType="lazy">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
            <result property="remark" column="remark"/>
        </collection>
    </resultMap>
    <!-- 注意，如果查询出来的列同名，例如tb_user表的id和tb_order表的id都是id，同名，需要使用别名区分 -->
    <select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
  	SELECT u.*,o.id AS oid,CODE,total,user_id
  	 FROM tb_user u,tb_order o
  	WHERE u.id = o.user_id
  	 AND o.id = #{id}
  </select>

    <!-- 根据userid查询订单 -->
    <select id="selectOrderByUserId" parameterType="int" resultType="com.answer.model.Order">
  	SELECT * FROM tb_order WHERE user_id = #{id}
  </select>

</mapper>

<mapper namespace="com.answer.mapper.ArticleMapper">

    <select id="selectArticleByOrderId" parameterType="int" resultMap="articleResultMapper">
  	SELECT * FROM tb_article WHERE id IN (
		SELECT article_id FROM tb_item WHERE order_id = #{id}
	)
  </select>
	<select id="selectArticleById"  parameterType="int" resultMap="articleResultMapper">
        select * from tb_article where id= #{id}
    </select>
	<resultMap id="articleResultMapper" type="com.answer.model.Article">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="price" column="price"/>
		<result property="remark" column="remark"/>
	</resultMap>
</mapper>

测试:

private SqlSessionFactory sqlSessionFactory;
    public OrderImpl(){
        sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));

    }
    @Test
    public void query(){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            /*List<Order> orders=sqlSession.selectList("selectOrderByUserId",1);
            orders.forEach(order -> System.out.println(order.getArticles()));
            for(Order order:orders){
                System.out.println(order.getArticles());
            }*/
            Order order=sqlSession.selectOne("selectOrderById",1);
            System.out.println(order.getUser().getUsername());
            List<Article> articles=order.getArticles();
            articles.forEach(article -> System.out.println(article.getName()));
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

简单的注解-CRUD：

UserMapper接口：

@Select("select * from tb_user where id=#{id}")
    @Results({
         @Result(id = true,property = "id",column = "id"),
            @Result(property ="username",column = "username"),
            @Result(property ="loginname",column = "loginname"),
            @Result(property ="pword",column = "pword"),
            @Result(property ="phone",column = "phone"),
            @Result(property ="address",column = "address"),
    })
    User selectUserById(int id);

@Insert("insert into tb_user() values(#{id},#{username},#{loginname},#{pword},#{phone},#{address})")
    int insert(User user);

    @Delete("delete from tb_user where id=#{id}")
    int delete(int id);

    @Update("update tb_user set username =#{username},loginname=#{loginname}," +
            "        pword=#{pword},phone=#{phone},address=#{address}" +
            "        where id=#{id}")
    int update(User user);

测试同xml;

一对一：Card和Person

CardMapper接口:

@Select("select * from tb_card where id=#{id}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "card",column = "card"),

    })
    Card selectCardById(int id);

PersonMapper接口：

@Select("select * from tb_person where id=#{id} ")
    @Results({
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "name",column = "name"),
            @Result(property = "sex",column = "sex"),
            @Result(property = "age",column = "age"),
            @Result(property = "card",column = "card_id",
            one = @One(select = "com.answer.mapper.CardMapper.selectCardById"))
    })
    Person selectPersonById(int id);

一对多：Class和Student

public interface ClassMapper {
    @Select("select * from tb_class where id=#{id}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property ="code",column = "code"),
            @Result(property ="name",column = "name"),
            @Result(property = "student",column = "id",
            many = @Many(select = "com.answer.mapper.StudentMapper.selectStudentByClassId"))
    })
    Class selectClassById(int id);
}

public interface StudentMapper {
    @Select("select * from tb_student where id=#{id}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property ="name",column = "name"),
            @Result(property ="sex",column = "sex"),
            @Result(property ="age",column = "age"),
            @Result(property = "aClass",column = "class_id",
                    one = @One(select = "com.answer.mapper.ClassMapper.selectClassById"))
    })
    Student selectStudentById(int id);

    @Select("select * from tb_student where class_id=#{id}")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property ="name",column = "name"),
            @Result(property ="sex",column = "sex"),
            @Result(property ="age",column = "age")
    })
    List<Student> selectStudentByClassId(int id);
}

多对多：Order和Article(需建立中间表item)

public interface OrderMapper {
    @Select("select * from tb_order where id=#{id}")
    @Results({
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "code",column = "code"),
            @Result(property = "total",column = "total"),
            @Result(property = "articles",column = "id",
            many = @Many(select = "com.answer.mapper.ArticleMapper.selectArticleByOrderId"))
    })
    Order selectOrderById(int id);

}

public interface ArticleMapper {
    @Select("select * from tb_article where id in (select article_id from tb_item where order_id=#{id})")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "name",column = "name"),
            @Result(property = "price",column = "price"),
            @Result(property = "remark",column = "remark")
    })
    List<Article> selectArticleByOrderId(int id);

    
}