Search code examples
javamysqlibatis

Can I create dynamic temporary table using iBatis?

During a query of a user's contacts, I want to use a temporary table specific to that user, e.g. tmp_contact_{userId}, if userId is 123, should resolve to tmp_contact_123

I have inherited a java project that uses iBatis to interoperate with a MySql database. I've read some of the iBatis documentation, and I see replaceable parameters in the xml as either #{userId} or #userId#. The former appears to be interpreted as a parameter for a prepared statement and produces a question mark, e.g. 'create temporary table tmp_contact_?', while the latter was left as a literal, e.g. 'create temporary table tmp_contact_#userId#'. Is there alternate syntax to produce 'create temporary table tmp_contact_123'?

Solution

  • Yes there is a way to do this!

    Using # puts a quote around string params. You can use $ instead. For your example, it would be

    create temporary table tmp_contact_$userId$

    When iBatis compiles this statement it will be

    create temporary table tmp_contact_123

    and not

    create temporary table tmp_contact_'123'