jacob实现oracle数据字典

jacob:Java COM Bridge  即java和com组件间的桥梁

 

目前最新的版本到了1.14

 

  http://nchc.dl.sourceforge.net/sourceforge/jacob-project/jacob-1.14.3.zip

操作jacob遇见2个常见问题

1、如果出现下面的错误
   com.jacob.com.ComFailException: A COM exception has been encountered:
   At Invoke of: Version
   Description: An unknown COM error has occured.
   表示dll的版本不对，换成最新版本即可。
2、如果出现下面的错误 
   java.lang.UnsatisfiedLinkError: no jacob in java.library.path
   表示把dll放到path下即可，设置path或是放到window/system32下

 

以上是对jacob的一些基本介绍

 

以下是对oracle的一些查询操作和jacob的结合自动生成数据字典.

 

设计上尽量想解耦合,发现一些地方还是偶合在一起

 

首先

基本数据类

package com.linpyi.databasedictionary;

public class DataBaseInfo {
	private String column_Name;//数据名称
	private String data_type;//数据类型
	private String data_length;//数据长度
	private String data_null;//数据是否为空
	private String data_comments;//数据注释
	public String getColumn_Name() {
		return column_Name;
	}
	public void setColumn_Name(String column_Name) {
		this.column_Name = column_Name;
	}
	public String getData_type() {
		return data_type;
	}
	public void setData_type(String data_type) {
		this.data_type = data_type;
	}
	public String getData_length() {
		return data_length;
	}
	public void setData_length(String data_length) {
		this.data_length = data_length;
	}
	public String getData_null() {
		return data_null;
	}
	public void setData_null(String data_null) {
		this.data_null = data_null;
	}
	public String getData_comments() {
		return data_comments;
	}
	public void setData_comments(String data_comments) {
		this.data_comments = data_comments;
	}
	public String toString(){
		return ""+column_Name+"\r"+data_type+"\r"+data_length+"\r"+data_null+"\r"+data_comments+"\n";
	}
}

 

 

写一个数据接口

package com.linpyi.databasedictionary;

import java.util.Map;

public interface DataBaseOperate {

	@SuppressWarnings("unchecked")
	public Map dealDataBase();
}

 

写操作oracle数据库来实现数据接口,以后如果有其他数据库也可以使用该接口

package com.linpyi.databasedictionary;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * 操作oracle数据字典
 * 主要实现对oracle数据库的查询返回信息放在map中
 * @author lpy 
 *
 */
public class DataBaseDictionaryOperateOracle implements DataBaseOperate {

	private Statement stmt = null;
	private ResultSet rs = null;
	private Connection con = null;

	// private FileOutputStream fileOut = null;

	@SuppressWarnings("unchecked")
	public Map dealDataBase(){

		// TODO Auto-generated method stub
		try {
			Class.forName(DataBasicInfo.dataDriver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			con = DriverManager.getConnection(DataBasicInfo.dataUrl,
					DataBasicInfo.userName, DataBasicInfo.userPwd);
			stmt = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		StringBuffer strbuf = new StringBuffer();
		strbuf.append("SELECT A.*,B.comments");
		strbuf.append(" FROM all_tab_columns A,DBA_COL_COMMENTS B");
		strbuf.append(" WHERE A.owner=B.owner");
		strbuf.append(" AND A.table_name=B.table_name");
		strbuf.append(" AND A.COLUMN_NAME=B.COLUMN_NAME");
		// owner是建立表的用户名
		strbuf.append(" AND A.owner='" + DataBasicInfo.dataName + "'");
		strbuf.append(" ORDER BY A.TABLE_NAME");
		System.out.println(strbuf.toString());
		Map map = new HashMap<String, ArrayList>();
		try {
			rs = stmt.executeQuery(strbuf.toString());
			String tb = "";
			ArrayList list = null;
			while (rs.next()) {
				// 对每个表生成一个新的sheet,并以表名命名
				String line = "";
				String tablename = rs.getString("TABLE_NAME");
				//如果tablename和上次循环的不一样,则说明到了一个新的表,重新实例话list
				if (!tb.equals(rs.getString("TABLE_NAME"))) {
					list = new ArrayList();
					DataBaseInfo databaseInfoHead = new DataBaseInfo();
					databaseInfoHead.setColumn_Name("字段名");
					databaseInfoHead.setData_type("字段类型");
					databaseInfoHead.setData_length("字段长度");
					databaseInfoHead.setData_null("是否为空");
					databaseInfoHead.setData_comments("字段含义");
					list.add(databaseInfoHead);
				} else {
					if (tb.length() > 0) {
						map.put(tb, list);//如果不是第一次循环,把list放入map
					}
				}
				DataBaseInfo databaseInfo = new DataBaseInfo();
				databaseInfo.setColumn_Name(rs.getString("COLUMN_NAME"));
				databaseInfo.setData_comments(rs.getString("COMMENTS"));
				databaseInfo.setData_null(rs.getString("NULLABLE"));
				String type = rs.getString("DATA_TYPE");
				databaseInfo.setData_type(type);

				String data_length = "";
				if (!type.equals("NUMBER")) {
					data_length = rs.getString("DATA_LENGTH") + "\t";
				} else {
					String scale = rs.getString("DATA_SCALE");
					if (scale == "null")
						scale = "";
					else
						scale = "," + scale;
					data_length = rs.getString("DATA_PRECISION") + scale + "\t";
				}

				databaseInfo.setData_length(data_length);
				list.add(databaseInfo);
				tb = tablename;
			}

			map.put(tb, list);// 最后一条记录

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return map;
	}


}

 

jacob实现接口,方便以后做其他扩展

public interface JacobOperate {
	public void operate();
}

 jacob 使用word实现上面接口,以后可以换为excel或者html

package com.linpyi.databasedictionary;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

/**
 * jacob实现转化为word文件 
 * @author lpy
 *
 */
public class JacobWordOperate implements JacobOperate {

	private DataBaseOperate dataBaseOperate;

	public JacobWordOperate(DataBaseOperate dataBaseOperate) {
		this.dataBaseOperate = dataBaseOperate;
	}

	@SuppressWarnings("unchecked")
	public void operate() {
		// TODO Auto-generated method stub
		Map map = null;

		map = dataBaseOperate.dealDataBase();

		System.out.println("表数="+map.size());
		ActiveXComponent wordApp = new ActiveXComponent("Word.Application"); // 启动word
		Dispatch.put(wordApp, "Visible", new Variant(true));// //设置word可见
		Dispatch docs = wordApp.getProperty("Documents").toDispatch();
		Dispatch document = Dispatch.call(docs, "Add").toDispatch();// create
		Dispatch selection = Dispatch.get(wordApp, "Selection").toDispatch();
		Dispatch align = Dispatch.get(selection, "ParagraphFormat")
				.toDispatch(); // 行列格式化需要的对象
		Dispatch font = Dispatch.get(selection, "Font").toDispatch(); // 字型格式化需要的对象
		// 标题处理////////////////////////
//		Dispatch.put(align, "Alignment", "1"); // 1:置中 2:靠右 3:靠左
//		Dispatch.put(font, "Bold", "1"); // 字型租体
//		Dispatch.put(font, "Color", "1,0,0,0"); // 字型颜色红色
//		Dispatch.call(selection, "TypeText", "Word文档处理"); // 写入标题内容
		// 表格处理////////////////////////
		Dispatch tables = Dispatch.get(document, "Tables").toDispatch();
		
		Iterator ito = map.keySet().iterator();
		int k=1;
		while (ito.hasNext()) {
			Dispatch.put(align, "Alignment", "3"); // 1:置中 2:靠右 3:靠左
			Dispatch.put(font, "Bold", "1"); // 字型租体
			Dispatch.put(font, "Color", "1,0,0,0"); // 字型颜色红色
			String tableName = (String) ito.next();//从迭代中获取表名
			Dispatch.call(selection, "TypeText", "table name:"+tableName); // 写入标题内容
			ArrayList list = (ArrayList) map.get(tableName);//根据表名获取相应的字段信息
			Dispatch range = Dispatch.get(selection, "Range").toDispatch();
			Dispatch table1 = Dispatch.call(tables, "Add", range, list.size(),
					new Variant(5), new Variant(1)).toDispatch(); // 设置行数,列数,表格外框宽度
			Dispatch t1 = Dispatch.call(tables, "Item", k)//每循环一次,新设计一个对象,方便下面的值存入对应的表格(k为变量)
			.toDispatch();
			k++;
			for (int i = 1; i <= list.size(); i++) {
				// 要填充对应的表格
				Dispatch.call(Dispatch.get(t1, "columns").toDispatch(), "AutoFit");// 自动调整
				DataBaseInfo info = (DataBaseInfo) list.get(i-1);
				Dispatch cell = Dispatch.call(t1, "Cell", new Variant(i),
						new Variant(1)).toDispatch();// 行，列				
				Dispatch.call(cell, "Select");
				Dispatch.put(selection, "Text", info.getColumn_Name()); // 写入word的内容
				Dispatch.put(font, "Bold", "0"); // 字型租体(1:租体 0:取消租体)
				Dispatch.put(font, "Color", "1,1,1,0"); // 字型颜色
				Dispatch.put(font, "Italic", "1"); // 斜体 1:斜体 0:取消斜体


				Dispatch cell1 = Dispatch.call(t1, "Cell", new Variant(i),
						new Variant(2)).toDispatch();// 行，列
				Dispatch.call(cell1, "Select");
				Dispatch.put(selection, "Text", info.getData_type()); // 写入word的内容
				Dispatch.put(font, "Bold", "0"); // 字型租体(1:租体 0:取消租体)
				Dispatch.put(font, "Color", "1,1,1,0"); // 字型颜色
				Dispatch.put(font, "Italic", "1"); // 斜体 1:斜体 0:取消斜体
				Dispatch range1 = Dispatch.get(cell1, "Range").toDispatch();


				Dispatch cell2 = Dispatch.call(t1, "Cell", new Variant(i),
						new Variant(3)).toDispatch();// 行，列
				Dispatch.call(cell2, "Select");
				Dispatch.put(selection, "Text", info.getData_length()); // 写入word的内容
				Dispatch.put(font, "Bold", "0"); // 字型租体(1:租体 0:取消租体)
				Dispatch.put(font, "Color", "1,1,1,0"); // 字型颜色
				Dispatch.put(font, "Italic", "1"); // 斜体 1:斜体 0:取消斜体

				Dispatch cell3 = Dispatch.call(t1, "Cell", new Variant(i),
						new Variant(4)).toDispatch();// 行，列
				Dispatch.call(cell3, "Select");
				Dispatch.put(selection, "Text", info.getData_null()); // 写入word的内容
				Dispatch.put(font, "Bold", "0"); // 字型租体(1:租体 0:取消租体)
				Dispatch.put(font, "Color", "1,1,1,0"); // 字型颜色
				Dispatch.put(font, "Italic", "1"); // 斜体 1:斜体 0:取消斜体


				Dispatch cell4 = Dispatch.call(t1, "Cell", new Variant(i),
						new Variant(5)).toDispatch();// 行，列
				Dispatch.call(cell4, "Select");
				Dispatch.put(selection, "Text", info.getData_comments()); // 写入word的内容
				Dispatch.put(font, "Bold", "0"); // 字型租体(1:租体 0:取消租体)
				Dispatch.put(font, "Color", "1,1,1,0"); // 字型颜色
				Dispatch.put(font, "Italic", "1"); // 斜体 1:斜体 0:取消斜体
//				Dispatch.put(font, "Underline", "1"); // 下划线
				Dispatch.call(selection, "MoveDown"); // 光标往下一行(才不会输入盖过上一输入位置)
			}			
			Dispatch.call(selection, "MoveDown"); // 光标往下一行(才不会输入盖过上一输入位置)
		}

		// 保存操作////////////////////////
		Dispatch.call(document, "SaveAs", DataBasicInfo.savaPath+DataBasicInfo.dataName+".doc");
	}

}

 

基本信息类,这里使用静态类,也可以使用配置文件

package com.linpyi.databasedictionary;

/**
 * 基本信息类
 * @author lpy
 *
 */
public class DataBasicInfo {
	public static String dataDriver="oracle.jdbc.driver.OracleDriver";
	public static String dataUrl="jdbc:oracle:thin:@192.168.1.101:1521:ora";
	public static String userName="system";
	public static String userPwd="manager";
	public static String dataName="";
	public static String savaPath="d:/";
}

 

client

package com.linpyi.databasedictionary;

public class Client {
	public static void main(String args[]) {
		JacobOperate operate = new JacobWordOperate(
				new DataBaseDictionaryOperateOracle());
		operate.operate();
	}
}

 

 

其实就只是借用了jacob来实现word插入表格.其实如果在实现表格那再解耦合,下次增加一些新的需要描述的数据

 

信息改起来就不会那么麻烦.可能还需要再设计下.

 

代码应该还可以再优化.

评论

3 楼 linpyi 2009-10-12  

tyzqqq 写道

jacob依赖于dll,别人要用的话，是不是每客户都要下载它的dll?

他的弊端就是这样,一般使用在客户端需要安装的情况下,这只不过是做个说明,jacob的使用,至于怎么使用,看具体情况

2 楼 tyzqqq 2009-10-12  

jacob依赖于dll,别人要用的话，是不是每客户都要下载它的dll?

1 楼 crabboy 2009-09-08  

没运行成功