springboot+jdbc+多数据源

最新推荐文章于 2024-05-06 09:45:49 发布

铁憨憨b

最新推荐文章于 2024-05-06 09:45:49 发布

阅读量2.3k

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/qq_38730338/article/details/93193404

版权

java 专栏收录该内容

42 篇文章 2 订阅

订阅专栏

springboot+jdbc+多数据源

碰到的坑

用默认配置配置mysql数据源，数据库连接池空闲连接超过5分钟自动断开。搞了蛮久没搞好，直接换了一个数据源德鲁伊durid，并且配置好检测校验

主数据源：采用默认hikari

第二数据源：采用durid

依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.zywl</groupId>
	<artifactId>ZywlSchedual</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>

	<name>ZywlSchedual</name>
	<description>Zhong Yan Wu Liu</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.4.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
		<tk-mybatis.version>1.1.7</tk-mybatis.version>
		<mybatis-starter.version>1.3.1</mybatis-starter.version>
		<druid.version>1.1.10</druid.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<exclusions>
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-tomcat</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<!-- <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> 
			<version>1.3.2</version> </dependency> -->

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-freemarker</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-logging</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
		</dependency>


		<dependency>
			<groupId>net.sf.json-lib</groupId>
			<artifactId>json-lib</artifactId>
			<version>2.4</version>
			<classifier>jdk15</classifier>
		</dependency>

		<!-- <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> 
			<version>${druid.version}</version> </dependency> -->

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>

		<!-- <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> 
			<version>${mybatis-starter.version}</version> </dependency> <dependency> 
			<groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> 
			<version>${tk-mybatis.version}</version> </dependency> <dependency> <groupId>com.github.pagehelper</groupId> 
			<artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.7</version> 
			</dependency> -->

		<dependency>
			<groupId>dm.jdbc.driver</groupId>
			<artifactId>DmDriver</artifactId>
			<version>17</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.jeecgframework/jeasypoi-base -->
		<dependency>
			<groupId>org.jeecgframework</groupId>
			<artifactId>jeasypoi-base</artifactId>
			<version>2.1.7</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<scope>provided</scope>
		</dependency>

		<!-- https://mvnrepository.com/artifact/commons-net/commons-net -->
		<dependency>
			<groupId>commons-net</groupId>
			<artifactId>commons-net</artifactId>
			<version>2.0</version>
		</dependency>

		<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpclient</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.47</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.9.5</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>2.9.5</version>
		</dependency>
		
		 <dependency>
		     <groupId>com.alibaba</groupId>
		     <artifactId>druid-spring-boot-starter</artifactId>
		     <version>1.1.9</version>
		 </dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<mainClass>com.zywl.ZywlApplication</mainClass>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<compilerArguments>
						<bootclasspath>${JAVA_HOME}/jre/lib/rt.jar</bootclasspath>
					</compilerArguments>
				</configuration>
			</plugin>
		</plugins>
	</build>


</project>

配置文件

server.port=9060
##server.servlet.context-path=/ZywlSchedual
####dev mode or  prod mode
mode=dev
 
##### The default datasource in spring boot 2 is hikari
##JSZY datasource
spring.datasource.primary.jdbc-url=jdbc:dm://10.188.179.51:5236
spring.datasource.primary.username=JSZYLIO
spring.datasource.primary.password=JSZYLIO123456
spring.datasource.primary.driver-class-name=dm.jdbc.driver.DmDriver
spring.datasource.primary.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.primary.hikari.minimum-idle=5
spring.datasource.primary.hikari.maximum-pool-size=15
spring.datasource.primary.hikari.auto-commit=true
spring.datasource.primary.hikari.idle-timeout=30000
spring.datasource.primary.hikari.pool-name=DatebookHikariCP
spring.datasource.primary.hikari.max-lifetime=1800000
spring.datasource.primary.hikari.connection-timeout=30000
spring.datasource.primary.hikari.connection-test-query=SELECT 1

### ETC datasource
spring.datasource.secondary.url= jdbc:mysql://47.107.242.146:3306/jszy?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=123456
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.secondary.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.secondary.druid.db-type=mysql
spring.datasource.secondary.druid.initial-size=10
spring.datasource.secondary.druid.async-init=true
spring.datasource.secondary.druid.max-active=20
spring.datasource.secondary.druid.min-idle=5
spring.datasource.secondary.druid.max-wait=60000
spring.datasource.secondary.druid.pool-prepared-statements:true
spring.datasource.secondary.druid.max-pool-prepared-statement-per-connection-size=100
spring.datasource.secondary.druid.time-between-eviction-runs-millis=30000
spring.datasource.secondary.druid.min-evictable-idle-time-millis=60000
spring.datasource.secondary.druid.validation-query=SELECT 1
spring.datasource.secondary.druid.test-while-idle=true
spring.datasource.secondary.druid.test-on-borrow=true
spring.datasource.secondary.druid.test-on-return=false
spring.datasource.secondary.druid.filters=stat

#########freemarker#######################################3
spring.freemarker.template-loader-path=classpath:/templates/
spring.freemarker.suffix=.html
spring.freemarker.cache=true
spring.freemarker.enabled=true
spring.freemarker.content-type=text/html
spring.freemarker.charset=UTF-8
spring.freemarker.allow-request-override=false
spring.freemarker.allow-session-override=false
spring.freemarker.expose-request-attributes=false
spring.freemarker.expose-session-attributes=false
spring.freemarker.prefer-file-system-access=true
spring.freemarker.request-context-attribute=request
spring.freemarker.check-template-location=true
spring.freemarker.settings.classic_compatible=true
spring.freemarker.settings.default_encoding=UTF-8

###########################  resource directory
spring.resources.static-locations=classpath:/static/
spring.resources.chain.cache=true
spring.resources.chain.strategy.content.enabled=true
spring.resources.chain.strategy.content.paths=/**

################### dr port ip 
dr.ip=127.0.0.1
dr.port=9080
dr.downloadPath=D:\\log

配置数据源java代码

package com.zywl.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
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.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
@Configuration
public class DataSourceConfig {
	@Bean(name = "dataSource")
    @Qualifier("dataSource")
    @ConfigurationProperties(prefix="spring.datasource.primary")
	@Primary
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix="spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

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

    @Bean(name = "primaryNamedParameterJdbcTemplate")
    @Primary
    public NamedParameterJdbcTemplate primaryNamedParameterJdbcTemplate(
            @Qualifier("dataSource") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
    
    @Bean(name = "secondaryNamedParameterJdbcTemplate")
    public NamedParameterJdbcTemplate secondaryNamedParameterJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
    
    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
    
    
}

具体用法：

铁憨憨b

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
springboot+jdbc+多数据源

springboot+jdbc+多数据源碰到的坑用默认配置配置mysql数据源，数据库连接池空闲连接超过5分钟自动断开。搞了蛮久没搞好，直接换了一个数据源德鲁伊durid，并且配置好检测校验主数据源：采用默认hikari第二数据源：采用durid依赖<?xml version="1.0" encoding="UTF-8"?><projec...
复制链接

扫一扫