• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

springboot-mybatis-multiDatasourcce-xml

开发技术 开发技术 4小时前 1次浏览

0 程序结构图如下

springboot-mybatis-multiDatasourcce-xml

1 pom.xml

代码如下,需要注意的点是Druid数据池的依赖,写为

<artifactId>druid-spring-boot-starter</artifactId>

才不会报错,全部依赖代码如下:

<properties>
        <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
        <mysql-connector>5.1.39</mysql-connector>
        <druid>1.0.18</druid>
    </properties>

    <dependencies>

        <!-- Spring Boot Web 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot Test 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

        <!-- Druid 数据连接池依赖, <artifactId>druid</artifactId>这样写虽然数据库正常运行,但是会报错-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!-- Junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

2 application.properties

代码如下

## master 数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/springbootdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.cj.jdbc.Driver

## cluster 数据源配置
cluster.datasource.url=jdbc:mysql://localhost:3306/springbootdb_cluster?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
cluster.datasource.username=root
cluster.datasource.password=123456
cluster.datasource.driverClassName=com.mysql.cj.jdbc.Driver

3 Config层

3.1 主数据源配置类

代码如下:

**
 * -@MapperScan 扫描 Mapper 接口并容器管理,包路径精确到 master,为了和下面 cluster 数据源做到精确区分
 */

@Configuration
// 扫描 Mapper 接口并容器管理
// 他这个PACKAGE所以就是类中定义的static final 属性
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    // 精确到 master 目录,以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

    // @Value-给普通变量注入值, 获取全局配置文件 application.properties 的 kv 配置
    @Value("${master.datasource.url}")
    private String url;

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

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

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

    /**
     * -@Primary
     * 标志这个 Bean 如果在多个同类 Bean 候选时,该 Bean 优先被考虑。
     * 「多数据源配置的时候注意,必须要有一个主数据源,用 @Primary 标志该 Bean」
     */

    @Bean(name = "masterDataSource")
    @Primary
    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());
    }

    /**
     * -@Qualifier 作用
     * 在需要自动注入javabean时,如果注入的是一个接口,而这个接口又有多个实现类,则会报错
     * 解决办法是在实现类上加@Service("别名"),在注入接口上增加@Qualifier("别名") 和@Autowired注解
     */

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        //如果是注解注入的话,就不用setMapper
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

3.2 从数据源配置类

代码如下:

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {

    // 精确到 cluster 目录,以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.cluster";
    static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";

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

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

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

    @Value("${cluster.datasource.driverClassName}")
    private String driverClass;

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

    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

4 domain/entity层

4.1 master包下的UserDao类

代码如下所示:

@Mapper
public interface UserDao {

    /**
     * 根据用户名获取用户信息
     *
     * @param userName
     * @return
     */
    User findByName(@Param("userName") String userName);
}

对应的UserMapper.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="org.spring.springboot.dao.master.UserDao">
	<resultMap id="BaseResultMap" type="org.spring.springboot.domain.User">
		<result column="id" property="id" />
		<result column="user_name" property="userName" />
		<result column="description" property="description" />
		<result column="user_city_id" property="userToCity" />
	</resultMap>

	<sql id="Base_Column_List">
		id, user_name, description,user_city_id
	</sql>

	<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
		select id,
		       user_name,
		       description,
		       user_city_id
-- 		<include refid="Base_Column_List" />
		from user
		where user_name = #{userName}
	</select>

</mapper>

4.2 cluster包下的CityDao类

代码如下:

@Mapper
public interface CityDao {

    /**
     * 根据城市名称,查询城市信息
     *
     * @param id 城市名
     */
    City findById(@Param("cityId") Long id);
}

对应的CityMapper.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="org.spring.springboot.dao.cluster.CityDao">
	<resultMap id="BaseResultMap" type="org.spring.springboot.domain.City">
		<result column="id" property="id" />
		<result column="province_id" property="provinceId" />
		<result column="city_name" property="cityName" />
		<result column="description" property="description" />
	</resultMap>

	<sql id="Base_Column_List">
		id, province_id, city_name, description
	</sql>

	<select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from city
		where id = #{cityId}
	</select>

</mapper>

5 Service层

5.1 UserService接口

public interface UserService {

    /**
     * 根据用户名获取用户信息,包括从库的地址信息
     *
     * @param userName
     * @return
     */
    User findByName(String userName);
}

5.2 UserServiceImpl实现类

首先通过userName获得User信息,通过User的CityId获得City信息,再将City信息赋值给User,从而使得User信息完整。

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao; // 主数据源

    @Autowired
    private CityDao cityDao; // 从数据源

    @Override
    public User findByName(String userName) {
        User user = userDao.findByName(userName);
        City city = cityDao.findById(user.getUserToCity());
        user.setCity(city);
        return user;
    }
}

6 Controller层

主要实现Service实现类

代码如下:

@RestController
public class UserRestController {

    @Autowired
    private UserService userService;

    /**
     * 根据用户名获取用户信息,包括从库的地址信息
     *
     * @param userName
     * @return
     */
    @RequestMapping(value = "/api/user", method = RequestMethod.GET)
    public User findByName(@RequestParam(value = "userName", required = true) String userName) {
        return userService.findByName(userName);
    }

}

7 测试

7.1 cmd运行mysql

7.2 打开Navicat导入sql文件

  • master.sql代码如下:
DROP TABLE IF EXISTS `city`;
CREATE TABLE user
(
id INT(10) unsigned PRIMARY KEY NOT NULL COMMENT '用户编号' AUTO_INCREMENT,
user_name VARCHAR(25) COMMENT '用户名称',
description VARCHAR(25) COMMENT '描述',
user_city_id INT(10) COMMENT '对应城市编号'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT user VALUES (1 ,'四无君','平风造雨四无君','2');
INSERT user VALUES (2 ,'剑君','剑君十二恨','1');
  • cluster.sql代码如下:
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市编号',
`province_id` int(10) unsigned NOT NULL COMMENT '省份编号',
`city_name` varchar(25) DEFAULT NULL COMMENT '城市名称',
`description` varchar(25) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT city VALUES (1 ,1,'不归亭','关外第一家、不归亭、青云榻');
INSERT city VALUES (2 ,1,'冥界天岳','军师');

7.3 运行springboot程序

7.4 打开页面

  • 输入网址:
http://localhost:8080/api/user?userName=四无君

结果为:
springboot-mybatis-multiDatasourcce-xml

  • 输入网址:
http://localhost:8080/api/user?userName=剑君

结果为:
springboot-mybatis-multiDatasourcce-xml


程序员灯塔
转载请注明原文链接:springboot-mybatis-multiDatasourcce-xml
喜欢 (0)