NutzCN Logo
精华 分享:通过map快速创建和修改表结构,支持注释( nutz-1.r.70-SNAPSHOT 以上版本)
发布于 214天前 作者 大鲨鱼 1957 次浏览 复制 上一个帖子 下一个帖子
标签:

工具类:

/**
 * 动态修改表结构
 */
@IocBean
@Slf4j
public class MyExtDao extends NutDao {
    public void create(Dao dao, final String tableName,
                       final Map<String, Object> map, boolean hasColumnComment, boolean hasTableComment, String tableComment, boolean dropIfExists) {
        MapEntityMaker mapEntityMaker = new MapEntityMaker();
        mapEntityMaker.init(getDataSource(), dao.getJdbcExpert(), null);
        dao.create(mapEntityMaker.make(tableName, map, hasColumnComment, hasTableComment, tableComment), dropIfExists);
    }

    public void migration(Dao dao,
                          final String tableName,
                          final Map<String, Object> map,
                          final boolean hasColumnComment,
                          final boolean add,
                          final boolean del) {
        MapEntityMaker mapEntityMaker = new MapEntityMaker();
        mapEntityMaker.init(getDataSource(), dao.getJdbcExpert(), null);
        Entity<?> en = mapEntityMaker.make(tableName, map, hasColumnComment, false, null);
        final JdbcExpert expert = dao.getJdbcExpert();
        dao = Daos.ext(dao, tableName);
        final List<Sql> sqls = new ArrayList<Sql>();
        dao.run(new ConnCallback() {
            @Override
            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.info("add column[{}] to table[{}]",
                                    mf.getColumnName(),
                                    en.getTableName());
                            sqls.add(expert.createAddColumnSql(en, mf));
                        }
                    }
                    if (del) {
                        for (String colName : columnNames) {
                            log.info("del column[{}] from table[{}]", 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);
                        }
                    }
                } catch (SQLException e) {
                    if (log.isDebugEnabled()) {
                        log.debug("migration Table '{}' fail!", en.getTableName(), e);
                    }
                }
                // Close ResultSet and Statement
                finally {
                    Daos.safeClose(stat, rs);
                }
            }
        });
        for (Sql sql : sqls) {
            dao.execute(sql);
        }
    }
}

调用:

public void buildTable(String tableCode, String tableComment, String tableJson) {
        String tableName = OfficeConstant.TABLE_PREFIX + tableCode;
        if (Strings.isBlank(tableJson)) {
            log.info("表结构未定义 {}", tableName);
            return;
        }
        List<NutMap> fieldList = Json.fromJsonAsList(NutMap.class, Json.fromJson(String.class, tableJson));
        if (!this.dao().exists(tableName)) {
            // 表不存在,则创建
            myExtDao.create(this.dao(), tableName, getTableMap(tableName, fieldList),
                    true, true, tableComment, false);
        } else {
            // 修改表结构
            myExtDao.migration(this.dao(), tableName, getTableMap(tableName, fieldList), true,true, true);
        }
    }

ps:欢迎优化代码

1 回复
private Map<String, Object> getTableMap(String tableName, List<NutMap> fieldList) {
        Map<String, Object> table = new LinkedHashMap<>();
        table.put(".table", tableName);
        for (NutMap map : fieldList) {
            String code = map.getString("code");
            String name = map.getString("name");
            String type = map.getString("type");
            int width = map.getInt("width", 0);
            table.put("*id", "");
            table.put(".id.type", String.class);
            table.put(".id.coltype", ColType.VARCHAR);
            table.put(".id.width", 32);
            table.put(".id.comment", "主键");
            if (code.equals("id")) {
                continue;
            }
            if ("string".equals(type)) {
                table.put(code, "");
                table.put("." + code + ".type", String.class);
                table.put("." + code + ".coltype", ColType.VARCHAR);
                table.put("." + code + ".width", width);
            } else if ("integer".equals(type)) {
                table.put(code, 0);
                table.put("." + code + ".type", Integer.class);
                table.put("." + code + ".coltype", ColType.INT);
            } else if ("timestamp".equals(type)) {
                table.put(code, 0);
                table.put("." + code + ".type", Long.class);
                table.put("." + code + ".coltype", ColType.INT);
                table.put("." + code + ".width", 64);
            } else if ("double".equals(type)) {
                table.put(code, 0);
                table.put("." + code + ".type", Double.class);
                table.put("." + code + ".coltype", ColType.DOUBLE);
            } else if ("text".equals(type)) {
                table.put(code, "");
                table.put("." + code + ".type", String.class);
                table.put("." + code + ".customtype", "longtext");
            } else if ("datetime".equals(type)) {
                table.put(code, "");
                table.put("." + code + ".type", String.class);
                table.put("." + code + ".coltype", ColType.VARCHAR);
                table.put("." + code + ".width", 32);
            }
            if (Strings.isNotBlank(name)) {
                table.put("." + code + ".comment", name);
            }
        }
        log.debug(Json.toJson(table));
        return table;
    }
添加回复
请先登陆
回到顶部