处理“NOT IN (:param)”时的 JPA setParameter

发布于 2024-08-15 12:53:00 字数 430 浏览 6 评论 0原文

我正在尝试在查询中设置一个参数，例如：

select * from Cars where Cars.color NOT IN (:color_params)

当我在 JavaClass 中添加参数时，就像：

...
query.setParameter("color_params", "RED,BLUE");
...

这不起作用，仅适用于仅一个参数。
我尝试过 "'RED','BLUE'" 并且不工作。

如果我将参数放入查询中，例如：

select * from Cars where Cars.color NOT IN ('RED','BLUE')

我做错了什么？

原文

I'm trying to set a parameter in my query, for example:

select * from Cars where Cars.color NOT IN (:color_params)

And when I'm adding the parameter in my JavaClass is like:

...
query.setParameter("color_params", "RED,BLUE");
...

And this is not working, is only working with only one parameter.
I've tried with "'RED','BLUE'"
and is not working to.

If I put my parameters in the query is working for example:

select * from Cars where Cars.color NOT IN ('RED','BLUE')

What I'm doing wrong?

分享到QQ

分享到微博

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

发布评论

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

毅然前行 2024-08-22 12:53:00

你应该传递一个列表。

List<String> colors = ....;
String query = "select * from Cars where Cars.color NOT IN (:color_params)";
Map<String, Object> params = new HashMap<String, Object>();
params.put("color_params", colors);
// ... execute the query with the param.

您还可以这样做：

query.setParameter("color_params", colors);

作为一般规则，通常更喜欢将参数传递给固定查询，而不是自定义字符串。优点可能是：

减少解析：JPA 实现（至少是 Hibernate）在解析每个查询时需要付出很大的努力。因此，解析后的查询会进入缓存以供重用。如果查询字符串是在运行时根据参数构建的，则它可能永远不会两次相同，因此会损失大量时间、计算能力和缓存内存。但如果您使用具有不同参数的相同查询字符串，宾果：速度快、内存使用量低、CPU 要求低。
防止 SQL 注入。如果您使用参数，则可以提供此保证。如果您使用参数构建查询字符串，则必须为自己提供此保证......！

You are supposed to pass a List.

List<String> colors = ....;
String query = "select * from Cars where Cars.color NOT IN (:color_params)";
Map<String, Object> params = new HashMap<String, Object>();
params.put("color_params", colors);
// ... execute the query with the param.

You could also do:

query.setParameter("color_params", colors);

As a general rule, it is often prefered to pass parameters to a fixed query, instead of customizing the String. The advantages could be:

Reduced parsing: JPA implementation (at least Hibernate) have a hard work parsing each query. So the parsed query goes into a cache, to be reused. If the query string is build at runtime from parameters, it might never be twice the same, so a lot of time, computing power and cache memory are lost. But if you use the same query string with different parameters, bingo : fast, low memory use, low cpu requirement.
Prevent SQL injection. This guarantee is offered if you use parameters. If you build your query string with the parameters, you have to provide yourself this guarantee ...!

回复收藏 0 原文