springboot连接两个mysql

最新推荐文章于 2024-04-26 13:29:49 发布

蓝黑墨水

最新推荐文章于 2024-04-26 13:29:49 发布

阅读量2.1k

点赞数 5

分类专栏：学习文章标签： mysql

本文链接：https://blog.csdn.net/lanxiaziyi/article/details/115331606

版权

学习专栏收录该内容

146 篇文章 0 订阅

订阅专栏

文章目录

前言
步骤
注意我没写service层（我懒），也没用sql的xml
补充
- 本地连接root的时候不能用局域网ip
- 必须要加上时区

前言

一个很简单。
两个有点麻烦，网上很多例子都不完全。要不就是过时的（springboot进化的有点快）。
参考

步骤

目录结构

在这里插入图片描述

配置文件（properties或者xml）

#数据链接1
master.datasource.url=jdbc:mysql://127.0.0.1:3306/shimo?&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=UTC
master.datasource.driver-class-name=com.mysql.jdbc.Driver
master.datasource.username=root
master.datasource.password=123456

#数据链接2
second.datasource.url=jdbc:mysql://127.0.0.1:3306/testmysql?&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=UTC
second.datasource.driver-class-name=com.mysql.jdbc.Driver
second.datasource.username=root
second.datasource.password=123456

config文件

第一个

package com.example.config;



import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.mapper.master", sqlSessionFactoryRef  = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.example.mapper.DB1";
    static final String MAPPER_LOCATION = "classpath*:mapper/master/*.xml";

    @Value("${master.datasource.url}")
    private String url;
    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driver-class-name}")
    private String driverClass;



    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("datasource.master")
    public DataSource masterDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactoryBean.getObject();
    }
}

第二个

package com.example.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE,sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SecondDataSourceConfig {

    // 精确到
    static final String PACKAGE = "com.example.mapper.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

    @Value("${second.datasource.url}")
    private String url;

    @Value("${second.datasource.username}")
    private String user;

    @Value("${second.datasource.password}")
    private String password;

    @Value("${second.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/DB2/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

注意里面有冗余的代码。里面的xml文件我没用到

control 文件

package com.example.controller;

import com.example.entity.TiktokAccountMap;
import com.example.entity.UserInfo;
import com.example.mapper.master.AppInfoMapper;
import com.example.mapper.second.UserInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

@RestController
@RequestMapping("/appone")
public class AppOneController {

    @Autowired
    private AppInfoMapper appInfoMapper;

    @Autowired
    private UserInfoMapper userInfoMapper;

    @RequestMapping(value = "/testmysql",method = RequestMethod.GET)
    public String getInfo(HttpServletRequest request){

        List<TiktokAccountMap> t_appList = appInfoMapper.getAllAccount();
        List<UserInfo>  t_userList = userInfoMapper.getAllUser();


        return "hello";
    }

}

实体文件

package com.example.entity;

import lombok.Data;

@Data
public class TiktokAccountMap {
    private String tiktok_open_id;
    private String xzm_user_code;
}

package com.example.entity;

import lombok.Data;

@Data
public class UserInfo {
    private String username;
    private String userpassword;
    private int age;
    private String sex;
    private int userId;
    private int addtime;
}

mapper文件

package com.example.mapper.master;

import com.example.entity.TiktokAccountMap;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface AppInfoMapper {
    @Select({"select * from tiktok_account_map"})
    List<TiktokAccountMap> getAllAccount();

}

第二个

package com.example.mapper.second;

import com.example.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface UserInfoMapper {
    @Select({"select * from usertable"})
    List<UserInfo> getAllUser();
}

从代码中可以看到，是在config中指定了应用范围

注意我没写service层（我懒），也没用sql的xml

补充

本地连接root的时候不能用局域网ip

也就是只能用127.0.0.1或者localhost

master.datasource.url=jdbc:mysql://127.0.0.1:3306/douyin?&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=UTC

必须要加上时区

master.datasource.url=jdbc:mysql://127.0.0.1:3306/douyin?&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=UTC