How to Auto‑Generate Spring Boot CRUD APIs from SQL in Minutes

Preface

When developing projects we often write repetitive CRUD code, defining controller, service, DAO, mapper, DTO, and constantly repeat yourself. Existing rapid‑development frameworks can generate interfaces from a single SQL, but many are not mature or widely used, making troubleshooting difficult.

This article explores a simple way to automatically generate interfaces by defining only SQL, providing a proof‑of‑concept rather than a production‑ready solution.

Idea

The implementation uses Spring Boot and Swagger2 and follows five steps:

Configure datasource information and test the connection (URL, username, password, etc.).

Define custom interface metadata (path, HTTP method, parameters, datasource, SQL script).

Register Spring endpoints dynamically based on the custom metadata.

Execute the SQL when the endpoint is called and return the result as JSON.

Optionally register the endpoint with Swagger2 for documentation.

Implementation

Datasource

We store datasource definitions in a table. The essential fields are:

public class Source {
    private String key; // datasource key
    private String name; // datasource name
    private DbTypeEnum type; // MYSQL or ORACLE
    private String url; // JDBC URL
    private String username;
    private String password;
}

The supported database types are defined by a simple enum:

public enum DbTypeEnum {
    MYSQL(0, "MYSQL"),
    ORACLE(1, "ORACLE")
}

Clients provide a JDBC URL such as:

jdbc:mysql://192.0.0.1:3306/test?characterEncoding=UTF8

Testing the connection requires the appropriate driver dependencies:

<!-- Oracle driver -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
</dependency>
<!-- MySQL driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Connection test code:

try {
    Connection conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
    // connection error
} finally {
    connection.close();
}

API Definition

API objects store the path, HTTP method, datasource key, and SQL script:

public class Api {
    @TableId("ID")
    private Long id;
    @ApiModelProperty(value = "接口名称")
    private String name;
    @ApiModelProperty(value = "路径")
    private String path;
    @ApiModelProperty(value = "数据源key")
    private String sourceKey;
    @ApiModelProperty(value = "操作类型")
    private OpTypeEnum method; // GET/POST/PUT/DELETE
    @ApiModelProperty(value = "sql脚本")
    private String sql;
}

Dynamic Spring Registration

Spring registers request mappings via RequestMappingHandlerMapping. We create a dynamic registry that converts an Api object into a RequestMappingInfo and registers or unregisters it at runtime.

@Component
public class RequestDynamicRegistry {
    @Autowired
    private RequestMappingHandlerMapping requestMappingHandlerMapping;
    @Autowired
    private RequestHandler requestHandler;
    private final Method method = RequestHandler.class.getDeclaredMethod(
        "invoke", HttpServletRequest.class, HttpServletResponse.class,
        Map.class, Map.class, Map.class);
    private final Map<String, Api> apiCaches = new ConcurrentHashMap<>();

    private RequestMappingInfo toRequestMappingInfo(Api api) {
        return RequestMappingInfo.paths(api.getPath())
            .methods(RequestMethod.valueOf(api.getOpType().name().toUpperCase()))
            .build();
    }

    public boolean register(Api api) {
        RequestMappingInfo info = toRequestMappingInfo(api);
        if (requestMappingHandlerMapping.getHandlerMethods().containsKey(info)) {
            throw new BusinessException("接口冲突，无法注册");
        }
        requestMappingHandlerMapping.registerMapping(info, requestHandler, method);
        apiCaches.put(api.getKey(), api);
        return true;
    }

    public boolean unregister(Api api) {
        RequestMappingInfo info = toRequestMappingInfo(api);
        requestMappingHandlerMapping.unregisterMapping(info);
        apiCaches.remove(api.getKey());
        return true;
    }

    public List<Api> apiCaches() {
        return new ArrayList<>(apiCaches.values());
    }

    public Api getApiFromReqeust(HttpServletRequest request) {
        String mappingKey = Objects.toString(request.getMethod(), "GET").toUpperCase()
            + ":" + request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE);
        return apiCaches.get(mappingKey);
    }
}

Request Handler (initial version)

@Component
@Slf4j
public class RequestHandler {
    @Autowired
    private RequestDynamicRegistry requestDynamicRegistry;

    @ResponseBody
    public CommonResult<Object> invoke(HttpServletRequest request, HttpServletResponse response,
                                      @PathVariable(required = false) Map<String, Object> pathVariables,
                                      @RequestHeader(required = false) Map<String, Object> defaultHeaders,
                                      @RequestParam(required = false) Map<String, Object> parameters) throws Throwable {
        Api api = requestDynamicRegistry.getApiFromReqeust(request);
        if (api == null) {
            log.error("{}找不到对应接口", request.getRequestURI());
            throw new Exception("接口不存在");
        }
        // simple test response
        return CommonResult.success("ok");
    }
}

Executing SQL and Returning Results

The handler is extended to obtain a JDBC connection from the selected datasource, execute the configured SQL, and convert the ResultSet to a JSON array.

@Component
@Slf4j
public class RequestHandler {
    @Autowired
    private RequestDynamicRegistry requestDynamicRegistry;
    @Autowired
    private SourceService sourceService;

    @ResponseBody
    public CommonResult<Object> invoke(HttpServletRequest request, HttpServletResponse response,
                                      @PathVariable(required = false) Map<String, Object> pathVariables,
                                      @RequestHeader(required = false) Map<String, Object> defaultHeaders,
                                      @RequestParam(required = false) Map<String, Object> parameters) throws Throwable {
        Api api = requestDynamicRegistry.getApiFromReqeust(request);
        if (api == null) {
            log.error("{}找不到对应接口", request.getRequestURI());
            throw new BusinessException("接口不存在");
        }
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Source dbSource = sourceService.getById(api.getSourceKey());
            conn = JdbcUtils.getConnection(dbSource.getUrl(), dbSource.getUsername(), dbSource.getPassword());
            statement = conn.createStatement();
            rs = statement.executeQuery(api.getSql());
            return CommonResult.success(convert(rs));
        } finally {
            if (rs != null) rs.close();
            if (statement != null) statement.close();
            if (conn != null) conn.close();
        }
    }

    public static JSONArray convert(ResultSet rs) throws SQLException, JSONException {
        // conversion logic omitted for brevity
    }
}

At this point, adding an Api definition (e.g., a GET request to /user) and registering it via RequestDynamicRegistry makes the endpoint instantly callable and returns "ok" or the query result.

Swagger Documentation

To make the generated endpoints discoverable, a custom SwaggerResourcesProvider can be implemented (similar to the approach used by magic‑api) to expose all Api objects to Swagger2.

Resulting Swagger UI shows the dynamically created endpoints.