Spring boot uses Mysql's OSQL functionality

Keywords: Programming MySQL Java Session MongoDB

problem

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.

thinking

Use mysql to provide xdevapi java for development.

Solve

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

application.properties

mysqlx.datasource.url=mysqlx://localhost:33060/xxxx_db?user=xxxx&password=xxxxx
mysqlx.datasource.schema=xxxx_db_sch

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

Mysqlx.java

package com.xxx.properties;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Mysqlx {
    private String url;
    private String schema;
}

MysqlxConfig.java

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;

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

  @Bean("sessionFactory")
  public SessionFactory sFact() {
    return new SessionFactory();
  }

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

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

Step 3: The DAO layer calls xdevapi

FormLogRepositoryImpl.java

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;

@Repository
public class FormLogRepositoryImpl implements FormLogRepository {

  private static final String name = "logs";

  @Resource private Schema schema;

  @Resource private ObjectMapper jacksonObjectMapper;

  @Override
  public FormLog save(FormLog formLog) {
    try {
      schema
          .createCollection(name, true)
          .add(jacksonObjectMapper.writeValueAsString(formLog))
          .executeAsync();
    } catch (JsonProcessingException e) {
      e.printStackTrace();
    }
    return formLog;
  }

  @Override
  public Optional<FormLog> find(String id) {
    return schema.createCollection(name, true).find(String.format("$._id='%s'", id)).execute()
        .fetchAll().stream()
        .map(
            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()));
              }
            })
        .findFirst();
  }
}

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.

summary

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

Programmer Group