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?

ave · Accepted Answer · 2019-10-02 08:33:32Z

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.

answered Oct 2, 2019 at 8:33

ave

3,7242 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MyBatis Resolving parameter with multiple statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related