MyBatis use different database according to configuration-CodePudding

I'm using SpringBoot and MyBatis (using mapper XML) to build an application. Assume there's such a query:

SELECT ... FROM some_database.some_table WHERE ...

in which some_database could change in different "environments" (like project or project_cloud). By this time, to build artifacts for different "environments", we have to grep in our XML files. I wonder if there's a best practice to such circumstance.

AFAIK, for JPA, one may configure data sources with application.yml so that they may refer to "databases" with data source names, instead of actual data base names. I wonder if there's such an option for MyBatis.

CodePudding user response：

solution1 intercepter

create an intercepter, like SchemaInterceptAnnotation, and you can change the actual sql at runtime.

according to https://javamana.com/2022/04/202204070748273889.html

@SchemaInterceptAnnotation(schemaType = "business")
public interface UserMapper {}

solution2 jdbctemplate !!!not mybatis-related

two application.yaml files

application.yaml

user_db:
  jdbc:mysql://xxx.com:3306/user?characterEncoding=utf8

application_product.yaml

user_db:
  jdbc:mysql://xxx.com:3306/user_product?characterEncoding=utf8

switch environment

provide spring.config.name on command line when starting the server

register datasource

register all datasource in a map, and give the PO a dataSource config, and when you querying, you get the dataSource out of the map.

@Data
@Table(value="table_user", dataSource="user_db")
public class UserPO implements Serializable {
    @DBColumn
    private Long id;
}

CodePudding user response：

The proper way to achieve this is by using MyBatis configuration properties (https://mybatis.org/mybatis-3/configuration.html#properties), e.g.

application.yml

mybatis:
  ...
  configuration-properties:
    dbschema: project

application-cloud.yml

mybatis:
  ...
  configuration-properties:
    dbschema: project_cloud

SomeMapper.xml

...
<select id="getWhateverById" resultMap="...">
    select ...
    from ${dbschema}.some_table
    where ...
</select>

The configuration in application.yml will be active by default while application-cloud.yml will override it when the app is run with the cloud profile active.

Make sure to have the org.mybatis.spring.boot:mybatis-spring-boot-starter dependency in your project (https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/).