当前位置： 代码网 > it编程>编程语言>Java > Mysql在Spring Boot项目中的完整配置教程

Mysql在Spring Boot项目中的完整配置教程

2026年02月12日 • Java •我要评论
1. 基础配置1.1 maven依赖<dependencies> <!-- spring boot starter data jpa --> <depende

1. 基础配置

1.1 maven依赖

<dependencies>
    <!-- spring boot starter data jpa -->
    <dependency>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-starter-data-jpa</artifactid>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
        <groupid>mysql</groupid>
        <artifactid>mysql-connector-java</artifactid>
        <version>8.0.33</version> <!-- 根据实际版本调整 -->
        <scope>runtime</scope>
    </dependency>
    <!-- 连接池（hikaricp已包含在starter中） -->
    <!-- 或者使用其他连接池 -->
    <dependency>
        <groupid>com.zaxxer</groupid>
        <artifactid>hikaricp</artifactid>
    </dependency>
</dependencies>

1.2 application.yml 基础配置

spring:
  datasource:
    # 基本配置
    url: jdbc:mysql://localhost:3306/your_database?useunicode=true&characterencoding=utf8&usessl=false&servertimezone=asia/shanghai
    username: root
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.driver
    # hikari连接池配置
    hikari:
      connection-timeout: 30000
      maximum-pool-size: 20
      minimum-idle: 10
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: select 1
      pool-name: hikaripool-yourapp
  # jpa配置
  jpa:
    database-platform: org.hibernate.dialect.mysql8dialect
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true
        jdbc:
          batch_size: 20
        order_inserts: true
        order_updates: true

1.3 application.properties 版本

# 基本配置
spring.datasource.url=jdbc:mysql://localhost:3306/your_database?useunicode=true&characterencoding=utf8&usessl=false&servertimezone=asia/shanghai
spring.datasource.username=root
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.driver
# hikari连接池配置
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-test-query=select 1
# jpa配置
spring.jpa.database-platform=org.hibernate.dialect.mysql8dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.jdbc.batch_size=20

2. 高级配置

2.1 多数据源配置

@configuration
public class datasourceconfig {
    @primary
    @bean(name = "primarydatasource")
    @configurationproperties(prefix = "spring.datasource.primary")
    public datasource primarydatasource() {
        return datasourcebuilder.create().build();
    }
    @bean(name = "secondarydatasource")
    @configurationproperties(prefix = "spring.datasource.secondary")
    public datasource secondarydatasource() {
        return datasourcebuilder.create().build();
    }
    @primary
    @bean(name = "primaryentitymanager")
    public localcontainerentitymanagerfactorybean primaryentitymanager(
            entitymanagerfactorybuilder builder,
            @qualifier("primarydatasource") datasource datasource) {
        return builder
                .datasource(datasource)
                .packages("com.example.primary.entity")
                .persistenceunit("primary")
                .build();
    }
    @bean(name = "secondaryentitymanager")
    public localcontainerentitymanagerfactorybean secondaryentitymanager(
            entitymanagerfactorybuilder builder,
            @qualifier("secondarydatasource") datasource datasource) {
        return builder
                .datasource(datasource)
                .packages("com.example.secondary.entity")
                .persistenceunit("secondary")
                .build();
    }
}

yaml配置：

spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/primary_db
      username: root
      password: password1
      driver-class-name: com.mysql.cj.jdbc.driver
      hikari:
        maximum-pool-size: 15
    secondary:
      url: jdbc:mysql://localhost:3306/secondary_db
      username: root
      password: password2
      driver-class-name: com.mysql.cj.jdbc.driver
      hikari:
        maximum-pool-size: 10

2.2 读写分离配置

@configuration
@enabletransactionmanagement
public class readwritedatasourceconfig {
    @bean
    @configurationproperties(prefix = "spring.datasource.write")
    public datasource writedatasource() {
        return datasourcebuilder.create().build();
    }
    @bean
    @configurationproperties(prefix = "spring.datasource.read")
    public datasource readdatasource() {
        return datasourcebuilder.create().build();
    }
    @bean
    public datasource routingdatasource(
            @qualifier("writedatasource") datasource writedatasource,
            @qualifier("readdatasource") datasource readdatasource) {
        map<object, object> targetdatasources = new hashmap<>();
        targetdatasources.put(datasourcetype.write, writedatasource);
        targetdatasources.put(datasourcetype.read, readdatasource);
        routingdatasource routingdatasource = new routingdatasource();
        routingdatasource.setdefaulttargetdatasource(writedatasource);
        routingdatasource.settargetdatasources(targetdatasources);
        return routingdatasource;
    }
}

2.3 连接池优化配置

spring:
  datasource:
    hikari:
      # 连接池大小配置
      maximum-pool-size: 50
      minimum-idle: 10
      # 连接生命周期
      max-lifetime: 1800000  # 30分钟
      idle-timeout: 600000   # 10分钟
      connection-timeout: 30000  # 30秒
      # 连接验证
      connection-test-query: select 1
      validation-timeout: 5000
      # 性能优化
      leak-detection-threshold: 60000  # 60秒
      initialization-fail-timeout: 1
      # 连接属性
      data-source-properties:
        cacheprepstmts: true
        prepstmtcachesize: 250
        prepstmtcachesqllimit: 2048
        useserverprepstmts: true
        uselocalsessionstate: true
        rewritebatchedstatements: true
        cacheresultsetmetadata: true
        cacheserverconfiguration: true
        elidesetautocommits: true
        maintaintimestats: false

3. jpa/hibernate 高级配置

3.1 实体类配置

@entity
@table(name = "users")
@data
@builder
@noargsconstructor
@allargsconstructor
@entitylisteners(auditingentitylistener.class)
public class user {
    @id
    @generatedvalue(strategy = generationtype.identity)
    private long id;
    @column(name = "username", nullable = false, unique = true, length = 50)
    private string username;
    @column(name = "email", nullable = false, unique = true)
    private string email;
    @column(name = "age")
    private integer age;
    @enumerated(enumtype.string)
    @column(name = "status")
    private userstatus status;
    @column(name = "created_at")
    @createddate
    private localdatetime createdat;
    @column(name = "updated_at")
    @lastmodifieddate
    private localdatetime updatedat;
    @version
    @column(name = "version")
    private integer version;
    @column(name = "metadata", columndefinition = "json")
    @convert(converter = jsonconverter.class)
    private map<string, object> metadata;
}
// json转换器
@converter
public class jsonconverter implements attributeconverter<map<string, object>, string> {
    private static final objectmapper objectmapper = new objectmapper();
    @override
    public string converttodatabasecolumn(map<string, object> attribute) {
        try {
            return objectmapper.writevalueasstring(attribute);
        } catch (jsonprocessingexception e) {
            throw new runtimeexception(e);
        }
    }
    @override
    public map<string, object> converttoentityattribute(string dbdata) {
        try {
            return objectmapper.readvalue(dbdata, map.class);
        } catch (jsonprocessingexception e) {
            throw new runtimeexception(e);
        }
    }
}

3.2 repository配置

@repository
public interface userrepository extends jparepository<user, long>,
                                        jpaspecificationexecutor<user>,
                                        querydslpredicateexecutor<user> {
    // 方法名查询
    optional<user> findbyusername(string username);
    list<user> findbyagegreaterthan(int age);
    // @query注解查询
    @query("select u from user u where u.email like %:email%")
    list<user> findbyemailcontaining(@param("email") string email);
    @query(value = "select * from users where age > :age", nativequery = true)
    list<user> findusersbyagenative(@param("age") int age);
    // 修改查询
    @modifying
    @query("update user u set u.status = :status where u.id = :id")
    @transactional
    int updateuserstatus(@param("id") long id, @param("status") userstatus status);
}

3.3 审计配置

@configuration
@enablejpaauditing
public class jpaauditingconfig {
    @bean
    public auditoraware<string> auditoraware() {
        return () -> optional.ofnullable(securitycontextholder.getcontext())
                .map(securitycontext::getauthentication)
                .filter(authentication::isauthenticated)
                .map(authentication::getname)
                .or(() -> optional.of("system"));
    }
}

4. 事务管理配置

4.1 声明式事务

@configuration
@enabletransactionmanagement
public class transactionconfig {
    @bean
    public platformtransactionmanager transactionmanager(entitymanagerfactory entitymanagerfactory) {
        return new jpatransactionmanager(entitymanagerfactory);
    }
}
// 服务层使用
@service
@transactional
public class userservice {
    @autowired
    private userrepository userrepository;
    @transactional(readonly = true)
    public user getuserbyid(long id) {
        return userrepository.findbyid(id).orelse(null);
    }
    @transactional(rollbackfor = exception.class, propagation = propagation.required)
    public user createuser(user user) {
        return userrepository.save(user);
    }
    @transactional(propagation = propagation.requires_new)
    public void updateuserstatus(long id, userstatus status) {
        userrepository.updateuserstatus(id, status);
    }
}

4.2 编程式事务

@service
public class userservice {
    @autowired
    private platformtransactionmanager transactionmanager;
    @autowired
    private transactiontemplate transactiontemplate;
    public void complexoperation() {
        transactiontemplate.execute(new transactioncallbackwithoutresult() {
            @override
            protected void dointransactionwithoutresult(transactionstatus status) {
                try {
                    // 业务操作
                    // ...
                } catch (exception e) {
                    status.setrollbackonly();
                    throw e;
                }
            }
        });
    }
    public void manualtransaction() {
        defaulttransactiondefinition definition = new defaulttransactiondefinition();
        definition.setpropagationbehavior(transactiondefinition.propagation_required);
        definition.setisolationlevel(transactiondefinition.isolation_read_committed);
        definition.settimeout(30);
        transactionstatus status = transactionmanager.gettransaction(definition);
        try {
            // 业务操作
            // ...
            transactionmanager.commit(status);
        } catch (exception e) {
            transactionmanager.rollback(status);
            throw e;
        }
    }
}

5. 性能优化配置

5.1 批量操作

spring:
  jpa:
    properties:
      hibernate:
        # 批量插入/更新
        jdbc.batch_size: 50
        order_inserts: true
        order_updates: true
        # 二级缓存配置
        cache.use_second_level_cache: true
        cache.use_query_cache: true
        cache.region.factory_class: org.hibernate.cache.ehcache.ehcacheregionfactory
        generate_statistics: true

5.2 连接池监控

@configuration
public class hikarimetricsconfig {
    @bean
    public meterregistrycustomizer<meterregistry> metricscommontags() {
        return registry -> registry.config().commontags("application", "your-app-name");
    }
}
// 在application.yml中启用监控
management:
  endpoints:
    web:
      exposure:
        include: health,info,metrics,prometheus
  metrics:
    enable:
      hikaricp: true

6. 安全配置

6.1 加密密码配置

@configuration
public class datasourcesecurityconfig {
    @value("${datasource.password}")
    private string encryptedpassword;
    @bean
    public datasource datasource() {
        // 解密密码
        string decryptedpassword = decryptpassword(encryptedpassword);
        hikaridatasource datasource = new hikaridatasource();
        datasource.setjdbcurl("jdbc:mysql://localhost:3306/your_database");
        datasource.setusername("root");
        datasource.setpassword(decryptedpassword);
        // ... 其他配置
        return datasource;
    }
    private string decryptpassword(string encrypted) {
        // 实现解密逻辑
        return encrypted; // 实际应用中需要解密
    }
}

6.2 ssl连接配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database?usessl=true&requiressl=true&verifyservercertificate=true&servertimezone=asia/shanghai
    hikari:
      data-source-properties:
        sslmode: required
        trustcertificatekeystoreurl: file:/path/to/keystore.jks
        trustcertificatekeystorepassword: keystore_password

7. 故障排除配置

7.1 日志配置

logging:
  level:
    org.hibernate.sql: debug
    org.hibernate.type.descriptor.sql.basicbinder: trace
    org.springframework.orm.jpa: debug
    org.springframework.transaction: debug
    com.zaxxer.hikari: debug

7.2 健康检查

management:
  health:
    db:
      enabled: true
    diskspace:
      enabled: true
  endpoint:
    health:
      show-details: always

8. 完整配置示例

# application-prod.yml 生产环境配置
spring:
  datasource:
    url: jdbc:mysql://${db_host:localhost}:${db_port:3306}/${db_name:production_db}?useunicode=true&characterencoding=utf8&usessl=true&requiressl=true&verifyservercertificate=true&servertimezone=asia/shanghai&allowpublickeyretrieval=true
    username: ${db_username:app_user}
    password: ${db_password:strong_password}
    driver-class-name: com.mysql.cj.jdbc.driver
    hikari:
      maximum-pool-size: ${db_pool_size:50}
      minimum-idle: 10
      max-lifetime: 1800000
      idle-timeout: 600000
      connection-timeout: 30000
      connection-test-query: select 1
      leak-detection-threshold: 120000
      validation-timeout: 5000
      data-source-properties:
        cacheprepstmts: true
        prepstmtcachesize: 250
        prepstmtcachesqllimit: 2048
        useserverprepstmts: true
        rewritebatchedstatements: true
  jpa:
    database-platform: org.hibernate.dialect.mysql8dialect
    show-sql: false
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        jdbc:
          batch_size: 30
        order_inserts: true
        order_updates: true
        generate_statistics: false
        cache:
          use_second_level_cache: true
          region:
            factory_class: org.hibernate.cache.jcache.jcacheregionfactory
        query:
          in_clause_parameter_padding: true
  sql:
    init:
      mode: never
# 监控配置
management:
  endpoints:
    web:
      exposure:
        include: health,info,metrics
  metrics:
    enable:
      hikaricp: true
  health:
    db:
      enabled: true
# 日志配置
logging:
  level:
    com.yourcompany: info
    org.springframework: warn
    org.hibernate: error

9. 常用配置说明

9.1 重要参数说明

  • usessl: 是否使用ssl加密连接
  • servertimezone: 服务器时区设置
  • allowpublickeyretrieval: 允许公钥检索
  • characterencoding: 字符编码
  • rewritebatchedstatements: 批量语句重写（性能优化）
  • useserverprepstmts: 使用服务器端预处理语句

9.2 ddl-auto 选项

  • none: 不做任何操作
  • validate: 验证表结构，不修改
  • update: 更新表结构
  • create: 每次启动创建新表（会删除旧数据）
  • create-drop: 启动创建，关闭删除

9.3 连接池选择

  • hikaricp (默认): 高性能，推荐使用
  • tomcat jdbc: 替代选择
  • dbcp2: apache commons项目
  • c3p0: 老牌连接池（基本不怎么用）
  • druid: 阿里出品，必属精品（必须推荐）

到此这篇关于mysql在 spring boot 项目中的完整配置指南的文章就介绍到这了,更多相关mysql在springboot配置内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com