Adam Bien's Weblog

Tuesday Jan 06, 2009

Mapping JPA Entities To SQL Views - It Works Even With Derby

SQL Views can be considered either as best practice or an anti-pattern. It depends on the perspective. Sometimes, however,  it is required to provide an efficient way to iterate over an excerpt or even a set of related entities, but return a different “view” to the client. This can be achieved by fetching the entities with EntityManager and merging them together inside a Session Bean (a Service). This approach is neither fast, nor easy to maintain. Especially the merging and extraction of entity data is error-prone and can become quite complex. Another possibility is the execution of more complex native SQL-statements and mapping them into existing entities or TOs. The query could become complex and the filter criteria highly repetitive. You will need the filter in all related queries which return the particular subset. Especially in the context of pagination, where the data is mostly retrieved for read-only purposes, database views are the easier and more efficient alternative. Instead of implementing a lot of plumbing on the “Java-side” all the work could be easily done in the database. You have just to create a SQL View – it is a part of the SQL standard and is even supported by the Derby DB shipped with Java:

CREATE VIEW APP.V_CUSTOMER(NAME,CITY) AS SELECT NAME,CITY FROM APP.CUSTOMER

For SQL queries there is no difference between views and tables, so you can easily map a JPA entity to a view transparently. The code on the Java side remains clean and simple – and you will even get better performance. There is a drawback: not all views are updatable. Whether a view is updatable or not highly depends on the complexity and particular database. E.g. in Derby DB all views are not updatable.

[It is an excerpt from my current book "Productive Java EE - Rethinking Best Practices]


NEW workshop: Microservices with Java EE 7 and Java 8, January 26th, 2015, Airport Munich

A book about rethinking Java EE Patterns

Comments:

Hello Adam,

The reference at the bottom to your current book - do you have any details about this? When/what languages/etc? I'm planning to take the SCEA in the next six months or so, and your book sounds like it will make an interesting read alongside the existing recommended reading.

Regards,

Ghee

Posted by Ghee on January 06, 2009 at 03:16 PM CET #

Adam,
isn't there something like projections in JPA (like in JDO2) ?

Effectively there are the way to realize views while staying in the java- /OR-Mapper world. Furthermore you are a lot more flexible and not bound to the views you created.
For instance you can choose to project data on a custom bean (like you created for the view) or you might also just retrieve a list of maps if that's sufficient.

They should be as effective as normal DB-Views, because the generated SQL is the same as the one from the view.

Posted by Stefan Hansel on January 06, 2009 at 09:45 PM CET #

@Stefan Hansel

EJB 3 allows EJB projection using EJB QL. If you are querying for a single result, then it will give you can object array, else it will give you an array of object array. You can to reconstruct the object array to create the object, or you can specify the constructor in the EJB QL, i.e. select new foopackage.foo(c.id) from foo c with the assumption that the constructor is defined in the class.

Of course, the power of the views will be limited to the object graph of the base object. The only downside in performing EJB projection is that they are not registered in the first and second level cache. Therefore, each projection will bypass the cache and increase database hits.

Thus, the domain model that defines the object graph plays a big role in maximizing EJB. Bad domain model means bad performance for EJB.

Regards.

Posted by Jerwin Louise V. Uy on January 07, 2009 at 11:25 PM CET #

@Ghee,

thank you for the nice email. If you really want to pass the SCEA exam, I don't think my book will be the right choice for you. It is too pragmatic :-). It will be, however, published in english only...
...I will give a class for the SCEA preparation in few weeks...

Thank you for the nice comment && happy new year,

regards,
adam

Posted by Adam Bien on January 08, 2009 at 11:51 PM CET #

@Stefan Hansel,

sometimes it is worth to be lazy :-). Jerwin answered your question really well.

The advantage of DB-views in *some* cases is clean Java code. You can just map your entity to the view and it looks like a table... Performance is better as well, because the database can cache the data internally. Some databases do support even materialized views...

thanks for your comment, and sorry for my late answer...

adam

Posted by Adam Bien on January 08, 2009 at 11:55 PM CET #

Hi.

Can any one tel me how to call postgre db view in java?

Posted by Manoj on June 09, 2011 at 02:03 PM CEST #

Hello Adam,

long time no see (we met at BMW about 8 years ago).

Late comment on your post.
I have used views a lot for reporting - and the java code is then much cleaner.

I have an entity - it is included in the hibernate.cfg.xml to generate the table.

In order to calculate something, I include an array of things based on a view.
I want to tell hibernate not to generate the view even though it is included in the table ...

Posted by david cole on June 01, 2012 at 08:34 PM CEST #

Post a Comment:
  • HTML Syntax: NOT allowed
realworldpatterns.com
...the last 150 posts
...the last 10 comments
License