Spring Boot Project Database Configuration Guide for Local and Cloud Environments

Chan Meng

4 min read1 day ago

1. Prerequisites

1.1 Required Software Downloads

MySQL 8.0: https://dev.mysql.com/downloads/mysql/
Redis for Windows: https://github.com/microsoftarchive/redis/releases
IDE: IntelliJ IDEA (Recommended)

1.2 Project Structure Overview

This project is a TikTok e-commerce application using Spring Boot, with the following key configuration files:

application-dev.yml: Development environment configuration
RedisClientConfig.java: Redis configuration class
RedisClientConfigProperties.java: Redis properties configuration class
Constants.java: Constants definition class

2. MySQL Configuration

2.1 MySQL Installation

Run MySQL installer
Select “Developer Default” installation type
Configure root password
Complete installation

2.2 Create Project Database

Open Command Prompt (with admin privileges):

mysql -u root -p
# Enter your set password

2. Create database:

CREATE DATABASE IF NOT EXISTS douyin_mall DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE douyin_mall;

2.3 Local MySQL Connection Configuration

In application-dev.yml:

spring:
  datasource:
    username: root
    password: {your_mysql_password}  # Replace with your password
    url: jdbc:mysql://127.0.0.1:3306/douyin_mall?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&useSSL=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource

  jpa:
    database-platform: org.hibernate.dialect.MySQL8Dialect
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        format_sql: true

  hikari:
    pool-name: Retail_HikariCP
    minimum-idle: 15 
    maximum-pool-size: 25
    idle-timeout: 180000 # Max idle connection time, default 600000 (10 minutes)
    max-lifetime: 1800000 # Max connection lifetime, default 1800000 (30 minutes)
    connection-timeout: 60000 # Connection timeout, default 30 seconds
    auto-commit: true  
    connection-test-query: SELECT 1

3. Redis Configuration

3.1 Redis Installation

Run Redis-x64-xxx.msi
Check “Add the Redis installation folder to the PATH environment variable”
Verify installation after completion:

# Open new command prompt
redis-cli
127.0.0.1:6379> ping
# PONG indicates successful installation

3.2 Redis Connection Configuration

Add to application-dev.yml:

redis:
  sdk:
    config:
      host: 127.0.0.1
      port: 6379
      pool-size: 10
      min-idle-size: 5
      idle-timeout: 30000
      connect-timeout: 5000
      retry-attempts: 3
      retry-interval: 1000
      ping-interval: 60000
      keep-alive: true

3.3 Implement Redis Test Interface

Create Redis Test Controller (src/main/java/com/qxy/controller/RedisTestController.java):

package com.qxy.controller;

import com.qxy.common.response.Response;
import com.qxy.common.response.ResponseCode;
import com.qxy.infrastructure.redis.IRedisService;
import org.redisson.api.RMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
@RequestMapping("/api/redis")
public class RedisTestController {
    
    @Resource
    private IRedisService redisService;
    
    @GetMapping("/test")
    public Response<String> testRedis() {
        try {
            // Test basic operations
            redisService.setValue("test_key", "Redis connection test");
            String value = redisService.getValue("test_key");
            
            // Test numeric operations
            redisService.setAtomicLong("test_count", 0);
            long count = redisService.incr("test_count");
            
            // Test Map structure
            RMap<String, String> map = redisService.getMap("test_map");
            map.fastPut("key1", "value1");
            
            return Response.<String>builder()
                .code(ResponseCode.SUCCESS.getCode())
                .info(ResponseCode.SUCCESS.getInfo())
                .data("Redis test successful! value=" + value + ", count=" + count)
                .build();
                
        } catch (Exception e) {
            return Response.<String>builder()
                .code(ResponseCode.UN_ERROR.getCode())
                .info("Redis connection test failed: " + e.getMessage())
                .build();
        }
    }
}

2. Add Redis key constants in Constants class:

public class Constants {
    // Other constant definitions...

    public static class RedisKey {
        public static final String TEST_PREFIX = "test:";
        public static final String USER_PREFIX = "user:";
        public static final String PRODUCT_PREFIX = "product:";
        public static final String ORDER_PREFIX = "order:";
        
        public static String getKey(String prefix, String key) {
            return prefix + key;
        }
    }
}

4. Cloud Server Database Configuration

4.1 Connection Information

Cloud server database connection details:

Server IP: [REDACTED_IP_ADDRESS]
Database Port: [REDACTED_PORT]
Username: [REDACTED_USERNAME]
Password: [REDACTED_PASSWORD]

4.2 Update Configuration File

Modify datasource configuration in application-dev.yml:

spring:
  datasource:
    username: [REDACTED_USERNAME]    # Cloud database username
    password: [REDACTED_PASSWORD]  # Cloud database password
    url: jdbc:mysql://[REDACTED_IP_ADDRESS]:[REDACTED_PORT]/douyin_mall?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&useSSL=true

5. Entity Class Configuration

5.1 Modify AiOrder Entity Class

Update src/main/java/com/qxy/model/po/AiOrder.java:

package com.qxy.model.po;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "ai_order")  // Avoid MySQL keywords
public class AiOrder {
    @Id
    private String orderId;
    private String status;

    // Getters and Setters
    public String getOrderId() {
        return orderId;
    }

    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

6. Verification and Testing

6.1 Project Startup

Ensure services are running:

# Check MySQL service
net start mysql

# Check Redis service
redis-cli ping

2. Run Application.java

3. Observe startup logs for any error messages

6.2 Interface Testing

Redis Test:

Access http://localhost:8080/api/redis/test
Expected response format:

{
    "code": "0000",
    "info": "Call Successful",
    "data": "Redis test successful! value=Redis connection test, count=1"
}

2. MySQL Connection Test:

mysql -h 116.62.149.227 -P 13306 -u root -p123456
use douyin_mall;
show tables;
# Check if ai_order table exists

7. Troubleshooting Common Issues

7.1 MySQL Connection Problems

Access Denied Error:

Verify username and password
Confirm user database permissions

Communications Link Failure:

Check network connection
Ensure MySQL service is running
Verify firewall settings

7.2 Redis Connection Issues

Connection Timeout:

Confirm Redis service status
Check redis.conf configuration
Verify port availability

Data Operation Failures:

Test basic operations with redis-cli
Check memory usage
Review Redis logs

7.3 Entity Mapping Problems

If encountering table name errors:

# Login to MySQL and delete incorrect table
mysql -h 116.62.149.227 -P 13306 -u root -p123456
use douyin_mall;
DROP TABLE IF EXISTS `order`;

Restart the application to let Hibernate recreate the correct table structure.

8. Configuration Verification Checklist

After complete configuration, confirm the following:

Basic Configuration

MySQL service running normally
Redis service running normally
application-dev.yml configured correctly

2. Database Connection

Command-line cloud database connection possible
Project can connect to database
Database table structures created correctly

3. Redis Functionality

Redis test interface returns success
Normal data storage and retrieval
Connection pool functioning correctly

4. Application Status

No error logs during startup
Interface calls normal
Data operations correct

If any checklist item fails, refer to the corresponding section for troubleshooting and repair.