JdbcUtil

  package com.todaytech.pwp.core.exception;








  public class BizException
    extends RuntimeException
  {
    public BizException() {}








    public BizException(String message)
    {
      super(message);
    }






    public BizException(String message, Throwable cause)
    {
      super(message, cause);
    }






    public BizException(Throwable cause)
    {
      super(cause);
    }







    public static void throwWhenFalse(boolean maybeFalse, String msgToUsr, Throwable cause)
      throws BizException
    {
      if (!maybeFalse) {
        throw new BizException(msgToUsr, cause);
      }
    }






    public static void throwWhenFalse(boolean maybeFalse, String msgToUsr)
      throws BizException
    {
      if (!maybeFalse) {
        throw new BizException(msgToUsr);
      }
    }







    public static void throwWhenTrue(boolean maybeTrue, String msgToUsr, Throwable cause)
      throws BizException
    {
      if (!maybeTrue) {
        throw new BizException(msgToUsr, cause);
      }
    }





    public static void throwWhenTrue(boolean maybeTrue, String msgToUsr)
      throws BizException
    {
      if (maybeTrue) {
        throw new BizException(msgToUsr);
      }
    }







    public static void throwWhenNull(Object objMayBeNull, String msgToUsr, Throwable cause)
      throws BizException
    {
      if (objMayBeNull == null) {
        throw new BizException(msgToUsr, cause);
      }
    }






    public static void throwWhenNull(Object objMayBeNull, String msgToUsr)
      throws BizException
    {
      if (objMayBeNull == null) {
        throw new BizException(msgToUsr);
      }
    }
  }

  package com.todaytech.pwp.core.exception;







  public class SysException
    extends RuntimeException
  {
    public SysException() {}







    public SysException(String message)
    {
      super(message);
    }





    public SysException(String message, Throwable cause)
    {
      super(message, cause);
    }





    public SysException(Throwable cause)
    {
      super(cause);
    }
  }

/*
 * Copyright 2002-2012 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.jdbc.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;

/**
 * Simple implementation of the standard JDBC {@link javax.sql.DataSource} interface,
 * configuring the plain old JDBC {@link java.sql.DriverManager} via bean properties, and
 * returning a new {@link java.sql.Connection} from every {@code getConnection} call.
 *
 * <p><b>NOTE: This class is not an actual connection pool; it does not actually
 * pool Connections.</b> It just serves as simple replacement for a full-blown
 * connection pool, implementing the same standard interface, but creating new
 * Connections on every call.
 *
 * <p>Useful for test or standalone environments outside of a J2EE container, either
 * as a DataSource bean in a corresponding ApplicationContext or in conjunction with
 * a simple JNDI environment. Pool-assuming {@code Connection.close()} calls will
 * simply close the Connection, so any DataSource-aware persistence code should work.
 *
 * <p><b>NOTE: Within special class loading environments such as OSGi, this class
 * is effectively superseded by {@link SimpleDriverDataSource} due to general class
 * loading issues with the JDBC DriverManager that be resolved through direct Driver
 * usage (which is exactly what SimpleDriverDataSource does).</b>
 *
 * <p>In a J2EE container, it is recommended to use a JNDI DataSource provided by
 * the container. Such a DataSource can be exposed as a DataSource bean in a Spring
 * ApplicationContext via {@link org.springframework.jndi.JndiObjectFactoryBean},
 * for seamless switching to and from a local DataSource bean like this class.
 * For tests, you can then either set up a mock JNDI environment through Spring's
 * {@link org.springframework.mock.jndi.SimpleNamingContextBuilder}, or switch the
 * bean definition to a local DataSource (which is simpler and thus recommended).
 *
 * <p>If you need a "real" connection pool outside of a J2EE container, consider
 * <a href="http://jakarta.apache.org/commons/dbcp">Apache's Jakarta Commons DBCP</a>
 * or <a href="http://sourceforge.net/projects/c3p0">C3P0</a>.
 * Commons DBCP's BasicDataSource and C3P0's ComboPooledDataSource are full
 * connection pool beans, supporting the same basic properties as this class
 * plus specific settings (such as minimal/maximal pool size etc).
 *
 * @author Juergen Hoeller
 * @since 14.03.2003
 * @see SimpleDriverDataSource
 */
public class DriverManagerDataSource extends AbstractDriverBasedDataSource {

	/**
	 * Constructor for bean-style configuration.
	 */
	public DriverManagerDataSource() {
	}

	/**
	 * Create a new DriverManagerDataSource with the given JDBC URL,
	 * not specifying a username or password for JDBC access.
	 * @param url the JDBC URL to use for accessing the DriverManager
	 * @see java.sql.DriverManager#getConnection(String)
	 */
	public DriverManagerDataSource(String url) {
		setUrl(url);
	}

	/**
	 * Create a new DriverManagerDataSource with the given standard
	 * DriverManager parameters.
	 * @param url the JDBC URL to use for accessing the DriverManager
	 * @param username the JDBC username to use for accessing the DriverManager
	 * @param password the JDBC password to use for accessing the DriverManager
	 * @see java.sql.DriverManager#getConnection(String, String, String)
	 */
	public DriverManagerDataSource(String url, String username, String password) {
		setUrl(url);
		setUsername(username);
		setPassword(password);
	}

	/**
	 * Create a new DriverManagerDataSource with the given JDBC URL,
	 * not specifying a username or password for JDBC access.
	 * @param url the JDBC URL to use for accessing the DriverManager
	 * @param conProps JDBC connection properties
	 * @see java.sql.DriverManager#getConnection(String)
	 */
	public DriverManagerDataSource(String url, Properties conProps) {
		setUrl(url);
		setConnectionProperties(conProps);
	}

	/**
	 * Create a new DriverManagerDataSource with the given standard
	 * DriverManager parameters.
	 * @param driverClassName the JDBC driver class name
	 * @param url the JDBC URL to use for accessing the DriverManager
	 * @param username the JDBC username to use for accessing the DriverManager
	 * @param password the JDBC password to use for accessing the DriverManager
	 * @deprecated since Spring 2.5. DriverManagerDataSource is primarily
	 * intended for accessing <i>pre-registered</i> JDBC drivers.
	 * If you need to register a new driver, consider using
	 * {@link SimpleDriverDataSource} instead.
	 */
	@Deprecated
	public DriverManagerDataSource(String driverClassName, String url, String username, String password) {
		setDriverClassName(driverClassName);
		setUrl(url);
		setUsername(username);
		setPassword(password);
	}


	/**
	 * Set the JDBC driver class name. This driver will get initialized
	 * on startup, registering itself with the JDK's DriverManager.
	 * <p><b>NOTE: DriverManagerDataSource is primarily intended for accessing
	 * <i>pre-registered</i> JDBC drivers.</b> If you need to register a new driver,
	 * consider using {@link SimpleDriverDataSource} instead. Alternatively, consider
	 * initializing the JDBC driver yourself before instantiating this DataSource.
	 * The "driverClassName" property is mainly preserved for backwards compatibility,
	 * as well as for migrating between Commons DBCP and this DataSource.
	 * @see java.sql.DriverManager#registerDriver(java.sql.Driver)
	 * @see SimpleDriverDataSource
	 */
	public void setDriverClassName(String driverClassName) {
		Assert.hasText(driverClassName, "Property 'driverClassName' must not be empty");
		String driverClassNameToUse = driverClassName.trim();
		try {
			Class.forName(driverClassNameToUse, true, ClassUtils.getDefaultClassLoader());
		}
		catch (ClassNotFoundException ex) {
			throw new IllegalStateException("Could not load JDBC driver class [" + driverClassNameToUse + "]", ex);
		}
		if (logger.isInfoEnabled()) {
			logger.info("Loaded JDBC driver: " + driverClassNameToUse);
		}
	}


	@Override
	protected Connection getConnectionFromDriver(Properties props) throws SQLException {
		String url = getUrl();
		if (logger.isDebugEnabled()) {
			logger.debug("Creating new JDBC DriverManager Connection to [" + url + "]");
		}
		return getConnectionFromDriverManager(url, props);
	}

	/**
	 * Getting a Connection using the nasty static from DriverManager is extracted
	 * into a protected method to allow for easy unit testing.
	 * @see java.sql.DriverManager#getConnection(String, java.util.Properties)
	 */
	protected Connection getConnectionFromDriverManager(String url, Properties props) throws SQLException {
		return DriverManager.getConnection(url, props);
	}

}

package com.todaytech.czjd.metadata.superviseObject.application.impl;

import com.todaytech.pwp.core.exception.BizException;
import com.todaytech.pwp.core.exception.SysException;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;


/**
 * Created by IntelliJ IDEA.
 * User: A41
 * Date: 12-12-13
 * Time: 上午9:45
 * To change this template use File | Settings | File Templates.
 */
public class JdbcUtil {

    private static JdbcUtil instance = null;
    private static DataSource ds = null;
    private Map<String, Object> tableMap = new HashMap();

    public static final String KEY_ID = "id";
    public static final String KEY_PK_NAME = "PK_NAME";
    public static final String KEY_TABLE_NAME = "table_name";
    public static final String KEY_FIELD_NAME = "fieldName";
    public static final String KEY_FIELD_NAME_CN = "fieldNameCn";
    public static final String KEY_TYPE_NAME = "TypeName";
    public static final String KEY_DATA_TYPE = "dataType";
    public static final String KEY_LENGTH = "length";
    public static final String KEY_POINT = "point";
    public static final String KEY_NO = "no";
    public static final String KEY_DEFAULT_VALUE = "defaultVal";
    public static final String KEY_IS_NULL = "isNull";
    public static final String KEY_IS_PK = "isPk";
    public static final String KEY_COLUMN_LIST = "columnList";
    public static final String KEY_SUPERVISEPARAM = "superviseParam";


    public static final String KEY_NAME = "name";
    public static final String KEY_TYPE = "type";
    public static final String KEY_REMARK = "remark";
    public static final String KEY_STATUS = "status";
    public static final String KEY_TYPE_VIEW = "VIEW";
    public static final String KEY_TABLE_TYPE_SYNONYM="SYNONYM";//表的类型：同义词；
    public static final String KEY_TABLE_TYPE_TABLE="TABLE";//表的类型：同义词；
    public static final String KEY_TABLE_TYPE_VIEW="VIEW";//表的类型：同义词；


    public static  Map jdbcTemplateMap=null;


    public JdbcUtil() {
        super();
    }

    /**
     * 单例方式创建对象
     *
     * @return
     */
    public static JdbcUtil getInstance() {
        if (instance == null) {
            synchronized (JdbcUtil.class) {
                if (instance == null) {
                    instance = new JdbcUtil();
                }
            }
        }
        return instance;
    }


    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            if(ThreadDataSource.get().getDataSource() instanceof DriverManagerDataSource){
                ds = (DriverManagerDataSource) ThreadDataSource.get().getDataSource();
                conn = ds.getConnection();
            }else{
                ds = (DataSource) ThreadDataSource.get().getDataSource();
                conn = ds.getConnection();
            }

        } catch (Exception e) {
            BizException.throwWhenFalse(false, "数据库连接不上，请检查数据源配置或者联系管理员！");
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 利用表名和数据库用户名查询出该表对应的属性、字段等。
     *
     * @param tableName 表名
     * @return
     * @throws Exception
     */
    public Map getTableProperty(String tableName,String tableType) throws Exception {
       // BizException.throwWhenTrue(StringUtils.isBlank(schema),"jd_db表remark字段不能为空，应填入可查询表结构的用户名");
        Connection conn = getConnection();
        ResultSet rsColumn = null;
        ResultSet rsPK = null;
        List columnList = new ArrayList<Map>();
        try {
           String  schema = getDbUser(conn,tableName,tableType);
            DatabaseMetaData metaData = conn.getMetaData();
            rsPK = metaData.getPrimaryKeys(null,schema, tableName.toUpperCase());
            String pk_column_name = null;
            while (rsPK.next()) {
                pk_column_name = rsPK.getString("column_name");
            }
            tableMap.put(KEY_PK_NAME, pk_column_name);
            tableMap.put(KEY_TABLE_NAME, tableName);
            rsColumn = metaData.getColumns(null, schema, tableName.toUpperCase(), null);
            while (rsColumn.next()) {
                Map columnMap = new LinkedHashMap();
                columnMap.put(KEY_FIELD_NAME, rsColumn.getString("COLUMN_NAME"));
                columnMap.put(KEY_FIELD_NAME_CN, getColumnRemark(conn,tableName,rsColumn.getString("COLUMN_NAME"))); //备注当作是中文名。
//                columnMap.put(KEY_TYPE_NAME, rsColumn.getString("TYPE_NAME")); //字段类型名称(例如：VACHAR2)
                columnMap.put(KEY_DATA_TYPE, DaoUtils.toMetaType(rsColumn.getInt("DATA_TYPE"), rsColumn.getInt("column_size"), rsColumn.getInt("decimal_digits")));//字段数据类型(对应java.sql.Types中的常量)
                columnMap.put(KEY_LENGTH, rsColumn.getInt("column_size"));
//                columnMap.put(KEY_POINT, rsColumn.getInt("decimal_digits"));
//                columnMap.put(KEY_DEFAULT_VALUE, rsColumn.getString("column_def"));
//                columnMap.put(KEY_NO, rsColumn.getInt("ordinal_position"));
//                columnMap.put(KEY_IS_NULL, rsColumn.getString("nullable"));
                if (rsColumn.getString("COLUMN_NAME") != null && (rsColumn.getString("COLUMN_NAME").equals(tableMap.get(KEY_PK_NAME)))) {
                    columnMap.put(KEY_IS_PK, "1");
                } else {
                    columnMap.put(KEY_IS_PK, "0");
                }
//                columnMap.put("isUnique", rsColumn.getString(""));//暂时不拿

                columnList.add(columnMap);
            }
            tableMap.put(KEY_COLUMN_LIST, columnList);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if(rsColumn!=null){
                rsColumn.close();
            }
            if(rsPK!=null){
                rsPK.close();
            }
            if(conn!=null){
            conn.close();
            }
        }
        return tableMap;
    }

    private  String getDbUser(Connection con,String tableName, String tableType) throws Exception {
        BizException.throwWhenNull(tableName,"表名为空，请检查！");
        String  sql = "",res=null;
        if("1".equals(tableType)){
        	sql = "select OWNER from ALL_TABLES where TABLE_NAME= ?";
        }
        else if("2".equals(tableType)){
        	sql = "select OWNER from ALL_VIEWS where VIEW_NAME= ?";
        }
        else if("3".equals(tableType)){
        	sql = "SELECT t.tablespace_name FROM USER_TABLES t where t.table_name= ?";
//        	sql = "Select  table_owner from user_synonyms where synonym_name= ?";
        }else{
        	BizException.throwWhenNull(tableName,"表类型不确定");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
             ps = con.prepareStatement(sql);
             ps.setString(1, tableName.toUpperCase());
             rs = ps.executeQuery();

            while(rs.next()){
             res = rs.getString(1);
            }

        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
        }
        BizException.throwWhenNull(res,"数据库用户为空，请检查表:"+tableName+" 是否存在或者配错了");
        return res;
    }


    public List getAllTableByDb() throws Exception {
        Connection conn = getConnection();
        ResultSet rsAllSynonym = null;
        ResultSet rsAllView = null;
        ResultSet rsAllTable = null;
        List tabViewList = new ArrayList();
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            rsAllSynonym = metaData.getTables(null,metaData.getUserName() , null, new String[]{KEY_TABLE_TYPE_SYNONYM});
            rsAllView = metaData.getTables(null,metaData.getUserName() , null, new String[]{KEY_TABLE_TYPE_VIEW});
            rsAllTable = metaData.getTables(null,metaData.getUserName() , null, new String[]{KEY_TABLE_TYPE_TABLE});
            while (rsAllSynonym.next()) {
                Map valueMap = new HashMap();
                valueMap.put(KEY_NAME, rsAllSynonym.getString("TABLE_NAME"));  //表名
                valueMap.put(KEY_TYPE, rsAllSynonym.getString("TABLE_TYPE"));  //表类型
//                valueMap.put(KEY_REMARK, rsAllTable.getString("REMARKS"));   //表备注
               if(KEY_TABLE_TYPE_SYNONYM.equals( rsAllSynonym.getString("TABLE_TYPE"))){
                   valueMap.put(KEY_REMARK,rsAllSynonym.getString("TABLE_NAME") );   //表备注
               }
                else{
                   valueMap.put(KEY_REMARK, getTableComment(conn, rsAllSynonym.getString("TABLE_NAME")));   //表备注
               }
                tabViewList.add(valueMap);
            }
            while (rsAllTable.next()) {
            	Map valueMap = new HashMap();
            	valueMap.put(KEY_NAME, rsAllTable.getString("TABLE_NAME"));  //表名
            	valueMap.put(KEY_TYPE, rsAllTable.getString("TABLE_TYPE"));  //表类型
            	if(KEY_TABLE_TYPE_TABLE.equals( rsAllTable.getString("TABLE_TYPE"))){
            		valueMap.put(KEY_REMARK,rsAllTable.getString("TABLE_NAME") );   //表备注
            	}
            	else{
            		valueMap.put(KEY_REMARK, getTableComment(conn, rsAllTable.getString("TABLE_NAME")));   //表备注
            	}
            	tabViewList.add(valueMap);
            }
            while (rsAllView.next()) {
            	Map valueMap = new HashMap();
            	valueMap.put(KEY_NAME, rsAllView.getString("TABLE_NAME"));  //表名
            	valueMap.put(KEY_TYPE, rsAllView.getString("TABLE_TYPE"));  //表类型
//                valueMap.put(KEY_REMARK, rsAllTable.getString("REMARKS"));   //表备注
            	if(KEY_TABLE_TYPE_VIEW.equals( rsAllView.getString("TABLE_TYPE"))){
            		valueMap.put(KEY_REMARK,rsAllView.getString("TABLE_NAME") );   //表备注
            	}
            	else{
            		valueMap.put(KEY_REMARK, getTableComment(conn, rsAllView.getString("TABLE_NAME")));   //表备注
            	}
            	tabViewList.add(valueMap);
            }

            return tabViewList;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            conn.close();
            rsAllSynonym.close();
        }
    }

    public String getTableComment(Connection conn,String tableName) {
        String comment=" ";
        try {
            PreparedStatement ps = conn.prepareStatement("SELECT COMMENTS FROM user_tab_comments WHERE table_name = ?");
            ps.setString(1, tableName);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
                comment= rs.getString("COMMENTS");
            else {
                throw new SysException("表不存在");
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            throw new SysException(e.getMessage(), e);
        }
        return comment;
    }

    private String getColumnRemark(Connection conn,String tableName, String columnName)
  {
    PreparedStatement comments = null;
    try {
      comments =conn.prepareStatement("select comments from USER_COL_COMMENTS where table_name= ?  and column_name= ?");
      comments.setString(1, tableName);
      comments.setString(2, columnName);
      ResultSet rc = comments.executeQuery();
      String remark;
      if (rc.next())
        remark = rc.getString(1);
      else {
        remark = columnName;
      }

      if(remark == null || "".equals(remark.trim())){ remark =columnName;}

      String str1 = remark;
      return str1;
    }
    catch (SQLException e)
    {
      throw new SysException(e.getMessage(), e.getCause());
    } finally {
      if (comments != null)
        try {
          comments.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
//    throw localObject;
  }


}