SpringBoot(九)SpringBoot整合tk.mybatis

╰半橙微兮° 2022-06-04 03:12 226阅读 0赞

## Maven ##

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>${druid.version}</version>
    </dependency>
    
    
    
    <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>
    
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.1</version>
    </dependency>
    
    <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());
    }