Spring Boot：实现MyBatis动态创建表

清疚 2023-10-08 18:52 24阅读 0赞

在有些应用场景中，我们会有需要动态创建和操作表的需求。

比如因为单表数据存储量太大而采取分表存储的情况，又或者是按日期生成日志表存储系统日志等等。这个时候就需要我们动态的生成和操作数据库表了。

而我们都知道，以往我们使用MyBatis是需要提前生成包括Model，Mapper和XML映射文件的，显然因为动态生成和操作表的需求一开始表都是不存在的，所以也就不能直接通过MyBatis连接数据库来生成我们的数据访问层代码并用来访问数据库了。

MyBatis提供了动态SQL，我们可以通过动态SQL，传入表名等信息然组装成建表和操作语句。

本小节中实现的案例中每个用户都会有一个自己日志表，我们的设计 思路就是在新创建用户的时候，根据用户的信息 创建一个日志存储表，表名是根据用户的 id 来创建，首先是控制中新增用户：

@Controller
    @RequestMapping("/user")
    public class UserController {
        
        @Autowired
        private IUserService userService;
        @PostMapping(value="/add")
        public Object addUser(@RequestBody User user) {
        
            return userService.addUser(user);
        }
    }

然后用户的操作IUserService实现定义如下：

@Service("userService")
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
        
    
        @Resource
        private UserMapper userMapper;
        @Resource
        private UserLogMapper userLogMapper;
    
        @Override
        @Transactional
        public User addUser(User user) {
        
            // 插入
            userMapper.saveUser(user);
            // 添加用户时，创建日志存储表
            Integer id = user.getId();
            //定义用户日志表表名
            String tableName = "t_user_log_" + id;
            //查询表是否存在
            if (userLogMapper.existTable(tableName) > 0) {
        
                //删除用户对应的日志表
                userLogMapper.dropTable(tableName);
            }
            //新创建表
            userLogMapper.createTable(tableName);
            return user;
        }
    }

UserMapper 就是操作用户数据相关的，这里使用的是新增用户的数据：

@Mapper
    public interface UserMapper extends BaseMapper<User> {
        
        int saveUser(@Param("user") User user);
    }

对应的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="flutter.dio.model.mapper.UserMapper">
        <insert id="saveUser"  useGeneratedKeys="true" keyProperty="id">
            insert into t_user(name,password,age)
            values(#{user.name},#{user.password},#{user.age})
        </insert>
    </mapper>

用户新建成功后，再根据用户的id定义表名，然后创建新的日志表：

UserLogMapper 是用户日志操作使用Mapper ，定义如下：

public interface UserLogMapper {
        
        //保存用户的日志 
        int insert(@Param("tableName")String tableName, @Param("userLog") UserLog userLog);
        /**
         * 查找用户全部的日志
         * @param tableName 用户对应的表名
         * @return
         */
        List<UserLog> selectAll(@Param("tableName")String tableName);
    
        /**
         * 是否存在表
         * @param tableName
         * @return
         */
        int existTable(@Param("tableName")String tableName);
        /**
         * 删除表
         * @param tableName
         * @return
         */
        int dropTable(@Param("tableName")String tableName);
        /**
         * 创建表
         * @param tableName
         * @return
         */
        int createTable(@Param("tableName")String tableName);
    }

UserLogMapper对应的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="flutter.dio.model.mapper.UserLogMapper">
        <resultMap id="BaseResultMap" type="flutter.dio.model.entity.UserLog">
            <id column="id" jdbcType="BIGINT" property="id"/>
            <result column="user_name" jdbcType="VARCHAR" property="userName"/>
            <result column="operation" jdbcType="VARCHAR" property="operation"/>
            <result column="method" jdbcType="VARCHAR" property="method"/>
            <result column="params" jdbcType="VARCHAR" property="params"/>
            <result column="time" jdbcType="BIGINT" property="time"/>
            <result column="ip" jdbcType="VARCHAR" property="ip"/>
        </resultMap>
        <sql id="Base_Column_List">
            id, user_name, operation, method, params, time, ip
        </sql>
    
        <insert id="insert" parameterType="flutter.dio.model.entity.UserLog">
            insert into ${tableName} (id, user_name, operation,
                                      method, params, time,
                                      ip)
            values (#{userLog.id,jdbcType=BIGINT}, #{userLog.userName,jdbcType=VARCHAR},
                    #{userLog.operation,jdbcType=VARCHAR},
                    #{userLog.method,jdbcType=VARCHAR}, #{userLog.params,jdbcType=VARCHAR}, #{userLog.time,jdbcType=BIGINT},
                    #{userLog.ip,jdbcType=VARCHAR})
        </insert>
    
        <select id="selectAll" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            from ${tableName}
        </select>
    
        
        <select id="existTable" parameterType="String" resultType="Integer">
            select count(*)
            from information_schema.TABLES
            where table_name = #{tableName}
        </select>
        
        <update id="dropTable">
            DROP TABLE IF EXISTS ${tableName}
        </update>
        
        <update id="createTable" parameterType="String">
            CREATE TABLE ${tableName}
            (
                `id`        bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
                `user_name` varchar(50)   DEFAULT NULL COMMENT '用户名',
                `operation` varchar(50)   DEFAULT NULL COMMENT '用户操作',
                `method`    varchar(200)  DEFAULT NULL COMMENT '请求方法',
                `params`    varchar(5000) DEFAULT NULL COMMENT '请求参数',
                `time`      bigint(20) NOT NULL COMMENT '执行时长(毫秒)',
                `ip`        varchar(64)   DEFAULT NULL COMMENT 'IP地址',
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=2897 DEFAULT CHARSET=utf8 COMMENT='用户操作日志';
        </update>
    </mapper>

上述代码中包括两部分内容，一部分是对表的操作 创建 与 删除，另一部分是对表中的数据的操作，保存用户的日志数据与查询用户的日志数据，都需要将用户对应的日志表名做为参数查询。