Because the front-end needs user information userInfo, and the back-end modules, such as reports, also need user information, the frequency of query is very high, involving more linked tables.

However, the actual system users are small and the data is basically unchanged. For the convenience of each module, it is decided to adopt caching.

Choose to put it in the Redis database. When accessing user data, the modules are decoupled.

1. function

Some of the fields for userInfo may come from functions.

Because of the need, when I get a user, I also need to know which top-level organization he belongs to.

The organization chart is as follows:

First, create a function to get the rootId according to the orgId

CREATE FUNCTION `queryRoot`(org varchar(32))
 RETURNS varchar(32)
BEGIN

# Define child node, parent node
DECLARE nodeId VARCHAR(32);
DECLARE parentId VARCHAR(32);
DECLARE rootId VARCHAR(32);

# assignment
SET nodeId = org;
SET parentId = '$';

WHILE parentId IS NOT NULL DO
	SELECT parentOrgId INTO parentId FROM T_ORG WHERE ORG_ID = nodeId;
	SET rootId = nodeId;
	SET nodeId = parentId;
END WHILE;

RETURN rootId;

END;

2. view

SELECT
	`users`.`USER_ID` AS `userId`,
	`users`.`ORG_ID` AS `orgId`,
	(
		SELECT
			`queryRootByOrgId` (`users`.`ORG_ID`)
	) AS `rootOrgId`,
	`orgs`.`ORG_NAME` AS `orgName`,
	`users`.`USER_NAME` AS `userName`
FROM
	(
		`T_USERS` `users`
		JOIN `T_ORG` `orgs` ON (
			(
				`users`.`ORG_ID` = `orgs`.`ORG_ID`
			)
		)
	)

3. Regularly update to Redis

Reflection is used to store attributes and values in Hash.

@EnableScheduling
@Component
public class RedisTask {

    @Autowired
    private StringRedisTemplate redis;

    @Autowired
    private UserInfoRepository userInfoRepository;

    static Field[] fields;
    static {
        fields = UserInfo.class.getDeclaredFields();
        for (Field field : fields)
            field.setAccessible(true);
    }

    /**
     * Query the database at a fixed time of 3 minutes, and then update it to redis
     */
    @Scheduled(fixedRate = 3*60*1000)
    private void userInfo() {

        List<UserInfo> list = userInfoRepository.findAll();

        list.forEach(user -> {
            Map<String, String> map = new HashMap();
            String hashKey, hashValue, hash = user.getUserId();
            for (Field field : fields) {
                try {
                    hashKey = field.getName();
                    if (field.get(user) != null)
                        hashValue = field.get(user).toString();
                    else
                        hashValue = "";
                    map.put(hashKey, hashValue);
                } catch (Exception e) {
                    System.out.println("Abnormal reflex");
                    e.printStackTrace();
                }
            }
            redis.opsForHash().putAll(hash, map);
        });

    }

}