通过代码的方式创建表，备份数据_代码实现表的备份

作者：Monodyee | 2024-04-12 10:44:01

踩

代码实现表的备份

表结构

CREATE TABLE t_msg (
  id varchar(100) NOT NULL,
  create_ip varchar(100)  ,
  create_time timestamp(6),
  group_id varchar(100) NOT NULL,
  msg_content text  ,
  msg_content_type varchar(100) NOT NULL,
  msg_type varchar(20) NOT NULL,
  revoked int4 NOT NULL,
  sender_dept_id varchar(100),
  sender_dept_name varchar(100),
  sender_org_id varchar(100),
  sender_org_name varchar(100),
  send_time timestamp(6),
  sender_id varchar(100),
  sender_name varchar(100),
  unique_key varchar(255)  
);

CREATE INDEX idx_t_msg1 ON t_msg(group_id);
CREATE INDEX idx_t_msg2 ON t_msg(send_time);
CREATE INDEX idx_t_msg3 ON t_msg(unique_key);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE t_msg_year (
  id varchar(100) NOT NULL,
  create_time timestamp(6),
  year int4 NOT NULL
);
1
2
3
4
5

CREATE TABLE t_msg_rel_user (
  id varchar(100) NOT NULL,
  group_id varchar(100) NOT NULL,
  msg_id varchar(100) NOT NULL,
  rel_type varchar(20) NOT NULL,
  removed int4 NOT NULL,
  user_id varchar(100),
  user_name varchar(100),
  user_dept_id varchar(100),
  user_dept_name varchar(100),
  user_org_id varchar(100),
  user_org_name varchar(100),
  view_time timestamp(6),
  viewed int4 NOT NULL
);

CREATE INDEX idx_t_msg_rel_user1 ON t_msg_rel_user(msg_id);
CREATE INDEX idx_t_msg_rel_user2 ON t_msg_rel_user(group_id);
CREATE INDEX idx_t_msg_rel_user3 ON t_msg_rel_user(user_id);
CREATE INDEX idx_t_msg_rel_user4 ON t_msg_rel_user(viewed);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

实体模型

-- 消息模型
@Entity
@Table(
    name="T_MSG",
    indexes={
		@Index(name="idx_T_MSG1",columnList="groupId"),
        @Index(name="idx_T_MSG2",columnList="sendTime"),
		@Index(name="idx_T_MSG3",columnList="uniqueKey")
    }
)
public class Msg extends IEntity {
    public static final String MSGCONTENTTYPE_TEXT="text";//文本
    public static final String MSGCONTENTTYPE_OBJECT="object";//对象
	
	@Column(length=100,nullable=false)
    private String groupId;//消息所属组id
	@Column(length=20,nullable=false)
    private String msgType;//消息类型 见XysdMsgConstants.MSG_TYPE_
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="sender_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="sender_name",length=100))
    })
    private SysDataSimpleValObj sender;//消息发送者
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="sender_dept_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="sender_dept_name",length=100))
    })
    private SysDataSimpleValObj sendDept;//消息发送者当时部门
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="sender_org_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="sender_org_name",length=100))
    })
    private SysDataSimpleValObj sendOrg;//消息发送者当时机构
    private Date sendTime;//消息发送时间
    
    @Column(length=100,nullable=false)
    private String msgContentType;//消息内容类型
    @Lob
    @Type(type="org.hibernate.type.TextType")
    private String msgContent;//消息内容
    
    @Column(length=100)
    private String createIp;//创建ip
    private Date createTime;//创建时间
    @Type(type="org.hibernate.type.NumericBooleanType")
    private boolean revoked=false;//是否已撤销的
	@Column
	private String uniqueKey;//防止重复的唯一key
	//省略get方法

	public Msg() {
        super();
        this.createTime = new Date();
    }
	public Msg(OperateInfo operateInfo, MsgGroup msgGroup, String msgContentType, String msgContent) {
		this();
		
		this.groupId = msgGroup.getId();
		this.msgType = msgGroup.getMsgType();
		
		this.sender = operateInfo.getOperator().getUser();
		this.sendDept = operateInfo.getOperator().obtainDept();
		this.sendOrg = operateInfo.getOperator().obtainOrg();
		this.sendTime = operateInfo.obtainNotNullOperateTime();
		this.msgContentType = msgContentType;
		this.msgContent = msgContent;
		
		this.createIp = operateInfo.getOperateIp();

		this.uniqueKey = Utils.getUUID(JsonConverter.toJsonStr(
				Utils.buildMap("groupId", groupId, "msgType", msgType,
						"sender", sender, "sendTime", sendTime, "msgContentType", msgContentType,
						"msgContent", msgContent)), "");
	}
    
    /**
     * 撤回已发消息
     * @param operateInfo
     */
    public void revokeMsg(OperateInfo operateInfo){
        this.revoked = true;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

--消息年度模型
@Entity
@Table(
    name="T_MSG_YEAR"
)
public class MsgYear extends IEntity {
    
	private Date createTime = new Date();
	private int year;//表年度
    
    
    public Date getCreateTime() {
		return createTime;
	}
	public int getYear() {
		return year;
	}
	
	
	public MsgYear() {
        super();
    }
    public MsgYear(int year) {
        super();
        this.id = year+"_msg";
        this.year = year;
        this.createTime = new Date();
    }
    
    
    //获取消息表名称
    public String obtainMsgTableName() {
    	return "T_MSG_"+year;
    }
    //获取消息查询标志表名称
    public String obtainMsgRelUserTableName() {
    	return "T_MSG_REL_USER_"+year;
    }
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

--消息用户关联关系模型
@Entity
@Table(
    name="T_MSG_REL_USER",
    indexes={
		@Index(name="idx_T_MSG_REL_USER1",columnList="msgId"),
		@Index(name="idx_T_MSG_REL_USER2",columnList="groupId"),
        @Index(name="idx_T_MSG_REL_USER3",columnList="user_id"),
        @Index(name="idx_T_MSG_REL_USER4",columnList="viewed")
    }
)
public class MsgRelUser extends IEntity {
    public static final String RELTYPE_SENDER="sender";//消息发送者
    public static final String RELTYPE_RECEIVER="receiver";//消息接收者
	
    @Column(length=100,nullable=false)
    private String msgId;//所属消息id
    @Column(length=100,nullable=false)
    private String groupId;//所属消息组id
	@Column(length=20,nullable=false)
    private String relType;//用户关联类型
	
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="user_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="user_name",length=100))
    })
    private SysDataSimpleValObj user;//关联用户
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="user_dept_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="user_dept_name",length=100))
    })
    private SysDataSimpleValObj userDept;//关联用户当时部门
    @AttributeOverrides({
    	@AttributeOverride(name="id",column=@Column(name="user_org_id",length=100)),
    	@AttributeOverride(name="name",column=@Column(name="user_org_name",length=100))
    })
    private SysDataSimpleValObj userOrg;//关联用户当时机构
    
    @Type(type="org.hibernate.type.NumericBooleanType")
    private boolean viewed=false;//消息是否已阅
    private Date viewTime;//消息查阅时间
    
    @Type(type="org.hibernate.type.NumericBooleanType")
    private boolean removed=false;//消息是否已删除
    
    
	public String getMsgId() {
		return msgId;
	}
	public String getGroupId() {
		return groupId;
	}
	public String getRelType() {
		return relType;
	}
	public SysDataSimpleValObj getUser() {
		return user;
	}
	public SysDataSimpleValObj getUserDept() {
		return userDept;
	}
	public SysDataSimpleValObj getUserOrg() {
		return userOrg;
	}
	public boolean isViewed() {
		return viewed;
	}
	public Date getViewTime() {
		return viewTime;
	}
	public boolean isRemoved() {
		return removed;
	}
	
	
	
	public MsgRelUser() {
		super();
	}
	//创建默认发送用户记录关系
	public MsgRelUser(Msg msg) {
		super();
		this.id = msg.getId()+"_"+msg.getSender().getId();
		this.msgId = msg.getId();
		this.groupId = msg.getGroupId();
		this.relType = RELTYPE_SENDER;
		this.user = msg.getSender();
		this.userDept = msg.getSendDept();
		this.userOrg = msg.getSendOrg();
		this.viewed = true;
		this.viewTime = msg.getSendTime();
		this.removed = false;
	}
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

通过定时任务执行

@Component
public class CronCreateMsgTablesScheduler {
    @Autowired
    private ILockService lockService;
    @Autowired
    private MsgDomainService msgDomainService;
    
    /**
     * 每年1月1号0:10分执行一次定时任务
     */
    @Scheduled(cron="0 10 0 1 1 ?")
    public void cronCreateMsgTablesTask(){
        //增加乐观锁
        boolean locked=this.lockService.addLock("cronCreateMsgTableTask");
        if(!locked) {
            return;
        }
        try {
            this.msgDomainService.getNotNullCurrMsgYear();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ThreadLocalCache.clear();
            //删除乐观锁
            this.lockService.deleteLock("cronCreateMsgTableTask");
        }
    }
    
    //项目启动 直接检查 创建
    @PostConstruct
    public void checkAndCreateMsgTables() {
        this.cronCreateMsgTablesTask();
    }
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

备份数据

//系统目前所有消息年度
    private Map<Integer,MsgYear> yearToMsgYear=new ConcurrentHashMap<Integer,MsgYear>();
    public Map<Integer,MsgYear> getAllMsgYears(){
        if(this.yearToMsgYear.size()>0) return yearToMsgYear;
        
        synchronized (this.yearToMsgYear) {
            if(this.yearToMsgYear.size()>0) return yearToMsgYear;
            
            List<MsgYear> msgYears=this.msgRepository.findAllMsgYear();
            for (MsgYear msgYear:msgYears) {
                this.yearToMsgYear.put(msgYear.getYear(), msgYear);
            }
        }
        
        return this.yearToMsgYear;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

//获取 非空当前 消息年度
    public MsgYear getNotNullCurrMsgYear() {
        int currYear=CalendarUtils.getCurrentYear();
        MsgYear currMsgYear=this.yearToMsgYear.get(currYear);
        if(currMsgYear!=null) return currMsgYear;
        
        synchronized (this.yearToMsgYear) {
            this.getAllMsgYears();//加载this.yearToMsgYear
            
            currMsgYear=this.yearToMsgYear.get(currYear);
            if(currMsgYear!=null) return currMsgYear;
            
            currMsgYear=new MsgYear(currYear);
            this.msgRepository.createMsgYear(currMsgYear);
            this.yearToMsgYear.put(currMsgYear.getYear(), currMsgYear);
            
            //移动 消息 到 去年消息表中
            this.moveMsgToPrevYearTable();
        }
        
        return currMsgYear;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

//移动 消息 到 去年消息表中
    private void moveMsgToPrevYearTable() {
        int currYear=CalendarUtils.getCurrentYear();
        MsgYear prevMsgYear=this.yearToMsgYear.get(currYear-1);
        if(prevMsgYear==null) {
            return;
        }
        StringBuffer sql=null;
        /************************************备份Msg表数据************************************/
        Table[] tables=Msg.class.getAnnotationsByType(Table.class);
        //备份表名称
        String oldMsgTableName=tables[0].name();
        String tableName=oldMsgTableName+"_"+prevMsgYear.getYear();
        //备份表
        sql=new StringBuffer();
        sql.append("CREATE TABLE ").append(tableName).append(" AS ");
        sql.append("SELECT * FROM ").append(oldMsgTableName);
        this.createSQLQueryByParams(sql.toString()).executeUpdate();
        //添加主键
        sql=new StringBuffer();
        sql.append("ALTER TABLE ").append(tableName).append(" ADD CONSTRAINT ");
        sql.append(tableName).append("_PK").append(" PRIMARY KEY (id)");
        this.createSQLQueryByParams(sql.toString()).executeUpdate();
        //创建索引
        Index[] indexs=tables[0].indexes();
        for (Index index : indexs) {
            sql=new StringBuffer();
            sql.append("CREATE ").append(index.unique()?"UNIQUE ":"").append("INDEX ").append(index.name().replace(oldMsgTableName,tableName));
            sql.append(" ON ");
            // SpringPhysicalNamingStrategy
            StringBuilder columns = new StringBuilder(index.columnList());
            for (int i = 1; i < columns.length() - 1; i++) {
                if (isUnderscoreRequired(columns.charAt(i - 1), columns.charAt(i), columns.charAt(i + 1))) {
                    columns.insert(i++, '_');
                }
            }
            sql.append(tableName).append("(").append(columns.toString().toLowerCase(Locale.ROOT)).append(")");
            this.createSQLQueryByParams(sql.toString()).executeUpdate();
        }
        
        /************************************备份MsgRelUser表数据************************************/
        tables=MsgRelUser.class.getAnnotationsByType(Table.class);
        //备份表名称
        String oldMsgQuerySignTableName=tables[0].name();
        tableName=oldMsgQuerySignTableName+"_"+prevMsgYear.getYear();
        //备份表
        sql=new StringBuffer();
        sql.append("CREATE TABLE ").append(tableName).append(" AS ");
        sql.append("SELECT * FROM ").append(oldMsgQuerySignTableName);
        this.createSQLQueryByParams(sql.toString()).executeUpdate();
        //添加主键
        sql=new StringBuffer();
        sql.append("ALTER TABLE ").append(tableName).append(" ADD CONSTRAINT ");
        sql.append(tableName).append("_PK").append(" PRIMARY KEY (id)");
        this.createSQLQueryByParams(sql.toString()).executeUpdate();
        //创建索引
        indexs=tables[0].indexes();
        for (Index index : indexs) {
            sql=new StringBuffer();
            sql.append("CREATE ").append(index.unique()?"UNIQUE ":"").append("INDEX ").append(index.name().replace(oldMsgQuerySignTableName,tableName));
            sql.append(" ON ");
            // SpringPhysicalNamingStrategy
            StringBuilder columns = new StringBuilder(index.columnList());
            for (int i = 1; i < columns.length() - 1; i++) {
                if (isUnderscoreRequired(columns.charAt(i - 1), columns.charAt(i), columns.charAt(i + 1))) {
                    columns.insert(i++, '_');
                }
            }
            sql.append(tableName).append("(").append(columns.toString().toLowerCase(Locale.ROOT)).append(")");
            this.createSQLQueryByParams(sql.toString()).executeUpdate();
        }
        
        /************************************清空当前表数据************************************/
        this.createSQLQueryByParams("TRUNCATE TABLE "+oldMsgTableName).executeUpdate();
        this.createSQLQueryByParams("TRUNCATE TABLE "+oldMsgQuerySignTableName).executeUpdate();
    }
    private boolean isUnderscoreRequired(char before, char current, char after) {
        return Character.isLowerCase(before) && Character.isUpperCase(current) && Character.isLowerCase(after);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/410334