Spring Boot Java JdbcTemplate实现数据库表和字段的动态添加、编辑、删除

基于JdbcTemplate来动态操作数据库的表结构：

/**
 * Created by WangBaBa on 2023/9/12.
 */
@Service
public class TableService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 返回所有表
     * @return
     */
    @Transactional
    public Result getTable(){
        try {
            String SQL = "SELECT * FROM 表";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(SQL);
            return Result.ok("查询成功",list);

        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("查询失败");
        }
    }

    /**
     * 返回表格字段
     * @return
     */
    @Transactional
    public Result getTableFields(String table){
        try {
            String SQL = "SELECT * FROM 表 WHERE id = ?";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(SQL, table);
            return Result.ok("查询成功",list);

        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("查询失败");
        }
    }

    /**
     * 创建（编辑）表
     * @return
     */
    @Transactional
    public Result addTable(String ID ,String oldtablename, String tablename, String alname, String tabletype){
        try {
            // 判断数据库是否存在数据
            if(ID == ""){
                // 添加ID
                Map<String, Object> count = jdbcTemplate.queryForMap("SELECT MAX(ID) FROM 表");
                int id =  1;
                if(count.get("") != null){
                    id =  Integer.parseInt(count.get("").toString()) + 1;
                }
                // 添加数据
                String SQL = "INSERT INTO 表 VALUES (?, ?, ?, ?, ? ,NULL ,NULL )";
                jdbcTemplate.update(SQL, id, tablename, alname, tabletype, "0");

                // 创建表
                String SQL1 = "CREATE TABLE " + tablename + " (ID int not null PRIMARY KEY)";
                jdbcTemplate.execute(SQL1);

                System.out.println("创建表：" + id);
                return Result.ok("创建成功");
            } else {
                // 更新数据
                String SQL = "UPDATE 表 SET 表名=?,别名=?,类型=? WHERE ID=?";
                int res = jdbcTemplate.update(SQL, tablename, alname, tabletype, ID);

                // 修改表名
                String SQL1 = "EXEC sp_rename " + oldtablename + "," + tablename;
                jdbcTemplate.execute(SQL1);

                System.out.println("表名：" + oldtablename + "  修改为：" + tablename);
                return Result.ok("编辑成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("编辑失败");
        }
    }

    /**
     * 删除表
     * @return
     */
    @Transactional
    public Result deleteTable(String tablename){
        try {
            // 删除数据
            String SQL = "DELETE FROM 表 WHERE 表名=?";
            jdbcTemplate.update(SQL, tablename);

            // 删除表
            String SQL1 = "DROP TABLE " + tablename;
            jdbcTemplate.execute(SQL1);

            System.out.println("删除表：" + tablename);
            return Result.ok("删除成功");

        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("删除失败");
        }
    }

    /**
     * 创建（编辑）字段
     * @return
     */
    @Transactional
    public Result addFields(String ID , String oldfieldname, String fieldname, String alname, String fieldtype, String form, String unit, String type, String table, String config, String preset){
        try {
            // 判断数据库是否存在数据
            if(ID == ""){
                // 添加ID
                Map<String, Object> count = jdbcTemplate.queryForMap("SELECT MAX(ID) FROM 表");
                int id =  1;
                if(count.get("") != null){
                    id =  Integer.parseInt(count.get("").toString()) + 1;
                }
                // 添加字段
                String SQL = "INSERT INTO 表 VALUES (?, ?, ?, ?, ? ,?, ?, ?, ?, ? )";
                jdbcTemplate.update(SQL, id, fieldname, alname, fieldtype, form, unit, type, table, config, preset);

                // 添加表字段
                String SQL1 = "ALTER TABLE " + table + " ADD " + fieldname + " " + fieldtype;
                jdbcTemplate.execute(SQL1);

                System.out.println("创建字段：" + id);
                return Result.ok("创建成功");
            } else {
                // 更新数据
                String SQL = "UPDATE 表 SET 字段名=?,别名=?,字段类型=?,形态=?,单位=?,类型=?,配置=?,预设值=? WHERE ID=?";
                jdbcTemplate.update(SQL, fieldname, alname, fieldtype, form, unit, type, config, preset, ID);

                // 修改字段名 exec sp_rename '表.aa','bb';
                String SQL1 = "EXEC sp_rename \"" + table + "." + oldfieldname + "\",\"" + fieldname + "\"";
                jdbcTemplate.execute(SQL1);

                // 修改字段类型 ALTER TABLE 表 ALTER COLUMN 字段 nvarchar(100)
                String SQL2 = "ALTER TABLE " + table + " ALTER COLUMN " + fieldname + " " + fieldtype;
                jdbcTemplate.execute(SQL2);

                System.out.println("字段：" + oldfieldname + "  修改为：" + fieldname + "  类型：" + fieldtype);
                return Result.ok("编辑成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("编辑失败");
        }
    }

    /**
     * 删除字段
     * @return
     */
    @Transactional
    public Result deleteFields(String table, String fieldname){
        try {
            // 删除数据
            String SQL = "DELETE FROM 表 WHERE 字段名=? AND 所属表=?";
            jdbcTemplate.update(SQL, fieldname, table);

            // 删除表字段
            String SQL1 = "ALTER TABLE " + table + " DROP COLUMN " + fieldname;
            jdbcTemplate.execute(SQL1);

            System.out.println("表：" + table + " 删除字段：" + fieldname);
            return Result.ok("删除成功");

        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("删除失败");
        }
    }

}