Replacing MySQL with Apache Doris in Spring Boot for Real‑Time Analytics

Background

When analytical queries need to scan tens of millions of rows, MySQL often times out or suffers severe performance degradation, making it unsuitable for real‑time analytics such as e‑commerce inventory monitoring, anomaly detection, or marketing campaign adjustment.

Apache Doris

Architecture and capabilities

Massively Parallel Processing (MPP) reduces single‑query latency to milliseconds, delivering 10‑100× faster responses than MySQL on large datasets.

Real‑time ingestion supports Kafka, Flink and synchronization from MySQL, Hive, etc., with second‑level latency.

MySQL protocol compatibility allows use of the standard MySQL JDBC driver without SQL syntax changes.

High concurrency handles thousands of queries per second, suitable for user‑facing analytical applications.

Simplified operations can be deployed as a single node or scaled to hundreds of nodes with automatic sharding and load balancing.

Workload comparison

MySQL excels at "write‑heavy, read‑light" transactional workloads (e.g., order creation, user registration). Apache Doris is designed for "read‑heavy, write‑light" analytical scenarios such as statistical reporting and dashboard generation.

Spring Boot Integration

Prerequisites

JDK 1.8+, Spring Boot 2.3+, Maven 3.6+

Deploy Apache Doris via Docker for testing or follow the official documentation for a production cluster.

Doris JDBC driver is not required separately; the standard MySQL JDBC driver works because Doris implements the MySQL protocol.

Project scaffolding

src/
├── main/
│   ├── java/com/example/dorisdemo/
│   │   ├── controller/   // API layer
│   │   ├── entity/        // Entity classes
│   │   ├── repository/    // Data access layer
│   │   ├── service/       // Business logic
│   │   └── DorisDemoApplication.java // Main class
│   └── resources/
│       └── application.yml // Configuration file
└── pom.xml // Maven dependencies

Dependencies (pom.xml)

<!-- Spring Web -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- Spring Data JPA -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- MySQL JDBC (compatible with Doris) -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

<!-- Lombok (optional) -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>

Data source configuration (application.yml)

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:9030/demo_db?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    username: root   # Doris default user
    password: 123456 # Set during deployment
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: none   # Disable auto DDL; Doris tables are created manually
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
    show-sql: true
    format_sql: true
logging:
  level:
    org.springframework.data.jpa: debug
    com.example.dorisdemo: info

Doris table definition

-- Create database
CREATE DATABASE IF NOT EXISTS demo_db;
USE demo_db;

-- Create partitioned user_behavior table
CREATE TABLE IF NOT EXISTS user_behavior (
    user_id BIGINT COMMENT '用户ID',
    product_id BIGINT COMMENT '商品ID',
    category_id INT COMMENT '商品分类ID',
    behavior_type VARCHAR(20) COMMENT '行为类型（click/purchase/collect）',
    create_time DATETIME COMMENT '行为时间'
) ENGINE=OLAP
DUPLICATE KEY(user_id, product_id)
PARTITION BY RANGE (create_time) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01')
)
DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES (
    "storage_medium" = "HDD",
    "storage_ttl" = "30 DAY"
);

Entity mapping (UserBehavior.java)

package com.example.dorisdemo.entity;

import lombok.Data;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import javax.persistence.*;
import java.time.LocalDateTime;

@Data
@Entity
@Table(name = "user_behavior")
@DynamicInsert
@DynamicUpdate
public class UserBehavior {
    @Id
    @Column(name = "user_id")
    private Long userId;

    @Column(name = "product_id")
    private Long productId;

    @Column(name = "category_id")
    private Integer categoryId;

    @Column(name = "behavior_type")
    private String behaviorType; // click/purchase/collect

    @Column(name = "create_time")
    private LocalDateTime createTime;
}

Repository interface (UserBehaviorRepository.java)

package com.example.dorisdemo.repository;

import com.example.dorisdemo.entity.UserBehavior;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface UserBehaviorRepository extends JpaRepository<UserBehavior, Long> {
    // Find behaviors within a date range
    List<UserBehavior> findByCreateTimeBetween(LocalDateTime start, LocalDateTime end);

    // Count clicks for a specific category (optimized aggregation in Doris)
    @Query(value = "SELECT COUNT(*) FROM user_behavior WHERE category_id = :categoryId AND behavior_type = 'click' AND create_time BETWEEN :start AND :end", nativeQuery = true)
    Long countClickByCategoryId(@Param("categoryId") Integer categoryId,
                                @Param("start") LocalDateTime start,
                                @Param("end") LocalDateTime end);
}

Service layer (UserBehaviorService.java)

package com.example.dorisdemo.service;

import com.example.dorisdemo.entity.UserBehavior;
import com.example.dorisdemo.repository.UserBehaviorRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import java.util.List;

@Service
@RequiredArgsConstructor
public class UserBehaviorService {
    private final UserBehaviorRepository behaviorRepository;

    // Save a single record
    public UserBehavior save(UserBehavior behavior) {
        return behaviorRepository.save(behavior);
    }

    // Batch save for bulk import
    public List<UserBehavior> batchSave(List<UserBehavior> behaviors) {
        return behaviorRepository.saveAll(behaviors);
    }

    // Query by date range
    public List<UserBehavior> getBehaviorByDateRange(LocalDateTime start, LocalDateTime end) {
        return behaviorRepository.findByCreateTimeBetween(start, end);
    }

    // Real‑time click count per category
    public Long getCategoryClickCount(Integer categoryId, LocalDateTime start, LocalDateTime end) {
        return behaviorRepository.countClickByCategoryId(categoryId, start, end);
    }
}

Controller layer (UserBehaviorController.java)

package com.example.dorisdemo.controller;

import com.example.dorisdemo.entity.UserBehavior;
import com.example.dorisdemo.service.UserBehaviorService;
import lombok.RequiredArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.*;
import java.time.LocalDateTime;
import java.util.List;

@RestController
@RequestMapping("/user-behavior")
@RequiredArgsConstructor
public class UserBehaviorController {
    private final UserBehaviorService behaviorService;

    // Save a single behavior
    @PostMapping
    public UserBehavior save(@RequestBody UserBehavior behavior) {
        return behaviorService.save(behavior);
    }

    // Batch save
    @PostMapping("/batch")
    public List<UserBehavior> batchSave(@RequestBody List<UserBehavior> behaviors) {
        return behaviorService.batchSave(behaviors);
    }

    // Query by date range
    @GetMapping("/range")
    public List<UserBehavior> getByRange(@RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime start,
                                         @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime end) {
        return behaviorService.getBehaviorByDateRange(start, end);
    }

    // Real‑time click count per category
    @GetMapping("/click-count")
    public Long getClickCount(@RequestParam Integer categoryId,
                              @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime start,
                              @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") LocalDateTime end) {
        return behaviorService.getCategoryClickCount(categoryId, start, end);
    }
}

Testing and verification

Start the application

Run DorisDemoApplication.java and ensure the Spring Boot service starts without errors.

API examples

POST /user-behavior/batch
Content-Type: application/json
[
  {"userId":1001,"productId":2001,"categoryId":301,"behaviorType":"click","createTime":"2024-01-15 10:30:00"},
  {"userId":1002,"productId":2002,"categoryId":301,"behaviorType":"click","createTime":"2024-01-15 11:20:00"},
  {"userId":1003,"productId":2003,"categoryId":302,"behaviorType":"purchase","createTime":"2024-01-15 14:10:00"}
]

GET /user-behavior/click-count?categoryId=301&start=2024-01-01%2000:00:00&end=2024-01-31%2023:59:59

Performance benchmark

Using one million rows of user_behavior data, query latency was measured for MySQL and Doris. The chart shows Doris maintaining millisecond‑level latency while MySQL latency grows dramatically as the data volume increases.

Conclusion

Integrating Apache Doris with Spring Boot requires only the MySQL JDBC driver because Doris implements the MySQL protocol. The combination delivers millisecond‑level query latency for OLAP workloads and scales efficiently to petabyte‑scale real‑time analytics without the operational complexity of a custom distributed system.