springboot3数据库连接配置

编程路上的小学渣
1,132 阅读5分钟

数据库配置

默认+Mybatis

pom.xml文件

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>
<!--用于测试，不然测试用不了 -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter-test</artifactId>
    <version>3.0.3</version>
    <scope>test</scope>
</dependency>

在application.yml文件里配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost/springboot3
    username: root
    password: root
    
# mybatis
mybatis:
  # 实体类的别名,多个使用逗号隔开
  type-aliases-package: wnan.explore.entity.po
  # mapper的映射xml文件，多个使用逗号隔开
  mapper-locations: classpath:wnan/explore/mapper/**.xml

package wnan.explore.mapper.db;
import org.springframework.stereotype.Component;
import wnan.explore.entity.po.Car;
import java.util.List;
@Component("carMapper_db") // 这个可以不写
public interface CarMapper {
    List<Car> carAll();
}

package wnan.explore.entity.po;
import lombok.Data;
import lombok.experimental.Accessors;
import java.util.Date;

@Data
@Accessors(chain = true)
public class Car {
    private int carId;
    private String makeEnterprise;
    private String modelInfo;
    private Date makeYear;
    private double price;
}

<?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="wnan.explore.mapper.db.CarMapper">
    <resultMap id="car_map" type="Car">
        <result column="carId" property="carId" javaType="int"/>
        <result column="make_enterprise" property="makeEnterprise" javaType="String"/>
        <result column="model_info" property="modelInfo" javaType="String"/>
        <result column="make_year" property="makeYear" javaType="Date"/>
        <result column="price" property="price" javaType="double"/>
    </resultMap>

    <sql id="car_sql">
        carId, make_enterprise, model_info, make_year, price
    </sql>
    <select id="carAll" resultMap="car_map">
        select
        <include refid="car_sql"/>
        from car
    </select>
</mapper>

多数据源+Mybatis

application.yml

 spring:
  # 多数据源
  datasource:
    # 数据源1,这里的db1，是自己想的名字，啥都可以，反正都是自己要自定义配置数据源
    db1:
      driver-class-name: com.mysql.cj.jdbc.Driver
      # 不可以是url，必须是jdbc-url,因为使用数据库连接池Hikari
      jdbc-url: jdbc:mysql://localhost/springboot3
      username: root
      password: root
    # 数据源2
    db2:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost/springboot3_2
      username: root
      password: root

sql文件

create database if not exists springboot3 character set utf8mb4 collate utf8mb4_unicode_ci;
use springboot3;

drop table if exists car;
create table if not exists car
(
    carId           int primary key,
    make_enterprise varchar(225)   not null comment '制造商信息',
    model_info      varchar(50)    not null comment '汽车型号',
    make_year       year           not null comment '制造年份',
    price           decimal(10, 2) not null comment '汽车价格'
) comment '汽车表';
INSERT INTO car (carId, make_enterprise, model_info, make_year, price)
VALUES (1, '长安汽车', 'CS75 PLUS', 2020, 145000.00),
       (2, '比亚迪', '汉DM', 2021, 220000.00),
       (3, '领克', '03', 2019, 150000.00),
       (4, '广汽埃安', 'AIONS', 2022, 170000.00),
       (5, '五菱汽车', '宏光MINI EV', 2021, 40000.00);
       
drop table if exists file;
create table if not exists file
(
    fileId           int primary key,
    file_info      varchar(50)    not null comment '文件信息'
) comment '文件表';
INSERT INTO file (fileId, file_info)
VALUES (1, '文件1'),
       (2, 'wenjia2');
       
       
-----------------------------------------------------------------------------
create database if not exists springboot3_2 character set utf8mb4 collate utf8mb4_unicode_ci;
use springboot3_2;

drop table if exists car;
create table if not exists car
(
    carId           int  primary key,
    make_enterprise varchar(225)   not null comment '制造商信息',
    model_info      varchar(50)    not null comment '汽车型号',
    make_year       year           not null comment '制造年份',
    price           decimal(10, 2) not null comment '汽车价格'
) comment '汽车表';
INSERT INTO car (carId, make_enterprise, model_info, make_year, price) VALUES
(1, '吉利', '博越', 2018, 120000.00),
(2, '长城', '哈弗H6', 2019, 135000.00),
(3, '比亚迪', '秦Pro', 2020, 150000.00),
(4, '奇瑞', '瑞虎8', 2017, 110000.00),
(5, '广汽传祺', 'GS4', 2021, 140000.00),
(6, '上汽荣威', 'RX5', 2022, 130000.00),
(7, '东风风神', '奕炫', 2019, 105000.00),
(8, '一汽红旗', 'H5', 2020, 160000.00),
(9, '江淮汽车', '瑞风S4', 2021, 120000.00),
(10, '北汽新能源', 'EU5', 2018, 155000.00);

实体类

package wnan.explore.entity.po;
import lombok.Data;
import lombok.experimental.Accessors;
import java.util.Date;

@Data
@Accessors(chain = true)
public class Car {
    private int carId;
    private String makeEnterprise;
    private String modelInfo;
    private Date makeYear;
    private double price;
}
--------------------------------------------------------------------------
package wnan.explore.entity.dto;
import lombok.Data;

@Data
public class File {
    private int fileId;
    private String fileInfo;
}

数据源配置类

package wnan.explore.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/*****UTF-8*****
 * Description: 数据源1配置
 * Author: wnan
 * Create Date: 2024/10/7 19:04
 * Proverbs: 吃得苦中苦，方为人上人
 */
@Configuration
@Primary 
@MapperScan(basePackages = {"wnan.explore.mapper.db1"},sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource_1_Config {

    // 数据源Bean
    @Bean("db1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource getDataSource() {
        return DataSourceBuilder.create().build();
    }

    // session工厂
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory getSqlSessionFactory(@Qualifier("db1") DataSource dataSource) throws Exception{
        final org.mybatis.spring.SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        // 别名设置，多个用逗号隔开
        factoryBean.setTypeAliasesPackage("wnan.explore.entity.po,wnan.explore.entity.dto");

        PathMatchingResourcePatternResolver patternResolver = new PathMatchingResourcePatternResolver();
        String xmlMapper = "classpath:wnan/explore/mapper/db1/**.xml";
        Resource[] mapperLocations = patternResolver.getResources(xmlMapper);
        factoryBean.setMapperLocations(mapperLocations);
        return factoryBean.getObject();
    }
}

  • @Primary 表示一个主数据库，就是备胎

  • factoryBean.setTypeAliasesPackage（）别名设置，多个用逗号隔开

  • getResources（）是复数方法，有s

  • 配置类可以额外设置事务配置【建议配置】

  • classpath 表示转换为项目所在地的绝对路径

  • xxx.xml 如果在多个文件夹，就用逗号隔开，如：

    String xmlMapper = "classpath*:wnan/explore/mapper/db1/**.xml,classpath*:wnan/explore/mapper/db2/**.xml";

package wnan.explore.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

/*****UTF-8*****
 * Description: 数据库2配置
 * Author: wnan
 * Create Date: 2024/10/7 19:04
 * Proverbs: 吃得苦中苦，方为人上人
 */
@Configuration
@MapperScan(basePackages = {"wnan.explore.mapper.db2"},sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource_2_Config {

    // 数据源Bean
    @Bean("db2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource getDataSource() {
        return DataSourceBuilder.create().build();
    }

    // session工厂
    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory getSqlSessionFactory(@Qualifier("db2") DataSource dataSource) throws Exception{
        final org.mybatis.spring.SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);

        PathMatchingResourcePatternResolver patternResolver = new PathMatchingResourcePatternResolver();
        String xmlMapper = "classpath*:wnan/explore/mapper/db2/**.xml";
        Resource[] mapperLocations = patternResolver.getResources(xmlMapper);
        factoryBean.setMapperLocations(mapperLocations);
        return factoryBean.getObject();
    }
}

mapper

package wnan.explore.mapper.db1;
import org.springframework.stereotype.Component;
import wnan.explore.entity.po.Car;
import java.util.List;

@Component("carMapper1")
public interface CarMapper {
    List<Car> carAll();
}

-----------------------------------------------
package wnan.explore.mapper.db1;
import wnan.explore.entity.dto.File;
import java.util.List;

public interface FileMapper {
    List<File> fileDtoList();
}

------------------------------------------------
package wnan.explore.mapper.db2;
import org.springframework.stereotype.Component;
import wnan.explore.entity.po.Car;
import java.util.List;

@Component("carMapper2")
public interface CarMapper {
    List<Car> carAll();
}
  • @Component("carMapper1")/@Component("carMapper2") 类名字一样，会导致注入Bean的名字一样，会有冲突，这个就是解决了冲突。

xml文件

CarMapper.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="wnan.explore.mapper.db1.CarMapper">
    <resultMap id="car_map" type="Car">
        <result column="carId" property="carId" javaType="int"/>
        <result column="make_enterprise" property="makeEnterprise" javaType="String"/>
        <result column="model_info" property="modelInfo" javaType="String"/>
        <result column="make_year" property="makeYear" javaType="Date"/>
        <result column="price" property="price" javaType="double"/>
    </resultMap>

    <sql id="car_sql">
        carId, make_enterprise, model_info, make_year, price
    </sql>

    <select id="carAll" resultMap="car_map">
        select
        <include refid="car_sql"/>
        from car
    </select>
</mapper>

FileMapper.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="wnan.explore.mapper.db1.FileMapper">
    <resultMap id="file_map" type="File">
        <result column="fileId" property="fileId" javaType="int"/>
        <result column="file_info" property="fileInfo" javaType="String"/>
    </resultMap>

    <sql id="file_sql">
        fileId, file_info
    </sql>
    
    <select id="fileDtoList" resultMap="file_map">
        select
        <include refid="file_sql"/>
        from file
    </select>
</mapper>

CarMapper.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="wnan.explore.mapper.db2.CarMapper">
    <resultMap id="car_map" type="wnan.explore.entity.po.Car">
        <result column="carId" property="carId" javaType="int"/>
        <result column="make_enterprise" property="makeEnterprise" javaType="String"/>
        <result column="model_info" property="modelInfo" javaType="String"/>
        <result column="make_year" property="makeYear" javaType="Date"/>
        <result column="price" property="price" javaType="double"/>
    </resultMap>

    <sql id="car_sql">
        carId, make_enterprise, model_info, make_year, price
    </sql>

    <select id="carAll" resultMap="car_map">
        select
        <include refid="car_sql"/>
        from car
    </select>
</mapper>

测试

@SpringBootTest
public class CarServiceTest {

    @Autowired
    wnan.explore.mapper.db1.CarMapper mapper_1;

    @Test
    void t1() {
        mapper_1.carAll().forEach(System.out::println);
    }

    @Autowired
    wnan.explore.mapper.db2.CarMapper mapper_2;
    @Test
    void t2(){
        mapper_2.carAll().forEach(System.out::println);
    }

    @Autowired
    FileMapper fileMapper;
    @Test
    void t3(){
        fileMapper.fileDtoList().forEach(System.out::println);
    }
}

运行结果

image.png

image.png

image.png 参考文章

使用Druid数据库连接池

  1. 这样整合版druid-spring-boot-starter
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.23</version>
</dependency>

application.yml

spring:
    # 单数据源
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost/springboot3
    username: root
    password: root
    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

@Autowired
DataSource dataSource;
@Test
void t1(){
    String string = dataSource.getClass().toString();
    System.out.println(string);
}

image.png

这个有问题，虽然数据库连接池有效了，但是application.yml的druid配置的信息不能起作用，我不知道为啥，我看他们的博客都这样写，但是我就不能成功，如果要有效，必须写个配置类，去配置DataSource这个Bean。然后才能生效。

  1. 一般导入使用druid库不使用stater这个
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.23</version>
</dependency>

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost/springboot3
    username: root
    password: root
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000

这个配置类是必须的，不然不会生效

package wnan.explore.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/*****UTF-8*****
 * Description: druid配置
 * Author: wnan
 * Create Date: 2024/10/8 12:15
 * Proverbs: 吃得苦中苦，方为人上人
 */
@Configuration
public class DruidConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.druid")
    public DataSource getDruidDataSource(){
        return new DruidDataSource();
    }
}

@Autowired
DataSource dataSource;
@Test
void t1(){
    String string = dataSource.getClass().toString();
    System.out.println(string);
    // 确保dataSource实际是DruidDataSource类型
    if (dataSource instanceof DruidDataSource) {
        DruidDataSource druidDataSource = (DruidDataSource) dataSource;

        // 打印Druid数据源的配置属性
        System.out.println("Initial Size: " + druidDataSource.getInitialSize());
        System.out.println("Min Idle: " + druidDataSource.getMinIdle());
        System.out.println("Max Active: " + druidDataSource.getMaxActive());
        System.out.println("Max Wait: " + druidDataSource.getMaxWait());
        System.out.println("Time Between Eviction RunsMillis: " + druidDataSource.getTimeBetweenEvictionRunsMillis());
    } else {
        System.out.println("The injected DataSource is not an instance of DruidDataSource.");
    }

运行结果：

class com.alibaba.druid.pool.DruidDataSource
Initial Size: 5
Min Idle: 5
Max Active: 20
Max Wait: 60000
Time Between Eviction RunsMillis: 60000

源码--->>>springboot-explore

avatar
文章
阅读
粉丝