User-defined variables set by parameters

问题:

The SQL is am using is:

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

The way I then input parameters is

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

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.

This gives me an error,

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.

回答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.

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()
...

回答2:

I don't know it this will help, but in this case I usually use another syntax (for SQL Server):

declare @person1_id long
declare @person2_id long
declare @safety_factor int

SELECT @person1_id=?, @person2_id=?, @safety_factor=?

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