Hibernate Table or view does not exist Oracle

Question

I Have a problem executing an hibernate query on Oracle:

my persistence.xml:

<description>ProfileEntityManager</description>
<class>com.entity.User</class>  
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
    <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
    <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@XXX:9999/db"/> 
    <property name="javax.persistence.jdbc.user" value="user"/> 
<property name="javax.persistence.jdbc.password" value="password"/> 
    <property name = "hibernate.show_sql" value = "true" />
</properties>

my entity class com.entity.User:

package com.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name ="USER", schema = "SCHEMA")
public class User{

    @Id
    @Column(name = "USER_ID")
    private String userId;
    @Column(name = "FIRST_NAME")
    private String firstName;
    @Column(name = "LAST_NAME")
    private String lastName;
    
    public String getUserId() {
        return userId;
    }
    public void setUserId(String userId) {
        this.userId = userId;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    
}

my java class:

        //System.out.println(uid);
        List<User> lp = null;
        try {
            lp = em.createNativeQuery("SELECT iu FROM User iu WHERE iu.userId = ?", User.class)
                    .setParameter(1, uid).getResultList();
        } catch (Exception e) {
            System.out.println(e.getMessage());
            writeLog(fw, e.getMessage());
        }

but when i execute my code, i obtain:

Hibernate: SELECT iu FROM User iu WHERE iu.userId = ?
gen 29, 2021 6:36:59 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 942, SQLState: 42000
gen 29, 2021 6:36:59 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-00942: tabella o vista inesistente

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Can you help me to find the error?

NOTE: if a use a PreparedStatement with the same connections and query i don't have any issue, so it is not a problem of permissions

Answer 1

You try to use HQL query with EntityManager.createNativeQuery method. This is a mistake.

Try to correct your query in this way:

em.createNativeQuery("SELECT * FROM SCHEMA.USER WHERE USER_ID = :usrid", User.class)
.setParameter("usrid", uid)
.getResultList();

If you use the SCHEMA for all your entities, you can put it to the hibernate config:

<property name="hibernate.default_schema" value="SCHEMA"/>

then correct your entity definition in the following way:

@Entity
@Table(name ="USER")
public class User {
   // ...
}

and then correct your query:

em.createNativeQuery("SELECT * FROM {h-schema}USER WHERE USER_ID = :usrid", User.class)
.setParameter("usrid", uid)
.getResultList();

P.S. And by the way, I would suggest you to correct your hibernate dialect definition. You should use as specific dialect as possible. For example if you use Oracle 10g, you should use org.hibernate.dialect.Oracle10gDialect. You can still use org.hibernate.dialect.Oracle9iDialect but some new features defined in Oracle10gDialect will not be available.

Answer 2

Seems like you need to add this line to your configuration file

either

<property name="hbm2ddl.auto">create</prop> 

or

<property name="hbm2ddl.auto">update</prop>