spring data and ORA-00942: table or view does not exist

Viewed 11057

I have funny issues with Spring Data and Oracle 12 database

I got

java.sql.SQLSyntaxErrorException:
ORA-00942: table or view does not exist

but it works when I use JdbcTemplate! Spring data and JdbcTemplate use the same dataSource. Liquibase migrations also work without problem

I've tried to use schema for models but no luck.

@Getter
@Setter
@Entity
@Table(name = "tb_accounts", schema = "rx")
public class CustomerAccount {

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

   private String accountNo;
}

@Repository
public interface CustomerAccountRepository extends JpaRepository<CustomerAccount, String> {

}

But as I said it works with JdbcTemplate

@Repository
public class CustomerAccountDao {

    @Autowired
    private DataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    private void postConstruct() {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List<CustomerAccount> findAll() {
    return jdbcTemplate.query("select * from tb_accounts", (rs, i) -> {

        CustomerAccount account = new CustomerAccount();
        account.setId(rs.getString("id"));

        return account;
    });
}

Migration file

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">

    <changeSet id="1" author="user1">
        <createTable tableName="tb_accounts">
            <column name="id" type="VARCHAR(256)">
                <constraints primaryKey="true"/>
            </column>
            <column name="accountNo" type="VARCHAR(256)"/>

        </createTable>

    </changeSet>

</databaseChangeLog>

Any ideas? What should I check? I spent hours to find the reason but nothing helped :(

2 Answers

It needs to use TB_ACCOUNTS instead tb_accounts...

  1. 1)You will use the table annotation if you already have a ready-made table in your database, otherwise, if there is no table in the database with the name you declared in the annotation, you will encounter the following error.
  2. This error is not an error that will be big, if you give the value of spring.jpa.hibernate.ddl-auto to none in your project and run your project 2 times, you will not get the error anymore.
  3. For example, I created a project in Spring boot and created an Entity class there, and by adding @Table annotation on it, I write a table name that is not yet in my database. When I run the project you will get the error you encountered.
  4. 4.If I then prevent it from recreating the table in the database in the application.properties section and run the project again, I will not encounter this error.
Related