Mybatis-应用分析与最佳实践

Jdbc：

当java需要访问数据库数据交互时，一共需要几步进行连接。
1.在Pom文件引入Mysql驱动依赖
2.Class.forName注册驱动（在新版Mybatis中在getConnection中已经进行注册驱动，不用单独注册了）
3.获取一个Connection链接
4.创建一个Statement对象
5.execute()方法执行sql，execute方法返回一个ResultSet结果集
6.通过ResultSet获取数据，给POJO的属性赋值
7.最后关闭数据库相关资源，包括Result,Statement,Connection.关闭顺序与开启顺序相反

jdbc代码：

public class JdbcTest {
    @Test
    public void testJdbc() throws IOException {
        Connection conn = null;
        Statement stmt = null;
        Blog blog = new Blog();

        try {
            // 注册 JDBC 驱动
            // Class.forName("com.mysql.jdbc.Driver");

            // 打开连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/gp-mybatis", "root", "123456");

            // 执行查询
            stmt = conn.createStatement();
            String sql = "SELECT bid, name, author_id FROM blog where bid = 1";
            ResultSet rs = stmt.executeQuery(sql);

            // 获取结果集
            while (rs.next()) {
                Integer bid = rs.getInt("bid");
                String name = rs.getString("name");
                Integer authorId = rs.getInt("author_id");
                blog.setAuthorId(authorId);
                blog.setBid(bid);
                blog.setName(name);
            }
            System.out.println(blog);

            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    /**
     * 原生JDBC的批量操作方式 ps.addBatch()
     * @throws IOException
     */
    @Test
    public void testJdbcBatch() throws IOException {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            Long start = System.currentTimeMillis();
            // 打开连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true", "root", "123456");
            ps = conn.prepareStatement(
                    "INSERT into blog values (?, ?, ?)");

            for (int i = 1000; i < 101000; i++) {
                Blog blog = new Blog();
                ps.setInt(1, i);
                ps.setString(2, String.valueOf(i)+"");
                ps.setInt(3, 1001);
                ps.addBatch();
            }

            ps.executeBatch();
            // conn.commit();
            ps.close();
            conn.close();
            Long end = System.currentTimeMillis();
            System.out.println("cost:"+(end -start ) +"ms");
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

orm框架：

因为最早时候使用的全自动的orm框架，使用早年的hibernate，会将所有表和类进行完全映射，包括底层也有一些优化(一级缓存，二级缓存)等，但因为在业务比较复杂的时候无法进行对sql优化，后出现了mybatis框架，最早时候叫ibatis,从2010年后更名为Mybatis。Mybatis作者也加入了英雄联盟的团队。

Mybatis配置文件：

<?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"></properties>
    <settings>
        <!-- 打印查询语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />

        <!-- 控制全局缓存（二级缓存），默认 true-->
        <setting name="cacheEnabled" value="true"/>

        <!-- 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。默认 false  -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 当开启时，任何方法的调用都会加载该对象的所有属性。默认 false，可通过select标签的 fetchType来覆盖-->
        <setting name="aggressiveLazyLoading" value="true"/>
        <!--  Mybatis 创建具有延迟加载能力的对象所用到的代理工具，默认JAVASSIST -->
        <!--<setting name="proxyFactory" value="CGLIB" />-->
        <!-- STATEMENT级别的缓存，使一级缓存，只针对当前执行的这一statement有效 -->
        <!--
                <setting name="localCacheScope" value="STATEMENT"/>
        -->
        <setting name="localCacheScope" value="SESSION"/>
    </settings>

    <typeAliases>
        <typeAlias alias="blog" type="com.gupaoedu.domain.Blog" />
    </typeAliases>

<!--    <typeHandlers>
        <typeHandler handler="com.gupaoedu.type.MyTypeHandler"></typeHandler>
    </typeHandlers>-->

    <!-- 对象工厂 -->
<!--    <objectFactory type="com.gupaoedu.objectfactory.GPObjectFactory">
        <property name="gupao" value="666"/>
    </objectFactory>-->

<!--    <plugins>
        <plugin interceptor="com.gupaoedu.interceptor.SQLInterceptor">
            <property name="gupao" value="betterme" />
        </plugin>
        <plugin interceptor="com.gupaoedu.interceptor.MyPageInterceptor">
        </plugin>
    </plugins>-->

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/><!-- 单独使用时配置成MANAGED没有事务 -->
            <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="BlogMapper.xml"/>
        <mapper resource="BlogMapperExt.xml"/>
    </mappers>

</configuration>

Mybatis代码：

public class MyBatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void prepare() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     * 使用 MyBatis API方式
     * @throws IOException
     */
    @Test
    public void testStatement() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            Blog blog = (Blog) session.selectOne("com.gupaoedu.mapper.BlogMapper.selectBlogById", 1);
            System.out.println(blog);
        } finally {
            session.close();
        }
    }

    /**
     * 通过 SqlSession.getMapper(XXXMapper.class)  接口方式
     * @throws IOException
     */
    @Test
    public void testSelect() throws IOException {
        SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            Blog blog = mapper.selectBlogById(1);
            System.out.println(blog);
        } finally {
            session.close();
        }
    }

    @Test
    public void testSelectList() throws IOException {
        SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            Blog blog = new Blog();
            blog.setName("改行");
            List<Blog> list1 = new ArrayList<Blog>();
            List<Blog> list2 = new ArrayList<Blog>();
            list1 = mapper.selectBlogListIf(blog);
            list2 = mapper.selectBlogListChoose(blog);
        } finally {
            session.close();
        }
    }

    /**
     * 动态SQL批量插入
     * @throws IOException
     */
    @Test
    public void testInsert() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            Blog blog = new Blog();
            blog.setBid(1688);
            blog.setName("测试插入");
            blog.setAuthorId(1111);
            System.out.println(mapper.insertBlog(blog));
            session.commit();
        } finally {
            session.close();
        }
    }

    /**
     * 动态SQL批量删除
     * @throws IOException
     */
    @Test
    public void testDelete() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            List<Blog> list = new ArrayList<Blog>();
            Blog blog1 = new Blog();
            blog1.setBid(666);
            list.add(blog1);
            Blog blog2 = new Blog();
            blog2.setBid(777);
            list.add(blog2);
            mapper.deleteByList(list);
        } finally {
            session.close();
        }
    }

    /**
     * 单条更新
     * @throws IOException
     */
    @Test
    public void testUpdate() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            Blog newBlog = new Blog();
            newBlog.setBid(333);
            newBlog.setName("修改以后的名字");
            mapper.updateByPrimaryKey(newBlog);
            session.commit();
        } finally {
            session.close();
        }
    }

    /**
     * 动态SQL批量更新
     * @throws IOException
     */
    @Test
    public void testUpdateBlogList() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            List<Blog> list = new ArrayList<Blog>();
            Blog blog1 = new Blog();
            blog1.setBid(666);
            blog1.setName("newName666");
            blog1.setAuthorId(666666);
            list.add(blog1);
            Blog blog2 = new Blog();
            blog2.setBid(777);
            blog2.setName("newName777");
            blog2.setAuthorId(777777);
            list.add(blog2);
            mapper.updateBlogList(list);
            session.commit();
        } finally {
            session.close();
        }
    }

    /**
     * # 和 $ 的区别
     * @throws IOException
     */
    @Test
    public void testSelectByBean() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            Blog queryBean = new Blog();
            queryBean.setName("MySQL从入门到改行");
            List<Blog> blog = mapper.selectBlogByBean(queryBean);
            System.out.println("查询结果："+blog);
        } finally {
            session.close();
        }
    }

    /**
     * 逻辑分页
     * @throws IOException
     */
    @Test
    public void testSelectByRowBounds() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            int start = 0; // offset
            int pageSize = 5; // limit
            RowBounds rb = new RowBounds(start, pageSize);
            List<Blog> list = mapper.selectBlogList(rb); // 使用逻辑分页
            for(Blog b :list){
                System.out.println(b);
            }
        } finally {
            session.close();
        }
    }

    /**
     * Mapper.xml的继承性
     * @throws IOException
     */
    @Test
    public void testMapperExt() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapperExt mapper = session.getMapper(BlogMapperExt.class);
            Blog blog = mapper.selectBlogByName("MySQL从入门到改行");
            System.out.println(blog);
            // 继承了父Mapper的方法
            Blog blog1 = mapper.selectBlogById(1);
            System.out.println(blog1);
        } finally {
            session.close();
        }
    }

    /**
     * 一对一，一篇文章对应一个作者
     * 嵌套结果，不存在N+1问题
     */
    @Test
    public void testSelectBlogWithAuthorResult() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        BlogAndAuthor blog = mapper.selectBlogWithAuthorResult(1);
        System.out.println("-----------:"+blog);
    }

    /**
     * 一对一，一篇文章对应一个作者
     * 嵌套查询，会有N+1的问题
     */
    @Test
    public void testSelectBlogWithAuthorQuery() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        BlogAndAuthor blog = mapper.selectBlogWithAuthorQuery(1);
        System.out.println("-----------:"+blog.getClass());
        // 如果开启了延迟加载(lazyLoadingEnabled=true)，会在使用的时候才发出SQL
        // equals,clone,hashCode,toString也会触发延迟加载
        System.out.println("-----------调用toString方法:"+blog);
        System.out.println("-----------getAuthor:"+blog.getAuthor().toString());
        // 如果 aggressiveLazyLoading = true ，也会触发加载，否则不会
        //System.out.println("-----------getName:"+blog.getName());
    }

    /**
     * 一对多关联查询：一篇文章对应多条评论
     * @throws IOException
     */
    @Test
    public void testSelectBlogWithComment() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            BlogAndComment blog = mapper.selectBlogWithCommentById(1);
            System.out.println(blog);
        } finally {
            session.close();
        }
    }

    /**
     * 多对多关联查询：作者的文章的评论
     * @throws IOException
     */
    @Test
    public void testSelectAuthorWithBlog() throws IOException {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            BlogMapper mapper = session.getMapper(BlogMapper.class);
            List<AuthorAndBlog> authors = mapper.selectAuthorWithBlog();
            for (AuthorAndBlog author : authors){
                System.out.println(author);
            }
        } finally {
            session.close();
        }
    }

}

配置文件：

分为全局配置文件和mapper映射器。
全局配置：xml等基础配置文件，从头到尾都要用的
mapper映射器：针对增删改查的操作。