CODEWITHSUNDEEP
javamysqldatabasedate
Ellinoir
Ellinoir

Reputation: 11

Incorrect date value: 'd. MMM y' for column 'Birthday' at row 1 - MySQL exception

When i insert data in my java eclipse program and try to insert it to the database i get this error:

I have already installed MySQL connector/J and made the database in MySQL workbench.

    Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
feb. 03, 2021 2:31:18 PM numerologi.views.Make$5 actionPerformed
SEVERE: null
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: 'd. MMM y' for column 'Birthday' at row 1

What does this mean and how do i fix it? please help!

is it the " pst.setString(3, dateChooser.getDateFormatString().toString()); " ?

my code:

 dateChooser = new JDateChooser();
  dateChooser.setBounds(140, 40, 500, 20);
  panel.add(dateChooser);
  
  UpdateButton = new JButton("Update");
  UpdateButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  UpdateButton.setBounds(805, 40, 85, 20);
  panel.add(UpdateButton);
  
  DeleteButton = new JButton("Delete");
  DeleteButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  DeleteButton.setBounds(805, 70, 85, 20);
  panel.add(DeleteButton);
  
  ResetButton = new JButton("Reset");
  ResetButton.setFont(new Font("Tahoma", Font.BOLD, 12));
  ResetButton.setBounds(805, 100, 85, 20);
  panel.add(ResetButton);
  
  scrollPane = new JScrollPane();
  scrollPane.setBounds(10, 154, 880, 286);
  panel.add(scrollPane);
  
  table = new JTable();
  table.setBackground(Color.WHITE);
  model = new DefaultTableModel();
  Object[] column = {"Nr","Name","Birthday","Description","Other"};
  Object[] row = new Object[0];
  model.setColumnIdentifiers(column);
  table.setModel(model);
  scrollPane.setViewportView(table);
  
 
//This method contains all codes for update of Database. //

private void upDateDB() {
  try { 
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/num clients", "root", "");
  pst = con.prepareStatement("SELECT * FROM client");
      
  rs =pst.executeQuery();
  ResultSetMetaData StData = rs.getMetaData();
      
  q = StData.getColumnCount();
      
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  RecordTable.setRowCount(0);
      
  while(rs.next()){
     Vector columnData = new Vector();
          
  for (i = 1; i <= q; i++) {
     columnData.add(rs.getString("Nr"));
     columnData.add(rs.getString("Name"));
     columnData.add(rs.getString("Birthday"));
     columnData.add(rs.getString("Description"));
     columnData.add(rs.getString("Other"));
}
     RecordTable.addRow(columnData);                
          
}} catch (Exception ex) {
     JOptionPane.showMessageDialog(null, ex);
} 

      }


//This method contains all codes for creating events. //

private void createEvents() {
 
  NewButton.addActionListener(new ActionListener() { 
  public void actionPerformed(ActionEvent e) {
  try { 
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("INSERT INTO client (Nr, Name, Birthday, Description, Other) VALUES (?,?,?,?,?)");

      pst.setInt(1,Integer.valueOf(Tnr.getText()));
      pst.setString(2, Tname.getText()); 
      //pst.setString(2, CBunknown.getActionCommand().toString()); - how to insert Checkbox here? if i want name to be unknown?
      pst.setString(3, dateChooser.getDateFormatString().toString());
      pst.setString(4, Tdescription.getText());
      pst.setString(5, Tother.getText());
      
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Saved");
      upDateDB();
      
  }catch (ClassNotFoundException ex) { 
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
  }catch (SQLException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
      
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}
});
  
  UpdateButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
  try { 
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("UPDATE client SET Nr=?,Name=?,Birthday=?,Description=?,Other=? WHERE Nr = ? ");
      pst.setString(1, Tnr.getText());
      pst.setString(2, Tname.getText()); 
      //pst.setString(2, CBunknown.getActionCommand().toString()); - how to insert Checkbox here? if i want name to be unknown?
      pst.setString(3, dateChooser.getDateFormatString().toString());
      pst.setString(4, Tdescription.getText());
      pst.setString(5, Tother.getText());
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Updated");
      upDateDB();
          
  }catch (ClassNotFoundException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level. SEVERE,null, ex);
  }catch (SQLException ex) {             
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
                  
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}
});
  
  DeleteButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  int SelectedRows = table.getSelectedRow();
          
  try{
      Class.forName("com.mysql.jdbc.Driver");
      id = Integer.parseInt(RecordTable.getValueAt(SelectedRows, 0).toString());
      deleteItem = JOptionPane.showConfirmDialog(null,"Do you want to delete client?", "warning",JOptionPane.YES_NO_OPTION);
      
  if (deleteItem ==JOptionPane.YES_OPTION ) {}
      con = DriverManager.getConnection("jdbc:mysql://localhost/num clients", "root", "");   
      pst = con.prepareStatement("DELETE FROM client WHERE Nr = ? ");
      pst.setInt(1, id);
      pst.executeUpdate();
      JOptionPane.showMessageDialog(null, "Deleted");
      upDateDB();
      
      Tnr.setText(" ");
      Tname.setText("");
      Tdescription.setText("");
      Tother.setText("");
      dateChooser.setToolTipText("");
  
  }catch (ClassNotFoundException ex) {
      java.util.logging.Logger.getLogger(Make.class.getName()).log(java.util.logging.Level.SEVERE,null, ex);
  }catch (SQLException ex) {
      System.err.println(ex);
              
  }catch (Exception ex){
      JOptionPane.showMessageDialog(null, ex); 
  }}  
});
  
  ResetButton.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
      Tnr.setText(" ");
      Tname.setText(" ");
      Tdescription.setText(" ");
      Tother.setText(" ");
      }
});
  
  table.addMouseListener(new MouseListener() {
  public void mouseClicked(MouseEvent e) {
  DefaultTableModel RecordTable = (DefaultTableModel)table.getModel();
  int SelectedRows = table.getSelectedRow();   
      
     Tnr.setText(RecordTable.getValueAt(SelectedRows, 1).toString());
     Tname.setText(RecordTable.getValueAt(SelectedRows, 2).toString());
     dateChooser.setToolTipText(RecordTable.getValueAt(SelectedRows, 3).toString());
     Tdescription.setText(RecordTable.getValueAt(SelectedRows, 4).toString());
     Tother.setText(RecordTable.getValueAt(SelectedRows, 5).toString());
      
}
      
});
  table.addMouseListener(new MouseAdapter() {
  public void mouseClicked(MouseEvent evt) {
  mouseClicked(evt); }
});
      }
                      }

Upvotes: 0

Views: 356

Answers (1)

Anonymous
Anonymous

Reputation: 86232

java.time and JDBC 4.2

The code below has not been tested, but I think that it works. I basically recommend that you use java.time, the modern Java date and time API, for your date work. JDBC 4.2, which is part of Java SE 8, fully supports java.time types.

Get date from your JDateChooser. Unfortunately JDateChooser is old and not upgraded to java.time, so only supports the poorly designed and long-outdated classes Date and Calendar.

Date oldfashionedDateObject = dateChooser.getDate();

Convert to a modern LocalDate. A LocalDate is a date without time of day and without a timezone.

LocalDate chosenDate = oldfashionedDateObject.toInstant()
        .atZone(ZoneId.systemDefault())
        .toLocalDate();

Pass the date to your prepared statement:

pst.setObject(3, chosenDate);

Neither in Java nor in your database process nor keep your dates as strings. Use date in SQL and LocalDate in Java. This relieves you of all concern about the format of the date. Neither any parsing nor any formatting is called for.

What went wrong in your code?

You called JDateChooser.getDateFormatString(). The method gives you the format string that your JDateChooser uses for formatting the date when showing to the user, in your case d. MMM y. Not the chosen date itself. So you get the same string no matter which date the user chooses. Next, you try to save the string d. MMM y as a date to the database. Since this is not a valid date by any measure, saving fails with the exception you saw.

Links

Upvotes: 2

Related Questions