SpringBoot+Mybatis集成搭建

时间：2023-03-08 21:26:44

本博客介绍一下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.****.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，有需要可以参考

相关文章