数据库管理/监控

于 2020-08-22 12:07:07 发布
阅读量248 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库管理/监控 数据库文档生成
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_37664223/article/details/108166060
版权
对于各个部门的数据库对接，以及大量的数据库表，我们想了解数据库的表设计，字段含义，不想通过navicate来查看，而是通过文档或者可视化界面来实时动态来查看，推荐两款开源项目，来满足你们的要求:

一：cn.smallbun.screw 生成方便查看的html文档: 参考文档.

1：POM jar包引用

    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example.zdp</groupId>
    <artifactId>practice</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>practice</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <lastVersion>1.0.4</lastVersion>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>cn.smallbun.screw</groupId>
            <artifactId>screw-core</artifactId>
            <version>${lastVersion}</version>
        </dependency>
    <!--    <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>-->

       <!-- <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
    </dependencies>
2、代码示例
package com.example.zdp.practice.config;

import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineFileType;
import cn.smallbun.screw.core.engine.EngineTemplateType;
import cn.smallbun.screw.core.execute.DocumentationExecute;
import cn.smallbun.screw.core.process.ProcessConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.ArrayList;

/**
 * @ClassName ScrewConfig
 * @Author dongping
 * @Date 2020/8/18 16:12
 * @Description ScrewConfig
 * @Version 1.0
 */
@Component
public class ScrewConfig {
    public static void main(String[] args) {

        new ScrewConfig().documentGeneration("a","");
    }
    /**
     * 文档生成
     */
     public void  documentGeneration(String url,String name) {
        //数据源
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
        hikariConfig.setJdbcUrl("jdbc:mysql://");
        hikariConfig.setUsername("root");
        hikariConfig.setPassword("");
        //设置可以获取tables remarks信息
        hikariConfig.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig.setMinimumIdle(2);
        hikariConfig.setMaximumPoolSize(5);
        DataSource dataSource = new HikariDataSource(hikariConfig);
       /*  String staticPath = this.getClass().getClassLoader().getResource("static").getFile();
         System.out.println(staticPath);*/
        //生成配置
        EngineConfig engineConfig = EngineConfig.builder()
                //生成文件路径
                .fileOutputDir(url)
                //打开目录
                .openOutputDir(true)
                //文件类型
                .fileType(EngineFileType.HTML)
                //生成模板实现
                .produceType(EngineTemplateType.freemarker)
                //自定义文件名称
                .fileName(name).build();

        //忽略表
        ArrayList<String> ignoreTableName = new ArrayList<>();
        ignoreTableName.add("test_user");
        ignoreTableName.add("test_group");
        //忽略表前缀
        ArrayList<String> ignorePrefix = new ArrayList<>();
        ignorePrefix.add("test_");
        //忽略表后缀
        ArrayList<String> ignoreSuffix = new ArrayList<>();
        ignoreSuffix.add("_test");
        ProcessConfig processConfig = ProcessConfig.builder()
                //指定生成逻辑、当存在指定表、指定表前缀、指定表后缀时，将生成指定表，其余表不生成、并跳过忽略表配置
                //根据名称指定表生成
                .designatedTableName(new ArrayList<>())
                //根据表前缀生成
                .designatedTablePrefix(new ArrayList<>())
                //根据表后缀生成
                .designatedTableSuffix(new ArrayList<>())
                //忽略表名
                .ignoreTableName(ignoreTableName)
                //忽略表前缀
                .ignoreTablePrefix(ignorePrefix)
                //忽略表后缀
                .ignoreTableSuffix(ignoreSuffix).build();
        //配置
        Configuration config = Configuration.builder()
                //版本
                .version("1.0.0")
                //描述
                .description("数据库设计文档生成")
                //数据源
                .dataSource(dataSource)
                //生成配置
                .engineConfig(engineConfig)
                //生成配置
                .produceConfig(processConfig)
                .build();
        //执行生成
        new DocumentationExecute(config).execute();
    }
}

二 可视化界面查看 开源项目地址.

我们把项目打成jar包扔到私服，或者打包到本地仓库 maven install 命令，然后新启一个springboot 项目引入该jar包，通过简单的数据库源配置即可实现前端的直接查看。
pom 引入 jar包
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.zyplayer</groupId>
	<artifactId>zyplayer-doc-db-demo</artifactId>
	<packaging>jar</packaging>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.9.RELEASE</version>
	</parent>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jta-atomikos</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>2.6</version>
		</dependency>
		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.10</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
		<dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.6</version>
		</dependency>
		<!--
			zyplayer-doc-db文档工具，没有放到中央仓库，需要用的话可以自己打包到自己的私仓来使用
			如果后期使用的人多也可以放下中央仓库╮(￣▽￣)╭
		-->
		<dependency>
			<groupId>com.zyplayer</groupId>
			<artifactId>zyplayer-doc-db</artifactId>
			<version>1.0.1-SNAPSHOT</version>
		</dependency>
	</dependencies>
</project>
代码示例
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * mybatis数据库配置，配置有mysql和sqlsever两种数据库的例子
 * 
 */
@Configuration
public class MybatisConfig {

	/**
	 * mysql数据库配置
	 */
	@Configuration
	@EnableTransactionManagement
	static class UserInfoMybatisDbConfig {

		@Value("${mysql.datasource.driverClassName}")
		private String driverClassName;
		@Value("${mysql.datasource.url}")
		private String datasourceUrl;
		@Value("${mysql.datasource.username}")
		private String datasourceUsername;
		@Value("${mysql.datasource.password}")
		private String datasourcePassword;

		@Primary
		@Bean(name = "userInfoDatasource")
		public DataSource userInfoDatasource() {
			Properties xaProperties = new Properties();
			xaProperties.setProperty("driverClassName", driverClassName);
			xaProperties.setProperty("url", datasourceUrl);
			xaProperties.setProperty("username", datasourceUsername);
			xaProperties.setProperty("password", datasourcePassword);
			xaProperties.setProperty("maxActive", "500");
			xaProperties.setProperty("testOnBorrow", "true");
			xaProperties.setProperty("testWhileIdle", "true");
			xaProperties.setProperty("validationQuery", "select 'x'");

			AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
			xaDataSource.setXaProperties(xaProperties);
			xaDataSource.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
			xaDataSource.setUniqueResourceName("userInfoDatasource");
			xaDataSource.setMaxPoolSize(500);
			xaDataSource.setMinPoolSize(1);
			xaDataSource.setMaxLifetime(60);
			return xaDataSource;
		}

		@Primary
		@Bean(name = "userInfoSqlSessionFactory")
		public SqlSessionFactoryBean userInfoSqlSessionFactory() throws Exception {
			SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
			sqlSessionFactoryBean.setDataSource(userInfoDatasource());
			return sqlSessionFactoryBean;
		}
	}

package com.zyplayer.doc.db.demo.config;

import java.util.LinkedList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;

import com.zyplayer.doc.db.framework.configuration.EnableDocDb;
import com.zyplayer.doc.db.framework.db.bean.DatabaseRegistrationBean;

/**
 * bean声明注入和开启注解
 * 
 */
@Component
@EnableDocDb// 开启文档注解
@Configuration
public class WebMvcConfig {
	
	// mysql数据源例子
	@Resource(name = "userInfoDatasource")
	private DataSource userInfoDatasource;
	// sqlsever数据源例子
/*	@Resource(name = "orderInfoDatasource")
	private DataSource orderInfoDatasource;*/

	/**
	 * 声明文档所需的bean
	 * 
	 * @return
	 */
	@Bean
	public DatabaseRegistrationBean databaseRegistrationBean() {
		DatabaseRegistrationBean bean = new DatabaseRegistrationBean();
		List<DataSource> dataSourceList = new LinkedList();
		dataSourceList.add(userInfoDatasource);
//		dataSourceList.add(orderInfoDatasource);
		bean.setDataSourceList(dataSourceList);
		return bean;
	}
}
application.properties 的配置文件
mysql.datasource.driverClassName=com.mysql.jdbc.Driver
mysql.datasource.url=jdbc:mysql://useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&autoReconnect=true
mysql.datasource.username=root
mysql.datasource.password=***

demo 地址链接.

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值