package com.rongyichuang.tools;
|
|
import org.junit.jupiter.api.Test;
|
import java.sql.*;
|
|
public class FixNamesTest {
|
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 fixChineseNames() throws Exception {
|
try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
|
conn.setAutoCommit(false);
|
try {
|
// 修复活动中文名称
|
try (PreparedStatement ps = conn.prepareStatement("UPDATE t_activity SET name=? WHERE path=?")) {
|
ps.setString(1, "测试比赛A"); ps.setString(2, "testA"); ps.executeUpdate();
|
ps.setString(1, "测试比赛B"); ps.setString(2, "testB"); ps.executeUpdate();
|
}
|
// 修复学员中文名称：根据手机号尾号设置为“批量学员X”
|
try (PreparedStatement sel = conn.prepareStatement("SELECT id, phone FROM t_player");
|
ResultSet rs = sel.executeQuery()) {
|
while (rs.next()) {
|
long id = rs.getLong("id");
|
String phone = rs.getString("phone");
|
String tail = phone != null && phone.length() >= 2 ? phone.substring(phone.length() - 2) : "01";
|
// 解析为数字索引（避免前导零问题）
|
int idx;
|
try { idx = Integer.parseInt(tail); } catch (Exception e) { idx = 1; }
|
String name = "批量学员" + idx;
|
try (PreparedStatement upd = conn.prepareStatement("UPDATE t_player SET name=? WHERE id=?")) {
|
upd.setString(1, name);
|
upd.setLong(2, id);
|
upd.executeUpdate();
|
}
|
}
|
}
|
conn.commit();
|
System.out.println("[FixNamesTest] 中文名称修复完成");
|
} catch (Exception ex) {
|
conn.rollback();
|
throw ex;
|
}
|
}
|
}
|
}
|
