mybatis 动态数据源 :
springboot项目yml配置:
spring:
application:
name: name #
autoconfigure:
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
datasource:
maintype: oracle#用来获取主数据源是哪种类型
dynamic:
primary: master
strict: false
datasource:
master:
driver-class-name: org.postgresql.Driver
url: jdbc:orcl://ip:host/postgres?searchpath=xxxx&stringtype=xxx&tSchema=xxx
username: username
password: password
p6spy: true
后台代码工具类:
package com.xxx;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.BasicDataSourceCreator;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.zz.project.provide.jk.mapper.ZzDataSourceDto;
import lombok.AllArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.Set;
/**
* @author
* @date 2020/9/4 9:43
*/
@Component
@AllArgsConstructor
public class MoreDataSourceUtils {
private final DataSource dataSource;
private final BasicDataSourceCreator basicDataSourceCreator;
public void addDataSourceToDS(ZzDataSourceDto dto){
DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
Set<String> dataSourceIds = ds.getCurrentDataSources().keySet();
if(!dataSourceIds.contains(dto.getPollName())){
DataSourceProperty dataSourceProperty = new DataSourceProperty();
BeanUtils.copyProperties(dto, dataSourceProperty);
DataSource dataSource = basicDataSourceCreator.createDataSource(dataSourceProperty);
ds.addDataSource(dto.getPollName(), dataSource);
}
}
public void deleteDataSourceToDS(String dataSourceId){
//dataSourceId就是存在数据源列表中的key
DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
ds.removeDataSource(dataSourceId);
}
/**
* 数据源id
* @param datasourceId
* @return 返回添加到DS中的数据源的类型
*/
public String addDataSourceToDSByDatasourceId(String datasourceId,String mainDataSourceType){
//主数据源
if(datasourceId.equals("0") ||"master".equals(datasourceId)){
return mainDataSourceType;
}else{
//0.先判断当前数据源中是否有该数据源id 若没有则继续 若有返回该数据源类型
//1.根据数据源id获取数据源信息
//2.组装成ZzDataSourceDto dto
//3.添加到DS addDataSourceToDS(dto)
//返回数据源类型
return mainDataSourceType;
}
}
}
动态数据库连接:
public void test(){
ZzDataSourceDto dto = new ZzDataSourceDto();
dto.setUsername(sjk.getUsername());
dto.setPassword(sjk.getPassword());
dto.setPollName(sjk.getSid().toString());
dto.setUrl(sjk.getWzslmc());
dto.setDriverClassName("org.postgresql.Driver");
setDataSource(dto);
}
public void setDataSource(ZzDataSourceDto dto){
moreDataSourceUtils.addDataSourceToDS(dto);
return ;
}
public void removeDataSource(String dataSourceId) {
moreDataSourceUtils.deleteDataSourceToDS(dataSourceId);
return ;
}
就这。。
查询sql语句有:
package com.zz.project.provide.jk.service.impl;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zz.project.provide.jk.mapper.MoreDataSourceMapper;
import com.zz.project.provide.jk.service.MoreDataSourceService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
/**
* @author
* @date 2020/9/4 11:55
*/
@Service
public class MoreDataSourceServiceImpl implements MoreDataSourceService {
@Resource
public MoreDataSourceMapper moreDataSourceMapper;
@DS("#dataSourceId")
@Override
public List<Map<String, Object>> selectList(Page<Map<String, Object>> page, String tableName
, String querySql,String dataSourceId) {
return moreDataSourceMapper.selectList(page,tableName,querySql);
}
@DS("#dataSourceId")
@Override
public Integer insertData(String tableName, String columnNames, String columnValus,String dataSourceId) {
return moreDataSourceMapper.insertData(tableName,columnNames,columnValus);
}
@DS("#dataSourceId")
@Override
public Integer updateData(String tableName, String columnsSql, String id,String dataSourceId) {
return moreDataSourceMapper.updateData(tableName,columnsSql,id);
}
@DS("#dataSourceId")
@Override
public Integer deleteData(String tableName, String id,String dataSourceId) {
return moreDataSourceMapper.deleteData(tableName,id);
}
@DS("#dataSourceId")
@Override
public Integer executeSQL(String sqlStr, String dataSourceId)
{
return moreDataSourceMapper.executeSQL(sqlStr);
}
@DS("#dataSourceId")
@Override
public List<Map<String,Object>> selectAnysql( String querySql, String dataSourceId){
return moreDataSourceMapper.selectAnysql(querySql) ;
}
@DS("#dataSourceId")
@Override
public Map<String,Object> selectAnySingle( String querySql, String dataSourceId){
return moreDataSourceMapper.selectAnySingle(querySql) ;
}
@DS("#dataSourceId")
@Override
public Object selectOne( String querySql, String dataSourceId){
return moreDataSourceMapper.selectOne(querySql) ;
}
}
mapper.xml 为:
<?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.project.provide.jk.mapper.MoreDataSourceMapper">
<select id="selectList" parameterType="java.lang.String" resultType="java.util.Map">
select * from ${tableName} where 1=1
<if test="querySql != null and querySql !=''" >
and ${querySql}
</if>
</select>
<insert id="insertData" parameterType="java.lang.String">
insert into ${tableName} (${columnNames}) values (${columnValus})
</insert>
<update id="updateData" parameterType="java.lang.String">
update ${tableName} set ${columnsSql} where id=#{id}
</update>
<delete id="deleteData" parameterType="java.lang.String">
delete ${tableName} where id=#{id}
</delete>
<update id="executeSQL" parameterType="java.lang.String">
${sqlStr}
</update>
<select id="selectAnysql" resultType="java.util.Map">
${querySql}
</select>
<select id="selectAnySingle" resultType="java.util.Map">
${querySql}
</select>
<select id="selectOne" resultType="java.lang.Object">
${querySql}
</select>
</mapper>
为了记录而写,叙述不详请见谅。