一、实现方式
①将配置表读取存入redis做缓存
②创建自定义注解定义方法返回参数需要在数据字典中翻译的表明和列名
③使用aop切面对查询出的结果进行数据字典注入
二、测试
本测试使用springboot+mysql+redis,过程如下:
1、准备工作
mysql创建测试表user,user_data,两张表
CREATE TABLE `user` (
`id`int(11) NOT NULLAUTO_INCREMENT,
`name`varchar(255) DEFAULT NULL,PRIMARY KEY(`id`)
);CREATE TABLE`user_data` (
`id`int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`action`varchar(10) DEFAULT NULL,
`time`datetime DEFAULT NULL,PRIMARY KEY(`id`)
)
表内插入数据
INSERT INTO`user`(`id`, `name`) VALUES (1, '小顾');INSERT INTO`user`(`id`, `name`) VALUES (2, '小陈');INSERT INTO`user`(`id`, `name`) VALUES (3, '小王');INSERT INTO`user_data`(`id`, `user_id`, `action`, `time`) VALUES (1, 1, '吃', '2020-07-30 06:10:09');INSERT INTO`user_data`(`id`, `user_id`, `action`, `time`) VALUES (2, 1, '穿', '2020-08-01 11:10:45');INSERT INTO`user_data`(`id`, `user_id`, `action`, `time`) VALUES (3, 1, '玩', '2020-08-03 11:11:10');INSERT INTO`user_data`(`id`, `user_id`, `action`, `time`) VALUES (4, 2, '看书', '2020-08-02 11:11:37');INSERT INTO`user_data`(`id`, `user_id`, `action`, `time`) VALUES (5, 2, '跑步', '2020-08-03 11:11:55');
安装redis,在springboot项目引入相关依赖包并配置redis(过程省略)
RedisUtil工具类
importcom.alibaba.fastjson.JSONArray;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.data.redis.core.StringRedisTemplate;importorg.springframework.stereotype.Component;importjava.util.List;importjava.util.Map;importjava.util.concurrent.TimeUnit;
@Componentpublic classRedisUtil {
@Autowired
StringRedisTemplate stringRedisTemplate;public voidadd(String key,String value){
stringRedisTemplate.opsForValue().set(key,value);
}public voidadd(String key, String value,Long time){if (time>0){
stringRedisTemplate.opsForValue().set(key,value,time, TimeUnit.SECONDS);
}else{
stringRedisTemplate.opsForValue().set(key,value);
}
}public void add(String key, List> o, inttime){
stringRedisTemplate.opsForValue().set(key, JSONArray.toJSONString(o),time,TimeUnit.SECONDS);
}publicString get(String key){return key==null?"":stringRedisTemplate.opsForValue().get(key);
}
}
2、测试接口
添加返回参数类Data.java
importjava.io.Serializable;importjava.util.List;importjava.util.Map;public class Data implementsSerializable {private static final long serialVersionUID = 4030739808145679471L;private List>result;public List>getResult() {returnresult;
}public void setResult(List>result) {this.result =result;
}
}
添加测试接口显示user_data内容
@ApiOperation(value = "测试", notes = "测试")
@RequestMapping(value= "/example", method =RequestMethod.GET)publicData example() {
LOG.info("example");
List> res = mysql.nQueryForMapList("select user_id,action,date_format(time,'%Y-%m-%d %T') as time from user_data");
Data data= newData();
data.setResult(res);returndata;
}
调用example接口,返回参数如下:
{
"result": [
{
"user_id": 1,
"action": "吃",
"time": "2020-07-30 06:10:09"
},
{
"user_id": 1,
"action": "穿",
"time": "2020-08-01 11:10:45"
},
{
"user_id": 1,
"action": "玩",
"time": "2020-08-03 11:11:10"
},
{
"user_id": 2,
"action": "看书",
"time": "2020-08-02 11:11:37"
},
{
"user_id": 2,
"action": "跑步",
"time": "2020-08-03 11:11:55"
}
]
}
3、redis缓存数据
将user表的数据缓存到redis中
import com.github.drinkjava2.jsqlbox.SqlBoxContext;
import com.jsepc.collectmonitor.util.RedisUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;
@Component
public class AppRunner implements ApplicationRunner {
@Qualifier("mysql")
@Autowired
public SqlBoxContext mysql;
@Autowired
RedisUtil redisUtil;
@Override
public void run(ApplicationArguments applicationArguments) throws Exception {
//读取mysql的数据
String sql = "select id,name from user";
List> taskList = mysql.nQueryForMapList(sql);
//拼接储存到redis的数据
for (Mapmap : taskList){
redisUtil.add("user"+"&&"+map.get("id"),map.get("name").toString());
}
}
}
执行后使用Redis Desktop Manager工具查看到Redis中已经新增了三条数据,key为表名+id拼接,value为id的含义。
4、添加AOP切面
添加自定义注解类OperatorRedisData.java,其中参数tablename对应要注入的表名,key对应返回参数中需要解析的字段名。在本测试中,tablename=user,key=user_id。
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documentedpublic @interfaceOperatorRedisData {
String tableName();
String key();
}
添加字典AOP类
importcom.jsepc.collectmonitor.model.Data;importcom.jsepc.collectmonitor.util.RedisUtil;importorg.aspectj.lang.ProceedingJoinPoint;importorg.aspectj.lang.annotation.Around;importorg.aspectj.lang.annotation.Aspect;importorg.aspectj.lang.annotation.Pointcut;importorg.aspectj.lang.reflect.MethodSignature;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Component;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;
@Aspect
@Componentpublic classOperatorRedisDataAspect {private final Logger log = LoggerFactory.getLogger(com.jsepc.collectmonitor.aop.OperatorRedisDataAspect.class);
@Autowired
RedisUtil redisUtil;
@Pointcut("@annotation(com.jsepc.collectmonitor.aop.OperatorRedisData)")public voidoperatorRedis(){
}
@Around("operatorRedis()")public Object doAround(ProceedingJoinPoint pjp) throwsThrowable {long time1 =System.currentTimeMillis();
Object result=pjp.proceed();long time2 =System.currentTimeMillis();//通过自定义注释获取tableName和key
MethodSignature signature =(MethodSignature)pjp.getSignature();
OperatorRedisData d=signature.getMethod().getAnnotation(OperatorRedisData.class);
log.info("获取JSON数据 耗时:" + (time2 - time1) + "ms");long start =System.currentTimeMillis();this.parseDictText(result,d.tableName(),d.key());long end =System.currentTimeMillis();
log.info("解析注入JSON数据 耗时" + (end - start) + "ms");returnresult;
}//对返回值解析并注入数据字典
private voidparseDictText(Object result,String table,String key) {if (result instanceofData) {
List> items = new ArrayList<>();for (Mapitem : ((Data) result).getResult()) {
String k=item.get(key).toString();
String res= redisUtil.get(table+"&&"+k);
item.put(key+"_dictText",res);
items.add(item);
}
((Data) result).setResult(items);
}
}
}
5、应用注解后测试接口
对步骤2中的测试接口添加步骤4中的自定义注解@OperatorRedisData,如下:
@ApiOperation(value = "测试", notes = "测试")
@RequestMapping(value= "/example", method =RequestMethod.GET)
@OperatorRedisData(tableName="user",key="user_id")publicData example() {
LOG.info("example");
List> res = mysql.nQueryForMapList("select user_id,action,date_format(time,'%Y-%m-%d %T') as time from user_data");
Data data= newData();
data.setResult(res);returndata;
}
添加后运行程序再次测试,返回结果如下:
{"result": [
{"user_id": 1,"action": "吃","time": "2020-07-30 06:10:09","user_id_dictText": "小顾"},
{"user_id": 1,"action": "穿","time": "2020-08-01 11:10:45","user_id_dictText": "小顾"},
{"user_id": 1,"action": "玩","time": "2020-08-03 11:11:10","user_id_dictText": "小顾"},
{"user_id": 2,"action": "看书","time": "2020-08-02 11:11:37","user_id_dictText": "小陈"},
{"user_id": 2,"action": "跑步","time": "2020-08-03 11:11:55","user_id_dictText": "小陈"}
]
}
AOP对返回参数注入了user_id_dictText字段。
三、总结
通过Redis将一些使用率比较高的台帐、配置类的表缓存,进而通过AOP在返回参数中注入。这种方式可以减少数据库的压力,提高读取效率。