2020-12-15

JAVA 通过JDBC测试连接数据库【mysql，dm，orcale，hive】

controller

/**
     * 测试连接
     */
    @OperationLogDetail(detail = "数据资产管理-数据源表，参数：[{{url}},{{user}},{{pwd}}]", module = "dataAsset", level = Level.INFO, operationUnit = "数据资产管理-数据源表", operationType = OperationType.FIND)
    @GetMapping("/test")
    @ApiOperation(value = "测试连接", notes = "测试连接")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "url", required = true, value = "url", paramType = "query", dataType = "string")
            , @ApiImplicitParam(name = "user", required = true, value = "用户", paramType = "query", dataType = "string")
            , @ApiImplicitParam(name = "pwd", required = true, value = "密码", paramType = "query", dataType = "string")
    })
    public ResultData testDB(@RequestParam(value = "url", required = true) String url
            , @RequestParam(value = "user", required = true) String user
            , @RequestParam(value = "pwd", required = true) String pwd) {

        if (StringUtils.isBlank(url) || StringUtils.isBlank(user) || StringUtils.isBlank(pwd)) {
            return ResultData.error(ResultCode.E_PARAM_NOT_NULL.msg());
        }
        try {
            String res = dsDatabasePoolService.test_connect(url, user, pwd);
            return ResultData.success(res);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultData.error(ResultCode.E_CONFIG_NOT_EXISTS.msg());
        }
    }

service

String test_connect( String url,String user,String pwd ) throws Exception;

serviceImpl

 public String test_connect(String url, String user, String pwd) throws Exception {
        String res = "false";
        res = DBConnectUtil.testConnect(url, user, pwd).toString();
        return res.toString();
    }

DBConnectUtil

public static Boolean testConnect(String url, String user, String pwd) throws Exception {
        //不同的数据库有不同的驱动
        Connection con = null;
        //返回值默认为false
        Boolean msg = Boolean.FALSE;
        //加载驱动
        String driverClass = "";
        if (url.contains("mysql")) {
            driverClass = "com.mysql.jdbc.Driver";
        }
        if (url.contains("oracle")) {
            driverClass = "oracle.jdbc.driver.OracleDriver";
        }
        if (url.contains("dm")) {
            driverClass = "dm.jdbc.driver.DmDriver";
        }
        if (url.contains("hive")) {
            driverClass = "org.apache.hive.jdbc.HiveDriver";
        }
        if (StringUtils.isBlank(driverClass)) {
            return null;
        }
        //动态加载到方法区
        Class.forName(driverClass);
        // 设置 配置数据
        // 1.url(数据看服务器的ip地址 数据库服务端口号 数据库实例)
        // 2.user
        // 3.password
        if (con == null) {
            con = DriverManager.getConnection(url, user, pwd);
            if (con.isClosed()) {
                con = DriverManager.getConnection(url, user, pwd);
            }
        }
        // 开始连接数据库
        if (con != null) {
            DatabaseMetaData metaData = con.getMetaData();
            if (metaData != null)
                msg = Boolean.TRUE;
        }
        con.close();
        return msg;