SpringBoot+ShardingSphere completely solves the problem of encryption and decryption of database fields in production environment


   companies in the Internet industry must encrypt the sensitive fields of the database. There are many schemes. The most direct one is to write an encryption and decryption tool class and then process it manually in each business logic. This method is obviously unrealistic in slightly large-scale projects, which is not only heavy workload, but also difficult to maintain in the later stage.

   at present, mybatis plus has provided a very good encryption and decryption scheme, and Jushi has tried it with good results. However, many Internet companies do not necessarily introduce mybatis plus as a data layer tool, but prefer to use mybatis, and many even use SpringDataJPA, so it is not necessary to introduce mybatis plus specifically for encryption and decryption.

                 8195? The answer is yes. Shardingsphere provides a scheme. Why choose it? Because Internet companies will probably consider database and table splitting. At present, the best database and table splitting scheme is actually shardingsphere. In that case, directly using its database encryption and decryption scheme does not need to introduce additional third-party tools.


1. Case preparation

The technical system is as follows. The introduction of MybatisPlus is to facilitate the case to be formed faster. ShardingSphere does not recommend using 5 X version, because the version difference is large, it has always been the reason why people criticize it. Even the small version has many differences, 4 The X version is more informative.


2. Introduce dependency
    <!-- spring web -->
    <!-- sharding-jdbc -->
    <!-- mysql -->
    <!-- mybatis-plus -->
    <!-- Code generator  mybatisPlus Built in generator -->
    <!-- freemarker Template generator introduces code generator -->
    <!-- swagger because mybatisPlus The code generator comes with it swagger Notes for -->
    <!-- Load configuration file after startup -->
    <!-- lombok Simplify entity class management tools-->
    <!-- fastjson analysis json If you want to use it, you can also change it to what you like -->
    <!-- test -->

3. yml configuration
  port: 8888

# data source
  # shardingsphere configuration
        show: false
            column: true # Query whether to use ciphertext column
      name: master
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/encrypt_demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456
        initial-size: 20
        max-active: 200
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
              multi-statement-allow: true
    # Encryption configuration
          type: mySharding # Declare the type of encryption processor and customize it.
            aes.key.value: fly13579@# # The encryption processor will use 10 numbers and English letters to create the key, which can be customized.
      # Which fields in which table need to be encrypted, and which encryption processor is used for each field, here is my_encryptor is the name of the processor configured above.
              cipherColumn: id_card
              encryptor: my_encryptor
              cipherColumn: name
              encryptor: my_encryptor

4. Custom encryption and decryption processor

ShardingSphere has its own encryption and decryption processor, which can be used directly. In the production environment, it still prefers to customize the processor, because it is safer and not easy to be brutally cracked.

1) . add SPI pointing

Create a new META-INF/services directory under the resources directory, and write a file to point to the custom processor.

Document name: org.org apache. shardingsphere. encrypt. strategy. spi. Encryptor

PS: the names of versions 4.1.1 and 4.0.0 are different because many class names and package names have changed.

2) Write custom encryption and decryption processor
package com.example.encrypt.config;

import com.google.common.base.Preconditions;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.codec.binary.StringUtils;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.shardingsphere.encrypt.strategy.impl.AESEncryptor;
import org.apache.shardingsphere.encrypt.strategy.spi.Encryptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;

import javax.crypto.Cipher;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.spec.SecretKeySpec;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.Properties;

 * <p>
 *  Shardingsphere Encryption processor
 * </p>
 * @author Fu Long court, the official account: Java sharing inn.
 * @since 2022-02-20
public class MyShardingEncryptor implements Encryptor {

    private final Logger log = LoggerFactory.getLogger(MyShardingEncryptor.class);

    // AES KEY
    private static final String AES_KEY = "aes.key.value";

    private Properties properties = new Properties();

    public MyShardingEncryptor(){


    public void init() {


    public String encrypt(Object plaintext) {
        try {
            byte[] result = this.getCipher(1).doFinal(StringUtils.getBytesUtf8(String.valueOf(plaintext)));
            log.debug("[MyShardingEncryptor]>>>> encryption: {}", Base64.encodeBase64String(result));
            return Base64.encodeBase64String(result);
        } catch (Exception ex) {
            log.error("[MyShardingEncryptor]>>>> Encryption exception:", ex);
        return null;


    public Object decrypt(String ciphertext) {
        try {
            if (null == ciphertext) {
                return null;
            } else {
                byte[] result = this.getCipher(2).doFinal(Base64.decodeBase64(ciphertext));
                log.debug("[MyShardingEncryptor]>>>> decrypt: {}", new String(result));
                return new String(result);
        } catch (Exception ex) {
            log.error("[MyShardingEncryptor]>>>> Decryption exception:", ex);
        return null;

    public String getType() {
        return "mySharding"; // Consistent with yml configuration

    public Properties getProperties() {
        return this.properties;

    public void setProperties(Properties properties) {
        this.properties = properties;

    * Encryption and decryption algorithm
    * @param decryptMode 1-Encryption, 2- decryption, and other types can click in to see the source code.
    private Cipher getCipher(int decryptMode) throws NoSuchPaddingException, NoSuchAlgorithmException, InvalidKeyException {
        Preconditions.checkArgument(this.properties.containsKey("aes.key.value"), "No available secret key for `%s`.",
        Cipher result = Cipher.getInstance("AES");
        result.init(decryptMode, new SecretKeySpec(this.createSecretKey(), "AES"));
        return result;

    * Create keys and define rules according to your needs.
    * -- PS: The production environment specification requires that the key related log cannot be printed to avoid accidental disclosure.
   private byte[] createSecretKey() {
      // Original key configured in yml
        String oldKey = this.properties.get("aes.key.value").toString();
        Preconditions.checkArgument(null != oldKey, String.format("%s can not be null.", "aes.key.value"));
         * Encrypt the original key and the customized salt again to generate a new key
         * Note that the final key of AES encryption and decryption must be 16 bits, otherwise AES will report an error,
         * And application AES configured in YML key. Value is a 10 bit character combination, so substring(0,5) is used here. Otherwise, if 16 bits are not returned, AES exception will be thrown. You can test it yourself.
      String secretKey = DigestUtils.sha1Hex(oldKey + AES_KEY).toUpperCase().substring(0, 5) + "!" + oldKey;
      // The key print must be deleted before going online to avoid security accidents caused by disclosure.
      log.debug("[MyShardingEncryptor]>>>> secret key: {}", secretKey);
        return secretKey.getBytes();


5. Write test interface
package com.example.encrypt.controller;

import com.alibaba.fastjson.JSONObject;
import com.example.encrypt.entity.Order;
import com.example.encrypt.enums.ResponseCodeEnum;
import com.example.encrypt.service.IOrderService;
import com.example.encrypt.util.ResultEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

 * <p>
 *  Front end controller
 * </p>
 * @author Fu Long court, the official account: Java sharing inn.
 * @since 2022-02-21
public class OrderController {

   private final IOrderService orderService;

   public OrderController(IOrderService orderService) {
      this.orderService = orderService;

    * Query order
   public ResponseEntity<List<Order>> list() {
      return ResponseEntity.ok().body(orderService.list());

    * Insert order
   public ResponseEntity<List<Order>> save(@RequestBody Order order) {
      // This is just a simple demonstration. Remember not to directly transfer the formal code to the entity object, but to transfer the VO object for conversion, and do parameter verification.
      log.debug("[Insert order]>>>> order={}", JSONObject.toJSONString(order));
      order.setCreatedAt(new Date());
      order.setUpdatedAt(new Date());
      boolean ret = orderService.save(order);
      return ret ? ResponseEntity.ok().body(orderService.list())
            : ResponseEntity.badRequest().body(new ArrayList<>());

6. Effect

Insert order

Our yml is configured to encrypt the name and ID number. Look at the database order record and find that it has been automatically encrypted.

Try to query the order again to see if the encrypted field of the database will be decrypted and the result will be returned. It is found that there is no problem with decryption.

7. Usage of key in database

Here is a special explanation of how the key can directly encrypt and decrypt the field value in the database through SQL, because many companies will use fortress machine or cloud desktop to access the production environment database. At this time, when troubleshooting online problems, we often need to know what the encrypted field is.

1) I. get the key

PS: remember that this key can be kept once before going online. The log of printing the key must be deleted to avoid accidents caused by the disclosure of the key in the production environment. Generally, regular companies will have requirements.
The key printing place is in the custom encryption and decryption processor, which can be debug ged and printed.

2) Used in database

The statement is as follows. Save it and copy it when you use it in the future.

# encryption 
SELECT to_base64(AES_ENCRYPT('Value to encrypt','secret key')) 
# decrypt 
SELECT AES_DECRYPT(FROM_BASE64('Value to decrypt'),'secret key') 
# Chinese decryption, anti aliasing. 
select CAST(AES_DECRYPT(FROM_BASE64('Chinese value to decrypt'),'secret key') as char)


   ShardingSphere's encryption and decryption steps are simple, but people who are not familiar with or have not used this tool will step on many holes, including version differences, unresolved defects, etc., but its advantages far outweigh its disadvantages, so it will be accepted by many companies and a required course for learning sub database and sub table.

   the lay provides you with the source code that can be run directly and the small notes of personal stepping on the pit. If you need it, you can download it and run for experiments.

  the source code link will be shared in the comments~

My original article is purely hand-made. If you feel that there is a drop of help, please extend your slender jade hand and point out your praise and collection~~~

Keywords: Java Database Spring Boot sharding

Added by cfemocha on Mon, 21 Feb 2022 10:29:44 +0200