Spring Boot Project Database Configuration for Local and Cloud

Originally published on LinkedIn, 23 January 2025. Republished here with light editing.

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.