Avoid insert 'null' values to database table via JPA

how to avoid inserting duplicate records in mysql
how to avoid inserting duplicate records in mysql using java
how to avoid inserting duplicate records in mysql using python
how to avoid inserting duplicate records in sql
how to avoid inserting duplicate records in sql server c#
insert ignore
how to avoid inserting duplicate records in sqlite in android
how to avoid inserting duplicate records in postgresql

im starting with JPA2 and feel quite comfortbale so far. But I have a problem when persisting Entities with null property values for NON NULL database fields with default value.

I would like to be able to leave the entity property null and let the database insert the default value.

My current setup is openJPA with PostgreSQL.

I have this VERSION database table (Vorgabewert = Default value):


     Spalte     |             Typ             |         Attribute
----------------+-----------------------------+----------------------------
 status_        | smallint                    | not null Vorgabewert 0
 time_          | timestamp without time zone | not null
 system_time    | timestamp without time zone | not null Vorgabewert now()
 version        | character varying(20)       | not null
 activationtime | timestamp without time zone |
 importtime     | timestamp without time zone |

I have an entity (Java DTO) which maps the database fields (except 'status') by xml configuration.

I hoped I could insert an entity without the system_time set and expected that the database will fill the current time as default value.

JPA constructs the following SQL-Query:

INSERT INTO public.version (version, activationtime, importtime, system_time, time_) VALUES (?, ?, ?, ?, ?) [params=?, ?, ?, ?, ?]

and Postgres reacts with:

FEHLER: NULL-Wert in Spalte »system_time« verletzt Not-Null-Constraint (sorry for German language but this message means the Not-Null-Constraint violation on 'system_time').

So what can I do? Is this a JPA or Database Problem. Can I configure JPA to exclude null properties from the INSERT SQL Statement.

I want to have the ability to set the 'system_time' in my entity or to let it be 'null' and let the database put the default value.

Any help is welcome!

Regads Klaus

I would not rely on default values in the database in conjunction with JPA. You would have to read the entity back after the insert otherwise you have a mismatch between the entity state and the db state.

Choose the pragmatic approach here and initialise all values in java. Never heard of a way to tell JPA/Hibernate to leave out null values in an insert/update.

MySQL - Handling Duplicates, Most of the times it is allowed but sometimes it is required to stop duplicate in a table normally causes an error to occur if you insert a record into the table that  To avoid inserting duplicate rows in MySQL, you can use UNIQUE(). The syntax is as follows − ALTER TABLE yourTableName ADD UNIQUE(yourColumnName1,yourColumnName2,N); To understand the above syntax, let us create a table. The query to create a table is as follows −

Using the annotation

@Column(insertable = false)

will prevent the value being generated in the sql.

Things To Avoid, Episode 1: INSERT IGNORE, Forcefully ignoring errors is never a good idea. Your code may be triggered twice for the same action and that can lead to duplicated INSERT  The solution for this problem would be to create an additional row that represents the hash of the entry, and lets call it hash, and this hash would play the role of a unique key, so when we try to insert the entry, we add its hash to it, and try to insert, if the operation goes through, i.e Mysql doesn’t throw any exception, then the entry

In the Annotation @Column put the atribute insertable to false like this:

`@Column(name="system_time", insertable = false)`

Or if you need check when the value is NULL then make a Trigger BEFORE INSERT on the table.

How to Get Microsoft Word to Stop Deleting as You Type, Press the "Ins" key to toggle overtype mode off. Depending on your keyboard model, this key may also be labeled "Insert." If you simply want to disable overtype mode but keep the ability to toggle it back on, you are done. Check if a row already exists before trying to insert it. If the person initiating the INSERT can't do that, then have your application do it. You can use a MERGE statement to check in a key already exists, and only insert the new row if it doesn't.

From documentation : columnDefinition : The SQL fragment that is used when generating the DDL for the column.

By using columnDefinition, you can specify constraints as required.

   @Column(name="COLUMN_NAME", 
   columnDefinition="DATE DEFAULT CURRENT_DATE",table="TABLE_NAME")

Else you can try to initialize field in entity itself to get rid of this.

    @Column(name = "somedate", nullable = false)
    private Date someDate = new Date();

So by default current date will be inserted if you do not set it.

INSERT IGNORE, Normally INSERT stops and rolls back when it encounters an error. By using the IGNORE keyword all errors are converted to warnings, which will not stop inserts​  create trigger t23 on studies after insert, update, delete as begin REFERENCING NEW ROW NewStudent FOR EACH ROW WHEN (30 <= (SELECT SUM(credits) FROM Studies) DELETE FROM NewStudent N WHERE N.spnr = NewStudent.spnr end. I'm trying to create a trigger which only inserts a student if the credits is < or == to '30'.

I found out a simple solution: define a default value in the pojo.

@Column(name="system_time")
private Date systemTime = new Date();

SQL Performance Best Practices, Use multi-row INSERT statements for bulk inserts into existing tables Use indexes for faster joins; Drop unused indexes; Avoid indexes on sequential keys. begin transaction insert into A values(1) insert into A values(2) commit begin transaction update A set PK=7 where PK=1 Session 2: begin transaction update A set pk=9 where pk=2 update A set pk=8 where pk=1 Session 1: update A set pk=9 where pk=2 Deadlock!

Using INSERT IGNORE with MySQL to prevent duplicate key errors , An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents  How to prevent insert of duplicated records SQL server using C# windows application want to prevent duplication in records using C# with the help of code How to prevent duplicate data when insert to datagridview VB net

MySQL INSERT IGNORE Statement Explained By Examples, clause is an extension of MySQL to the SQL standard. I need to prevent inserting duplicated records in a sql table and if exists show message says already exists. this is my button event : private void button2_Click(object sender, EventArgs e)

How to prevent insert duplicate id in sql server, How can we avoid inserting duplicate records in Oracle using Java? You have multiple options to avoid duplicate insertion of data into Database. Option 1: Make the Username field as Primary Key/ Unique Key, so the same username data will not be inserted, which in turn throws an exception. You handle the appropriate exception and intimate the user.

Comments
  • Good point - I hoped persisting the object and bringing it to the 'managed' state would include a kind of read back operation.
  • Accepted this answer because of the important "state mismatch" note. The answer also points out, that there is no way to do the thing I would linke to do :-(.
  • +1 for the tip - but this also prevents the value being generated if NOT null. In this case I would like to have the value in the SQL statement. Seems to be no way to achieve this behaviour..
  • Ah, I see. There is no way to do this. You have to decide whether the Java or the DBMS is going to be responsible for times, you can't have a mixture of both.
  • Could you have two entities mapped to the same table, one of which defined the column as insertable=false, and the other didn't, the control autogeneration by using one or the other? I ask this only out of theoretical interest - this would obviously be a fundamentally insane thing to do.
  • I would hope Hibernate has sanity checks for that sort of thing, though I've never tried it. Perhaps if you used two entirely separate Hibernate instances at the same time...
  • There's also updatable = false which removes the column from your update statements. Not what the OP was asking about but related
  • As you quoted this definition is only used when generating the DDL for the column (for table creation). Thus it has no effect for null values in SQL Statements when running the application (I still get the violation when trying to insert a NULL in this column). This happends also with plain SQL so I am looking for a configuration way in JPA to exclude columns from generated (INSERT) SQL Statements when they are null.
  • A default value will still not help because that is only used when the column is not explicitely listed in the INSERT INTO clause
  • Initializing a default value will definitly help - but in the case some process sets the value back to NULL the insert will fail.
  • you can configure that in setter-method in entity, setting value only if it is not null.
  • Is it possible to specify constraint name in column definition or some other way. if yes please specify. Thanks
  • The INSERT statment is generated by the JPA EntityManager when persisting the entity (javax.persistence.EntityManager.persist(Object)). How can I configure JPA to leave out the column if null?
  • Not sure about JPA, but Hibernate supports an annotation "dynamicInsert = true, dynamicUpdate = true" which will not send any non-changed rows.
  • Hibernate also supports "insert='false'" and "update='false'" on a property mapping to omit certain columns from the static insert/update SQL generated. Mapping "insert='false' generated='insert'" may be appropriate here (not sure how to translate that to annotation configuration though).