SpringBoot(九)SpringBoot整合tk.mybatis

Maven

<!-- 阿里巴巴连接池Druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
</dependency>


<!-- mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis-spring-boot-starter.version}</version>
</dependency>
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>1.1.4</version>
</dependency>
<!-- pagehelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.1</version>
</dependency>
<!-- pagehelper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.1</version>
</dependency>

Properties

##################### mybatis ###############################
mybatis.typeAliasesPackage=com.fu.producer
mybatis.mapperLocations=classpath:mapper/**/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql


##################### Druid ##################################

spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# 下面为连接池的补充设置，应用到上面所有数据源中
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
spring.datasource.logSlowSql=true

Druid配置

/**
 * @author Nick
 * @version V1.0.0
 * @Date 2017/11/28 20:29
 * @description 阿里巴巴连接池配置
 *  输入http://IP:port/druid/，输入数据库用户名和密码即可登录阿里巴巴数据库监控中心
 *
 */
@Configuration
public class DruidConfig {

    private static Logger logger = LoggerFactory.getLogger(DruidConfig.class);

    @Value("${spring.datasource.url}")
    private String dbUrl;

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

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

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.filters}")
    private String filters;

    @Value("${spring.datasource.logSlowSql}")
    private String logSlowSql;

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", username);
        reg.addInitParameter("loginPassword", password);
        reg.addInitParameter("logSlowSql", logSlowSql);
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error("druid configuration initialization filter", e);
        }
        return datasource;
    }

}

/**
 * @author Nick
 * @version V1.0.0
 * @Date 2017/12/1 13:45
 * @description BaseMapper
 */
@NoRepositoryBean
public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {
}

@Mapper
public interface AccountMapper extends BaseMapper<Account> {

    @Select("select account_id as accountId, account_name as accountName from account where account_id = #{accountId}")
    Account findById(@Param("accountId") Long accountId);

    @Select("SELECT count(*) as totalRow FROM account ")
    Integer gettotalRow();

    @Select("SELECT * FROM ( SELECT a.*, ROWNUM RN FROM (SELECT * FROM account) a  WHERE ROWNUM <= #{endNum} )WHERE RN >= #{startNum} ")
    List<Account> findByPage(@Param("startNum") Integer startNum, @Param("endNum") Integer endNum);

    Account selectAccount(@Param("accountId") Long accountId);

    Map<String, Object> selectAccount1(@Param("accountId") Long accountId);

    /**
     * 批量插入，Oralce需要设置useGeneratedKeys=false，不然报错
     *  Oracle批量插入：  insert all into table(...) values(...) into table(...) values(...) select * from dual
     *  Mysql批量插入：   insert into table(...) values(...),(...)
     * @param accounts
     * @return
     */
    @Insert("<script>" +
                "insert all " +
                "<foreach collection=\"list\" item=\"account\">" +
                "into account(account_id, account_name, account_code) " +
                "values(#{account.accountId}, #{account.accountName}, #{account.accountCode})" +
                "</foreach> SELECT * FROM DUAL" +
            "</script>")
    @Options(useGeneratedKeys = false)
    int insertAccounts(List<Account> accounts);

    /**
     * 根据主键查询一个
     * @param accountId
     * @return
     */
    @Results(id = "accountResultTest", value = {
            @Result(property = "accountId", column = "account_id", id = true),
            @Result(property = "accountName", column = "account_name", id = true),
            @Result(property = "accountCode", column = "account_code", id = true)
    })
    @Select("select account_id, account_name, account_code from account where account_id = #{accountId}")
    Account selectById(Long accountId);

    /**
     * 查询全部，引用上面的Results
     * @return
     */
    @ResultMap("accountResultTest")
    @Select("select account_id, account_name, account_code from account")
    List<Account> selectAll();

}

测试

@Test
@Transactional(readOnly = true)
@Rollback
public void dbTest() throws Exception {
    Account account = accountMapper.findById(105L);
    logger.info(account.getAccountName());

    Account account2 = accountMapper.selectAccount(13276L);
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String dateStr = simpleDateFormat.format(account2.getAccountInvDate());
    logger.info(account2.getAccountName() + dateStr);

    Account selectAccount = new Account();
    selectAccount.setAccountId(13276L);
    selectAccount = accountMapper.selectOne(selectAccount);
    logger.info(selectAccount.getAccountCode() + "----------->"+selectAccount.getAccountName()+"---->"+selectAccount.getAccountInvDate());

    Map<String, Object> account3 = accountMapper.selectAccount1(13276L);
    logger.info(account3.get("ACCOUNTID").toString() + "----------" + account3.get("ACCOUNTNAME").toString()
            + "-------------------" + account3.get("ACCOUNTINVDATE").toString());
}


@Test
@Transactional
@Rollback(true)
public void mapperTest() {
    List<Account> accountList = new ArrayList<Account>();

    Account account1 = new Account();
    account1.setAccountId(17178L);
    account1.setAccountName("张三1");
    account1.setAccountCode("zhangsan1");
    accountList.add(account1);

    Account account2 = new Account();
    account2.setAccountId(17179L);
    account2.setAccountName("张三2");
    account2.setAccountCode("zhangsan2");
    accountList.add(account2);

    if(accountMapper.insertAccounts(accountList) > 0) {
        Account account3 = accountMapper.selectById(17178L);
        Assert.assertEquals("zhangsan1", account3.getAccountCode());

        Account account4 = accountMapper.selectById(17179L);
        Assert.assertEquals("zhangsan2", account4.getAccountCode());
    }
}


@Transactional
@Rollback(true)
@Test
public void pageHelperTest() {
    //分页，通过sqlSession来获取
    List<Account> accounts1 = sqlSession.selectList("com.ry.fu.producer.pwp.mapper.AccountMapper.selectAll", null, new RowBounds(0, 10));
    for(Account acc : accounts1) {
        logger.info(acc.getAccountId() + "---------------" + acc.getAccountCode() + "----------------" + acc.getAccountName() );
    }

    //PageHelper分页
    PageHelper.startPage(1, 10);
    List<Account> accounts2 = accountMapper.selectAll();
    for(Account acc : accounts2) {
        logger.info(acc.getAccountId() + "---------------" + acc.getAccountCode() + "----------------" + acc.getAccountName() );
    }

    //使用JDK8中的Lambda表达式
    Page<Account> page = PageHelper.startPage(1, 10).doSelectPage(()-> accountMapper.selectAll());
}