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

?

轉載于:https://www.cnblogs.com/chenfei0801/p/3477617.html

總結

以上是生活随笔為你收集整理的插入ts以及判断列是否存在（支持多数据库）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。