1：增加ts.dr字段，先判断ts.dr字段是否存在，其中ts字段插入的是日期，默认值为当前插入的时间，dr字段是数值型，默认值为0
* 增加ts/dr字段
*
* @param tableList
*/
private void addTSAndDRColumn(List<String> tableList) {
// 先判断是否有ts,dr列
String dsname = getDsName();
boolean addTS = false;
boolean addDR = false;
List<String> sqlList = new ArrayList<String>();
for (int i = 0; i < tableList.size(); i++) {
String addSql = null;
String tableName = tableList.get(i);
addTS = !isColumnExist(dsname, tableName, "ts");
addDR = !isColumnExist(dsname, tableName, "dr");
if (addTS) {
if (addDR) {
addSql = addTSAndDR(tableName);
} else {
addSql = addTS(tableName);
}
} else {
if (addDR) {
addSql = addDR(tableName);
}
}
if (!StringUtils.isEmpty(addSql)) {
sqlList.add(addSql);
}
}
String[] sqls = sqlList.toArray(new String[0]);
if (!ArrayUtils.isEmpty(sqls)) {
SqlExecAction_Client.execBatch(dsname, sqls);// 批量处理
}
}
/**
* 判断列是否存在。思路：针对sqlserver/oracle/postgrel做了特殊处理，其他类型的数据库，则按照字段执行查询，如果出错，则说明字段不存在
*
* @param dsname
* 数据源
* @param tableName
* 表名
* @param column
* 列名
* @return
*/
private boolean isColumnExist(String dsname, String tableName, String column) {
boolean isExist = true;
DbType dbType = SmartUtilities.getDbType(dsname);
if (dbType == DbType.SQLSERVER || dbType == DbType.ORACLE
|| dbType == DbType.POSTGRESQL) {
return isColumnExist(dsname, tableName, column, dbType);
} else {
try {
String sql = "select count(" + column + ") from " + tableName;
SqlExecAction_Client.execQuery(dsname, sql,
new ResultSetProcessor() {
private static final long serialVersionUID = -3528176693847689283L;
@Override
public Object handleResultSet(ResultSet rs)
throws SQLException {
while (rs.next()) {
return rs.getInt(1);
}
return 0;
}
});
} catch (Exception e) {// 如果有异常，则说明字段不存在
AppDebug.error(e);
isExist = false;
}
}
return isExist;
}
private boolean isColumnExist(String dsname, String tableName,
String column, DbType dbType) {
String sql = null;
if (dbType == DbType.SQLSERVER) {
sql = "select count(*) from syscolumns where id=object_id('"
+ tableName.toLowerCase() + "') and name='" + column + "'";
} else if (dbType == DbType.ORACLE) {
sql = "select count(*) from cols where table_name=upper('" + tableName
+ "') and column_name=upper('" + column + "')";
} else if (dbType == DbType.POSTGRESQL) {
sql = "select count(*) from pg_attribute where attname='"
+ column
+ "' and attrelid=(select oid from pg_class where relname='"
+ tableName.toLowerCase() + "')";
}
Integer count = (Integer) SqlExecAction_Client.execQuery(dsname, sql,
new ResultSetProcessor() {
private static final long serialVersionUID = -3528176693847689283L;
@Override
public Object handleResultSet(ResultSet rs)
throws SQLException {
while (rs.next()) {
return rs.getInt(1);
}
return 0;
}
});
int icount = count.intValue();
return (icount != 0);
}
/**
* @param tableName
* @return
*/
private String addTSAndDR(String tableName) {
String sql = "alter table " + tableName
+ " add (ts CHAR(19),dr numeric(10,0) DEFAULT 0)";
String dsName = FileStorageConfigFactory.getBaseConfig().getDsName();
DbType dbType = SmartUtilities.getDbType(dsName);
if (dbType == DbType.SQLSERVER) {
sql = "alter table "
+ tableName
+ " add ts NCHAR(19) COLLATE Chinese_PRC_CI_AS DEFAULT CONVERT(nchar(19),GETDATE(),20),dr SMALLINT DEFAULT 0";
} else if (dbType == DbType.ORACLE) {
sql = "alter table "
+ tableName
+ " add (ts CHAR(19) DEFAULT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),dr numeric(10,0) DEFAULT 0)";
} else if (dbType == DbType.DB2) {
sql = "alter table "
+ tableName
+ " add COLUMN ts CHAR(19) DEFAULT char(current timestamp,'yyyy-mm-dd hh24:mi:ss')"
+ " add COLUMN dr SMALLINT DEFAULT 0)";
} else if (dbType == DbType.POSTGRESQL) {
sql = "alter table "
+ tableName
+ " add ts CHAR(19) DEFAULT to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),add dr numeric(10,0) DEFAULT 0";
}
return sql;
}
private String addTS(String tableName) {
String sql = "alter table " + tableName + " add ts CHAR(19)";
String dsName = FileStorageConfigFactory.getBaseConfig().getDsName();
DbType dbType = SmartUtilities.getDbType(dsName);
if (dbType == DbType.SQLSERVER) {
sql = "alter table "
+ tableName
+ " add ts NCHAR(19) COLLATE Chinese_PRC_CI_AS DEFAULT CONVERT(nchar(19),GETDATE(),20)";
} else if (dbType == DbType.ORACLE) {
sql = "alter table "
+ tableName
+ " add ts CHAR(19) DEFAULT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')";
} else if (dbType == DbType.DB2) {
sql = "alter table "
+ tableName
+ " add COLUMN ts CHAR(19) DEFAULT char(current timestamp,'yyyy-mm-dd hh24:mi:ss')";
} else if (dbType == DbType.POSTGRESQL) {
sql = "alter table "
+ tableName
+ " add ts CHAR(19) DEFAULT to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss')";
}
return sql;
}
private String addDR(String tableName) {
String sql = "alter table " + tableName
+ " add dr numeric(10,0) DEFAULT 0";
String dsName = FileStorageConfigFactory.getBaseConfig().getDsName();
DbType dbType = SmartUtilities.getDbType(dsName);
if (dbType == DbType.SQLSERVER) {
sql = "alter table " + tableName + " add dr SMALLINT DEFAULT 0";
} else if (dbType == DbType.DB2) {
sql = "alter table " + tableName
+ " add COLUMN dr SMALLINT DEFAULT 0)";
}
return sql;
}
