将项目数据库表结构导出Excel

前言

因为项目文档一直疏于维护，导致数据结构文档内容不完整，comment缺失，有些表甚至在文档中找不到。无奈之下，只能自己重新做一个文档。让我先来看看库里究竟有多少表。

使用工具

使用了mybatis作为数据映射工具
使用了easyExcel作为excel导出工具

上代码

本来想传到git上的，不过这个东西我用的机会也许不多，况且无用的项目已经够多了……
config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
		PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
		"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<settings>
		<!-- 打印查询语句 -->
		<setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>
	
	<typeAliases>
		<package name="cn.Kevin.dailyTest.entity"/>
	</typeAliases>

	<!-- 和Spring整合后environment配置都会被干掉 -->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理，目前由mybatis来管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池，目前由mybatis来管理 -->
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
				<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
				<property name="username" value="system" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="mybatis/dataStruct.xml" />
	</mappers>
</configuration>

dataStruct.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
		PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
		"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="DataStructDao">
	<select id="getDataStruct" parameterType="String" resultType="DataStruct">
		SELECT t.COLUMN_NAME "name",
               t.DATA_TYPE    "type",
               t.DATA_LENGTH  "length",
               DECODE(t.NULLABLE,'Y','是','N','否','是')     "nullAble",
               NVL(tt.COMMENTS,'null')    "comments"
          FROM USER_TAb_COLS t, USER_COL_COMMENTS tt
         WHERE t.TABLE_NAME = tt.TABLE_NAME
           AND t.COLUMN_NAME = tt.COLUMN_NAME
           AND t.TABLE_NAME = UPPER(#{tableName})
         ORDER BY t.COLUMN_ID ASC
	</select>

	<select id="getTables" resultType="java.lang.String">
		select table_name from all_tables where owner = 'system'
	</select>
</mapper>

这里是2个mybatis的配置文件。没啥好解释的。

DataStruct.java

package cn.Kevin.dailyTest.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

public class DataStruct {
	@ExcelProperty("字段名")
	@ColumnWidth(30)
	private String name;
	@ExcelProperty("数据类型")
	@ColumnWidth(15)
	private String type;
	@ExcelProperty("长度")
	@ColumnWidth(15)
	private String length;
	@ExcelProperty("允许为空")
	@ColumnWidth(15)
	private String nullAble;
	@ExcelProperty("注释")
	@ColumnWidth(50)
	private String comments;

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getLength() {
		return length;
	}

	public void setLength(String length) {
		this.length = length;
	}

	public String getNullAble() {
		return nullAble;
	}

	public void setNullAble(String nullAble) {
		this.nullAble = nullAble;
	}

	public String getComments() {
		return comments;
	}

	public void setComments(String comments) {
		this.comments = comments;
	}
}

实体类
BaseDao.java

package cn.Kevin.dailyTest.dao;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;

public class BaseDao {
	public SqlSession getSqlSession(){
		SqlSessionFactory sqlSessionFactory = null;
		try {
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis/config.xml"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		SqlSession sqlSession = sqlSessionFactory.openSession();
		return sqlSession;
	}
}

DataStructDao.java

package cn.Kevin.dailyTest.dao;

import cn.Kevin.dailyTest.entity.DataStruct;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class DataStructDao extends BaseDao {
	SqlSession sqlSession = super.getSqlSession();
	private List<String> tables = null;
	private List<DataStruct> structs = null;

	public List<String> getTables() {
		tables = sqlSession.selectList("DataStructDao.getTables");
		return tables;
	}

	public List<DataStruct> getStructs(String tableName) {
		structs = sqlSession.selectList("DataStructDao.getDataStruct",tableName);
		return structs;
	}
}

TestMain.java

package cn.Kevin.dailyTest;

import cn.Kevin.dailyTest.dao.DataStructDao;
import cn.Kevin.dailyTest.entity.DataStruct;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.List;
import java.util.regex.Pattern;

public class TestMain {
	public static void main(String[] args) throws Exception {
		String fileName = "E:\\数据结构v3.0.0.xlsx";

		//处理表
		DataStructDao dataStructDao = new DataStructDao();
		List<String> tables = dataStructDao.getTables();
		Pattern pattern = Pattern.compile(".*[\\d]+");
		for (int i = tables.size() -1; i >= 0; i--) {
			if (pattern.matcher(tables.get(i)).matches()) {
				String table = tables.remove(i);
				System.out.println(table);
			}
		}

		//导出excel
		ExcelWriter excelWriter = null;
		excelWriter = EasyExcel.write(fileName).build();
		for (int i = 0; i < tables.size(); i++) {
			WriteSheet writeSheet = EasyExcel.writerSheet(i, tables.get(i)).head(DataStruct.class).build();
			List<DataStruct> dataStructs = dataStructDao.getStructs(tables.get(i));
			excelWriter.write(dataStructs, writeSheet);
		}
		if (excelWriter != null) {
			excelWriter.finish();
		}
	}
}

先查询出数据库里该用户名下所有的表，去掉不需要归档的表（备份数据时会用原表名+当日日期作为表名）。然后进行数据导出。

结束语

就算是数据库中，也没维护注释啊。我再去看看代码里有没有注释吧。再找不到注释只能去逻辑里判断了。我要疯了，这项目没法维护了！