MySQL table design—dictionary table design and interface implementation

Article directory

  • 1. The meaning of the dictionary table
  • 2. Ruoyi’s dictionary table structure
  • 3. Ruoyi enumeration class
  • 4. Code.ruoyi dictionary query interface and cache

1. The meaning of the dictionary table

Suppose there is a staff table:

Name Gender Certificate Type Education Nationality
A male ID card undergraduate China
B Female ID card Undergraduate China
… … … … …

There are 100 million pieces of data in this table. Now the user requires the type of certificate to be changed from “ID card” to “resident ID card”, so that all the data can be updated at once, but it is difficult to maintain. Therefore, consider implementing it in this way :

code number ID card type
001 ID card
002 Temporary residence permit

After adding such an ID card type table, the employee table becomes:

Name Gender Certificate Type Education Nationality
A Male 001 Undergraduate China
B Female 001 Undergraduate China
… … … … …

At this time, to change “ID card” to “resident ID card”, you only need to change a field of a row of data in the ID card type table. But there is a new problem at this time. Nationality and education are also fields that can be enumerated. How about adding an education type table? Obviously not, if there are more than a dozen such fields, then the query will have to perform join operations on dozens of tables. First look at the implementation:


System Code Table

identification category content
666 Country China
333 Country United States United States td>
…… … …
001 ID Identity Card
002 ID Temporary Residence Permit

That is, add a type field to distinguish which field it is.

2. Ruoyi’s dictionary table structure

Based on the above simple background, let’s look at the dictionary table structure provided by the Ruoyi framework: Ruoyi has two dictionary-related tables, a dictionary type table sys_dict_type, and a dictionary data table sys_dict_data, to separate the dictionary definition from the data. First look at the dictionary type table:

Look at the dictionary data table again:


  • The two tables are associated through the dictionary type field dict_type
  • There is a order fielddict_sort in the dictionary data table, which can adjust the corresponding display order of the enumeration
  • The dictionary data table has key and value fields, the page displays the value dict_label, and the code number dict_value is stored in the database
  • Both tables have a status field to control whether the entire data is available

It is also a point to consider that the dictionary data table supports level, such as the following requirement:


At this point, you can add a parent_id to recursively query all hierarchical data in the data table:


For the realization of this requirement, refer to: https://blog.csdn.net/llg___/article/details/129683540

3, ruoyi enumeration class

After the data is defined in the dictionary, write an enumeration class corresponding to dict_type, and define values in the enumeration class, so that it is convenient to call the enumeration value for business processing in the following code. Take ruoyi’s audit status dictionary as an example:


Enum class definition:

import brave.internal.Nullable;

import java.util.HashMap;
import java.util.Map;

public enum AuditStatusEnum {<!-- -->
    BEFORE("before","to be submitted"),
    WAIT("wait", "to be reviewed"),
    NO("no", "The review failed"),
    PASS("pass","approved");

    String code;
    String name;

    AuditStatusEnum(String code, String name) {<!-- -->
        this.code = code;
        this.name = name;
    }

    private static final Map<String, AuditStatusEnum> mappings = new HashMap<>(5);

    static {<!-- -->
        for (AuditStatusEnum statusEnum : values()) {<!-- -->
            mappings.put(statusEnum.code, statusEnum);
        }
    }

    public String getCode(){<!-- -->
        return code;
    }

    public String getName(){<!-- -->
        return name;
    }

    @Nullable
    public static AuditStatusEnum resolve(@Nullable String code) {<!-- -->
        return (code != null ? mappings. get(code) : null);
    }
}
  • The values() method is used to get all the values of the enumeration members and return an array
  • Write a static code block, use the code of the enumeration value as the key, and create a Map collection with the enumeration value itself as the value
  • The last static method resolve() is to query the enumeration value according to the code of the enumeration value
  • Here is one of the opportunities to use Java class loading: access the static method of the class, execute the static code block, initialize the Map to facilitate subsequent queries, wonderful!

About the timing of Java class loading, visit here.

/ After defining the enumeration class corresponding to the above dictionary, write the business logic code:


if (xxDto.getAuditCode().equals(AuditStatusEnum.NO.getCode())){<!-- -->
return new MyException("The audit has not passed, it cannot be operated!");
}

4. Code.ruoyi dictionary query interface and cache

Define the interface, query the key and value of dictionaries under different types, and show it to the front end (of course, the front end can also use html to define the drop-down box options and write a few dead)

/**
 * Query dictionary data information according to dictionary type
 */
@GetMapping(value = "/type/{dictType}")
public AjaxResult dictType(@PathVariable String dictType) {<!-- -->
    return AjaxResult.success(dictTypeService.selectDictDataByType(dictType));
}

service layer interface:

/**
 * Query dictionary data according to dictionary type
 *
 * @param dictType dictionary type
 * @return dictionary data set information
 */
public List<SysDictData> selectDictDataByType(String dictType);

Service interface implementation class: here is to first check in the dictionary cache, if there is, return it directly, if not in the cache, then call the mapper layer to check the database, and write the query result into the cache for backup

/**
 * Query dictionary data according to dictionary type
 *
 * @param dictType dictionary type
 * @return dictionary data set information
 */
@Override
public List<SysDictData> selectDictDataByType(String dictType) {<!-- -->
    List<SysDictData> dictDatas = DictUtils.getDictCache(dictType);
    if (StringUtils.isNotNull(dictDatas)) {<!-- -->
        return dictDatas;
    }
    dictDatas = dictDataMapper. selectDictDataByType(dictType);
    if (StringUtils.isNotNull(dictDatas)) {<!-- -->
        DictUtils.setDictCache(dictType, dictDatas);
        return dictDatas;
    }
    return null;
}

The method definition for obtaining the dictionary cache:

/**
 * Get dictionary cache
 *
 * @param key parameter key
 * @return dictDatas dictionary data list
 */
public static List<SysDictData> getDictCache(String key)
{<!-- -->
    Object cacheObj = SpringUtils.getBean(RedisService.class).getCacheObject(getCacheKey(key));
    if (StringUtils.isNotNull(cacheObj))
    {<!-- -->
        List<SysDictData> dictDatas = StringUtils.cast(cacheObj);
        return dictDatas;
    }
    return null;
}

At this point, the front end calls the interface and passes in the corresponding dictType to get the dictionary field in the drop-down box: