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

分享记录一个工具，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 "";
    }

}

调用