用户定义的变量由参数设置 [英] User-defined variables set by parameters

问题描述

我正在使用的是：

SET @person1_id = ?;
SET @person2_id = ?;
SET @safety_factor = ?;
SELECT *
FROM table
WHERE person1 = @person1_id
AND person2 = @person2_id
AND safety_factor = @safety_factor;

这不是确切的代码，但显示了我正在尝试做的事情

That's not the exact code, but shows what i'm trying to do

我输入参数的方式是

Statement stmt = connection.prepareStatement(*script*)
stmt.setLong(1, person1.id)
stmt.setLong(2, person2.id)
stmt.setBigDecimal(3, safetyFactor)

我在sql中使用变量，因为在整个脚本中重复使用这些值，我不想输入相同的值多次作为不同的参数值。

I'm using variables in the sql because the values are used repeatedly throughout the script, and I don't want to have to input the same value multiple times as different parameters.

这给了我一个错误，

线程main中的异常org.h2.jdbc.JdbcSQLException：参数parameterIndex无效值
2[90008-195]

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Invalid value "2" for parameter "parameterIndex" [90008-195]

我想这与脚本被视为四个单独的语句有关，但我不知道如何单独执行它们并使变量在语句之间起作用。

I imagine this is to do with the script being treated as four separate statements, but I don't know how to execute them seperately and have the variables work between statements.

推荐答案

H2无法处理带有多个sql语句的参数化查询。设置参数时，它只会看到第一个分号，这意味着它只能看到要设置的1个参数。

H2 cannot handle a parameterised query with multiple sql statements. When setting a parameter, it will only look as far as the first semicolon, meaning that it only sees 1 parameter to be set.

由于用户定义的变量是 session scoped ，我们可以在单独的语句中设置变量。

Since user-defined variables are session scoped, we can set the variables in a separate statement.

PreparedStatement statement = connection.prepareStatement("SET @person1_id = ?")
statement.setLong(1, person1.id)
statement.execute()

PreparedStatement statement = connection.prepareStatement("SET @person2_id = ?")
statement.setLong(1, person2.id)
statement.execute()
...

这篇关于用户定义的变量由参数设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！