ABMIS

package com.simba.canalkafkaredis.redis;

import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.concurrent.TimeUnit;

@Component
public class RedisClient {

    @Resource
    private StringRedisTemplate stringRedisTemplate;

    /**
     * 设置redis的key-value
     */
    public void setString(String key, String value) {
        setString(key, value, null);
    }

    /**
     * 设置redis的key-value，带过期时间
     * 时间单位：秒
     */
    public void setString(String key, String value, Long timeOut) {
        stringRedisTemplate.opsForValue().set(key, value);
        if (timeOut != null) {
            stringRedisTemplate.expire(key, timeOut, TimeUnit.SECONDS);
        }
    }

    /**
     * 获取redis中key对应的值
     */
    public String getString(String key) {
        return stringRedisTemplate.opsForValue().get(key);
    }

    /**
     * 删除redis中key对应的值
     */
    public Boolean deleteKey(String key) {
        return stringRedisTemplate.delete(key);
    }

}

package com.simba.canalkafkaredis.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Xdual {

    private Integer ID;

    private String X;

}

package com.simba.canalkafkaredis.canal;

import com.simba.canalkafkaredis.model.Xdual;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CanalBean {

    private List<Xdual> data;

    private String database;

    private Long es;

    private Integer id;

    private Boolean isDdl;

    private MysqlType mysqlType;

    private String old;

    private List<String> pkNames;

    private String sql;

    private SqlType sqlType;

    private String table;

    private Long ts; //(新增)INSERT、(更新)UPDATE、(删除)DELETE、(删除表)ERASE等等

    private String type;

}

package com.simba.canalkafkaredis.canal;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MysqlType {

    private String ID;

    private String X;

}

package com.simba.canalkafkaredis.canal;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class SqlType {

    private Integer ID;

    private Integer X;

}

package com.simba.canalkafkaredis.consumer;

import com.alibaba.fastjson.JSONObject;
import com.simba.canalkafkaredis.canal.CanalBean;
import com.simba.canalkafkaredis.model.Xdual;
import com.simba.canalkafkaredis.redis.RedisClient;
import lombok.extern.slf4j.Slf4j;
import org.apache.kafka.clients.consumer.ConsumerRecord;
import org.springframework.kafka.annotation.KafkaListener;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@Component
public class CanalConsumer {

    private static final Long TIME_OUT = 600L;

    @Resource
    private RedisClient redisClient;

    @KafkaListener(topics = "canaltopic")
    public void receive(ConsumerRecord<?,?> consumer) {

        String value = (String)consumer.value();
        log.info("topic名称:{},key:{},分区位置:{},下标:{},value:{}", consumer.topic(), consumer.key(),consumer.partition(), consumer.offset(), value);
        // 转换为 javabean
        CanalBean canalBean = JSONObject.parseObject(value, CanalBean.class);
        // 是否是 DDL 语句
        Boolean isDdl = canalBean.getIsDdl();
        // 获取语句类型
        String type = canalBean.getType();
        if (!isDdl) {
            List<Xdual> items = canalBean.getData();
            if ("INSERT".equals(type)) {
                //新增语句
                for (Xdual item : items) {
                    Integer id = item.getID();
                    //新增到redis中,过期时间是10分钟
                    redisClient.setString(String.valueOf(id), JSONObject.toJSONString(item), TIME_OUT);
                    log.info("插入---id号:{},值:{}", id, item);
                }
            } else if ("UPDATE".equals(type)) {
                //更新语句
                for (Xdual item : items) {
                    Integer id = item.getID();
                    //更新到redis中,过期时间是10分钟
                    redisClient.setString(String.valueOf(id), JSONObject.toJSONString(item), TIME_OUT);
                    log.info("更新---id号:{},值:{}", id, item);
                }
            } else if ("DELETE".equals(type)){
                //删除语句
                for (Xdual item : items) {
                    Integer id = item.getID();
                    //从redis中删除
                    redisClient.deleteKey(String.valueOf(id));
                    log.info("删除---id号:{},值:{}", id, item);
                }
            } else {
                log.error("Unsupported type!");
            }
        }
    }
}

public Integer setNxAndExp(String key, String val, int expSecond) {
    // 如果键值对创建成功，设置一个过期时间
    if (jedisCluster.setNx(key, val) > 0) {
        if (expSecond > 0) {
            jedisCluster.expire(key, expSecond);
        }
        return 1;
    } else {
        // 如果创建失败，并且已有的键值对没有过期时间，为其设置一个过期时间，一段时间后释放锁
        if (jedisCluster.ttl(key).longValue() == -1) {
            jedisCluster.expire(key, expSecond);
        }
    }
    // 创建成功返回 1，创建失败返回 0，与 setNx 返回值相同
    return 0;
}

package com.ziqian.yudao.aop;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class LoginAspect {

    @Pointcut("execution( * com.ziqian.yudao.controller.*.*(..)) && @annotation(Login)")
    public void cut(){}

    @Before("cut()")
    public void before() {
        System.out.println("已经记录下操作日志@Before 方法执行前");
    }

    @Around("cut()")
    public Object around(ProceedingJoinPoint proceedingJoinPoint) throws Throwable{
        System.out.println("已经记录下操作日志@Around 方法执行前");
        Object object = proceedingJoinPoint.proceed();
        System.out.println("已经记录下操作日志@Around 方法执行后");
        return object;
    }

    @After("cut()")
    public void after() {
        System.out.println("已经记录下操作日志@After 方法执行后");
    }

}

package com.ziqian.yudao.aop;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface Login {
    boolean loginRequired() default true;
}

package com.ziqian.yudao.controller;

import com.ziqian.yudao.aop.Login;
import com.ziqian.yudao.service.UserService;
import com.ziqian.yudao.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    UserService userService;

    /**
     * 查询用户列表
     *
     * @return 用户列表
     */
    @Login
    @GetMapping("/list") // URL 修改成 /list
    public List<UserVO> list() {
        // 查询列表
        UserVO user1 = new UserVO(); user1.setId(1L); user1.setUserName("yudaoyuanma");
        UserVO user2 = new UserVO(); user2.setId(2L); user2.setUserName("woshiyutou");
        UserVO user3 = new UserVO(); user3.setId(3L); user3.setUserName("chifanshuijiao");
        List<UserVO> result = new ArrayList<>();
        result.add(user1);
        result.add(user2);
        result.add(user3);
        // 返回列表
        return result;
    }

}

import org.apache.poi.xssf.usermodel.*;

@CrossOrigin
@RequestMapping(value = {"/parseExcel"}, method = RequestMethod.POST, produces = {"application/json;charset=UTF-8"})
@ResponseBody
public Result<Boolean> parseExcel(@RequestParam("file") MultipartFile file) {
        // @RequestParam("file")MultipartFile file 用来接受前端传过来的 Excel 文件
        // 创建 Workbook 对象，读取整个文档
        InputStream inputStream = file.getInputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        // 读取页脚 sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        // 循环取每行的数据
        // rowIndex 为 1 是为了跳过标题行
        for (int rowIndex = 1; rowIndex < xssfSheet.getPhysicalNumberOfRows(); rowIndex++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            if (xssfRow == null) {
                continue;
            }
            // 获取单元格数据
            Long id = ExcelUtils.getLong(xssfRow.getCell(0));
            String str = ExcelUtils.getString(xssfRow.getCell(1)));
            Integer num = ExcelUtils.getInteger(xssfRow.getCell(2));
            Boolean isFalse = ExcelUtils.getBoolean(xssfRow.getCell(3));
        }
}

import org.apache.poi.xssf.usermodel.*;

@ApiOperation(value = "exportExcel")
@RequestMapping(value = {"/exportExcel"}, method = RequestMethod.GET)
@ResponseBody
public Result<Boolean> exportExcel(HttpServletResponse response) {
    // 设置要导出的文件的名字
    String fileName = "data"  + new Date() + ".xlsx";
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("信息表");
        // headers表示excel表中第一行的表头 在excel表中添加表头
        String[] headers = { "id", "名称", "类型"};
        XSSFRow head = sheet.createRow(0);
        for(int i = 0; i < headers.length; i++){
            XSSFCell cell = head.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 新增数据行，并且设置单元格数据
        XSSFRow row =sheet.createRow(1);
        row.createCell(0).setCellValue(id);
        row.createCell(1).setCellValue(name);
        row.createCell(2).setCellValue(type);
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
}

import org.apache.poi.xssf.usermodel.*;

// 把单元格值转为 String
public static String getString(XSSFCell xssfCell) {
    if (xssfCell == null) {
        return "";
    }
    if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
        return String.valueOf(xssfCell.getNumericCellValue());
    } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
        return String.valueOf(xssfCell.getBooleanCellValue());
    } else {
        return xssfCell.getStringCellValue();
    }
}

// 切割是为了去除小数点
public static Long getLong(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Long.valueOf(s.substring(0, s.length() - 2));
}

public static Integer getInteger(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Integer.valueOf(s.substring(0, s.length() - 2));
}

public static Boolean getBoolean(XSSFCell xssfCell) {
    String s = getString(xssfCell);
    return Boolean.valueOf(s.substring(0, s.length() - 2));
}

/**
 * 缓存
 */
private LoadingCache<String, Map<String, String> cache = CacheBuilder
        .newBuilder()
        .refreshAfterWrite(EXPIRE_TIME_MINUTES, TimeUnit.MINUTES)
        .build(new CacheLoader<String, Map<String, String>>() {
            @Override
            public Map<String, String> load(String key) {
                return getFromDb();
            }
        });