Using DB Views

[hyder]

Hi,

My apologies if this has been asked before. I have a view in my database and I created a Model (sorry for the cryptic name and fields):

@Entity

@Table(name="MYVIEWNAME")

public class MyModel extends Model {

@Id

public String idfield;

@Column(name="aname")

public String aname;

@Column(name="anumber")

public int anumber;

@Column(name="date1")

public Date date1;

@Column(name="date2")

public Date date2;

@Column(name="anint")

public int anint;

public MyModel(String idfield, String aname, int anumber,

Date date1, Date date2, int anint) {

this.idfield = idfield;

this.aname = aname;

this.anumber = anumber;

this.date1 = date1;

this.date2 = date2;

this.anint = anint;

}

}

Among others, I need to run this query: SELECT aname, min(anint), max(anint) FROM MYVIEWNAME GROUP BY aname. I tried this just to test:

List<MyModel> = MyModel.findAll()

but got a NoSuchFieldError. I also tried various suggestions on this forum and the JPASupport page but haven't been able to resolve this.

Please advise.

Thank you.

[hyder]

Hi,

Any ideas on this? I've tried various approaches but they didn't work for one reason or another.

Thanks a lot in advance.

[notalifeform]

Hi There,

To JPA db views are not different than normal tables, so that should not be the problem.

What I do notice is that that you define the @Id of the class, but are also extending the Model, which already provides the id field (as Long), You should either extend GenericModel or remove the @Id field. Could you provide a complete stackstrace of the issue?

regards,

Robert

[Olivier Refalo]

What Database are you using ?

With some vendors Views are READ only. Hence creating an @id will fail.

I believe hibernate has a readonly annotation from the transaction.

Olivier

[hyder]

It's an Oracle DB. I'll also try Robert's suggestion this evening.

[hyder]

Ok so I tried a few things, starting with Robert's. I'll then come to the errors I'm now getting but before that, I wanted to make sure I got the types of my fields correct.

I first created a separate JPA project (in Eclipse), created an Entity from Tables and selected my view. The only changes were on the dates (changed to TemporalType.Date and 2 of the ints which became BigDecimal.

In my controller, I then do:

    public static void index(){
    List<MyModel> models = MyModel.findAll();

    //use renderArgs to add the models and then display
    .....
    }
 
I then start play:

16:16:06,978 WARN  ~ SQL Error: 1702, SQLState: 42000
16:16:06,978 ERROR ~ ORA-01702: a view is not appropriate here

16:16:06,979 ERROR ~ could not complete schema update
org.hibernate.exception.SQLGrammarException: could not get table metadata: MYVIEWNAME
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:128)
    at org.hibernate.cfg.Configuration.generateSchemaUpdateScript(Configuration.java:1030)
    at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:187)
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:386)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1385)
    at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:954)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:892)
    at play.db.jpa.JPAPlugin.onApplicationStart(JPAPlugin.java:225)
    at play.Play.start(Play.java:426)

I also notice this: Caused by: java.sql.SQLException: ORA-01702: a view is not appropriate here

Then, when I try to access the page  where I want to display the result:

NoSuchFieldError occured : NONE

play.exceptions.JavaExecutionException: NONE
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:285)
    at Invocation.HTTP Request(Play!)
Caused by: java.lang.NoSuchFieldError: NONE
    at org.hibernate.ejb.QueryImpl.<init>(QueryImpl.java:604)
    at org.hibernate.ejb.QueryImpl.<init>(QueryImpl.java:79)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:268)
    at play.db.jpa.JPQL.findAll(JPQL.java:30)
    at models.MyModel.findAll(MyModel.java)

The error highlighted in the browser is this:

List<MyModel> models = MyModel.findAll();

Please advise.

[hyder]

I should add that after generating the classes in the separate JPA project, I just copied it over to my models pacakge and then extended the play.db.jpa.Model in my play app.

[notalifeform]

Hi,

Your first problem appears to be Play! (JPA actually) trying to change to view. In dev mode play by default tries to update the db-schema according to your model. You can stop this by make sure your application.conf contains

jpa.ddl=none

The fact that update action fires queries suggest that your model doesn't properly represent your database view.

I'm not sure about the other issue (NoSuchFieldError occured : NONE)  - it might be caused by the earlier update issue, but I'm not sure. I googled a bit and it seems that this error can also be caused by including the wrong libraries in your codebase. I guess you have a clean play! project without any additional libraries?

If your problem persists after fixing the update-issue, maybe you can publish you test-project somewhere, so we can take a look.

regards,

Robert

[hyder]

Hi,

Thanks a lot Robert. Adding jpa.ddl to none solves the first problem. But the 2nd problem (NoSuchFieldError:NONE). I guess I'll have to restart with a clean database.

If that still doesn't work, then I'll have to find a way to share this part of the code as this is an integration project and it's not possible to run it standalone.