MyBatis Resolving parameter with multiple statements

Question

I'm trying to dynamically set the lock timeout for a query with MyBatis and PostgreSQL.

My Mapper looks like :

 @Select("SET LOCAL lock_timeout = '#{lockTimeout}s';"
   + "SELECT ......where id= #{myId} FOR UPDATE")
 MyObject select(@Param("lockTimeout") String lockTimeout, @Param("myId") String id);

It seems that there is a mismatch with parameters and I'm getting a

 org.postgresql.util.PSQLException: Index of column out of range : 2, number of column 1.

@SelectProvider won't match in my case because my parameter lock timeout isn't static.

Does anyone have an idea how can I dynamically set the parameter lock timeout?

Answer 1

pgjdbc seems to execute each statement independently.
I would not recommend putting multiple statements in a single mapper statement as the behavior depends on the driver.
You should declare two methods and call them in the same session/transaction instead.

@Update("select set_config('lock_timeout', #{lockTimeout}, true)")
void setLockTimeout(String lockTimeout);

@Select("SELECT ......where id= #{myId} FOR UPDATE")
MyObject select(@Param("myId") String id);

A few notes:

• set_config() is used as SET LOCAL does not seem to work with a PreparedStatement.
• @Update is used to apply the change immediately. If you use @Select, you may need to call SqlSession#commit() explicitly.
• Unlike your example, the parameter must include s i.e. setLockTimeout("1s").
  If you prefer passing just a number, #{lockTimeout} || 's' should work.