(四)SpringBoot2.0基础篇- 多数据源,JdbcTemplate和JpaRepository

在日常开发中,经常会遇到多个数据源的问题,而SpringBoot也有相关API:Configure Two DataSources:https://docs.spring.io/spring-boot/docs/1.5.4.RELEASE/reference/htmlsingle/#howto-two-datasources

本文SpringBoot版本为2.0(由于2.0之前的版本和之后的版本配置会有些许不同,2.0之前的版本推荐一位大牛的博文:http://blog.didispace.com/springbootmultidatasource/)下面会介绍这两种多数据源的配置方法,希望大家多多指教!

一、JdbcTemplate多数据源配置

  1、添加applicaton.properties数据库连接信息,有两个数据源,一个为主,一个为从:

app.datasource.foo.url=jdbc:mysql://192.168.1.121:3306/test
app.datasource.foo.username=root
app.datasource.foo.password=admincss
app.datasource.foo.driver-class-name=com.mysql.jdbc.Driver

app.datasource.bar.url=jdbc:mysql://192.168.1.121:3306/test2
app.datasource.bar.username=root
app.datasource.bar.password=admincss
app.datasource.bar.driver-class-name=com.mysql.jdbc.Driver

  2、创建数据源类:

package com.cn.datasource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @program: spring-boot-example
 * @description: 数据源配置类
 * @author:
 * @create: 2018-05-03 14:35
 **/

@Configuration
public class JdbcDataSourceConfig {

    @Primary
    @Bean(name = "dataSourcePropertiesFoo")
    @Qualifier("dataSourcePropertiesFoo")
    @ConfigurationProperties(prefix="app.datasource.foo")
    public DataSourceProperties dataSourcePropertiesFoo() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "fooDataSource")
    @Qualifier("fooDataSource")
    @ConfigurationProperties(prefix="app.datasource.foo")
    public DataSource fooDataSource(@Qualifier("dataSourcePropertiesFoo") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "dataSourcePropertiesBar")
    @Qualifier("dataSourcePropertiesBar")
    @ConfigurationProperties(prefix="app.datasource.bar")
    public DataSourceProperties dataSourcePropertiesBar() {
        return new DataSourceProperties();
    }

    @Bean(name = "barDataSource")
    @Qualifier("barDataSource")
    @ConfigurationProperties(prefix="app.datasource.bar")
    public DataSource barDataSource(@Qualifier("dataSourcePropertiesBar") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "fooJdbcTemplate")
    @Qualifier("fooJdbcTemplate")
    public JdbcTemplate fooJdbcTemplate(@Qualifier("fooDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "barJdbcTemplate")
    @Qualifier("barJdbcTemplate")
    public JdbcTemplate barJdbcTemplate(@Qualifier("barDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

  3、创建简单的测试bean、controller、service、entityRowMapper:

package com.cn.entity.u;

import java.io.Serializable;

/**
 * @program: spring-boot-example
 * @description: 用户类
 * @author:
 * @create: 2018-05-02 09:59
 **/
public class User implements Serializable{

    private int id;
    private String name;
    private int age;
    private String address;

    @Override
    public String toString() {
        return "User{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age=" + age +
            ", address='" + address + '\'' +
            '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}
package com.cn.controller;

import com.cn.entity.u.User;
import com.cn.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 09:58
 **/

@RestController
public class JdbcTestController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "getUserById/{id}",method = RequestMethod.GET)
    public User getUserById(@PathVariable int id) {
        return userService.getUserById(id);
    }

}
package com.cn.service;


import com.cn.entity.u.User;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 10:02
 **/

public interface UserService {

    User getUserById(int id);

}
package com.cn.service;

import com.cn.entity.u.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 10:07
 **/

@Service
public class UserServiceImpl implements UserService{

    @Autowired
    @Qualifier("fooJdbcTemplate")
    protected JdbcTemplate fooJdbcTemplate;

    @Autowired
    @Qualifier("barJdbcTemplate")
    protected JdbcTemplate barJdbcTemplate;

    @Override
    public User getUserById(int id) {
        User user = fooJdbcTemplate.queryForObject("select * from user where id=?", new Object[]{id},new UserRowMapper());
        User user2 = barJdbcTemplate.queryForObject("select * from user where id=?", new Object[]{id},new UserRowMapper());
        System.out.println(user);
        System.out.println(user2);
        return user;
    }

}
package com.cn.service;

import com.cn.entity.u.User;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

class UserRowMapper implements RowMapper<User> {

    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setName(resultSet.getString("name"));
        user.setId(resultSet.getInt("id"));
        user.setAge(resultSet.getInt("age"));
        user.setAddress(resultSet.getString("address"));
        return user;
    }

}

  4、测试;

二、JpaRepository多数据源

  1、添加数据源信息如上;

  2、使用上一个项目的数据源DataSource进行进一步的配置JpaFooConfig、JpaBarConfig:

package com.cn.datasource;

import java.util.Map;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-04 10:54
 **/

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "entityManagerFactoryFoo",
    transactionManagerRef = "transactionManagerFoo",
    basePackages = {"com.cn.entity.s"})
public class JpaFooConfig {

    @Resource
    @Qualifier("fooDataSource")
    private DataSource fooDataSource;

    @Primary
    @Bean(name = "entityManagerFoo")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryFoo(builder).getObject().createEntityManager();
    }

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    /**
     * 设置实体类所在位置
     */
    @Primary
    @Bean(name = "entityManagerFactoryFoo")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryFoo(EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(fooDataSource)
            .packages("com.cn.entity.s")
            .persistenceUnit("fooPersistenceUnit")
            .properties(getVendorProperties())
            .build();
    }

    @Primary
    @Bean(name = "transactionManagerFoo")
    public PlatformTransactionManager transactionManagerFoo(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryFoo(builder).getObject());
    }

}
package com.cn.datasource;

import java.util.Map;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-04 10:54
 **/

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "entityManagerFactoryBar",
    transactionManagerRef = "transactionManagerBar",
    basePackages = {"com.cn.entity.t"})//repository的目录
public class JpaBarConfig {

    @Autowired
    @Qualifier("barDataSource")
    private DataSource barDataSource;

    @Bean(name = "entityManagerBar")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryBar(builder).getObject().createEntityManager();
    }

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    @Bean(name = "entityManagerFactoryBar")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBar(EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(barDataSource)
            .packages("com.cn.entity.t")//实体类的目录
            .persistenceUnit("barPersistenceUnit")
            .properties(getVendorProperties())
            .build();
    }

    @Bean(name = "transactionManagerBar")
    PlatformTransactionManager transactionManagerBar(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryBar(builder).getObject());
    }

}

  3、同上创建相关的测试类进行测试(bean、repository、controller、service   注意bean、repository的目录要放在2步骤中配置的位置):

package com.cn.entity.s;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @program: spring-boot-example
 * @description: 学生实体类
 * @author:
 * @create: 2018-05-02 10:47
 **/
@Entity
public class Student {

    @Id
    @GeneratedValue
    private int id;

    private String name;

    private int age;

    private int grade;

    public Student() {
    }

    public Student(String name, int age, int grade) {
        this.name = name;
        this.age = age;
        this.grade = grade;
    }

    @Override
    public String toString() {
        return "Student{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age=" + age +
            ", grade=" + grade +
            '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }
}
package com.cn.entity.s;

import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 11:02
 **/
public interface StudentDao extends JpaRepository<Student,Integer> {

    Student findByName(String name);

}
package com.cn.entity.t;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-04 10:38
 **/

@Entity
public class Teacher {

    @Id
    @GeneratedValue
    private int id;
    private String name;
    private String age;
    private String course;

    public Teacher() {
    }

    public Teacher(String name, String age, String course) {
        this.name = name;
        this.age = age;
        this.course = course;
    }

    @Override
    public String toString() {
        return "Teacher{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age='" + age + '\'' +
            ", course='" + course + '\'' +
            '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}
package com.cn.entity.t;

import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 11:02
 **/
public interface TeacherDao extends JpaRepository<Teacher,Integer> {

    Teacher findByName(String name);

}
package com.cn.controller;

import com.cn.entity.s.Student;
import com.cn.service.JpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/*
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 11:15
 */

@RestController
public class JpaTestController {

    @Autowired
    private JpaService jpaService;

    @RequestMapping("findByName/{name}")
    public Student findByName(@PathVariable String name) {
        return jpaService.findByName(name);
    }

}
package com.cn.service;


import com.cn.entity.s.Student;

/*
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 11:12
 */

public interface JpaService {

    Student findByName(String name);

}
package com.cn.service;

import com.cn.entity.s.StudentDao;
import com.cn.entity.s.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/*
 * @program: spring-boot-example
 * @description:
 * @author:
 * @create: 2018-05-02 11:13
 */

@Service
public class JpaServiceImpl implements JpaService {

    @Autowired
    private StudentDao studentDao;

    @Override
    public Student findByName(String name) {
        return studentDao.findByName(name);
    }

}

   4、测试;

示例代码:https://gitee.com/lfalex/spring-boot-example/tree/dev/spring-boot-datasource

参考官方文档:https://docs.spring.io/spring-boot/docs/1.5.4.RELEASE/reference/htmlsingle/#howto-data-access

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT 指南者专栏

Spring 框架系列之 JDBC 整合

微信公众号:compassblog 欢迎关注、转发,互相学习,共同进步! 有任何问题,请后台留言联系! 1、Spring框架整合 DAO 模板 JDBC:org...

344110
来自专栏扎心了老铁

springboot scheduled并发配置

本文介绍如何使用springboot的sheduled实现任务的定时调度,并将调度的任务实现为并发的方式。 1、定时调度配置scheduled 1)注册定时任务...

1.9K70
来自专栏公众号_薛勤的博客

2小时学会Spring Boot(IDE:eclipse)

1.)使启动类继承SpringBootServletInitializer 覆写configure()方法。

47940
来自专栏向治洪

史上最强Spring mvc入门

一、SpringMVC基础入门,创建一个HelloWorld程序   1.首先,导入SpringMVC需要的jar包。 ?   2.添加Web.xml配置...

364100
来自专栏Hongten

Hibernate 过滤器

通过调用Session对象的setFilter()和enableFilter()方法使用过滤器。

14720
来自专栏闻道于事

Spring boot之SpringApplicationBuilder,@@Configuration注解,@Component注解

48200
来自专栏杨建荣的学习笔记

ORA-17500 ODM err的问题排查(r2笔记78天)

今天在一套环境中做系统检查的时候,发现alert日志中有一段ODM的错误。 日志内容大体如下,可以看到是在半夜4点多报的错误。 Clearing Resourc...

31530
来自专栏一个会写诗的程序员的博客

Springboot使用JPA操作数据库第七章 使用JPA操作数据库

本章主要介绍如何在Spring Boot的Web应用中使用Mysq数据库,也充分展示Spring Boot的优势(尽可能少的代码和配置).

18530
来自专栏技术专栏

logback日志写入kafka遇到的那些坑

这两天在学习storm实时流的时候需要将logback日志写入kafka,这期间遇到了很多坑,这里把遇到的坑和解决的问题记录一下,和大家共勉

2K30
来自专栏Java技术栈

Spring Boot 集成 Mybatis 实现双数据源

这里用到了Spring Boot + Mybatis + DynamicDataSource配置动态双数据源,可以动态切换数据源实现数据库的读写分离。

22920

扫码关注云+社区

领取腾讯云代金券