mybatis动态表名(分库分表)

//测试代码
@RestController
public class UserController {

    @Resource
    private UserMapper userMapper;


    @Autowired
    MysqlTbNameUtil mysqlTbNameUtil;

    @GetMapping("/selectByAge")
    @ResponseBody
    public Object selectByAge() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByAge(27);
//        return userMapper.selectByAge(27);
    }

    @GetMapping("/selectByAge2")
    @ResponseBody
    public Object selectByAge2() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByAge2(27);
//        return userMapper.selectByAge2(27);
    }

    @GetMapping("/selectByAgeAndName")
    @ResponseBody
    public Object selectByAgeAndName() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByAgeAndName(1, "ctl");
//        return userMapper.selectByAgeAndName(1, "ctl");
    }

    @GetMapping("/selectByAgeAndName2")
    @ResponseBody
    public Object selectByAgeAndName2() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByAgeAndName2(1, "ctl");
//        return userMapper.selectByAgeAndName2(1, "ctl");
    }

    @GetMapping("/selectByUser")
    @ResponseBody
    public Object selectByUser() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByUser(new User().withAge(1));
//        return userMapper.selectByUser(new User().withAge(1));
    }

    @GetMapping("/selectByUser2")
    @ResponseBody
    public Object selectByUser2() {
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByUser2(new User().withId(1L));
//        return userMapper.selectByUser2(new User().withId(1L));
    }

    @GetMapping("/selectByMap")
    @ResponseBody
    public Object selectByMap() {
        Map<String, Object> map = new HashMap<>();
        map.put(User.Fields.age.name(), "27");
        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").selectByMap(map);
//        return userMapper.selectByMap(map);
    }

    @GetMapping("/update")
    @ResponseBody
    public Object update() {

        return mysqlTbNameUtil.getMapperPlus(UserMapper.class, "tb001").update(new User().withId(26L).withAge(1).withUserName("ctl").withAddress("新乡" + System.currentTimeMillis()));
//        return userMapper.update(new User().withId(26L).withAge(1).withUserName("ctl").withAddress("新乡" + System.currentTimeMillis()));
    }

}



//mapper.java

@Mapper
public interface UserMapper {

    List<User> selectByAgeAndName(int age,String userName);
    List<User> selectByAgeAndName2(@Param("age") int age2,@Param("name") String userName2);

    List<User> selectByAge(@Param("age") int age2);

    List<User> selectByAge2(int age3);

    List<User>  selectByUser(User user);
    List<User>  selectByUser2(@Param("u") User user);
    List<User>  selectByMap(Map<String,Object> user);

    long update(User user);

}



//mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ctl.domain.mapper.UserMapper">

    <resultMap id="userResultMap" type="com.ctl.domain.model.User">
        <result column="id" property="id"/>
        <result column="user_name" property="userName"/>
        <result column="age" property="age"/>
        <result column="address" property="address"/>
    </resultMap>

    <select id="selectByAge" resultMap="userResultMap">
        SELECT * FROM user where age=#{age}
    </select>
    <select id="selectByAge2" resultMap="userResultMap">
        SELECT * FROM user where age=#{id}
    </select>
    <select id="selectByAgeAndName" resultMap="userResultMap">
        SELECT * FROM user where user_name = #{param2} and age=#{param1}
    </select>
    <select id="selectByAgeAndName2" resultMap="userResultMap">
        SELECT * FROM user where user_name = #{name} and age=#{age}
    </select>
    <select id="selectByUser" resultMap="userResultMap">
        SELECT * FROM user
        <where>
            <if test="userName!=null and userName!=''">
                user_name = #{userName}
            </if>
            <if test="age!=null ">
                age = #{age}
            </if>
            <if test="id!=null ">
                id = #{id}
            </if>
        </where>
    </select>
    <select id="selectByUser2" resultMap="userResultMap">
        SELECT * FROM user
        <where>
            <if test="u.userName!=null and u.userName!=''">
                user_name = #{u.userName}
            </if>
            <if test="u.age!=null ">
                age = #{u.age}
            </if>
            <if test="u.id!=null ">
                id = #{u.id}
            </if>
        </where>
    </select>
    <select id="selectByMap" resultMap="userResultMap">
        SELECT * FROM user
        <where>
            <if test="userName!=null and userName!=''">
                user_name = #{userName}
            </if>
            <if test="age!=null ">
                age = #{age}
            </if>
            <if test="id!=null ">
                id = #{id}
            </if>
        </where>
    </select>
 
    <update id="update" parameterType="com.ctl.domain.model.User">
        update user
        <set>
            <if test="userName!=null and userName!=''">
                user_name = #{userName},
            </if>
            <if test="age!=null ">
                age = #{age},
            </if>
            <if test="address!=null and address!=''">
                address = #{address},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>



//MysqlTbNameUtil
@Component("mysqlTbNameUtil")
public class MysqlTbNameUtil {
    public static final String TB_PREFIX_PARAMKEY="tbPrefix";

    private final  ThreadLocal<String> holder= new ThreadLocal<>();
    public String get(){
        String value = holder.get();
        return ObjectUtils.isEmpty(value)?"":value;
    }
    public void set(String prefix){
        if(prefix==null){
            throw new RuntimeException("tenantId not null.");
        }
        holder.set(prefix);
    }
    public void clear(){
        holder.remove();
    }

    public <T> T getMapperPlus(Class<T> clazz, String prefix) {
        try {
            holder.set(prefix + "_");
            return SpringContextUtils.getApplicationContext().getBean(clazz);
        } catch (Exception e) {
            return null;
        }
    }

}




//ReplaceTablePlugin

/**
 * mybatis插件实现动态表名，可以拦截器新增、编辑、删除、查询等
 * MyBatis插件是一种拦截器，可以在MyBatis某个行为执行时进行拦截并改变这个行为。
 * 通常，MyBatis的插件可以作用于MyBatis中的四大接口，
 * 分别为Executor，ParameterHandler，ResultSetHandler和StatementHandler
 */
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class ReplaceTablePlugin implements Interceptor {
    @Autowired
    MysqlTbNameUtil mysqlTbNameUtil;
    private static final Logger log = LoggerFactory.getLogger(ReplaceTablePlugin.class);

    private final static Map<String, String> TABLE_MAP = new LinkedHashMap<>();

    /**
     * 需要替换的表(替换前的表名和替换后的表名)
     * 方案一、直接使用TABLE_MAP的表替换,写死不友好
     * 方案二、使用mysqlTbNameUtil获取前缀,友好且线程安全
     */
    static {
//        TABLE_MAP.put("user", "tb001_user");
        TABLE_MAP.put("user", "");
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        doTable(statementHandler, metaObject);
        return invocation.proceed();
    }

    /**
     * 是否为基本数据类型
     *
     * @param clazz
     * @return
     */
    public static boolean isPrimitiveType(Class<?> clazz) {
        List<String> extracted = extracted();
        return extracted.contains(clazz.getName());
    }

    private static List<String> extracted() {
        List<String> list = new ArrayList<>();
        list.add(Boolean.class.getName());
        list.add(Byte.class.getName());
        list.add(Character.class.getName());
        list.add(Short.class.getName());
        list.add(Integer.class.getName());
        list.add(Long.class.getName());
        list.add(Float.class.getName());
        list.add(Double.class.getName());
        list.add(String.class.getName());
        list.add(Date.class.getName());
        list.add(LocalDate.class.getName());
        list.add(LocalDateTime.class.getName());
        return list;
    }

    /**
     * 是否为基本数据类型
     *
     * @param obj
     * @return
     */
    public static boolean isPrimitiveType(Object obj) {
        return isPrimitiveType(obj.getClass());
    }

    private void doTable(StatementHandler handler, MetaObject metaStatementHandler) throws ClassNotFoundException {
        BoundSql boundSql = handler.getBoundSql();
        String originalSql = boundSql.getSql();
        ParameterHandler parameterHandler = handler.getParameterHandler();
        Object parameterObject = parameterHandler.getParameterObject();
        boolean primitiveType = isPrimitiveType(parameterObject);
        if (primitiveType) {
            MapperMethod.ParamMap paramMap = new MapperMethod.ParamMap();
            paramMap.put("id", parameterObject);
            paramMap.put(TB_PREFIX_PARAMKEY, mysqlTbNameUtil.get());
            ReflectUtil.setFieldValue(parameterHandler, "parameterObject", paramMap);
            log.info("基本类型{},更改为ParamMap={}", parameterObject.getClass().getName(), paramMap);
        } else {
            if (parameterObject instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap) parameterObject;
                paramMap.put(TB_PREFIX_PARAMKEY, mysqlTbNameUtil.get());
                log.info("非基本类型ParamMap,更新为ParamMap={}", paramMap);
            } else {
                Map<String, Object> map = new HashMap<>();
                BeanUtil.beanToMap(parameterObject, map, false, true);
                MapperMethod.ParamMap paramMap = new MapperMethod.ParamMap();
                paramMap.put(TB_PREFIX_PARAMKEY, mysqlTbNameUtil.get());
                paramMap.putAll(map);
                ReflectUtil.setFieldValue(parameterHandler, "parameterObject", paramMap);
                log.info("非基本类型{},更改为ParamMap={}", parameterObject.getClass().getName(), paramMap);
            }
        }
        log.info("parameterObject={}", JacksonMapper.getJsonStr(parameterHandler.getParameterObject()));
        if (originalSql != null && !originalSql.equals("")) {
            log.info("拦截前的sql:{}", originalSql);
            originalSql= originalSql.replace(","," , ").replaceAll("\\s+", " ").replaceAll("\\n"," ");
            if (isReplaceTableName(originalSql)) {
                originalSql = replaceTableName(originalSql);
                log.info("拦截后的sql:{}", originalSql);
                metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
            }
        }
    }

    private boolean isReplaceTableName(String sql) {
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        try {
            List<String> tableList = tablesNamesFinder.getTableList(CCJSqlParserUtil.parse(sql));
            String[] splits = sql.split(" ");
            for (int i = 0; i < splits.length; i++) {
                String split = splits[i].toLowerCase();
                if (!ObjectUtils.isEmpty(TABLE_MAP.keySet().contains(split)) && tableList.contains(split)) {
                    return true;
                }
            }
        } catch (Exception e) {
            log.error("", e);
        }
        return false;
    }

    /**
     * 替换sql中的表名得到分表表名
     * @param sql
     * @return
     */
    private String replaceTableName(String sql) {
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        try {
            List<String> tableList = tablesNamesFinder.getTableList(CCJSqlParserUtil.parse(sql));
            log.info("tableList={}", tableList);
            String[] splits = sql.split(" ");
            for (int i = 0; i < splits.length; i++) {
                String split = splits[i].toLowerCase();
                if (!ObjectUtils.isEmpty(TABLE_MAP.keySet().contains(split)) && tableList.contains(split)) {
                    splits[i]=mysqlTbNameUtil.get()+split;
                }
            }
            StringJoiner stringJoiner =new StringJoiner(" ");
            for (int i = 0; i < splits.length; i++) {
                stringJoiner.add(splits[i]);
            }
            return stringJoiner.toString();
        } catch (Exception e) {
            log.error("", e);
        }
        return sql;
    }
    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {
    }

    public static <T> T realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return (T) target;
    }
}

mongo动态分表参看 mongodb动态分表_ctllin的博客-CSDN博客

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` int(11) NOT NULL  COMMENT '年龄',
  `address` varchar(128) COMMENT '地址',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户表';



CREATE TABLE `tb001_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` int(11) NOT NULL  COMMENT '年龄',
  `address` varchar(128) COMMENT '地址',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户表';