package com.rongyichuang.region;
|
|
import org.junit.jupiter.api.Test;
|
import org.springframework.boot.test.context.SpringBootTest;
|
|
import java.sql.*;
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
|
@SpringBootTest
|
public class RegionDataInsertTest {
|
|
@Test
|
public void insertRegionData() {
|
// 使用应用配置的数据库连接信息
|
String url = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
|
String username = "ryc";
|
String password = "KiYap3E8X8RLcM6T";
|
|
try (Connection connection = DriverManager.getConnection(url, username, password)) {
|
System.out.println("=== 开始插入区域数据 ===");
|
|
// 先查看表结构
|
System.out.println("=== t_region 表结构 ===");
|
DatabaseMetaData metaData = connection.getMetaData();
|
ResultSet columns = metaData.getColumns(null, null, "t_region", null);
|
|
while (columns.next()) {
|
String columnName = columns.getString("COLUMN_NAME");
|
String columnType = columns.getString("TYPE_NAME");
|
System.out.printf("%-15s %s%n", columnName, columnType);
|
}
|
|
// 清空现有数据（可选）
|
// connection.createStatement().executeUpdate("DELETE FROM t_region");
|
|
String currentTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
|
|
// 插入省级数据
|
insertProvince(connection, "四川省", currentTime);
|
insertProvince(connection, "重庆市", currentTime);
|
|
// 获取四川省ID
|
Long sichuanId = getRegionIdByName(connection, "四川省");
|
|
// 插入市级数据
|
insertCity(connection, "成都市", sichuanId, currentTime);
|
insertCity(connection, "眉山市", sichuanId, currentTime);
|
insertCity(connection, "德阳市", sichuanId, currentTime);
|
|
// 获取成都市ID
|
Long chengduId = getRegionIdByName(connection, "成都市");
|
|
// 插入成都区县数据（根据图片内容）
|
String[] chengduDistricts = {
|
"天府新区", "东部新区", "高新", "锦江", "青羊", "金牛", "武侯", "成华",
|
"龙泉", "青白江", "新都", "温江", "双流", "郫都", "简阳", "都江堰",
|
"彭州", "邛崃", "崇州", "金堂", "新津", "大邑", "蒲江"
|
};
|
|
for (String district : chengduDistricts) {
|
insertDistrict(connection, district, chengduId, currentTime);
|
}
|
|
// 查看插入结果
|
System.out.println("\n=== 插入结果 ===");
|
Statement stmt = connection.createStatement();
|
ResultSet rs = stmt.executeQuery("SELECT id, name, pid, create_time FROM t_region ORDER BY pid, id");
|
|
while (rs.next()) {
|
System.out.printf("ID: %d, Name: %s, PID: %s, CreateTime: %s%n",
|
rs.getLong("id"),
|
rs.getString("name"),
|
rs.getObject("pid"),
|
rs.getString("create_time")
|
);
|
}
|
|
System.out.println("=== 数据插入完成 ===");
|
|
} catch (SQLException e) {
|
System.err.println("数据库操作失败: " + e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
|
private void insertProvince(Connection connection, String name, String createTime) throws SQLException {
|
String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, 0, NULL, 1, 0, 1, ?, ?, 1, 1, 0)";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, name);
|
stmt.setString(2, createTime);
|
stmt.setString(3, createTime);
|
int result = stmt.executeUpdate();
|
System.out.println("插入省级: " + name + " - " + (result > 0 ? "成功" : "失败"));
|
}
|
}
|
|
private void insertCity(Connection connection, String name, Long parentId, String createTime) throws SQLException {
|
String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, ?, NULL, 2, 0, 1, ?, ?, 1, 1, 0)";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, name);
|
stmt.setLong(2, parentId);
|
stmt.setString(3, createTime);
|
stmt.setString(4, createTime);
|
int result = stmt.executeUpdate();
|
System.out.println("插入市级: " + name + " (parent: " + parentId + ") - " + (result > 0 ? "成功" : "失败"));
|
}
|
}
|
|
private void insertDistrict(Connection connection, String name, Long parentId, String createTime) throws SQLException {
|
String sql = "INSERT INTO t_region (name, pid, code, level, leaf_flag, state, create_time, update_time, create_user_id, update_user_id, version) VALUES (?, ?, NULL, 3, 1, 1, ?, ?, 1, 1, 0)";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, name);
|
stmt.setLong(2, parentId);
|
stmt.setString(3, createTime);
|
stmt.setString(4, createTime);
|
int result = stmt.executeUpdate();
|
System.out.println("插入区县: " + name + " (parent: " + parentId + ") - " + (result > 0 ? "成功" : "失败"));
|
}
|
}
|
|
private Long getRegionIdByName(Connection connection, String name) throws SQLException {
|
String sql = "SELECT id FROM t_region WHERE name = ?";
|
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
|
stmt.setString(1, name);
|
ResultSet rs = stmt.executeQuery();
|
if (rs.next()) {
|
return rs.getLong("id");
|
}
|
}
|
return null;
|
}
|
}
|
