The project needs to log interface invocations, and since the number is not large at present, it is decided to create a table dynamically based on years (invoke_interface_LOG_2021). Implement (dynamic SQL) with MyBatis.

Dao.xml

<mapper namespace="com.xxx.xxx.dao.manager.system.InvokeInterfaceLogDao">
    <! Count the number of existing tables by table name -->
    <select id="countTables" parameterType="string" resultType="int" databaseId="mysql">
        SELECT count(1)
        FROM information_schema.TABLES
        WHERE LCASE(table_name) = #{tableName}
    </select>

    <! SQL > create table SQL > create table SQL
    <update id="createTable" parameterType="string" databaseId="mysql">CREATE TABLE ${tableName} (' id 'bigint(20) NOT NULL AUTO_INCREMENT COMMENT' primary key ', 'client_id' varchar(50) DEFAULT NULL COMMENT 'client_id' varchar(50) DEFAULT NULL COMMENT 'client_id' varchar(50) DEFAULT NULL COMMENT 'client id ',' interface_type 'tinyint(4) unsigned DEFAULT NULL COMMENT' interface type ', 'interface_name' varchar(50) DEFAULT NULL COMMENT 'id ',' invoke_time 'datetime DEFAULT NULL COMMENT' id ', 'is_success' tinyint(1) unsigned DEFAULT '0' COMMENT 'is_success' 'fail_type' tinyint(4) unsigned DEFAULT NULL COMMENT 'failure type ',' fail_msg 'vARCHar (255) DEFAULT NULL COMMENT' failure type ', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET= UTf8MB4 COMMENT='</update>

    <insert id="insert" parameterType="com.xxx.xxx.xxx.manager.system.InvokeInterfaceLogDO" databaseId="mysql">
        INSERT INTO ${tableName} (
            client_id,
            interface_type,
            interface_name,
            invoke_time,
            is_success,
            fail_type,
            fail_msg
        )
        VALUES
            (
                #{invokeInterfaceLog.clientId},
                #{invokeInterfaceLog.interfaceType},
                #{invokeInterfaceLog.interfaceName},
                #{invokeInterfaceLog.invokeTime},
                #{invokeInterfaceLog.success},
                #{invokeInterfaceLog.failType},
                #{invokeInterfaceLog.failMsg}
            )
    </insert>
</mapper>
Copy the code

Dao.java

/** * The interface calls the log data access layer interface **@author: yemingxiang
 */
public interface InvokeInterfaceLogDao {

    /** * Count the number of existing tables **@paramTableName table name *@returnNumber of existing tables */
    int countTables(String tableName);

    /** create table **@paramTableName table name * /
    void createTable(@Param("tableName") String tableName);

    /**
     * 新增
     *
     * @paramTableName table name *@paramInvokeInterfaceLogDO invokes the interface log data object *@returnNumber of new successful records */
    int insert(@Param("tableName") String tableName,
        @Param("invokeInterfaceLog") InvokeInterfaceLogDO invokeInterfaceLogDO);
}
Copy the code

The business layer

The implementation is as follows:

/** * Call interface log business logic layer implementation **@author yemingxiang
 */
@Service
public class InvokeInterfaceLogServiceImpl implements InvokeInterfaceLogService {

    private static final Logger logger = LoggerFactory.getLogger(InvokeInterfaceLogServiceImpl.class);

    @Autowired
    private InvokeInterfaceLogDao invokeInterfaceLogDao;
    
    @Autowired
    private RedisService redisService;

    @Autowired
    private RedissonClient redissonClient;

    @Override
    public boolean save(InvokeInterfaceLogDO invokeInterfaceLogDO) {
        int currentYear = LocalDate.now().getYear();
        String key = String.format("system:invokeInterfaceLog:%s:string", currentYear);
        logger.info(Interface call log table Redis Key: + key);
        String tableName = redisService.getString(key);
        logger.info("Interface call log table name:" + tableName);
        if(tableName ! =null) {
            return invokeInterfaceLogDao.insert(tableName, invokeInterfaceLogDO) == 1;
        }

        String newTableName = String.format("invoke_interface_log_%s", currentYear);
        String lockKey = newTableName;
        try {
            RLock redissonLock = redissonClient.getLock(lockKey);
            redissonLock.lock();
            if(! Thread.currentThread().isInterrupted()) { log.info("get redis lock, key: {}", lockKey);
                try {
                    // The second layer of judgment is for prevention
                    tableName = redisService.getString(key);
                    logger.info(Get the name of the interface call log table again: + tableName);
                    if(tableName ! =null) {
                        return invokeInterfaceLogDao.insert(tableName, invokeInterfaceLogDO) == 1;
                    }

                    int count = invokeInterfaceLogDao.countTables(newTableName);
                    if (count == 0) {
                        logger.info("Create interface call log table and save table name to Redis");
                        invokeInterfaceLogDao.createTable(newTableName);
                        redisService.set(key, newTableName);
                    }
                    return invokeInterfaceLogDao.insert(newTableName, invokeInterfaceLogDO) == 1;
                } finally {
                    redissonLock.unlock();
                    log.info("release redis lock, key: {}", lockKey); }}else {
                log.error("{} does not get lock.", Thread.currentThread().getName());
                throw newThirdPartyServiceException(ThirdPartyServiceErrorCodeEnum.CACHE_SERVICE_TIMEOUT); }}catch (Exception e) {
            if (e instanceof ThirdPartyServiceException) {
                throw e;
            }
            throw new BaseException("Processing failure _" + e.getMessage(), e);
        }
        return false; }}Copy the code

Done ^_^