Hibernate, Spring, PostgreSQL - column index is out of range

Question

I have problem with persisting simple data structure into database.

Each message could has multiple message receivers. Everything I need is to save in database Message and MessageReceivers (MR). MR has column named fk_message_id which should be filled with message_id (M) automatically.

In database (PostgreSQL) tables are created with SQL code:

CREATE TABLE public.message
(
 message_id integer NOT NULL DEFAULT nextval('message_message_id_seq'::regclass),
 fk_author_id integer NOT NULL,
 topic text NOT NULL,
 text text NOT NULL,
 audit_cd timestamp without time zone NOT NULL DEFAULT now(),
 audit_md timestamp without time zone,
 CONSTRAINT message_pkey PRIMARY KEY (message_id)
)

CREATE TABLE public.message_receiver
(
 fk_message_id integer NOT NULL,
 fk_user_id integer NOT NULL,
 is_read boolean NOT NULL,
 read_date timestamp without time zone,
 audit_cd timestamp without time zone NOT NULL DEFAULT now(),
 autid_md timestamp without time zone,
 CONSTRAINT message_receiver_pkey PRIMARY KEY (fk_message_id, fk_user_id),
 CONSTRAINT message_receiver_fk_message_id_fkey FOREIGN KEY (fk_message_id)
  REFERENCES public.message (message_id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE
)

Message.java

@Entity
@Table(name="message")
public class Message implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="message_message_id_seq")
@SequenceGenerator(name="message_message_id_seq", sequenceName="message_message_id_seq", allocationSize=1)
@Column(name="message_id")
private Long id;

@NotNull
@Column(name="fk_author_id")
private Long author;

@OneToMany
@Cascade(CascadeType.PERSIST)
@JoinColumn(name="fk_message_id", nullable = false)
private List<MessageReceiver> receivers;

@NotNull
@Column(name="topic")
private String topic;

@NotNull
@Column(name="text")
private String text;

@NotNull
@Column(name="audit_cd")
@Convert(converter=PersistentLocalDateTime.class)
private LocalDateTime sendDate;

...getters, setters, constructors...
}

MessageReceiver.java

@Entity
@Table(name="message_receiver")
public class MessageReceiver implements Serializable {

private static final long serialVersionUID = 2L;

@Id
@Column(name="fk_message_id")
private Long messageId;

@Id
@Column(name="fk_user_id")
private Long receiverId;

@NotNull
@Column(name="is_read")
private Boolean isRead;

@Column(name="read_date")
@Convert(converter = PersistentLocalDateTime.class)
private LocalDateTime readDate;

...getters, setters, constructors...
}

When I try to save message with receivers I get:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not insert: [com.example.foldermessage.model.MessageReceiver]; SQL [insert into message_receiver (is_read, read_date, fk_message_id, fk_user_id) values (?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not insert: [com.example.foldermessage.model.MessageReceiver]] with root cause

org.postgresql.util.PSQLException: The column index is out of range: 5, number of columns: 4.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:68) ~[postgresql-9.4.1211.jar:9.4.1211]
at org.postgresql.core.v3.SimpleParameterList.setNull(SimpleParameterList.java:157) ~[postgresql-9.4.1211.jar:9.4.1211]
at org.postgresql.jdbc.PgPreparedStatement.setNull(PgPreparedStatement.java:287) ~[postgresql-9.4.1211.jar:9.4.1211]
at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:61) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:252) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.ComponentType.nullSafeSet(ComponentType.java:343) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrateId(AbstractEntityPersister.java:2636) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2604) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2883) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3386) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:560) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:434) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]

I use JpaRepository to perform saving operation:

@Repository
public interface MessageRepository extends JpaRepository<Message, Long> {}

Insert query seems ok and I can't see any error in it.

I also have tried change MR entity ids into composite key with @IdClass annotation and @PrimaryKeyJoinColumn. Those attempts didn't help.

None of your tables has only 4 columns. Are you sure your database schema matches your class structure? (When was the last time you executed the script that creates your database?) — Mike Nakis
– Mike Nakis, Commented Sep 25, 2016 at 13:30
@MikeNakis sql queries that I put at the beginning of the post were copied straight from PgAdmin. Does class structure have to be identical to database table structure? In classes I haven't put audit_md columns. — mateusz
– mateusz, Commented Sep 25, 2016 at 18:25
Yes, class structure and database schema must match, otherwise it is to be expected that hibernate will be trying to access columns that do not exist in the database. Look up the hbm2ddl property of hibernate which allows you to have your database schema automagically created from your class structure. If you don't want to (or cannot) do this, you can also use hbm2ddl to at least have hibernate verify that your class structure agrees with your database schema during program startup, in order to avoid nasty surprises. — Mike Nakis
– Mike Nakis, Commented Sep 25, 2016 at 18:34
@MikeNakis I added missing columns to java classes. Now I get same error but with different numbers. Query: insert into message_receiver (audit_cd, is_read, autid_md, read_date, fk_message_id, fk_user_id) values (?, ?, ?, ?, ?, ?) Error: org.postgresql.util.PSQLException: The column index is out of range: 7, number of columns: 6. — mateusz
– mateusz, Commented Sep 26, 2016 at 6:21
Okay, so then obviously, the actual postgres table does not contain the columns audit_cd, is_read, autid_md, read_date, fk_message_id, fk_user_id. Make it contain those columns. — Mike Nakis
– Mike Nakis, Commented Sep 26, 2016 at 6:28

mateusz · Accepted Answer · 2016-09-28 05:45:32Z

I found temporary solution.

First I enabled DDL spring.jpa.hibernate.ddl-auto=create (or update) to generate schema automatically.
In previous solution message_receiver has primary key fk_message_id, fk_user_id. Main purpose of this composite key was to prevent sending same message twice or more times to the one user.

I decided to put generated value instead of composite key. Constraint about message uniqueness could be realized by adding UNIQUE CONSTRAINT on PostgreSQL level.

MessageReceiver.java

@Entity
@Table(name="message_receiver")
public class MessageReceiver implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column(name="message_receiver_id")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="message_receiver_id_seq")
@SequenceGenerator(name="message_receiver_id_seq", sequenceName="message_receiver_id_seq", allocationSize=1)
private Long messageReceiverId;

@NotNull
@Column(name="fk_message_id")
private Long messageId;

@NotNull
@Column(name="fk_user_id")
private Long receiverId;

@NotNull
@Column(name="is_read")
private Boolean isRead;

@Column(name="read_date")
@Convert(converter = PersistentLocalDateTime.class)
private LocalDateTime readDate;

..getters, setters..

Message.java

@Entity
@Table(name="message")
public class Message implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="message_message_id_seq")
@SequenceGenerator(name="message_message_id_seq", sequenceName="message_message_id_seq", allocationSize=1)
@Column(name="message_id")
private Long id;

@NotNull
@Column(name="fk_author_id")
private Long author;

@OneToMany(fetch=FetchType.LAZY, cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@JoinColumn(name="fk_message_id")
private List<MessageReceiver> receivers = new ArrayList<>();

@NotNull
@Column(name="topic")
private String topic;

@NotNull
@Column(name="text")
private String text;

@NotNull
@Column(name="audit_cd")
@Convert(converter=PersistentLocalDateTime.class)
private LocalDateTime sendDate;

..getters, setters..

Now, I'm curious why first solution doesn't work and how to make it work. I tried mix of @PrimaryKeyJoinColumn, @IdClass, @OneToMany(mappedBy), @JoinColumn(nullable, insertable, updatable).

When I enable DDL (create) Hibernate generate same table as I put at the beginning. It means that it isn't problem with inappropriate table structure.

If changing strategy of generating primary key helped then probably saving composite key caused errors. In logs I often saw that Hibernate add to entity *_IdBackref. Maybe it try save/map this id into database but the column for it isn't prepared.

I'll post final solution if I'll find it.

Collectives™ on Stack Overflow

Hibernate, Spring, PostgreSQL - column index is out of range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related