学习使用mybatis配置多数据源
目标
实现一个插入商品的功能,根据商品的类型插入不同的数据库。
1、创建两个数据库product_one
和product_two
,
2、两个库分别创建一张product表
3、实现增加商品功能
创建数据库表
一模一样的表
product_one
use product_one;
CREATE TABLE `product` (
`id` varchar(12) NOT NULL COMMENT '商品编号',
`product_name` varchar(60) NOT NULL COMMENT '商品名称',
`stock` integer NOT NULL COMMENT '库存',
`product_type` int NOT NULL COMMENT '商品类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
product_two
use product_two;
CREATE TABLE `product` (
`id` varchar(12) NOT NULL COMMENT '商品编号',
`product_name` varchar(60) NOT NULL COMMENT '商品名称',
`stock` integer NOT NULL COMMENT '库存',
`product_type` int NOT NULL COMMENT '商品类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Java实体
import lombok.Data;
@Data //使用lombok省略getter setter
public class Product {
private String id;
private String productName;
private Integer stock;
private int productType;
}
mybatis->mapper接口
划重点,后面会讲到:
因为mybatis数据库配置会根据mapper接口所在的包进行扫描,所以两个mapper要分开放在两个包中。
mypper1
import org.apache.ibatis.annotations.Insert;
import org.monkey.mybatis.twodatasource.entity.Product;
import org.springframework.stereotype.Repository;
@Repository("mapper1")
public interface ProductMapper {
@Insert("INSERT INTO PRODUCT(ID, PRODUCT_NAME, STOCK, PRODUCT_TYPE) " +
"VALUES(#{id}, #{productName}, #{stock}, #{productType})")
int insert(Product product);
mapper2
import org.apache.ibatis.annotations.Insert;
import org.monkey.mybatis.twodatasource.entity.Product;
import org.springframework.stereotype.Repository;
@Repository("mapper2")
public interface ProductMapper {
/*
@Results({
@Result(column = "ID", property = "id"),
@Result(column = "PRODUCT_NAME", property = "productName"),
@Result(column = "STOCK", property = "stock"),
@Result(column = "PRODUCT_TYPE", property = "productType")
})
*/
@Insert("INSERT INTO PRODUCT(ID, PRODUCT_NAME, STOCK, PRODUCT_TYPE) " +
"VALUES(#{id}, #{productName}, #{stock}, #{productType})")
int insert(Product product);
}
service
这里定义mapper1和mapper2的代码有点丑陋,原本想着定义一个接口,用接口定义一个mapper,在判断商品类型选择mapper1或者mapper2。
但是没有解决如何在代码中自动装配,不是本文的重点,放弃了!!懂的大佬指点迷津可?
import org.monkey.mybatis.twodatasource.entity.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class ProductService {
//@Autowired
//private org.monkey.mybatis.twodatasource.mapper.IProductMapper mapper;
@Autowired
private org.monkey.mybatis.twodatasource.mapper.ds1.ProductMapper mapper1;
@Autowired
private org.monkey.mybatis.twodatasource.mapper.ds2.ProductMapper mapper2;
public int add(Product product) {
System.out.println("add product: " + product);
// 判断type,选择调用具体的mapper实现增加操作
if (product.getProductType() == 0) {
return mapper1.insert(product);
} else {
return mapper2.insert(product);
}
}
}
controller
import org.monkey.mybatis.twodatasource.entity.Product;
import org.monkey.mybatis.twodatasource.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/product")
public class ProductController {
@Autowired
private ProductService productService;
@PostMapping("/add")
public String addProduct(@RequestBody Product product) {
int rst = productService.add(product);
if (rst == 1) {
return "success";
}
return "faulire";
}
}
多数据源—yml配置(重点1
)
server:
port: 8081
spring:
datasource:
#与单数据源相比,增加一层,是自定义的,可以是test1-xxx,也可以是mysql selserver oracle,下文书写java配置类会用到
test1:
driver-class-name: com.mysql.cj.jdbc.Driver
#这里 由单数据源的 url 改成 jdbc-url,原因未知
jdbc-url: jdbc:mysql://localhost:3306/product_one?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: Admin888
test2:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/product_two?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: Admin888
#mybatis:
# mapper-locations: classpath:mapping/*Mapper.xml
多数据源—java配置(重点2
)
定义两个java配置类,原理不敢随意解读,参考大佬博文
https://www.cnblogs.com/ityouknow/p/6102399.html
贴上完整代码
DataSourceConfig1
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
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.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
// MapperScan扫描指定package下的mapper接口
// sqlSessionTemplateRef SqlSessionTemplate对象
@MapperScan(basePackages = {"org.monkey.mybatis.twodatasource.mapper.ds1"},
sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DatabaseConfig1 {
// 将这个对象抽取到一个公共配置文件中了,不要问为什么,问就是没学懂
@Autowired
@Qualifier("ds1")
private DataSource ds1;
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory test1SqlSessionFactory() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds1);
// 注释的这三行是配置读取映射文件的场景的,我没有使用映射文件,而是注解,所以注释了
//ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
//Resource[] resources = resourcePatternResolver.getResources("classpath:mapping/*/*.xml");
//factoryBean.setMapperLocations(resources);
return factoryBean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager test1TransactionManager() {
return new DataSourceTransactionManager(ds1);
}
// @Qualifier 说明一下这个注解,用在有多个一样的对象的情况下,
// spring不知道将哪一个对象注入给定的引用时,用这个注解指定注入哪一个对象。
// 比如此时,因为多数据源配置,会有2个sqlSessionFactory
// 名字定为test1SqlSessionFactory和test2SqlSessionFactory,
// 这里是数据源1的配置,所以我指定要注入test1SqlSessionFactory
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate test1SqlSessionTemplate(
@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
}
}
DataSourceConfig2
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
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.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"org.monkey.mybatis.twodatasource.mapper.ds2"},
sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DatabaseConfig2 {
@Autowired
@Qualifier("ds2")
private DataSource ds2;
@Bean(name = "test2SqlSessionFactory")
@Primary
public SqlSessionFactory test2SqlSessionFactory() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds2);
/*ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
Resource[] resources = resourcePatternResolver.getResources("classpath:mapping/course/*.xml");
factoryBean.setMapperLocations(resources);*/
return factoryBean.getObject();
}
@Bean(name = "test2TransactionManager")
@Primary
public DataSourceTransactionManager test2TransactionManager() {
return new DataSourceTransactionManager(ds2);
}
@Bean(name = "test2SqlSessionTemplate")
@Primary
public SqlSessionTemplate test2SqlSessionTemplate(
@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
}
}
DataSourceConfig
公共配置类
重点是@ConfigurationProperties(prefix = "spring.datasource.test1")
和@ConfigurationProperties(prefix = "spring.datasource.test2")
,这里用这个注解指定数据库配置的前缀,也就是我们在yml文件中配置的那一层
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean(name = "ds1")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.test1") // application.properteis中对应属性的前缀
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
@Bean(name = "ds2")
@ConfigurationProperties(prefix = "spring.datasource.test2") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}
验证结果
插入10条数据,衣服类的type子字段是0,电脑组件type是1
{"id": "1001", "productName": "鼠标", "stock": "100", "productType": "1"},
{"id": "1002", "productName": "键盘", "stock": "100", "productType": "1"}
{"id": "1003", "productName": "显示器", "stock": "100", "productType": "1"}
{"id": "1004", "productName": "显卡", "stock": "100", "productType": "1"}
{"id": "1005", "productName": "CPU", "stock": "100", "productType": "1"}
{"id": "1006", "productName": "衣服", "stock": "100", "productType": "0"}
{"id": "1007", "productName": "帽子", "stock": "100", "productType": "0"}
{"id": "1008", "productName": "鞋子", "stock": "100", "productType": "0"}
{"id": "1009", "productName": "袜子", "stock": "100", "productType": "0"}
{"id": "1010", "productName": "鞋子", "stock": "100", "productType": "0"}
插入结果如图:
终于写完了。。。
我自己学习时,找了很多资料,很多都只贴了一部分代码,尤其是没有import列表,有时候就刚好卡在import了错误的注解或者类上,所以贴了完整的代码,看着有点多,我相信应该会有用的吧!!!