Spring Boot Project Database Configuration Guide for Local and Cloud Environments

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

1. Run MySQL installer
2. Select “Developer Default” installation type
3. Configure root password
4. Complete installation

2.2 Create Project Database

1. 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

1. Run Redis-x64-xxx.msi
2. Check “Add the Redis installation folder to the PATH environment variable”
3. 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

1. 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

1. 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

1. 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

1. Access Denied Error:

• Verify username and password
• Confirm user database permissions

1. Communications Link Failure:

• Check network connection
• Ensure MySQL service is running
• Verify firewall settings

7.2 Redis Connection Issues

1. Connection Timeout:

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

1. 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:

1. 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.