在javafx中编辑TableView数据库表

JavaFX中编辑TableView数据库表

基础概念

TableView是JavaFX中用于显示和编辑表格数据的控件，常用于展示数据库查询结果并提供编辑功能。它与数据库表的交互通常通过数据模型和控制器实现。

实现方式

1. 基本TableView与数据库集成

// 数据库连接类
public class DBConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "username";
    private static final String PASS = "password";
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }
}

// 数据模型类
public class Person {
    private final IntegerProperty id;
    private final StringProperty name;
    private final IntegerProperty age;
    
    public Person(int id, String name, int age) {
        this.id = new SimpleIntegerProperty(id);
        this.name = new SimpleStringProperty(name);
        this.age = new SimpleIntegerProperty(age);
    }
    
    // Getter和Setter方法...
}

// 主控制器类
public class TableViewController implements Initializable {
    @FXML private TableView<Person> tableView;
    @FXML private TableColumn<Person, Integer> idColumn;
    @FXML private TableColumn<Person, String> nameColumn;
    @FXML private TableColumn<Person, Integer> ageColumn;
    
    private ObservableList<Person> personData = FXCollections.observableArrayList();
    
    @Override
    public void initialize(URL location, ResourceBundle resources) {
        // 设置列与属性的绑定
        idColumn.setCellValueFactory(cellData -> cellData.getValue().idProperty().asObject());
        nameColumn.setCellValueFactory(cellData -> cellData.getValue().nameProperty());
        ageColumn.setCellValueFactory(cellData -> cellData.getValue().ageProperty().asObject());
        
        // 启用表格编辑
        tableView.setEditable(true);
        
        // 设置可编辑列
        nameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
        ageColumn.setCellFactory(TextFieldTableCell.forTableColumn(new IntegerStringConverter()));
        
        // 加载数据
        loadDataFromDB();
    }
    
    private void loadDataFromDB() {
        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM persons")) {
            
            while (rs.next()) {
                personData.add(new Person(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("age")
                ));
            }
            tableView.setItems(personData);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 编辑提交处理
    @FXML
    private void handleNameEdit(TableColumn.CellEditEvent<Person, String> event) {
        Person person = event.getRowValue();
        person.setName(event.getNewValue());
        updatePersonInDB(person);
    }
    
    @FXML
    private void handleAgeEdit(TableColumn.CellEditEvent<Person, Integer> event) {
        Person person = event.getRowValue();
        person.setAge(event.getNewValue());
        updatePersonInDB(person);
    }
    
    private void updatePersonInDB(Person person) {
        String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, person.getName());
            pstmt.setInt(2, person.getAge());
            pstmt.setInt(3, person.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. 使用ORM框架(如Hibernate)

@Entity
@Table(name = "persons")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    private String name;
    private int age;
    
    // Getter和Setter方法...
}

// 控制器中使用Hibernate
public class TableViewController implements Initializable {
    // ...其他代码同前...
    
    private void loadDataFromDB() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        List<Person> persons = session.createQuery("FROM Person", Person.class).list();
        session.close();
        
        personData.addAll(persons);
        tableView.setItems(personData);
    }
    
    private void updatePersonInDB(Person person) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        
        try {
            tx = session.beginTransaction();
            session.update(person);
            tx.commit();
        } catch (Exception e) {
            if (tx != null) tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
}

常见问题及解决方案

1. 数据不更新问题

原因: 可能是未正确设置可编辑属性或未绑定事件处理器解决:

tableView.setEditable(true);
nameColumn.setCellFactory(TextFieldTableCell.forTableColumn());
nameColumn.setOnEditCommit(event -> handleNameEdit(event));

2. 数据库连接问题

原因: 连接未正确关闭或连接池耗尽解决: 使用try-with-resources确保资源释放

try (Connection conn = DBConnection.getConnection();
     Statement stmt = conn.createStatement()) {
    // 执行查询
}

3. 性能问题

原因: 大量数据加载导致界面卡顿解决: 使用分页加载

// 分页查询
String sql = "SELECT * FROM persons LIMIT ? OFFSET ?";
pstmt.setInt(1, pageSize);
pstmt.setInt(2, pageNumber * pageSize);

4. 数据同步问题

原因: 多用户同时编辑导致数据不一致解决: 使用乐观锁或时间戳检查

// 更新时检查版本
String sql = "UPDATE persons SET name=?, age=?, version=version+1 WHERE id=? AND version=?";
pstmt.setInt(4, originalVersion);

高级功能实现

1. 自定义单元格编辑器

// 自定义年龄编辑器
ageColumn.setCellFactory(column -> new TableCell<Person, Integer>() {
    private final Spinner<Integer> spinner = new Spinner<>(0, 150, 0);
    
    {
        spinner.setEditable(true);
        spinner.valueProperty().addListener((obs, oldVal, newVal) -> {
            if (isEditing()) {
                commitEdit(newVal);
            }
        });
    }
    
    @Override
    protected void updateItem(Integer item, boolean empty) {
        super.updateItem(item, empty);
        if (empty) {
            setGraphic(null);
        } else {
            spinner.getValueFactory().setValue(item);
            setGraphic(spinner);
        }
    }
});

2. 添加删除行功能

@FXML
private void handleDelete() {
    Person selected = tableView.getSelectionModel().getSelectedItem();
    if (selected != null) {
        deleteFromDB(selected.getId());
        personData.remove(selected);
    }
}

private void deleteFromDB(int id) {
    String sql = "DELETE FROM persons WHERE id = ?";
    try (Connection conn = DBConnection.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, id);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

3. 添加新行功能

@FXML
private void handleAdd() {
    String sql = "INSERT INTO persons (name, age) VALUES (?, ?)";
    try (Connection conn = DBConnection.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
        
        pstmt.setString(1, "New Person");
        pstmt.setInt(2, 20);
        pstmt.executeUpdate();
        
        try (ResultSet rs = pstmt.getGeneratedKeys()) {
            if (rs.next()) {
                int newId = rs.getInt(1);
                Person newPerson = new Person(newId, "New Person", 20);
                personData.add(newPerson);
                tableView.getSelectionModel().select(newPerson);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}