springboot动态建立表、修改表结构、删除表工具

冷不防 2023-09-30 23:40 17阅读 0赞

分享记录一个工具，springboot按需求动态建立表、修改表结构、删除表。

import com.alibaba.csp.sentinel.util.StringUtil;
    import com.mes.material.domain.TeSource;
    import com.mes.material.domain.vo.AttributeItemEntity;
    import com.mes.material.service.ITeSourceService;
    import lombok.AllArgsConstructor;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;
    
    /**
     * @Author: best_liu
     * @Description:springboot动态建立表、修改表结构、删除表工具
     * @Date Create in 8:28 2022/5/12
     * @Modified By:
     */
    @Service
    @AllArgsConstructor
    public class DbStructureUtils {
    
        @Autowired
        private ITeSourceService teSourceService;
    
        public String getTableName(String ss) {
            return "material_info_" + ss.toLowerCase();
        }
    
        public boolean createTable(String tableName, List<AttributeItemEntity> columns) {
            StringBuffer sb = new StringBuffer();
            sb.append("CREATE TABLE IF NOT EXISTS \"public\".\"" + tableName + "\" (\n");
            sb.append("\"id\" varchar(64) NOT NULL,\n");
            columns.forEach(e -> {
                sb.append("\"" + e.getName() + "\" " + columnType(e.getType(), e.getLength()) + " " + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + ",\n");
            });
            sb.append("\"remark\" varchar(255) , \n");
            sb.append("\"create_time\" timestamp(6) DEFAULT CURRENT_TIMESTAMP,\n");
            sb.append("\"create_by\" varchar(64) , \n");
            sb.append("\"update_time\" timestamp(6) DEFAULT CURRENT_TIMESTAMP,\n");
            sb.append("\"update_by\" varchar(64) , \n");
            sb.append("\"del_flag\" char(1),  \n");
            sb.append("\"tenant_id\" int8 \n");
            sb.append(");\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"id\" IS '主键';\n");
            columns.forEach(e -> {
                sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
            });
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"remark\" IS '备注';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"create_time\" IS '创建时间';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"create_by\" IS '创建人';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"update_time\" IS '修改时间';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"update_by\" IS '修改人';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"del_flag\" IS '是否删除（0-否；1-是）';\n");
            sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"tenant_id\" IS '租户ID';\n");
            sb.append("ALTER TABLE \"public\".\"" + tableName + "\" ADD CONSTRAINT \"" + tableName + "_pkey\" PRIMARY KEY (\"id\");");
            return execute(sb);
        }
    
        @Transactional(rollbackFor = Throwable.class)
        public boolean dropTables(List<String> tableNames) {
            StringBuffer sb = new StringBuffer();
            tableNames.forEach(tableName -> {
                sb.append("DROP TABLE IF EXISTS \"public\".\"" + tableName + "\";");
            });
            return execute(sb);
        }
    
        public boolean addColumns(String tableName, List<AttributeItemEntity> columns) {
            StringBuffer sb = new StringBuffer();
            columns.forEach(e -> {
                sb.append("ALTER TABLE \"public\".\"" + tableName + "\"  ADD COLUMN \"" + e.getName() + "\" " + columnType(e.getType(), e.getLength()) + " " + isNullSql(e.getEnableNull()) + " " + isDefaultSql(e.getDefaultValue()) + ";\n");
                sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
            });
            return execute(sb);
        }
    
        public boolean updateColumns(String tableName, List<AttributeItemEntity> columns, Map<Long, String> oldColumnMap) {
            StringBuffer sb = new StringBuffer();
            columns.forEach(e -> {
                String oldName = oldColumnMap.get(e.getId());
                if (oldName != null && !oldName.equals(e.getName())) {
                    sb.append("ALTER TABLE \"public\".\"" + tableName + "\"  RENAME COLUMN \"" + oldName + "\" TO \"" + e.getName() + "\";\n");
                }
                sb.append("COMMENT ON COLUMN \"public\".\"" + tableName + "\".\"" + e.getName() + "\" IS '" + e.getAlias() + "';\n");
            });
            return execute(sb);
        }
    
        public boolean dropColumns(String tableName, List<AttributeItemEntity> columns) {
            StringBuffer sb = new StringBuffer();
            columns.forEach(e -> {
                sb.append("ALTER TABLE \"public\".\"" + tableName + "\"  DROP COLUMN \"" + e.getName() + "\";\n");
            });
            System.out.println(sb);
            return execute(sb);
        }
    
        private String columnType(String type, Integer length) {
            if ("varchar".equals(type)) {
                if (Objects.isNull(length) || length == 0) {
                    length = 255;
                }
                return type + "(" + length + ")";
            }
            return type;
        }
    
        //获取所有表名称
        public List<String> tableNames() {
            List<String> tableNames = new ArrayList<>();
            try {
                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                TeSource teSource = teSourceService.selectByTeSourceId(Long.valueOf("1"));
                String driver = teSource.getDriverClassName();
                String url = teSource.getUrlPrepend() + teSource.getUrlAppend();//数据库链接
                String user = teSource.getUsername();//用户名
                String password = teSource.getPassword();//密码
                dataSource.setUrl(url);
                dataSource.setDriverClassName(driver);
                dataSource.setUsername(user);
                dataSource.setPassword(password);
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                Connection getConnection = jdbcTemplate.getDataSource().getConnection();
                DatabaseMetaData metaData = getConnection.getMetaData();
                ResultSet rs = metaData.getTables(getConnection.getCatalog(), null, null, new String[]{"TABLE"});
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    tableNames.add(tableName);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return tableNames;
        }
    
    
        private boolean execute(StringBuffer sb) {
            try {
                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                TeSource teSource = teSourceService.selectByTeSourceId(Long.valueOf("1"));
                String driver = teSource.getDriverClassName();
                String url = teSource.getUrlPrepend() + teSource.getUrlAppend();//数据库链接
                String user = teSource.getUsername();//用户名
                String password = teSource.getPassword();//密码
                dataSource.setUrl(url);
                dataSource.setDriverClassName(driver);
                dataSource.setUsername(user);
                dataSource.setPassword(password);
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.execute(sb.toString());
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
    
        private String isNullSql(Boolean boo) {
            if (boo) {
                return "NOT NULL";
            }
            return "";
        }
    
        private String isDefaultSql(Object obj) {
            if (Objects.nonNull(obj)) {
                if (obj instanceof String) {
                    if (StringUtil.isEmpty(obj.toString())) {
                        return "";
                    }
                    return "DEFAULT '" + obj + "'";
                }
                return "DEFAULT " + obj;
            }
            return "";
        }
    
    }

调用

![6af2914eb8a84404a49df4f78ec7931a.png][]

![ca74bf6fd2234da3a14ff92be0dc1fab.png][]

[6af2914eb8a84404a49df4f78ec7931a.png]: https://img-blog.csdnimg.cn/6af2914eb8a84404a49df4f78ec7931a.png
[ca74bf6fd2234da3a14ff92be0dc1fab.png]: https://img-blog.csdnimg.cn/ca74bf6fd2234da3a14ff92be0dc1fab.png