使用SimpleJdbcCall调用MySQL存储过程插入用户失败求助

许久没做Java开发，最近开发了一个项目管理小应用，需要调用MySQL存储过程插入新用户。该存储过程在MySQL Workbench中调用可成功插入新用户，但通过SimpleJdbcCall从应用调用时，既未插入用户也未抛出异常。已排除数据库连接问题——测试调用另一个统计现有邮箱总数的存储过程可正常执行，目前无法定位问题原因，恳请提供排查指导。

无法正常运行的代码（更新版） #

@Repository
@RequiredArgsConstructor
@Slf4j
public class UserRepository implements IUser<User> {
    private SimpleJdbcCall sj;
    private final BCryptPasswordEncoder encoder;
    
    @Bean
    public SimpleJdbcCall setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        sj = new SimpleJdbcCall(jdbcTemplate);
        return sj;
    }
    
    private boolean emailExists(String email) {
         SqlParameterSource in = new MapSqlParameterSource()
                    .addValue("email", email); 
        Map<String, Object> out = 
                sj.withProcedureName(ApplicationConstant.APPLICATION_EMAIL_COUNT)
                .execute(in);
       Integer count =  (Integer) out.get("total_email");
       return count > 0;
    }
    
    @Override
    public User create(User user) {
        if(emailExists(user.getEmail().trim().toLowerCase())) throw new ApiException("Email already in use. Please login using your email.");
        try {
            String firstName = user.getFirstName();
            String lastName = user.getLastName();
            String email = user.getEmail();
            String password = user.getPassword();
             SqlParameterSource in = new MapSqlParameterSource()
                        .addValue("firstName", firstName,Types.NVARCHAR)
                        .addValue("lastName", lastName,Types.NVARCHAR)
                        .addValue("email", email,Types.NVARCHAR)
                        .addValue("password", password,Types.NVARCHAR)
                        .addValue("user_id", 0, Types.BIGINT);
            Map<String, Object> out = 
                    sj.withProcedureName("UspUserUpsert")
                    .withCatalogName("manageproject")
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(
                      new SqlParameter("firstName", Types.NVARCHAR),
                      new SqlParameter("lastName", Types.NVARCHAR),
                      new SqlParameter("email", Types.NVARCHAR),
                      new SqlParameter("password", Types.NVARCHAR),
                      new SqlOutParameter("user_id", Types.BIGINT)  
                    )
                    .execute(in);
    
            Long userId = (Long) out.get("user_id");
        } catch(Exception e) {
            throw new ApiException("An error occurs. Please try again.");
        }
        return user;
    }
    
}

参数值 #

MapSqlParameterSource {firstName=John, lastName=Doe, email=jdoe@gmail.com, password=123456.}

存储过程 #

DELIMITER //;
CREATE PROCEDURE UspUserUpsert(
 IN firstName       VARCHAR(50),
 IN lastName        VARCHAR(50),
 IN email           VARCHAR(100),
 IN `password`      VARCHAR(255),
 OUT user_id        INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
     
    SELECT 
        id 
    INTO user_id
    FROM 
        `user` 
    WHERE
        email = @email;
     
     START TRANSACTION;
     IF (user_id > 0) THEN
        UPDATE 
            `user` u
        SET
            u.address = 'TESTING'
        WHERE
            u.user_id = user_id;
     END IF;
     IF (ISNULL(user_id) =  1 OR user_id = 0) THEN
         INSERT INTO User (
            first_name,
            last_name,
            email,
            `password`
        )
        VALUES(
            firstName,
            lastName,
            email,
            `password`
        );
     SELECT 
        1028513
     INTO user_id;
     END IF;
    COMMIT;
END //
DELIMITER ;

排查指导 #

1. 修复存储过程的参数引用错误 #

存储过程中WHERE email = @email使用了MySQL会话变量@email，而非传入的存储过程IN参数email，这会导致查询永远匹配不到数据，user_id始终为NULL。修改为：

SELECT 
    u.id 
INTO user_id
FROM 
    `user` u
WHERE
    u.email = email;

2. 补充日志定位执行流程 #

• Java端日志：在execute前后添加日志，打印调用参数和返回结果，确认存储过程是否被正确调用：

  log.info("调用存储过程UspUserUpsert，参数：{}", in.getValues());
  Map<String, Object> out = sj...execute(in);
  log.info("存储过程返回结果：{}", out);
  

• 存储过程日志：在关键步骤添加日志记录（比如插入自定义日志表），确认存储过程执行到了哪个分支：

  -- 在SELECT INTO后添加
  INSERT INTO debug_log(content) VALUES('查询user_id结果：' + user_id);
  -- 在INSERT后添加
  INSERT INTO debug_log(content) VALUES('执行了INSERT操作');
  

3. 修正参数类型不匹配问题 #

• Java代码中使用Types.NVARCHAR对应存储过程的VARCHAR参数，改为Types.VARCHAR；
• 存储过程的OUT参数user_id是INT类型，Java中对应Types.INTEGER，而非Types.BIGINT，修正declareParameters中的配置：

  new SqlOutParameter("user_id", Types.INTEGER)
  

4. 让存储过程抛出异常 #

当前存储过程的异常处理只做回滚，不会把异常传递给Java端，导致应用无法感知错误。修改异常处理器，添加RESIGNAL抛出异常：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL; -- 将异常抛出给调用方
END;

5. 避免SimpleJdbcCall复用冲突 #

每次调用create方法时重复配置SimpleJdbcCall，可能导致旧配置残留。建议每次调用时创建新的SimpleJdbcCall实例：

// 替换原execute代码部分
Map<String, Object> out = new SimpleJdbcCall(jdbcTemplate)
        .withProcedureName("UspUserUpsert")
        .withCatalogName("manageproject")
        .withoutProcedureColumnMetaDataAccess()
        .declareParameters(
          new SqlParameter("firstName", Types.VARCHAR),
          new SqlParameter("lastName", Types.VARCHAR),
          new SqlParameter("email", Types.VARCHAR),
          new SqlParameter("password", Types.VARCHAR),
          new SqlOutParameter("user_id", Types.INTEGER)  
        )
        .execute(in);

6. 直接用原生JDBC调用存储过程 #

绕过SimpleJdbcCall，用JdbcTemplate直接执行CALL语句，排查是否是SimpleJdbcCall的配置问题：

jdbcTemplate.execute(connection -> {
    CallableStatement cs = connection.prepareCall("{call manageproject.UspUserUpsert(?, ?, ?, ?, ?)}");
    cs.setString(1, firstName);
    cs.setString(2, lastName);
    cs.setString(3, email);
    cs.setString(4, password);
    cs.registerOutParameter(5, Types.INTEGER);
    cs.execute();
    Long userId = cs.getLong(5);
    log.info("返回user_id: {}", userId);
    return null;
});

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