代码之家 › 专栏 › 技术社区 › PacificNW_Lover

如何使用SpringBoot设置SpringJDBC连接池?

spring-jdbc spring-boot mysql java

PacificNW_Lover · 技术社区 · 7 年前

我在SpringJDBC中使用SpringBoot1.5.4。

使用Spring JDBC的Spring Boot微服务在尝试执行HTTP PUT(在一群用户尝试执行HTTP PUT之后)时会出现以下问题,该HTTP PUT会流到此Spring JDBC调用:

2018-10-10 19:40:02 [http-nio-8081-exec-4] ERROR c.v.r.RepositoryImpl - Problem in updateData() method: 
"org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [select a.user_id,b.user_id, from user a join user_profile b where a.user_id=b.user_id and a.date=?;]; No operations allowed after connection closed.; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:79)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 28,915,589 milliseconds ago.  The last packet sent successfully to the server was 9 milliseconds ago.
    at com.myapp.repository.RepositoryImpl.updateData(RepositoryImpl.java:74)
    at com.myapp.repository.RepositoryImpl$$FastClassBySpringCGLIB$$1be9dd8e.invoke(<generated>)
    ... 52 common frames omitted
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)
    ... 83 common frames omitted

pom.xml:

<artifactId>MyService</artifactId>
<packaging>jar</packaging>
<version>1.0</version>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.4.RELEASE</version>
</parent>

<properties>
    <java.version>1.7</java.version>
</properties>   

<dependencies>
    <!-- Spring -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

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

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.33</version>
    </dependency>
</dependencies>

在我的application.properties设置中,(我有两个不同的数据库-一个本地数据库和一个远程数据库,它与远程数据库的连接丢失;数据库2):

# Local
spring.datasource.database1.url=jdbc:mysql://localhost/database1?zeroDateTimeBehavior=convertToNull
spring.datasource.database1.username=root
spring.datasource.database1.password=ret2my
spring.datasource.database1.driverClassName=com.mysql.jdbc.Driver

# Remote
spring.datasource.database2.url=jdbc:mysql://read-replica-database-production.cranmichpmc.us-west-2.rds.amazonaws.com/database2?zeroDateTimeBehavior=convertToNull
spring.datasource.database2.username=root
spring.datasource.database2.password=ret2a$$
spring.datasource.database2.driverClassName=com.mysql.jdbc.Driver

spring.datasource.database2.hikari.maximum-pool-size=10
spring.datasource.database2.hikari.connection-timeout=60000

还有其他有用的参数我应该考虑吗?

1 回复 | 直到 7 年前

Ajay V 7 年前

spring.datasource.url=jdbc:mysql://10.168.143.140:3306/database_name
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.hikari.maximum-pool-size=4

https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html