Mysql + Mybatis动态建表
service层业务
package com.zx.common.service.impl; import com.zx.common.entity.SysUser; import com.zx.common.mapper.SysUserMapper; import com.zx.common.service.SysUserService; import com.zx.common.util.DateUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; import java.util.UUID; @Service("sysUserService") public class SysUserServiceImpl implements SysUserService { @Autowired private SysUserMapper sysUserMapper; @Override public List<SysUser> selectAllUser() { return sysUserMapper.selectAllUser(); } @Transactional(rollbackFor = {Exception.class}) @Override public void updateUserInfoTimer() { //根据上一天的日期将sys_user重命名为sys_user_上一天日期 String beforeDateStr = DateUtil.getBeforeDateString(); String rename = "sys_user_" + beforeDateStr + UUID.randomUUID().toString().substring(0,16); sysUserMapper.renameTable(rename.replace("-","")); //新建sys_user表 sysUserMapper.createTable(); } }
mapper层接口
package com.zx.common.mapper; import com.zx.common.entity.SysUser; import org.apache.ibatis.annotations.Param; import java.util.List; public interface SysUserMapper { List<SysUser> selectAllUser(); void renameTable(@Param("tableName") String tableName); void createTable(); }
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.zx.common.mapper.SysUserMapper" > <resultMap id="BaseResultMap" type="com.zx.common.entity.SysUser" > <id column="ID" property="id" jdbcType="INTEGER" /> <result column="USERNAME" property="username" jdbcType="VARCHAR" /> <result column="PASSWORD" property="password" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > ID, USERNAME, PASSWORD </sql> <update id="renameTable" parameterType="java.lang.String"> rename table sys_user to ${tableName }; </update> <update id="createTable"> CREATE TABLE sys_user ( ID INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键', USERNAME VARCHAR(64) NOT NULL DEFAULT '', PASSWORD VARCHAR(32) NOT NULL DEFAULT '', PRIMARY KEY(ID), KEY I_SYS_USER_USERNAME (USERNAME) )ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT='用户表'; </update> </mapper>