dealdatabaseinfostandard.java

J2EE eclipse 下开发数据库一个插件

package com.tanghan.db.logic;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.sql.*;

import com.tanghan.db.Field;
import com.tanghan.db.SQLResult;
import com.tanghan.db.Table;
//import com.tanghan.plugin.TanghanClassLoader;
//import com.tanghan.plugin.TanghanPlugin;
import com.tanghan.util.PageList;
import com.tanghan.util.DealString;
import com.tanghan.util.TanghanException;

/**
 * DealDatabaseInfo的一个实现类
 * TO DO 添加Exception
 * @author Jerry Tang
 * @version v0.1.0
 * @createdate 2003-3-25
 * @copyright  (C) 2003 Tanghan工作组
 */
public class DealDatabaseInfoStandard extends AbstractDealDatabaseInfo {

	/**
	 * Constructor for DealDatabaseInfoStandard.
	 */
	public DealDatabaseInfoStandard() {
		super();
	}

	/**
	 * @see com.tanghan.db.logic.DealDatabaseInfo#getTableInfo(String)
	 */
	public Table getTableInfo(String tableName) {
		Table tb = null;

		try {
			tb = new Table(tableName);
			tb.setFieldList(getTableField(tableName));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tb;
	}

	/**
	 * @see com.tanghan.db.logic.DealDatabaseInfo#getTableList()
	 */
	public List getTableList()  throws TanghanException{
		Connection con = null;
		DatabaseMetaData dma;
		ResultSet rs = null;
		Table tb = null;
		java.util.Vector vc = new java.util.Vector(6, 6);
		try {
			con = getConnection();
			dma = con.getMetaData();
//			dma.get
			String[] types = { "TABLE" };
			rs = dma.getTables(null, this.dbConnection.getDefaultSchema(), "%", types);

			while (rs.next()) {
				tb = new Table(rs.getString(3));
				//tb.setFieldList(getTableField(rs.getString(3)));
				vc.add(tb);
			}

		} catch (SQLException e) {
			throw new TanghanException(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (con != null)
					closeConnection();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return vc;

	}

	/**
	 * @see com.tanghan.db.logic.DealDatabaseInfo#getTableField(String)
	 */
	public List getTableField(String tableName) {
		Connection con = null;
		Statement stmt = null;
		//数据结果集
		ResultSet rs = null;
		//主键的结果集
		ResultSet pmrs = null;
		//外键的结果集
		ResultSet fnrs = null;
		ResultSetMetaData rsmd = null;
		DatabaseMetaData dbmd = null;
		java.util.Vector vc = new java.util.Vector(6, 6);
		int numCols = 0;
		Field fd = null;

		try {

			con = getConnection();

			stmt =	con.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(1);
			stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
			
			dbmd = con.getMetaData();
			pmrs = dbmd.getPrimaryKeys(null,this.dbConnection.getDefaultSchema(),tableName);
			fnrs = dbmd.getExportedKeys(null,this.dbConnection.getDefaultSchema(),tableName);
			//得到所有主键
			HashMap pkLS = new HashMap();
			while (pmrs.next()){
				pkLS.put(DealString.trim(pmrs.getString(4)),pmrs.getString(4));
			}
			//得到所有外键
			HashMap fnLs = new HashMap();
			while (fnrs.next()){
				fnLs.put(DealString.trim(fnrs.getString(4)),fnrs.getString(4));
			}
			
			rs = stmt.executeQuery("select * from " + tableName);
			
			rsmd = rs.getMetaData();
			
			numCols = rsmd.getColumnCount();
			
			for (int i = 1; i <= numCols; i++) {
				fd = new Field(
						rsmd.getTableName(i),
						rsmd.getColumnName(i),
						rsmd.getColumnType(i),
						rsmd.getColumnTypeName(i),
						rsmd.getColumnDisplaySize(i),
						rsmd.getPrecision(i),
						rsmd.getScale(i),
						rsmd.isNullable(i)==ResultSetMetaData.columnNullable?true:false);
				if(pkLS.containsKey(DealString.trim(fd.getFieldName()))){
					fd.setPrimaryKey(true);
				}else if(fnLs.containsKey(DealString.trim(fd.getFieldName()))){
					fd.setForeignKey(true);
				}
				vc.add(fd);
			}
			rs.close();
			closeConnection();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return vc;

	}

	/**
	 * @see com.tanghan.db.logic.DealDatabaseInfo#getTableFieldInfo(String, String)
	 */
	public Field getTableFieldInfo(String tableName, String fieldName) {

		Field fd = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		String sqlstr = "";
		try {

			sqlstr = "select " + fieldName + " from " + tableName;
			con = getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sqlstr);
			rsmd = rs.getMetaData();

//			fd =new FieldVO(fieldName, rsmd.getColumnTypeName(1), rsmd.getColumnDisplaySize(1));
			fd = new Field(
					rsmd.getTableName(1),
					rsmd.getColumnName(1),
					rsmd.getColumnType(1),
					rsmd.getColumnTypeName(1),
					rsmd.getColumnDisplaySize(1),
					rsmd.getPrecision(1),
					rsmd.getScale(1),
					rsmd.isNullable(1)==ResultSetMetaData.columnNullable?true:false);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					closeConnection();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return fd;
	}

	/**
	 * @see com.tanghan.db.logic.DealDatabaseInfo#getNotes(String, PageList)
	 */
	public String[][] getNotes(String tableName, PageList pageList) {
		if(pageList == null || DealString.equals(tableName,""))
			return new String[0][0];
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		String[][] n = null;
		int numCols = 0;
		long numNotes = 0;
		String sqlstr = "";

		try {

			sqlstr = "select * from " + tableName;
			con = getConnection();
			//应该根据showAll分成两种情况
			if(pageList.isShowAll()){
				stmt = con.createStatement();
			}else{
				stmt =
					con.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				stmt.setFetchSize(pageList.getList()+1);
				stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
				
			}
			rs = stmt.executeQuery(sqlstr);
			rsmd = rs.getMetaData();
			numCols = rsmd.getColumnCount();
			numNotes = fetchResultCounts(tableName);
			pageList.setSize(numNotes);
			//应该根据showAll分成两种情况
			if(pageList.isShowAll()){
				n = new String[numCols][(int) numNotes + 1];
			}else{
				if (numNotes > 0) {
					if (pageList.getCurrentPage() == pageList.getTotalPage()) {
						n = new String[numCols][(int)(numNotes-(pageList.getTotalPage()-1)*pageList.getList()+1)];
					} else{
						n = new String[numCols][pageList.getList()+1];
					}
				} else {
					n = new String[numCols][1];
				}
			}

			for (int ii = 0; ii < numCols; ii++) {
				if (rsmd.getColumnLabel(ii + 1) != null) {
					n[ii][0] = rsmd.getColumnLabel(ii + 1);
				} else {
					n[ii][0] = "null";
				}
			}
			try{

				if(pageList.isShowAll()){
					int currentRowOfPage = 0;
					while(rs.next()&&(currentRowOfPage<numNotes)){
						currentRowOfPage++;
						for (int i = 0; i < numCols; i++) {
							n[i][currentRowOfPage] = DealString.trim(rs.getString(i + 1));
						}
					}
				}else{
					int currentRowOfPage = 1;
					do {
						if (currentRowOfPage == 1) {
							if (!rs.absolute((int)pageList.getStart())) {
								break;
							}
						}
						for (int i = 0; i < numCols; i++) {
							n[i][currentRowOfPage] = DealString.trim(rs.getString(i + 1));
						}
					} while ((currentRowOfPage++ < pageList.getList()) && rs.next());
				}
			}catch(Exception ex){
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			//PorpoisePlugin.log(e.toString(), e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					closeConnection();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	protected long fetchResultCounts(String tableName) throws TanghanException{

		long ret = 0;
		String sql = "select count(*) from " + tableName;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection con = null;

		try {
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				ret = rs.getLong(1);
			}
		} catch (SQLException sqle) {
			throw new TanghanException(sqle);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException sqle) {}
			closeConnection();
		}
		return ret;
	}


	/* (non-Javadoc)
	 * @see com.tanghan.db.logic.AbstractDealDatabaseInfo#getSchemas()
	 */
	public List getSchemas() throws TanghanException {
		Connection con = null;
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		List ls = new ArrayList();
		try {
			con = getConnection();
			dbmd = con.getMetaData();
			rs = dbmd.getSchemas();
			while(rs.next()){
				ls.add(rs.getString(1));
			}
			
		} catch (SQLException sqle) {
			throw new TanghanException(sqle);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException sqle) {}
			closeConnection();
		}		
		return ls;
	}

	/* (non-Javadoc)
	 * @see com.tanghan.db.logic.AbstractDealDatabaseInfo#executeSQL(java.lang.String)
	 */
	public SQLResult executeSQL(String sql) throws TanghanException {
		// TODO 执行SQL语句的
		SQLResult sqlRes = null; 
		Statement stat = null;
		Connection con = null;
		ResultSet rs = null;
		try{
			con = this.getConnection();
			stat = con.createStatement();
			if(stat.execute(sql)){
				ResultSetMetaData rsmd = null;
				rs = stat.getResultSet();
				
				rsmd = rs.getMetaData();
				String[][] n = null;
				int numCols = 0;
				long numNotes = 0;
				numCols = rsmd.getColumnCount();
				ArrayList ls = new ArrayList();
				String[] datas = new String[numCols];
				for (int ii = 0; ii < numCols; ii++) {
					if (rsmd.getColumnLabel(ii + 1) != null) {
						datas[ii] = DealString.trim(rsmd.getColumnLabel(ii + 1));
					} else {
						datas[ii] = "null";
					}
				}
				ls.add(datas);				
				while(rs.next()){
					datas = new String[numCols];
					for (int ii = 0; ii < numCols; ii++) {
						datas[ii] = DealString.trim(rs.getString(ii + 1));
					}
					ls.add(datas);
				}
				sqlRes = new SQLResult(ls);
			}else{
				sqlRes = new SQLResult(stat.getUpdateCount());
			}
			closeConnection();
		}catch(Exception ex){
			throw new TanghanException(ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stat != null) {
					rs.close();
				}
			} catch (Exception sqle) {}
			closeConnection();
		}				
		return sqlRes;
	}

}