Home > Software design >  Spring JPA H2 database get org.h2.jdbc.JdbcSQLSyntaxErrorException Table not found
Spring JPA H2 database get org.h2.jdbc.JdbcSQLSyntaxErrorException Table not found

Time:04-12

I have a Spring boot Entity defined as :

@Data
@Entity
@Table(name = "TaxOffice")
public class TaxOffice {

    public TaxOffice(){}

    public TaxOffice(int id, String name, int voivodeship_id){
        this.id = id;
        this.name = name;
        this.voivodeship_id = voivodeship_id;
      
    }

    @Id
    private int id;

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

    @Column(name="voivodeship_id")
    private int voivodeship_id;

    @ManyToOne
    @JoinColumn(name = "city_id")
    private City city;

    @OneToOne
    @JoinColumn(name = "details_id")
    private TaxOffice_Detail taxOffice_details;
}

In application-test.properties, I have following settings:

spring.datasource.url=jdbc:h2:mem:TestDB;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.defer-datasource-initialization=true

When I run this Test

    @Test
    void  findAllByCity_idTest(){
        assertEquals(1, taxOfficeService.findAllByCity_id(48).size());
    }

i recieve this error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: 
Table "TAX_OFFICE" not found; SQL statement:
/* select t from TaxOffice t where t.city.id = :id */ select taxoffice0_.id as id1_1_, taxoffice0_.city_id as city_id4_1_, taxoffice0_.name as name2_1_, taxoffice0_.details_id as details_5_1_, taxoffice0_.voivodeship_id as voivodes3_1_ from tax_office taxoffice0_ where taxoffice0_.city_id=? [42102-210]

There is no Table "TAX_OFFICE", but there is "TaxOffice", so why is it looking for "TAX_OFFICE"? Why is this happening and how can i fix this?

Edit: TaxService.java

@Transactional
@Service
public class TaxOfficeService {

    @Autowired
    TaxOfficeRepository taxOfficeRepository;

    public List<TaxOffice> findAllByCity_id(int id){
        return taxOfficeRepository.findAllByCity_id(id);
    }

}

TaxOfficeRepository

@Repository("taxOfficeRepository")
public interface TaxOfficeRepository extends JpaRepository<TaxOffice,Integer> {

    @Query("select t from TaxOffice t where t.city.id = :id")
    List<TaxOffice> findAllByCity_id(int id);
}

CodePudding user response:

Hibernate and Spring by default having naming strategies, which decide how the entity class must be compiled and the table and column names be generated. This can be customized as per use through application properties or hibernate configuration file.

eg

spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
  • Related