Java程序可连接MySQL但Spring MVC Web应用无法连接求助

Hey there! Let's troubleshoot why your Spring MVC web app isn't saving registration data to MySQL, even though your standalone Java test works perfectly. Here are the key areas to check and fix:

1. Fix the SQL Syntax Error #

Looking at your DAO code, the SQL statement has a critical formatting issue. When you concatenate the strings, there's no space between register and the opening parenthesis, or between the closing parenthesis and VALUES. This will result in invalid SQL that the database can't parse.

Bad SQL:

String sql = "Insert into register"+" (FirstName,LastName,Email_username,password)" + "values(?,?,?,?)";

Fixed SQL:

String sql = "INSERT INTO register (FirstName, LastName, Email_username, password) VALUES (?, ?, ?, ?)";

2. Verify DataSource Injection #

Your JdbcRegisterDAO uses @Service to mark it as a Spring-managed bean, but you need to make sure:

• Your Spring configuration includes <context:component-scan base-package="com.chorechart"/> to scan and register this bean.
• The dataSource bean is being properly injected into the DAO. Check if System.out.println("DataSourrce"+dataSource); prints a non-null value in Tomcat's logs. If it's null, Spring isn't injecting the datasource—double-check your bean configuration files are loaded correctly in web.xml.

3. Check Tomcat's Classpath Dependencies #

Your standalone test works because it has access to MySQL JDBC driver and Apache DBCP jars locally, but these might not be packaged into your WAR file for Tomcat. In your Maven pom.xml, ensure these dependencies are set to compile scope (the default) so they end up in WEB-INF/lib:

<!-- MySQL JDBC Driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version> <!-- Use a version compatible with your MySQL server -->
</dependency>

<!-- Apache DBCP for DataSource -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

4. Get Full Error Logs #

Right now, your catch block only prints a partial message and stack trace to the console. Tomcat logs most output to catalina.out (in Tomcat's logs directory), so check that file for the full exception details. Common issues here might be:

• Invalid database credentials (even if your test works, double-check the username/password in your datasource config)
• Missing database permissions for the user
• Connection timeouts

5. Ensure Proper Resource Handling #

Your DAO code doesn't close the Connection or PreparedStatement after use, which can lead to connection leaks over time. Wrap these resources in try-with-resources blocks to auto-close them:

@Override
public void insertUser(Register registerUser) {
    String sql = "INSERT INTO register (FirstName, LastName, Email_username, password) VALUES (?, ?, ?, ?)";
    
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
             
        ps.setString(1, registerUser.getFirstName());
        ps.setString(2, registerUser.getLastName());
        ps.setString(3, registerUser.getEmail_username());
        ps.setString(4, registerUser.getPassword());
        
        int rowsInserted = ps.executeUpdate();
        System.out.println("Inserted " + rowsInserted + " row(s)");
    } catch (Exception ex) {
        System.err.println("Error inserting user:");
        ex.printStackTrace();
    }
}

Your Original Config & Code #

DataSource Configuration:

<beans xmlns="http://www.springframework.org/schema/beans" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> 
        <property name="driverClassName" value="com.mysql.jdbc.Driver" /> 
        <property name="url" value="jdbc:mysql://localhost:3306/chorechart" /> 
        <property name="username" value="username" /> 
        <property name="password" value="password*" /> 
    </bean> 
</beans>

DAO Layer Code:

package com.chorechart.dao.impl; 

import java.sql.Connection; 
import java.sql.PreparedStatement; 
import javax.sql.DataSource; 
import org.springframework.stereotype.Service; 
import com.chorechart.dao.RegisterDAO; 
import com.chorechart.model.Register; 

@Service("registerDAO") 
public class JdbcRegisterDAO implements RegisterDAO{ 
    private DataSource dataSource; 

    public void setDataSource(DataSource dataSource) { 
        this.dataSource = dataSource; 
    } 

    @Override 
    public void insertUser(Register registerUser) { 
        String sql = "Insert into register"+" (FirstName,LastName,Email_username,password)" + "values(?,?,?,?)"; 
        try { 
            System.out.println("DataSourrce"+dataSource); 
            Connection conn = dataSource.getConnection(); 
            System.out.println("coonection"+conn); 
            PreparedStatement ps= conn.prepareStatement(sql); 
            ps.setString(1,registerUser.getFirstName()); 
            ps.setString(2,registerUser.getLastName()); 
            ps.setString(3, registerUser.getEmail_username()); 
            ps.setString(4, registerUser.getPassword()); 
            int x = ps.executeUpdate(); 
        }catch(Exception ex) { 
            System.out.print("JDBCRegisterDao"); 
            ex.printStackTrace(); 
        } 
    } 

    @Override 
    public Register findUser(String userName, String userPwd) { 
        // TODO Auto-generated method stub 
        return null; 
    } 

    public DataSource getDataSource() { 
        return dataSource; 
    } 
}

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