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]