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;
} }