How to use Mysql view’s with Spring Data and eclipselink

Question

In the development of my project I came across a select that will require a lot of application performance, as it consists of using two Join’s. Soon came to mind the creation of a view in Mysql, but the problem is that I never used it with eclipselink and Spring Data. Has anyone ever gone through this? Wasn’t wanting to accomplish @query("minha_consulta") to bring the data.

Answer 1

It is possible to map a view as a common table using @Entity and @Table no difficulties. I’ve done this a few times using Hibernate.

Just be careful not to use DDL generation, otherwise Eclipselink might try to run a ALTER TABLE in view.

Also, do not change the entity, that is, when you retrieve an object from the bank, do not use the methods setters because JPA understands this as a change in the data and will try to make a UPDATE.

However, specifically in Eclipselink, I believe that the above two problems can be avoided using the annotation @ReadOnly in its entity.

Example:

@ReadOnly 
@Entity 
@Table(name = "VW_MINHAVIEW") 
public class ReadOnlyEntity { ... }

Answer 2

@utluiz, got grade 10! I had to do the following, I created a view in my BD:

select c.id_customer, c.tenant_id, c.first_name, ds.name 
from viatgedb.customer c
join viatgedb.customer_service cs
on c.id_customer = cs.fk_customer 
join viatgedb.destination_requested ds on cs.id_customer_service = ds.fk_customer_service;

Soon after I mapped JPA (I’m working with multi-tenant architecture):

@ReadOnly
@Entity
@Table(name="vw_open_services")
@Multitenant
@TenantDiscriminatorColumn(name = "tenant_id", discriminatorType = DiscriminatorType.INTEGER, contextProperty = PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT)
public class VwOpenService implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id_customer")
    private Long id;

    @Column(name="tenant_id", insertable=false, updatable=false)
    private Long tenantId;

    @Column(name="day_service")
    private String dayService;

    @Column(name="first_name")
    private String firstName;

    @Column(name="NAME")
    private String name;

    // Getter and Setter

And I usually create a repository to bring my records with Spring Data:

@Repository
public interface OpenServiceRepository extends BaseRepository<VwOpenService, Long>{

    List<VwOpenService> findAll(); 

}

The result was as expected.

Thank you