这里给出的是用Java代码如何生成数据字典，数据字典各表说明直接在本手册 索引 里面搜索！ TIPTOP GP ERP 5.30 数据字典
如何生成数据库手册(数据字典)帮助文档
package com.yihui.tiptoptools;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/***
* @author 李yi辉
* 生成TIPTOP GP ERP 数据库字典文件
* 个人代码环境是TIPTOP GP ERP 5.25
* 此仅限于Oracle架构的TIPTIP ERP,其他数据库(SQLSERVER、INFORMIX)数据库表结构是不同的
*/
public class DSHtmlFiles {
@SuppressWarnings("unused")
public static void main(String[] args) {
final String htmlFileDir = "E:/menu/htmlfile/ds/"; /*生成的数据库字典htm网页文件路径*/
final boolean disSYSFlag = false; /*是否区分ERP各模块路径存放数据字典文件,如果不区分则改为false*/
final int gap03Length = 40;
final String htmlFileTxt1= "<HTML><HEAD>\n"
+ "<META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=UTF-8>\n"
+ "<title> ";
final String htmlFileTxt2= " </title>\n"
+ "<style type=\"text/css\">\n"
+ "<!--\n"
+ ".9t {font-size: 9pt; color: #000000; font-family: \"宋体\";}\n"
+ "-->\n"
+ "</style></HEAD>\n"
+ "<body bgcolor=\"#FFFFFF\">\n"
+ "<table width=\"90%\" border=\"0\" align=\"center\" ><tr><td>\n"
+ "<PRE class=\"9t\">\n";
StringBuffer htmlFileTxt3= new StringBuffer();
final String htmlFileTxt4= "</PRE>\n"
+ "</td></tr></table>\n"
+ "</Body>\n"
+ "</HTML>\n";
final String ORACLEDBDRIVER = "oracle.jdbc.driver.OracleDriver";
final String ORACLEDBURL = "jdbc:oracle:thin:@数据库.IP.地.址:1521:topprod"; /*Oracle JDBC连接字符串*/
final String ORACLEDBUSER = "ds"; /*用户名*/
final String ORACLEDBPASSWORD = "ds"; /*密码*/
Connection connOracle = null; /*得到Oracle数据库的连接*/
PreparedStatement pstmTableOracle = null; /*查表-执行CRUD操作*/
ResultSet rsTableOracle = null; /*查表-保持CRUD操作记录集*/
PreparedStatement pstmColumnOracle = null; /*查栏位-执行CRUD操作*/
ResultSet rsColumnOracle = null; /*查栏位-保持CRUD操作记录集*/
/*模块、档案代号、档案名称、档案目的、上游档案、下游档案、档案类型*/
String grabTableSQL = "SELECT zta03,zta01,gat03,gat04,zta11,zta12,zta09"
+ " FROM zta_file"
+ " LEFT JOIN gat_file ON zta01 = gat01 AND gat02='2'" /*gat02='2',简体*/
+ " WHERE zta02 = 'ds'"
+ " AND zta03 IS NOT NULL"
+ " ORDER BY zta03,zta01";
/*栏位代号、栏位类型、DEFAULT值、栏位名称、栏位说明、NULL否*/
/*
String grabColumnSQL = "SELECT lower(column_name),lower(data_type),"
+ " trim(to_char(decode(data_precision,null,data_length,data_precision),'9999')),"
+ " data_default,rpad(gaq03,30),gaq05,data_scale,nullable"
+ " FROM all_tab_columns"
+ " LEFT JOIN gaq_file ON lower(all_tab_columns.column_name)=gaq01 AND gaq02='2'"
+ " LEFT JOIN ztb_file ON lower(all_tab_columns.column_name)=ztb03 AND ztb02='ds'"
+ " WHERE lower(table_name)=?"
+ " AND lower(owner)='ds'"
+ " ORDER BY column_id";
*/
String grabColumnSQL = "SELECT rpad(lower(column_name),15),"
+ " (case"
+ " when lower(data_type) = 'varchar2'"
+ " then rpad(lower(data_type)||'('||trim(to_char(decode(data_precision,null,data_length,data_precision),'9999'))||')',16)"
+ " when lower(data_type) = 'number'"
+ " then"
+ " (case"
+ " when data_scale = 0"
+ " then rpad(lower(data_type)||'('||trim(to_char(decode(data_precision,null,data_length,data_precision),'9999'))||')',16)"
+ " else"
+ " rpad(lower(data_type)||'('||trim(to_char(decode(data_precision,null,data_length,data_precision),'9999'))||','||data_scale||')',16)"
+ " end)"
+ " else"
+ " rpad(lower(data_type),16)"
+ " end),"
+ " data_default,rpad(gaq03," + gap03Length + "),gaq05,rpad(decode(nullable,'N',' NOT NULL','Y',' ',' '),10)"
+ " FROM all_tab_columns"
+ " LEFT JOIN gaq_file ON lower(all_tab_columns.column_name)=gaq01 AND gaq02='2'"
+ " LEFT JOIN ztb_file ON lower(all_tab_columns.column_name)=ztb03 AND ztb02='ds'"
+ " WHERE lower(table_name)=?"
+ " AND lower(owner)='ds'"
+ " ORDER BY column_id";
try {
Class.forName(ORACLEDBDRIVER); /*加载驱动*/
connOracle = DriverManager.getConnection(ORACLEDBURL, ORACLEDBUSER, ORACLEDBPASSWORD); /*取得数据库连接*/
//统计TIPTOP ERP的表信息
pstmTableOracle = connOracle.prepareStatement(grabTableSQL);
pstmColumnOracle = connOracle.prepareStatement(grabColumnSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rsTableOracle = pstmTableOracle.executeQuery();
TableInfo tableInfo;
String htmFilrSYSDir = htmlFileDir;
String htmFileTableDir = "";
String sys = "";
if(!disSYSFlag){
File combFileDSDir = new File(htmlFileDir);
if(!combFileDSDir.exists()){
combFileDSDir.mkdir();
}
}
while(rsTableOracle.next()){
tableInfo = new TableInfo();
tableInfo.zta03 = rsTableOracle.getString("zta03");
tableInfo.zta01 = rsTableOracle.getString("zta01");
tableInfo.gat03 = rsTableOracle.getString("gat03");
tableInfo.gat04 = rsTableOracle.getString("gat04");
tableInfo.zta11 = rsTableOracle.getString("zta11");
tableInfo.zta12 = rsTableOracle.getString("zta12");
tableInfo.zta09 = rsTableOracle.getString("zta09");
//创建模组系统别目录
if(tableInfo.zta03.trim() != sys && disSYSFlag){
htmFilrSYSDir = htmlFileDir + tableInfo.zta03 + "/";
File sysFileDir = new File(htmFilrSYSDir);
if(!sysFileDir.exists()){
sysFileDir.mkdir();
}
sys = tableInfo.zta03.trim();
}
//htmIndex为方便制作htm的目录及索引 而存在
String htmIndex = "<LI> <OBJECT type=\"text/sitemap\"><param name=\"Name\" value=\""
+ tableInfo.zta01
+ "\"><param name=\"Local\" value=\""
+ tableInfo.zta01 + ".htm"
+ "\"></OBJECT>";
System.out.println(htmIndex);
//创建ds表字典htm文件
htmFileTableDir = htmFilrSYSDir + tableInfo.zta01.trim() + ".htm";
File htmFileTable = new File(htmFileTableDir);
htmFileTable.createNewFile();
//统计对应表的栏位信息
pstmColumnOracle.setString(1, tableInfo.zta01);
rsColumnOracle = pstmColumnOracle.executeQuery();
ColumnInfo columnInfo;
if(htmlFileTxt3.length()>0){
htmlFileTxt3.delete(0, htmlFileTxt3.length());
}
htmlFileTxt3.append("/*" + tableInfo.zta03 + "\n===========.===========.============.============.============.===========.===========.============\n");
htmlFileTxt3.append("档案代号:" + tableInfo.zta01 + "\n");
htmlFileTxt3.append("档案名称:" + tableInfo.gat03 + "\n");
htmlFileTxt3.append("档案目的:" + tableInfo.gat04 + "\n");
htmlFileTxt3.append("上游档案:" + tableInfo.zta11 + "\n");
htmlFileTxt3.append("下游档案:" + tableInfo.zta12 + "\n");
htmlFileTxt3.append("档案类型:" + tableInfo.zta09 + "\n");
htmlFileTxt3.append("===========.===========.============.============.============.===========.===========.============\n*/\n");
htmlFileTxt3.append("create table " + tableInfo.zta01 + "(\n");
int totalRow = 0;
int indexRow = 0;
rsColumnOracle.last();
totalRow = rsColumnOracle.getRow();
rsColumnOracle.beforeFirst();
while(rsColumnOracle.next()){
++indexRow;
columnInfo = new ColumnInfo();
columnInfo.column_name = rsColumnOracle.getString(1);
columnInfo.date_type = rsColumnOracle.getString(2);
columnInfo.data_default = rsColumnOracle.getString(3);
columnInfo.gaq03 = rsColumnOracle.getString(4);
columnInfo.gaq05 = rsColumnOracle.getString(5);
columnInfo.nullable = rsColumnOracle.getString(6);
boolean defaultFlag = false;
boolean noNullableFlag = false;
if(columnInfo.data_default != null){
defaultFlag = true; //有DEFAULT默认值
}
if(columnInfo.nullable.indexOf("NOT NULL")>0){
noNullableFlag = true; //不能为空
}
StringBuffer columnInfoStrBuffer = new StringBuffer();
if(!defaultFlag && !noNullableFlag){ //无默认值&&为空
if(indexRow<totalRow){
columnInfoStrBuffer.append(columnInfo.column_name + columnInfo.date_type.replaceFirst(" ", ","));
columnInfoStrBuffer.append(" ");
}else{
columnInfoStrBuffer.append(columnInfo.column_name + columnInfo.date_type);
columnInfoStrBuffer.append(" ");
}
}else if(!defaultFlag && noNullableFlag){ //无默认值&&不为空
columnInfoStrBuffer.append(columnInfo.column_name + columnInfo.date_type);
if(indexRow<totalRow){
columnInfoStrBuffer.append(" " + columnInfo.nullable.trim() + ", ");
}else{
columnInfoStrBuffer.append(" " + columnInfo.nullable.trim() + " ");
}
}else if(defaultFlag && !noNullableFlag){ //有默认值&&为空
columnInfoStrBuffer.append(columnInfo.column_name + columnInfo.date_type);
if(indexRow<totalRow){
columnInfoStrBuffer.append("DEFAULT " + columnInfo.data_default + ", ");
}else{
columnInfoStrBuffer.append("DEFAULT " + columnInfo.data_default + " ");
}
}else{ //有默认值&&不为空
columnInfoStrBuffer.append(columnInfo.column_name + columnInfo.date_type);
if(indexRow<totalRow){
columnInfoStrBuffer.append("DEFAULT " + columnInfo.data_default + columnInfo.nullable.trim() + ", ");
}else{
columnInfoStrBuffer.append("DEFAULT " + columnInfo.data_default + columnInfo.nullable.trim() + " ");
}
}
columnInfoStrBuffer.append(" /*" + columnInfo.gaq03 + "*/" + "\n");
if(columnInfo.gaq05 != null && !columnInfo.gaq05.equals(columnInfo.gaq03) && columnInfo.gaq05.trim().length()>0){
String[] gaq05 = columnInfo.gaq05.trim().split("\n");
for(int i=0;i<gaq05.length;i++){
columnInfoStrBuffer.append(" ");
gaq05[i] = gaq05[i].trim();
int lengthUTF_8 = 0;
for(int j=0;j<gaq05[i].length();j++){
char c = gaq05[i].charAt(j);
if(c>255){
lengthUTF_8 += 2;
}else{
lengthUTF_8++;
}
}
if(lengthUTF_8<gap03Length){
for(int k=lengthUTF_8;k<gap03Length;k++){
gaq05[i] = gaq05[i] + " ";
}
}
columnInfoStrBuffer.append("/*" + gaq05[i] + "*/" + "\n");
}
}
htmlFileTxt3.append(columnInfoStrBuffer);
}
htmlFileTxt3.append(");\n\n\n");
//将上面信息写入Html字典文件
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(htmFileTable),"UTF-8");
BufferedWriter htmlBufferedWriter = new BufferedWriter(writer);
htmlBufferedWriter.write(htmlFileTxt1 + tableInfo.zta01.trim() + htmlFileTxt2 + htmlFileTxt3 + htmlFileTxt4);
htmlBufferedWriter.flush();
htmlBufferedWriter.close();
writer.close();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally{
try {
if(rsColumnOracle != null){
rsColumnOracle.close();
rsColumnOracle = null;
}
if(pstmColumnOracle != null){
pstmColumnOracle.close();
pstmColumnOracle = null;
}
if(rsTableOracle != null){
rsTableOracle.close();
rsTableOracle = null;
}
if(pstmTableOracle != null){
pstmTableOracle.close();
pstmTableOracle = null;
}
if(connOracle != null){
connOracle.close();
connOracle = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//表类
class TableInfo {
String zta03; /*模块*/
String zta01; /*档案代号*/
String gat03; /*档案名称*/
String gat04; /*档案目的*/
String zta11; /*上游档案*/
String zta12; /*下游档案*/
String zta09; /*档案类型*/
public String getZta03() {
return zta03;
}
public void setZta03(String zta03) {
this.zta03 = zta03;
}
public String getZta01() {
return zta01;
}
public void setZta01(String zta01) {
this.zta01 = zta01;
}
public String getGat03() {
return gat03;
}
public void setGat03(String gat03) {
this.gat03 = gat03;
}
public String getGat04() {
return gat04;
}
public void setGat04(String gat04) {
this.gat04 = gat04;
}
public String getZta11() {
return zta11;
}
public void setZta11(String zta11) {
this.zta11 = zta11;
}
public String getZta12() {
return zta12;
}
public void setZta12(String zta12) {
this.zta12 = zta12;
}
public String getZta09() {
return zta09;
}
public void setZta09(String zta09) {
this.zta09 = zta09;
}
}
//栏位类
class ColumnInfo {
String column_name; /*栏位代号*/
String date_type; /*栏位类型*/
String data_default; /*DEFAULT值*/
String gaq03; /*栏位名称*/
String gaq05; /*栏位说明*/
String nullable; /*NULL否*/
public String getColumn_name() {
return column_name;
}
public void setColumn_name(String column_name) {
this.column_name = column_name;
}
public String getDate_type() {
return date_type;
}
public void setDate_type(String date_type) {
this.date_type = date_type;
}
public String getData_default() {
return data_default;
}
public void setData_default(String data_default) {
this.data_default = data_default;
}
public String getGaq03() {
return gaq03;
}
public void setGaq03(String gaq03) {
this.gaq03 = gaq03;
}
public String getGaq05() {
return gaq05;
}
public void setGaq05(String gaq05) {
this.gaq05 = gaq05;
}
public String getNullable() {
return nullable;
}
public void setNullable(String nullable) {
this.nullable = nullable;
}
}
如何找出系统所有的cl共用函数及所在对应4gl代码文件
统计sub副程序类似
目前个人所在公司版本的TIPTOP ERP 5.25 共有 cl_函数811个，sub公用副函数1125个(有4gl代码的,某些函数没有4gl代码就没办法分析了)
㈠将TIPTOP ERP的Linux服务器$LIB/4gl整个文件夹通过FTP工具(比如xftp)下载到本地
㈡执行以下Java代码代码就可以找出所有的共用函数及所在文件
package com.liyihui.tiptoptools;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
/***
*
* @author 李yi辉
* 统计多少个cl_共用函数及多少共用个sub副函数
*/
public class GrabFunction {
public static void main(String[] args) {
File clFileDir = new File("E:\\menu\\lib\\4gl\\"); //4gl代码目录,这里不处理异常了,4gl文件目录不对时会抛异常
File[] allClFiles = clFileDir.listFiles(); //你应该try...catch...
int index = 0;
for(int i=0;i<allClFiles.length;i++){
try {
BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(allClFiles[i]),"UTF-8"));
String sLine;
String functionName;
int beginIndex;
int endIndex;
try {
while((sLine=br.readLine())!=null){
sLine = sLine.trim().toLowerCase();
if(sLine.indexOf("function")==0){
//System.out.println(allClFiles[i].getName());
beginIndex = 8;
endIndex = (sLine.indexOf("("))>0 ? sLine.indexOf("(") : sLine.length();
functionName = sLine.substring(beginIndex, endIndex);
System.out.println(++index + " " + functionName.trim() + " " + allClFiles[i].getName());
}
}
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
br.close();
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
}
}
}
㈢将4gl文件转换为本手册所需要的代码格式化后的htm网页文档（这步非必须项，个人做手册时用的工具代码）
package com.yihui.tiptoptools;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
/***
*
* @author 李yi辉
*将4gl源代码转出为格式化的htm网页文档
*/
public class FourjsConverToHtm {
public static void main(String[] args) {
File htmTxtDir = new File("E:/menu/htmfile/htmTxt/"); //公用html文件代码目录
File lib4glDir = new File("E:/menu/htmfile/lib/"); //lib源文件4gl目录
File sub4glDir = new File("E:/menu/htmfile/sub/"); //sub源文件4gl目录
File[] AllFiles = {htmTxtDir,lib4glDir,sub4glDir};
String lines = null;
StringBuffer strBufferHtmTxt0 = new StringBuffer();
String strHtmTxt1 = null;
String strHtmTxt2 = null;
String strHtmTxt3 = null;
String strHtmTxt4 = null;
String strHtmTxt5 = null;
for(int i=0;i<AllFiles.length;i++){
File[] file = AllFiles[i].listFiles();
for(int j=0;j<file.length;j++){
try {
BufferedReader brHtmTxt = new BufferedReader(new InputStreamReader(new FileInputStream(file[j]),"UTF-8"));
if (strBufferHtmTxt0.length() > 0) {
strBufferHtmTxt0.delete(0, strBufferHtmTxt0.length());
}
while ((lines = brHtmTxt.readLine()) != null) { // 一行一行读4gl文件
strBufferHtmTxt0.append(lines);
strBufferHtmTxt0.append("\n");
}
if(i==0){
if ("htmTxt1.txt".equals(file[j].getName())) {
strHtmTxt1 = strBufferHtmTxt0.toString(); // htmTxt1.txt读入到strHtmTxt1
//System.out.println("htmTxt1.txt\n" + strHtmTxt1);
} else if ("htmTxt2.txt".equals(file[j].getName())) {
strHtmTxt2 = strBufferHtmTxt0.toString(); // htmTxt2.txt读入到strHtmTxt2
//System.out.println("htmTxt2.txt\n" + strHtmTxt2);
} else if ("htmTxt3.txt".equals(file[j].getName())) {
strHtmTxt3 = strBufferHtmTxt0.toString(); // htmTxt3.txt读入到strHtmTxt3
//System.out.println("htmTxt3.txt\n" + strHtmTxt3);
} else if ("htmTxt4.txt".equals(file[j].getName())) {
strHtmTxt4 = strBufferHtmTxt0.toString(); // htmTxt4.txt读入到strHtmTxt4
//System.out.println("htmTxt4.txt\n" + strHtmTxt4);
} else if ("htmTxt5.txt".equals(file[j].getName())) {
strHtmTxt5 = strBufferHtmTxt0.toString(); // htmTxt5.txt读入到strHtmTxt5
//System.out.println("htmTxt5.txt\n" + strHtmTxt5);
}
}else{
/*输出调试用
System.out.print(strHtmTxt1); //html
System.out.print(file[j].getName()); //title
System.out.print(strHtmTxt2); //html
System.out.print(file[j].getName()); //title
System.out.print(strHtmTxt3); //html
System.out.print(file[j].getName()); //title
System.out.print(strHtmTxt4); //html
System.out.print(strBufferHtmTxt0); //4gl代码文件内容
System.out.println(strHtmTxt5); //html
*/
String htmlFilesDir = null;
if(i==1){
htmlFilesDir = "E:/menu/htmfile/libhtm/" + file[j].getName() + ".htm"; /*生成lib对应的html文件目录*/
}else{
htmlFilesDir = "E:/menu/htmfile/subhtm/" + file[j].getName() + ".htm"; /*生成sub对应的html文件目录*/
}
File htmlFile = new File(htmlFilesDir); /*创建新htm文件*/
htmlFile.createNewFile();
OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(htmlFile),"UTF-8");
BufferedWriter htmlBufferedWriter = new BufferedWriter(writer);
htmlBufferedWriter.write(strHtmTxt1 + file[j].getName() //写入到html文件
+ strHtmTxt2 + file[j].getName()
+ strHtmTxt3 + file[j].getName()
+ strHtmTxt4 + strBufferHtmTxt0
+ strHtmTxt5);
htmlBufferedWriter.flush();
htmlBufferedWriter.close();
}
brHtmTxt.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
转载请注明：赫非域 » TIPTOP GP 用Java代码生成数据库手册(数据字典)