SpringBoot+Mybatis+druid整合詳細過程，這里使用的是druid連接池（亦可以使用hikari連接池，參考SpringBoot整合hikari），具體參數(shù)配置請參考我的上一篇博客SpringBoot整合druid

項目準備

首先新建一個SpringBoot的web項目，選擇Spring Initializr（用來初始化一個Spring boot 的項目）

image

然后填寫項目坐標，自行修改

image

最后選擇要添加的組件web，Mybatis，mysql新建成功

image

配置

打開pom.xml文件

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
       <!--mybatis依賴-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
         <!--添加druid依賴-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
        <!--導(dǎo)入log4j，因為driud添加的拓展插件為log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!--mysql依賴-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

打開配置文件application.yml添加具體屬性

========================
#數(shù)據(jù)源基本配置
spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver# 時區(qū)配置錯誤就添加 serverTimezone = UTC
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
    #druid增加的配置
    type: com.alibaba.druid.pool.DruidDataSource
=========================以下配置酌情處理===========
    # 初始化連接池個數(shù)
    initialSize: 5
    # 最小連接池個數(shù)
    minIdle: 5
    # 最大連接池個數(shù)
    max-active: 20
    # 配置獲取連接等待超時的時間
    maxWait: 60000
    # 配置間隔多久才進行一次檢測
    timeBetweenEvictionRunsMillis: 60000
    # 最小生存的時間
    minEvictableIdleTimeMillis: 300000
    # 用來檢測連接是否有效的sql，要求是一個查詢語句。
    # 如果validationQuery為null，testOnBorrow、testOnReturn、testWhileIdle都不會起作用
    validationQuery: SELECT 1 FROM DUAL
    # 建議配置為true，不影響性能，并且保證安全性。
    # 申請連接的時候檢測，如果空閑時間大于timeBetweenEvictionRunsMillis，執(zhí)行validationQuery檢測連接是否有效。
    testWhileIdle: true
    # 申請連接時執(zhí)行validationQuery檢測連接是否有效，做了這個配置會降低性能
    testOnBorrow: false
    # 歸還連接時執(zhí)行validationQuery檢測連接是否有效，做了這個配置會降低性能
    testOnReturn: false
    # 打開PSCache，并且指定每個連接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 通過別名的方式配置擴展插件，多個英文逗號分隔，常用的插件有：
    # 監(jiān)控統(tǒng)計用的filter:stat
    # 日志用的filter:log4j
    # 防御sql注入的filter:wall
    filters: stat,wall,log4j
    # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多個DruidDataSource的監(jiān)控數(shù)據(jù)
    useGlobalDataSourceStat: true

添加配置類MyBatisConfig開啟駝峰命名

public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true); //開啟駝峰
            }
        };
    }
}

添加實體類Department

public class Department {
    private Integer id;
    private String departmentName;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

打開數(shù)據(jù)庫管理工具新建名為mybatis的數(shù)據(jù)庫，執(zhí)行Department類對應(yīng)sql腳本

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

添加接口DepartmentMapper，使用注解增刪改查，這里只演示insert和select

public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    Department getDeptById(Integer id);

    //使用自動生成的主鍵，并告訴是那個屬性是封裝主鍵的
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into department(department_name) values(#{departmentName})")
    int insertDept(Department department);
}

在啟動類上添加@MapperScan掃描mapper包

@MapperScan(value = "com.springboot.mapper")
@SpringBootApplication
public class SpringBootDataMybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringBootDataMybatisApplication.class, args);
    }

}

添加DeptController類

@RestController
public class DeptController {

    @Autowired
    private DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") Integer id) {
        return departmentMapper.getDeptById(id);
    }

    @GetMapping("/insert")
    public Department insertDept(Department department) {
        departmentMapper.insertDept(department);
        return department;
    }
}

啟動項目，在瀏覽器輸入localhost：8080/insert?departmentName=huawei，新增數(shù)據(jù)成功

image

然后再輸入localhost:8080/dept/1，獲取departmentName=huawei的數(shù)據(jù)

image

Mybatis注解中使用if標簽

用script標簽包圍，然后像xml語法一樣書寫

@Select({
            "<script>" ,
            "SELECT COUNT(*) FROM category",
            "<if test='query != null and query != \" \" '>",
            "where cat_name like '%${query}%'",
            "</if>",
            "</script>"
    })
    Integer getCount(@Param("query") String query);