Search code examples
javajdbcspring-jdbc

How to use org.springframework.jdbc.object to implement reusable ddl statements?

I would like to use entities from org.springframework.jdbc.object package for modeling access to database.

It is clear how to create objects for select, update, insert and delete queries. We can just extend MappingSqlQuery<?> or SqlUpdate then declare all the parameters, collect named parameters in some map and execute statement stmt.executeByNamedParam(Map<String, ?> paramMap);.

It is not clear how we can use org.springframework.jdbc.object package to implement ddl statements like schema/table creation, or altering table. For example I would like to create schema dynamically, the query look like this: create schema if not exists schema_name. I create statement in next way:

public final class CreateSchemaStmt extends SqlUpdate {

    private static final String CREATE_SCHEMA_SQL_TEMPLATE =
            "create schema if not exists ?";

    public CreateSchemaStmt(DataSource ds) {
        setDataSource(ds);
        setSql(CREATE_SCHEMA_SQL_TEMPLATE);
        declareParameter(new SqlParameter("schema", Types.VARCHAR));
        compile();
    }
}

But it is not possible to declare parameter in such a way and pass it to statement execution. Of course I can always create a new statement when I need to create new schema/table or alter table (with sql query without placeholders), but in this case this statement will not be reusable.

Could such kind of query be created and executed with named parameters?

Solution

  • No, a DDL statement cannot be reused. Any change of table name, column name, column data type, etc. will be a different statement.

    All the classes in the org.springframework.jdbc.object package are helpers for using a PreparedStatement, and DDL statements will gain no benefit from being prepared.

    For DDL, use JdbcTemplate and the execute(String sql) method. As the javadoc of that method says:

    Issue a single SQL execute, typically a DDL statement.

    Don't focus on the tool. When you say "I would like to use entities from org.springframework.jdbc.object package", you're focusing too much on the tool. You should focus on the job that needs done, then pick the right tool for the job. For DDL statements, JdbcTemplate.execute(sql) is the right tool.

    Use batchUpdate(String... sql) and ignore the return value, if you have a lot of DDL statements to execute and performance is critical. For DDL statements though, that is unlikely to be the case.