Does JPA support mapping to sql views?

Question

I'm currently using Eclipselink, but I know now days most JPA implementations have been pretty standardized. Is there a native way to map a JPA entity to a view? I am not looking to insert/update, but the question is really how to handle the @Id annotation. Every entity in the JPA world must have an ID field, but many of the views I have created do not conform to this. Is there native support for this in the JPA or do I need to use hacks to get it to work? I've searched a lot and found very little information about doing this.

Thessalonians

Views functions as normal tables, however if you views does not fit well with JPA ORM mapping you may instead use a stored procedure to return a custom cursor formed from the view(s). You can map stored procedures in EclipseLink using the @NamedStoredProcedureQuery annotation. For more info on this Google for "EclipseLink Extensions". – Zoospore

Answer 1

While using the @Id annotation with fields of directly supported types is not the only way to specify an entity's identity (see @IdClass with multiple @Id annotations or @EmbeddedId with @Embedded), the JPA specification requires a primary key for each entity.

That said, you don't need entities to use JPA with database views. As mapping to a view is no different from mapping to a table from an SQL perspective, you could still use native queries (createNativeQuery on EntityManager) to retrieve scalar values instead.

Kailey

JPA libs as of 10/2020 throw runtime exception "not a managed entity" – Ephemera

Answer 2

I've been looking into this myself, and I've found a hack that I'm not 100% certain works but that looks promising.

In my case, I have a FK column in the view that can effectively function as a PK -- any given instance of that foreign object can only occur once in the view. I defined two objects off of that one field: one is designated the ID and represents the raw value of the field, and the other is designated read-only and represents the object being referred to.

@Id
@Column(name = "foreignid", unique = true, nullable = false)
public Long getForeignId() {
...

@OneToOne
@JoinColumn(name = "foreignid", insertable=false, updatable=false)
public ForeignObject getForeignObject() {
...

Like I said, I'm not 100% sure on this one (and I'll just delete this answer if it turns out not to work), but it got my code past a particular crash point.

Dunno if it applies to your specific situation, though. And there's an excellent chance that after 11 months, you no longer care. :-) What the hell, that "Necromancer" badge doesn't just earn itself....

Brainard

Answer 3

In my view I have a "unique" id, so I mapped it as the Entity id. It works very well:

@Entity
@Table(name="table")
@NamedQuery(name="Table.findAll", query="SELECT n FROM Table n")
public class Table implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="column_a")
    private int columnA;

Euplastic

Answer 4

JPA - 2.5.4

    CREATE MATERIALIZED VIEW IF NOT EXISTS needed_article as select  product_id, count(product_id) as count from product_article group by product_id;
    CREATE MATERIALIZED VIEW IF NOT EXISTS available_article as select product_id, count(product_id) as count from article a inner join  product_article p
                                on  a.id = p.article_id and a.stock >= p.amount_of group by product_id;
    
    CREATE UNIQUE INDEX productId_available_article ON available_article (product_Id);
    CREATE UNIQUE INDEX productId_needed_article ON needed_article (product_Id);

Entity.java

@Entity
@Immutable // hibernate import
@Getter
@Setter
public class NeededArticle {
    @Id
    Integer productId;
    Integer count;
}

Repository.java

@Repository
public interface AvailableProductRepository extends CrudRepository<AvailableArticle, Integer> {

    @Query("select available.productId from AvailableArticle available, NeededArticle needed where available.productId = needed.productId and available.count = needed.count")
    List<Integer> availableProduct();

Shaitan