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

àì夳堔傛蜴生んèń 2022-05-17 03:45 275阅读 0赞

# 前言 #

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

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

### 多数据源切换逻辑 ###

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

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

![70][]

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

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

![70 1][]

### 在我们开始项目之前，我们需要先将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>
            
            <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都是数据库的名称，不是表名 ###

![70 2][]

### 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下了，

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

### 下一篇----[动态多数据源展示(界面化)][Link 1] ###

[springboot _ mybatis _]: https://www.cnblogs.com/java-zhao/p/5413845.html
[springBoot _Mybatis]: https://blog.csdn.net/tengxing007/article/details/78424645
[70]: /images/20220517/9a1a10a550454b2584572e0f09ae8463.png
[70 1]: /images/20220517/6d74343ca42c4c1aa7ee7ab30aca5680.png
[70 2]: /images/20220517/c96c03d0fe624d9a9b17c76fa131d5be.png
[Link 1]: https://blog.csdn.net/qq_42685050/article/details/81452091