参考文章:https://www.liangzl.com/get-article-detail-29242.html

pom文件配置

<!--阿里druid-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.9</version>
</dependency>
<!--事务管理：原子性，一致性，隔离性，持久性-->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!--引入postgresql依赖-->
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
</dependency>

application.yml配置

# postgresql 环境配置
server:
  port: 18005
  max-http-header-size: 8192
logging:
  level:
    root: info
spring:
  datasource:
    url: jdbc:postgresql://10.101.15.59:5432/postgres
    username: root
    password: 123abc
    driverClassName: org.postgresql.Driver
    platform: postgres
    type: com.alibaba.druid.pool.DruidDataSource
    # type: org.apache.commons.dbcp2.BasicDataSource
    initialization-mode: always
    # ============================== druid ============================== #
    druid:
      #最大活跃数
      maxActive: 20
      #初始化数量
      initialSize: 1
      #最大连接等待超时时间
      maxWait: 60000
      #打开PSCache，并且指定每个连接PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      #通过connectionProperties属性来打开mergeSql功能；慢SQL记录
      #connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1 from dual
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #配置监控统计拦截的filters，去掉后监控界面sql将无法统计,'wall'用于防火墙
      filters: stat, wall, log4j
  # ============================= spring jpa 配置 ============================= #
  jpa:
    show_sql: true
    database-platform: org.hibernate.dialect.PostgreSQL9Dialect
    # database-platform: cn.luutqf.springboot.dialect.JsonbPostgresDialect
    hibernate:
      ddl-auto: update  # none: 关闭hibernate的自动创建表结构的机制
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        hbm2ddl.auto: update
        jdbc.lob.non_contextual_creation: true
        format_sql: true
        temp:
          # 兼容SpringBoot2.X, 关闭 Hibernate尝试验证PostgreSQL的CLOB特性
          use_jdbc_metadata_defaults: false

工程目录结构

DataSourceConfig配置

package org.fiend.config;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
/**
 * postgresql data source config
 * @author fiend 2018/12/5
 */
@Configuration
public class DataSourceConfig {
    @Primary
    @Bean(name="dataSource1")
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
    /**
     * 多数据源配置, 数据源二
     */
    @Bean(name="dataSource2")
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
    @Value("${spring.datasource.type}")
    private Class<? extends DataSource> dataSourceType;
    /**
     * 配置监控服务器
     * @return 返回监控注册的servlet对象
     */
    @Bean
    @SuppressWarnings("unchecked")
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean servletRegistrationBean =
                new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // 添加IP白名单
        servletRegistrationBean.addInitParameter("allow", "192.168.25.125,127.0.0.1");
        // 添加IP黑名单，当白名单和黑名单重复时，黑名单优先级更高
        servletRegistrationBean.addInitParameter("deny", "192.168.25.123");
        // 添加控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "druid");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        // 是否能够重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }
    /**
     * 配置服务过滤器
     * @return 返回过滤器配置对象
     */
    @Bean
    @SuppressWarnings("unchecked")
    public FilterRegistrationBean statFilter() {
        FilterRegistrationBean filterRegistrationBean=new FilterRegistrationBean(new WebStatFilter());
        // 添加过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        // 忽略过滤格式
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*,");
        return filterRegistrationBean;
    }
}

PrimaryConfig配置

PrimaryConfig使用dataSource1数据源

package org.fiend.config;
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;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
import java.util.Objects;
/**
 * @author fiend 2018/12/12
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"org.fiend.repository.primary"} //设置Repository所在位置
)
public class PrimaryConfig {
    @Resource
    @Qualifier("dataSource1")
    private DataSource dataSource1;
    @Resource
    private JpaProperties jpaProperties;
    /**
     * 配置EntityManager实体
     * @return 实体管理器
     */
    @Primary
    @Bean(name="entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()).createEntityManager();
    }
    @Primary
    @Bean(name="transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
    /**
     * 设置实体类所在位置
     */
    @Primary
    @Bean(name="entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(
               EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(dataSource1)
                .packages("org.fiend.entity.primary")   // 实体类所在位置
                .persistenceUnit("ds1PersistenceUnit")  // datasource1 persistenceUnit
                .properties(getVendorProperties())
                .build();
    }
    /**
     *  这里其实不需要配置数据库的方言.
     *  像hibernate.hbm2ddl.auto 可以在这里配置. 我们已在application.yml中配置.
     */
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
}

SecondaryConfig配置

SecondaryConfig使用dataSource2数据源

package org.fiend.config;
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;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;
import java.util.Objects;
/**
 * @author fiend 2018/12/12
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"org.fiend.repository.secondary"}  //设置Repository所在位置
)
public class SecondaryConfig {
    @Resource
    @Qualifier("dataSource2")
    private DataSource dataSource2;
    @Resource
    private JpaProperties jpaProperties;
    /**
     * 配置EntityManager实体
     * @return 实体管理器
     */
    @Bean(name="entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject()).createEntityManager();
    }
    @Bean(name="transactionManagerSecondary")
    public PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
    /**
     * 设置实体类所在位置
     */
    @Bean(name="entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(
               EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(dataSource2)
                .packages("org.fiend.entity.secondary")   // 实体类所在位置
                .persistenceUnit("ds2PersistenceUnit")  // datasource2 persistenceUnit
                .properties(getVendorProperties())
                .build();
    }
    /**
     *  这里其实不需要配置数据库的方言.
     *  像hibernate.hbm2ddl.auto 可以在这里配置. 我们已在application.yml中配置.
     */
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
}

Entity文件

这里只展示primary的entity文件

package org.fiend.entity.primary;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
/**
 * @author fiend 2018/12/5
 */
@Entity
// catalog: 数据库名称, name: 表名
@Table(catalog="primary1", name="user")
public class UserPri implements Serializable {  //序列化在Jpa需要
    // @Id是用来标识主键的，而@GeneratedValue则是用来指定主键策略的
    @Id
    @GeneratedValue
    private String name;
    // @Column(name = "count", nullable = false)
    private Integer count;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getCount() {
        return count;
    }
    public void setCount(Integer count) {
        this.count = count;
    }
}

Repository文件

这里只展示primary的repository文件

package org.fiend.repository.primary;
import org.fiend.entity.primary.UserPri;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import javax.transaction.Transactional;
import java.util.List;
/**
 * @author fiend 2018/12/10
 */
@Repository
public interface UserRepositoryPri extends JpaRepository<UserPri, String> {
    /**
     * 进行数据库操作, 可以通过自定义HQL来完成符合个性的其他操作
     * 通过@Query注解, 完成了HQL的书写, 其中“:name”与下方的@Param注解中参数保持一致。
     * HQL可使用Hibernate的SQL生成和缓存策略
     * HQL中一些关键字如 SELECT, FROM 和 WHERE等, 是不区分大小写的, 但是一些属性如表名和列名区分大小写
     */
    @Query(value = "SELECT u FROM UserPri u WHERE name=:name")
    UserPri findName(@Param("name") String name);
    /**
     * 使用SQL语句, nativeQuery为true代表使用SQL语言
     * 建议尽可能的使用HQL语句, 以避免数据库关于可移植性的麻烦,  且HQL可使用Hibernate的SQL生成和缓存策略。
     */
    @Query(value = "insert into User value(?,?,?)", nativeQuery = true)
    @Transactional
    @Modifying
    int countUserBy(@Param("id") Integer id, @Param("name") String name, 
           @Param("password") String password);
    /**
     * 根据用户名查询
     */
    @Query("select t from UserPri t where t.name = :name")
    UserPri findByUserName(@Param("name") String name);
    /**
     * 查询全部
     */
    @Query("select t from UserPri t")
    List<UserPri> find();
    /**
     * 删除, 必须加入@Modifying和@Transactional
     */
    @Modifying
    @Transactional
    @Query("delete from UserPri u where u.id=:id")
    int deleteUserById(@Param("id") Integer id);
    @Modifying
    @Transactional
    @Query("update UserPri u set u.name = :name where u.id=:id")
    int queryUserById(@Param("id") Integer id, @Param("name") String name);
}

Service文件

package org.fiend.service;
import com.alibaba.fastjson.JSONObject;
import org.fiend.base.AbstractService;
import org.fiend.entity.primary.UserPri;
import org.fiend.entity.secondary.UserSec;
import org.fiend.repository.primary.UserRepositoryPri;
import org.fiend.repository.secondary.UserRepositorySec;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
 * @author Administrator
 */
@Service
public class HomeService extends AbstractService {
    Logger log = LoggerFactory.getLogger(getClass());
    @Autowired
    private UserRepositoryPri userRepositoryPri;
    @Autowired
    private UserRepositorySec userRepositorySec;
    public JSONObject getUserListPri() {
        JSONObject json = new JSONObject();
        List<UserPri> userList = userRepositoryPri.find();
        json.put("userList", userList);
        return json;
    }
    public JSONObject getUserListSec() {
        JSONObject json = new JSONObject();
        List<UserSec> userList = userRepositorySec.find();
        json.put("userList", userList);
        return json;
    }
}

Controller文件

package org.fiend.controller;
import com.alibaba.fastjson.JSONObject;
import org.fiend.base.AbstractController;
import org.fiend.service.HomeService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.inject.Inject;
import javax.inject.Named;
/**
 * @author Administrator
 */
@Controller
@RequestMapping(value="home")
public class HomeController extends AbstractController<HomeService> {
	Logger log=LoggerFactory.getLogger(this.getClass());
	@Override
	@Inject
	@Named("homeService")
	protected void setService(HomeService service) {
		this.service=service;
	}
	@RequestMapping(value="getUserListPri")
	@ResponseBody
	public JSONObject getUserListPri() {
		return service.getUserListPri();
	}
	@RequestMapping(value="getUserListSec")
	@ResponseBody
	public JSONObject getUserListSec() {
		return service.getUserListSec();
	}
}

springboot -- 使用jpa和hibernate的方式配置多数据源 ( postgresql -- druid连接池)（二） --- 实践版本

pom文件配置

application.yml配置

工程目录结构

DataSourceConfig配置

PrimaryConfig配置

SecondaryConfig配置

Entity文件

Repository文件

Service文件

Controller文件

输出结果:

相关文章