Search code examples
sqlspring-bootmybatisibatisspring-mybatis

How to change the database name dynamically in MyBatis XML file?

I am using Spring Boot with MyBatis. I have the following query in one mapper XML file.

<select id="someFunction" resultMap="someResultMap">
    SELECT *
    FROM p LEFT JOIN anotherDatabase.table AS q ON p.id = q.id
</select>

Actually "anotherDatabase" is hard-coded in my query because I do NOT want to add another data source for only this query. But how can I make this "anotherDatabase" name dynamically (maybe configure it in some properties file) as it may change in different environment deployed?

Solution

  • Though ugly solution, you can use a parameter: not a traditional JDBC/SQL parameter #{schema} but a direct parameter ${schema}. See the dollar sign ($) there?

    When using a direct parameter ${param} you can insert whatever you want into the SQL. Even a entire whole SQL statement if you wish. Use it with care and only as a last resort.

    Please carefully consider this insertion of direct parameters into the SQL is susceptible of SQL injection. You need to carefully control the value of the schema property/parameter, so it does not come from the user or any external source. If you do it like this, it will be safe to use.

    However, a cleaner solution is to use a separate datasource. The only drawback is you may need to enable two-phase commit if you need transactions that emcompass tables from both datasources.