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.producermybatis.mapperLocations=classpath:mapper/**/*.xmlmybatis.configuration.map-underscore-to-camel-case=truepagehelper.helperDialect=mysqlpagehelper.reasonable=truepagehelper.supportMethodsArguments=truepagehelper.params=count=countSql##################### Druid ##################################spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCLspring.datasource.username=testspring.datasource.password=testspring.datasource.driver-class-name=oracle.jdbc.OracleDriver# 下面为连接池的补充设置，应用到上面所有数据源中spring.datasource.initialSize=5spring.datasource.minIdle=5spring.datasource.maxActive=20# 配置获取连接等待超时的时间spring.datasource.maxWait=60000# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒spring.datasource.timeBetweenEvictionRunsMillis=60000# 配置一个连接在池中最小生存的时间，单位是毫秒spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.validationQuery=SELECT 1 FROM DUALspring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=false# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙spring.datasource.filters=stat,wall,log4jspring.datasource.logSlowSql=true

Druid配置

/** * @author Nick * @version V1.0.0 * @Date 2017/11/28 20:29 * @description 阿里巴巴连接池配置 *  输入http://IP:port/druid/，输入数据库用户名和密码即可登录阿里巴巴数据库监控中心 * */@Configurationpublic 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 */@NoRepositoryBeanpublic interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {}

@Mapperpublic 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)@Rollbackpublic 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)@Testpublic 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());}