Trying to insert ID and getting "cannot accept a NULL value"

Question

I am trying to insert record in Derby database with auto generated key and I am getting this error:

Exception in thread "main" java.sql.SQLIntegrityConstraintViolationException: Column 'CUSTOMERID' cannot accept a NULL value. at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.executeUpdate(Unknown Source) at jakub.ordereditor.OrderEditorMain.create(OrderEditorMain.java:54) at jakub.ordereditor.OrderEditorMain.main(OrderEditorMain.java:39) Caused by: ERROR 23502: Column 'CUSTOMERID' cannot accept a NULL value. at org.apache.derby.client.am.ClientStatement.completeExecute(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecute(Unknown Source) at org.apache.derby.client.net.StatementReply.readExecute(Unknown Source) at org.apache.derby.client.net.NetPreparedStatement.readExecute_(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.readExecute(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.flowExecute(Unknown Source) at org.apache.derby.client.am.ClientPreparedStatement.executeUpdateX(Unknown Source) ... 3 more

I followed this answer: https://stackoverflow.com/a/1915197 and many others. My piece of code:

package jan.ordereditor;

import jan.ordereditor.customer.entity.Customer;
import jan.ordereditor.customer.entity.CustomerName;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class OrderEditorMain {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException {

        Customer customer = new Customer();

        CustomerName customerFullName = new CustomerName();
        customerFullName.setCustomerFirstName("John");
        customerFullName.setCustomerSurname("Doe");

        customer.setCustomerFullName(customerFullName);

        create(customer);

    }

    public static void create(Customer customer) throws SQLException {
        String SQL_INSERT = "INSERT INTO customer (customerFirstName, customerSurname) VALUES (?,?)";
        try (
                Connection connection = DriverManager.getConnection("jdbc:derby://localhost:1527/OrderEditor");
                PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                        Statement.RETURN_GENERATED_KEYS);) {
            CustomerName customerFullName = customer.getCustomerFullName();
            statement.setString(1, customerFullName.getCustomerFirstName());
            statement.setString(2, customerFullName.getCustomerSurname());


            int affectedRows = statement.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating user failed, no rows affected.");
            }

            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    customer.setCustomerId(generatedKeys.getInt(1));
                } else {
                    throw new SQLException("Creating user failed, no ID obtained.");
                }
            }
        }
    }

}

and my DB id in database Derby:

Make you id to Auto_increment or add it to the insert statement — Jens, Aug 22 '16 at 07:39
`id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)` is this statement present in your DDL? — Imran, Aug 22 '16 at 07:42
@Imran : No, I did not. I have just created table with adding column feature and I have not filled constraints. Now it works nice. I have followed SQL constraints you mentioned. — Jan444444, Aug 22 '16 at 08:34
And now I am wondering why does it work without defying generatedKeys? — Jan444444, Aug 22 '16 at 08:40
Sorry, I have found it out. It will work without defying generatedKeys, but If I create new record I will get "id" in my table 0 ("null"). If I implement generatedKeys I will get next id (generated automatically by my DB). — Jan444444, Sep 08 '16 at 11:56

score 2 · Answer 1 · edited Aug 22 '16 at 08:42

2

You need to remove and readd the customerid column in your table as an identity column.

Example:

ALTER TABLE TableName add customerid int identity(1,1)

Starting with 1, On every insert, value will be incremented by 1.

edited Aug 22 '16 at 08:42

svarog

9,477
4
61
77

answered Aug 22 '16 at 07:58

Dheeraj Kumar

3,917
8
43
80

Trying to insert ID and getting "cannot accept a NULL value"

1 Answers1