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

刺骨的言语ヽ痛彻心扉 2022-09-17 04:22 294阅读 0赞

代码如下（以postgressql为列子）

基于JdbcTemplate 实现

import lombok.AllArgsConstructor;
    import org.springblade.core.tool.utils.StringUtil;
    import org.springblade.gis.constant.AppConst;
    import org.springblade.gis.modules.feature.attribute.entity.AttributeItemEntity;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.ResultSet;
    import java.util.*;
    
    @Service
    @AllArgsConstructor
    public class DbStructureService {
    
        private final JdbcTemplate jdbcTemplate;
    
    
    
        public boolean createTable(Long id, List<AttributeItemEntity> columns){
                return createTable(AppConst.tablePrefix+id,columns);
        }
    
        public boolean dropTables(List<Long> ids){
            List<String> tableNames=new ArrayList<>();
            ids.forEach(id->{
                tableNames.add(AppConst.tablePrefix+id);
            });
            return dropTable(tableNames);
        }
    
        public  boolean addColumns(Long id,List<AttributeItemEntity> columns){
            return addColumns(AppConst.tablePrefix+id,columns);
        }
    
        public  boolean updateColumns(Long id,List<AttributeItemEntity> columns,Map<Long,String> oldColumnMap){
            return updateColumns(AppConst.tablePrefix+id,columns,oldColumnMap);
        }
    
        public  boolean dropColumns(Long id,List<AttributeItemEntity> columns){
            return dropColumns(AppConst.tablePrefix+id,columns);
        }
    
        private String columnType(String type,Integer length){
            if("varchar".equals(type)){
                if(Objects.isNull(length)||length==0){
                    length=255;
                }
                return type+"("+length+")";
            }
            return type;
        }
    
        private   boolean createTable(String tableName,List<AttributeItemEntity> columns){
            StringBuffer sb = new StringBuffer();
            sb.append("CREATE TABLE IF NOT EXISTS \"public\".\""+tableName+"\" (\n");
            sb.append("id int8 NOT NULL,\n");
            columns.forEach(e->{
                sb.append(e.getName() + " "+columnType(e.getType(),e.getLength())+" "+isNullSql(e.getEnableNull())+" "+isDefaultSql(e.getDefaultValue())+",\n");
            });
            sb.append("create_time timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP\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+"\".\"create_time\" IS '创建时间';\n");
            sb.append("ALTER TABLE \"public\".\""+tableName+"\" ADD CONSTRAINT \""+tableName+"_pkey\" PRIMARY KEY (\"id\");");
            return  execute(sb);
        }
    
        private  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);
        }
    
        private  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);
        }
    
        private  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");
            });
            return execute(sb);
        }
    
    
        private  boolean dropTable(List<String> tableNames){
            StringBuffer sb = new StringBuffer();
            tableNames.forEach(tableName->{
                sb.append("DROP TABLE IF EXISTS \"public\".\""+tableName+"\";");
            });
            return  execute(sb);
        }
    
        //获取所有表名称
        public List<String> tableNames() {
            List<String> tableNames= new ArrayList<>();
            try {
                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 {
                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)){
                        return "";
                    }
                }
                return "DEFAULT "+obj;
            }
            return "";
        }
    
    
    
    }