mybatis的介绍

mybatis介绍官网： http://www.mybatis.org/mybatis-3/

认识 MyBatis ，从以下三点认识，（what,advantage,how）

1.what：

MyBatis is a first class persistence framework with support for custom SQL, stored

procedures and advanced mappings.是什么

mybatis 是一个支持动态化sql,存储过程，和mapper映射的持久化框架。

2.advantage

MyBatis eliminates almost all of the JDBC code and manual setting of parameters and

retrieval of results.

mabatis消除了几乎所有的JDBC代码和手动设置参数和检索结果。

3.how

MyBatis can use simple XML or Annotations for configuration and map primitives, Map

interfaces and Java POJOs (Plain Old Java Objects) to database records.

mybatis可以使用简单的XML或注释,可以将数据库中的对象与Java实体类进行转换。

 

TestMapper testMapper = getSqlSession().getMapper(TestMapper.class);（动词，查询）
Test test = testMapper.selectByPrimaryKey(1);（名词，返回的结果映射成对象）

使用mybaits两个概念

编程式的：写代码（一次交互就是一个会话），跟框架打交道一般都是configration完成，

sqlSessionFactory-->SqlSession-->testMapper--->connection--->DB

public static SqlSession getSqlSession() throws FileNotFoundException {
    //配置文件F:\workSpace\gupaoedu-mybatis-vip-2.1\src\main\java\com\gupaoedu\mybatis
    InputStream configFile = new FileInputStream(
            "F:\\workSpace\\gupaoedu-mybatis-vip-2.1\\src\\main\\java\\com\\gupaoedu\\mybatis\\demo\\mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configFile);
    //加载配置文件得到SqlSessionFactory
    return sqlSessionFactory.openSession();
}

public static void main(String[] args) throws FileNotFoundException {
    TestMapper testMapper = getSqlSession().getMapper(TestMapper.class);
    Test test = testMapper.selectByPrimaryKey(1);
    /**
     * 1. 找到SQL
     * 2. 参数设置
     * 3. 执行
     * 4. 结果映射
     */


}

 

集成式：通过容器管理，集成到spring

@Configuration
@ComponentScan(basePackages = "com.gupao.dal")
@PropertySource("classpath:config/app.properties")
public class DataSourceConfig {
    @Value("${jdbc.driverClassName}")
    private String driverClassName;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    @Bean
    public Filter statFilter() {
        StatFilter statFilter = new StatFilter();
        statFilter.setSlowSqlMillis(2000);
        statFilter.setLogSlowSql(true);
        return statFilter;
    }

    @Bean(name = "dataSource", initMethod = "init", destroyMethod = "close")
    public DataSource dataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setConnectionProperties("config.decrypt=true");
//        dataSource.setFilters("stat,config");
        dataSource.setFilters("stat");
        dataSource.setMaxActive(20);
        dataSource.setInitialSize(20);
        dataSource.setMaxWait(60000);
        dataSource.setMinIdle(1);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("SELECT 'x'");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setPoolPreparedStatements(false);
        dataSource.setMaxOpenPreparedStatements(20);
        dataSource.setProxyFilters(Arrays.asList(statFilter()));
        dataSource.setConnectionErrorRetryAttempts(5);
        return dataSource;
    }

    /**
     * 必须加上static
     */
    @Bean
    public static PropertySourcesPlaceholderConfigurer loadProperties() {
        PropertySourcesPlaceholderConfigurer configurer = new PropertySourcesPlaceholderConfigurer();
        return configurer;
    }

}

/**
 * Created by James
 * Description:
 */
@Configuration
@MapperScan(basePackages = "com.gupao.dal.dao")
@EnableTransactionManagement(proxyTargetClass = true)
public class MybatisConfig {
    @Autowired
    @Qualifier("dataSource")
    public DataSource dataSource;


    @Lazy(false)
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory localSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
//        sqlSessionFactoryBean.setTypeHandlers(new TypeHandler[]{new TestTypeHandle()});
//        sqlSessionFactoryBean.setTypeHandlersPackage("com.gupao.dal.typehandles");
//        sqlSessionFactoryBean.setPlugins(new Interceptor[]{new TestPlugin()});
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor()});
        SqlSessionFactory factory = sqlSessionFactoryBean.getObject();
        //lazy loading switch
        factory.getConfiguration().setLazyLoadingEnabled(true);
        factory.getConfiguration().setAggressiveLazyLoading(false);
        factory.getConfiguration().setProxyFactory(new CglibProxyFactory());
        return factory;
    }

    private PageInterceptor pageInterceptor() {
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.put("helperDialect", "mysql");
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }

    @Primary
    @Lazy(false)
    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(localSessionFactoryBean(), ExecutorType.SIMPLE);
    }

    @Lazy(false)
    @Bean(name = "batchSst")
    public SqlSessionTemplate batchSst() throws Exception {
        return new SqlSessionTemplate(localSessionFactoryBean(), ExecutorType.BATCH);
    }

    @Bean(name = "txManager")
    public DataSourceTransactionManager dataSourceTransactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}
工作当中的使用方式方法：数据库的设计，类抽象，每个需求都会涉及到数据库的设计，业务的抽象，类的提取

一般mybaits三步骤：第一步分析业务，第二步:创建表结构，第三步：使用mybaits逆向工程生成文件

scope 作用域：

 

1. Mapper在spring管理下其实是单例，为什么可以是一个
单例？ SCOPE -> application

2. MyBatis在Spring集成下没有mapper的xml文件会不会
报错，为什么？

Mapper 的 xml 和 annotation 形式

1. 兼容？ 兼容形式  互补

@Select("select * from test where id = 2")
  Test selectByPrimaryKey(Integer id);

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from test
  where id = #{id,jdbcType=INTEGER}
</select>

测试方法：

 @Test
    public void select() {
        System.out.println(mapper.selectByPrimaryKey(1));

    }

报错

java.lang.IllegalArgumentException: Mapped Statements collection already contains value for com.gupao.dal.dao.TestMapper

2. Pros vs cons 

typeHandler的介绍，最主要支持mybatis中的参数和实体类中的参数一一映射

https://www.cnblogs.com/yulinfeng/p/5991170.html

①第一步：第一个一个testHandler

@MappedJdbcTypes(JdbcType.VARCHAR)
public class TestTypeHandle extends BaseTypeHandler<String> {
    public TestTypeHandle() {
    }

    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter+"with handlers");
    }

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }


}

②第二步，注册定义的testHandler

 sqlSessionFactoryBean.setTypeHandlersPackage("com.gupao.dal.typehandles");

③引用testHandler

insert into test (id, nums, name
)
values (#{id,jdbcType=INTEGER}, #{nums,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR,typeHandler=com.gupao.dal.typehandlers.TestTypeHandle}
)

<result column="name" jdbcType="VARCHAR" property="name" typeHandler="com.gupao.dal.typehandlers.TestTypeHandle"/>

plugins：拦截器

①定义一个拦截器

@Intercepts({@Signature(type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class TestPlugin implements Interceptor {
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
        System.out.println(String.format("plugin output sql = %s , param=%s", boundSql.getSql(),boundSql.getParameterObject()));
        return invocation.proceed();
    }
mybaits的一级缓存和二级缓存：
https://blog.csdn.net/zhongzh86/article/details/50019511
分页,批量操作，联合查询

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {

    }
}

②怎么用，注册

 sqlSessionFactoryBean.setPlugins(new Interceptor[]{new TestPlugin()})分页：pageHelper

逻辑分页：

org.apache.ibatis.executor.resultset.DefaultResultSetHandler#handleRowValuesForSimpleResultMap

针对返回的结果进行分页

private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
    if(rs.getType() != 1003) {
        if(rowBounds.getOffset() != 0) {
            rs.absolute(rowBounds.getOffset());
        }
    } else {
        for(int i = 0; i < rowBounds.getOffset(); ++i) {
            rs.next();
        }
    }

物理分页：

①select limit 0,10

②mybatis的page分页插件：

sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor()})

 @Test
    public void pagination分页插件() {
        PageHelper.startPage(1,20);
//        List<com.gupao.dal.dao.Test> tests =  sqlSessionTemplate.selectList("selectAll",null,new RowBounds(2,10));
//        List<com.gupao.dal.dao.Test> tests =  mapper.selectAll();
        PageInfo<Object> page =PageHelper.startPage(1, 10).doSelectPageInfo(new ISelect() {
            public void doSelect() {
                mapper.selectAll();
            }
        });
        log.info(" {}", page);
    }

批量插入：（一般list集合会有限制）

①第一种
<insert id="insertBatch" parameterType="list">
  insert into test (id, nums, name
  )
  values
  <foreach collection="list" item="item" separator=",">
  (#{item.id,jdbcType=INTEGER}, #{item.nums,jdbcType=INTEGER}, #{item.name,jdbcType=VARCHAR}
  )
  </foreach>
</insert>

②第二种

@Test
@Transactional
public void insertBatchExType() {
    SqlSession session = sqlSessionTemplate
            .getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
    TestMapper testMapper = session.getMapper(com.gupao.dal.dao.TestMapper.class);
    long start = System.currentTimeMillis();
    for (int i = 0; i < 10; i++) {
        com.gupao.dal.dao.Test test = new com.gupao.dal.dao.Test();
        test.setName(TestEnum.A.name());
        test.setNums(i);
        mapper.insert(test);
        if (i % 5 == 0 && i != 0) {
            session.commit();
            session.clearCache();
        }
    }
    log.info("cost {}ms", System.currentTimeMillis() - start);
}

嵌套结果查询和嵌套查询

一对一的嵌套查询

<!-- 嵌套查询 start  1：1-->
<resultMap id="blogResultMap" type="com.gupao.dal.resultmap.BlogResultMap">
  <id column="bid" jdbcType="INTEGER" property="bid" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <association property="author" column="author_id"
               select="com.gupao.dal.dao.AuthorMapper.selectByPrimaryKey" />
</resultMap>

<select id="selectBlogAuthor" resultMap="blogResultMap" parameterType="int">
  select
  <include refid="Base_Column_List" />
  from blog
  where bid = #{id,jdbcType=INTEGER}
</select>
<!-- 嵌套查询 end -->

<!-- 嵌套查询 1:N start  -->（存在一对多的问题）
针对查询性能问题，可以通过懒加载
factory.getConfiguration().setLazyLoadingEnabled(true);
factory.getConfiguration().setAggressiveLazyLoading(false);
factory.getConfiguration().setProxyFactory(new CglibProxyFactory());

<resultMap id="blogPostsResultMap" type="com.gupao.dal.resultmap.BlogPostsResultMap">
  <id column="bid" jdbcType="INTEGER" property="bid" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <result column="author_id" jdbcType="INTEGER" property="authorId" />
  <collection column="bid" property="posts"
              select="selectByBlogId" ofType="com.gupao.dal.dao.Posts" >
  </collection>
</resultMap>

<select id="selectByBlogId" parameterType="java.lang.Integer"
                resultType="com.gupao.dal.dao.Posts">
  select
  pid,post_name as postName,blog_id as blogId
  from posts
  where blog_id = #{id,jdbcType=INTEGER}
</select>

<select id="selectBlogPosts" resultMap="blogPostsResultMap" parameterType="int">
  select
  <include refid="Base_Column_List" />
  from blog
  where bid = #{id,jdbcType=INTEGER} order by bid asc
</select>

<select id="selectBlogPostsList" resultMap="blogPostsResultMap">
  select
  <include refid="Base_Column_List" />
  from blog where bid > #{id,jdbcType=INTEGER}
</select>
<!-- 嵌套查询 1:N end -->

嵌套结果查询

<!-- 嵌套结果 start  1:1-->
<resultMap id="blogResultMap2" type="com.gupao.dal.resultmap.BlogResultMap">
  <id column="bid" jdbcType="INTEGER" property="bid" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <association property="author" javaType="com.gupao.dal.dao.Author">
    <id column="aid" jdbcType="INTEGER" property="aid" />
    <result column="author_name" jdbcType="VARCHAR" property="authorName" />
  </association>
</resultMap>

<select id="selectBlogAuthor2" resultMap="blogResultMap2" parameterType="int">
  select
  *
  from blog b,author a
  where bid = #{id,jdbcType=INTEGER}
  and
  b.author_id = a.aid
</select>
<!-- 嵌套结果 end  1:1-->


<!-- 嵌套结果 1:N start  -->（不存在一对多的问题）
<resultMap id="blogPostsResultMap2" type="com.gupao.dal.resultmap.BlogPostsResultMap">
  <id column="bid" jdbcType="INTEGER" property="bid" />
  <result column="name" jdbcType="VARCHAR" property="name" />
  <result column="author_id" jdbcType="INTEGER" property="authorId" />
  <collection property="posts" ofType="com.gupao.dal.dao.Posts" >
    <id column="pid" jdbcType="INTEGER" property="pid" />
    <result column="post_name" jdbcType="VARCHAR" property="postName" />
  </collection>
</resultMap>

<select id="selectBlogPosts2" resultMap="blogPostsResultMap2" parameterType="int">
  select
  *
  from blog b,posts p
  where b.bid = #{id,jdbcType=INTEGER}
  and b.bid = p.blog_id
</select>
<!-- 嵌套结果 1:N end -->