导出数据库设计结构为Excel表格，数据库字段操作

第一步：
package DB;

import java.util.HashMap;
import java.util.Map;
/**
* 数据库字段数据类型与数字之间的对应关系
* @author lxzqz
*
*/
public class Types {
protected final static Map<Integer, String> MAP = new HashMap<Integer, String>();
public static final int ARRAY = 2003;
public static final int BIGINT = -5;
public static final int BINARY = -2;
public static final int BIT = -7;
public static final int BLOB = 2004;
public static final int BOOLEAN = 16;
public static final int CHAR = 1;
public static final int CLOB = 2005;
public static final int DATALINK = 70;
public static final int DATE = 91;
public static final int DECIMAL = 3;
public static final int DISTINCT = 2001;
public static final int DOUBLE = 8;
public static final int FLOAT = 6;
public static final int INTEGER = 4;
public static final int JAVA_OBJECT = 2000;
public static final int LONGNVARCHAR = -16;
public static final int LONGVARBINARY = -4;
public static final int LONGVARCHAR = -1;
public static final int NCHAR = -15;
public static final int NCLOB = 2011;
public static final int NULL = 0;
public static final int NUMERIC = 2;
public static final int NVARCHAR = -9;
public static final int OTHER = 1111;
public static final int REAL = 7;
public static final int REF = 2006;
public static final int ROWID = -8;
public static final int SMALLINT = 5;
public static final int SQLXML = 2009;
public static final int STRUCT = 2002;
public static final int TIME = 92;
public static final int TIMESTAMP = 93;
public static final int TINYINT = -6;
public static final int VARBINARY = -3;
public static final int VARCHAR = 12;
/**
* 根据需要添加数字与数据类型字符串的对应关系
*/
static {
MAP.put(12, "VARCHAR");
MAP.put(4, "INTEGER");
MAP.put(2005, "CLOB");
MAP.put(2004, "BLOB");
MAP.put(91, "DATE");
MAP.put(-5, "BIGINT");
MAP.put(1, "CHAR");
MAP.put(93, "TIMESTAMP");
MAP.put(2, "NUMERIC");
MAP.put(5, "SMALLINT");
}
}
第二步：
package DB;
/**
* 数据库连接凭证
* @author lxzqz
*
*/
public class DBInfo {
//数据库地址,数据库端口号,数据库名称
private String url;
//用户名
private String user;
//密码
private String password;
//数据库驱动
private String driver;
public DBInfo() {}
public DBInfo(String url, String user, String password, String driver) {
this.setUrl(url);
this.setUser(user);
this.setPassword(password);
this.setDriver(driver);
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
/**
* 执行生成Excel表格
* @throws Exception
*/
public void doIt() throws Exception{
DBToTable.main(this);
}

public static void main(String[] args) throws Exception{
DBInfo info = new DBInfo("jdbc:kingbase://172.24.38.5:54321/tourism", "bjlyw", "bjlyw", "com.kingbase.Driver");
info.doIt();
}
}
第三步：需要提供数据库驱动包，以及操作Excel表的jxl.jar包
package DB;
import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* 导出数据库设计结构为Excel表格
* @author lxzqz
*
*/
public class DBToTable {
protected static void main(DBInfo info) throws Exception{
Class.forName(info.getDriver());
Connection conn = DriverManager.getConnection(info.getUrl(), info.getUser(), info.getPassword());
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet tables = databaseMetaData.getTables(null, "PUBLIC", "%", new String[]{"TABLE"});
String path = "E://数据库设计.xls";
WritableWorkbook book = Workbook.createWorkbook(new File(path));
// 生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet("第一页 ", 0);
int i = 0;
while(tables.next()) {
String tableName = tables.getString("TABLE_NAME");
ResultSet columns = databaseMetaData.getColumns(null, "%", tableName, "%");
/**
* COLUMN_NAME String => 列名称
DATA_TYPE int => 来自 java.sql.Types 的 SQL 类型
COLUMN_SIZE int => 列的大小。
REMARKS String => 描述列的注释
IS_NULLABLE String => ISO 规则用于确定列是否包括 null。
YES --- 如果参数可以包括 NULL
NO --- 如果参数不可以包括 NULL
*/
sheet.mergeCells(0, i, 5, i + 1);
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
Label label = new Label(0, i, "表名" + tableName, format);
i += 2;
Label label2 = new Label(0, i, "字段名", format);
sheet.addCell(label2);
Label label3 = new Label(1, i, "属性名", format);
sheet.addCell(label3);
Label label4 = new Label(2, i, "类型", format);
sheet.addCell(label4);
Label label5 = new Label(3, i, "长度", format);
sheet.addCell(label5);
Label label6 = new Label(4, i, "是否可空", format);
sheet.addCell(label6);
Label label7 = new Label(5, i, "注解", format);
sheet.addCell(label7);
sheet.addCell(label);
while(columns.next()) {
++i;
//列明
String colName = columns.getString("COLUMN_NAME");
Label C = new Label(0, i, colName, format);
sheet.addCell(C);
//注解-属性名
String remarks = columns.getString("REMARKS");
Label R = new Label(1, i, remarks, format);
sheet.addCell(R);
//数据类型-数字
int type = columns.getInt("DATA_TYPE");
//数据类型-字符串
String typeName = Types.MAP.get(type);
Label T = new Label(2, i, typeName, format);
sheet.addCell(T);
//数据长度
int length = columns.getInt("COLUMN_SIZE");
Label L = new Label(3, i, length + "", format);
sheet.addCell(L);
//是否为空
String isNull = columns.getString("IS_NULLABLE");
isNull = "YES".equals(isNull) ? "NULL" : "NOT NULL";
Label N = new Label(4, i, isNull, format);
sheet.addCell(N);
Label RR = new Label(5, i, remarks, format);
sheet.addCell(RR);
}
i += 3;
}
// 写入数据并关闭文件
book.write();
book.close();
}
}