dbinfocollector.java

生成与Oracle相关的Ibatis相关配置文件及Java源码

/*
 * Copyright (c) 2008 胜利油田胜利软件有限责任公司. All rights reserved.
 */
package com.victorysoft.code.base;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.victorysoft.code.bean.ColInfo;
import com.victorysoft.code.bean.TabInfo;

/**
 * 
 * @author 于景洋
 * @newtime Oct 17, 2008,2:03:42 PM
 * @version 1.0
 * @see
 * @since JDK 1.5.0
 */
public class DBInfoCollector {

	/**
	 * 获取指定数据表信息
	 * 
	 * @return
	 */
	public TabInfo getTabInfo(String tabName) {
		TabInfo tab = null;
		String sql = "SELECT A.TABLE_NAME, A.COMMENTS FROM USER_TAB_COMMENTS A "
				+ "WHERE A.TABLE_TYPE='TABLE' AND A.TABLE_NAME=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tabName);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tab = new TabInfo();
				tab.setName(rs.getString("TABLE_NAME"));
				tab.setComt(rs.getString("COMMENTS"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(rs);
			this.close(pstmt);
			this.close(conn);
		}
		return tab;
	}

	/**
	 * 获取所有数据表信息
	 * 
	 * @return
	 * @throws SQLException 
	 */
	public List<TabInfo> getTabInfos() throws SQLException {
		List<TabInfo> list = new ArrayList<TabInfo>();
		String sql = "SELECT A.TABLE_NAME, A.COMMENTS FROM USER_TAB_COMMENTS A WHERE A.TABLE_TYPE='TABLE'";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TabInfo tab = new TabInfo();
				tab.setName(rs.getString("TABLE_NAME"));
				tab.setComt(rs.getString("COMMENTS"));
				list.add(tab);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			this.close(rs);
			this.close(pstmt);
			this.close(conn);
		}
		return list;
	}

	/**
	 * 获取指定数据表的所有数据列信息
	 * 
	 * @param tableName
	 *            数据表名称
	 * @return
	 */
	public List<ColInfo> getTabColInfos(String tabName) {
		List<ColInfo> list = new ArrayList<ColInfo>();
		String sql = "SELECT C.*,DECODE(D.COLUMN_NAME,C.COLUMN_NAME,'Y','N') ISPRIMARY FROM "
				+ "(SELECT A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_PRECISION,"
				+ "A.DATA_SCALE,B.COMMENTS FROM USER_TAB_COLS A LEFT OUTER JOIN USER_COL_COMMENTS B "
				+ "ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME WHERE A.TABLE_NAME=?) C "
				+ "LEFT OUTER JOIN USER_CONS_COLUMNS D ON C.TABLE_NAME=D.TABLE_NAME "
				+ "AND C.COLUMN_NAME=D.COLUMN_NAME";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tabName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				ColInfo col = new ColInfo();
				col.setName(rs.getString("COLUMN_NAME"));
				col.setType(rs.getString("DATA_TYPE"));
				col.setLength(rs.getInt("DATA_LENGTH"));
				col.setPrecision(rs.getString("DATA_PRECISION"));
				col.setScale(rs.getString("DATA_SCALE"));
				col.setComt(rs.getString("COMMENTS"));
				col.setKey("Y".equals(rs.getString("ISPRIMARY")));
				list.add(col);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(rs);
			this.close(pstmt);
			this.close(conn);
		}
		return list;
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
		StringBuffer buff = new StringBuffer();
		buff.append("jdbc:oracle:thin:@");
		buff.append(CodeMakerConst.hostname);
		buff.append(":1521:");
		buff.append(CodeMakerConst.database);
		return DriverManager.getConnection(buff.toString(),
				CodeMakerConst.username, CodeMakerConst.password);
	}

	/**
	 * 关闭结果集
	 * 
	 * @param rs
	 */
	private void close(ResultSet rs) {
		if (rs == null) {
			return;
		}
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭PreparedStatement
	 * 
	 * @param pstmt
	 */
	private void close(PreparedStatement pstmt) {
		if (pstmt == null) {
			return;
		}
		try {
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	private void close(Connection conn) {
		if (conn == null) {
			return;
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}