Spring Boot+Flutter(Dart)连续请求MySQL报错：No operations allowed after connection closed

Hey, let's break down what's causing this "No operations allowed after connection closed" error and fix it properly!

The Root Cause #

Your issue stems from two critical mistakes in how you're managing database connections, especially in the Spring Boot environment:

1. Singleton Service + Shared Connection Variable
   Spring creates ConnectionService as a singleton bean (only one instance exists for the entire app). That means the connection member variable is shared across all incoming requests. When multiple requests hit your /user/test endpoint concurrently:

   • One request might close the connection while another is still using it
   • Subsequent requests could end up trying to use a connection that was already closed by a previous request
   • The shared variable gets overwritten constantly, leading to unpredictable connection states

2. Manual Connection Management Anti-Pattern
   You're manually creating connections with DriverManager and handling close logic yourself, which is unnecessary in Spring Boot. Spring already provides a robust connection pool (HikariCP by default) that manages connection lifecycle, reuse, and cleanup automatically. Also, calling DriverManager.registerDriver every time you create a connection is redundant and can cause unexpected issues.

3. Flawed Close Logic
   Your closeConnection() method closes the shared connection variable in the service, not the specific connection reference you obtained in testFunction(). In concurrent scenarios, this could close a connection that's being used by another request entirely.

Fixes: Let's Do This the Spring Way #

Option 1: Use JdbcTemplate (Recommended, Simplest Approach) #

Spring's JdbcTemplate handles all connection management for you—you never have to create or close connections manually.

1. Delete your ConnectionService class (we won't need it anymore)
2. Update your UserController to use JdbcTemplate:

@RestController
@RequestMapping("/user")
public class UserController {

    // Inject Spring's pre-configured JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping(path = "/test")
    public void testFunction(@RequestParam(name = "abc") String abc) {
        // Example: Check if record exists using JdbcTemplate
        boolean recordExists = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM your_table WHERE column1 = ? AND column2 = ?",
            Integer.class,
            param1, param2
        ) > 0;

        if (recordExists) {
            // Your existing logic here (e.g., update)
            jdbcTemplate.update("UPDATE your_table SET some_column = ? WHERE ...", yourValue);
        } else {
            // Your insert or other logic here
            jdbcTemplate.update("INSERT INTO your_table (col1, col2) VALUES (?, ?)", val1, val2);
        }
    }
}

Option 2: Manual Connection Handling (Only If You Really Need It) #

If you must manage connections manually (not recommended), fix your service to use Spring's DataSource and avoid shared state:

1. Revise ConnectionService

@Service
public class ConnectionService {
    private static final Logger log = LoggerFactory.getLogger(ConnectionService.class);

    // Inject Spring's auto-configured DataSource (connection pool)
    @Autowired
    private DataSource dataSource;

    public Connection createConnection() throws SQLException {
        // Get a fresh connection from the pool (no shared variable!)
        return dataSource.getConnection();
    }

    // Accept the specific connection to close, instead of using a shared variable
    public void closeConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (Exception e) {
            log.error("Failed to close database connection", e);
        }
    }
}

2. Update UserController to use try-finally for safe cleanup

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    ConnectionService connectionService;

    @GetMapping(path = "/test")
    public void testFunction(@RequestParam(name = "abc") String abc) {
        Connection connection = null;
        try {
            connection = connectionService.createConnection();
            if (abc.isExist(param1, param2, connection)) {
                // Your existing logic
            } else {
                // Your other operations
            }
        } catch (SQLException e) {
            log.error("Database operation failed", e);
            // Add proper error handling here (e.g., return an error response)
        } finally {
            // Always close the connection we created, in a finally block
            connectionService.closeConnection(connection);
        }
    }
}

Bonus: Proper Application Configuration #

Make sure your application.properties (or application.yml) has the correct database setup (Spring will auto-configure the connection pool):

spring.datasource.url=jdbc:mysql://localhost/databaseName?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Final Tips #

• Avoid singleton state: Never store request-specific resources (like database connections) in singleton beans—they're shared across all requests and cause concurrency bugs.
• Use Spring Data JPA: For even simpler database operations, consider using Spring Data JPA instead of JdbcTemplate—it eliminates most boilerplate code.
• Leverage connection pools: Spring's built-in connection pool manages connection reuse, timeouts, and cleanup automatically, which is far more reliable than manual handling.

内容的提问来源于stack exchange，提问作者Lily