Spring boot uses Mysql's OSQL functionality

Keywords: Programming MySQL Java Session MongoDB


The need to save unstructured data, the first thought of mongodb, but the company does not allow mongodb, can only consider the storage of unstructured data in mysql.


Use mysql to provide xdevapi java for development.


Step 1: Check whether the X plug-in is installed

If you want Mysql to support document storage, you need Mysql to pre-install the X plug-in, log in to mysql, and then run the following command, you can:

SHOW plugins;

You can see the following effects:

Seeing that the mysqlx plug-in is active indicates that mysql now supports document storage, that is, mysql supports nosql.

Step 2: Add dependency Libraries

    implementation "mysql:mysql-connector-java"
    implementation "com.google.protobuf:protobuf-java"

Step 3: Add Spring configuration



Note: The password here, if there are special characters, needs to use the percentile encoding method.


package com.xxx.properties;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

public class Mysqlx {
    private String url;
    private String schema;


package com.xxx.config;

import com.xxx.properties.Mysqlx;
import com.mysql.cj.xdevapi.Schema;
import com.mysql.cj.xdevapi.Session;
import com.mysql.cj.xdevapi.SessionFactory;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;

public class MysqlxConfig {
  @ConfigurationProperties(prefix = "mysqlx.datasource")
  public Mysqlx mysqlx() {
    return new Mysqlx();

  public SessionFactory sFact() {
    return new SessionFactory();

  @DependsOn({"sessionFactory", "mysqlX"})
  public Session sess(SessionFactory sessionFactory, Mysqlx mysqlX) {
    return sessionFactory.getSession(mysqlX.getUrl());

  @DependsOn({"session", "mysqlX"})
  public Schema schema(Session session, Mysqlx mysqlX) {
    return session.createSchema(mysqlX.getSchema(), true);

Step 3: The DAO layer calls xdevapi


package com.xxx.dao;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.xxx.model.FormLog;
import com.xxx.model.exception.HandleException;
import com.mysql.cj.xdevapi.Schema;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

public class FormLogRepositoryImpl implements FormLogRepository {

  private static final String name = "logs";

  @Resource private Schema schema;

  @Resource private ObjectMapper jacksonObjectMapper;

  public FormLog save(FormLog formLog) {
    try {
          .createCollection(name, true)
    } catch (JsonProcessingException e) {
    return formLog;

  public Optional<FormLog> find(String id) {
    return schema.createCollection(name, true).find(String.format("$._id='%s'", id)).execute()
            dbDoc -> {
              try {
                return jacksonObjectMapper.readValue(dbDoc.toString(), FormLog.class);
              } catch (IOException e) {
                throw new HandleException(
                    String.format("Read the log object exception, the content is:\n%s", dbDoc.toFormattedString()));

The main purpose of this paper is to use Schema class in xdevapi to do nosql operation of mysql. It only needs to serialize the object into json manually to save in order to save the object. The use of query uses xdevapi's grammar to query.


If the company does not use mongodb and wants to use documents to store unstructured data, you can consider the traditional mysql, but the mainstream development framework is not yet supported.

Reference resources

Posted by dewed on Wed, 09 Oct 2019 11:11:58 -0700