CODEWITHSUNDEEP
sqlpostgresqljpa-2.0sql-insertnative-sql
user652158
user652158

Reputation:

Cannot insert NULL values into database

We use JPA to load/persist our entities in a prostges database. In one case i must persist a entity with a native query. Heres is the code:

String sql = "INSERT INTO recording (id,creationdate,duration,endtime,filemaninfocomplete,lastupdated,packagesdeletetime,rcinfocomplete,"
                + "rcrecordingkept,recordstate,recordingtype,starttime,tenantid,recordingnode_id,transcription_id) "
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, recording.getId());
query.setParameter(2, new Date(), TemporalType.TIMESTAMP);
query.setParameter(3, recording.getDuration());
query.setParameter(4, recording.getEndtime(), TemporalType.TIMESTAMP);
query.setParameter(5, recording.isFileManInfoComplete());
query.setParameter(6, new Date(), TemporalType.TIMESTAMP);
query.setParameter(7, recording.getPackagesDeleteTime(), TemporalType.TIMESTAMP);
query.setParameter(8, recording.isRcInfoComplete());
query.setParameter(9, recording.isRcRecordingKept());
query.setParameter(10,recording.getRecordState() != null ? recording.getRecordState().toString() : RecordState.DEFAULT);
query.setParameter(11,recording.getRecordingType()!= null ? recording.getRecordingType().toString() : null);
query.setParameter(12,recording.getStarttime(), TemporalType.TIMESTAMP);        
query.setParameter(13,recording.getTenantId());        
query.setParameter(14,recording.getRecordingNode() != null ? recording.getRecordingNode().getId() : null);        
query.setParameter(15, recording.getTranscription() != null ? recording.getTranscription().getId() : null);        
query.executeUpdate();

The insert works finde unless "getPackagesDeleteTime" returns null. In this case the following message ist shown:

Caused by: org.postgresql.util.PSQLException: FEHLER: Spalte »packagesdeletetime« hat Typ timestamp without time zone, aber der Ausdruck hat Typ character varying
  Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen.
  Position: 252

(ie:

ERROR: column "packagesdeletetime" has type timestamp without time zone, but the expression is of type character varying
Hint: You must rewrite the expression or typecast.

)

Generated Statement looks like:

INSERT INTO recording(id,creationdate,duration,endtime,filemaninfocomplete,lastupdated,packagesdeletetime,rcinfocomplete,rcrecordingkept,recordstate,recordingtype,starttime,tenantid,recordingnode_id,transcription_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
bind => [0c850391-cae9-4ac2-9381-b252167fa239, 2013-04-18 08:38:00.704, 20240, 2013-04-18 08:37:24.131, false, 2013-04-18 08:38:00.704, null, false, true, DEFAULT, VOICE, 2013-04-18 08:37:03.891, f56251a7-44df-4151-aa0d-7c3fc538f621, null, null]

Some tips how i could solve the problem?

Upvotes: 2

Views: 2381

Answers (1)

Eelke
Eelke

Reputation: 22033

The corresponding field in the entity has to be annotated with:

@Column(nullable=true)

Also check the corresponding column in your table allows NULL (has no NOT NULL constraint). If this doesn't help please post the code of your entity.

Upvotes: 1

Related Questions