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

更新時間：2026年02月12日 10:38:33 作者：JavaBoy_XJ

本文詳細介紹了在Spring Boot項目中配置Mysql數(shù)據(jù)庫的各個方面,包括基礎(chǔ)配置、高級配置、JPA/Hibernate配置、事務(wù)管理、性能優(yōu)化、安全配置以及故障排除,同時,提供了常用配置參數(shù)的說明和連接池的選擇建議,感興趣的朋友跟隨小編一起看看吧

1. 基礎(chǔ)配置

1.1 Maven依賴

<dependencies>
    <!-- Spring Boot Starter Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- MySQL驅(qū)動 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version> <!-- 根據(jù)實際版本調(diào)整 -->
        <scope>runtime</scope>
    </dependency>
    <!-- 連接池（HikariCP已包含在starter中） -->
    <!-- 或者使用其他連接池 -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

1.2 application.yml 基礎(chǔ)配置

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 多數(shù)據(jù)源配置

@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 連接池優(yōu)化配置

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
      # 性能優(yōu)化
      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轉(zhuǎn)換器
@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. 事務(wù)管理配置

4.1 聲明式事務(wù)

@Configuration
@EnableTransactionManagement
public class TransactionConfig {
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}
// 服務(wù)層使用
@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 編程式事務(wù)

@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 {
                    // 業(yè)務(wù)操作
                    // ...
                } 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 {
            // 業(yè)務(wù)操作
            // ...
            transactionManager.commit(status);
        } catch (Exception e) {
            transactionManager.rollback(status);
            throw e;
        }
    }
}

5. 性能優(yōu)化配置

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 連接池監(jiān)控

@Configuration
public class HikariMetricsConfig {
    @Bean
    public MeterRegistryCustomizer<MeterRegistry> metricsCommonTags() {
        return registry -> registry.config().commonTags("application", "your-app-name");
    }
}
// 在application.yml中啟用監(jiān)控
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) {
        // 實現(xiàn)解密邏輯
        return encrypted; // 實際應(yīng)用中需要解密
    }
}

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 生產(chǎn)環(huán)境配置
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
# 監(jiān)控配置
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 重要參數(shù)說明

useSSL: 是否使用SSL加密連接
serverTimezone: 服務(wù)器時區(qū)設(shè)置
allowPublicKeyRetrieval: 允許公鑰檢索
characterEncoding: 字符編碼
rewriteBatchedStatements: 批量語句重寫（性能優(yōu)化）
useServerPrepStmts: 使用服務(wù)器端預(yù)處理語句

9.2 DDL-Auto 選項

none: 不做任何操作
validate: 驗證表結(jié)構(gòu)，不修改
update: 更新表結(jié)構(gòu)
create: 每次啟動創(chuàng)建新表（會刪除舊數(shù)據(jù)）
create-drop: 啟動創(chuàng)建，關(guān)閉刪除

9.3 連接池選擇

HikariCP (默認): 高性能，推薦使用
Tomcat JDBC: 替代選擇
DBCP2: Apache Commons項目
C3P0: 老牌連接池（基本不怎么用）
Druid: 阿里出品，必屬精品（必須推薦）

到此這篇關(guān)于Mysql在 Spring Boot 項目中的完整配置指南的文章就介紹到這了,更多相關(guān)mysql在springboot配置內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线