Spring Boot 入门实战(5)--JdbcTempalte、Mybatis及多数据源整合(单库事务)
本文主要介绍 JdbcTempalte、Mybatis 在多数据源下的配置及使用,实际的应用里可以根据情况选择其中之一或同时使用;事务只涉及单库事务,不涉及 XA 事务。文中所使用到的软件版本:Spring Boot 2.4.4、jdk1.8.0_181、Mybatis 3.5.6、Druid 1.2.5。
1、工程整体结构
2、pom.xml
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.abc.demo</groupId> <artifactId>demo-md</artifactId> <version>1.0</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.4.4</version> <relativePath /> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>commons-lang</groupId> <artifactId>commons-lang</artifactId> <version>2.6</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.2.0</version> <!--systemPath>E:/bin/jar/oracle/ojdbc6.jar</systemPath> <scope>system</scope--> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies> <build> <resources> <resource> <directory>src/main/resources</directory> </resource> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> </resources> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
3、application.yml
spring: datasource: druid: datasource1: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://10.49.196.10:3306/itest?useUnicode=true&characterEncoding=UTF-8 username: root password: 123456 initialSize: 2 minIdle: 1 maxActive: 2 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: true testOnReturn: false maxWait: 6000 filters: wall,stat,slf4j datasource2: driverClassName: oracle.jdbc.OracleDriver url: jdbc:oracle:thin:@10.49.196.10:1521:test username: test password: 123456 initialSize: 2 minIdle: 1 maxActive: 2 validationQuery: SELECT 1 from dual testWhileIdle: true testOnBorrow: true testOnReturn: false maxWait: 6000
4、配置
4.1、数据源配置
配置两个数据源及对应的事务管理器。
package com.abc.demo.config; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @Configuration public class DataSourceConfig { @Primary @Bean(name = "dataSource1") @ConfigurationProperties(prefix="spring.datasource.druid.datasource1") public DataSource dataSource1() { return DruidDataSourceBuilder.create().build(); } @Bean(name = "dataSource2") @ConfigurationProperties(prefix="spring.datasource.druid.datasource2") public DataSource dataSource2() { return DruidDataSourceBuilder.create().build(); } @Bean(name = "transactionManager1") public DataSourceTransactionManager dataSourceTransactionManager1() { return new DataSourceTransactionManager(dataSource1()); } @Bean(name = "transactionManager2") public DataSourceTransactionManager dataSourceTransactionManager2() { return new DataSourceTransactionManager(dataSource2()); } }
4.2、JdbcTempalte配置
package com.abc.demo.config; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.core.JdbcTemplate; import javax.sql.DataSource; @Configuration public class JdbcTempalteConfig { @Bean("jdbcTemplate1") public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource) { return new JdbcTemplate(dataSource); } @Bean("jdbcTemplate2") public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource) { return new JdbcTemplate(dataSource); } }
4.3、Mybatis配置
Mybatis不同数据源的DAO接口需放在不同的包下面。
package com.abc.demo.config; import org.apache.ibatis.mapping.DatabaseIdProvider; import org.apache.ibatis.mapping.VendorDatabaseIdProvider; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.mybatis.spring.annotation.MapperScans; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import javax.sql.DataSource; import java.util.Properties; @Configuration @MapperScans(value = {@MapperScan(basePackages = {"com.abc.demo.dao1"}, sqlSessionFactoryRef = "sqlSessionFactory1"), @MapperScan(basePackages = {"com.abc.demo.dao2"}, sqlSessionFactoryRef = "sqlSessionFactory2")}) public class MybatisConfig { @Primary @Bean("sqlSessionFactory1") public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource datasource) throws Exception { SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); factoryBean.setDataSource(datasource); factoryBean.setDatabaseIdProvider(getDatabaseIdProvider()); factoryBean.setConfigLocation( new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml")); factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/abc/demo/dao1/*.xml")); return factoryBean.getObject(); } @Bean("sqlSessionFactory2") public SqlSessionFactory sqlSessionFactory2(@Qualifier("dataSource2") DataSource datasource) throws Exception { SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); factoryBean.setDataSource(datasource); factoryBean.setDatabaseIdProvider(getDatabaseIdProvider()); factoryBean.setConfigLocation( new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml")); factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/abc/demo/dao2/*.xml")); return factoryBean.getObject(); } @Bean public DatabaseIdProvider getDatabaseIdProvider() { DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider(); Properties properties = new Properties(); properties.setProperty("Oracle", "oracle"); properties.setProperty("MySQL", "mysql"); databaseIdProvider.setProperties(properties); return databaseIdProvider; } }
Mybatis的全局配置resources/mybatis/mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!--配置全局属性 --> <settings> <setting name="useGeneratedKeys" value="true" /> <setting name="useColumnLabel" value="true" /> <setting name="mapUnderscoreToCamelCase" value="true" /> <setting name="logImpl" value="SLF4J"/> </settings> </configuration>
5、业务代码
5.1、实体类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.abc.demo.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; @NoArgsConstructor @AllArgsConstructor @Data @ToString public class School { private String name; private String location; }
School实体类对应a_school表,放在第一个数据库里面。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.abc.demo.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString; @NoArgsConstructor @AllArgsConstructor @Data @ToString public class Student { private String name; private Integer age; }
Student实体类对应a_student表,放在第一二个数据库里面。
5.2、DAO及对应的XML文件
5.2.1、ISchoolDao
package com.abc.demo.dao1; import com.abc.demo.entity.School; import java.util.List; public interface ISchoolDao { void insertSchool(School school); List<School> selectSchool(); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.abc.demo.dao1.ISchoolDao"> <select id="insertSchool" parameterType="com.abc.demo.entity.School"> insert into a_school(name,location) values(#{name}, #{location}) </select> <select id="selectSchool" resultType="com.abc.demo.entity.School"> select name,location from a_school </select> </mapper>
5.2.2、IStudentDao
package com.abc.demo.dao2; import com.abc.demo.entity.Student; import java.util.List; public interface IStudentDao { void insertStudent(Student student); List<Student> selectStudent(); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.abc.demo.dao2.IStudentDao"> <select id="insertStudent" parameterType="com.abc.demo.entity.Student"> insert into a_student(name,age) values(#{name}, #{age}) </select> <select id="selectStudent" resultType="com.abc.demo.entity.Student"> select name,age from a_student </select> </mapper>
5.3、Service
5.3.1、ISchoolService
package com.abc.demo.service; import com.abc.demo.entity.School; import java.util.List; public interface ISchoolService { void addSchool(); List<School> querySchool(); void addSchoolMybatis(); List<School> querySchoolMybatis(); }
package com.abc.demo.service.impl; import com.abc.demo.dao1.ISchoolDao; import com.abc.demo.entity.School; import com.abc.demo.service.ISchoolService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; @Service public class SchoolServiceImpl implements ISchoolService { @Autowired @Qualifier("jdbcTemplate1") private JdbcTemplate jdbcTemplate1; @Autowired private ISchoolDao schoolDao; @Transactional(transactionManager = "transactionManager1") @Override public void addSchool() { jdbcTemplate1.update("insert into a_school(name,location) values(?,?)", "南京大学", "南京"); jdbcTemplate1.update("insert into a_school(name,location) values(?,?)", "北京大学", "北京"); } @Override public List<School> querySchool() { return jdbcTemplate1.query("select * from a_school", new BeanPropertyRowMapper<>(School.class)); } @Transactional(transactionManager = "transactionManager1") @Override public void addSchoolMybatis() { schoolDao.insertSchool(new School("南京大学", "南京")); schoolDao.insertSchool(new School("北京大学", "北京")); } @Override public List<School> querySchoolMybatis() { return schoolDao.selectSchool(); } }
5.3.2、IStudentService
package com.abc.demo.service; import com.abc.demo.entity.Student; import java.util.List; public interface IStudentService { void addStudent(); List<Student> queryStudent(); void addStudentMybatis(); List<Student> queryStudentMybatis(); }
package com.abc.demo.service.impl; import com.abc.demo.dao2.IStudentDao; import com.abc.demo.entity.Student; import com.abc.demo.service.IStudentService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; @Service public class StudentServiceImpl implements IStudentService { @Autowired @Qualifier("jdbcTemplate2") private JdbcTemplate jdbcTemplate2; @Autowired private IStudentDao studentDao; @Transactional(transactionManager = "transactionManager2") @Override public void addStudent() { jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "李白", 20); jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "杜甫", 21); } @Override public List<Student> queryStudent() { return jdbcTemplate2.query("select * from a_student", new BeanPropertyRowMapper<>(Student.class)); } @Transactional(transactionManager = "transactionManager2") @Override public void addStudentMybatis() { studentDao.insertStudent(new Student("李白", 20)); studentDao.insertStudent(new Student("杜甫", 21)); } @Override public List<Student> queryStudentMybatis() { return studentDao.selectStudent(); } }
6、Controller
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.abc.demo.controller; import com.abc.demo.entity.R; import com.abc.demo.entity.School; import com.abc.demo.entity.Student; import com.abc.demo.service.ISchoolService; import com.abc.demo.service.IStudentService; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("/business") public class BusinessController { protected static Logger logger = LoggerFactory.getLogger(BusinessController.class); @Autowired private ISchoolService schoolService; @Autowired private IStudentService studentService; @RequestMapping("addSchool") public R addSchool() { schoolService.addSchool(); return R.ok(); } @RequestMapping("querySchool") public R querySchool() { List<School> list = schoolService.querySchool(); return R.ok(list); } @RequestMapping("addSchoolMybatis") public R addSchoolMybatis() { schoolService.addSchoolMybatis(); return R.ok(); } @RequestMapping("querySchoolMybatis") public R querySchoolMybatis() { List<School> list = schoolService.querySchoolMybatis(); return R.ok(list); } @RequestMapping("addStudent") public R addStudent() { studentService.addStudent(); return R.ok(); } @RequestMapping("queryStudent") public R queryStudent() { List<Student> list = studentService.queryStudent(); return R.ok(list); } @RequestMapping("addStudentMybatis") public R addStudentMybatis() { studentService.addStudentMybatis(); return R.ok(); } @RequestMapping("queryStudentMybatis") public R queryStudentMybatis() { List<Student> list = studentService.queryStudentMybatis(); return R.ok(list); } }
Controller用到的返回对象R:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.abc.demo.entity; /** * 返回数据 */ public class R { private static final long serialVersionUID = 1L; /** * 返回码 * 0 正常,其他异常 */ private int returnCode = 0; /** * 描述 */ private String description = "OK"; /** * 结果数据 */ private Object result; public int getReturnCode() { return returnCode; } public String getDescription() { return description; } public Object getResult() { return result; } public static R ok() { return new R(); } public static R ok(String description) { R r = new R(); r.description = description; return r; } public static R ok(Object result) { R r = new R(); r.result = result; return r; } public static R error() { R r = new R(); r.returnCode = -1; r.description = "未知异常,请联系管理员"; return r; } public static R error(int returnCode, String description) { R r = new R(); r.returnCode = returnCode; r.description = description; return r; } }