package com.rongyichuang.tools;
|
|
import org.springframework.boot.CommandLineRunner;
|
import org.springframework.stereotype.Component;
|
|
import java.sql.*;
|
import java.time.LocalDateTime;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
@Component
|
public class MockDataInserter implements CommandLineRunner {
|
|
private static final String URL = "jdbc:mysql://140.143.152.226:3308/ryc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
|
private static final String USER = "openai";
|
private static final String PASS = "Xml@uk2025";
|
|
@Override
|
public void run(String... args) {
|
String flag = System.getenv("RUN_MOCK_INSERT");
|
if (!"1".equals(flag)) {
|
return;
|
}
|
System.out.println("[MockDataInserter] RUN_MOCK_INSERT=1 检测到，开始插入模拟数据...");
|
|
try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
|
conn.setAutoCommit(false);
|
try {
|
// 安全检查：若已有足够数据则跳过
|
if (existingPlayers(conn) >= 20) {
|
System.out.println("[MockDataInserter] 检测到已有 >=20 条学员数据，跳过插入。");
|
return;
|
}
|
|
// 1) 准备 2 个活动（若不足则补充）
|
List<Long> activityIds = fetchActivityIds(conn);
|
if (activityIds.size() < 2) {
|
long a1 = insertActivity(conn, "模拟比赛A", "mockA", 7);
|
long a2 = insertActivity(conn, "模拟比赛B", "mockB", 10);
|
activityIds.add(a1);
|
activityIds.add(a2);
|
}
|
|
// 2) 插入 20 个用户
|
List<Long> userIds = new ArrayList<>();
|
for (int i = 1; i <= 20; i++) {
|
long uid = insertUser(conn, "模拟用户" + i, "wx_openid_" + i, "wx_unionid_" + i, 1, "1380000" + String.format("%04d", i));
|
userIds.add(uid);
|
}
|
|
// 3) 插入 20 个学员（audit_state=0 未审核）
|
List<Long> playerIds = new ArrayList<>();
|
for (int i = 1; i <= 20; i++) {
|
long pid = insertPlayer(conn,
|
"学员" + i,
|
"1380013" + String.format("%04d", i),
|
1, // role_id
|
0, // audit_state 未审核
|
1, // state=1 正常
|
userIds.get(i - 1));
|
playerIds.add(pid);
|
}
|
|
// 4) 报名关系：所有学员报名 activityIds.get(0)，其中前3人再报名 activityIds.get(1)
|
for (int i = 0; i < playerIds.size(); i++) {
|
insertActivityPlayer(conn, activityIds.get(0), playerIds.get(i));
|
if (i < 3) {
|
insertActivityPlayer(conn, activityIds.get(1), playerIds.get(i));
|
}
|
}
|
|
conn.commit();
|
System.out.println("[MockDataInserter] 插入完成：用户20、学员20、报名关系" + (20 + 3));
|
} catch (Exception ex) {
|
conn.rollback();
|
System.err.println("[MockDataInserter] 插入失败，已回滚：" + ex.getMessage());
|
ex.printStackTrace();
|
}
|
} catch (Exception e) {
|
System.err.println("[MockDataInserter] 连接数据库失败：" + e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
|
private int existingPlayers(Connection conn) throws SQLException {
|
try (Statement st = conn.createStatement();
|
ResultSet rs = st.executeQuery("SELECT COUNT(1) FROM t_player")) {
|
if (rs.next()) return rs.getInt(1);
|
}
|
return 0;
|
}
|
|
private List<Long> fetchActivityIds(Connection conn) throws SQLException {
|
List<Long> ids = new ArrayList<>();
|
try (Statement st = conn.createStatement();
|
ResultSet rs = st.executeQuery("SELECT id FROM t_activity ORDER BY id LIMIT 2")) {
|
while (rs.next()) {
|
ids.add(rs.getLong(1));
|
}
|
}
|
return ids;
|
}
|
|
private long insertActivity(Connection conn, String name, String path, int deadlineDays) throws SQLException {
|
String sql = "INSERT INTO t_activity (pid, path, name, signup_deadline, rating_scheme_id, state) VALUES (0, ?, ?, ?, 1, 1)";
|
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
|
ps.setString(1, path);
|
ps.setString(2, name);
|
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now().plusDays(deadlineDays)));
|
ps.executeUpdate();
|
try (ResultSet keys = ps.getGeneratedKeys()) {
|
if (keys.next()) return keys.getLong(1);
|
}
|
}
|
throw new SQLException("插入活动失败");
|
}
|
|
private long insertUser(Connection conn, String name, String wxOpenId, String wxUnionId, int state, String mobile) throws SQLException {
|
String sql = "INSERT INTO t_user (name, wx_openid, wx_unionid, state, mobile) VALUES (?, ?, ?, ?, ?)";
|
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
|
ps.setString(1, name);
|
ps.setString(2, wxOpenId);
|
ps.setString(3, wxUnionId);
|
ps.setInt(4, state);
|
ps.setString(5, mobile);
|
ps.executeUpdate();
|
try (ResultSet keys = ps.getGeneratedKeys()) {
|
if (keys.next()) return keys.getLong(1);
|
}
|
}
|
throw new SQLException("插入用户失败");
|
}
|
|
private long insertPlayer(Connection conn, String name, String phone, long roleId, int auditState, int state, long userId) throws SQLException {
|
String sql = "INSERT INTO t_player (name, phone, role_id, audit_state, state, user_id) VALUES (?, ?, ?, ?, ?, ?)";
|
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
|
ps.setString(1, name);
|
ps.setString(2, phone);
|
ps.setLong(3, roleId);
|
ps.setInt(4, auditState);
|
ps.setInt(5, state);
|
ps.setLong(6, userId);
|
ps.executeUpdate();
|
try (ResultSet keys = ps.getGeneratedKeys()) {
|
if (keys.next()) return keys.getLong(1);
|
}
|
}
|
throw new SQLException("插入学员失败");
|
}
|
|
// 注意：表名为 t_avtivity_player（按 db.sql 拼写）
|
private long insertActivityPlayer(Connection conn, long activityId, long playerId) throws SQLException {
|
String sql = "INSERT INTO t_avtivity_player (activity_id, player_id) VALUES (?, ?)";
|
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
|
ps.setLong(1, activityId);
|
ps.setLong(2, playerId);
|
ps.executeUpdate();
|
try (ResultSet keys = ps.getGeneratedKeys()) {
|
if (keys.next()) return keys.getLong(1);
|
}
|
}
|
throw new SQLException("插入报名关系失败");
|
}
|
}
|
