# Spring Boot Project Database Configuration for Local and Cloud

> Configuring MySQL and Redis for a Spring Boot application in both local and cloud-server environments: installation, connection configuration, a Redis test endpoint, entity-class changes, and start-up verification.

Source: https://chanmeng.org/blog/spring-boot-database-configuration
Author: Chan Meng — https://chanmeng.org
Published: 2025-01-23
Tags: Integrations

---

*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:

```json
{
    "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.