SQL系类

批量修改某一列的sql

UPDATE user  
    SET name = 
        CASE id 
            WHEN 1 THEN "张三"
            WHEN 2 THEN "李四"
            WHEN 3 THEN "王五"
        END
WHERE id IN (1,2,3)

 xml文件格式

<update id="update">
		update test_student set name = case id
			<foreach collection="collection" separator=" " item="student" open=" " close=" ">
				when #{student.id} then #{student.name}
			</foreach>
			end
		where id in
			<foreach collection="collection" open="(" close=")" item="student"                 separator=",">
				#{student.id}
			</foreach>
</update>

查询重复列的离现在最近的时间

SELECT
	t1.重复列,
	t1.时间列,
	t1.其余列 
FROM
	表 t1
	INNER JOIN ( SELECT t2.重复列, max( t2.时间列 ) AS 时间列 FROM 表 t2 GROUP BY t2.重复列 ) AS t3
	ON t1.重复列 = t3.重复列 AND t1.时间列 = t3.时间列
	GROUP BY t1.重复列

案例

SELECT t1.time,t1.time FROM `test_student` t1 INNER JOIN 
(select t2.name,max(t2.time) as time from test_student t2 GROUP BY t2.`name` ) as t3
on t1.name = t3.name and t1.time = t3.time GROUP BY t1.name

编写SQL定时任务

-- 查看定时任务是否开启
SHOW VARIABLES LIKE 'event_scheduler'
-- 如果是关闭则开启
SET GLOBAL event_scheduler = ON
-- 执行代码
DELIMITER $$
DROP EVENT IF EXISTS deleteLog;
CREATE EVENT deleteLog
ON SCHEDULE EVERY 300 SECOND
ON COMPLETION PRESERVE
DO BEGIN
delete from sys_log where TO_DAYS(now())-TO_DAYS(log_time)>90;
END$$
DELIMITER ；

-- SCHEDULE EVERY 300 SECOND : 每5分钟执行一次
-- 还有很多比如:   YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND (根据自己要求来)
-- sys_log  : 你要操作的表
-- log_time : 表中含有事件的字段(必须有含有时间的字段 不然没有办法对比)
-- TO_DAYS(now())-TO_DAYS(log_time)>90 : 当前时间减去字段(log_time) 时间 大于 90 就 删除这些数据 

-- 查看事件
select * from mysql.event;

-- 删除事件
DROP EVENT deletLKMessageLog;

sql拦截器

package com.example.npdmnew.config;

import com.example.npdmnew.common.anno.UserSql;
import com.example.npdmnew.util.ThreadLocalUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Properties;

@Component
@Intercepts(
        {@Signature(
                type = Executor.class,
                method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class,
                        ResultHandler.class, CacheKey.class, BoundSql.class})})
public class SqlReplaceInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
//        Integer deptId = ThreadLocalUtil.get("user");
//        if (deptId < 10){
//            invocation.proceed();
//        }
        Object[] args = invocation.getArgs();
        Object target = invocation.getTarget();
        Method method1 = invocation.getMethod();
        //拿到运行sql的mappedStatement
        MappedStatement mappedStatement = (MappedStatement) args[0];
        //判断是否是select语句
        if (!"SELECT".equals(mappedStatement.getSqlCommandType().name())) return invocation.proceed();
        String nameSpace = mappedStatement.getId();
        String classPath = nameSpace.substring(0, nameSpace.lastIndexOf("."));
        String methodName = nameSpace.substring(nameSpace.lastIndexOf(".") + 1);
        //反射获取方法
        Class<?> clazz = Class.forName(classPath);
        Method[] methods = clazz.getDeclaredMethods();
//        Method declaredMethod = clazz.getDeclaredMethod(methodName);
//        if (!declaredMethod .isAnnotationPresent(UserSql.class)){
//            return invocation.proceed();
//        }
        BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
        String sql = boundSql.getSql();
        for (Method method : methods) {
            //是否含有userSql注解
            if (methodName.equals(method.getName())) {
                UserSql annotation = method.getAnnotation(UserSql.class);
                if (annotation == null) {
                    return invocation.proceed();
                }
                int code = annotation.code();
                String userSql = annotation.sql();
                //获取原始sql
//                BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
//                String sql = boundSql.getSql();
                Integer deptId = ThreadLocalUtil.get("user");
                if (deptId == null || deptId < 10) {
                    invocation.proceed();
                }
                //修改sql语句
                String newSql = "";
                if (code == 1) {
                    newSql = sql + "and dep_id = " + deptId;
                }
                Class boundClass = boundSql.getClass();
                //反射注入
                Field field = boundClass.getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, newSql);
            }
        }
//        BoundSql boundSql = (BoundSql) args[5];
//        String sql1 = boundSql.getSql();
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Interceptor.super.plugin(target);
    }

    @Override
    public void setProperties(Properties properties) {
        Interceptor.super.setProperties(properties);
    }
}