SpringBoot进阶教程 | 第四篇：整合Mybatis实现多数据源

这篇文章主要介绍，通过Spring Boot整合Mybatis后如何实现在一个工程中实现多数据源。同时可实现读写分离。

准备工作

环境：

windows
jdk 8
maven 3.0
IDEA

创建数据库表

在mysql中创建student库并执行下面查询创建student表

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROp TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(15) NOT NULL,
  `sname` varchar(50) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `dept` varchar(25) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '李同学', '1', '王同学学习成绩很不错', '2010-07-22', '17');


在mysql中创建teacher库并执行下面查询创建teacher表
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `Tno` varchar(20) NOT NULL DEFAULT '',
  `Tname` varchar(50) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `dept` varchar(25) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`Tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王老师', '1', '王老师上课很认真', '2018-07-06', '35');


构建工程


	4.0.0

	
		cn.zhangbox
		spring-boot-study
		1.0-SNAPSHOT
	

	cn.zhangbox
	spring-boot-mybatis-datasource
	0.0.1-SNAPSHOT
	jar

	spring-boot-mybatis-datasource
	this project for Spring Boot



	
		UTF-8
		UTF-8
		1.8
		
		3.4
		1.10
		1.2.0
		1.16.14
		1.2.41
		1.1.2
	

	
		
		
			aliyunmaven
			http://maven.aliyun.com/nexus/content/groups/public/
		
	

	

		
		
			org.mybatis.spring.boot
			mybatis-spring-boot-starter
			${mybatis-spring-boot.version}
		
		

		
		
			org.springframework.boot
			spring-boot-starter-web
		
		

		
		
			mysql
			mysql-connector-java
			runtime
		
		

		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
		

		
		
			org.apache.commons
			commons-lang3
			${commons-lang3.version}
		
		
			commons-codec
			commons-codec
			${commons-codec.version}
		
		

		
		
			com.alibaba
			fastjson
			${fastjson.version}
		
		

		
		
			com.alibaba
			druid-spring-boot-starter
			${druid.version}
		
		

		
		
			org.projectlombok
			lombok
			${lombok.version}
		
		
	

	
		spring-boot-mybatis-datasource
		
		
			org.apache.maven.plugins
			maven-compiler-plugin
			3.5.1
			
				1.8
				1.8
				UTF-8
			
		
		
			org.apache.maven.plugins
			maven-surefire-plugin
			2.19.1
		

		
			org.springframework.boot
			spring-boot-maven-plugin
			
				
					org.springframework
					springloaded
					1.2.4.RELEASE
				
			
			
				cn.zhangbox.admin.SpringBootDruidApplication
				-Dfile.encoding=UTF-8 -Xdebug
					-Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005
				
				true
				true
			
		
			
				org.springframework.boot
				spring-boot-maven-plugin
				
					cn.zhangbox.admin.SpringBootDruidApplication
					-Dfile.encoding=UTF-8 -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005
					true
					true
				
			
		
	



注意：这里引入了lombok插件节省编写实体类时候写get和set方法,这里在idea中进行set和get操作需要下载lombok插件,在设置页面的plugins中搜索lombok插件在中央插件库下载后重启idea即可,更详细的lombok使用教程可以查考:
程序员DD的lombok系列教程:
Lombok：让JAVA代码更优雅 
修改YML配置
#公共配置
server:
    port: 80
    tomcat:
      uri-encoding: UTF-8
spring:
  #激活哪一个环境的配置文件
  profiles:
    active: dev
  #连接池配置
  datasource:
    #配置student库驱动和连接池
    student:
      driver-class-name: com.mysql.jdbc.Driver
      # 使用druid数据源
      type: com.alibaba.druid.pool.DruidDataSource
    #配置teacher库驱动和连接池
    teacher:
      driver-class-name: com.mysql.jdbc.Driver
      # 使用druid数据源
      type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 配置测试查询语句
      validationQuery: SELECT 1 FROM DUAL
      # 初始化大小，最小，最大
      initialSize: 10
      minIdle: 10
      maxActive: 200
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 180000
      testOnBorrow: false
      testWhileIdle: true
      removeAbandoned: true
      removeAbandonedTimeout: 1800
      logAbandoned: true
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxOpenPreparedStatements: 100
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#mybatis
mybatis:
  # 实体类扫描
  type-aliases-package: cn.zhangbox.springboot.entity
  # 配置映射文件位置
  mapper-locations: classpath:mapper
    @Bean(name = "studentDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.student")
    @Primary
    public DataSource writeDataSource() {
 return DataSourceBuilder.create().type(dataSourceType).build();
    }

    
    @Bean(name = "studentSqlSessionFactory")
    @Primary
    public SqlSessionFactory studentSqlSessionFactory(@Qualifier("studentDataSource") DataSource dataSource) throws Exception {
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(dataSource);
 //bean.setTypeAliasesPackage("com.ztzq.data.beans.bigdata");
 bean.setVfs(SpringBootVFS.class);
 bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/student
    @Bean(name = "studentTransactionManager")
    @Primary
    public DataSourceTransactionManager TransactionManager(@Qualifier("studentDataSource") DataSource dataSource) {
 return new DataSourceTransactionManager(dataSource);
    }

    
    @Bean(name = "studentSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("studentSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
 return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建Teacher数据源配置类
在工程java代码目录下创建config的目录在下面创建TeacherDataSourceConfig类加入以下代码:
@Configuration
@MapperScan(basePackages ="cn.zhangbox.springboot.dao.teacher",sqlSessionFactoryRef = "teacherSqlSessionFactory")//mybatis接口包扫描
public class TecaherDataSourceConfig {

    @Value("${spring.datasource.teacher.type}")
    private Class dataSourceType;

    
    @Bean(name = "teacherDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.teacher")
    public DataSource writeDataSource() {
 return DataSourceBuilder.create().type(dataSourceType).build();
    }

    
    @Bean(name = "teacherSqlSessionFactory")
    public SqlSessionFactory teacherSqlSessionFactory(@Qualifier("teacherDataSource") DataSource dataSource) throws Exception {
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(dataSource);
 //bean.setTypeAliasesPackage("com.ztzq.data.beans.bigdata");
 bean.setVfs(SpringBootVFS.class);
 bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/teacher
    @Bean(name = "teacherTransactionManager")
    public DataSourceTransactionManager TransactionManager(@Qualifier("teacherDataSource") DataSource dataSource) {
 return new DataSourceTransactionManager(dataSource);
    }

    
    @Bean(name = "teacherSqlSessionTemplate")
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("teacherSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
 return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建实体
在工程java代码目录下创建entity的目录在下面创建Student类加入以下代码:
@Data
@EqualsAndHashCode(callSuper = false)
public class Student {

    private static final long serialVersionUID = 1L;

	
	private Integer sno;
    
	private String sname;
    
	private String sex;
    
	private String birth;
    
	private String age;
    
	private String dept;

}

在工程java代码目录下创建entity的目录在下面创建Teacher类加入以下代码:
@Data
@EqualsAndHashCode(callSuper = false)
public class Teacher {

    private static final long serialVersionUID = 1L;

	
	private Integer tno;
    
	private String tname;
    
	private String sex;
    
	private String birth;
    
	private String age;
    
	private String dept;

}

创建Controller
在工程java代码目录下创建controller的目录在下面创建StudentConteroller类加入以下代码:
@Controller
@RequestMapping("/student")
public class StudentConteroller {
    private static final Logger LOGGER = LoggerFactory.getLogger(StudentConteroller.class);

    @Autowired
    protected StudentService studentService;

    
    @ResponseBody
    @GetMapping("/list")
    public String list(String sname, Integer age, ModelMap modelMap) {
 String json = null;
 try {
     List studentList = studentService.getStudentList(sname, age);
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询成功");
     modelMap.put("studentList", studentList);
     json = JSON.toJSONString(modelMap);
 } catch (Exception e) {
     e.printStackTrace();
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询失败===>" + e);
     LOGGER.error("查询失败===>" + e);
     json = JSON.toJSONString(modelMap);
 }
 return json;
    }
}

在工程java代码目录下创建controller的目录在下面创建TeacherConteroller类加入以下代码:
@Controller
@RequestMapping("/teacher")
public class TeacherConteroller {
    private static final Logger LOGGER = LoggerFactory.getLogger(TeacherConteroller.class);

    @Autowired
    protected TeacherService teacherService;

    
    @ResponseBody
    @GetMapping("/list")
    public String list(String tname, Integer age, ModelMap modelMap) {
 String json = null;
 try {
     List teacherList = teacherService.getTeacherList(tname, age);
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询成功");
     modelMap.put("teacherList", teacherList);
     json = JSON.toJSONString(modelMap);
 } catch (Exception e) {
     e.printStackTrace();
     modelMap.put("ren_code", "0");
     modelMap.put("ren_msg", "查询失败===>" + e);
     LOGGER.error("查询失败===>" + e);
     json = JSON.toJSONString(modelMap);
 }
 return json;
    }
}

创建Service
在工程java代码目录下面创建service目录在下面创建StudentService类加入以下代码:
public interface StudentService {

    
    List getStudentList(String sname, Integer age);
}

在工程java代码目录下面创建service目录在下面创建TeacherService类加入以下代码:
public interface TeacherService {

    
    List getTeacherList(String tname, Integer age);
}


创建ServiceImpl
在工程java代码目录下的service的目录下面创建impl目录在下面创建StudentServiceImpl类加入以下代码:
@Service("StudentService")
@Transactional(readonly = true, rollbackFor = Exception.class)
public class StudentServiceImpl implements StudentService {

	@Autowired
	StudentDao studentDao;

	@Override
	public List getStudentList(String sname, Integer age) {
		return studentDao.getStudentList(sname,age);
	}
}

在工程java代码目录下的service的目录下面创建impl目录在下面创建TeacherServiceImpl类加入以下代码:
@Service("TeacherService")
@Transactional(readonly = true, rollbackFor = Exception.class)
public class TeacherServiceImpl implements TeacherService {

	@Autowired
	TeacherDao teacherDao;

	@Override
	public List getTeacherList(String tname, Integer age) {
		return teacherDao.getTeacherList(tname,age);
	}
}

创建Dao
在工程java代码目录下创建dao的目录下面创建student目录在此目录下创建StudentDao类加入以下代码:
public interface StudentDao {

	List getStudentList(@Param("sname")String sname, @Param("age")Integer age);

}

在工程java代码目录下创建dao的目录下面创建teacher目录在此目录下创建TeacherDao类加入以下代码:
public interface TeacherDao {

	List getTeacherList(@Param("tname") String tname, @Param("age") Integer age);

}

创建Mapper映射文件
在工程resource目录下创建mapper的目录下创建student目录在此目录下面创建StudentMapper.xml映射文件加入以下代码:



    
    
 SELECT
     s.sno,
     s.sname,
     s.sex,
     s.dept,
     s.birth,
     s.age
 FROM
     student s
 WHERe
 1 = 1
 
     and s.sname = #{sname}
 
 
     and s.age = #{age}
 
    


在工程resource目录下创建mapper的目录下创建teacher目录在此目录下面创建TeacherMapper.xml映射文件加入以下代码: