package com.rongyichuang.carousel;
|
|
import org.junit.jupiter.api.Test;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.boot.test.context.SpringBootTest;
|
import org.springframework.test.context.ActiveProfiles;
|
|
import javax.sql.DataSource;
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
@SpringBootTest
|
@ActiveProfiles("test")
|
public class DatabaseSchemaTest {
|
|
@Autowired
|
private DataSource dataSource;
|
|
@Test
|
public void testCarouselTableSchema() {
|
try (Connection connection = dataSource.getConnection()) {
|
System.out.println("=== 数据库连接成功 ===");
|
System.out.println("数据库URL: " + connection.getMetaData().getURL());
|
System.out.println("数据库产品: " + connection.getMetaData().getDatabaseProductName());
|
System.out.println("数据库版本: " + connection.getMetaData().getDatabaseProductVersion());
|
|
// 检查表是否存在
|
DatabaseMetaData metaData = connection.getMetaData();
|
ResultSet tables = metaData.getTables(null, null, "t_carousel", null);
|
|
if (tables.next()) {
|
System.out.println("\n=== t_carousel 表存在 ===");
|
|
// 获取表结构
|
ResultSet columns = metaData.getColumns(null, null, "t_carousel", null);
|
System.out.println("\n=== 表字段信息 ===");
|
System.out.printf("%-20s %-15s %-10s %-10s %-15s%n",
|
"字段名", "数据类型", "长度", "可空", "默认值");
|
System.out.println("=".repeat(80));
|
|
List<String> actualColumns = new ArrayList<>();
|
while (columns.next()) {
|
String columnName = columns.getString("COLUMN_NAME");
|
String dataType = columns.getString("TYPE_NAME");
|
int columnSize = columns.getInt("COLUMN_SIZE");
|
String nullable = columns.getString("IS_NULLABLE");
|
String defaultValue = columns.getString("COLUMN_DEF");
|
|
actualColumns.add(columnName);
|
System.out.printf("%-20s %-15s %-10d %-10s %-15s%n",
|
columnName, dataType, columnSize, nullable,
|
defaultValue != null ? defaultValue : "NULL");
|
}
|
|
// 对比实体类期望的字段
|
System.out.println("\n=== 实体类字段对比 ===");
|
String[] expectedColumns = {
|
"id", "title", "content", "sort_order", "state",
|
"create_time", "update_time", "create_by", "update_by"
|
};
|
|
for (String expected : expectedColumns) {
|
if (actualColumns.contains(expected)) {
|
System.out.println("✓ " + expected + " - 存在");
|
} else {
|
System.out.println("✗ " + expected + " - 缺失");
|
}
|
}
|
|
// 检查是否有多余字段
|
System.out.println("\n=== 多余字段检查 ===");
|
for (String actual : actualColumns) {
|
boolean found = false;
|
for (String expected : expectedColumns) {
|
if (expected.equals(actual)) {
|
found = true;
|
break;
|
}
|
}
|
if (!found) {
|
System.out.println("! " + actual + " - 实体类中未定义");
|
}
|
}
|
|
} else {
|
System.out.println("\n=== t_carousel 表不存在 ===");
|
|
// 列出所有表
|
System.out.println("\n=== 数据库中的所有表 ===");
|
ResultSet allTables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
|
while (allTables.next()) {
|
String tableName = allTables.getString("TABLE_NAME");
|
if (tableName.toLowerCase().contains("carousel") ||
|
tableName.toLowerCase().contains("news") ||
|
tableName.toLowerCase().contains("banner")) {
|
System.out.println("- " + tableName);
|
}
|
}
|
}
|
|
} catch (SQLException e) {
|
System.err.println("数据库连接或查询失败: " + e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
|
@Test
|
public void testSimpleQuery() {
|
try (Connection connection = dataSource.getConnection()) {
|
// 尝试简单查询
|
String sql = "SELECT COUNT(*) as count FROM t_carousel WHERE state = 1";
|
try (PreparedStatement stmt = connection.prepareStatement(sql);
|
ResultSet rs = stmt.executeQuery()) {
|
|
if (rs.next()) {
|
int count = rs.getInt("count");
|
System.out.println("t_carousel 表中有效记录数: " + count);
|
}
|
}
|
} catch (SQLException e) {
|
System.err.println("查询失败: " + e.getMessage());
|
|
// 如果查询失败，尝试不带条件的查询
|
try (Connection connection = dataSource.getConnection()) {
|
String sql = "SELECT COUNT(*) as count FROM t_carousel";
|
try (PreparedStatement stmt = connection.prepareStatement(sql);
|
ResultSet rs = stmt.executeQuery()) {
|
|
if (rs.next()) {
|
int count = rs.getInt("count");
|
System.out.println("t_carousel 表总记录数: " + count);
|
}
|
}
|
} catch (SQLException e2) {
|
System.err.println("简单查询也失败: " + e2.getMessage());
|
}
|
}
|
}
|
}
|
