SpringBoot JPA creating wrong JPQL and throwing org.hibernate.exception.SQLGrammarException

Question

My entities has OneToMany relation. Below is the code:

Entity Classes :

@Data
@Entity
@Table(name = "TEST_LADDER", schema = "TEST_DBO")
public class LadderEntityTemp implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "LADDER_SEQUENCE_ID", nullable = false, insertable = true)
    protected UUID ladderSequenceId;
    @Column(name = "LADDER_ID")
    protected String ladderId;
    @Column(name = "LADDER_ACCOUNT_NBR")
    protected String ladderAccountNbr;
    and more.....

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "ladderEntityTemp")
    private List<RungEntityTemp> ladderRungs;
}

@Data
@Entity
@Table(name = "TEST_LADDER_RUNG", schema = "TEST_DBO")
public class RungEntityTemp implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "LADDER_RUNG_SEQUENCE_ID", nullable = false, insertable = true, table = "")
    private UUID ladderRungSequenceId;
    @Column(name = "LADDER_SEQUENCE_ID")
    protected UUID ladderSequenceId;
    @Column(name = "LADDER_RUNG_NBR")
    protected Integer ladderRungNbr;
    and more.....

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private LadderEntityTemp ladderEntityTemp;
}

Repository Class :

@Repository
public interface LaddersRepositoryTemp extends JpaRepository<LadderEntityTemp, UUID> {
    @Query(value = "SELECT * from TEST_DBO.TEST_LADDER ld where ld.LADDER_ACCOUNT_NBR = :accountNum", nativeQuery = true)
    List<LadderEntityTemp> tempQuery(String accountNum);
}

DBConfigClass :

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.temp.repositories",
        entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef= "transactionManager")
public class DatabaseConfig {
    private static final String BASE_PACKAGE = "com.temp.model.entities";

    @Primary
    @Bean(name = "tempDatasource")
    @ConfigurationProperties(prefix = "temp.datasource")
    public DataSource tempDataSource()  {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("tempDatasource") DataSource tempDatasource) {
        Map<String,Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "none");
        properties.put("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");
        properties.put("oracle.net.encryption_client", "REQUIRED");
        properties.put("oracle.net.encryption_types_client", "( AES256 )");
        return builder.dataSource(tempDatasource).packages(BASE_PACKAGE).properties(properties).build();
    }

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

pom.xml code snippet for jpa :

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.0.8</version>
  </parent>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

When calling List<LadderEntityTemp> ladderEntityList = laddersRepository.tempQuery(accountNumber); I find in the log that JPA trying to execute below 2 queries:

Custom Query - Hibernate: SELECT * from TEST_DBO.TEST_LADDER ld where ld.LADDER_ACCOUNT_NBR = ?

A JPQL - Hibernate: select l1_0.ladderEntityTemp_LADDER_SEQUENCE_ID,l1_0.LADDER_RUNG_SEQUENCE_ID,l1_0.LADDER_RUNG_NBR,l1_0.LADDER_SEQUENCE_ID from TEST_DBO.TEST_LADDER_RUNG l1_0 where l1_0.ladderEntityTemp_LADDER_SEQUENCE_ID=?

and Getting below errors :

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper 133 : Local-1 : SQL Error: 904, SQLState: 42000 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper 138 : Local-1 : ORA-00904: "L1_0"."LADDERENTITYTEMP_LADDER_SEQUENCE_ID": invalid identifier

2024-04-25 12:10:35 ERROR com.temp.controller.LadderController 120 : Local-1 : Ladder API Error : Exception in search inventory and save model ladder request: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select l1_0.ladderEntityTemp_LADDER_SEQUENCE_ID,l1_0.LADDER_RUNG_SEQUENCE_ID,l1_0.LADDER_RUNG_NBR,l1_0.LADDER_SEQUENCE_ID from TEST_DBO.TEST_LADDER_RUNG l1_0 where l1_0.ladderEntityTemp_LADDER_SEQUENCE_ID=?] at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)

I have tried couple of options after googleing on this issue by adding below properties to stop atleast the error:

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

but nothing worked.

Questions :

1. What wrong i did in the code?
2. Why JPA JPQL creating wrong column name "LADDERENTITYTEMP_LADDER_SEQUENCE_ID"?
3. How can i stop JPA to execute the second auto generated JPQL because i don't need that data while executing the custom query.
4. Is that problem with the version of JPA? I am using spring-boot-starter-parent: "3.0.8" which seems has dependency on jakarta.persistence-api: "3.0.1".

New Code :

    @Data
    @Entity
    @Table(name = "TEST_LADDER", schema = "TEST_DBO")
    public class LadderEntityTemp implements Serializable {
        private static final long serialVersionUID = 1L;
        @Id
        @GeneratedValue(strategy = GenerationType.UUID)
        @Column(name = "LADDER_SEQUENCE_ID", nullable = false, insertable = true)
        protected UUID ladderSequenceId;
        @Column(name = "LADDER_ID")
        protected String ladderId;
        @Column(name = "LADDER_ACCOUNT_NBR")
        protected String ladderAccountNbr;
        @OneToMany(cascade = CascadeType.ALL, mappedBy = "ladderEntityTemp")
        private List<RungEntityTemp> ladderRungs;
    }
    
    
    @Data
    @Entity
    @Table(name = "TEST_LADDER_RUNG", schema = "TEST_DBO")
    public class RungEntityTemp implements Serializable {
        private static final long serialVersionUID = 1L;
        @Id
        @GeneratedValue(strategy = GenerationType.UUID)
        @Column(name = "LADDER_RUNG_SEQUENCE_ID")
        private UUID ladderRungSequenceId;
        @Column(name = "LADDER_RUNG_NBR")
        protected Integer ladderRungNbr;
        @ManyToOne(optional = false)
        @JoinColumn(name = "LADDER_SEQUENCE_ID")
        private LadderEntityTemp ladderEntityTemp;
    }
    
    @Repository
    public interface LaddersRepositoryTemp extends JpaRepository<LadderEntityTemp, UUID> {
        @Query(value = "SELECT ld from LadderEntityTemp ld where ld.ladderAccountNbr = :accountNum")
        List<LadderEntityTemp> tempQuery(String accountNum);
    }

New Error :

DEBUG org.hibernate.SQL 128 : Local-1 : select l1_0.LADDER_SEQUENCE_ID,l1_0.LADDER_ACCOUNT_NBR,l1_0.LADDER_ID from TEST_DBO.TEST_LADDER l1_0 where l1_0.LADDER_ACCOUNT_NBR=?
Hibernate: select l1_0.LADDER_SEQUENCE_ID,l1_0.LADDER_ACCOUNT_NBR,l1_0.LADDER_ID from TEST_DBO.TEST_LADDER l1_0 where l1_0.LADDER_ACCOUNT_NBR=?
DEBUG org.hibernate.SQL 128 : Local-1 : select l1_0.LADDER_SEQUENCE_ID,l1_0.LADDER_RUNG_SEQUENCE_ID,l1_0.LADDER_RUNG_NBR from TEST_DBO.TEST_LADDER_RUNG l1_0 where l1_0.LADDER_SEQUENCE_ID=?
Hibernate: select l1_0.LADDER_SEQUENCE_ID,l1_0.LADDER_RUNG_SEQUENCE_ID,l1_0.LADDER_RUNG_NBR from TEST_DBO.TEST_LADDER_RUNG l1_0 where l1_0.LADDER_SEQUENCE_ID=?

jakarta.servlet.ServletException: Handler dispatch failed: java.lang.StackOverflowError
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1096)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:974)

it keep going and then

Caused by: java.lang.StackOverflowError: null
    at java.base/java.lang.System$2.fastUUID(System.java:2320)
    at java.base/java.util.UUID.toString(UUID.java:461)
    at java.base/java.lang.StringConcatHelper.stringOf(StringConcatHelper.java:453)
    at com.fmr.fbt.fi.ladder.model.entities.RungEntityTemp.toString(RungEntityTemp.java:11)
    at java.base/java.lang.String.valueOf(String.java:4220)
    at java.base/java.lang.StringBuilder.append(StringBuilder.java:173)
    at java.base/java.util.AbstractCollection.toString(AbstractCollection.java:457)
    at org.hibernate.collection.spi.PersistentBag.toString(PersistentBag.java:585)
    at java.base/java.lang.StringConcatHelper.stringOf(StringConcatHelper.java:453)
    at com.fmr.fbt.fi.ladder.model.entities.LadderEntityTemp.toString(LadderEntityTemp.java:11)
    at java.base/java.lang.StringConcatHelper.stringOf(StringConcatHelper.java:453)

and keep going

This the new error i am getting after updating the code as suggested by Wladimir Diskowski

Answer 1

You missed JoinColumn Annotation

@Data
@Entity
@Table(name = "TEST_LADDER", schema = "TEST_DBO")
public class LadderEntityTemp implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "LADDER_SEQUENCE_ID", nullable = false, insertable = true)
    protected UUID ladderSequenceId;
    @Column(name = "LADDER_ID")
    protected String ladderId;
    @Column(name = "LADDER_ACCOUNT_NBR")
    protected String ladderAccountNbr;
    and more.....

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "ladderEntityTemp")
    @ToString.Exclude
    private List<RungEntityTemp> ladderRungs;
}

@Data
@Entity
@Table(name = "TEST_LADDER_RUNG", schema = "TEST_DBO")
public class RungEntityTemp implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "LADDER_RUNG_SEQUENCE_ID")
    private UUID ladderRungSequenceId;
    @Column(name = "LADDER_RUNG_NBR")
    protected Integer ladderRungNbr;
    and more.....

    @ManyToOne(optional = false)
    @JoinColumn(name = "LADDER_SEQUENCE_ID")
    private LadderEntityTemp ladderEntityTemp;
}


Repository
public interface LaddersRepositoryTemp extends JpaRepository<LadderEntityTemp, UUID> {
    @Query(value = "SELECT ld from LadderEntityTemp ld where ld.ladderAccountNbr = :accountNum")
    List<LadderEntityTemp> tempQuery(String accountNum);
}

And if you don't need second query you can make your OneToMany Lazy: OneToMany(fetch = FetchType.LAZY ...