在有些应用场景中,我们会有需要动态创建和操作表的需求。
比如因为单表数据存储量太大而采取分表存储的情况,又或者是按日期生成日志表存储系统日志等等。这个时候就需要我们动态的生成和操作数据库表了。
而我们都知道,以往我们使用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核心内容如下:文章来源:https://www.uudwc.com/A/ZGdAa/
<?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>
上述代码中包括两部分内容,一部分是对表的操作 创建 与 删除,另一部分是对表中的数据的操作,保存用户的日志数据与查询用户的日志数据,都需要将用户对应的日志表名做为参数查询。文章来源地址https://www.uudwc.com/A/ZGdAa/