JavaFX : TableView結合MySQL資料庫範例

此範例為一個書籍訂單系統透過TableView來顯示和透過TextField輸入資料來新增、刪除、修改資料庫裡面的資料

1.裝好mysql-connector-java才能連接資料庫

2.程式碼如下 :

APP.java

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

public class APP extends Application{

 public static void main(String[] args) {
  launch(args);
 }

 @Override
 public void start(Stage primaryStage) throws Exception {
  Parent root = FXMLLoader.load(getClass().getResource("/main/Login.fxml"));
  Scene scene = new Scene(root, 600, 400);
  
  primaryStage.setScene(scene);
  primaryStage.setTitle("Book資料庫");
  primaryStage.show();
  
 }

}

Book.java

import javafx.beans.property.StringProperty ;
import javafx.beans.property.SimpleStringProperty ;

public class Book {
    private final StringProperty id = new SimpleStringProperty(this, "id");
    public StringProperty idProperty() {
        return id ;
    }
    public final String getid() {
        return idProperty().get();
    }
    public final void setid(String id) {
     idProperty().set(id);
    }

    private final StringProperty name = new SimpleStringProperty(this, "name");
    public StringProperty nameProperty() {
        return name ;
    }
    public final String getname() {
        return nameProperty().get();
    }
    public final void setname(String name) {
     nameProperty().set(name);
    }

    private final StringProperty money = new SimpleStringProperty(this, "money");
    public StringProperty moneyProperty() {
        return money ;
    }
    public final String getmoney() {
        return moneyProperty().get();
    }
    public final void setmoney(String money) {
     moneyProperty().set(money);
    }
    
    private final StringProperty number = new SimpleStringProperty(this, "number");
    public StringProperty numberProperty() {
        return number ;
    }
    public final String getnumber() {
        return numberProperty().get();
    }
    public final void setnumber(String number) {
     numberProperty().set(number);
    }

    public Book() {}

    public Book(String id, String name, String money, String number) {
        setid(id);
        setname(name);
        setmoney(money);
        setnumber(number);
    }

}

mysqlconnect.java

import java.sql.*;
public class mysqlconnect {
    Connection conn = null;

    public static Connection ConnecrDb() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/book","帳號","密碼");
            System.out.println("Connection success!");
            return conn;
        } catch(Exception e){
            System.out.println(e);
            return null;
        }
    }
}

MainController.java

import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Stage;

import java.sql.*;

public class MainController implements Initializable {

    
    @FXML
    private Button btnlogin;
    
    

    @Override
    public void initialize(URL location, ResourceBundle resources){

        // TODO Auto-generated method stub

     public void login(ActionEvent event) throws IOException{
      Stage stage2 = null; 
          Parent root;
          stage2=(Stage) btnlogin.getScene().getWindow();
          root = FXMLLoader.load(getClass().getResource("/main/Main2.fxml"));
          Scene scene2 = new Scene(root);
          stage2.setScene(scene2);
          stage2.show();
     }

    }

Main2Controller.java

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Button;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.input.MouseEvent;

public class Main2Controller implements Initializable {
 
 Connection conn = null;

    ResultSet rs = null;

    PreparedStatement pst = null;
 
 @FXML
    private TableView tableview;
 
 @FXML 
 private TextField txtfid,txtfname,txtfmoney,txtfnumber;
 
 @FXML
 private Button btninsert,btnupdate,btndelete;

 @Override
 public void initialize(URL arg0, ResourceBundle arg1) {
  // TODO Auto-generated method stub
  
  conn = mysqlconnect.ConnecrDb();
        
        
        TableColumn IdCol = new TableColumn<>("Id");
        IdCol.setCellValueFactory(new PropertyValueFactory<>("id"));
        TableColumn NameCol = new TableColumn<>("Name");
        NameCol.setCellValueFactory(new PropertyValueFactory<>("name"));
        TableColumn MoneyCol = new TableColumn<>("Money");
        MoneyCol.setCellValueFactory(new PropertyValueFactory<>("money"));
        TableColumn NumberCol = new TableColumn<>("Number");
        NumberCol.setCellValueFactory(new PropertyValueFactory<>("number"));
        
        tableview.getColumns().addAll(IdCol, NameCol, MoneyCol, NumberCol);
        try {
   getBookList();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
        
        
        btninsert.setOnAction((ActionEvent event) ->{
         String sql = "INSERT INTO book (id, name, money, number) VALUES (?, ?, ?, ?)";
            try {
    pst=conn.prepareStatement(sql);
    pst.setString(1, txtfid.getText());
             pst.setString(2, txtfname.getText());
             pst.setString(3, txtfmoney.getText());
             pst.setString(4, txtfnumber.getText());
             pst.executeUpdate();
             tableview.getItems().clear();
             getBookList();
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
            
        });
        btnupdate.setOnAction((ActionEvent event) ->{
         String sql = "update book set id=?, name=?, money=?, number=? where id='"+txtfid.getText()+"'";
            try {
    pst=conn.prepareStatement(sql);
    pst.setString(1, txtfid.getText());
             pst.setString(2, txtfname.getText());
             pst.setString(3, txtfmoney.getText());
             pst.setString(4, txtfnumber.getText());
             pst.executeUpdate();
             tableview.getItems().clear();
             getBookList();
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
        });
        btndelete.setOnAction((ActionEvent event) ->{
         String sql = "DELETE FROM book WHERE id=?";
            try {
    pst=conn.prepareStatement(sql);
    pst.setString(1, txtfid.getText());
             pst.executeUpdate();
             tableview.getItems().clear();
             getBookList();
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
        });
        tableview.setOnMouseClicked(new EventHandler() {

   @Override
   public void handle(MouseEvent arg0) {
    // TODO Auto-generated method stub
   
    txtfid.setText(((Book) tableview.getSelectionModel().getSelectedItem()).getid());
    txtfname.setText(((Book) tableview.getSelectionModel().getSelectedItem()).getname());
    txtfmoney.setText(((Book) tableview.getSelectionModel().getSelectedItem()).getmoney());
    txtfnumber.setText(((Book) tableview.getSelectionModel().getSelectedItem()).getnumber());
   }
         
        });
         
        
        
 }
 public List getBookList() throws SQLException {
     String sql = "SELECT * FROM book";
        pst=conn.prepareStatement(sql);
      rs= pst.executeQuery();
       // pst.executeUpdate();
      
      List bookList = new ArrayList<>();
      while (rs.next()) {
          String id = rs.getString("id");
          String name = rs.getString("name");
          String money = rs.getString("money");
          String number = rs.getString("number");
          Book person = new Book(id, name, money, number);
          bookList.add(person);

        }
      tableview.getItems().addAll(bookList);
      
      return bookList;
   }
  
 
}