package com.rongyichuang.tools;
|
|
import org.junit.jupiter.api.Test;
|
|
import java.sql.*;
|
import java.time.LocalDateTime;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
public class DataInsertTest {
|
|
private static final String URL = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&connectTimeout=60000&socketTimeout=60000";
|
private static final String USER = "ryc";
|
private static final String PASS = "KiYap3E8X8RLcM6T";
|
|
@Test
|
public void insertMockData() throws Exception {
|
try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
|
conn.setAutoCommit(false);
|
try {
|
// 1) 获取两个活动ID，不足则最小化补充
|
List<Long> activityIds = fetchActivityIds(conn);
|
if (activityIds.size() < 2) {
|
long a1 = insertActivity(conn, "测试比赛A", "testA", 7);
|
long a2 = insertActivity(conn, "测试比赛B", "testB", 10);
|
activityIds.add(a1);
|
activityIds.add(a2);
|
}
|
|
// 2) 插入 20 个用户，并收集生成的 user_id
|
List<Long> userIds = new ArrayList<>();
|
for (int i = 1; i <= 20; i++) {
|
long uid = insertUser(conn, "批量用户" + i, "wx_openid_batch_" + i, "wx_unionid_batch_" + i, 1, "1380001" + String.format("%04d", i));
|
userIds.add(uid);
|
}
|
|
// 3) 插入 20 个学员（未审核 audit_state=0），每个学员关联对应 user_id
|
List<Long> playerIds = new ArrayList<>();
|
for (int i = 1; i <= 20; i++) {
|
long pid = insertPlayer(conn,
|
"批量学员" + i,
|
"1380014" + String.format("%04d", i),
|
1, // role_id
|
0, // audit_state 未审核
|
1, // state=1
|
userIds.get(i - 1));
|
playerIds.add(pid);
|
}
|
|
// 4) 报名关系：全部报名 activityIds[0]，前3名再报名 activityIds[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("[DataInsertTest] 插入完成：用户20、学员20、报名关系" + (20 + 3));
|
} catch (Exception ex) {
|
conn.rollback();
|
throw ex;
|
}
|
}
|
}
|
|
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("插入学员失败");
|
}
|
|
// 注意：按 db.sql 拼写为 t_avtivity_player
|
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("插入报名关系失败");
|
}
|
}
|
