import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Test{
private final static String fileName = "D:\MyDocuments\wangyong77\桌面\topTable3.xlsx";
private final static String driver = "com.mysql.cj.jdbc.Driver";
private final static String ip = "10.130.201.237:";
private final static String user = "tkbus";
private final static String password = "tkbus123";
private final static String mydata = "tkbus";
private static Connection conn;
public static void main(String[] args) {
DBinit ();
getTableInfo ();
DBclose ();
}
public static void getTableInfo() {
try {
//获取数据库的元数据
DatabaseMetaData dbMetaData = conn.getMetaData ();
System.out.println ("---------------获取" + conn.getCatalog () + "库的所有表名----------------");
//从元数据中获取到所有的表名
ResultSet rs = dbMetaData.getTables (conn.getCatalog (),null,null,new String[]{"TABLE"});
//存放所有表名
List<String> tableNames = new ArrayList<> ();
//存放当前表的字段
List<String> fields = new ArrayList<> ();
//存放当前表的字段类型
List<String> fieldstype = new ArrayList<> ();
//存放当前表的字段属性
List<String> commits = new ArrayList<> ();
List<TableData> tableList = new ArrayList ();
Map<Integer,List<FieldData>> map = new HashMap<> ();
TableData table;
while (rs.next ()) {
// System.out.println("表名: "+rs.getString("TABLE_NAME"));
// System.out.println("表类型: "+rs.getString("TABLE_TYPE"));
// System.out.println("表所属数据库: "+rs.getString("TABLE_CAT"));
// System.out.println("表所属用户名: "+rs.getString("TABLE_SCHEM"));
// System.out.println("表备注: "+rs.getString("REMARKS"));
if (rs.getString ("TABLE_NAME").contains ("copy") || HasDigit (rs.getString ("TABLE_NAME"))) {
continue;
}
tableNames.add (rs.getString ("TABLE_NAME"));
table = new TableData ();
table.setTableName (rs.getString ("TABLE_NAME"));
table.setRemark (rs.getString ("REMARKS"));
tableList.add (table);
}
//查询每个表的字段
for (int i = 0;i < tableNames.size ();i++) {
List<FieldData> filedlist = new ArrayList ();
if (tableNames.get (i).contains ("copy") || HasDigit (tableNames.get (i))) {
continue;
}
String sql = "select * from " + tableNames.get (i);
System.out.println ("表开始" + tableNames.get (i));
PreparedStatement ps = conn.prepareStatement (sql);
ResultSet rstable = ps.executeQuery ();
//结果集元数据
ResultSetMetaData meta = rstable.getMetaData ();
//表列数量
int columeCount = meta.getColumnCount ();
FieldData data;
for (int k = 1;k <= columeCount;k++) {
data = new FieldData ();
data.setTableName (tableNames.get (i));
data.setField (meta.getColumnName (k));
data.setFieldType (meta.getColumnTypeName (k));
filedlist.add (data);
fields.add (meta.getColumnName (k));
fieldstype.add (meta.getColumnTypeName (k));
}
// System.out.println("表"+tableNames.get(i)+"字段: "+fields);
// System.out.println("表"+tableNames.get(i)+"字段类型: "+fieldstype);
fields.clear ();
ResultSet rs1 = rs = ps.executeQuery ("show full columns from " + tableNames.get (i));
while (rs1.next ()) {
commits.add (rs.getString ("Comment"));
}
// System.out.println("表"+tableNames.get(i)+"字段备注类型: "+commits);
for (int j = 0;j < filedlist.size ();j++) {
filedlist.get (j).setFieldCommit (commits.get (j));
}
map.put (i,filedlist);
commits.clear ();
System.out.println ("表完成" + tableNames.get (i));
}
// createTableExcel(tableList);
createExcel (map);
} catch (Exception e) {
e.printStackTrace ();
}
}
public static void DBinit() {
//驱动程序名
Properties props = new Properties ();
props.put ("user",user);
props.put ("password",password);
props.put ("useInformationSchema","true"); //表注释
//不同端口号
String[] db_url_port = {"3306"};
//数据库服务器
String dbServer;
for (String db_port : db_url_port) {
dbServer = ip + db_port;
//URL指向要访问的数据库名mydata
String url = "jdbc:mysql://" + dbServer + "/" + mydata;
try {
Class.forName (driver);
//声明Connection对象
conn = DriverManager.getConnection (url,props);
if (!conn.isClosed ()) {
System.out.println ("当前访问数据库端口号为" + db_port + "，数据库连接成功！");
break;
}
} catch (Exception e) {
System.out.println ("当前访问数据库端口号为" + db_port + "，数据库连接失败！");
continue;
}
}
}
public static void DBclose() {
try {
conn.close ();
} catch (SQLException e) {
System.out.println ("数据关闭异常");
e.printStackTrace ();
}
}
/**
* 生成表内容详细信息
* @param map
*/
public static void createExcel(Map<Integer,List<FieldData>> map) {
System.out.println ("开始写入excel");
ExcelWriter excelWriter = EasyExcel.write (fileName).build ();
WriteSheet writeSheet;
//map根据key排序
for (Map.Entry<Integer,List<FieldData>> map1 : map.entrySet ()) {
// EasyExcel.write(fileName, FieldData.class).sheet(map1.getKey()).doWrite(map1.getValue());
// 每次都要创建writeSheet 这里注意必须指定sheetNo。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
writeSheet = EasyExcel.writerSheet (map1.getKey (),map1.getValue ().get (0).getTableName ()).head (FieldData.class).build ();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
excelWriter.write (map1.getValue (),writeSheet);
}
excelWriter.finish ();
System.out.println ("excel生成完毕");
}
/**
* 生成表信息
* @param tableList
*/
public static void createTableExcel(List<TableData> tableList) {
System.out.println ("开始写入Tableexcel");
EasyExcel.write (fileName,TableData.class).sheet ("top表").doWrite (tableList);
System.out.println ("Tableexcel生成完毕");
}
public static boolean HasDigit(String content) {
boolean flag = false;
Pattern p = Pattern.compile (".*\d+.*");
Matcher m = p.matcher (content);
if (m.matches ()) {
flag = true;
}
return flag;
}
}
