解析mysql的spring.datasource.url获取host，端口，数据库名，获取表里的 字段名，字段类型，字段注释

解析mysql的spring.datasource.url获取host，端口，数据库名，获取表里的 字段名，字段类型，字段注释

做项目做了一个功能：需要展示一个表中的所有信息：字段名，字段类型，字段注释。
需要解析mysql的url，获取到host,port,dbName信息，然后调用 java.sql.Connection 里的api来进行操作

application.properties

# mysql配置信息
spring.datasource.url=jdbc:mysql://localhost:3306/work?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=

解析此 spring.datasource.url 想要获取结果为：

host=jdbc:mysql://localhost
port=3306
dbName=work

直接上代码

JdbcController

package com.zjy.knife4j.controller;

import com.zjy.knife4j.model.ResultBO;
import com.zjy.knife4j.utils.JdbcUtils;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RequestMapping("/jdbc")
@RestController
public class JdbcController {

    /**日志对象*/
    private static final Logger logger = LoggerFactory.getLogger(JdbcController.class);

    @Autowired
    private JdbcUtils jdbcUtils;

    @ApiOperation(value = "获取数据库表信息接口", notes = "获取数据库表信息接口")
    @PostMapping("/database/info")
    public ResultBO info(@RequestParam(value = "tableName",required = false) String tableName) throws SQLException {

        // 获取dbName
        Map<String, String> dbInfo = jdbcUtils.getDBInfo();
        String dbName = dbInfo.get("dbName");
        String url = dbInfo.get("url");
        String username = dbInfo.get("userName");
        String password = dbInfo.get("passWord");

        ResultBO results = new ResultBO();
        String sql = "SELECT column_name,column_comment,data_type FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = '" + dbName + "' and TABLE_NAME = '" + tableName + "'";
        logger.info("查询到的sql为:{}", sql);

        Connection conn = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Map<String, String>> list = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, String> map = new HashMap<>();
            map.put("column_name", resultSet.getString("column_name"));
            map.put("column_comment", resultSet.getString("column_comment"));
            map.put("data_type", resultSet.getString("data_type"));

            list.add(map);
        }

        results.setCode(200);
        results.setContent(list);
        results.setMsg("调用测试接口成功！");
        results.setSucceed(true);

        return results;
    }

    @ApiOperation(value = "获取数据库url信息接口", notes = "获取数据库url信息接口")
    @PostMapping("/url/info")
    public ResultBO info(){
        ResultBO results = new ResultBO();

        Map<String, String> dbInfo = jdbcUtils.getDBInfo();
        String host = dbInfo.get("host");
        String port = dbInfo.get("port");
        String dbName = dbInfo.get("dbName");
        String userName = dbInfo.get("userName");
        String passWord = dbInfo.get("passWord");
        String url = dbInfo.get("url");
        logger.info("链接地址为:{}, 端口为:{}, 数据库名为:{}, 用户名为:{}, 密码为:{}, url为:{}", host, port, dbName, userName, passWord, url);
        results.setCode(200);
        results.setContent(dbInfo);
        results.setMsg("调用测试接口成功！");
        results.setSucceed(true);

        logger.info("调用测试接口成功");

        return results;
    }
}

JdbcUtils

这是里解析 spring.datasource.url 的核心方法

package com.zjy.knife4j.utils;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.util.HashMap;

/**
 * @author zhoujunyi10
 */
@Component
public class JdbcUtils {

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    public HashMap<String, String> getDBInfo() {
        String[] split = url.split(":");
        String host = String.format("%s:%s:%s", split[0], split[1], split[2]);
        String[] portSplit = split[3].split("/");
        String port = portSplit[0];

        String[] databaseSplit = portSplit[1].split("\\?");
        String dbName = databaseSplit[0];
        HashMap<String, String> result = new HashMap<>();
        result.put("url",url);
        result.put("host",host);
        result.put("port",port);
        result.put("dbName",dbName);
        result.put("userName",username);
        result.put("passWord",password);

        return result;
    }
}

开始测试：
启动项目，访问swagger。

数据库表字段：

响应结果：

打印的json：

{
  "content": [
    {
      "column_name": "menu_id",
      "column_comment": "主键id",
      "data_type": "bigint"
    },
    {
      "column_name": "code",
      "column_comment": "菜单编号",
      "data_type": "varchar"
    },
    {
      "column_name": "pcode",
      "column_comment": "菜单父编号",
      "data_type": "varchar"
    },
    {
      "column_name": "pcodes",
      "column_comment": "当前菜单的所有父菜单编号",
      "data_type": "varchar"
    },
    {
      "column_name": "name",
      "column_comment": "菜单名称",
      "data_type": "varchar"
    },
    {
      "column_name": "icon",
      "column_comment": "菜单图标",
      "data_type": "varchar"
    },
    {
      "column_name": "url",
      "column_comment": "url地址",
      "data_type": "varchar"
    },
    {
      "column_name": "sort",
      "column_comment": "菜单排序号",
      "data_type": "int"
    },
    {
      "column_name": "levels",
      "column_comment": "菜单层级",
      "data_type": "int"
    },
    {
      "column_name": "menu_flag",
      "column_comment": "是否是菜单（1：是  0：不是）",
      "data_type": "varchar"
    },
    {
      "column_name": "description",
      "column_comment": "备注",
      "data_type": "varchar"
    },
    {
      "column_name": "status",
      "column_comment": "菜单状态 :  1:启用   0:不启用",
      "data_type": "varchar"
    },
    {
      "column_name": "open_flag",
      "column_comment": "是否打开:    1:打开   0:不打开",
      "data_type": "int"
    },
    {
      "column_name": "menutype",
      "column_comment": "",
      "data_type": "varchar"
    },
    {
      "column_name": "new_page_flag",
      "column_comment": "",
      "data_type": "varchar"
    },
    {
      "column_name": "system_type",
      "column_comment": "系统分类(字典)",
      "data_type": "varchar"
    },
    {
      "column_name": "create_time",
      "column_comment": "",
      "data_type": "datetime"
    },
    {
      "column_name": "update_time",
      "column_comment": "",
      "data_type": "datetime"
    },
    {
      "column_name": "create_user",
      "column_comment": "",
      "data_type": "bigint"
    },
    {
      "column_name": "update_user",
      "column_comment": "",
      "data_type": "bigint"
    },
    {
      "column_name": "tenant",
      "column_comment": "租户",
      "data_type": "varchar"
    }
  ],
  "succeed": true,
  "code": 200,
  "msg": "调用测试接口成功！"
}

控制台打印：

测试OK！
欢迎大神指导,可以留言交流!

======================
本人原创文章,转载注明出入!

=================