开发网站过程中,每当修改海报时,通过触发器自动将新的海报缓存到Redis,运用mysql2redis(一个可以在mysql中操作redis的插件)
1、建立存储过程cache_poster_to_redis(),将所需字段拼接成json字符串保存到Redis中。
BEGIN
DECLARE v_json VARCHAR(4000);
DECLARE v_redis_ret INT;
SELECT
CONCAT('[',GROUP_CONCAT(CONCAT(json_object(
'pk', id,
'name', name,
'type', type,
'contentId', content_id,
'poster', poster,
'guid', IF(type=5, (SELECT guid FROM rec_experiences WHERE id=content_id), '')
)) ORDER BY publish_time DESC, show_date_set_time DESC),']')
INTO v_json
FROM
rec_poster
WHERE
status=1 AND type<21 AND type IN (5,12,20) AND poster IS NOT NULL AND poster != '' AND publish_time<=NOW() AND publish_time IS NOT NULL AND publish_time != '' AND (end_date IS NULL OR end_date='' OR DATE_FORMAT(NOW(), '%Y-%m-%d')<=end_date);
SET v_redis_ret=redis_command_v2('SET', 'PCWebHomePoster', v_json);
END
2、rec_poster触发器设置,在每次增删改时候执行。
3、java程序修改
// 此封装方法从Redis中获取值,如果key不存在,则执行getHomePosterFromDB方法,将结果保存到Redis。
public List<ReadingPoster> getHomePoster() {
return JsonUtil.fromJsonAsList(RedisUtil.getValue(redisService.getRedis(), "PCWebHomePoster", () -> JsonUtil.toJson(getHomePosterFromDB())), ReadingPoster.class);
}
public List<ReadingPoster> getHomePosterFromDB() {
return websiteMapper.getHomePoster();
}
4、后台添加海报测试,key:PCWebHomePoster