SpringBoot整合Mybatis与c3p0

1. 导入maven依赖 

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>

        <!--        mysql jar包依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
            <scope>runtime</scope>
        </dependency>

        <!--        数据库连接池-->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>


        <!-- 配置devtools开启热部署 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.7.4</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.23</version>
        </dependency>



        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

2.在resoures目录下创建并配置application-c3p0.properties

spring.datasource.c3p0.driverClass=com.mysql.cj.jdbc.Driver
spring.datasource.c3p0.jdbcUrl=jdbc:mysql://localhost:3306/msm_db?serverTimezone=Asia/Shanghai&useSSL=false&zeroDateTimeBehavior=convertToNull&useLegacyDatetimeCode=false
spring.datasource.c3p0.user=root
spring.datasource.c3p0.password=123456
spring.datasource.c3p0.maxPoolSize=30
spring.datasource.c3p0.minPoolSize=10
spring.datasource.c3p0.initialPoolSize=10
spring.datasource.c3p0.acquireIncrement=5
spring.datasource.c3p0.acquireRetryAttempts=30
spring.datasource.c3p0.acquireRetryDelay=1000
spring.datasource.c3p0.maxIdleTime=120
spring.datasource.c3p0.maxConnectionAge=0
spring.datasource.c3p0.maxStatements=100
spring.datasource.c3p0.maxStatementsPerConnection=5
spring.datasource.c3p0.preferredTestQuery=SELECT SYSDATE FROM DUAL
spring.datasource.c3p0.idleConnectionTestPeriod=120
spring.datasource.c3p0.numHelperThreads=3

3.在application.yml下配置

spring:
    profiles:
    active: c3p0

 3.1 在config包下配置数据源

package com.tian.sms.config;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfiguration {
    @Bean(name = "dataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.c3p0")
    public DataSource createDataSource(){
        return DataSourceBuilder.create()//创建数据源对象
                .type(ComboPooledDataSource.class)//设置数据源类型
                .build();//构建数据源对象
    }
}

4.整合mybatis在resources目录下创建mybatis-config.xml文件配置mybatis核心

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--mybatis的主配置文件-->
<configuration>
    <!--配置全局属性-->
    <settings>
        <!--全局映射器启用缓存-->
        <setting name="cacheEnabled" value="true"/>
        <!--对于未知的SQL查询，允许返回不同的结果集以达到通用的效果-->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <!--对于批量更新操作缓存sql以提高性能-->
        <setting name="defaultExecutorType" value="SIMPLE"/>

        <!--使用jdbc中的getGeneratedKeys获取数据库自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>

        <!--使用列别名替换列名 默认true-->
        <setting name="useColumnLabel" value="true"/>

        <!--开启驼峰命名转换:Table{create_time}-> Entity{createTime}-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>


</configuration>

5.为防止maven出现Invalid bound statement (not found)需配置maven该标签写在build标签下

<resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                    <include>**/*.yml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
            </resource>
        </resources>

6.定义一个mapper类用于测试能否成功执行。需mapper标签才能被spring识别才能够注入。需要@param标签来声明参数名字，否则在mybaitis-mapper.xml中不能直接通过名字直接引用。

package com.tian.sms.dao;

import com.tian.sms.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserMapper {

    List<User> queryUser(@Param("rowIndex") int rowIndex,@Param("pageNum") int pageNum);
}

7.配置mapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tian.sms.dao.UserMapper">
    <select id="queryUser" parameterType="int" resultType="com.tian.sms.pojo.User">
        select * from msm_db.user order by user_private desc limit #{rowIndex},#{pageNum};
    </select>
</mapper>

8.进行Test测试，需导入junit包，导入test包时注意不要导错到api下的test，否则无法连接到数据库

package com.tian.sms.dao;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.boot.logging.LogLevel;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {

    @Autowired
    UserMapper userMapper;

    @Test
    public void queryUserTest(){
        System.out.println(userMapper.queryUser(0, 10));
    }
}