Integrating Elasticsearch with MySQL in a Spring Boot Backend: Technical Selection, Common Package, Repository, Mapper, and Service Implementation

The author outlines a step‑by‑step approach for using Elasticsearch (ES) together with MySQL in a Spring Boot micro‑service architecture, emphasizing the performance benefits of ES for full‑text and complex queries over traditional relational databases.

1. Technical Selection

Elasticsearch is chosen to complement MySQL for large‑scale, high‑concurrency search scenarios, offering optimized full‑text search, efficient complex queries, and real‑time retrieval.

2. Create a Common Elasticsearch Package

Instead of importing ES directly into each service, a dedicated common module is created to house the ES service, facilitating future unified management.

3. Import Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-elasticsearch</artifactId>
</dependency>

The ES dependency is added to the service that requires it.

4. Database Preparation

SQL scripts are provided to create the tb_question table in MySQL, including fields such as question_id , title , difficulty , time_limit , content , etc.

5. Entity Classes

5.1 Elasticsearch Entity (QuestionES)

import org.springframework.data.elasticsearch.annotations.Document;
import lombok.Getter;
import lombok.Setter;
import org.springframework.data.annotation.Id;
import org.springframework.data.elasticsearch.annotations.Field;
import org.springframework.data.elasticsearch.annotations.FieldType;
import java.time.LocalDateTime;

@Getter
@Setter
@Document(indexName = "idx_question")
public class QuestionES {
    @Id
    @Field(type = FieldType.Long)
    private Long questionId;

    @Field(type = FieldType.Text, analyzer = "ik_max_word", searchAnalyzer = "ik_max_word")
    private String title;

    @Field(type = FieldType.Byte)
    private Integer difficulty;

    @Field(type = FieldType.Long)
    private Long timeLimit;

    @Field(type = FieldType.Long)
    private Long spaceLimit;

    @Field(type = FieldType.Text, analyzer = "ik_max_word", searchAnalyzer = "ik_max_word")
    private String content;

    @Field(type = FieldType.Text)
    private String questionCase;

    @Field(type = FieldType.Text)
    private String mainFunc;

    @Field(type = FieldType.Text)
    private String defaultCode;

    @Field(type = FieldType.Date, format = DateFormat.date_hour_minute_second)
    private LocalDateTime createTime;
}

5.2 MySQL Entity (Question)

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.guan.common.core.domain.BaseEntity;
import lombok.Getter;
import lombok.Setter;

@TableName("tb_question")
@Getter
@Setter
public class Question extends BaseEntity {
    @TableId(type = IdType.ASSIGN_ID)
    private Long questionId;
    private String title;
    private Integer difficulty;
    private Long timeLimit;
    private Long spaceLimit;
    private String content;
    private String questionCase;
    private String defaultCode;
    private String mainFunc;
}

6. Repository and Mapper Interfaces

6.1 Elasticsearch Repository

import com.guan.friend.domain.question.es.QuestionES;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.elasticsearch.annotations.Query;
import org.springframework.data.elasticsearch.repository.ElasticsearchRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface IQuestionRepository extends ElasticsearchRepository<QuestionES, Long> {
    Page<QuestionES> findQuestionByDifficulty(Integer difficulty, Pageable pageable);

    @Query("{\"bool\":{\"should\":[{\"match\":{\"title\":\"?0\"}},{\"match\":{\"content\":\"?1\"}}],\"minimum_should_match\":1,\"must\":[{\"term\":{\"difficulty\":\"?2\"}}]}")
    Page<QuestionES> findByTitleOrContentAndDifficulty(String keywordTitle, String keywordContent, Integer difficulty, Pageable pageable);

    @Query("{\"bool\":{\"should\":[{\"match\":{\"title\":\"?0\"}},{\"match\":{\"content\":\"?1\"}}],\"minimum_should_match\":1}")
    Page<QuestionES> findByTitleOrContent(String keywordTitle, String keywordContent, Pageable pageable);
}

6.2 MySQL Mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.guan.friend.domain.question.Question;

public interface QuestionMapper extends BaseMapper<Question> {}

7. Service Implementation

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.guan.common.core.domain.TableDataInfo;
import com.guan.friend.domain.question.Question;
import com.guan.friend.domain.question.dto.QuestionQueryDTO;
import com.guan.friend.domain.question.es.QuestionES;
import com.guan.friend.domain.question.vo.QuestionVO;
import com.guan.friend.elasticsearch.IQuestionRepository;
import com.guan.friend.mapper.question.QuestionMapper;
import com.guan.friend.service.question.IQuestionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class QuestionServiceImpl implements IQuestionService {
    @Autowired
    private IQuestionRepository questionRepository;
    @Resource
    private QuestionMapper questionMapper;

    @Override
    public TableDataInfo list(QuestionQueryDTO dto) {
        long count = questionRepository.count();
        if (count <= 0) { refreshQuestion(); }
        Sort orders = Sort.by(Sort.Direction.DESC, "createTime");
        Pageable pageable = PageRequest.of(dto.getPageNum() - 1, dto.getPageSize(), orders);
        Integer difficulty = dto.getDifficulty();
        String keywords = dto.getKeywords();
        Page
page;
        if (difficulty == null && StrUtil.isEmpty(keywords)) {
            page = questionRepository.findAll(pageable);
        } else if (StrUtil.isEmpty(keywords)) {
            page = questionRepository.findQuestionByDifficulty(difficulty, pageable);
        } else if (difficulty == null) {
            page = questionRepository.findByTitleOrContent(keywords, keywords, pageable);
        } else {
            page = questionRepository.findByTitleOrContentAndDifficulty(keywords, keywords, difficulty, pageable);
        }
        long total = page.getTotalElements();
        if (total <= 0) { return TableDataInfo.empty(); }
        List
voList = BeanUtil.copyToList(page.getContent(), QuestionVO.class);
        return TableDataInfo.success(voList, total);
    }

    private void refreshQuestion() {
        List
list = questionMapper.selectList(new LambdaQueryWrapper<>());
        if (CollectionUtil.isEmpty(list)) { return; }
        List
esList = BeanUtil.copyToList(list, QuestionES.class);
        questionRepository.saveAll(esList);
    }
}

8. Testing

The article includes screenshots demonstrating queries without parameters, with keywords, and with both keywords and difficulty filters, confirming that the ES‑backed search works as expected.

Overall, the guide provides a complete workflow for integrating Elasticsearch with a MySQL‑backed Spring Boot application, covering configuration, entity definition, repository creation, service logic, and verification.