技术栈:lua、Canal、OpenResty
功能:实现广告缓存
功能架构图
lua脚本实现读数据库内容并存入redis中
ngx.header.content_type="application/json;charset=utf8"
local cjson = require("cjson")
local mysql = require("resty.mysql")
local uri_args = ngx.req.get_uri_args()
local id = uri_args["id"]
local db = mysql:new()
db:set_timeout(1000)
local props = {
host = "192.168.211.132",
port = 3306,
database = "changgou_content",
user = "root",
password = "123456"
}
local res = db:connect(props)
local select_sql = "select url,pic from tb_content where status ='1' and category_id="..id.." order by sort_order"
res = db:query(select_sql)
db:close()
local redis = require("resty.redis")
local red = redis:new()
red:set_timeout(2000)
local ip ="192.168.211.132"
local port = 6379
red:connect(ip,port)
red:set("content_"..id,cjson.encode(res))
red:close()
ngx.say("{flag:true}")
修改nginx中的配置文件信息
server {
listen 80;
server_name localhost;
location /update_content {
content_by_lua_file /root/lua/update_content.lua;
}
}
读取广告信息
ngx.header.content_type="application/json;charset=utf8"
local uri_args = ngx.req.get_uri_args();
local id = uri_args["id"];
--获取本地缓存
local cache_ngx = ngx.shared.dis_cache;
--根据ID 获取本地缓存数据
local contentCache = cache_ngx:get('content_cache_'..id);
if contentCache == "" or contentCache == nil then
local redis = require("resty.redis");
local red = redis:new()
red:set_timeout(2000)
red:connect("192.168.211.132", 6379)
local rescontent=red:get("content_"..id);
if ngx.null == rescontent then
local cjson = require("cjson");
local mysql = require("resty.mysql");
local db = mysql:new();
db:set_timeout(2000)
local props = {
host = "192.168.211.132",
port = 3306,
database = "changgou_content",
user = "root",
password = "123456"
}
local res = db:connect(props);
local select_sql = "select url,pic from tb_content where status ='1' and category_id="..id.." order by sort_order";
res = db:query(select_sql);
local responsejson = cjson.encode(res);
red:set("content_"..id,responsejson);
ngx.say(responsejson);
db:close()
else
cache_ngx:set('content_cache_'..id, rescontent, 10*60);
ngx.say(rescontent)
end
red:close()
else
ngx.say(contentCache)
end
canal对数据库进行监听并同步进redis
package com.changgou.canal.listener;
import com.alibaba.otter.canal.protocol.CanalEntry;
import com.xpand.starter.canal.annotation.*;
@CanalEventListener
public class CanalDataEventListener {
/***
* 增加数据监听
* @param eventType
* @param rowData
*/
@InsertListenPoint
public void onEventInsert(CanalEntry.EventType eventType, CanalEntry.RowData rowData) {
rowData.getAfterColumnsList().forEach((c) -> System.out.println("By--Annotation: " + c.getName() + " :: " + c.getValue()));
}
/***
* 修改数据监听
* @param rowData
*/
@UpdateListenPoint
public void onEventUpdate(CanalEntry.RowData rowData) {
System.out.println("UpdateListenPoint");
rowData.getAfterColumnsList().forEach((c) -> System.out.println("By--Annotation: " + c.getName() + " :: " + c.getValue()));
}
/***
* 删除数据监听
* @param eventType
*/
@DeleteListenPoint
public void onEventDelete(CanalEntry.EventType eventType) {
System.out.println("DeleteListenPoint");
}
/***
* 自定义数据修改监听
* @param eventType
* @param rowData
*/
@ListenPoint(destination = "example", schema = "changgou_content", table = {"tb_content_category", "tb_content"}, eventType = CanalEntry.EventType.UPDATE)
public void onEventCustomUpdate(CanalEntry.EventType eventType, CanalEntry.RowData rowData) {
System.err.println("DeleteListenPoint");
rowData.getAfterColumnsList().forEach((c) -> System.out.println("By--Annotation: " + c.getName() + " :: " + c.getValue()));
}
}
@CanalEventListener
public class CanalDataEventListener {
@Autowired
private ContentFeign contentFeign;
//字符串
@Autowired
private StringRedisTemplate stringRedisTemplate;
//自定义数据库的 操作来监听
//destination = "example"
@ListenPoint(destination = "example",
schema = "changgou_content",
table = {"tb_content", "tb_content_category"},
eventType = {
CanalEntry.EventType.UPDATE,
CanalEntry.EventType.DELETE,
CanalEntry.EventType.INSERT})
public void onEventCustomUpdate(CanalEntry.EventType eventType, CanalEntry.RowData rowData) {
//1.获取列名 为category_id的值
String categoryId = getColumnValue(eventType, rowData);
//2.调用feign 获取该分类下的所有的广告集合
Result<List<Content>> categoryresut = contentFeign.findByCategory(Long.valueOf(categoryId));
List<Content> data = categoryresut.getData();
//3.使用redisTemplate存储到redis中
stringRedisTemplate.boundValueOps("content_" + categoryId).set(JSON.toJSONString(data));
}
private String getColumnValue(CanalEntry.EventType eventType, CanalEntry.RowData rowData) {
String categoryId = "";
//判断 如果是删除 则获取beforlist
if (eventType == CanalEntry.EventType.DELETE) {
for (CanalEntry.Column column : rowData.getBeforeColumnsList()) {
if (column.getName().equalsIgnoreCase("category_id")) {
categoryId = column.getValue();
return categoryId;
}
}
} else {
//判断 如果是添加 或者是更新 获取afterlist
for (CanalEntry.Column column : rowData.getAfterColumnsList()) {
if (column.getName().equalsIgnoreCase("category_id")) {
categoryId = column.getValue();
return categoryId;
}
}
}
return categoryId;
}
}