学习Spring中遇到的坑——配置数据源 引用外部属性文件

博主在学习Spring用c3p0连接数据库配置数据源时遇到报错。介绍了db.properties、beans配置文件等情况,参考相关博客后,详细说明了<context:property-placeholder/>标签各属性含义,最终得出要加上system-properties-mode=\NEVER\来解决问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在学习Spring中用c3p0连接数据库,配置数据源时
遇到一些报错;
下面是原本的db.properties

user=root
password=???
driverclass=com.mysql.cj.jdbc.Driver
jdbcurl=jdbc:mysql:///test?useSSL=false&useUnicode=true&characterEncoding=utf-8

下面是beans的配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

<context:property-placeholder location="classpath:db.properties" />

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	
	<property name="user" value="${user}"></property>
	<property name="password" value="${password}"></property>
	<property name="driverClass" value="${driverclass}"></property>
	<property name="jdbcUrl" value="${jdbcurl}"></property>
</bean>
</beans>

下面是Main.java

package com.cefer.test;

import java.sql.SQLException;

import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {

	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		ApplicationContext ctx = new ClassPathXmlApplicationContext("beans_properties.xml");
		
		DataSource dataSource = (DataSource)ctx.getBean("dataSource");
		System.out.println("ok"+dataSource.getConnection());
	}

}

下面是错误

3月 20, 2019 1:37:28 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
3月 20, 2019 1:37:29 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
3月 20, 2019 1:37:30 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1bqub5va116mb91z1y5y790|58e1d9d, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1bqub5va116mb91z1y5y790|58e1d9d, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql:///test?useSSL=false&useUnicode=true&characterEncoding=utf-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {password=******, user=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
3月 20, 2019 1:37:59 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@6318d479 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Access denied for user 'S'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
	at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
	at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
	at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
	at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

3月 20, 2019 1:37:59 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@2ff47c50 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Access denied for user 'S'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
	at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
	at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
	at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
	at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

3月 20, 2019 1:37:59 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1e23ed65 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Access denied for user 'S'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
	at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
	at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
	at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
	at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
	at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

Exception in thread "main" java.sql.SQLException: Connections could not be acquired from the underlying database!
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
	at com.cefer.test.Main.main(Main.java:16)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
	... 2 more

参考(https://blog.youkuaiyun.com/Rickesy/article/details/50791534)
发现<context:property-placeholder />这个标签的所有属性:

location:表示属性文件位置,多个之间通过如逗号/分号等分隔;
file-encoding:文件编码;
ignore-resource-not-found:如果属性文件找不到,是否忽略,默认false,即不忽略,找不到将抛出异常
ignore-unresolvable:是否忽略解析不到的属性,如果不忽略,找不到将抛出异常
properties-ref:本地java.util.Properties配置
local-override:是否本地覆盖模式,即如果true,那么properties-ref的属性将覆盖location加载的属性
system-properties-mode:系统属性模式,ENVIRONMENT(默认),NEVER,OVERRIDE
ENVIRONMENT:将使用Spring 3.1提供的PropertySourcesPlaceholderConfigurer,其他情况使用Spring 3.1之前的PropertyPlaceholderConfigurer
OVERRIDE: PropertyPlaceholderConfigurer使用,因为在spring 3.1之前版本是没有Enviroment的,所以OVERRIDE是spring 3.1之前版本的Environment
NEVER:只查找properties-ref、location;
order:当配置多个context:property-placeholder/时的查找顺序

所以要加上system-properties-mode=“NEVER”

变成

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

<context:property-placeholder location="classpath:db.properties" system-properties-mode="NEVER"/>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	
	<property name="user" value="${user}"></property>
	<property name="password" value="${password}"></property>
	<property name="driverClass" value="${driverclass}"></property>
	<property name="jdbcUrl" value="${jdbcurl}"></property>
</bean>
</beans>

### 配置Spring Cloud项目中的MySQL和ClickHouse多数据源Spring Cloud项目中配置MySQL和ClickHouse作为多数据源涉及几个关键步骤。以下是详细的说明: #### 1. 修改`application.yml` 为了支持两种不同的数据库,需在`application.yml`文件中定义各自的连接属性。 ```yaml spring: datasource: mysql: url: jdbc:mysql://localhost:3306/mysql_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver clickhouse: url: jdbc:clickhouse://localhost:8123/clickhouse_db username: default password: driver-class-name: ru.yandex.clickhouse.ClickHouseDriver ``` 上述设置指定了两个不同类型的数据库及其访问参数[^1]。 #### 2. 实现动态数据源路由逻辑 通过继承`AbstractRoutingDataSource`来创建自定义的数据源切换机制——即当应用程序运行期间能够根据业务需求灵活选择要使用的具体数据源实例。 ```java package com.example.config; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; // ... other imports... public class DynamicDataSource extends AbstractRoutingDataSource { private static final ThreadLocal<String> contextHolder = new ThreadLocal<>(); public void setDbType(String dbType){ contextHolder.set(dbType); } @Override protected Object determineCurrentLookupKey(){ return contextHolder.get(); } } ``` 此部分代码实现了基于线程局部变量(`ThreadLocal`)保存当前请求所对应的数据库名称的功能[^3]。 #### 3. 注册并初始化多个数据源对象 接下来,在Spring Boot应用启动时加载这些配置好的数据源,并将其注册到容器内供后续操作调用。 ```java @Configuration @MapperScan(basePackages={"com.example.mapper"}, sqlSessionFactoryRef="mysqlSqlSessionFactory") public class DataSourceConfig { // MySQL Data Source Bean Definition... @Bean(name = "mysqlDataSource") @ConfigurationProperties(prefix = "spring.datasource.mysql") public DataSource mysqlDataSource() throws SQLException{ return DataSourceBuilder.create().build(); } // ClickHouse Data Source Bean Definition... @Bean(name = "clickhouseDataSource") @ConfigurationProperties(prefix = "spring.datasource.clickhouse") public DataSource clickhouseDataSource() throws SQLException{ return DataSourceBuilder.create().build(); } // Define target data sources map and dynamic data source bean here. ... } ``` 这里展示了如何利用`@ConfigurationProperties`简化外部化配置绑定过程的同时也完成了针对每种类型数据源的具体bean声明工作[^4]。 #### 4. 定义SQL会话工厂和服务层组件 最后一步是要为每个持久化单元分别建立相应的MyBatis Plus SQL Session Factory以及Service Layer Component用于执行CRUD操作。 对于MySQL而言可以这样写: ```java @Bean(name = "mysqlSqlSessionFactory") public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception { MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean(); factory.setDataSource(dataSource); ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); factory.setMapperLocations(resolver.getResources("classpath*:mapper/mysql/*.xml")); return factory.getObject(); } @Service @Transactional(transactionManager = "mysqlTransactionManager") public class UserServiceImpl implements IUserService { // Implement service methods using the above defined session factory... } ``` 而对于ClickHouse,则应调整路径指向特定于该DBMS的映射器XML文件位置以及其他必要的适配措施。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值