MyBatis多数据源的两种配置方式

时间:2022-09-06 17:32:35

前言

同一个项目有时会涉及到多个数据库,也就是多数据源。多数据源又可以分为两种情况:

1)两个或多个数据库没有相关性,各自独立,其实这种可以作为两个项目来开发。比如在游戏开发中一个数据库是平台数据库,其它还有平台下的游戏对应的数据库;

2)两个或多个数据库是master-slave的关系,比如有mysql搭建一个 master-master,其后又带有多个slave;或者采用mha搭建的master-slave复制;

mybatis多数据源的配置主要有两种方式:

  • 通过@mapperscan注解,对不同包下的mapper使用不同的sqlsessionfactory
  • 通过@mapperscan注解加自定义注解,对使用不同注解的mapper使用不同的sqlsessionfactory

第二种配置相对灵活,示例如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
package bj;
 
import ch.qos.logback.classic.level;
import ch.qos.logback.classic.logger;
import com.zaxxer.hikari.hikaridatasource;
import io.shardingsphere.shardingjdbc.spring.boot.springbootconfiguration;
import org.apache.ibatis.annotations.mapper;
import org.apache.ibatis.annotations.select;
import org.apache.ibatis.session.sqlsessionfactory;
import org.mybatis.spring.sqlsessionfactorybean;
import org.mybatis.spring.annotation.mapperscan;
import org.slf4j.loggerfactory;
import org.springframework.boot.springapplication;
import org.springframework.boot.webapplicationtype;
import org.springframework.boot.autoconfigure.springbootapplication;
import org.springframework.boot.context.event.applicationreadyevent;
import org.springframework.context.applicationlistener;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import org.springframework.context.annotation.primary;
import org.springframework.jdbc.core.jdbctemplate;
 
import javax.annotation.resource;
import javax.sql.datasource;
import java.lang.annotation.elementtype;
import java.lang.annotation.retention;
import java.lang.annotation.retentionpolicy;
import java.lang.annotation.target;
import java.util.list;
import java.util.map;
 
/**
 * created by baijifeilong@gmail.com at 2018/12/6 下午9:29
 * <p>
 * mybatis多数据源演示
 */
@springbootapplication(exclude = {springbootconfiguration.class})
@configuration
@mapperscan(annotationclass = mapper.class, basepackageclasses = mybatisapp.class,
  sqlsessionfactoryref = "sqlsessionfactory")
public class mybatisapp implements applicationlistener<applicationreadyevent> {
 
 /**
  * secondarymapper配置
  * \@mapperscan 注解一次只能添加一个,所以需要单独再加一个配置类
  * 自定义@mapperscan会替换mybatis自动添加的默认@mapperscan。所以主@mapperscan也必须显式添加
  */
 @configuration
 @mapperscan(annotationclass = secondarymapper.class, basepackageclasses = mybatisapp.class,
   sqlsessionfactoryref = "sqlsessionfactorysecond")
 static class secondarymapperconfiguration {
 }
 
 public static void main(string[] args) {
  new springapplication(mybatisapp.class) {{
   setwebapplicationtype(webapplicationtype.none);
  }}.run(args);
 }
 
 @resource
 private datasource datasource;
 
 @resource
 private datasource datasourcesecond;
 
 @resource
 private jdbctemplate jdbctemplate;
 
 @resource
 private usermapper usermapper;
 
 @resource
 private secondaryusermapper secondaryusermapper;
 
 private void initlogger() {
  ((logger) loggerfactory.getlogger(mybatisapp.class)).setlevel(level.debug);
  ((logger) loggerfactory.getlogger(jdbctemplate.class)).setlevel(level.debug);
 }
 
 private void initdatabase() {
  string olddatabase = jdbctemplate.queryforobject("select database()", string.class);
  jdbctemplate.execute("drop schema if exists one");
  jdbctemplate.execute("create schema one");
  jdbctemplate.execute("use one");
  jdbctemplate.execute("create table user(id int auto_increment primary key, name varchar(32) charset 'utf8')");
  jdbctemplate.execute("insert into user(name) values ('人民的儿子')");
  jdbctemplate.execute("insert into user(name) values ('人民的孙子')");
  jdbctemplate.execute("insert into user(name) values ('人民的曾孙子')");
  jdbctemplate.execute("drop schema if exists two");
  jdbctemplate.execute("create schema two");
  jdbctemplate.execute("use two");
  jdbctemplate.execute("create table user(id int auto_increment primary key, name varchar(32) charset 'utf8')");
  jdbctemplate.execute("insert into user(name) values ('人民的爹')");
  jdbctemplate.execute("insert into user(name) values ('人民的爷')");
  jdbctemplate.execute("insert into user(name) values ('人民的太爷')");
  jdbctemplate.execute("insert into user(name) values ('人民的老太爷')");
  jdbctemplate.execute("use " + olddatabase);
 }
 
 @override
 public void onapplicationevent(applicationreadyevent applicationreadyevent) {
  initlogger();
  initdatabase();
  system.out.println("users:");
  usermapper.selectall().foreach(system.out::println);
  system.out.println("secondary users:");
  secondaryusermapper.selectall().foreach(system.out::println);
 }
 
 /**
  * 主数据源
  * <p>
  * 如果不添加@primary注解, mybatis可以工作,但是jdbctemplate无法注入
  *
  * @return .
  */
 @primary
 @bean
 public datasource datasource() {
  return new hikaridatasource() {{
   setjdbcurl("jdbc:mysql://localhost/one?useunicode=true&characterencoding=utf8");
   setusername("root");
   setpassword("root");
  }};
 }
 
 /**
  * 副数据源
  *
  * @return .
  */
 @bean
 public datasource datasourcesecond() {
  return new hikaridatasource() {{
   setjdbcurl("jdbc:mysql://localhost/two?useunicode=true&characterencoding=utf8");
   setusername("root");
   setpassword("root");
  }};
 }
 
 /**
  * 主sqlsessionfactory。使用主数据源。自定义sqlsessionfactory后,mybatis就不自动添加sqlsessionfactory了,所以必须有
  *
  * @return .
  * @throws exception .
  */
 @bean
 public sqlsessionfactory sqlsessionfactory() throws exception {
  return new sqlsessionfactorybean() {{
   setdatasource(datasource);
  }}.getobject();
 }
 
 /**
  * 副sqlsessionfactory。使用副数据源
  *
  * @return .
  * @throws exception .
  */
 @bean
 public sqlsessionfactory sqlsessionfactorysecond() throws exception {
  return new sqlsessionfactorybean() {{
   setdatasource(datasourcesecond);
  }}.getobject();
 }
 
 @mapper
 interface usermapper {
  @select("select * from user")
  list<map<string, object>> selectall();
 }
 
 @secondarymapper
 interface secondaryusermapper {
  @select("select * from user")
  list<map<string, object>> selectall();
 }
 
 /**
  * 自定义mapper注解,用于标识使用的数据源
  */
 @target(elementtype.type)
 @retention(retentionpolicy.runtime)
 @interface secondarymapper {
 }
}

控制台输出:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: spring boot ::        (v2.1.0.release)

2018-12-07 11:49:02.596  info 5154 --- [           main] bj.mybatisapp                            : starting mybatisapp on macbook-air-2.local with pid 5154 (/users/yuchao/temp/java/hellomaven/target/classes started by yuchao in /users/yuchao/temp/java/hellomaven)
2018-12-07 11:49:02.633  info 5154 --- [           main] bj.mybatisapp                            : no active profile set, falling back to default profiles: default
2018-12-07 11:49:05.341  info 5154 --- [           main] com.zaxxer.hikari.hikaridatasource       : hikaripool-1 - starting...
2018-12-07 11:49:05.499  info 5154 --- [           main] com.zaxxer.hikari.hikaridatasource       : hikaripool-1 - start completed.
2018-12-07 11:49:05.547  info 5154 --- [           main] org.quartz.impl.stdschedulerfactory      : using default implementation for threadexecutor
2018-12-07 11:49:05.569  info 5154 --- [           main] org.quartz.core.schedulersignalerimpl    : initialized scheduler signaller of type: class org.quartz.core.schedulersignalerimpl
2018-12-07 11:49:05.569  info 5154 --- [           main] org.quartz.core.quartzscheduler          : quartz scheduler v.2.3.0 created.
2018-12-07 11:49:05.570  info 5154 --- [           main] org.quartz.simpl.ramjobstore             : ramjobstore initialized.
2018-12-07 11:49:05.571  info 5154 --- [           main] org.quartz.core.quartzscheduler          : scheduler meta-data: quartz scheduler (v2.3.0) 'quartzscheduler' with instanceid 'non_clustered'
  scheduler class: 'org.quartz.core.quartzscheduler' - running locally.
  not started.
  currently in standby mode.
  number of jobs executed: 0
  using thread pool 'org.quartz.simpl.simplethreadpool' - with 10 threads.
  using job-store 'org.quartz.simpl.ramjobstore' - which does not support persistence. and is not clustered.

2018-12-07 11:49:05.571  info 5154 --- [           main] org.quartz.impl.stdschedulerfactory      : quartz scheduler 'quartzscheduler' initialized from an externally provided properties instance.
2018-12-07 11:49:05.571  info 5154 --- [           main] org.quartz.impl.stdschedulerfactory      : quartz scheduler version: 2.3.0
2018-12-07 11:49:05.571  info 5154 --- [           main] org.quartz.core.quartzscheduler          : jobfactory set to: org.springframework.scheduling.quartz.springbeanjobfactory@769a58e5
2018-12-07 11:49:05.780  warn 5154 --- [           main] reactor.netty.tcp.tcpresources           : [http] resources will use the default loopresources: defaultloopresources {prefix=reactor-http, daemon=true, selectcount=4, workercount=4}
2018-12-07 11:49:05.780  warn 5154 --- [           main] reactor.netty.tcp.tcpresources           : [http] resources will use the default connectionprovider: pooledconnectionprovider {name=http, poolfactory=reactor.netty.resources.connectionprovider$$lambda$284/1788545647@10667848}
2018-12-07 11:49:06.061  info 5154 --- [           main] o.s.s.quartz.schedulerfactorybean        : starting quartz scheduler now
2018-12-07 11:49:06.062  info 5154 --- [           main] org.quartz.core.quartzscheduler          : scheduler quartzscheduler_$_non_clustered started.
2018-12-07 11:49:06.079  info 5154 --- [           main] bj.mybatisapp                            : started mybatisapp in 4.645 seconds (jvm running for 6.354)
2018-12-07 11:49:06.084 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql query [select database()]
2018-12-07 11:49:06.105 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [drop schema if exists one]
2018-12-07 11:49:06.115 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [create schema one]
2018-12-07 11:49:06.117 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [use one]
2018-12-07 11:49:06.119 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [create table user(id int auto_increment primary key, name varchar(32) charset 'utf8')]
2018-12-07 11:49:06.153 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的儿子')]
2018-12-07 11:49:06.157 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的孙子')]
2018-12-07 11:49:06.161 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的曾孙子')]
2018-12-07 11:49:06.164 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [drop schema if exists two]
2018-12-07 11:49:06.174 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [create schema two]
2018-12-07 11:49:06.176 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [use two]
2018-12-07 11:49:06.178 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [create table user(id int auto_increment primary key, name varchar(32) charset 'utf8')]
2018-12-07 11:49:06.226 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的爹')]
2018-12-07 11:49:06.231 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的爷')]
2018-12-07 11:49:06.235 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的太爷')]
2018-12-07 11:49:06.243 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [insert into user(name) values ('人民的老太爷')]
2018-12-07 11:49:06.246 debug 5154 --- [           main] o.s.jdbc.core.jdbctemplate               : executing sql statement [use one]
users:
2018-12-07 11:49:06.271 debug 5154 --- [           main] bj.mybatisapp$usermapper.selectall       : ==>  preparing: select * from user
2018-12-07 11:49:06.297 debug 5154 --- [           main] bj.mybatisapp$usermapper.selectall       : ==> parameters:
2018-12-07 11:49:06.314 debug 5154 --- [           main] bj.mybatisapp$usermapper.selectall       : <==      total: 3
{name=人民的儿子, id=1}
{name=人民的孙子, id=2}
{name=人民的曾孙子, id=3}
secondary users:
2018-12-07 11:49:06.318  info 5154 --- [           main] com.zaxxer.hikari.hikaridatasource       : hikaripool-2 - starting...
2018-12-07 11:49:06.324  info 5154 --- [           main] com.zaxxer.hikari.hikaridatasource       : hikaripool-2 - start completed.
2018-12-07 11:49:06.325 debug 5154 --- [           main] b.m.selectall                            : ==>  preparing: select * from user
2018-12-07 11:49:06.325 debug 5154 --- [           main] b.m.selectall                            : ==> parameters:
2018-12-07 11:49:06.328 debug 5154 --- [           main] b.m.selectall                            : <==      total: 4
{name=人民的爹, id=1}
{name=人民的爷, id=2}
{name=人民的太爷, id=3}
{name=人民的老太爷, id=4}

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对服务器之家的支持。

原文链接:https://baijifeilong.github.io/2018/12/07/mybatis-multiple-datasource/