/** * 为数据表自动增减字段 * * @param dao Dao实例 * @param klass 映射Pojo * @param add 是否允许添加 * @param del 是否允许删除 * @param tableName 动态表名上下文 */ public static void migration( Dao dao, final Class<?> klass, final boolean add, final boolean del, Object tableName) { final AbstractJdbcExpert expert = (AbstractJdbcExpert) ((NutDao) dao).getJdbcExpert(); if (tableName != null && Strings.isNotBlank(tableName.toString())) { dao = ext(dao, tableName); } final Entity<?> en = dao.getEntity(klass); if (!dao.exists(klass)) return; final List<Sql> sqls = new ArrayList<Sql>(); final Set<String> _indexs = new HashSet<String>(); final boolean sqlAddNeedColumn = !dao.meta().isOracle(); final boolean isCanComment = dao.meta().isMySql(); dao.run( new ConnCallback() { public void invoke(Connection conn) throws Exception { expert.setupEntityField(conn, en); Statement stat = null; ResultSet rs = null; ResultSetMetaData meta = null; try { // 获取数据库元信息 stat = conn.createStatement(); rs = stat.executeQuery("select * from " + en.getTableName() + " where 1 != 1"); meta = rs.getMetaData(); Set<String> columnNames = new HashSet<String>(); int columnCount = meta.getColumnCount(); for (int i = 1; i <= columnCount; i++) { columnNames.add(meta.getColumnName(i).toLowerCase()); } for (MappingField mf : en.getMappingFields()) { if (mf.isReadonly()) continue; String colName = mf.getColumnName(); if (columnNames.contains(colName.toLowerCase())) { columnNames.remove(colName.toLowerCase()); continue; } if (add) { log.infof("add column[%s] to table[%s]", mf.getColumnName(), en.getTableName()); StringBuilder sb = new StringBuilder("ALTER TABLE "); sb.append(meta.getTableName(1)).append(" ADD "); if (sqlAddNeedColumn) sb.append("COLUMN "); sb.append(colName).append(" ").append(expert.evalFieldType(mf)); if (mf.isUnsigned()) { sb.append(" UNSIGNED"); } if (mf.isNotNull()) { sb.append(" NOT NULL"); } if (mf.getColumnType() == ColType.TIMESTAMP) { if (mf.hasDefaultValue()) { sb.append(" ").append(mf.getDefaultValue(null).replaceAll("@", "@@")); } else { if (mf.isNotNull()) { sb.append(" DEFAULT 0"); } else { sb.append(" NULL DEFAULT NULL"); } } } else { if (mf.hasDefaultValue()) expert.addDefaultValue(sb, mf); } if (mf.hasColumnComment() && isCanComment) { sb.append(" COMMENT '").append(mf.getColumnComment()).append("'"); } sb.append(';'); Sql sql = Sqls.create(sb.toString()); sqls.add(sql); } } if (del) { for (String colName : columnNames) { log.infof("del column[%s] from table[%s]", colName, en.getTableName()); Sql sql = Sqls.create("ALTER table $table DROP column $name"); sql.vars().set("table", en.getTableName()); sql.vars().set("name", colName); sqls.add(sql); } } // show index from mytable; String showIndexs = "show index from " + en.getTableName(); PreparedStatement ppstat = conn.prepareStatement(showIndexs); ResultSet rest = ppstat.executeQuery(); while (rest.next()) { String index = rest.getString(3); _indexs.add(index); } } catch (SQLException e) { if (log.isDebugEnabled()) log.debugf("migration Table '%s' fail!", en.getTableName(), e); } // Close ResultSet and Statement finally { Daos.safeClose(stat, rs); } } }); for (Sql sql : sqls) { dao.execute(sql); } // 创建索引 List<Sql> indexsSql = createIndexs(dao, en, _indexs, tableName); if (!Lang.isEmpty(indexsSql)) { dao.execute(indexsSql.toArray(new Sql[0])); } // 创建关联表 expert.createRelation(dao, en); }