springboot - mybatis连接多数据源(动态)

 

 

前言

  在实际开发中我们往往要实现多个数据源的信息操作，比如查询一个数据源的名称时，还要将该数据的其他信息从另一个数据源中提取，如果是多个数据源的话，使用静态的往往比较麻烦，这里我使用动态的进行数据源连接。

这里感谢其他博主的分享，参考文章：第八章 springboot + mybatis + 多数据源，springBoot 动态数据源以及Mybatis多数据源

多数据源切换逻辑

  在我们操作数据库时，一般是Controller调用Service，Service再通过Dao操作数据库，我们要实现数据源的切换，只要在Dao层在操作数据库之前把数据源更改，再将sql语句执行就可以了

废话不多说，我们先把整个项目的结构整理一下：

这个项目的名称是ninemysql,包使用默认的com.example

下面这个图用来理解common包和datasource包中的作用

在我们开始项目之前，我们需要先将pom.xml文件添加依赖

<dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.26</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!--页面依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!-- freemaker依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
    </dependencies>

 

第一步、配置application.properties的两个数据源

    这里我连接的是本地的两个库，可以修改localhost来连接其他ip数据库

#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/my
#spring.datasource.username=root
#spring.datasource.password=root

#the person datasource
datasource.person.driverClassName=com.mysql.jdbc.Driver
datasource.person.url=jdbc:mysql://localhost:3306/test
datasource.person.username=root
datasource.person.password=root
#the users datasource
datasource.users.driverClassName=com.mysql.jdbc.Driver
datasource.users.url=jdbc:mysql://localhost/lxy2018
datasource.users.username=root
datasource.users.password=root

# mybatis接口文件位置
mybatis.typeAliasesPackage=com.example.ninemysql.domain
# mybatis *.xml文件的位置
mybatis.mapperLocations=classpath*:/mapper/*Mapper.xml
#com/example/eightmysql/mapper/*.xml


spring.freemarker.cache = false
spring.thymeleaf.cache = false
default_encoding=UTF-8
locale=zh_CN

第二步、配置实体类，类名要和对应的数据库名表名一致

public class Student {
    private int oldid;
    private int id;
    private String name;
    private String university;

    //类的构造函数，get(),set()方法
}

 

public class User {
    private int oldid;
    private int id;
    private String username;
    private String password;

    //构造函数，get() set()方法
}

第三步、然后我们开始配置多数据源

首先是common包的MyBatisConfig类，将配置文件中的信息链接数据源，再根据数据源创建SqlSessionFactory

注意：这里的mybatis.typeAliasesPackage和mybatis.mapperLocations分别是我在application.properties中配置好的，mybatis.typeAliasesPackage对应实体类的包，mybatis.mapperLocations对应mapper包

@Configuration
@MapperScan(basePackages = "com.example.eightmysql.mapper")
public class MyBatisConfig {
    @Autowired
    private Environment env;

    /**
     * 创建数据源(数据源的名称：方法名可以取为XXXDataSource(),XXX为数据库名称,该名称也就是数据源的名称)
     */
    @Bean
    public DataSource myTestDbDataSource() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", env.getProperty("datasource.person.driverClassName"));
        props.put("url", env.getProperty("datasource.person.url"));
        props.put("username", env.getProperty("datasource.person.username"));
        props.put("password", env.getProperty("datasource.person.password"));
        return DruidDataSourceFactory.createDataSource(props);
    }

    @Bean
    public DataSource myTestDb2DataSource() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", env.getProperty("datasource.users.driverClassName"));
        props.put("url", env.getProperty("datasource.users.url"));
        props.put("username", env.getProperty("datasource.users.username"));
        props.put("password", env.getProperty("datasource.users.password"));
        return DruidDataSourceFactory.createDataSource(props);
    }

    /**
     * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
     * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）
     */
    @Bean
    @Primary
     public DynamicDataSource dataSource(@Qualifier("myTestDbDataSource") DataSource myTestDbDataSource,
                                         @Qualifier("myTestDb2DataSource") DataSource myTestDb2DataSource) {


        DynamicDataSource dataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DatabaseType.test, myTestDbDataSource);
        targetDataSources.put(DatabaseType.lxy2018, myTestDb2DataSource);
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(myTestDbDataSource);// 默认的datasource设置为myTestDbDataSource

        return dataSource;
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean
    //@Primary
    public SqlSessionFactory sqlSessionFactory(DynamicDataSource ds) throws Exception {
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(ds);
        //fb.setDataSource(this.dataSource(myTestDbDataSource, myTestDb2DataSource));// 指定数据源(这个必须有，否则报错)
        // 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加
        fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));// 指定基包
        fb.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapperLocations")));//

        return fb.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }

}

 

然后，建立datasource包，设置动态数据源切换

DatabaseType，这里需要注意，text,lxy2018都是数据库的名称，不是表名

DatabaseContextHolder 

//保存一个线程安全的DatabaseType容器
public class DatabaseContextHolder {
    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
    public static final Logger log = LoggerFactory.getLogger(DatabaseContextHolder.class);


    public static void setDatabaseType(DatabaseType type) {
        log.info("切换到{}数据源", type);
        contextHolder.set(type);
    }

    public static DatabaseType getDatabaseType() {
        return (contextHolder.get());
    }
    public static void clearDatabaseType(){
        contextHolder.remove();
    }
}

 

DynamicDataSource  需要继承AbstractRoutingDataSource类

public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        log.debug("数据源为===>{}", DatabaseContextHolder.getDatabaseType());
        return DatabaseContextHolder.getDatabaseType();
    }
}

 

DataSourceAspect  是springboot的AOP(面向切面)的使用，也可以不写这个类，直接在DAO文件中切换数据源

@Aspect
@Component
public class DataSourceAspect {

    @Pointcut("execution(* com.example.ninemysql.dao.*.*(..))")
    public void JoinPoint(){
    }

    //使用定义切点表达式的方法进行点表达式的引入
    @Before("JoinPoint()")
    public void setDataSourceKey(JoinPoint point){
        //如果连接点所属的类实例是StudentDao
        if(point.getTarget() instanceof StudentDao){
            DatabaseContextHolder.setDatabaseType(DatabaseType.lxy2018);
        }else{//连接点是UserDao,可以不写是默认的数据源
            DatabaseContextHolder.setDatabaseType(DatabaseType.test);
        }
    }
}

最后是Controller

DeleteController

@Controller
public class DeleteController {


    ModelAndView modelAndView;
    @Autowired
    private UserService userService;
    @Autowired
    private StudentService studentService;

    //修改页面
    @RequestMapping("/toupdpage")
    public ModelAndView updpage(@RequestParam("updid") Integer id,Map<String,Object> res){
        ModelAndView mv = new ModelAndView();
        res.put("id",id);
        mv.setViewName("updpage");
        return mv;
    }

    //删除数据
    //@Transactional
    @RequestMapping(value = "/deleteName1")
    public String deleteName(@RequestParam("delid")int id,Map<String,Object> res)throws Exception {
     if(studentService.seleteperson(id) == null){
            res.put("error", "student没有这个ID");
     }else if (userService.findById(id) == null) {
            res.put("error", "user没有这个ID");
     }else if ((studentService.delete(id) != 1) || (userService.dele(id) != 1))
       res.put("error","删除失败");

        return "success";
    }

    //增加数据
    @RequestMapping(value = "/savePerson1")
    public String savePerson(@RequestParam("id") int id,
                            @RequestParam("username")String username,
                            @RequestParam("password")String password,
                            @RequestParam("name")String name,
                            @RequestParam("university")String university,Map<String,Object> res){
        if((studentService.seleteperson(id) != null) && (userService.findById(id) != null)) {
            res.put("error", "这个ID已经被占用");
        }else if((studentService.save(id,name,university)!=1)||((userService.sav(id,username,password)!=1)))
            res.put("error","增加数据失败");

            return "success";
    }

    //修改数据
    @RequestMapping(value = "/updaPerson1")
    public String updaPerson1(@RequestParam("oldid") int oldid,
                             @RequestParam("id") int id,
                             @RequestParam("username")String username,
                             @RequestParam("password")String password,
                             @RequestParam("name")String name,
                             @RequestParam("university")String university,Map<String,Object> res){
        if(studentService.seleteperson(id) != null && oldid!=id) {
            res.put("error", "这个ID已经被student占用");
        }else if(userService.findById(id) != null && oldid!=id){
            res.put("error", "这个ID已经被user占用");
        }else if((studentService.update(oldid,id,name,university)!=1)||((userService.upd(oldid,id,username,password)!=1)))
            res.put("error","修改数据失败");

        return "success";
    }

}

FindController

@Controller
    public class FindController {

    ModelAndView modelAndView;
    @Autowired
    private UserService userService;
    @Autowired
    private StudentService studentService;

    //主页
    @RequestMapping("/aaa")
    public ModelAndView insert(){
        ModelAndView mv = new ModelAndView();
        mv.setViewName("index.html");
        return mv;
    }

    //查看综合数据
    @RequestMapping(value = "/ifindAll")
    public String find1All(Map<String,Object> res){
        List<Student> list1 =studentService.find1all();
        List<User> list2 = userService.AllUser();
        List<Object> reslist = new ArrayList<>();
       for(Student student:list1){
           for(User user:list2){
              if(student.getId() == user.getId()) {
                  Map<String,Object> context = new HashMap<>();
                  context.put("id",user.getId());
                  context.put("name", student.getName());
                  context.put("username",user.getUsername());
                  context.put("password",user.getPassword());
                  context.put("university",student.getUniversity());
                  reslist.add(context);
                  //System.out.println(reslist);
              }
           }
       }
        res.put("listres",reslist);
        return "a";
    }

    //筛选name,username,university找到符合的记录
    @RequestMapping(value = "/findmore")
    public String findmore(HttpServletRequest request,Map<String, Object> res){
        String name = request.getParameter("name");
        String username = request.getParameter("username");
        String university = request.getParameter("university");

        List<Object> reslist = new ArrayList<>();
        List<Student> Studentlist = studentService.seleteDouble(name,university);
        List<User> Userlist = userService.findByName(username);
        //返回错误信息
        if((Userlist.size()==0) && (Studentlist.size()==0)) {
            res.put("error", "没有这个数据");
            return "success";
        }else{
            for(Student student : Studentlist){
                for(User user:Userlist)
                    if(user.getId() == student.getId())
                        reslist.addAll(mapput(student,user));
            }
        }
        if(reslist.size() == 0) {
            res.put("error", "没有这个数据");
            return "success";
        }
        res.put("listres",reslist);
        return "a";
    }

    //查看符合该id的记录
    @RequestMapping(value = "/seletePersonId1")
    public String seletePerson(@RequestParam("id")int id,Map<String,Object> res){
        Student s=studentService.seleteperson(id);
        User user = userService.findById(id);
        List<Object> result = new ArrayList<>();
        //返回错误信息
        if(s!= null && user != null){
            /*return "找到id是 "+id+" 的数据为"+"..username:"+user.getUsername()+"..password:"+user.getPassword()
                    +"..name:"+s.getName()+"..university:"+s.getUniversity();*/
            result.addAll(mapput(s,user));
        }else {
            res.put("error", "没有这个数据");
            return "success";
        }
        res.put("findid",id);
        res.put("listres",result);
        return "a";
    }


    //模糊搜索符合username的记录
    @RequestMapping(value = "/findByname1")
    public String findByname(@RequestParam("username")String username,Map<String,Object> res){
        List<User> userlist = userService.findByName(username);
        List<Object> result = new ArrayList<>();
        //返回错误信息
        if(userlist.size() == 0){
            res.put("error", "没有这个数据");
            return "success";
        }else {
            for (User user : userlist) {
                int id = user.getId();
                Student s = studentService.seleteperson(id);
                result.addAll(mapput(s, user));
                /*return "找到username是 "+username+" 的数据为"+"..password:"+user.getPassword()
                        +"..name:"+s.getName()+"..university:"+s.getUniversity();*/
            }
        }
        res.put("findname", username);
        res.put("listres",result);
        return "a";
    }

    //将返回数据打包
    public List<Object> mapput(Student student,User user){
        Map<String,Object> context = new HashMap<>();
        context.put("id",user.getId());
        context.put("name", student.getName());
        context.put("username",user.getUsername());
        context.put("password",user.getPassword());
        context.put("university",student.getUniversity());
        List<Object>  list = new ArrayList<>();
        list.add(context);
        return list;
        //result.put("listres",list);
        //return result;
    }
}
//模糊搜索符合name的记录
    /*@RequestMapping(value = "/seletePerson1")
    public String seletePerson(@RequestParam("name")String name,Map<String,Object> res){
        List<Student> student1= studentService.seleteperson(name);
        List<Object> result = new ArrayList<>();
        for(Student student:student1){
            int id = student.getId();
            User user = userService.findById(id);
            result.addAll(mapput(student, user));
                *//*return "找到name是 "+name+" 的数据为"+"..id:"+s.getId()+"..username:"+user.getUsername()
                        +"..password:"+user.getPassword()+"..university:"+s.getUniversity();*//*
            }
        res.put("findname", name);
        res.put("listres",result);
        return "a";
    }

    //模糊搜索符合university的记录
    @RequestMapping(value = "/seleteUniversity")
    public String seleteUniversity(@RequestParam("university")String university,Map<String,Object> res){
        List<Student> student1= studentService.seleteStuuniversity(university);
        List<Object> result = new ArrayList<>();
        for(Student student : student1) {
            int id = student.getId();
            User user = userService.findById(id);
               *//* return "找到university是 "+university+" 的数据为"+"..id:"+s.getId()
                        +"..username:"+user.getUsername()+"..password:"+user.getPassword()+"..name:"+s.getName();*//*
                result.addAll(mapput(student, user));
        }
        res.put("finduniversity", university);
        res.put("listres",result);
        return "a";
    }*/

剩下的service,mapper,dao可以自己设置，我这里是使用xml文件进行sql操作，放在resource/mapper下了，

做到这里其实我们的数据源切换已经差不多了，不过如果能实现界面化才是我们的目标，我会在下一篇再解释(附源码)/

 

下一篇----动态多数据源展示(界面化)