英文:
org.springframework.jdbc.CannotGetJdbcConnectionException, nested exception is java.sql.SQLException
问题
我正在尝试使用Spring框架和Spring Security构建一个简单的CRUD应用程序。应用程序有一个“登录”要求，其中应用程序对用户进行授权和身份验证。我能够使用硬编码的值进行登录，但是当我尝试使用JDBC和MySQLWorkbench获取用户和密码时，出现以下错误：
`org.springframework.jdbc.CannotGetJdbcConnectionException: 无法获取JDBC连接；嵌套异常是java.sql.SQLException：无法从底层数据库获取连接！`
我尝试在测试类中进行调试，它显示“连接成功”。
```java
package com.paras.springsecurity.demo.config;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestApp {
public static void main(String[] args) {
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/spring_security_demo_plaintext?useSSL=false", "springstudent", "springstudent");
System.out.println("连接成功！！！！");
} catch(Exception e) {
e.printStackTrace();
}
}
}
现在我无法理解当应用程序和JDBC连接、密码和用户正常工作时出现了什么问题。
持久性MySQL属性 - persistence-mysql.properties
#
# JDBC连接属性
#
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_security_demo_plaintext?useSSL=false
jdbc.user=springstudent
jdbc.password=springstudent
#
# 连接池属性
#
connection.pool.initialPoolSize=5
connection.pool.minPoolSize=5
connection.pool.maxPoolSize=20
connection.pool.maxIdleTime=3000
pom.xml
<!-- 在这里是pom.xml的内容，已省略 -->
JDBC配置文件 - DemoAppConfig.java
// 在这里是DemoAppConfig.java的内容，已省略
在类中调用Spring Security数据源 - DemoSecurityConfig.java
// 在这里是DemoSecurityConfig.java的内容，已省略
<details>
<summary>英文:</summary>
I am trying to build simple CRUD app with Spring framework and Spring security. There is a 'sign in' requirement where app authorise and authenticates user. I was able to sign in with hard coded values but when I tried getting users and passwords with JDBC and MySQLWorkbench, its giving this error:
`org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!`
I tried debugging in a test Class and it gave "connection successful"
package com.paras.springsecurity.demo.config;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestApp {
public static void main(String[]args){
Connection con = null;
try {
con = DriverManager.
getConnection("jdbc:mysql://localhost:3306/spring_security_demo_plaintext?useSSL=false", "springstudent", "springstudent");
System.out.println("Connection is successful !!!!!");
} catch(Exception e) {
e.printStackTrace();
}
}
}
Now I am not able to understand what is the issue here when app and jdbc connections, password, and user is working
persistence-mysql.properties
#
# JDBC connection properties
#
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_security_demo_plaintext?useSSL=false
jdbc.user=springstudent
jdbc.password=springstudent
#
# Connection pool properties
#
connection.pool.initialPoolSize=5
connection.pool.minPoolSize=5
connection.pool.maxPoolSize=20
connection.pool.maxIdleTime=3000
pom.xml
<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.paras</groupId>
<artifactId>spring-security-demo</artifactId>
<version>1.0</version>
<packaging>war</packaging>
<name>spring-security-demo</name>
<properties>
<springframework.version>5.2.8.RELEASE</springframework.version>
<springsecurity.version>5.3.4.RELEASE</springsecurity.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<!-- Spring MVC support -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${springframework.version}</version>
</dependency>
<!-- Servlet, JSP and JSTL support -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp-api</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- to compensate for java 9+ not including jaxb -->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- Spring security dependency -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-web</artifactId>
<version>5.3.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-config</artifactId>
<version>5.3.4.RELEASE</version>
</dependency>
<!-- Add support for spring security Taglib support -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-taglibs</artifactId>
<version>5.3.4.RELEASE</version>
</dependency>
<!-- Add mysql and c3p0 support -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
</dependencies>
<!-- TO DO: Add support for Maven WAR Plugin -->
<build>
<finalName>spring-security-demo</finalName>
<pluginManagement>
<plugins>
<plugin>
<!-- Add maven coordinates(GAV) for: maven-war-plugin -->
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>3.3.1</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
Jdbc configuration file - DemoAppConfig.java
package com.paras.springsecurity.demo.config;
import java.beans.PropertyVetoException;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
import com.mchange.v2.c3p0.ComboPooledDataSource;
@Configuration
@EnableWebMvc
@ComponentScan(basePackages="com.paras.springsecurity.demo")
@PropertySource("classpath:persistence-mysql.properties")
public class DemoAppConfig {
//set up a var to hold the projects
@Autowired
private Environment env;
//env will hold data read from proprties file
//set up a logger
private Logger logger = Logger.getLogger(getClass().getName());
//define a bean for viewResolver
@Bean
public ViewResolver viewResolver() {
InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
viewResolver.setPrefix("/WEB-INF/view/");
viewResolver.setSuffix(".jsp");
return viewResolver;
}
//define a bean for our security datasource
@Bean
public DataSource securityDataSource() {
//create a connection pool
ComboPooledDataSource securityDataSource = new ComboPooledDataSource();
//set the jdbc driver class
try {
securityDataSource.setDriverClass(env.getProperty("jdbc.driver"));
} catch (PropertyVetoException exc) {
throw new RuntimeException(exc);
}
//log the connection props
logger.info(">>>>> jdbc.url " + env.getProperty("jdbc.url"));
logger.info(">>>>> jdbc.user " + env.getProperty("jdbc.user"));
//set database connection props
securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.user"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.password"));
//set connection pool props
securityDataSource.setInitialPoolSize(getIntProperty("connection.pool.initialPoolSize"));
securityDataSource.setMinPoolSize(getIntProperty("connection.pool.minPoolSize"));
securityDataSource.setMaxPoolSize(getIntProperty("connection.pool.maxPoolSize"));
securityDataSource.setMaxIdleTime(getIntProperty("connection.pool.maxIdleTime"));
return securityDataSource;
}
//helper method to read environment property and convert to int
private int getIntProperty(String propName) {
String propVal = env.getProperty(propName);
int intPropVal = Integer.parseInt(propVal);
return intPropVal;
}
}
Calling Spring Security Data Source in the class - DemoSecurityConfig.java
package com.paras.springsecurity.demo.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
@Configuration
@EnableWebSecurity
public class DemoSecurityConfig extends WebSecurityConfigurerAdapter {
//add a reference to our security source
@Autowired
private DataSource securityDataSource;
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.jdbcAuthentication().dataSource(securityDataSource);
}
@Override
protected void configure(HttpSecurity http) throws Exception {
http.authorizeRequests().antMatchers("/").hasRole("EMPLOYEE")
.antMatchers("/leaders/**").hasRole("MANAGER")
.antMatchers("/systems/**").hasRole("ADMIN")
.and().formLogin().loginPage("/showMyLoginPage")
.loginProcessingUrl("/authenticateTheUser").permitAll().and().logout().permitAll().and().exceptionHandling().
accessDeniedPage("/access-denied");
}
}
</details>
# 答案1
**得分**: 1
这是问题所在：
//设置数据库连接属性
securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.user"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.password"));
你在所有地方都使用了 setJdbcUrl。
应该像这样：
//设置数据库连接属性
securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
securityDataSource.setUser(env.getProperty("jdbc.user"));
securityDataSource.setPassword(env.getProperty("jdbc.password"));
<details>
<summary>英文:</summary>
Here is the issue
//set database connection props
securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.user"));
securityDataSource.setJdbcUrl(env.getProperty("jdbc.password"));
You are using setJdbcUrl for everything
It should be something like
//set database connection props
securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
securityDataSource.setUser(env.getProperty("jdbc.user"));
securityDataSource.setPassword(env.getProperty("jdbc.password"));
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论