/**
* 获取数据库中列的描述
*
* @param tableName
* @param session
* @return
* @throws SQLException
*/
public static Map<String, ColumnMeta> getColumnMetadataFormDataBase(
String schemaName, String tableName, Session session) throws SQLException {
Connection conn =
SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getColumns(null, schemaName, tableName, "%");
ColumnMeta columnMeta;
Map<String, ColumnMeta> columnMap = new HashMap<String, ColumnMeta>();
while (rs.next()) {
columnMeta = new ColumnMeta();
columnMeta.setTableName(rs.getString("COLUMN_NAME").toLowerCase());
columnMeta.setColumnName(rs.getString("COLUMN_NAME").toLowerCase());
columnMeta.setColunmType(
dbTableHandle.getMatchClassTypeByDataType(
rs.getString("TYPE_NAME"), rs.getInt("DECIMAL_DIGITS")));
columnMeta.setColumnSize(rs.getInt("COLUMN_SIZE"));
columnMeta.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
columnMeta.setIsNullable(rs.getInt("NULLABLE") == 1 ? "Y" : "N");
columnMeta.setComment(rs.getString("REMARKS"));
columnMeta.setFieldDefault(
judgeIsNumber(rs.getString("COLUMN_DEF")) == null
? ""
: judgeIsNumber(rs.getString("COLUMN_DEF")));
logger.info(
"getColumnMetadataFormDataBase --->COLUMN_NAME:"
+ rs.getString("COLUMN_NAME")
+ " TYPE_NAME :"
+ rs.getString("TYPE_NAME")
+ " DECIMAL_DIGITS:"
+ rs.getInt("DECIMAL_DIGITS")
+ " COLUMN_SIZE:"
+ rs.getInt("COLUMN_SIZE"));
columnMap.put(rs.getString("COLUMN_NAME").toLowerCase(), columnMeta);
}
return columnMap;
}
public List<String> updateTable(CgFormHeadEntity table, Session session) throws DBException {
// StringBuilder sb = new StringBuilder();
String tableName =
DbTableUtil.getDataType(session).equals("ORACLE")
? table.getTableName().toUpperCase()
: table.getTableName();
String alterTable = "alter table " + tableName + " ";
List<String> strings = new ArrayList<String>();
// 对表的修改列和删除列的处理，解决hibernate没有该机制
try {
Map<String, ColumnMeta> dataBaseColumnMetaMap =
getColumnMetadataFormDataBase(null, tableName, session);
Map<String, ColumnMeta> cgFormColumnMetaMap = getColumnMetadataFormCgForm(table);
Map<String, String> newAndOldFieldMap = getNewAndOldFieldName(table);
for (String columnName : cgFormColumnMetaMap.keySet()) {
if (!dataBaseColumnMetaMap.containsKey(
columnName)) { // 表如果不存在该列，则要对表做修改、增加、删除该列动作 此处无法处理删除的列，因为在这个循环中无法获得该列
// 如果旧列中包含这个列名，说明是修改名称的
ColumnMeta cgFormColumnMeta = cgFormColumnMetaMap.get(columnName);
if (newAndOldFieldMap.containsKey(columnName)
&& (dataBaseColumnMetaMap.containsKey(newAndOldFieldMap.get(columnName)))) {
ColumnMeta dataColumnMeta =
dataBaseColumnMetaMap.get(newAndOldFieldMap.get(columnName));
if (DbTableUtil.getDataType(session).equals("SQLSERVER")) {
// sqlserver 修改类名称需要调用存储过程
strings.add(getReNameFieldName(cgFormColumnMeta));
} else {
strings.add(alterTable + getReNameFieldName(cgFormColumnMeta));
}
// 执行完成之后修改成一致 fildname和oldfieldname
updateFieldName(columnName, cgFormColumnMeta.getColumnId(), session);
// 修改表名之后继续判断值有没有变化,有变化继续修改值
if (!dataColumnMeta.equals(cgFormColumnMeta)) {
strings.add(alterTable + getUpdateColumnSql(cgFormColumnMeta, dataColumnMeta));
if (DbTableUtil.getDataType(session).equals("POSTGRESQL")) {
strings.add(alterTable + getUpdateSpecialSql(cgFormColumnMeta, dataColumnMeta));
}
}
// 判断注释是不是相同,修改注释
if (!dataColumnMeta.equalsComment(cgFormColumnMeta)) {
strings.add(getCommentSql(cgFormColumnMeta));
}
} else { // 不包含就是要增加
strings.add(alterTable + getAddColumnSql(cgFormColumnMeta));
if (StringUtils.isNotEmpty(cgFormColumnMeta.getComment())) {
strings.add(getCommentSql(cgFormColumnMeta));
}
}
} else { // 已经存在的判断是否修改了类型长度。。
// 判断是否类型、长度、是否为空、精度被修改，如果有修改则处理修改
ColumnMeta dataColumnMeta = dataBaseColumnMetaMap.get(columnName);
ColumnMeta cgFormColumnMeta = cgFormColumnMetaMap.get(columnName);
// 如果不相同，则表示有变化，则需要修改
if (!dataColumnMeta.equals(cgFormColumnMeta)) {
strings.add(alterTable + getUpdateColumnSql(cgFormColumnMeta, dataColumnMeta));
}
if (!dataColumnMeta.equalsComment(cgFormColumnMeta)) {
strings.add(getCommentSql(cgFormColumnMeta));
}
}
}
// 删除数据库的列
// 要判断这个列不是修改的
for (String columnName : dataBaseColumnMetaMap.keySet()) {
if ((!cgFormColumnMetaMap.containsKey(columnName.toLowerCase()))
&& (!newAndOldFieldMap.containsValue(columnName.toLowerCase()))) {
strings.add(alterTable + getDropColumnSql(columnName));
}
}
} catch (SQLException e1) {
throw new RuntimeException();
}
logger.info(strings.toString());
return strings;
}
/**
* 返回cgForm中列的描述信息
*
* @param table
* @return
*/
public static Map<String, ColumnMeta> getColumnMetadataFormCgForm(CgFormHeadEntity table) {
Map<String, ColumnMeta> map = new HashMap<String, ColumnMeta>();
List<CgFormFieldEntity> cgFormFieldEntities = table.getColumns();
ColumnMeta columnMeta;
for (CgFormFieldEntity cgFormFieldEntity : cgFormFieldEntities) {
columnMeta = new ColumnMeta();
columnMeta.setTableName(table.getTableName().toLowerCase());
columnMeta.setColumnId(cgFormFieldEntity.getId());
columnMeta.setColumnName(cgFormFieldEntity.getFieldName().toLowerCase());
columnMeta.setColumnSize(cgFormFieldEntity.getLength());
columnMeta.setColunmType(cgFormFieldEntity.getType().toLowerCase());
columnMeta.setIsNullable(cgFormFieldEntity.getIsNull());
columnMeta.setComment(cgFormFieldEntity.getContent());
columnMeta.setDecimalDigits(cgFormFieldEntity.getPointLength());
columnMeta.setFieldDefault(judgeIsNumber(cgFormFieldEntity.getFieldDefault()));
columnMeta.setPkType(table.getJformPkType() == null ? "UUID" : table.getJformPkType());
columnMeta.setOldColumnName(
cgFormFieldEntity.getOldFieldName() != null
? cgFormFieldEntity.getOldFieldName().toLowerCase()
: null);
logger.info(
"getColumnMetadataFormCgForm ---->COLUMN_NAME:"
+ cgFormFieldEntity.getFieldName().toLowerCase()
+ " TYPE_NAME:"
+ cgFormFieldEntity.getType().toLowerCase()
+ " DECIMAL_DIGITS:"
+ cgFormFieldEntity.getPointLength()
+ " COLUMN_SIZE:"
+ cgFormFieldEntity.getLength());
map.put(cgFormFieldEntity.getFieldName().toLowerCase(), columnMeta);
}
return map;
}