北屋教程网

同上一篇文章：

Java控制台利用HuTool实现数据库读取和HTTP调用笔记

这里改成数据库连接可配置

相关的执行脚本：

javac -cp .:hutool-all-5.8.38.jar:mysql-connector-j-8.0.31.jar:fastjson-1.2.83.jar CmdToolDbConn.java

java -cp .:hutool-all-5.8.38.jar:mysql-connector-j-8.0.31.jar:fastjson-1.2.83.jar CmdToolDbConn

相关代码：

import cn.hutool.core.io.FileUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;

import javax.sql.DataSource;
import java.io.File;
import java.nio.charset.Charset;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

public class CmdToolDbConn {
    private static final String DB_TYPE_MYSQL = "mysql";
    private static final String DB_TYPE_ORACLE = "oracle";
    private static final String DB_TYPE_SQLSERVER = "sqlserver";
    private static final String DB_TYPE_POSTGRESQL = "postgresql";

    private static final String DRIVER_MYSQL = "com.mysql.cj.jdbc.Driver";
    private static final String DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
    private static final String DRIVER_SQLSERVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String DRIVER_POSTGRESQL = "org.postgresql.Driver";

    private static final String URL_TEMPLATE_MYSQL = "jdbc:mysql://%s:%s/%s?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static final String URL_TEMPLATE_ORACLE = "jdbc:oracle:thin:@//%s:%s/%s";
    private static final String URL_TEMPLATE_SQLSERVER = "jdbc:sqlserver://%s:%s;databaseName=%s;encrypt=false";
    private static final String URL_TEMPLATE_POSTGRESQL = "jdbc:postgresql://%s:%s/%s";


    private static final String DEFAULT_PORT_MYSQL = "3306";
    private static final String DEFAULT_PORT_ORACLE = "1521";
    private static final String DEFAULT_PORT_SQLSERVER = "1433";
    private static final String DEFAULT_PORT_POSTGRESQL = "5432";
    private static final String DEFAULT_PORT_UNKNOWN = "0";

    private static final String DEFAULT_SQL_MYSQL = "SELECT NOW() AS current_time";
    private static final String DEFAULT_SQL_ORACLE = "SELECT SYSDATE AS current_time FROM DUAL";
    private static final String DEFAULT_SQL_SQLSERVER = "SELECT GETDATE() AS current_time";
    private static final String DEFAULT_SQL_POSTGRESQL = "SELECT CURRENT_TIMESTAMP AS current_time";
    private static final String DEFAULT_SQL_GENERAL = "SELECT 1";

    protected static String runJavaDir(){
        return "/app/tool-db-conn";
    }

    protected static String shJavac(){
        return "javac -cp .:hutool-all-5.8.38.jar:mysql-connector-j-8.0.31.jar:fastjson-1.2.83.jar CmdToolDbConn.java";
    }


    protected static String shJava(){
        return "java -cp .:hutool-all-5.8.38.jar:mysql-connector-j-8.0.31.jar:fastjson-1.2.83.jar CmdToolDbConn";
    }

    private static String getReadMe() {
        return "数据库链接测试\n支持的数据库类型: " + DB_TYPE_MYSQL + ", " + DB_TYPE_ORACLE + ", " + DB_TYPE_SQLSERVER + ", " + DB_TYPE_POSTGRESQL;
    }

    public static String getCmdInput(Scanner scanner, String inputTip) {
        return getCmdInput(scanner, inputTip, false, "");
    }

    public static String getCmdInput(Scanner scanner, String inputTip, boolean allowEmptyFlag, String emptyDefaultValue) {
        String cmd = null;
        while (true) {
            System.out.print(inputTip);
            cmd = scanner.nextLine();
            if (cmd.contains("\u0011")) {
                return null;
            }

            if (!cmd.trim().isEmpty()) {
                return cmd.trim();
            }

            if (allowEmptyFlag) {
                return emptyDefaultValue;
            }
        }
    }

    public static boolean cmdIsQuit(String data) {
        return data == null;
    }

    public static String getFileFullName(String fileName) {
        if (fileName == null || fileName.isEmpty()) {
            return "";
        }

        String rootName = System.getProperty("user.dir");
        if (rootName == null || rootName.isEmpty()) {
            return "";
        }

        String linkChar = File.separator;
        return rootName + linkChar + fileName;
    }

    public static String readFileFullText(String fileName) {
        String fileFullName = getFileFullName(fileName);
        if (!FileUtil.exist(fileFullName)) {
            System.out.println("文件不存在: " + fileFullName);
            return null;
        }

        return FileUtil.readString(fileFullName, Charset.defaultCharset());
    }


    protected static DataSource createDataSourceForJson(String dataBaseType, String serverAddress,
                                                        String port, String dataBaseName,
                                                        String user, String pwd) {
        if (dataBaseType == null || dataBaseType.trim().isEmpty()) {
            throw new IllegalArgumentException("数据库类型不能为空");
        }

        String driver = getDriverByDbType(dataBaseType);
        String url = getConnectionUrl(dataBaseType, serverAddress, port, dataBaseName);

        if (driver.isEmpty()) {
            throw new IllegalArgumentException("不支持的数据库类型: " + dataBaseType);
        }

        if (url.isEmpty()) {
            throw new IllegalArgumentException("无法生成数据库连接URL，请检查参数");
        }

        return new SimpleDataSource(url, user, pwd, driver);
    }


    private static String getDriverByDbType(String dataBaseType) {
        switch (dataBaseType.toLowerCase()) {
            case DB_TYPE_MYSQL:
                return DRIVER_MYSQL;
            case DB_TYPE_ORACLE:
                return DRIVER_ORACLE;
            case DB_TYPE_SQLSERVER:
                return DRIVER_SQLSERVER;
            case DB_TYPE_POSTGRESQL:
                return DRIVER_POSTGRESQL;
            default:
                return "";
        }
    }


    private static String getConnectionUrl(String dataBaseType, String serverAddress,
                                           String port, String dataBaseName) {
        String type = dataBaseType.toLowerCase();

        if (serverAddress == null || serverAddress.trim().isEmpty()) {
            throw new IllegalArgumentException("服务器地址不能为空");
        }

        if (port == null || port.trim().isEmpty()) {
            port = getDefaultPort(type);
            System.out.println("未指定端口，使用默认端口: " + port);
        }

        if (dataBaseName == null || dataBaseName.trim().isEmpty()) {
            throw new IllegalArgumentException("数据库名称不能为空");
        }

        switch (type) {
            case DB_TYPE_MYSQL:
                return String.format(URL_TEMPLATE_MYSQL, serverAddress, port, dataBaseName);
            case DB_TYPE_ORACLE:
                return String.format(URL_TEMPLATE_ORACLE, serverAddress, port, dataBaseName);
            case DB_TYPE_SQLSERVER:
                return String.format(URL_TEMPLATE_SQLSERVER, serverAddress, port, dataBaseName);
            case DB_TYPE_POSTGRESQL:
                return String.format(URL_TEMPLATE_POSTGRESQL, serverAddress, port, dataBaseName);
            default:
                return "";
        }
    }


    private static String getDefaultPort(String dbType) {
        switch (dbType) {
            case DB_TYPE_MYSQL:
                return DEFAULT_PORT_MYSQL;
            case DB_TYPE_ORACLE:
                return DEFAULT_PORT_ORACLE;
            case DB_TYPE_SQLSERVER:
                return DEFAULT_PORT_SQLSERVER;
            case DB_TYPE_POSTGRESQL:
                return DEFAULT_PORT_POSTGRESQL;
            default:
                return DEFAULT_PORT_UNKNOWN;
        }
    }


    public static void run(String[] args) throws Exception {
        System.out.println(getReadMe());
        Scanner scanner = new Scanner(System.in);

        String cmd = null;
        String fileName = null;

        cmd = getCmdInput(scanner, "请输入配置文件名: ");
        if (cmdIsQuit(cmd)) {
            System.out.println("用户取消操作");
            return;
        }
        fileName = cmd;

        String fileText = readFileFullText(fileName);
        if (fileText == null) {
            System.out.println("无法读取配置文件，程序退出");
            return;
        }

        try {
            JSONObject jsonData = JSON.parseObject(fileText);
            String dataBaseType = jsonData.getString("dataBaseType");
            String serverAddress = jsonData.getString("serverAddress");
            String port = jsonData.getString("port");
            String dataBaseName = jsonData.getString("dataBaseName");
            String user = jsonData.getString("user");
            String pwd = jsonData.getString("pwd");
            String sql = jsonData.getString("sql");

            if(pwd == null||pwd.isEmpty()){
                cmd = getCmdInput(scanner, "请输入数据库密码: ");
                if (cmdIsQuit(cmd)) {
                    System.out.println("用户取消操作");
                    return;
                }

                pwd = cmd;
            }

            if (sql == null || sql.trim().isEmpty()) {
                sql = getDefaultTestSql(dataBaseType);
            }

            System.out.println("dataBaseType: " + dataBaseType);
            System.out.println("serverAddress: " + serverAddress);
            System.out.println("port: " + port);
            System.out.println("dataBaseName: " + dataBaseName);
            System.out.println("user: " + user);
            System.out.println("pwd: " + pwd);
            System.out.println("sql: " + sql);


            cmd = getCmdInput(scanner, "确定操作？Y/N：");
            if (cmd == null || !cmd.equalsIgnoreCase("y")) {
                return;
            }


            DataSource dbDs = createDataSourceForJson(dataBaseType, serverAddress, port, dataBaseName, user, pwd);

            System.out.println("开始执行查询...");
            long startTime = System.currentTimeMillis();
            List<Entity> result = Db.use(dbDs).query(sql);
            long endTime = System.currentTimeMillis();

            System.out.println("查询完成，耗时 " + (endTime - startTime) + "ms");
            System.out.println("查询结果:");
            if (result != null && !result.isEmpty()) {
                System.out.println(result);
            } else {
                System.out.println("查询结果为空");
            }

        } catch (JSONException e) {
            System.err.println("JSON解析错误: " + e.getMessage());
            throw e;
        } catch (IllegalArgumentException e) {
            System.err.println("参数错误: " + e.getMessage());
            throw e;
        } catch (SQLException e) {
            System.err.println("数据库操作错误: " + e.getMessage());
            throw e;
        } finally {
            scanner.close();
        }
    }


    private static String getDefaultTestSql(String dataBaseType) {
        if (dataBaseType == null) {
            return DEFAULT_SQL_GENERAL;
        }

        switch (dataBaseType.toLowerCase()) {
            case DB_TYPE_MYSQL:
                return DEFAULT_SQL_MYSQL;
            case DB_TYPE_ORACLE:
                return DEFAULT_SQL_ORACLE;
            case DB_TYPE_SQLSERVER:
                return DEFAULT_SQL_SQLSERVER;
            case DB_TYPE_POSTGRESQL:
                return DEFAULT_SQL_POSTGRESQL;
            default:
                return DEFAULT_SQL_GENERAL;
        }
    }

    public static void main(String[] args) {
        try {
            run(args);
            System.out.println("操作完成");
        } catch (Exception e) {
            System.err.println("程序执行失败: " + e.getMessage());
        }
    }
}

执行效果：