英文:
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语句,请使用 JdbcTemplate
和 execute(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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论