tableutils.java

Speedframework--基于类型元数据的羽量级ORM.完全基于Java类型元数据和反射机制同时不需要任何对象关系映射配置文件的轻量级ORM框架,它充分利用了类型本身的信息,使用Metadata

package org.speedframework.util;

import java.util.ArrayList;

import org.speedframework.exception.SpeedException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.speedframework.cache.CachePool;
import org.speedframework.cache.CachePoolFactory;
import org.speedframework.cache.JCSCachePool;
import org.apache.jcs.access.exception.CacheException;
import org.apache.log4j.Logger;

import java.util.List;
import java.util.Map;
import java.util.HashMap;

/**
 * 
 * <p>
 * Title: SpeedFrameworkWork持久层框架
 * </p>
 * <p>
 * Description:表信息工具类
 * </p>
 * <p>
 * Copyright: Copyright (c) 2005
 * </p>
 * <p>
 * Company: SpeedFrameworkWork
 * </p>
 * 
 * @author 程伟杰 电话:[phone] lizf
 * @version 1.2beta
 */

public class TableUtils {
	private static final Logger log = Logger.getLogger(TableUtils.class);
	private static final CachePool cp=CachePoolFactory.getProviderCache();


	/**
	 * 提取自增量返回值
	 * 
	 * @param source
	 *            Object
	 * @param keys
	 *            List
	 * @throws SpeedException
	 * @return Object
	 */
	public static Object getReturnData(Connection con, String dbtype,
			Object source, List keys) throws SpeedException {
		Object reObj = null;
		try {
			if (keys != null) {
				reObj = source.getClass().newInstance();
				List l = TableUtils.getTablePrimate(con, dbtype, source
						.getClass());
				PropertyUtil.copyProperties(reObj, source);
				int key = 0;
				if (l != null) {
					for (int i = 0; i < l.size(); i++) {
						ColumnTool column = (ColumnTool) l.get(i);
						if (column.isAutoincrements()) {
							if (PropertyUtil.getPropertyType(source,
									column.getColumn()).getName().equals(
									"java.lang.String")) {
								PropertyUtil.setProperty(reObj, column
										.getColumn(), keys.get(key).toString());
							}
							if (PropertyUtil.getPropertyType(source,
									column.getColumn()).getName().equals(
									"java.lang.Integer")) {
								PropertyUtil.setProperty(reObj, column
										.getColumn(), keys.get(key));
							}
							key++;
						} else {
							continue;
						}
					}

				}
			}
		} catch (Exception e) {
			throw new SpeedException(e);
		}
		return reObj;
	}

	public static List getTablePrimate(Connection con, String dbtype,
			Class t_class) throws SQLException, CacheException {
		String tableName = SQLHelper.getExcuteTableName(t_class);
		List keyList = getPrimateKeys(con, dbtype, tableName);
		return keyList;
	}

	private static List getMySQLPKs(Connection con, String dbtype,
			String tableName) throws SQLException {
		DatabaseMetaData rbmd = con.getMetaData();
		List keyObjList = null;
		List keyList = new ArrayList();
		String table_schem = "";
		tableName = tableName.toLowerCase();
		ResultSet keySet = rbmd.getPrimaryKeys(con.getCatalog(), null,
				tableName);
		// ResultSetMetaData rsmdd=keySet.getMetaData();
		while (keySet.next()) {
			// for(int i=1;i<=rsmdd.getColumnCount();i++){
			// log.debug(rsmdd.getColumnName(i)+" "+ keySet.getString(i));
			// }
			keyList.add(keySet.getString(4).toLowerCase());
			table_schem = keySet.getString("TABLE_SCHEM");
			log.debug("The " + tableName + " PK is "
					+ keySet.getString(4).toLowerCase());
		}
		keySet.close();
		if (keyList.size() > 0) {
			keyObjList = new ArrayList();
			ResultSet keyInfo = rbmd.getColumns(null, null, tableName, null);
			// ResultSetMetaData rsmd=keyInfo.getMetaData();
			while (keyInfo.next()) {
				// for(int i=1;i<=rsmd.getColumnCount();i++){
				// log.debug(rsmd.getColumnName(i)+" "+ keyInfo.getString(i));
				// }
				String key = keyInfo.getString("COLUMN_NAME").toLowerCase();
				for (int i = 0; i < keyList.size(); i++) {
					String primate = (String) keyList.get(i);
					if (key.equals(primate)) {
						ColumnTool primateObj = new ColumnTool();
						String remark = keyInfo.getString("REMARKS");
						String column_def = keyInfo.getString("COLUMN_DEF");
						String TYPE_NAME = keyInfo.getString("TYPE_NAME");
						primateObj.setColumn(key);
						primateObj.setTable_schem(table_schem);
//						log.debug("REMARKS " + remark);
//						log.debug("COLUMN_DEF " + column_def);
//						log.debug("TYPE_NAME " + TYPE_NAME);
						if ((remark != null && remark.toLowerCase().equals(
								"auto_increment"))) {
							primateObj.setAutoinCrements(true);
							log.debug("The " + tableName + " " + key
									+ " auto_increment is " + "true.");
						} else {
							primateObj.setAutoinCrements(false);
							log.debug("The " + tableName + " " + key
									+ " auto_increment is " + "false.");
							log.warn("The " + tableName + " " + key
									+ " auto_increment is " + "false.");
						}

						keyObjList.add(primateObj);
					} else {
						continue;
					}
				}
			}
		}
		return keyObjList;
	}

	/**
	 * 提取数据库主键信息列表
	 * 
	 * @param con
	 *            Connection
	 * @param dbtype
	 *            oracle/mysql/..
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */

	private static List getPrimateKeys(Connection con, String dbtype,
			String tableName) throws SQLException {

		List list = null;
//		if (dbtype.equals("mysql")) {
//			list = getMySQLPKs(con, dbtype, tableName);
//		} else if (dbtype.equals("oracle")) {
//			list = getOraclePKs(con, dbtype, tableName);
//		} else {
//			list = getOtherPKs(con, dbtype, tableName);
//		}
//		list = getOtherPKs(con, dbtype, tableName);
		list = getPKs(con, dbtype, tableName);
		return list;
	}

	
	
	public static String getTableSchem(Connection con, String dbtype,
			String tableName) throws SQLException {		
		DatabaseMetaData rbmd = con.getMetaData();
		String table_schem="";		
		if (dbtype.equals("oracle") || dbtype.indexOf("db2") != -1) {
			tableName = tableName.toUpperCase();
		} else {
			tableName = tableName.toLowerCase();
		}
		ResultSet keySet = rbmd.getPrimaryKeys(con.getCatalog(), null,
				tableName);
//		 ResultSetMetaData rsmdd=keySet.getMetaData();
		while (keySet.next()) {
//			 for(int i=1;i<=rsmdd.getColumnCount();i++){
//			 log.debug(rsmdd.getColumnName(i)+" "+ keySet.getString(i));
//			 }
			
			table_schem = keySet.getString("TABLE_SCHEM");
			log.debug("The " + tableName + " TABLE_SCHEM is "
					+ table_schem);
		}
		keySet.close();
		
		
		return table_schem;
	}
	
	
	private static List getOtherPKs(Connection con, String dbtype,
			String tableName) throws SQLException {
		DatabaseMetaData rbmd = con.getMetaData();
		List keyObjList = null;
		List keyList = new ArrayList();
		String table_schem = "";
		if (dbtype.equals("oracle") || dbtype.indexOf("db2") != -1 || dbtype.indexOf("hsql") != -1) {
			tableName = tableName.toUpperCase();
		} else {
			tableName = tableName.toLowerCase();
		}
		ResultSet keySet = rbmd.getPrimaryKeys(con.getCatalog(), null,
				tableName);
		 ResultSetMetaData rsmdd=keySet.getMetaData();
		while (keySet.next()) {
			 for(int i=1;i<=rsmdd.getColumnCount();i++){
			 log.debug(rsmdd.getColumnName(i)+" "+ keySet.getString(i));
			 }
			keyList.add(keySet.getString(4).toLowerCase());
			table_schem = keySet.getString("TABLE_SCHEM");
			log.debug("The " + tableName + " PK is "
					+ keySet.getString(4).toLowerCase());
		}
		keySet.close();
		if (keyList.size() > 0) {
			keyObjList = new ArrayList();
			ResultSet keyInfo = rbmd.getColumns(null, null, tableName, null);
//			 ResultSetMetaData rsmd=keyInfo.getMetaData();
			while (keyInfo.next()) {
//				 for(int i=1;i<=rsmd.getColumnCount();i++){
//				 log.debug(rsmd.getColumnName(i)+" "+ keyInfo.getString(i));
//				 }
				String key = keyInfo.getString("COLUMN_NAME").toLowerCase();
				for (int i = 0; i < keyList.size(); i++) {
					String primate = (String) keyList.get(i);
					if (key.equals(primate)) {
						ColumnTool primateObj = new ColumnTool();
						String remark = keyInfo.getString("REMARKS");
						String column_def = keyInfo.getString("COLUMN_DEF");
						String TYPE_NAME = keyInfo.getString("TYPE_NAME");
						primateObj.setColumn(key);
						primateObj.setTable_schem(table_schem);
						// log.debug("REMARKS "+remark);
						// log.debug("COLUMN_DEF "+column_def);
						// log.debug("TYPE_NAME" +TYPE_NAME);
						//				
						if (dbtype.equals("microsoft sql server")) {
							if (TYPE_NAME.toLowerCase().indexOf("identity") != -1) {
								primateObj.setAutoinCrements(true);
								log.debug("The " + tableName
										+ " auto_increment is " + "true.");
							}

						} else if (dbtype.indexOf("db2") != -1) {
							primateObj.setAutoinCrements(true);
							log.debug("The " + tableName
									+ " auto_increment is " + "true.");
						} else {
							if ((remark != null && remark.toLowerCase().equals(
									"auto_increment"))
									|| column_def != null) {
								primateObj.setAutoinCrements(true);
								log.debug("The " + tableName
										+ " auto_increment is " + "true.");

							} else {
								primateObj.setAutoinCrements(false);
								log.debug("The " + tableName
										+ " auto_increment is " + "false.");
								log.warn("The " + tableName
										+ " auto_increment is " + "false.");
							}
						}
						keyObjList.add(primateObj);
					} else {
						continue;
					}
				}
			}
		}
		return keyObjList;
	}

	private static List getPKs(Connection con, String dbtype,
			String tableName) throws SQLException {
		DatabaseMetaData rbmd = con.getMetaData();
		List keyObjList = null;
		List keyList = new ArrayList();
		String table_schem = "";
		
		if (dbtype.equals("oracle") || dbtype.indexOf("db2") != -1) {
			tableName = tableName.toUpperCase();
		} else {
			tableName = tableName.toLowerCase();
		}
		ResultSet keySet = rbmd.getPrimaryKeys(con.getCatalog(), null,
				tableName);
//		 ResultSetMetaData rsmdd=keySet.getMetaData();
		while (keySet.next()) {
//			 for(int i=1;i<=rsmdd.getColumnCount();i++){
//			 log.debug(rsmdd.getColumnName(i)+" "+ keySet.getString(i));
//			 }
			keyList.add(keySet.getString(4).toLowerCase());
			table_schem = keySet.getString("TABLE_SCHEM");
			log.debug("The " + tableName + " PK is "
					+ keySet.getString(4).toLowerCase());
		}
		keySet.close();
		if (keyList.size() > 0) {
			keyObjList = new ArrayList();
			ResultSet keyInfo = rbmd.getColumns(null, null, tableName, null);
//			 ResultSetMetaData rsmd=keyInfo.getMetaData();
			while (keyInfo.next()) {
//				 for(int i=1;i<=rsmd.getColumnCount();i++){
//				 log.debug(rsmd.getColumnName(i)+" "+ keyInfo.getString(i));
//				 }
				String key = keyInfo.getString("COLUMN_NAME").toLowerCase();
				for (int i = 0; i < keyList.size(); i++) {
					String primate = (String) keyList.get(i);
					if (key.equals(primate)) {
						ColumnTool primateObj = new ColumnTool();
						String remark = keyInfo.getString("REMARKS");
						String column_def = keyInfo.getString("COLUMN_DEF");
						String TYPE_NAME = keyInfo.getString("TYPE_NAME");
						primateObj.setColumn(key);
						primateObj.setTable_schem(table_schem);
						// log.debug("REMARKS "+remark);
						// log.debug("COLUMN_DEF "+column_def);
						// log.debug("TYPE_NAME" +TYPE_NAME);
						//						
						if (dbtype.equals("microsoft sql server")) {
							if (TYPE_NAME.toLowerCase().indexOf("identity") != -1) {
								primateObj.setAutoinCrements(true);
								log.debug("The " + tableName
										+ " auto_increment is " + "true.");
							}

						} else if (dbtype.indexOf("db2") != -1) {
							primateObj.setAutoinCrements(true);
							log.debug("The " + tableName
									+ " auto_increment is " + "true.");
						} else if(dbtype.indexOf("mysql") != -1){
							if ((remark != null && remark.toLowerCase().equals(
									"auto_increment"))) {
								primateObj.setAutoinCrements(true);
								log.debug("The " + tableName
										+ " auto_increment is " + "true.");

							} else {
								primateObj.setAutoinCrements(false);
								log.debug("The " + tableName
										+ " auto_increment is " + "false.");								
							}
						}
						keyObjList.add(primateObj);
					} else {
						continue;
					}
				}
			}
		}
		return keyObjList;
	}

	/**
	 * 提取字段在数据库的类型
	 * 
	 * @param con
	 *            Connection
	 * @param tableName
	 *            String
	 * @throws SQLException
	 * @return List
	 */
	private static List getColumnDataTypeInDataBase(Connection con,
			String tableName) throws SQLException {
		DatabaseMetaData rbmd = con.getMetaData();
		List keyObjList = new ArrayList();
		ResultSet keySet = rbmd.getColumns(null, null, tableName, null);
		while (keySet.next()) {
			ColumnTool column = new ColumnTool();
			column.setColumn(keySet.getString("COLUMN_NAME").toLowerCase());
			column.setDatatype(keySet.getString("TYPE_NAME").toLowerCase());
			keyObjList.add(column);
		}
		return keyObjList;
	}

	/**
	 * 提取表所有字段在数据库的数据类型(缓存检查)
	 * 
	 * @param con
	 *            Connection
	 * @param t_class
	 *            Class
	 * @throws CacheException
	 * @return List
	 */
	public static List getColumnDataType(Connection con, Class t_class)
			throws CacheException, SQLException {
		String tableName = SQLHelper.getExcuteTableName(t_class);
		cp.newInstance("speedCache");
		List keyList = null;
		TableMapping tb = null;
		Map map = (Map) cp.get("tableTypeList");
		if (map == null) {
			map = new HashMap();
			tb = new TableMapping();
			tb.setTablename(tableName);
			keyList = getColumnDataTypeInDataBase(con, tableName);
			tb.setKeyColum(keyList);
			map.put(tableName, tb);
			cp.put("tableTypeList", map);
		} else {
			if (map.containsKey(tableName)) {
				tb = (TableMapping) map.get(tableName);
				keyList = tb.getKeyColum();
			} else {
				tb = new TableMapping();
				tb.setTablename(tableName);
				keyList = getColumnDataTypeInDataBase(con, tableName);
				tb.setKeyColum(keyList);
				map.put(tableName, tb);
			}

		}
		return keyList;
	}

	/**
	 * 提取要操作的字段类型
	 * 
	 * @param keyList
	 *            List
	 * @param t_class
	 *            Class
	 * @return String[]
	 */
	public static String[] getDataTypeForExecute(List keyList, List typeList) {
		String[] types = new String[keyList.size()];
		for (int i = 0; i < keyList.size(); i++) {
			String column_ = (String) keyList.get(i);
			for (int j = 0; j < typeList.size(); j++) {
				ColumnTool ct = (ColumnTool) typeList.get(j);
				if (ct.getColumn().toLowerCase().equals(column_.toLowerCase())) {
					types[i] = ct.getDatatype();
				} else {
					continue;
				}
			}
		}
		return types;
	}

}