Adding new User to a table from MYSQL database using JavaFX is not working

Question

I searched but couldn't find a solution because the examples on google search does not with my programming stil.

edit: I solved the problem in the addUser method.

The date format in MySQL is YYY-MM-DD. In my DatePicker it's DD.MM.YYY. How can get this fixed? Do I have to change something in my database or in my java file?

package application;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javafx.application.Application;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Insets;
import javafx.stage.Stage;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ButtonBar;
import javafx.scene.control.DatePicker;
import javafx.scene.control.Label;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.BorderPane;
import javafx.scene.layout.ColumnConstraints;
import javafx.scene.layout.GridPane;
import javafx.scene.layout.HBox;
import javafx.scene.text.Font;

public class Main extends Application {

    private BorderPane root;
    private Scene scene;
    private GridPane grid;

    private Label lblFirstName;
    private Label lblLastName;
    private Label lblDOB;

    private TextField txtFirstName;
    private TextField txtLastName;
    private DatePicker txtDOB;

    private Button btnAdd;
    private Button btnCancel;
    private Button btnUpdate;

    private HBox hbox;

    private ButtonBar buttonBar;

    private TableView<User> table;

    final ObservableList<User> data = FXCollections.observableArrayList();

    Connection conn;
    PreparedStatement pst = null;
    ResultSet rs = null;

    String url = "jdbc:mysql://localhost:3306/...";
    String user = "root";
    String password = "...";

    @Override
    public void start(Stage primaryStage) {
        try {
            CheckConnection();

            root = new BorderPane();
            grid = new GridPane();

            // Create  labels and textfields
            lblFirstName = new Label("First Name");
            lblFirstName.setFont(new Font("Times New Roman", 18));
            lblFirstName.setPrefSize(100, 50);

            lblLastName = new Label("Last Name");
            lblLastName.setFont(new Font("Times New Roman", 18));
            lblLastName.setPrefSize(100, 50);

            lblDOB = new Label("DOB");
            lblDOB.setFont(new Font("Times New Roman", 18));
            lblDOB.setPrefSize(150, 50);

            txtFirstName = new TextField();
            txtLastName = new TextField();
            txtDOB = new DatePicker();

            // add to grid
            grid.add(lblFirstName, 0, 0, 1, 1);
            grid.add(txtFirstName, 1, 0, 1, 1);
            grid.add(lblLastName, 0, 1, 1, 1);
            grid.add(txtLastName, 1, 1, 1, 1);
            grid.add(lblDOB, 0, 2, 1, 1);
            grid.add(txtDOB, 1, 2, 1, 1);

            grid.setHgap(10);
            grid.setVgap(5);

            grid.setPadding(new Insets(50, 10, 10, 30));

            // Column constraints
            ColumnConstraints column1 = new ColumnConstraints();
            ColumnConstraints column2 = new ColumnConstraints();

            grid.getColumnConstraints().add(column1);
            grid.getColumnConstraints().add(column2);

            column1.setPrefWidth(110);
            column2.setPrefWidth(200);

            // Buttons, Button Actions, ButtonBar
            btnAdd = new Button("Add");
            btnAdd.setPrefSize(40, 40);
            btnAdd.setOnAction(e -> {
                addUser();
            });

            btnCancel = new Button("Cancel");
            btnCancel.setPrefSize(40, 40);
            btnCancel.setOnAction(e -> {
                clearFields();
            });

            btnUpdate = new Button("Update");
            btnUpdate.setPrefSize(40, 40);
            btnUpdate.setOnAction(e -> {
                updateTable();
            });

            buttonBar = new ButtonBar();
            buttonBar.getButtons().addAll(btnAdd, btnCancel, btnUpdate);

            // add ButtonBar to HBox
            hbox = new HBox();
            hbox.getChildren().add(buttonBar);
            hbox.setPadding(new Insets(10));

            // create table
            table = new TableView<>();

            TableColumn<User, String> IDColumn = new TableColumn<User, String>("PersonID");
            IDColumn.setPrefWidth(100);
            IDColumn.setCellValueFactory(new PropertyValueFactory<>("personID"));

            TableColumn<User, String> vornameColumn = new TableColumn<User, String>("First Name");
            vornameColumn.setPrefWidth(100);
            vornameColumn.setCellValueFactory(new PropertyValueFactory<>("firstName"));

            TableColumn<User, String> nachnameColumn = new TableColumn<User, String>("Last Name");
            nachnameColumn.setPrefWidth(100);
            nachnameColumn.setCellValueFactory(new PropertyValueFactory<>("lastName"));

            TableColumn<User, String> dobColumn = new TableColumn<User, String>("DOB");
            dobColumn.setPrefWidth(100);
            dobColumn.setCellValueFactory(new PropertyValueFactory<>("dob"));

            table.getColumns().addAll(IDColumn, vornameColumn, nachnameColumn, dobColumn);

            root.setCenter(table);
            BorderPane.setMargin(table, new Insets(10, 10, 10, 10));
            root.setLeft(grid);
            root.setBottom(hbox);

            scene = new Scene(root, 1000, 500);
            scene.getStylesheets().add(getClass().getResource("application.css").toExternalForm());
            primaryStage.setScene(scene);
            primaryStage.show();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void CheckConnection() {
        conn = DBConnection.DbConnector();
        if (conn == null) {
            System.out.println("Connection Not Successful");
            System.exit(1);
        } else {
            System.out.println("Connection Successful");
        }
    }

    public void updateTable() {
        data.clear();  
        try {
            String query = "SELECT * FROM persons ";
            pst = conn.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                data.add(new User(rs.getString("PersonID"), rs.getString("Firstname"), rs.getString("Lastname"),
                        rs.getString("DOB")
                ));
                table.setItems(data);
            }
            pst.close();
            rs.close();
        } catch (Exception e1) {
            System.err.println(e1);
        }
    }

    public void addUser() {
        try {
            conn = DBConnection.DbConnector();
            String query = "INSERT into persons (Firstname, Lastname, DOB) VALUES (?, ?, ?)";
            pst = conn.prepareStatement(query);

            pst.setString(1, txtFirstName.getText());
            pst.setString(2, txtLastName.getText());
            pst.setString(3, ((TextField)txtDOB.getEditor()).getText());

            pst.executeUpdate();
            pst.close();
        } catch (Exception e2) {
            System.err.println(e2);
        }
    }

    public void clearFields() {
        txtFirstName.clear();
        txtLastName.clear();
        txtDOB.setValue(null);
    }

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

package application;

import javafx.beans.property.SimpleStringProperty;

public class User {
    private SimpleStringProperty personID;
    private SimpleStringProperty firstName;
    private SimpleStringProperty lastName;
    private SimpleStringProperty dob;

    public User(String pID, String fName, String lName, String DOB) {
        this.personID = new SimpleStringProperty(pID);
        this.firstName = new SimpleStringProperty(fName);
        this.lastName = new SimpleStringProperty(lName);
        this.dob = new SimpleStringProperty(DOB);
    }

    // getter , setter
    public String getPersonID() {
        return personID.get();
    }

    public void setPersonenID(String pID) {
        personID.set(pID);
    }

    public String getFirstName() {
        return firstName.get();
    }

    public void setFirstName(String fName) {
        firstName.set(fName);
    }

    public String getLastName() {
        return lastName.get();
    }

    public void setLastName(String lName) {
        lastName.set(lName);
    }

    public String getDob() {
        return dob.get();
    }

    public void setDob(String DOB) {
        dob.set(DOB);
    }
}

package application;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    static String url ="jdbc:mysql://localhost:3306/...";         
    static String user ="root";
    static String password="...";

    public static Connection DbConnector(){        
        try{
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return null;
    }
}

it does nothing when I click the Add Button. there's also no error showing in the console — 2021, Apr 15 '21 at 17:47
how do you see _it does nothing_? anyway: stick to java naming conventions and naming patterns — kleopatra, Apr 15 '21 at 18:00
I solved the problem. Now I can add a new Person but theres something wrong with the value for dob. I get a wrong date im my table. The dateformat in mysql is YYY-MM-DD. In my DatePicker its DD.MM.YYY how can get this fixed? — 2021, Apr 15 '21 at 18:33
What is the data type of column `DOB` in database table `persons`? Is it [DATE](https://dev.mysql.com/doc/refman/8.0/en/datetime.html)? — Abra, Apr 15 '21 at 18:52

score 0 · Accepted Answer · answered Apr 16 '21 at 10:23

I understand from your comment that the data type of column DOB, in database table persons is DATE. Hence the format is irrelevant because even though the documentation for DATE type includes the following:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

I think this is misleading because according to the storage requirements documentation, a DATE value is stored in three bytes. So the format is only for human-readable display.

So since the format is irrelevant, your problem is basically how to get the DatePicker value and insert it into DOB column of persons database table.

Method getValue(), in class DatePicker, returns a LocalDate.

If you are using MySQL Connector, then according to the documentation, the DATE data type maps to java class java.sql.Date.

Therefore you need to convert java.time.LocalDate to java.sql.Date. You can do this by calling static method valueOf() in class java.sql.Date. Refer to this SO question.

Here is my rewritten version of your addUser() method. It uses try-with-resources.

public void addUser() {
    String query = "INSERT into persons (Firstname, Lastname, DOB) VALUES (?, ?, ?)";
    try (conn = DBConnection.DbConnector();
         pst = conn.prepareStatement(query)) {
        pst.setString(1, txtFirstName.getText());
        pst.setString(2, txtLastName.getText());
        pst.setDate(3, java.sql.Date.valueOf(txtDOB.getValue()));
        pst.executeUpdate();
    }
    catch (SQLException xSql) {
        xSql.printStackTrace();
    }
}

If you still also want to change the format of the date displayed by DatePicker, you can set its converter property. The below code demonstrates how to do this using an anonymous class.

DatePicker txtDOB = new DatePicker();
txtDOB.setConverter(new StringConverter<LocalDate>() {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy", Locale.ENGLISH);

    @Override
    public String toString(LocalDate object) {
        if (object != null) {
            return object.format(formatter);
        }
        return null;
    }

    @Override
    public LocalDate fromString(String string) {
        if (string != null) {
            return LocalDate.parse(string, formatter);
        }
        return null;
    }
});

Adding new User to a table from MYSQL database using JavaFX is not working

1 Answers1