Spring JPA - "Invalid column index" when inserting in DB

Question

I am facing an issue when trying to insert 1 record in a parent table (DART_ORDER) and 2 records in a child table connected to the parent (DART_ODATE). Those 2 tables are linked by a OneToMany-ManyToOne relation as follows:

@Getter
@Setter
@ToString
@NoArgsConstructor
@Accessors(chain = true)
@Entity
@Table(
    name = "DART_ORDER",
    schema = Constants.ORACLE_DB_SCHEMA
)
public class OrderDAO implements Serializable {

  @NotNull
  private String WHY;

  @NotNull
  private String WHO;

  @Id
  @NotNull
  private String ORDERID;

  private String DESCRIPTION;

  @NotNull
  private String STATUS;

  @NotNull
  private String COUNTRY;

  @NotNull
  private String TYPE;

  @NotNull
  private String REPETITION;

  private String REF_REFORDERID;

  @OneToMany(mappedBy = "ORDER", cascade = CascadeType.ALL)
  @OrderBy("DID ASC")
  @ToString.Exclude
  private List<OrderDateDAO> DATES;

  @OneToMany(mappedBy = "ORDER", cascade = CascadeType.ALL)
  @OrderBy("DID ASC")
  @ToString.Exclude
  private List<OrderDocDAO> DOCS;

}

@Getter
@Setter
@ToString
@NoArgsConstructor
@Accessors(chain = true)
@Entity
@Table(
    name = "DART_ODATE",
    schema = Constants.ORACLE_DB_SCHEMA,
    indexes = @Index(
        name = "PK_DART_ODATE",
        columnList = "REF_ORDERID, ODTTYPE",
        unique = true
    )
)
@IdClass(OrderDateDAO.class)
public class OrderDateDAO implements Serializable {

  @NotNull
  private String WHY;

  @NotNull
  private String WHO;

  @Id
  @NotNull
  private String REF_ORDERID;

  @Id
  @NotNull
  private String ODTTYPE;

  @NotNull
  private LocalDate ODATE;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "REF_ORDERID", referencedColumnName = "ORDERID", insertable = false, updatable = false)
  @ToString.Exclude
  private OrderDAO ORDER;

}

When I try saving a record giving a complete OrderDAO object, I get the following exception:

Caused by: java.sql.SQLException: Invalid column index at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:10470) at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:10451) at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5240) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:255) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java) at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$1.doBind(VarcharTypeDescriptor.java:46) at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271) at org.hibernate.type.ComponentType.nullSafeSet(ComponentType.java:340) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrateId(AbstractEntityPersister.java:3121) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:3079) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3372) ... 87 more

By enabling the logs for Hibernate's SQL statements, I noticed that the insert one for the DART_ODATE table has the correct number of ? symbols as parameters, but, for no apparent reason, the log immediately below shows 7 parameters passed, instead of 6, among which 1 seems to be null.

2022-05-03T13:27:24,074 TRACE [http-nio-8080-exec-5] o.h.t.d.s.BasicBinder: binding parameter [4] as [VARCHAR] - [null]

I don't honestly know why it keeps considering 1 parameter in addition, since the only additional mapping set in the DAO is the JoinColumn one... Besides, I already tried upgrading both my SpringBoot and Hibernate Validator versions to the latest ones, but I still got the error.

Has any of you experienced the same issue?

Thank you.

Regards, A.M.

Hi! Why is that? That parameter could be null, since no DOCS could be lined to the parent DAO. Besides, looking a the logs, the error is encountered when the first INSERT statement for DART_ODATE is processed. — AlexKibo88
– AlexKibo88, Commented May 3, 2022 at 11:50
org.springframework.orm.jpa.JpaSystemException: could not insert: [eu.unicredit.dtm.dtm_be.dao.oracle.OrderDateDAO]; nested exception is org.hibernate.exception.GenericJDBCException: could not insert: [eu.unicredit.dtm.dtm_be.dao.oracle.OrderDateDAO] — AlexKibo88
– AlexKibo88, Commented May 3, 2022 at 11:52

AlexKibo88 · Accepted Answer · 2022-05-03 12:46:12Z

0

I finally managed to understand my mistake!

The issue was related to a wrong use of the JoinColumn annotation. Basically, I removed the ORDER attribute within the *OrderDateDAO", and changed the parent DATES attribute as follows:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "REF_ORDERID", referencedColumnName = "ORDERID")
@ToString.Exclude
private List<OrderDateDAO> DATES;

Using a uni-directional relation, the INSERT statement worked like a charm! :-)

answered May 3, 2022 at 12:46

AlexKibo88

8271 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring JPA - "Invalid column index" when inserting in DB

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