import java.nio.charset.StandardCharsets;
|
import java.nio.file.Files;
|
import java.nio.file.Path;
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Scanner;
|
|
/**
|
* 数据库直连小工具。用法见 printUsage()。
|
* 运行：bash sql/db.sh "SELECT 1"
|
*/
|
public class DbTool {
|
|
private static final String DEFAULT_URL =
|
"jdbc:mysql://localhost:3306/product-inventory-management-rbhb"
|
+ "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull"
|
+ "&serverTimezone=GMT%2B8&sslMode=DISABLED&allowPublicKeyRetrieval=true";
|
private static final String DEFAULT_USER = "root";
|
private static final String DEFAULT_PASS = "123456";
|
|
public static void main(String[] args) throws Exception {
|
String url = DEFAULT_URL;
|
String user = DEFAULT_USER;
|
String pass = DEFAULT_PASS;
|
String sqlText = null;
|
Path scriptFile = null;
|
|
List<String> positional = new ArrayList<>();
|
for (int i = 0; i < args.length; i++) {
|
String arg = args[i];
|
switch (arg) {
|
case "-url" -> url = args[++i];
|
case "-user" -> user = args[++i];
|
case "-pass" -> pass = args[++i];
|
case "-f" -> scriptFile = Path.of(args[++i]);
|
case "-h", "--help" -> {
|
printUsage();
|
return;
|
}
|
default -> positional.add(arg);
|
}
|
}
|
|
if (scriptFile != null) {
|
sqlText = Files.readString(scriptFile, StandardCharsets.UTF_8);
|
} else if (!positional.isEmpty()) {
|
sqlText = String.join(" ", positional);
|
} else if (System.console() != null) {
|
printUsage();
|
return;
|
} else {
|
Scanner scanner = new Scanner(System.in, StandardCharsets.UTF_8).useDelimiter("\\A");
|
sqlText = scanner.hasNext() ? scanner.next() : null;
|
}
|
|
if (sqlText == null || sqlText.isBlank()) {
|
printUsage();
|
return;
|
}
|
|
List<String> statements = splitStatements(sqlText);
|
if (statements.isEmpty()) {
|
printUsage();
|
return;
|
}
|
|
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
|
for (String sql : statements) {
|
runOne(conn, sql);
|
}
|
}
|
}
|
|
private static void runOne(Connection conn, String sql) throws Exception {
|
long start = System.currentTimeMillis();
|
try (Statement stmt = conn.createStatement()) {
|
boolean hasResultSet = stmt.execute(sql);
|
long cost = System.currentTimeMillis() - start;
|
if (hasResultSet) {
|
try (ResultSet rs = stmt.getResultSet()) {
|
printResultSet(rs);
|
}
|
System.out.println("耗时 " + cost + " ms");
|
} else {
|
System.out.println("受影响行数: " + stmt.getUpdateCount() + " 耗时 " + cost + " ms");
|
}
|
} catch (Exception e) {
|
System.out.println("执行失败: " + e.getMessage());
|
System.out.println("SQL: " + sql);
|
throw e;
|
}
|
}
|
|
private static void printResultSet(ResultSet rs) throws Exception {
|
ResultSetMetaData meta = rs.getMetaData();
|
int columnCount = meta.getColumnCount();
|
String[] headers = new String[columnCount];
|
int[] widths = new int[columnCount];
|
for (int i = 0; i < columnCount; i++) {
|
headers[i] = meta.getColumnLabel(i + 1);
|
widths[i] = displayWidth(headers[i]);
|
}
|
|
List<String[]> rows = new ArrayList<>();
|
while (rs.next()) {
|
String[] row = new String[columnCount];
|
for (int i = 0; i < columnCount; i++) {
|
Object value = rs.getObject(i + 1);
|
row[i] = value == null ? "NULL" : String.valueOf(value);
|
widths[i] = Math.max(widths[i], displayWidth(row[i]));
|
}
|
rows.add(row);
|
}
|
|
StringBuilder separator = new StringBuilder();
|
for (int width : widths) {
|
separator.append('-').append("-".repeat(width)).append('-');
|
}
|
System.out.println(separator);
|
System.out.println(joinRow(headers, widths));
|
System.out.println(separator);
|
for (String[] row : rows) {
|
System.out.println(joinRow(row, widths));
|
}
|
System.out.println(separator);
|
System.out.println("共 " + rows.size() + " 行");
|
}
|
|
private static String joinRow(String[] cells, int[] widths) {
|
StringBuilder sb = new StringBuilder();
|
for (int i = 0; i < cells.length; i++) {
|
sb.append("| ").append(cells[i]);
|
sb.append(" ".repeat(Math.max(0, widths[i] - displayWidth(cells[i]) + 1)));
|
}
|
return sb.append('|').toString();
|
}
|
|
/**
|
* 全角字符占两个终端宽度，按 2 计算以保证表格对齐。
|
*/
|
private static int displayWidth(String text) {
|
int width = 0;
|
for (int i = 0; i < text.length(); i++) {
|
width += text.charAt(i) > 0x2E80 ? 2 : 1;
|
}
|
return width;
|
}
|
|
/**
|
* 按分号切分语句，忽略空语句与纯注释行。
|
* 简单实现：不支持字符串字面量中包含分号的极端场景。
|
*/
|
private static List<String> splitStatements(String sqlText) {
|
List<String> statements = new ArrayList<>();
|
for (String part : sqlText.split(";")) {
|
String trimmed = part.trim();
|
if (trimmed.isEmpty() || isCommentOnly(trimmed)) {
|
continue;
|
}
|
statements.add(trimmed);
|
}
|
return statements;
|
}
|
|
private static boolean isCommentOnly(String sql) {
|
for (String line : sql.split("\\R")) {
|
String stripped = line.trim();
|
if (!stripped.isEmpty() && !stripped.startsWith("--") && !stripped.startsWith("#")) {
|
return false;
|
}
|
}
|
return true;
|
}
|
|
private static void printUsage() {
|
System.out.println("""
|
数据库直连工具
|
|
用法:
|
bash sql/db.sh "SQL语句"
|
bash sql/db.sh -f 脚本.sql
|
echo "SQL语句" | bash sql/db.sh
|
|
可覆盖连接参数:
|
-url jdbc:mysql://host:3306/db?... -user root -pass 123456
|
""");
|
}
|
}
|
