（四）SpringBoot2.0基础篇- 多数据源，JdbcTemplate和JpaRepository

锋

发布于 2018-06-20 14:22:13

2.1K0

发布于 2018-06-20 14:22:13

文章被收录于专栏：F_Alex F_AlexF_Alex

在日常开发中，经常会遇到多个数据源的问题，而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

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018-05-08 ，如有侵权请联系 cloudcommunity@tencent.com 删除

spring

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

spring

登录后参与评论

0 条评论

热度