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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
@Component
public class DatabaseMigrationTool {
@Autowired
@Qualifier("mysqlDataSource")
private DataSource mysqlDataSource;
@Autowired
@Qualifier("sqliteDataSource")
private DataSource sqliteDataSource;
public void migrateFromMySQLToSQLite() {
log.info("开始从MySQL迁移到SQLite");
try {
createSQLiteSchema();
migrateData();
validateMigration();
log.info("数据迁移完成");
} catch (Exception e) {
log.error("数据迁移失败", e);
throw new RuntimeException("迁移过程中发生错误", e);
}
}
private void createSQLiteSchema() throws SQLException {
String[] createTableSQLs = {
"""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
balance REAL DEFAULT 0.0,
is_active INTEGER DEFAULT 1,
created_time TEXT DEFAULT (datetime('now')),
updated_time TEXT DEFAULT (datetime('now')),
deleted INTEGER DEFAULT 0
)
""",
"""
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
product_name TEXT NOT NULL,
amount REAL NOT NULL,
status TEXT DEFAULT 'PENDING',
created_time TEXT DEFAULT (datetime('now')),
deleted INTEGER DEFAULT 0,
FOREIGN KEY (user_id) REFERENCES users(id)
)
""",
"""
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)
""",
"""
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)
"""
};
try (Connection conn = sqliteDataSource.getConnection();
Statement stmt = conn.createStatement()) {
for (String sql : createTableSQLs) {
stmt.execute(sql);
}
log.info("SQLite表结构创建完成");
}
}
private void migrateData() throws SQLException {
migrateUsers();
migrateOrders();
}
private void migrateUsers() throws SQLException {
String selectSQL = """
SELECT id, username, email, balance, is_active,
created_time, updated_time, deleted
FROM users
WHERE deleted = 0
""";
String insertSQL = """
INSERT INTO users (id, username, email, balance, is_active,
created_time, updated_time, deleted)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""";
try (Connection mysqlConn = mysqlDataSource.getConnection();
Connection sqliteConn = sqliteDataSource.getConnection();
PreparedStatement selectStmt = mysqlConn.prepareStatement(selectSQL);
PreparedStatement insertStmt = sqliteConn.prepareStatement(insertSQL)) {
sqliteConn.setAutoCommit(false);
try (ResultSet rs = selectStmt.executeQuery()) {
int batchCount = 0;
while (rs.next()) {
insertStmt.setLong(1, rs.getLong("id"));
insertStmt.setString(2, rs.getString("username"));
insertStmt.setString(3, rs.getString("email"));
insertStmt.setBigDecimal(4, rs.getBigDecimal("balance"));
insertStmt.setBoolean(5, rs.getBoolean("is_active"));
insertStmt.setTimestamp(6, rs.getTimestamp("created_time"));
insertStmt.setTimestamp(7, rs.getTimestamp("updated_time"));
insertStmt.setInt(8, rs.getInt("deleted"));
insertStmt.addBatch();
batchCount++;
if (batchCount % 1000 == 0) {
insertStmt.executeBatch();
sqliteConn.commit();
insertStmt.clearBatch();
log.info("已迁移 {} 条用户记录", batchCount);
}
}
insertStmt.executeBatch();
sqliteConn.commit();
log.info("用户数据迁移完成，共迁移 {} 条记录", batchCount);
}
}
}
}
|
微信
