如何使用org.springframework.jdbc.object来实现可重用的DDL语句?

huangapple go评论71阅读模式
英文:

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

问题

我想使用 org.springframework.jdbc.object 包中的实体来对数据库访问进行建模。

对于创建用于选择、更新、插入和删除查询的对象,操作很明确。我们可以直接扩展 MappingSqlQuery<?>SqlUpdate,然后声明所有参数,将命名参数收集到某个映射中,并执行语句 stmt.executeByNamedParam(Map<String, ?> paramMap);

然而,如何使用 org.springframework.jdbc.object 包来实现像模式/表创建或修改表之类的 ddl 语句并不明确。
例如,我想动态创建模式,查询如下:create schema if not exists schema_name
我按照以下方式创建语句:

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();
    }
}

但是不能以这种方式声明参数并将其传递给语句执行。
当然,在需要创建新的模式/表或修改表(带有无占位符的 SQL 查询)时,我总是可以创建新的语句,但在这种情况下,该语句将无法重用。

是否可以使用命名参数创建并执行这种类型的查询呢?

英文:

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?

答案1

得分: 1

不,DDL语句无法被重用。任何表名、列名、列数据类型的更改都将成为不同的语句。

org.springframework.jdbc.object 包中的所有类都是用于使用 PreparedStatement 的辅助工具,DDL语句不会从准备中获益。

对于DDL语句,请使用 JdbcTemplateexecute(String sql) 方法。正如该方法的javadoc所说:

> 执行单个SQL语句,通常是DDL语句

不要过于关注工具。当你说“我想使用 org.springframework.jdbc.object 包中的实体”时,你过于关注工具了。你应该专注于需要完成的任务,然后选择适合该任务的正确工具。对于DDL语句,JdbcTemplate.execute(sql) 是正确的工具。

如果你有大量DDL语句需要执行并且性能至关重要,可以使用 batchUpdate(String... sql) 并忽略返回值。但对于DDL语句来说,这种情况不太可能发生。

英文:

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.

huangapple
  • 本文由 发表于 2020年9月17日 00:24:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/63924290.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定