JavaFX/SQL图书馆应用新增借阅功能异常，PHPMyAdmin操作正常求助

Hey there, let's figure out why your JavaFX app is failing to add lend records when phpMyAdmin works perfectly! Since the database accepts entries directly, the problem is almost certainly in how your Java code interacts with the DB—here are the key things to check and fix:

1. Verify Your PreparedStatement SQL & Parameter Binding #

First, let's make sure your SQL query and parameter setup are correct:

• Double-check your INSERT statement: Does it match the lend table's column names? A common mistake is mismatching camelCase (Java) vs snake_case (MySQL) column names (e.g., using userId instead of user_id in your SQL).
• Ensure you're binding the right number of parameters, in the right order. Remember, PreparedStatement uses 1-based indexing. For example:

  // Correct binding for INSERT INTO lend (user_id, book_id, return_date) VALUES (?, ?, ?)
  ps.setInt(1, userId);   // Matches first ? (user_id)
  ps.setInt(2, bookId);   // Matches second ? (book_id)
  ps.setString(3, returnDate); // Matches third ? (return_date)
  

• If your lend table has an auto-incrementing primary key (like lend_id), don't include it in your INSERT statement—let the database handle it automatically.

2. Don't Forget to Commit Transactions #

JDBC connections default to autoCommit=false in most cases, which means your INSERT won't actually save to the database unless you explicitly commit it. phpMyAdmin auto-commits every change, which is why it works there.
Add a commit call after executing your update, and rollback on failure:

int rowsAffected = ps.executeUpdate();
conn.commit(); // Critical! Saves the change to the DB
return rowsAffected > 0;

In your catch block, add rollback to undo any partial changes:

catch (SQLException e) {
    e.printStackTrace();
    if (conn != null) {
        try {
            conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    return false;
}

3. Fix Date Type Mismatches #

If your return_date column is a DATE or DATETIME type in MySQL, passing a raw String can cause format errors. MySQL expects dates in YYYY-MM-DD format—if your app is sending a different format (like MM/DD/YYYY), it'll throw an exception.
Instead of using a String, use Java's date classes for safer handling:

// Convert your String to a LocalDate (adjust formatter to match your input format)
LocalDate returnLocalDate = LocalDate.parse(returnDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
ps.setDate(3, java.sql.Date.valueOf(returnLocalDate));

4. Check for Foreign Key Constraint Violations #

Your lend table links user and book via foreign keys—if you're passing a userId or bookId that doesn't exist in their respective tables, MySQL will reject the insert. phpMyAdmin works because you're manually entering valid IDs, but your app might be passing invalid values (like a user ID from a typo or uninitialized variable).

5. Capture Full Error Details #

Right now, you're missing the full error context—print the complete exception stack trace instead of just a message. This will tell you exactly what's wrong (SQL syntax error, constraint violation, etc.):

catch (SQLException e) {
    e.printStackTrace(); // Prints full error, including SQL state and line number
    return false;
}

Example Fixed Code #

Here's a polished version of your addLend method incorporating these fixes:

@Override
public boolean addLend(int userId, int bookId, String returnDate) {
    String sql = "INSERT INTO lend (user_id, book_id, return_date) VALUES (?, ?, ?)";
    
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, userId);
        ps.setInt(2, bookId);
        
        // Handle date conversion safely
        LocalDate returnLocalDate = LocalDate.parse(returnDate, DateTimeFormatter.ISO_LOCAL_DATE);
        ps.setDate(3, java.sql.Date.valueOf(returnLocalDate));
        
        int rowsAffected = ps.executeUpdate();
        conn.commit();
        return rowsAffected > 0;
        
    } catch (SQLException | DateTimeParseException e) {
        e.printStackTrace();
        try {
            if (conn != null) conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
}

If you share the full exception stack trace, your complete addLend code, and the structure of your lend table, we can pinpoint the exact issue even faster!

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