java application cannot insert null value into mysql database

Question

I'm making a java program using Netbeans, I want to insert data into my "data supplier" table. I cannot post my JFrame picture as my reputation is not enough.

I've set "Kode Supplier" as PRIMARY_KEY and NOT_NULL, and allow the rest to be NULL

In the code below, telpField and hpField will show an error if I didn't type anything in it's textbox

Is it possible because it is INT type?

This is my code:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         

    try{

        String sql = "INSERT INTO datasupplier (`Kode Supplier`, `Nama Supplier`, `Contact Person`,"
                + " `Alamat`, `NoTelp`, `NoHP`, `Bank Account`, `A/C Info`, `A.N.`, `Keterangan`)"
                + "  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    pst = conn.prepareStatement(sql);

    //Get value from the textboxes
    pst.setString(1, codeField.getText());
    pst.setString(2, nameField.getText());
    pst.setString(3, cpField.getText());
    pst.setString(4, addressField.getText());
    pst.setString(5, telpField.getText());
    pst.setString(6, hpField.getText());
    pst.setString(7, bankField.getText());
    pst.setString(8, acField.getText());
    pst.setString(9,anField.getText());
    pst.setString(10, ketField.getText());

    pst.execute();
    JOptionPane.showMessageDialog(null, "Tabel Telah Di Update");

    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, "Data Invalid");
    }
    DataSupplierTable();
}                                        

//Set JComboBox First Diplayed Item
private void setTableCombo(){
  tableCombo.setSelectedItem("Data Supplier");
}


//Bind the table and databarang.datasupplier
private void DataSupplierTable(){
    String sql = "SELECT * FROM datasupplier";
    try{
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        supplierTable.setModel(DbUtils.resultSetToTableModel(rs));
        supplierTable.isCellEditable(0,0);



    }catch(Exception e){

    }
}

This is my table (using MySQL Community Server Database, InnoDB)

Kode Supplier    INT(30) PRIMARY_KEY NOT_NULL,
Nama Supplier    CHAR(45),
Contact Person   VARCHAR(20),
Alamat           VARCHAR(45),
NoTelp           INT(30),
NoHP             INT(30),
Bank Account     CHAR(30),
A/C Info         VARCHAR(45),
A.N.             CHAR(45),
Keterangan       VARCHAR(100)

Answer 1

Yes, this is because your Kode Supplier, NoTelp and NoHP columns are integer columns. For integer columns, you should be using the setInt method rather than setString.

But the setInt method only accepts an primitive int for the value of the field. So the first thing you'll need to do is convert the String value of the field to int. This is done with a statement like:

int telpVal = Integer.parseInt(telpField.getText());

But this means you have to decide what to do in the following cases:

• The user entered a value in the GUI field which is not an integer, like ABC, 1.2 or 123456789123456789. If that happens, then the statement I gave would throw a NumberFormatException.

  You could decide to display an error message and not call the insert statement when this happens. Or you may decide to insert a NULL. Or you may decide to insert a default value like 0.

• The user entered no value in the GUI field - it is an empty string. Note that there is a difference between an empty string and a null. You may decide to handle this case the same way you handle the previous one. Or you may decide to handle it separately.

Suppose you decide that:

• If the user entered an illegal number, you'll show an error message and will not insert the row.
• If the user did not enter a value and left the field empty, you want to insert a null.

Then you'll need to handle it like this:

String fieldName;

try {

    String sql = "INSERT INTO datasupplier (`Kode Supplier`, `Nama Supplier`, `Contact Person`,"
            + " `Alamat`, `NoTelp`, `NoHP`, `Bank Account`, `A/C Info`, `A.N.`, `Keterangan`)"
            + "  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    pst = conn.prepareStatement(sql);

    //Get value from the textboxes

    // Kode supplier is integer, but is not allowed to be null
    // so don't handle an empty field case, just let parseInt
    // throw the exception.

    fieldName = "Kode Supplier";
    pst.setInt(1, Integer.parseInt(codeField.getText()));

    pst.setString(2, nameField.getText());
    pst.setString(3, cpField.getText());
    pst.setString(4, addressField.getText());

    // Handle the NoTelp field - if empty, insert null. If not,
    // parse the number. Handle illegal number values in catch.

    fieldName = "NoTelp";
    if ( telpField.getText().isEmpty() ) {
        pst.setNull(5, Types.INTEGER);
    } else {
        pst.setInt(5, Integer.parseInt(telpField.getText());
    }

    // Handle the NoHP field

    fieldName = "NoHP";
    if ( hpField.getText().isEmpty() ) {
        pst.setNull(6, Types.INTEGER);
    } else {
        pst.setInt(6, Integer.parseInt(hpField.getText());
    }

    pst.setString(7, bankField.getText());
    pst.setString(8, acField.getText());
    pst.setString(9,anField.getText());
    pst.setString(10, ketField.getText());

    pst.executeUpdate();
    JOptionPane.showMessageDialog(null, "Tabel Telah Di Update");
}
catch (NumberFormatException nfe) {
    // Display error to the user
    JOptionPane.showMessageDialog(null, "Invalid number in field " + fieldName)
}
catch(SQLException e){
    JOptionPane.showMessageDialog(null, "Data Invalid");
}

Notes

• I handled Kode Supplier differently than NoTelp and NoHP because it is not allowed to be null. If the field is empty, NumberFormatException will be thrown from parseInt and will go to the catch part.
• I kept a fieldName variable which I set before trying each parseInt. If an exception is thrown, we can use it for displaying the specific field where the error occurred in the dialog box. You can do other things like keeping the JTextField that you are currently handling, and in the catch highlighting it and giving it focus.
• When you use setNull, you have to pass the type of the field as the second parameter. All the types are in java.sql.Types. So remember to import java.sql.Types.
• Don't use a catch (Exception e). It's too broad. In this case we expect only NumberFormatException and SQLException. If any other exception happens, especially a runtime exception, you want to know about it and see the stack trace. If you have catch (Exception e) you'll just get a dialog box that says "Data Invalid" and that is not helpful. "Catch all" is bad.