美文网首页Java编程程序员
SpringBoot+Mybatis集成搭建

SpringBoot+Mybatis集成搭建

作者: smileNicky | 来源:发表于2019-02-10 14:26 被阅读21次

    本博客介绍一下SpringBoot集成Mybatis,数据库连接池使用alibaba的druid,使用SpringBoot微框架虽然集成Mybatis之后可以不使用xml的方式来写sql,但是用惯了xml的其实也可以用xml来实现的,实现上具体用什么方式并不重要,主要是搭建一遍,对框架的运转就比较清晰。本博客还是用xml的方式来实现Mybatis的sql编写,不用注解方式。

    maven配置

     <!-- springboot mybatis-->
        <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>1.3.1</version>
        </dependency>
    

    当然要引入druid,mysql等等jar的话可以参考我一个项目里的parent工程的maven配置:

    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com.muses.taoshop</groupId>
      <artifactId>taoshop</artifactId>
      <version>1.0-SNAPSHOT</version>
      <packaging>pom</packaging>
      <name>taoshop</name>
    
      <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.7.RELEASE</version>
      </parent>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    
        <!-- 模块版本 -->
        <taoshop-web-portal.version>1.0</taoshop-web-portal.version>
    
        <!-- 外部依赖 -->
        <log4j.version>1.2.17</log4j.version>
        <mysql.version>5.1.27</mysql.version>
        <mybatis.version>3.4.0</mybatis.version>
        <mybatis.spring.version>1.3.0</mybatis.spring.version>
        <mybatis.springboot.version>1.3.1</mybatis.springboot.version>
        <mysql-connector.version>5.1.39</mysql-connector.version>
        <spring-boot.version>1.5.7.RELEASE</spring-boot.version>
        <druid.version>1.1.2</druid.version>
        <github.pagehelper.version>4.2.1</github.pagehelper.version>
        <fastjson.version>1.2.7</fastjson.version>
        <lombok.version>1.16.10</lombok.version>
      </properties>
    
      <modules>
        <module>taoshop-quartz</module>
        <module>taoshop-search</module>
        <module>taoshop-common</module>
        <module>taoshop-provider-api</module>
        <module>taoshop-provider</module>
        <module>taoshop-manager</module>
        <module>taoshop-portal</module>
        <module>taoshop-cms</module>
        <module>taoshop-order</module>
          <module>taoshop-sso</module>
      </modules>
    
      <build>
        <resources>
          <resource>
            <directory>src/main/java</directory>
            <includes>
              <include>**/*.xml</include>
            </includes>
              <filtering>true</filtering>
          </resource>
        </resources>
        <plugins>
          <!-- 设置源文件编译 -->
          <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.6.1</version>
            <configuration>
              <source>1.8</source>
              <target>1.8</target>
              <compilerVersion>1.8</compilerVersion>
              <encoding>UTF-8</encoding>
              <skipMain></skipMain>
              <annotationProcessorPaths>
                <path>
                  <groupId>org.projectlombok</groupId>
                  <artifactId>lombok</artifactId>
                  <version>${lombok.version}</version>
                </path>
              </annotationProcessorPaths>
    
            </configuration>
          </plugin>
    
          <!-- 解决资源文件的编码问题 -->
          <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-resources-plugin</artifactId>
            <version>3.0.2</version>
            <configuration>
              <encoding>UTF-8</encoding>
            </configuration>
          </plugin>
    
        </plugins>
      </build>
    
    
      <dependencies>
        <!-- Spring boot -->
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
          <version>${spring-boot.version}</version>
        </dependency>
    
        <!-- redis -->
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-redis</artifactId>
          <version>${spring-boot.version}</version>
        </dependency>
    
        <!-- springboot mybatis-->
        <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>${mybatis.springboot.version}</version>
        </dependency>
    
       <!-- 热部署-->
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-devtools</artifactId>
          <optional>true</optional>
        </dependency>
    
        <!--  alibaba druid -->
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>${druid.version}</version>
        </dependency>
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid-spring-boot-starter</artifactId>
          <version>${druid.version}</version>
        </dependency>
    
        <!-- Themeleaf -->
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
    
        <!-- pagehelper -->
        <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper</artifactId>
          <version>${github.pagehelper.version}</version>
        </dependency>
    
        <!-- mysql -->
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>${mysql-connector.version}</version>
        </dependency>
    
        <!-- fastjson -->
        <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>fastjson</artifactId>
          <version>1.2.35</version>
        </dependency>
    
        <!-- lombok -->
        <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
          <version>${lombok.version}</version>
        </dependency>
    
        <!-- 其他工具包-->
        <dependency>
          <groupId>commons-fileupload</groupId>
          <artifactId>commons-fileupload</artifactId>
          <version>1.2.2</version>
        </dependency>
        <dependency>
          <groupId>commons-io</groupId>
          <artifactId>commons-io</artifactId>
          <version>2.4</version>
        </dependency>
        <dependency>
          <groupId>commons-codec</groupId>
          <artifactId>commons-codec</artifactId>
          <version>1.9</version>
        </dependency>
        <dependency>
          <groupId>org.apache.commons</groupId>
          <artifactId>commons-lang3</artifactId>
          <version>3.5</version>
        </dependency>
      </dependencies>
      <!--  设定Maven主仓库为阿里私服 -->
      <repositories>
        <repository>
          <id>repos</id>
          <name>Repository</name>
          <url>http://maven.aliyun.com/nexus/content/groups/public</url>
        </repository>
      </repositories>
    
      <!--  设定插件仓库 -->
      <pluginRepositories>
        <pluginRepository>
          <id>pluginsRepos</id>
          <name>PluginsRepository</name>
          <url>http://maven.aliyun.com/nexus/content/groups/public</url>
        </pluginRepository>
      </pluginRepositories>
    
    </project>
    

    SpringBoot配置文件

    server:
      port: 8081
    #logging:
    #  config: classpath:logback_spring.xml.bat
    #  level:
    #    com.muses.taoshop: debug
    #  path: /data/logs
    
    spring:
      datasource:
    
        # 主数据源
        shop:
          url: jdbc:mysql://127.0.0.1:3306/taoshop?autoReconnect=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false
          username: root
          password: root
    
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
    
        # 连接池设置
        druid:
          initial-size: 5
          min-idle: 5
          max-active: 20
          # 配置获取连接等待超时的时间
          max-wait: 60000
          # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
          time-between-eviction-runs-millis: 60000
          # 配置一个连接在池中最小生存的时间,单位是毫秒
          min-evictable-idle-time-millis: 300000
          # Oracle请使用select 1 from dual
          validation-query: SELECT 'x'
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          # 打开PSCache,并且指定每个连接上PSCache的大小
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
          filters: stat,wall,slf4j
          # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
          connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          # 合并多个DruidDataSource的监控数据
          use-global-data-source-stat: true
    
    #  jpa:
    #    database: mysql
    #    hibernate:
    #      show_sql: true
    #      format_sql: true
    #      ddl-auto: none
    #      naming:
    #        physical-strategy: org.hibernate.boot.entity.naming.PhysicalNamingStrategyStandardImpl
    
    #  mvc:
    #    view:
    #      prefix: /WEB-INF/jsp/
    #      suffix: .jsp
    
      #添加Thymeleaf配置
      thymeleaf:
        cache: false
        prefix: classpath:/templates/
        suffix: .html
        mode: HTML5
        encoding: UTF-8
        content-type: text/html
    
      #Jedis配置
    #  jedis :
    #    pool :
    #      host : 127.0.0.1
    #      port : 6379
    #      password : redispassword
    #      timeout : 0
    #      config :
    #        maxTotal : 100
    #        maxIdle : 10
    #        maxWaitMillis : 100000
    
    
    
    
    

    Application启动类

    package com.muses.taoshop;
    
    
    
    import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
    import org.springframework.boot.*;
    import org.springframework.boot.autoconfigure.*;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
    import org.springframework.boot.web.servlet.ServletComponentScan;
    import org.springframework.cache.annotation.EnableCaching;
    import org.springframework.context.annotation.Bean;
    import org.springframework.scheduling.annotation.EnableAsync;
    import org.springframework.scheduling.annotation.EnableScheduling;
    import org.springframework.stereotype.*;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    import org.springframework.web.bind.annotation.*;
    /**
     *
     * <pre>
     *  SpringBoot启动配置类
     * </pre>
     * @author nicky
     * @version 1.00.00
     * <pre>
     * 修改记录
     *    修改后版本:     修改人:  修改日期:     修改内容:
     * </pre>
     */
    @Controller
    @EnableScheduling//开启对计划任务的支持
    @EnableTransactionManagement//开启对事务管理配置的支持
    @EnableCaching
    @EnableAsync//开启对异步方法的支持
    @EnableAutoConfiguration
    @ServletComponentScan
    @SpringBootApplication(exclude={DataSourceAutoConfiguration.class,
            MybatisAutoConfiguration.class,
            DataSourceTransactionManagerAutoConfiguration.class})
    public class PortalApplication {
    
        @RequestMapping("/")
        @ResponseBody
        String home() {
            return "portal web!";
        }
    
        @RequestMapping("/doTest")
        @ResponseBody
        String doTest(){
            System.out.println(Thread.currentThread().getName());
            String threadName = Thread.currentThread().getName();
            return threadName;
        }
    
    
        public static void main(String[] args) throws Exception {
            SpringApplication.run(PortalApplication.class, args);
        }
    
    
    }
    

    @SpringBootApplication(exclude={DataSourceAutoConfiguration.class,
    MybatisAutoConfiguration.class,
    DataSourceTransactionManagerAutoConfiguration.class}),这个注释是必须的,开启自动扫描数据源和Mybatis配置文件

    数据库配置
    做好SpringBoot启动类的注解之后,需要编写Mybatis和数据源的配置类

    写一个Constants类:

    package com.muses.taoshop.common.core.database.config;
    
    /**
     * <pre>
     *  基本配置类
     * </pre>
     *
     * @author nicky
     * @version 1.00.00
     * <pre>
     * 修改记录
     *    修改后版本:     修改人:  修改日期:     修改内容:
     * </pre>
     */
    public class BaseConfig {
    
        /**
         * 设置主数据源名称
         */
        public static final String DATA_SOURCE_NAME = "shop";
    
        /**
         * 加载配置文件信息
         */
        public static final String DATA_SOURCE_PROPERTIES = "spring.datasource.shop";
    
        /**
         * repository 所在包
         */
        public static final String REPOSITORY_PACKAGES = "com.muses.taoshop.**.repository";
    
        /**
         * mapper 所在包
         */
        public static final String MAPPER_PACKAGES = "com.muses.taoshop.**.mapper";
    
        /**
         * 实体类 所在包
         */
        public static final String ENTITY_PACKAGES = "com.muses.taoshop.*.entity";
    
        /**
         * 自定义TypeHandler
         */
        public static final String TYPE_HANDLERS_PACKAGES = "com.muses.taoshop.common.core.database.typehandlers";
    
        /**
         * Mybatis session 工厂
         */
        public static final String SQL_SESSION_FACTORY = "sqlSessionFactory";
    
        /**
         * Mybatis 事务管理器
         */
        public static final String MYBATIS_TRANSACTION_MANAGER = "mybatisTransactionManager";
    
        /**
         * Jedis连接池
         */
        public static final String JEDIS_POOL = "jedisPool";
    
        /**
         * Jedis连接池配置
         */
        public static final String JEDIS_POOL_CONFIG = "jedisPoolConfig";
    
    }
    
    

    DataSource配置类:

    package com.muses.taoshop.common.core.database.config;
    
    import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    
    import static com.muses.taoshop.common.core.database.config.BaseConfig.DATA_SOURCE_NAME;
    import static com.muses.taoshop.common.core.database.config.BaseConfig.DATA_SOURCE_PROPERTIES;
    
    
    /**
     * <pre>
     *  DataSource配置类
     * </pre>
     *
     * @author nicky
     * @version 1.00.00
     * <pre>
     * 修改记录
     *    修改后版本:     修改人:  修改日期:     修改内容:
     * </pre>
     */
    @Configuration
    public class DataSourceConfig {
    
        @Bean(name = DATA_SOURCE_NAME)
        @ConfigurationProperties(prefix = DATA_SOURCE_PROPERTIES)
        public DataSource dataSource() {
            return DruidDataSourceBuilder.create().build();
        }
    }
    
    

    Mybatis配置类:

    package com.muses.taoshop.common.core.database.config;
    
    //import com.muses.taoshop.common.core.database.annotation.MybatisRepository;
    import com.muses.taoshop.common.core.database.annotation.TypeAliasesPackageScanner;
    import org.apache.ibatis.io.VFS;
    import org.apache.ibatis.plugin.Interceptor;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.*;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.core.io.support.ResourcePatternResolver;
    
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    import javax.sql.DataSource;
    
    import static com.muses.taoshop.common.core.database.config.BaseConfig.*;
    
    /**
     * <pre>
     *  Mybatis配置类
     * </pre>
     *
     * @author nicky
     * @version 1.00.00
     * <pre>
     * 修改记录
     *    修改后版本:     修改人:  修改日期:     修改内容:
     * </pre>
     */
    @MapperScan(
            basePackages = MAPPER_PACKAGES,
            //annotationClass = MybatisRepository.class,
            sqlSessionFactoryRef = SQL_SESSION_FACTORY
    )
    @ComponentScan
    @EnableTransactionManagement
    @Configuration
    public class MybatisConfig {
    
    
        //@Autowired
       //MybatisSqlInterceptor mybatisSqlInterceptor;
    
        TypeAliasesPackageScanner packageScanner = new TypeAliasesPackageScanner();
    
        @Bean(name = DATA_SOURCE_NAME)
        @ConfigurationProperties(prefix = DATA_SOURCE_PROPERTIES)
        @Primary
        public DataSource dataSource(){
            return DataSourceBuilder.create().build();
        }
    
        @Primary
        @Bean(name = SQL_SESSION_FACTORY)
        public SqlSessionFactory sqlSessionFactory(@Qualifier(DATA_SOURCE_NAME)DataSource dataSource)throws Exception{
            //SpringBoot默认使用DefaultVFS进行扫描,但是没有扫描到jar里的实体类
            VFS.addImplClass(SpringBootVFS.class);
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            //factoryBean.setPlugins(new Interceptor[]{mybatisSqlInterceptor});
            factoryBean.setDataSource(dataSource);
            //factoryBean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            try{
                factoryBean.setMapperLocations(resolver.getResources("classpath*:/mybatis/*Mapper.xml"));
                String typeAliasesPackage = packageScanner.getTypeAliasesPackages();
                factoryBean.setTypeAliasesPackage(typeAliasesPackage);
                SqlSessionFactory sqlSessionFactory = factoryBean.getObject();
                return sqlSessionFactory;
            }catch (Exception e){
                e.printStackTrace();
                throw new RuntimeException();
            }
        }
    
        @Bean(name = MYBATIS_TRANSACTION_MANAGER)
        public DataSourceTransactionManager transactionManager(@Qualifier(DATA_SOURCE_NAME)DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
    
    }
    

    通配符别名扫描
    这里需要注意一点,因为项目业务需要,这里的别名扫描是做到了竟然通配符的,详情可以参考我之前的博客:https://blog.csdn.net/u014427391/article/details/84723292

    Mybatis例子实践
    所以,SpringBoot集成Mybatis就基本搭建好了,下面来实践一个例子:
    注意:Mybatis的xml文件都要放在resources资源文件夹下面的一个Mybatis文件夹里面,因为已经做了配置

    factoryBean.setMapperLocations(resolver.getResources("classpath:/mybatis/Mapper.xml"));

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.muses.taoshop.item.mapper.ItemMapper" >
    
        <sql id="BaseColumnList" >
            id ,
            sku_code ,
            sku_name ,
            price ,
            stock ,
            last_modify_time as lastModifyTime,
            create_time as createTime
        </sql>
        <sql id="OrderBy">
            ORDER BY price
        </sql>
    
        <!-- 商品详情-->
        <select id="getItemDetail" resultType="ItemDetail">
            SELECT
              itb.brand_name AS brandName,
              s.shop_name AS shopName,
              spu.item_name AS itemName,
              sku.price,
              sku.promotion_price AS promotionPrice,
              sku.img_path AS imgPath,
              sku.stock
            FROM
              item_spu spu
            RIGHT JOIN item_brand itb
              ON itb.id = spu.brand_id
           RIGHT JOIN shop_info s
              ON s.id = spu.shop_id
            LEFT JOIN
            (SELECT
                  s.price,
                  s.promotion_price,
                  s.spu_id,
                  s.img_path,
                  s.stock
             FROM
                  item_sku s
              GROUP BY s.spu_id) sku
            ON sku.spu_id = spu.id
            <where>
                spu.id = #{spuId}
            </where>
        </select>
    
    </mapper>
    

    Mapper接口类:

    package com.muses.taoshop.item.mapper;
    
    
    import com.muses.taoshop.item.entity.ItemDetail;
    import com.muses.taoshop.item.entity.ItemPortal;
    import com.muses.taoshop.item.entity.ItemSpec;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.List;
    
    @Mapper
    public interface ItemMapper {
    
        ItemDetail getItemDetail(@Param("spuId")int spuId);
    
    }
    
    

    业务接口类:

    package com.muses.taoshop.item.service;
    
    import com.muses.taoshop.item.entity.ItemDetail;
    import com.muses.taoshop.item.entity.ItemPortal;
    import com.muses.taoshop.item.entity.ItemSpec;
    import com.muses.taoshop.item.mapper.ItemMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * <pre>
     *  商品信息服务实现类
     * </pre>
     *
     * @author nicky
     * @version 1.00.00
     * <pre>
     * 修改记录
     *    修改后版本:     修改人:  修改日期: 2018.06.24 22:37    修改内容:
     * </pre>
     */
    @Service
    public class ItemServiceImpl implements IItemService {
    
        @Autowired
        ItemMapper itemMapper;
    
        /**
         * 获取商品详情信息
         * @return ItemDetail
         */
        @Override
        public ItemDetail getItemDetailInfo(int spuId){
            ItemDetail itemDetail = itemMapper.getItemDetail(spuId);
            return  itemDetail;
        }
    
    
    }
    
    

    代码取自个人的开源项目:https://github.com/u014427391/taoshop,有需要可以参考

    相关文章

      网友评论

        本文标题:SpringBoot+Mybatis集成搭建

        本文链接:https://www.haomeiwen.com/subject/pytxeqtx.html