Spring JDBC 模板条件插入

发布于 2024-10-01 06:02:38 字数 395 浏览 8 评论 0原文

我正在尝试编写一个基于Java参数的条件批量插入，例如

List<Object[]> params = new ArrayList();
params.add(new Object[]{1, 2, 3});

getSimpleJdbcTemplate.batchUpdate(
"INSERT INTO SomeTable( colA, colB, colC)" +
 " VALUES(?, ?, ?)" +
 " WHERE EXISTS ( // some condition )", params);

这显然不起作用，我发现的最接近的例子涉及从表中选择 INSERT 值，而不是我需要的列表参数。如何引用 Object[] 参数作为 INSERT 值的一部分？

谢谢。

原文

I am trying to write a conditional batch insert based on Java Parameters, for example

List<Object[]> params = new ArrayList();
params.add(new Object[]{1, 2, 3});

getSimpleJdbcTemplate.batchUpdate(
"INSERT INTO SomeTable( colA, colB, colC)" +
 " VALUES(?, ?, ?)" +
 " WHERE EXISTS ( // some condition )", params);

This obviously does not work, and the closest example I have found involves
selecting the INSERT values from tables, instead of the List arguments, which is what I need. How can I reference the Object[] parameters as part of the INSERT values?

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

≈。彩虹 2024-10-08 06:02:38

我想说你应该在单独的 SELECT 中移动这个条件。

回复收藏 0 原文

为你鎻心 2024-10-08 06:02:38

作为您的示例，您应该尝试以下语法和 SimpleJdbcOperations.update 方法：

getSimpleJdbcTemplate.update(
    " INSERT INTO SomeTable( colA, colB, colC)" +
    " SELECT srcA, srcB, srcC" +
    " FROM src_table" +
    " WHERE EXISTS ( // some condition )",
    params
);

此外，我一直在使用 StringTemplate 用于生成动态 SQL。这是我用过的最好的语言。

例如（作为 StringTemplate 语法）：

StringTemplate tmpl = new StringTemplate(
    " SELECT *" +
    " FROM some_table" +
    " WHERE 1 = 1" +
    "$search_age:{ AND age = ?}$" +
    "$search_name:{ AND name = ?}$"
);


/**
 * if param_age is not null, "AND age = ?" would be included in argumented sql,
 * same as param_name
 */
tmpl.setAttribute("age", param_age);
tmpl.setAttribute("name", param_name);
// :~)

String sqlTmpl = tmpl.toString(); // final SQL generated

As your example, you should try following syntax and the SimpleJdbcOperations.update method:

getSimpleJdbcTemplate.update(
    " INSERT INTO SomeTable( colA, colB, colC)" +
    " SELECT srcA, srcB, srcC" +
    " FROM src_table" +
    " WHERE EXISTS ( // some condition )",
    params
);

Additionally, I've been using StringTemplate for generation of dynamic SQL. It's the best language I ever use.

for Example(As StringTemplate Syntax):

StringTemplate tmpl = new StringTemplate(
    " SELECT *" +
    " FROM some_table" +
    " WHERE 1 = 1" +
    "$search_age:{ AND age = ?}$" +
    "$search_name:{ AND name = ?}$"
);


/**
 * if param_age is not null, "AND age = ?" would be included in argumented sql,
 * same as param_name
 */
tmpl.setAttribute("age", param_age);
tmpl.setAttribute("name", param_name);
// :~)

String sqlTmpl = tmpl.toString(); // final SQL generated

回复收藏 0 原文

~没有更多了~