Java+MySQL检查注册信息时ResultSet异常问题求助

异常原因 #

1. ResultSet游标未移动：JDBC的ResultSet默认指向结果集的起始位置之前，直接调用getString()会触发Before start of result set异常，必须先调用resultSet.next()将游标移动到第一条记录（如果存在）。
2. 空结果集直接取值：当输入未注册的用户名时，查询返回空ResultSet，此时调用getString()会触发Illegal operation on empty result set异常，因为没有任何数据可以读取。
3. 查询逻辑不完整：当前SQL仅根据用户名查询，无法检测邮箱或手机号是否被其他用户注册，即使用户名不存在，邮箱/手机号可能已被占用，现有逻辑覆盖不到这种场景。
4. 校验顺序错误：先执行数据库查询，再做参数非空、格式校验，会导致无效的数据库请求（比如参数为空时没必要查库）。
5. 异常被吞掉：try/catch块仅打印堆栈，没有抛出自定义异常，导致即使检测到重复也无法向上层抛出对应的异常。
6. 资源未释放：PreparedStatement和ResultSet未关闭，可能导致数据库连接资源泄漏。

修正后的代码 #

public void createUser(User user) throws NameFieldNotFilledException, EmailFieldNotFilledException, PhoneFieldNotFilledException,
        PasswordFieldNotFilledException, PasswordConfirmationDoesNotMatchException, EmailNotValidException, PasswordInvalidException,
        UserAlreadyRegisteredException, EmailAlreadyRegisteredException, PhoneAlreadyRegisteredException {

    // 1. 先做参数合法性校验，避免无效数据库请求
    if (user.getName() == null || user.getName().trim().isEmpty())
        throw new NameFieldNotFilledException("Name is required");
    if (user.getEmail() == null || user.getEmail().trim().isEmpty())
        throw new EmailFieldNotFilledException("Email is required");
    if (user.getPhone() == null || user.getPhone().trim().isEmpty())
        throw new PhoneFieldNotFilledException("Phone is required");
    if (user.getPassword() == null || user.getPassword().trim().isEmpty())
        throw new PasswordFieldNotFilledException("Password is required");
    if (!user.getConfirmPassword().equals(user.getPassword()))
        throw new PasswordConfirmationDoesNotMatchException("Password and confirm password do not match");

    Matcher matcherEmail = patternEmail.matcher(user.getEmail());
    if (!matcherEmail.matches())
        throw new EmailNotValidException("Email format invalid. Example valid: user@domain.com");

    Matcher matcherPassword = patternPassword.matcher(user.getPassword());
    if (!matcherPassword.matches())
        throw new PasswordInvalidException("Invalid password");

    // 2. 检查用户名是否已存在
    try (PreparedStatement ps = this.databaseMySQL.getConnection().prepareStatement(
            "SELECT USR_NAME FROM OS_USERS WHERE USR_NAME = ?")) {
        ps.setString(1, user.getName());
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                throw new UserAlreadyRegisteredException("User already registered");
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("Failed to check existing username", e);
    }

    // 3. 检查邮箱是否已存在
    try (PreparedStatement ps = this.databaseMySQL.getConnection().prepareStatement(
            "SELECT USR_EMAIL FROM OS_USERS WHERE USR_EMAIL = ?")) {
        ps.setString(1, user.getEmail());
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                throw new EmailAlreadyRegisteredException("Email already registered");
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("Failed to check existing email", e);
    }

    // 4. 检查手机号是否已存在
    try (PreparedStatement ps = this.databaseMySQL.getConnection().prepareStatement(
            "SELECT USR_PHONE FROM OS_USERS WHERE USR_PHONE = ?")) {
        ps.setString(1, user.getPhone());
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                throw new PhoneAlreadyRegisteredException("Phone already registered");
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("Failed to check existing phone", e);
    }

    // 5. 插入新用户
    String userSalt = PBKDF2Salt.getSalt();
    try (PreparedStatement ps = this.databaseMySQL.getConnection().prepareStatement(
            "INSERT INTO OS_USERS VALUES (?, ?, ?, ?, ?, ?)")) {
        ps.setInt(1, user.getId());
        ps.setString(2, user.getName());
        ps.setString(3, user.getEmail());
        ps.setString(4, user.getPhone());
        ps.setString(5, PBKDF2Salt.hashing(user.getPassword(), userSalt));
        ps.setString(6, userSalt);
        ps.executeUpdate();
    } catch (SQLException e) {
        throw new RuntimeException("Failed to create user", e);
    }
}

关键优化点 #

• 调整校验顺序：先做本地参数校验，再执行数据库操作，减少无效请求。
• 正确处理ResultSet：每次查询后调用rs.next()判断是否存在记录，避免空结果集或游标未移动的异常。
• 拆分查询逻辑：分别查询用户名、邮箱、手机号，确保每个字段的唯一性检查准确。
• 使用try-with-resources：自动关闭PreparedStatement和ResultSet，避免资源泄漏。
• 不吞异常：将数据库异常包装后抛出，同时正常触发自定义异常。
• 简化空值判断：用trim().isEmpty()替代冗余的equalsIgnoreCase判断，更简洁准确。

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